SQL - Part 2

1. SQL - Part 2 Much of the material presented in these slides was developed by Dr. Ramon Lawrence at the University of Iowa

2. SELECT Statement Overview SELECT <list of column expressions> FROM <list of tables and join operations> WHERE <list of logical expressions for rows> GROUP BY <list of grouping columns> HAVING <list of logical expressions for groups> ORDER BY <list of sorting specifications> • Expression: combination of columns, constants, operators, and functions

3. Example Relations • Relations: Emp (eno, ename, bdate, title, salary, supereno, dno) Proj (pno, pname, budget, dno) Dept (dno, dname, mgreno) WorksOn (eno, pno, resp, hours) • Foreign keys: • Emp: Emp.supereno to Emp.eno, Emp.dno to Dept.dno • Proj: Proj.dno to Dept.dno • Dept: Dept.mgreno to Emp.eno • WorksOn: WorksOn.eno to Emp.eno, WorksOn.pno to Proj.pno

4. Example Relation Instances

5. But First Join Revisited • Cross Product Style: • How many columns in the output table? SELECT * FROM emp, dept WHERE emp.dno=dept.dno; • Natural Join Style: • How many columns in the output table? SELECT * FROM emp NATURAL JOIN dept; SELECT * FROM emp INNER JOIN dept USING (dno); SELECT * FROM emp INNER JOIN dept ON emp.dno = dept.dno; • Alternative SQL92 styles: • How many columns in each output table?

6. More Join Practice emp (eno, ename, bdate, title, salary, supereno, dno) proj (pno, pname, budget, dno) dept (dno, dname, mgreno) workson (eno, pno, resp, hours) • Relational database schema: • Return a list of all department names, the names of the projects of that department, and the name of the manager of each department. • Return the names of all projects and the names of the employees who have worked on each project. • Return the names of all employees who are supervisors.

7. Ordering Result Data • The query result returned is not ordered on any attribute by default. We can order the data using the ORDER BY clause: SELECT ename, salary, bdate FROM emp WHERE salary > 30000 ORDER BY salary DESC, ename ASC; • 'ASC' sorts the data in ascending order, and 'DESC' sorts it in descending order. The default is 'ASC'. • The order of sorted attributes is significant. The first attribute specified is sorted on first, then the second attribute is used to break any ties, etc. • NULL is normally treated as less than all non-null values.

8. Aggregate Queries and Functions • Several queries cannot be answered using the simple form of the SELECT statement. These queries require a summary calculation to be performed. Examples: • What is the maximum employee salary? • What is the total number of hours worked on a project? • How many employees are there in department 'D1'? • To answer these queries requires the use of aggregate functions. These functions operate on a single column of a table and return a single value.

9. Aggregate Functions • The five basic aggregate functions are: • COUNT - returns the # of values in a column • SUM - returns the sum of the values in a column • AVG - returns the average of the values in a column • MIN - returns the smallest value in a column • MAX - returns the largest value in a column • Notes: • COUNT, MAX, and MIN apply to all types of fields, whereas SUM and AVG apply to only numeric fields. • Except for COUNT(*) all functions ignore nulls. COUNT(*) returns the number of rows in the table. • Use DISTINCT to eliminate duplicates.

10. Aggregate Function Example • Return the number of employees and their average salary. SELECT COUNT(eno) AS numEmp, AVG(salary) AS avgSalary FROM emp;

11. GROUP BY Clause • Aggregate functions are often most useful when combined with the GROUP BY clause. The GROUP BY clause groups the tuples based on the values of the attributes specified. • When used in combination with aggregation functions, the result is a table where each tuple consists of unique values for the group by attributes and the result of the aggregate functions applied to the tuples of that group.

12. GROUP BY Example • For each employee title, return the number of employees with that title, and the minimum, maximum, and average salary. SELECT title, COUNT(eno) AS numEmp, MIN(salary) as minSal, MAX(salary) as maxSal, AVG(salary) AS avgSal FROM emp GROUP BY title; Result:

13. GROUP BY Clause Rules • There are a few rules for using the GROUP BY clause: 1) A column name cannot appear in the SELECT part of the query unless it is part of an aggregate function or in the list of group by attributes. • Note that the reverse is true: a column can be in the GROUP BY without being in the SELECT part. 2) Any WHERE conditions are applied before the GROUP BY and aggregate functions are calculated.

