SELECT queries

selecting all content from a table

SELECT * FROM EMPLOYEE;

Specifying the selected columns from a table

SELECT FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPER_SSN, DNO FROM EMPLOYEE;

Using aliases

SELECT FNAME FIRST_NAME, /*The FNAME column is renamed FIRST_NAME*/
MINIT "MIDDLE INITIAL" , /*Renaming with a space*/
LNAME "LAST NAME",
SSN "SOCIAL SECURITY NUMBER",
BDATE AS BIRTH_DATE, /*Renaming with the AS keyword*/
ADDRESS,
SEX GENDER, /*Alias without AS keyword*/
SALARY,
SUPER_SSN "SUPERVISOR SOCIAL SECURITY NUMBER", /*A long alias. It might not work in all DBMS such as oracle*/
DNO
FROM EMPLOYEE;

Computed columns - example from oracle

SELECT FNAME, TRUNC((SYSDATE - BDATE) / 365) AS AGE

FROM EMPLOYEE;

Computed columns - date arithmetic

SELECT FNAME, BDATE BIRTH_DATE, BDATE + (65 * 365) as RETIREMENT_DATE

FROM EMPLOYEE;

Listing distinct values

SELECT DISTINCT ESSN FROM WORKS_ON;

Using Natural Join

Based on values with a common name

SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION

FROM DEPARTMENT NATURAL JOIN DEPT_LOCATIONS;

Using the USING syntax

Based on values with a common name

SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION
FROM DEPARTMENT JOIN DEPT_LOCATIONS USING (DNUMBER);

Using JOIN ON syntax

SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION
FROM DEPARTMENT
JOIN DEPT_LOCATIONS ON DEPARTMENT.DNUMBER = DEPT_LOCATIONS.DNUMBER;

LEFT OUTER JOIN

All data on "left side" table and nulls, if no data, from right side table*/

SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME, DEPENDENT.DEPENDENT_NAME
FROM EMPLOYEE LEFT OUTER JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN;

RIGHT OUTER JOIN

All data on the "right side" table and nulls, if no data, from the "left side" table*/

SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME, DEPENDENT.DEPENDENT_NAME
FROM EMPLOYEE RIGHT OUTER JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN;

FULL OUTER JOIN

All data on the "right side" table and nulls, if no data, from the "left side" table AND all data on the left side table and nulls, if no data, on the right side table*/

SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME, DEPENDENT.DEPENDENT_NAME
FROM EMPLOYEE FULL OUTER JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN;

Cross join

All attributes are joined in a cartesian product*/

SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME, DEPENDENT.DEPENDENT_NAME FROM EMPLOYEE CROSS JOIN DEPENDENT;

SELECT EMPLOYEE.*, DEPENDENT.* FROM EMPLOYEE CROSS JOIN DEPENDENT;

Joining tables with an alias

Here EMPLOYEE is renamed E and DEPENDENT is renamed D*/

SELECT E.SSN, E.FNAME, E.LNAME, D.DEPENDENT_NAME FROM EMPLOYEE E INNER JOIN DEPENDENT D ON E.SSN = D.ESSN;

Recursive join

A table joins itself

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN;

Order by one value ascending (implied)

SELECT FNAME FIRST_NAME,
MINIT "MIDDLE INITIAL" ,
LNAME "LAST NAME",
SSN "SOCIAL SECURITY NUMBER",
BDATE BIRTH_DATE,
ADDRESS,
SEX GENDER,
SALARY,
SUPER_SSN "SUPERVISOR NUMBER",
DNO
FROM EMPLOYEE
ORDER BY LNAME;

Order by one value ascending (implied)

SELECT FNAME FIRST_NAME,
MINIT "MIDDLE INITIAL" ,
LNAME "LAST NAME",
SSN "SOCIAL SECURITY NUMBER",
BDATE BIRTH_DATE,
ADDRESS,
SEX GENDER,
SALARY,
SUPER_SSN "SUPERVISOR NUMBER",
DNO
FROM EMPLOYEE
ORDER BY LNAME ASC;

Order by one value DESC (implied)

SELECT FNAME FIRST_NAME,
MINIT "MIDDLE INITIAL" ,
LNAME "LAST NAME",
SSN "SOCIAL SECURITY NUMBER",
BDATE BIRTH_DATE,
ADDRESS,
SEX GENDER,
SALARY,
SUPER_SSN "SUPERVISOR NUMBER",
DNO
FROM EMPLOYEE
ORDER BY LNAME DESC;

