1 / 50

CSE 480: Database Systems

CSE 480: Database Systems. Lecture 12: SQL (Nested queries and Aggregate functions). NESTED QUERIES. A nested query is specified within the WHERE-clause of the outer query. NESTED QUERIES. Query : Retrieve the name and address of employees who work for the 'Research' department .

hnelson
Télécharger la présentation

CSE 480: Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CSE 480: Database Systems • Lecture 12: SQL (Nested queries and Aggregate functions)

  2. NESTED QUERIES A nested query is specified within the WHERE-clause of the outer query

  3. NESTED QUERIES Query: Retrieve the name and address of employees who work for the 'Research' department • SELECT Fname, Lname, AddressFROM Employee, DepartmentWHERE Dname='Research' AND Dnumber=Dno; • (Non-nested query approach)

  4. NESTED QUERIES Query: Retrieve the name and address of employees who work for the 'Research' department • SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEEWHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research' ); • (Nested query approach)

  5. Example Query: List the names of projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project

  6. NESTED QUERIES Query: List the names of projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project (SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith') UNION (SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND NAME='Smith'); Non-nested query approach

  7. NESTED QUERIES Query: List the names of projects that involve an employee whose last name is 'Smith' as a worker or as a manager of the department that controls the project SELECT PNAMEFROM PROJECTWHERE 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 NAME='Smith') Nested query approach

  8. ANY, ALL, SOME v = ANY W (e.g., Id =ANY (1,2,3)) evaluates to TRUE if v equals to one of the elements in W v = SOME W (e.g., Id =SOME (1,2,3)) evaluates to TRUE if v equals to some elements in W v > ALL W (e.g., Age >ALL (23,19,34)) evaluates to TRUE if v is greater than all the elements in W v < ALL W (e.g., Age <ALL (23,19,34)) evaluates to TRUE if v is less than all the elements in W

  9. NESTED QUERIES Query: List the names of employees whose salary is greater than the salary of all managers SELECT FNAME, LNAMEFROM EMPLOYEEWHERE SALARY > ALL (SELECT SALARY FROM DEPARTMENT,EMPLOYEE WHERE MGRSSN=SSN);

  10. THE EXISTS FUNCTION EXISTS is used to check whether the result of a nested query is empty (contains no tuples) SELECT FROM WHEREEXISTS V WHERE-clause evaluates to TRUE if V is not an empty table and FALSE otherwise

  11. THE EXISTS FUNCTION This query will return the names of students with GPA > 3.0

  12. THE EXISTS FUNCTION Query: Retrieve the name of each employee who has a dependent with the same name as the employee’s first name. SELEC • SELECT FNAME, LNAMEFROM EMPLOYEEWHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN AND FNAME=DEPENDENT_NAME); Correlated nested query

  13. THE EXISTS FUNCTION Query: Retrieve the names of employees who have no dependents • SELECT FNAME, LNAME • FROM EMPLOYEE • WHERE NOT EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN); Correlated nested query because it involves attribute of outer query

  14. Adam Course Transcript CS101 CS 1234 CS101 2.5 2345 CS234 3.5 CS234 CS CS234 4.0 1234 Division Query Student (Id, Name)Course (CrsCode, Dept)Transcript (StudId, CrsCode, Grade) Query: Find the names of students who have taken every course offered by the CS department Student 1234 Adam 2345 Bill 3456 Cathy 4567 Didi 5678 Eva Result

  15. Division Query in SQL Student (Id, Name)Course (CrsCode, CrsName, Dept)Transcript (StudId, CrsCode, Semester, Grade) Query: Find the names of students who have taken every course offered by the CS department SELECT NameFROM Student WHERE ? Student has taken ALL the CS courses

  16. Division Query in SQL Student (Id, Name)Course (CrsCode, CrsName, Dept)Transcript (StudId, CrsCode, Semester, Grade) Query: Find the names of students who have taken every course offered by the CS department SELECT NameFROM StudentWHERE EXISTS (SELECT * FROM Transcript, Course WHERE Transcript.StudId = Student.Id AND Transcript.CrsCode = Course.CrsCode AND Course.Dept = ‘CS’) This will return names of students who have taken at least 1 CS course

  17. Division Query in SQL Student (Id, Name)Course (CrsCode, CrsName, Dept)Transcript (StudId, CrsCode, Semester, Grade) Query: Find the names of students who have taken every course offered by the CS department SELECT NameFROM Student WHERE ? Student has taken ALL the CS courses

  18. Division Query in SQL Student (Id, Name)Course (CrsCode, CrsName, Dept)Transcript (StudId, CrsCode, Semester, Grade) Query: Find the names of students who have taken every course offered by the CS department SELECT NameFROM Student WHERE ( ? ) Equivalent to saying “there are no CS courses the student has not taken”

  19. Division Query in SQL Student (Id, Name)Course (CrsCode, CrsName, Dept)Transcript (StudId, CrsCode, Semester, Grade) Query: Find the names of students who have taken every course offered by the CS department SELECT NameFROM Student WHERE NOT EXISTS ( ?) A CS course the student has not taken

  20. (Student, course) combination does not exist in Transcript Division Query in SQL Student (Id, Name)Course (CrsCode, CrsName, Dept)Transcript (StudId, CrsCode, Semester, Grade) Query: Find the names of students who have taken every course offered by the CS department SELECT NameFROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE Dept = ‘CS’ AND ?) Student has not taken the CS course

  21. Division Query in SQL Student (Id, Name)Course (CrsCode, CrsName, Dept)Transcript (StudId, CrsCode, Semester, Grade) Query: Find the names of students who have taken every course offered by the CS department SELECT NameFROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE Dept = ‘CS’ AND NOT EXISTS ( SELECT * FROM Transcript WHERE Transcript.StudId = Student.Id AND Transcript.Crscode = Course.Crscode) )

  22. Another Possible Solution Student (Id, Name)Course (CrsCode, CrsName, Dept)Transcript (StudId, CrsCode, Semester, Grade) Query: Find the names of students who have taken every course offered by the CS department SELECT NameFROM Student WHERE NOT EXISTS ( SELECT * FROM Course WHERE Dept = ‘CS’ AND CrsCode NOT IN ( SELECT CrsCode FROM Transcript WHERE Transcript.StudId = Student.Id))

  23. Division Query Retrieve the names of employees who work on every project controlled by the “Research” department

  24. Example: Division Query Retrieve the names of employees who work on every project controlled by the “Research” department SELECT DISTINCT E.Lname, E.Fname FROM EMPLOYEE E WHERE NOT EXISTS ( A project controlled by the Research department in which the employee had NOT worked on )

  25. Example: Division Query Retrieve the names of employees who work on every project controlled by the “Research” department SELECT DISTINCT E.Lname, E.Fname FROM EMPLOYEE E WHERE NOT EXISTS ( SELECT P.Pnumber FROM DEPARTMENT D, PROJECT P WHERE D.Dnumber = P.Dnum AND D.Dname=‘Research’ AND NOT EXISTS ( (employee,project) combination in the Works_On table ) )

  26. Example: Division Query Retrieve the names of employees who work on every project controlled by the “Research” department SELECT DISTINCT E.Lname, E.Fname FROM EMPLOYEE E WHERE NOT EXISTS (SELECT P.Pnumber FROM DEPARTMENT D, PROJECT P WHERE D.Dnumber = P.Dnum AND D.Dname=‘Research’ AND NOT EXISTS ( SELECT * FROM WORKS_ON W WHERE W.ESSN=E.SSN AND P.Pnumber=W.Pno))

  27. Exercise • Find the names of projects worked on by every employee in the Research department • Exclude the projects worked on only by a few but not all employees in the Research department • Find the names of employees who work for the Research department but do not work on all the projects controlled by the Research department

  28. Aggregate Functions • Used to compute summary statistics for a group of tuples in a table • Include COUNT, SUM, MAX, MIN, and AVG

  29. Aggregate Functions • Need to be careful when dealing with NULL values

  30. Aggregate Functions Query: Find the sum of the salaries, maximum salary, minimum salary, and average salary of employees in the ‘Research’ department

  31. Aggregate Functions Query: Count the number of employees who work for the 'Research' department

  32. Aggregate Functions Query: Count the number of employees in department number 3 who have worked on at least one project overestimate

  33. Aggregate Functions • Query: Count the number of employees in Department number 3 who have worked on at least one project

  34. Aggregate Functions Cannot use aggregate function directly in the WHERE clause Query: Find the id of employees who have two or more dependents

  35. Aggregate Functions Query: Find the name of employees who have two or more dependents This is ok

  36. Grouping Clause Suppose we want to apply the aggregate functions to subgroups of tuples in a relation Each subgroup consists of the set of tuples that have the same value for the grouping attribute(s) SQL has a GROUP BY-clause for specifying the grouping attributes

  37. Grouping Clause Example Query: 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; The EMPLOYEE tuples are divided into groups, each group having the same value for the grouping attribute DNO The COUNT and AVG functions are applied to each such group of tuples separately The SELECT-clause includes only the grouping attribute and the aggregate functions to be applied on each group of tuples

  38. Effect of null value Grouping Clause Example

  39. Grouping Clause Example Query: For each project, retrieve the project number, project name, and the number of employees who work on that project. In this case, the grouping and aggregate functions are applied after joining the two relations

  40. Grouping Clause Example Query: For each project, retrieve the project number, project name, and number of employees from department 3 who work on the project

  41. HAVING Clause • The HAVING-clause is used to retrieve only groups whose values of the aggregate functions satisfy certain condition

  42. HAVING Clause Example Query: For each project that has more than one employees work, retrieve the project number, project name, and the number of employees who work on that project.

  43. HAVING Clause Example Query: For each project that has more than one employees work on it, retrieve the project number, project name, and the number of employees who work on that project.

  44. HAVING Clause Example Query: For each department that has more than 1 employee, retrieve the department name and the number of employees who are making more than $50,000 • Answer should be: (Payroll, 1) and (Research, 1)

  45. HAVING Clause WRONG! This query looks for departments that have more than 1 employee making more than $50,000 • Query: For each department that has more than 1 employee, retrieve the department name and the number of employees who are making more than $50,000

  46. HAVING Clause • Query: For each department that has more than 1 employee, retrieve the department name and the number of employees who are making more than $50,000

  47. ORDER BY Clause Sort tuples based on the values of some attribute(s) Query: Retrieve a list of employees and the projects they work on, ordered by their department name, and within each department, ordered alphabetically by last name and first name. SELECT Dname, Lname, Fname, Pname FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE Dnumber=Dno AND SSN=ESSN AND Pno=Pnumber ORDER BY Dname, Lname, Fname; The default order is in ascending order of values We can also specify the keyword DESC or ASC

  48. ORDER BY Clause Query: Retrieve a list of employees and the projects they work on, ordered by their department name, and within each department, ordered alphabetically by salary (in descending order) SELECT Dname, Lname, Fname, Pname FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT WHERE Dnumber=Dno AND SSN=ESSN AND Pno=Pnumber ORDER BY Dname ASC, Salary DESC;

  49. Summary of SQL Queries SELECT <attribute list>FROM <table list>[WHERE <condition>][GROUPBY <grouping attribute(s)>][HAVING <group condition>][ORDER BY <attribute list>] SELECT-clause lists the attributes/functions to be retrieved FROM-clause specifies all relations (or aliases) needed in the query but not those needed in nested queries WHERE-clause specifies the conditions for selection and join of tuples from the relations specified in the FROM-clause GROUP BY specifies grouping attributes HAVING specifies a condition for selection of groups ORDER BY specifies an order for displaying the result of a query A query is evaluated by first applying the WHERE-clause, then GROUP BY and HAVING, and finally the SELECT-clause

  50. Limit (in MySQL) Find the name and salary of the highest paid employee

More Related