SELECT queries - Revision history

Leksy: /* LEFT OUTER JOIN */

2024-02-08T23:38:25Z

LEFT OUTER JOIN

2024-02-08T23:38:15Z

FULL OUTER JOIN

2024-02-08T23:37:46Z

Order by one value ascending (implied)

2024-02-08T23:37:13Z

Order by one value DESC (implied)

2024-02-08T23:36:32Z

Order by cascaded

2024-02-08T23:35:54Z

Where subquery

2024-02-08T23:35:35Z

UNION

2024-02-08T23:35:10Z

INTERSECT

2024-02-08T23:34:46Z

EXCEPT

2024-02-08T23:34:23Z

EXCEPT

@@ Line 62: / Line 62: @@
 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<br>
 FROM EMPLOYEE LEFT OUTER JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN;

@@ Line 75: / Line 75: @@
 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<br>
 FROM EMPLOYEE FULL OUTER JOIN DEPENDENT ON EMPLOYEE.SSN = DEPENDENT.ESSN;

@@ Line 118: / Line 118: @@
 ==Order by one value ascending (implied)==
-SELECT FNAME FIRST_NAME,
+SELECT FNAME FIRST_NAME, <br>
-MINIT "MIDDLE INITIAL" ,
+MINIT "MIDDLE INITIAL" , <br>
-LNAME "LAST NAME",
+LNAME "LAST NAME", <br>
-SSN "SOCIAL SECURITY NUMBER",
+SSN "SOCIAL SECURITY NUMBER", <br>
-BDATE BIRTH_DATE,
+BDATE BIRTH_DATE,<br>
-ADDRESS,
+ADDRESS, <br>
-SEX GENDER,
+SEX GENDER,<br>
-SALARY,
+SALARY, <br>
-SUPER_SSN "SUPERVISOR NUMBER",
+SUPER_SSN "SUPERVISOR NUMBER", <br>
-DNO
+DNO<br>
-FROM EMPLOYEE
+FROM EMPLOYEE<br>
 ORDER BY LNAME ASC;

@@ Line 133: / Line 133: @@
 ==Order by one value DESC (implied)==
-SELECT FNAME FIRST_NAME,
+SELECT FNAME FIRST_NAME, <br>
-MINIT "MIDDLE INITIAL" ,
+MINIT "MIDDLE INITIAL" , <br>
-LNAME "LAST NAME",
+LNAME "LAST NAME",<br>
-SSN "SOCIAL SECURITY NUMBER",
+SSN "SOCIAL SECURITY NUMBER", <br>
-BDATE BIRTH_DATE,
+BDATE BIRTH_DATE,<br>
-ADDRESS,
+ADDRESS, <br>
-SEX GENDER,
+SEX GENDER, <br>
-SALARY,
+SALARY, <br>
-SUPER_SSN "SUPERVISOR NUMBER",
+SUPER_SSN "SUPERVISOR NUMBER", <br>
-DNO
+DNO<br>
-FROM EMPLOYEE
+FROM EMPLOYEE<br>
 ORDER BY LNAME DESC;

@@ Line 148: / Line 148: @@
 ==Order by cascaded==
-SELECT FNAME FIRST_NAME,
+SELECT FNAME FIRST_NAME, <br>
-MINIT "MIDDLE INITIAL" ,
+MINIT "MIDDLE INITIAL" , <br>
-LNAME "LAST NAME",
+LNAME "LAST NAME", <br>
-SSN "SOCIAL SECURITY NUMBER",
+SSN "SOCIAL SECURITY NUMBER", <br>
-BDATE BIRTH_DATE,
+BDATE BIRTH_DATE, <br>
-ADDRESS,
+ADDRESS, <br>
-SEX GENDER,
+SEX GENDER, <br>
-SALARY,
+SALARY, <br>
-SUPER_SSN "SUPERVISOR NUMBER",
+SUPER_SSN "SUPERVISOR NUMBER", <br>
-DNO
+DNO<br>
-FROM EMPLOYEE
+FROM EMPLOYEE<br>
 ORDER BY FNAME, LNAME;

@@ Line 228: / Line 228: @@
 ==Where subquery==
-SELECT E.SSN, E.FNAME FIRST_NAME, E.LNAME LAST_NAME
+SELECT E.SSN, E.FNAME FIRST_NAME, E.LNAME LAST_NAME<br>
-FROM EMPLOYEE E
+FROM EMPLOYEE E<br>
 WHERE E.SSN IN (SELECT ESSN FROM DEPENDENT);

@@ Line 234: / Line 234: @@
 ==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<br>
-FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
+FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN<br>
-WHERE M.LNAME in ('Wong')
+WHERE M.LNAME in ('Wong')<br>
-UNION
+UNION<br>
-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<br>
-FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN
+FROM EMPLOYEE E JOIN EMPLOYEE M ON E.SUPER_SSN = M.SSN<br>
 WHERE M.LNAME in ('Wallace');

@@ Line 246: / Line 246: @@
 Tuples that are found in both type compatible relations.
-SELECT LNAME, FNAME
+SELECT LNAME, FNAME<br>
-FROM EMPLOYEE, DEPARTMENT
+FROM EMPLOYEE, DEPARTMENT<br>
-WHERE SSN = MGR_SSN
+WHERE SSN = MGR_SSN<br>
-INTERSECT
+INTERSECT<br>
-SELECT LNAME, FNAME
+SELECT LNAME, FNAME<br>
-FROM EMPLOYEE, WORKS_ON
+FROM EMPLOYEE, WORKS_ON<br>
 WHERE ESSN=SSN AND HOURS > 0

@@ Line 255: / Line 255: @@
 ==EXCEPT==
-Results in tuples that are in one type compatible relation but not in the other*/
+Results in tuples that are in one type compatible relation but not in the other.
 SELECT LNAME, FNAME<br>