SQL Rules
Data Retrieval- SELECT
DML (Data Manipulation Language)- INSERT, UPDATE, DELETE, MERGE
Data definition language (DDL)- CREATE, ALTER, DROP, RENAME, TRUNCATE
Transaction control- COMMIT, ROLLBACK, SAVEPOINT
Data control language (DCL)- GRANT, REVOKE
SQL SELECT Statements

Arithmetic Expressions
Operator
+ Add - Subtract
* Multiply / Divide
Using Arithmetic Operators:
SELECT last_name, salary, salary + 300 FROM employees; Operator Precedence:
•Multiplication and division take priority over addition and subtraction.
• Operators of the same priority are evaluated from left to right.
• Parentheses are used to force prioritized evaluation and to clarify statements.
Example: SELECT last_name, salary, 12*salary+100 FROM employees;
Using Parentheses: SELECT last_name, salary, 12*(salary+100) FROM employees;
Defining a Null Value
• A null is a value that is unavailable, unassigned, unknown, or inapplicable.
• A null is not the same as zero or a blank space.
Null Values in Arithmetic Expressions
Arithmetic expressions containing a null value evaluate to null.
Defining a Column Alias
A column alias:
• Renames a column heading
• Is useful with calculations
• Immediately follows the column name - there can also be the optional AS keyword between the column name and alias
• Requires double quotation marks if it contains spaces or special characters or is case sensitive
Example: SELECT last_name AS name, commission_pct comm. FROM employees;
SELECT last_name "Name", salary*12 "Annual Salary" FROM employees;
Concatenation Operator
A concatenation operator:
• Concatenates columns or character strings to other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character expression
Example: SELECT last_name||job_id AS "Employees" FROM employees;
Literal Character Strings
• A literal is a character, a number, or a date included in the SELECT list.
• Date and character literal values must be enclosed within single quotation marks.
• Each character string is output once for each row returned.
Example: SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;
Duplicate Rows
The default display of queries is all rows, including duplicate rows.
Example: SELECT department_id FROM employees;
Eliminating Duplicate Rows
Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.
Example: SELECT DISTINCT department_id FROM employees;
Displaying Table Structure: DESC table name
Limiting the Rows Selected
• Restrict the rows returned by using the WHERE clause.
• The WHERE clause follows the FROM clause.
Example: SELECT employee_id, last_name, job_id, department_id FROM employees
WHERE department_id = 90 ;
Character Strings and Dates
• Character strings and date values are enclosed in single quotation marks.
• Character values are case sensitive, and date values are format sensitive.
• The default date format is DD-MON-RR.
Example: SELECT last_name, job_id, department_id FROM employees
WHERE last_name = 'Whalen';
Comparison Conditions
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
<> Not equal to
Example: SELECT last_name, salary FROM employees
WHERE salary <= 3000;
Other Comparison Conditions
Operator Meaning
BETWEEN ...AND... Between two values (inclusive
IN(set) Match any of a list of values
LIKE Match a character pattern
IS NULL Is a null value
Using the BETWEEN Condition
Use the BETWEEN condition to display rows based on a range of values.
SELECT last_name, salary FROM employees
WHERE salary BETWEEN 2500 AND 3500;
Using the IN Condition
Use the IN membership condition to test for values in a list.
SELECT employee_id, last_name, salary, manager_id FROM employees
WHERE manager_id IN (100, 101, 201);
Using the LIKE Condition
• Use the LIKE condition to