Lecture 5: Advanced SQL Queries and Triggers

Lecture 5: More SQL Queries, Triggers, and Views

• Lecture focuses on complex SQL queries, triggers, and views
• Topics within the lecture include more complex SQL retrieval queries, triggers in SQL, and views

More Complex SQL Retrieval Queries

• Nested queries: Complete SELECT-FROM-WHERE blocks placed within the WHERE clause of other queries; used for filtering data based on results from other queries
  • Example Syntax:

    SELECT Pnumber, Pname FROM project
    WHERE Pnumber IN (SELECT Pnumber FROM Project, Department WHERE dname = 'Finance');
    

• Nested queries (cont.): Employ comparison operators to compare values; avoiding potential errors and ambiguities via tuple variables (aliases) in SQL queries
  • Example Syntax:

    SELECT Lname, Fname FROM employee
    WHERE salary > ALL (SELECT salary FROM employee WHERE Dno = 5);
    

• Correlated nested queries: Nested queries using = or IN operators, which can be collapsed into one single block
  • Example Syntax for Query 16A:

    SELECT E.Fname, E.Lname
    FROM EMPLOYEE AS E, DEPENDENT AS D
    WHERE E.Ssn=D.Essn AND E.Sex=D.Sex AND E.Fname=D.Dependent_name;
    

• EXISTS and NOT EXISTS functions: Boolean functions returning TRUE or FALSE; typically used with correlated nested queries to check if the result of a nested query is empty or not. The NOT EXISTS function is typically used in conditions where a query needs to check whether a result does not exist.
  • Example use of EXISTS:

    SELECT Fname, Lname
    FROM Employee
    WHERE EXISTS (SELECT * FROM DEPENDENT WHERE Ssn= Essn)
    AND EXISTS (SELECT * FROM Department WHERE Ssn= Mgr_Ssn);
    

Aggregate Functions

• Summarize information from multiple tuples into a single tuple
• COUNT, SUM, MAX, MIN, AVG are built-in functions;
  • Example Syntax:

    SELECT SUM(Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM EMPLOYEE;
    

Grouping: The GROUP BY Clause

• Partitions the relation into subsets of tuples based on grouping attributes.
• Functions are applied to each group independently.
• The grouping attribute must appear in SELECT clause
  • Example Syntax:

    SELECT Dno, COUNT (*), AVG (Salary) FROM EMPLOYEE GROUP BY Dno;
    

• GROUP BY can be used with joins
  • Example Syntax (with join):

    SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON
    WHERE Pnumber=Pno GROUP BY Pnumber, Pname;        
    

The GROUP BY and HAVING Clauses

• HAVING clause filters groups defined by GROUP BY clause.
• Condition is applied to entire groups.
• Syntax includes GROUP BY and HAVING clauses -Example Syntax: sql SELECT Pnumber, Pname, COUNT (*) as Ccol FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Pname HAVING Ccol > 2;

Use of Case

• Used to assign different values based on conditions in SQL queries
• Example Syntax:

  UPDATE EMPLOYEE
  SET Salary =
  CASE
  WHEN Dno = 5 THEN Salary + 2000
  WHEN Dno = 4 THEN Salary + 1500
  WHEN Dno = 1 THEN Salary + 3000
  

Introduction to Triggers in SQL

• CREATE TRIGGER statements monitor the database
• Triggers have three components (event, condition, action)

Use of Triggers

• Example trigger to check salary before insert or update

CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE OF Salary, Supervisor_ssn ON
EMPLOYEE
FOR EACH ROW WHEN (NEW.SALARY > ( SELECT Salary
FROM EMPLOYEE WHERE Ssn = NEW. Supervisor_Ssn))
INFORM_SUPERVISOR (NEW.Supervisor.Ssn, New.Ssn)

This quiz covers the concepts from Lecture 5, focusing on complex SQL retrieval queries, triggers, and views. It explores nested queries, their syntax, and how to effectively use them in SQL. Enhance your understanding of SQL by testing your knowledge on these advanced topics.