SQL: Advanced Queries, Assertions, Triggers and Views PDF

Chapter 5 SQL: Advanced Queries, Assertions, Triggers and Views Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 5 Outline ▪ More Complex SQL Retrieval Queries ▪ Specifying Constraints as Assertions and Actions as Triggers ▪ Vi...

Chapter 5 SQL: Advanced Queries, Assertions, Triggers and Views Copyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-Wesley  Chapter 5 Outline ▪ More Complex SQL Retrieval Queries ▪ Specifying Constraints as Assertions and Actions as Triggers ▪ Views (Virtual Tables) in SQL ▪ Schema Change Statements in SQL Copyright © 2011 Ramez Elmasri and Shamkant Navathe  More Complex SQL Retrieval Queries ▪ Additional features allow users to specify more complex retrievals from database: ▪ Nested queries, joined tables, outer joins, aggregate functions, and grouping Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Comparisons Involving NULL and Three-Valued Logic ▪ Meanings of NULL ▪ Unknown value ▪ Unavailable or withheld value ▪ Not applicable attribute ▪ Each individual NULL value considered to be different from every other NULL value ▪ SQL uses a three-valued logic: ▪ TRUE, FALSE, and UNKNOWN Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Comparisons Involving NULL and Three-Valued Logic (cont’d.) Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Comparisons Involving NULL and Three-Valued Logic (cont’d.) ▪ SQL allows queries that check whether an attribute value is NULL ▪ IS or IS NOT NULL Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Nested Queries, Tuples, and Set/Multiset Comparisons ▪ Comparison operator IN ▪ Compares value v with a set (or multiset) of values V ▪ Evaluates to TRUE if v is one of the elements in V Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example Retrieve the social security numbers of all employees who work on project numbers 1, 2, or 3. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Retrieve the social security numbers of all employees who work on project numbers 1, 2, or 3. select distinct essn from works_on where pno in (1,2,3); Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Nested Queries, Tuples, and Set/Multiset Comparisons ▪ Nested queries ▪ Complete select-from-where blocks within WHERE clause of another query ▪ Outer query Outer Query Inner Query Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Nested Queries (cont’d.) ▪ Use tuples of values in comparisons ▪ Place them within parentheses Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Output: 123456789 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Output: 123456789, 45353453 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee Copyright © 2011 Ramez Elmasri and Shamkant Navathe Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee select fname,lname from employee where ssn in (select essn from dependent where fname=dependent_name and sex=sex); Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Nested Queries (cont’d.) ▪ Avoid potential errors and ambiguities ▪ Create tuple variables (aliases) for all tables referenced in SQL query Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Correlated Nested Queries ▪ Correlated nested query ▪ Evaluated once for each tuple in the outer query Copyright © 2011 Ramez Elmasri and Shamkant Navathe  The EXISTS and UNIQUE Functions in SQL ▪ EXISTS function ▪ Check whether the result of a correlated nested query is empty or not ▪ EXISTS and NOT EXISTS ▪ Typically used in conjunction with a correlated nested query ▪ SQL function UNIQUE(Q) ▪ Returns TRUE if there are no duplicate tuples in the result of query Q Copyright © 2011 Ramez Elmasri and Shamkant Navathe Retrieve the names of employees who have no dependents. select fname,lname from employee where not exists (select * from dependent where ssn=essn); Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example List the names of managers who have at least one dependent. Copyright © 2011 Ramez Elmasri and Shamkant Navathe List the names of managers who have at least one dependent. select fname,lname from employee where exists (select * from department where ssn=mgrssn) and exists (select * from dependent where ssn=essn); Copyright © 2011 Ramez Elmasri and Shamkant Navathe Copyright © 2011 Ramez Elmasri and Shamkant Navathe Retrieve the name of each employee who works in a project controlled by department 5 Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Explicit Sets and Renaming of Attributes in SQL ▪ Can use explicit set of values in WHERE clause ▪ Use qualifier AS followed by desired new name ▪ Rename any attribute that appears in the result of a query Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Grouping: The GROUP BY and HAVING Clauses ▪ Partition relation into subsets of tuples ▪ Based on grouping attribute(s) ▪ Apply function to each such group independently ▪ GROUP BY clause ▪ Specifies grouping attributes ▪ If NULLs exist in grouping attribute ▪ Separate group created for all tuples with a NULL value in grouping attribute Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Aggregate Functions in SQL ▪ Used to summarize information from multiple tuples into a single-tuple summary ▪ Grouping ▪ Create subgroups of tuples before summarizing ▪ Built-in aggregate functions ▪ COUNT, SUM, MAX, MIN, and AVG ▪ Functions can be used in the SELECT clause or in a HAVING clause Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Aggregate Functions in SQL (cont’d.) ▪ NULL values discarded when aggregate functions are applied to a particular column Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Grouping: The GROUP BY and HAVING Clauses (cont’d.) ▪ HAVING clause ▪ Provides a condition on the summary information Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example Count the number of distinct salary values in the database. select count(distinct salary) from employee; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example Retrieve the total number of employees in the company. select count(*) from employee; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example Retrieve the total number of employees in the Research department. select count(*) from employee,department where dno=dnumber and dname='Research'; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example Retrieve the total number of managers in the Research department. select count(*) from employee,department where Ssn=Mgr_ssn and dname='Research'; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example Retrieve the names of all employees who have two or more dependents. Copyright © 2011 Ramez Elmasri and Shamkant Navathe Retrieve the names of all employees who have two or more dependents. select lname, fname from employee where (select count(*) from dependent where ssn=essn) >=2; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example For each department, retrieve the department number, the number of employees in the department, and their average salary Copyright © 2011 Ramez Elmasri and Shamkant Navathe For each department, retrieve the department number, the number of employees in the department, and their average salary select dno, count(*), avg(salary) from employee group by dno; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example For each project, retrieve the project number, the project name, and the number of employees who work on that project. Copyright © 2011 Ramez Elmasri and Shamkant Navathe For each project, retrieve the project number, the project name, and the number of employees who work on that project. select pnumber, pname, count(*) from project, works_on where pnumber = pno group by pnumber; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example For each project, retrieve the project number, the project name, and the number of employees from department 5 who work on the project. select pnumber,pname,count(*) from project,works_on,employee where pnumber=pno and ssn=essn and dno=5 group by pnumber; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example Find the sum of the salaries of all employees of the Research department, as well as the maximum salary, the minimum salary, and the average salary in this department. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Discussion and Summary of SQL Queries Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project. select pnumber, pname, count(*) from project, works_on where pnumber = pno group by pnumber having count(*)>2; Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000. Copyright © 2011 Ramez Elmasri and Shamkant Navathe  Example For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000. select dnumber, count(*) from department, employee where dnumber=dno and salary>40000 and dno in (select dno from employee group by dno having count(*)>5) Copyright © 2011 Ramez Elmasri and Shamkant Navathe