Announcements

1. Announcements • Written Homework 1 due Nov 2 • See course web page • Exercises 5.12, 5.15, 6.17, 6.20, 6.22 (a,c,f only). • Today • continue with SQL (chapter 8) • SELECT

2. SELECT SELECT <attribute and function list> FROM <table list> WHERE <condition> GROUP BY <grouping attributes> HAVING <group conditions> ORDER BY <attribute list> optional clauses

3. SELECT SELECT STATEMENT single table many tables input to a SELECT statement is N tables output is a single table

4. SELECT-FROM • all SELECT statements must have SELECT and FROM clauses • SELECT a1, a2, ..., an FROM t1, t2, ..., tm • conceptually evaluation of SELECT-FROM • form cross product (t1 x t2 x ... x tm) • project out attributes (a1, a2, ..., an) from the CP

5. * in attribute list • a * in attribute list is shorthand for “all attributes” • SELECT * FROM employee; • above statement returns the entire employee table examples 1,2,3

6. WHERE • the WHERE condition used to limit which tuples of the m-way cross product are retained SELECT dname, dlocation FROM department, dept_locations WHERE department.dnumber = dept_locations.dnumber; # note = *not* ==

7. Conceptual processing of SELECT-FROM-WHERE SELECT a1, a2, ..., an FROM t1, t2, ..., tm WHERE cond • form cross product (t1 x t2 x ... x tm) • retain CP rows where condevaluates to ‘true’ • project (a1, a2, ..., an) from retained rows similar to a m-way join

8. Simple SQL Queries (contd.) • Query 2: For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate.

9. Simple SQL Queries (contd.) • Query 2: For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate. SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford'

10. Aliasing • Tables can be given short names to make queries easier to write (and to resolve ambiguity) SELECT dname, dlocation FROM department as D, dept_locations as DL WHERE D.dnumber = DL.dnumber;

11. ALIASES • Some queries need to refer to the same relation twice • In this case, aliases are given to the relation name • Query 8: For each employee, retrieve the employee's name, and the name of his or her immediate supervisor.Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE AS E, EMPLOYEE ASS WHERE E.SUPERSSN=S.SSN • In Q8, the alternate relation names E and S are called aliases or tuple variables for the EMPLOYEE relation • We can think of E and S as two different copies of EMPLOYEE; E represents employees in role of supervisees and S represents employees in role of supervisors

12. SELECT DISTINCT • the default behavior of select is to not eliminate duplicate rows in the result table • remove duplicate rows w/ SELECT DISTINCT SELECT DISTINCT dlocation FROM dept_locations; SELECT DISTINCT dlocation, dnumber FROM dept_locations; returns set of department locations returns entire dept_locations table

13. ORDER BY • ORDER BY clause causes returned tuples to be ordered by some SELECT attribute (or attributes) SELECT fname, lname, salary FROM employee ORDER BY salary; SELECT fname, lname, salary FROM employee ORDER BY salary DESC; returns results in descending order

14. ORDER BY # the ordering field can be a string # (lexical ordering is used) SELECT fname, lname, salary FROM employee ORDER BY lname; # ordering attr need not be in SELECT SELECT fname, lname FROM employee ORDER BY salary;

15. Aggregates • Like RA, SQL supports “aggregate functions” SELECT MAX(salary) FROM employee WHERE dno = 5; SELECT MIN(salary), MAX(salary), AVG(SALARY), COUNT(*) FROM employee;

16. Conceptual processing of SELECT-FROM-WHEREaggregates SELECT MAX(salary) FROM employee WHERE dno = 5; • form cross product (t1 x t2 x ... x tm) • retain CP rows where condevaluates to ‘true’ • apply aggregate functions in SELECT to column(s) of retained rows

17. mixing attributes and aggregates in SELECT clause? • you cannot mix aggregate functions and attributes in select clause (without GROUP BY) SELECT ssn, MAX(salary) FROM employee; not a legal SQL statement how do you write a query to return the SSN and salary the highest paid employee?

18. GROUP BY • The GROUP BY clause is used with aggregate functions to define groups of tuples to which to apply the aggregate functions SELECT sex, MIN(salary), MAX(salary), AVG(SALARY), COUNT(*) FROM employee GROUP BY sex;

19. GROUPING (contd.) • Query 21: For each project, retrieve the project number, project name, and the number of employees who work on that project. Q21: SELECT PNUMBER, PNAME, COUNT (*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME • In this case, the grouping and functions are applied after the joining of the two relations

20. HAVING • HAVING is used w/ GROUP BY to impose conditions on groups present in result

21. THE HAVING-CLAUSE (contd.) • Query 22: For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project. Q22: SELECT PNUMBER, PNAME, COUNT(*) FROM PROJECT, WORKS_ON WHERE PNUMBER=PNO GROUP BY PNUMBER, PNAME HAVING COUNT (*) > 2