500 likes | 520 Views
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 .
E N D
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 • SELECT Fname, Lname, AddressFROM Employee, DepartmentWHERE Dname='Research' AND Dnumber=Dno; • (Non-nested query approach)
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)
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
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
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
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
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);
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
THE EXISTS FUNCTION This query will return the names of students with GPA > 3.0
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
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
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
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
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
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
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”
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
(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
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) )
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))
Division Query Retrieve the names of employees who work on every project controlled by the “Research” department
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 )
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 ) )
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))
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
Aggregate Functions • Used to compute summary statistics for a group of tuples in a table • Include COUNT, SUM, MAX, MIN, and AVG
Aggregate Functions • Need to be careful when dealing with NULL values
Aggregate Functions Query: Find the sum of the salaries, maximum salary, minimum salary, and average salary of employees in the ‘Research’ department
Aggregate Functions Query: Count the number of employees who work for the 'Research' department
Aggregate Functions Query: Count the number of employees in department number 3 who have worked on at least one project overestimate
Aggregate Functions • Query: Count the number of employees in Department number 3 who have worked on at least one project
Aggregate Functions Cannot use aggregate function directly in the WHERE clause Query: Find the id of employees who have two or more dependents
Aggregate Functions Query: Find the name of employees who have two or more dependents This is ok
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
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
Effect of null value Grouping Clause Example
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
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
HAVING Clause • The HAVING-clause is used to retrieve only groups whose values of the aggregate functions satisfy certain condition
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.
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.
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)
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
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
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
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;
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
Limit (in MySQL) Find the name and salary of the highest paid employee