Lecture 5: More SQL Queries, Triggers & Views

Lecture 5 More SQL: Complex Queries, Triggers and Views Database 2 Dr. Mohamed Saied Amer Outline More Complex SQL Retrieval Queries Triggers in SQL Views More Complex SQL Retrieval Queries More Complex SQL Retrieval Queries Nested queries Aggregate functions...

Lecture 5 More SQL: Complex Queries, Triggers and Views Database 2 Dr. Mohamed Saied Amer Outline More Complex SQL Retrieval Queries Triggers in SQL Views More Complex SQL Retrieval Queries More Complex SQL Retrieval Queries Nested queries Aggregate functions Grouping and Having Use of Case Nested queries Complete select-from-where blocks within WHERE clause of another query. Syntax: SELECT Pnumber, Pname FROM project WHERE Pnumber IN ( SELECT Pnumber FROM Project, Department WHERE dname = ‘Finance’ ); SELECT Pnumber, Pname FROM project WHERE (Pnumber, Pname) IN ( SELECT Pnumber, Pname FROM Project, Department WHERE dname = ‘Finance’ ); Nested queries (cont.) Use other comparison operators to compare a single value Syntax: SELECT Lname, Fname FROM employee WHERE salary > All ( SELECT salary FROM employee WHERE Dno = 5 ); Nested queries (cont.) Avoid potential errors and ambiguities. Create tuple variables (aliases) for all tables referenced in SQL query Correlated Nested Queries Queries that are nested using the = or IN comparison operator can be collapsed into one single block: E.g., Q16 can be written as: The EXISTS and UNIQUE Functions EXISTS function Check whether the result of a correlated nested query is empty or not. They are Boolean functions that return a TRUE or FALSE result. EXISTS and NOT EXISTS Typically used in conjunction with a correlated nested query UNIQUE(Q) function Returns TRUE if there are no duplicate tuples in the result of query Q USE of EXISTS USE OF NOT EXISTS Query: List first and last name of employees who work on ALL projects controlled by Dno=5. Aggregate Functions Used to summarize information from multiple tuples into a single- tuple summary Built-in aggregate functions: COUNT, SUM, MAX, MIN, and AVG Syntax: SELECT SUM(Salary), MAX (Salary), MIN (Salary), AVG (Salary) FROM EMPLOYEE; SELECT SUM(Salary) as Total_Salary, MAX (Salary) as Max_salary, MIN (Salary) as Min_Salary, AVG (Salary) as Average_salary FROM EMPLOYEE; Grouping: The GROUP BY Clause Partition relation into subsets of tuples Based on grouping attribute(s) Apply function to each such group independently Syntax: The grouping attribute must appear in the SELECT clause. SELECT Dno, COUNT (*), AVG (Salary) FROM EMPLOYEE GROUP BY Dno; Examples of GROUP BY GROUP BY may be applied to the result of a JOIN: Syntax: SELECT Pnumber, Pname, COUNT (*) FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Pname; The GROUP BY and HAVING Clauses HAVING clause Provides a condition to select or reject an entire group. Syntax: SELECT Pnumber, Pname, COUNT (*) as Ccol FROM PROJECT, WORKS_ON WHERE Pnumber=Pno GROUP BY Pnumber, Pname HAVING Ccol > 2; Use of Case Used when a value can be different based on certain conditions. Can be used in any part of an SQL query where a value is expected. Syntax: Introduction to Triggers in SQL CREATE TRIGGER statement Used to monitor the database Typical trigger has three components which make it a rule for an “active database “: Event(s) Condition Action USE OF TRIGGERS AN EXAMPLE with standard Syntax. 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)