Difference between revisions of "SELECT queries"
| Line 40: | Line 40: | ||
FROM WORKS_ON; | FROM WORKS_ON; | ||
| − | ==Using Natural Join | + | ==Using Natural Join== |
| + | Based on values with a common name | ||
SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION | SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION | ||
| Line 46: | Line 47: | ||
FROM DEPARTMENT NATURAL JOIN DEPT_LOCATIONS; | FROM DEPARTMENT NATURAL JOIN DEPT_LOCATIONS; | ||
| − | + | ==Using the USING syntax== | |
| + | Based on values with a common name | ||
SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION | SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION | ||
FROM DEPARTMENT JOIN DEPT_LOCATIONS USING (DNUMBER); | FROM DEPARTMENT JOIN DEPT_LOCATIONS USING (DNUMBER); | ||
| − | + | ==Using JOIN ON syntax== | |
SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION | SELECT DEPARTMENT.DNAME, DEPT_LOCATIONS.DLOCATION | ||
| Line 57: | Line 59: | ||
JOIN DEPT_LOCATIONS ON DEPARTMENT.DNUMBER = DEPT_LOCATIONS.DNUMBER; | 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 | SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME, DEPENDENT.DEPENDENT_NAME | ||
FROM EMPLOYEE LEFT OUTER JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN; | 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 | SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME, DEPENDENT.DEPENDENT_NAME | ||
FROM EMPLOYEE RIGHT OUTER JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN; | 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 | SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME, DEPENDENT.DEPENDENT_NAME | ||
FROM EMPLOYEE FULL OUTER JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN; | 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 | SELECT EMPLOYEE.SSN, EMPLOYEE.FNAME, EMPLOYEE.LNAME, DEPENDENT.DEPENDENT_NAME | ||
| Line 80: | Line 88: | ||
FROM EMPLOYEE CROSS JOIN 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 | SELECT E.SSN, E.FNAME, E.LNAME, D.DEPENDENT_NAME | ||
FROM EMPLOYEE E INNER JOIN DEPENDENT D ON E.SSN = D.ESSN; | 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 | 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; | FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN; | ||
| − | + | ==Order by one value ascending (implied)== | |
SELECT FNAME FIRST_NAME, | SELECT FNAME FIRST_NAME, | ||
| Line 105: | Line 116: | ||
ORDER BY LNAME; | ORDER BY LNAME; | ||
| − | + | ==Order by one value ascending (implied)== | |
SELECT FNAME FIRST_NAME, | SELECT FNAME FIRST_NAME, | ||
| Line 120: | Line 131: | ||
ORDER BY LNAME ASC; | ORDER BY LNAME ASC; | ||
| − | + | ==Order by one value DESC (implied)== | |
SELECT FNAME FIRST_NAME, | SELECT FNAME FIRST_NAME, | ||
| Line 135: | Line 146: | ||
ORDER BY LNAME DESC; | ORDER BY LNAME DESC; | ||
| − | + | ==Order by cascaded== | |
SELECT FNAME FIRST_NAME, | SELECT FNAME FIRST_NAME, | ||
| Line 150: | Line 161: | ||
ORDER BY FNAME, LNAME; | 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 | SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME | ||
| Line 164: | Line 175: | ||
WHERE E.BDATE <= '1965-01-01'; /*H2 syntax*/ | 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 | SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME | ||
| Line 170: | Line 181: | ||
WHERE M.LNAME = 'Wallace' OR M.LNAME = 'Borg'; | 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 | SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME | ||
| Line 176: | Line 187: | ||
WHERE E.LNAME = 'English' AND M.LNAME = 'Wong'; | 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 | SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME | ||
| Line 182: | Line 193: | ||
WHERE M.LNAME != 'Wong'; | 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 | SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME | ||
| Line 188: | Line 199: | ||
WHERE M.LNAME in ('Wong','Wallace'); | 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 | SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.BDATE, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME | ||
| Line 194: | Line 205: | ||
WHERE E.BDATE BETWEEN '1960-01-01' AND '1969-12-31' | WHERE E.BDATE BETWEEN '1960-01-01' AND '1969-12-31' | ||
| − | + | ==Grouping Data== | |
SELECT P.PNAME, COUNT(ESSN) AS NO_OF_EMP_ON_PROJ | SELECT P.PNAME, COUNT(ESSN) AS NO_OF_EMP_ON_PROJ | ||
| Line 201: | Line 212: | ||
GROUP BY P.PNAME | GROUP BY P.PNAME | ||
| − | + | ==Having Clause== | |
SELECT P.PNAME, COUNT(ESSN) AS NO_OF_EMP_ON_PROJ | SELECT P.PNAME, COUNT(ESSN) AS NO_OF_EMP_ON_PROJ | ||
| Line 209: | Line 220: | ||
HAVING COUNT(ESSN) <= 2 | HAVING COUNT(ESSN) <= 2 | ||
| − | + | ==Where subquery== | |
SELECT E.SSN, E.FNAME FIRST_NAME, E.LNAME LAST_NAME | SELECT E.SSN, E.FNAME FIRST_NAME, E.LNAME LAST_NAME | ||
| Line 215: | Line 226: | ||
WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT); | 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 | SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME | ||
| Line 225: | Line 236: | ||
WHERE M.LNAME in ('Wallace'); | WHERE M.LNAME in ('Wallace'); | ||
| − | + | ==INTERSECT== | |
| + | |||
| + | Tuples that are found in both type compatible relations. | ||
SELECT LNAME, FNAME | SELECT LNAME, FNAME | ||
| Line 235: | Line 248: | ||
WHERE ESSN=SSN AND HOURS > 0 | 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 | SELECT LNAME, FNAME | ||
| Line 245: | Line 259: | ||
WHERE ESSN=SSN AND HOURS > 0 | WHERE ESSN=SSN AND HOURS > 0 | ||
| − | + | ==MINUS== | |
| + | Tuples in one relation but not in the other | ||
SELECT LNAME, FNAME | SELECT LNAME, FNAME | ||
| Line 255: | Line 270: | ||
WHERE ESSN=SSN AND HOURS > 0 | 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 | SELECT E.SSN EMP_NUMBER, E.LNAME LAST_NAME, E.SEX, E.SALARY, E.SUPER_SSN MGR_NUMBER, M.LNAME MANAGER_LAST_NAME | ||
Revision as of 19:19, 8 February 2024
Contents
- 1 selecting all content from a table
- 2 Specifying the selected columns from a table
- 3 Using aliases
- 4 Computed columns - example from oracle
- 5 Computed columns - date arithmetic
- 6 Listing distinct values
- 7 Using Natural Join
- 8 Using the USING syntax
- 9 Using JOIN ON syntax
- 10 LEFT OUTER JOIN
- 11 RIGHT OUTER JOIN
- 12 FULL OUTER JOIN
- 13 Cross join
- 14 Joining tables with an alias
- 15 Recursive join
- 16 Order by one value ascending (implied)
- 17 Order by one value ascending (implied)
- 18 Order by one value DESC (implied)
- 19 Order by cascaded
- 20 WHERE Clause
- 21 OR Syntax
- 22 AND Syntax
- 23 Not Syntax
- 24 IN Syntax
- 25 BETWEEN Syntax
- 26 Grouping Data
- 27 Having Clause
- 28 Where subquery
- 29 UNION
- 30 INTERSECT
- 31 EXCEPT
- 32 MINUS
- 33 Order of operations for OR and AND
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
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;
