SQL Quick reference

SQL Command Summary

Task Example
List all the columns of a table SELECT * FROM emp
List specific columns of a table SELECT ename, empno FROM emp
List selected rows of a table SELECT * FROM emp WHERE job='analyst' AND deptno=''10'
Order the rows of a table SELECT * FROM emp ORDER BY ename
List distinct values in a column SELECT DISTINCT job FROM emp
Compute a derived column SELECT 12*sal + comm AS total FROM emp
Joining two tables SELECT * FROM emp NATURAL JOIN dept
or
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno
Joining a table with itself SELECT * from emp m, emp e WHERE e.mgr = m.empno
Grouping rows SELECT job, count(*),avg(sal) FROM emp
Selecting Groups SELECT job, avg(sal) FROM emp GROUP BY job HAVING avg(sal) > 2000
Inserting data INSERT INTO dept VALUES (50, 'Information Systems', 'London')
Updating data UPDATE emp SET job=’REP’ WHERE job='SALESMAN'
Deleting data DELETE from emp WHERE empno=7900
Creating table CREATE TABLE loc (location VARCHAR(50) PRIMARY KEY )
Deleting a table DROP TABLE loc
Changing a Table structure ALTER TABLE loc ADD COLUMN latitude DECIMAL(10,2) )

Operators

Arithmetic

Operator Meaning Example
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulus ROWNO % 2 - 0 for even numbers
(... ) Sub-expression

Comparison

Operator Meaning Example
= Equal
> Greater than
>= Greater than or equal
< Less than
<= Less than or equal
<> , != Not equal
BETWEEN in range sal between 1000 and 2000
IN equals list member JOB in ("ANALYST","SALESMAN")
LIKE matches character pattern
% means zero or more characters
_ means one character		 name like "%S%" - names with "S" somewhere
IS NULL value is null

Logical

Operator Meaning Example
NOT( ) true if expression is false Note that NOT is a function.
AND true if both expression ture
OR true if either expression is true