Based on the source provided, here is a comprehensive list of SQL queries categorized by the
advanced topics covered in the material:
1. Comparisons with NULL and Three-Valued Logic
Checking for NULL values: Retrieve the names of employees who do not have a supervisor.
SELECT Fname, Lname FROM EMPLOYEE
WHERE Superssn IS NULL;
2. Nested Queries and Set Comparisons
Using IN: Retrieve project numbers for projects that involve a manager or an employee named
'Smith'.
SELECT DISTINCT Pnumber FROM PROJECT
WHERE Pnumber IN (SELECT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum=Dnumber AND Mgrssn=Ssn AND Lname='Smith')
OR Pnumber IN (SELECT Pno FROM WORKS_ON, EMPLOYEE
WHERE Essn=Ssn AND Lname='Smith');
Tuple Comparison: Retrieve the Ssns of employees working on the same (project, hours)
combination as 'John Smith'.
SELECT DISTINCT Essn FROM WORKS_ON
WHERE (Pno, Hours) IN (SELECT Pno, Hours FROM WORKS_ON WHERE Ssn =
'123456789');
Set Comparison (> ALL): Retrieve names of employees whose salary is higher than every
employee in department 5.
SELECT Lname, Fname FROM EMPLOYEE
WHERE Salary > ALL (SELECT Salary FROM EMPLOYEE WHERE Dno = 5);
3. Correlated Nested Queries and EXISTS
Correlated Query: Find employees who have a dependent with the same first name and sex as
themselves.
SELECT [Link], [Link] FROM EMPLOYEE AS E
WHERE [Link] IN (SELECT Essn FROM DEPENDENT
WHERE [Link] = Dependent_name AND [Link] = Sex);
Using EXISTS: Same as above, using the EXISTS function.
SELECT [Link], [Link] FROM EMPLOYEE AS E
WHERE EXISTS (SELECT * FROM DEPENDENT AS D
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = D.Dependent_name);
Using NOT EXISTS: Retrieve names of employees with no dependents.
SELECT FNAME, LNAME FROM EMPLOYEE
WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN);
Set Difference (EXCEPT): Retrieve names of employees who work on all projects controlled
by department 5.
SELECT Fname, Lname FROM EMPLOYEE
WHERE NOT EXISTS ((SELECT Pnumber FROM PROJECT WHERE Dnum=5)
EXCEPT (SELECT Pno FROM WORKS_ON WHERE Ssn=Essn));
4. Joined Tables and Renaming
Explicit Inner Join: Retrieve name and address of employees in the 'Research' department.
SELECT Fname, Lname, Address FROM (EMPLOYEE JOIN DEPARTMENT ON
Dno=Dnumber)
WHERE Dname = 'Research';
Left Outer Join: Retrieve employee names and their supervisor's name, including employees
without supervisors.
SELECT [Link] AS Employee_name, [Link] AS Supervisor_name
FROM (EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S ON [Link]=[Link]);
Multiway Join: Join three tables (Project, Department, and Employee) as a single joined table.
SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS
FROM ((PROJECT JOIN DEPARTMENT ON DNUM=DNUMBER) JOIN EMPLOYEE ON
MGRSSN=SSN)
WHERE PLOCATION='Stafford';
5. Aggregate Functions and Grouping
Basic Aggregates: Find the sum, max, min, and average salaries of all employees.
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE;
Grouping with HAVING: For each project with more than two employees, retrieve the project
info and employee count.
SELECT Pnumber, Pname, COUNT(*)
FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO
GROUP BY Pnumber, Pname
HAVING COUNT(*) > 2;
6. Advanced SQL Constructs
WITH Clause: Define a temporary table for departments with more than 5 employees to use in
a query.
WITH LARGE_DEPTS (Dno) AS (SELECT Dno FROM EMPLOYEE GROUP BY Dno HAVING
COUNT(*) > 5)
SELECT Dno, COUNT(*) FROM EMPLOYEE
WHERE Salary > 40000 AND Dno IN LARGE_DEPTS GROUP BY Dno;
CASE Statement: Perform an update with different salary raises based on the department.
UPDATE EMPLOYEE
SET Salary = CASE WHEN Dno = 5 THEN Salary + 2000
WHEN Dno = 4 THEN Salary + 1500
WHEN Dno = 1 THEN Salary + 3000
ELSE Salary + 0 END;
Recursive Query: Retrieve all levels of supervisees for supervisors.
WITH RECURSIVE SUP_EMP (Supssn, Empssn) AS
(SELECT Superssn, Ssn FROM EMPLOYEE
UNION SELECT [Link], [Link] FROM EMPLOYEE AS E, SUP_EMP AS S
WHERE [Link] = [Link])
SELECT * FROM SUP_EMP;
7. Constraints, Triggers, and Transactions
Assertion: Ensure no employee makes more than their department manager.
CREATE ASSERTION SALARY_CONSTRAINT
CHECK (NOT EXISTS(SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE [Link] > [Link] AND [Link] = [Link] AND [Link] =
[Link]));
Trigger: Notify a supervisor if an employee's salary becomes higher than theirs.
CREATE TRIGGER SALARYVIOLATION
BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN ON EMPLOYEE
FOR EACH ROW
WHEN ([Link] > (SELECT SALARY FROM EMPLOYEE WHERE SSN =
NEW.SUPERVISOR_SSN))
INFORM_SUPERVISOR(NEW.Supervisor_ssn, [Link]);
Transaction: Setting isolation levels and committing changes.
SET TRANSACTION READ WRITE ISOLATION LEVEL SERIALIZABLE;
INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno, Salary) VALUES ('Robert', 'Smith',
'991004321', 2, 35000);
UPDATE EMPLOYEE SET Salary = Salary * 1.1 WHERE Dno = 2;
COMMIT;