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 EXCEPT SELECT LNAME, FNAME FROM EMPLOYEE, WORKS_ON WHERE ESSN=SSN AND HOURS > 0

/*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;