14. HAVING Clause • The HAVING clause is applied AFTER the GROUP BY clause and aggregate functions are calculated. • It is used to filter out entire groups that do not match certain criteria.

15. HAVING Example • Return the title and number of employees of that title where the number of employees of the title is at least 2. SELECT title, COUNT(eno) AS numEmp FROM emp GROUP BY title HAVING COUNT(eno) >= 2; Result:

16. GROUP BY/HAVING Example • For employees born after December 1, 1965, return the average salary by department where the average is > 40,000. SELECT dname, AVG(salary) AS avgSal FROM emp NATURAL JOIN dept WHERE emp.bdate > DATE ’1965-12-01' GROUP BY dname HAVING AVG(salary) > 40000; Step #1: Perform Join and Filter in WHERE clause

17. GROUP BY/HAVING Example (2) Step #2: GROUP BY on dname Step #3: Calculate aggregate functions Step #4: Filter groups using HAVING clause

18. GROUP BY Examples • Return the average budget per project: SELECT AVG(budget) FROM proj; • Return the average # of hours worked on each project: SELECT pno, AVG(hours) FROM workson GROUP BY pno; • Return the departments that have projects with at least 2 'EE's working on them: SELECT proj.dno, COUNT(*) FROM proj, workson, emp WHERE emp.title = 'EE' and workson.eno=emp.eno and workson.pno = proj.pno GROUP BY proj.dno HAVING COUNT(*) >=2;

19. Multi-Attribute Example • Return the employee number, department number and hours the employee worked per department where the hours is >= 10. SELECT W.eno, D.dno, SUM(hours) FROM workson AS W, dept AS D, proj AS P WHERE W.pno = P.pno and P.dno = D.dno GROUP BY W.eno, D.dno HAVING SUM(hours) >= 10; Result: Question: 1) How would you only return records for departments D2 and D3?

20. GROUP BY Practice Questions • Relational database schema: Emp (eno, ename, bdate, title, salary, supereno, dno) Proj (pno, pname, budget, dno) Dept (dno, dname, mgreno) WorksOn (eno, pno, resp, hours) • Return the highest salary of any employee. • For each project, return its name and the total number of hours employees have worked on it. • For each employee, return the total number of hours they have worked. • Calculate the average # of hours spent per project in each department.

21. Conceptual Evaluation Process

22. Conceptual Evaluation Lessons • Row operations before group operations • FROM and WHERE before GROUP BY and HAVING • Check row operations first • Grouping occurs only one time

23. Conceptual Evaluation Problem Relational database schema: Student(StdSSN, StdFirstName, StdLastName, StdCity, StdState, StdMajor, StdClass, StdGPA, StdZip) • Faculty(FacSSN, FacFirstName, FacLastName, FacCity, FacState, FacDept, FacRank, FacSalary) • Faculty_1(FacSSN, FacSupervisor, FacHireDate, FacZipCode) • Offering(offerNo, CourseNo, OffTerm, OffTerm, OffYear, OffLocation, OffTime, FacSSN,OffDays) • Course(CourseNo, CrsDesc, CrsUnits) • Enrollment(OfferNo, StdSSN, EnrGrade) • Example 15 from your text: List the number of offerings taught in 2006 by faculty rank and department. Exclude combinations of faculty rank and department with less than two offerings taught.

24. Conceptual Evaluation Problem (cont) • List the number of offerings taught in 2006 by faculty rank and department. Exclude combinations of faculty rank and department with less than two offerings taught. SELECT FacRank, FacDept, COUNT(*) AS NumOfferings FROM Faculty, Offering WHERE Offering.FacSSN = Faculty.FacSSN AND OffYear = 2006 GROUP BY FacRank, FacDept HAVING COUNT(*) > 1

25. Problem Statement Database Representation Database Language Statement Query Formulation Process

26. Critical Questions • What tables? • Columns in output • Conditions to test (including join conditions) • How to combine the tables? • Usually join PK to FK • More complex ways to combine • Individual rows or groups of rows? • Aggregate functions in output • Conditions with aggregate functions

27. Efficiency Considerations • Little concern for efficiency • Intelligent SQL compilers • Correct and non redundant solution • No extra tables • No unnecessary grouping • Use HAVING for group conditions only • Chapter 8 provides additional tips for avoiding inefficient SELECT statements