Order by cascaded

SELECT FNAME FIRST_NAME,
MINIT "MIDDLE INITIAL" ,
LNAME "LAST NAME",
SSN "SOCIAL SECURITY NUMBER",
BDATE BIRTH_DATE,
ADDRESS,
SEX GENDER,
SALARY,
SUPER_SSN "SUPERVISOR NUMBER",
DNO
FROM EMPLOYEE
ORDER BY FNAME, LNAME;

WHERE Clause

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE M.LNAME = 'Wallace';

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.BDATE, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE E.BDATE <= '01-JAN-1965'; /*ORACLE syntax*/

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.BDATE, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE E.BDATE <= '1965-01-01'; /*H2 syntax*/

OR Syntax

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE M.LNAME = 'Wallace' OR M.LNAME = 'Borg';

AND Syntax

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE E.LNAME = 'English' AND M.LNAME = 'Wong';

Not Syntax

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE M.LNAME != 'Wong';

IN Syntax

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE M.LNAME in ('Wong','Wallace');

BETWEEN Syntax

SELECT E.SSN EMP_NUMBER,
E.LNAME LAST_NAME,
E.BDATE,
E.SUPER_SSN MGR_NUMBER,
M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE E.BDATE BETWEEN '1960-01-01' AND '1969-12-31'

Grouping Data

SELECT P.PNAME, COUNT(ESSN) AS NO_OF_EMP_ON_PROJ
FROM WORKS_ON WO JOIN PROJECT P ON WO.PNO = P.PNUMBER
JOIN EMPLOYEE E ON WO.ESSN = E.SSN
GROUP BY P.PNAME

Having Clause

SELECT P.PNAME, COUNT(ESSN) AS NO_OF_EMP_ON_PROJ
FROM WORKS_ON WO JOIN PROJECT P ON WO.PNO = P.PNUMBER
JOIN EMPLOYEE E ON WO.ESSN = E.SSN
GROUP BY P.PNAME
HAVING COUNT(ESSN) <= 2

Where subquery

SELECT E.SSN, E.FNAME FIRST_NAME, E.LNAME LAST_NAME
FROM EMPLOYEE E
WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT);

UNION

SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE M.LNAME in ('Wong')
UNION
SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE M.LNAME in ('Wallace');

INTERSECT

Tuples that are found in both type compatible relations.

SELECT LNAME, FNAME
FROM EMPLOYEE, DEPARTMENT
WHERE SSN = MGR_SSN
INTERSECT
SELECT LNAME, FNAME
FROM EMPLOYEE, WORKS_ON
WHERE ESSN=SSN AND HOURS > 0

EXCEPT

Results in tuples that are in one type compatible relation but not in the other.

SELECT LNAME, FNAME
FROM EMPLOYEE, DEPARTMENT
WHERE SSN = MGR_SSN
EXCEPT
SELECT LNAME, FNAME
FROM EMPLOYEE, WORKS_ON
WHERE ESSN=SSN AND HOURS > 0

MINUS

Tuples in one relation but not in the other

SELECT LNAME, FNAME
FROM EMPLOYEE, DEPARTMENT
WHERE SSN = MGR_SSN
MINUS
SELECT LNAME, FNAME
FROM EMPLOYEE, WORKS_ON
WHERE ESSN=SSN AND HOURS > 0

Order of operations for OR and AND

The order of operations is important in OR and AND operands. The following queries produce different results. The first has no parenthesis, which is adjudicated first

SELECT E.SSN EMP_NUMBER,
E.LNAME LAST_NAME,
E.SEX,
E.SALARY,
E.SUPER_SSN MGR_NUMBER,
M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE E.SUPER_SSN = 333445555 OR E.SUPER_SSN = 888665555 AND E.SALARY > 40000.00;

SELECT E.SSN EMP_NUMBER,
E.LNAME LAST_NAME,
E.SEX, E.SALARY,
E.SUPER_SSN MGR_NUMBER,
M.LNAME MANAGER_LAST_NAME
FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
WHERE (E.SUPER_SSN = 333445555 OR E.SUPER_SSN = 888665555) AND E.SALARY > 40000.00;