More SQL

1. More SQL Todd Bacastow IST 210: Organization of Data

2. Simple Join Consider: Likes (drinker, beer) Sells (bar, beer, price) SELECT drinker, bar FROM Likes, Sells WHERE Likes.beer=Sells.beer; • What’s happening here????

3. Examples Employees: Orders:

4. Who has ordered a product, and what did they order? Result

5. Who ordered a printer? Result

6. Other ‘Joins’ in SQL2 • R NATURAL JOIN S • R CROSS JOIN S • R JOIN S ON condition • R OUTER JOIN S • R INNER JOIN S

7. Alternative JOIN Constructs • SQL2 provides alternative ways to specify joins: SELECT * FROM Likes.beer JOIN Sells.beer ON Likes.beer= Sells.beer SELECT * FROM Likes JOIN Sells USING beer SELECT * FROM Likes NATURAL JOIN Sells • The first produces a table with two identical beer columns, remaining two produce table with single beer column.

8. Outer Join • Outerjoin = natural join with dangling tuples padded with NULLs and included in the result • A tuple is dangling if it doesn’t join with any other tuple = OUTERJOIN

9. Modifiers of OUTER JOIN • Optional NATURAL in front • Optional ON condition at end • Optional LEFT, RIGHT, FULL before OUTER • LEFT = Pad dangling tuples of R ONLY • RIGHT = Pad dangling tuples of S ONLY

10. Inner Join • The INNER JOIN returns all rows from both tables where there is a match. If there are rows in Employees that do not have matches in Orders, those rows will not be listed. • EXAMPLE: Who has ordered a product, and what did they order? Result

11. Calculated Fields • Query Produce a list of monthly salaries for all staff, showing the staff number, the first and last names, and the salary details. SELECT sno, fname, lname, salary/12 FROM staff; Monthly_salary To name column, use AS clause: • SELECT sno, fname, lname, salary/12 AS monthly_salary • FROM staff;

12. Range Search Condition - BETWEEN Query: List all staff with a salary between 20,000 and 30,000. SELECT sno, fname, lname, position, salary FROM staff WHERE salary BETWEEN 20000 AND 30000;

13. Range Search Condition (Cont…) • BETWEEN test includes the endpoints of range. Equivalent to SELECT sno, fname, lname, position, salary FROM staff WHERE salary>=20000 AND salary <= 30000; • BETWEEN does not add much to SQL's expressive power • Also a negated version NOT BETWEEN.

14. Set Membership - IN / NOT IN • Query: List all Managers and Deputy Managers. SELECT sno, fname, lname, position FROM staff WHERE position IN ('Manager', 'Deputy');

15. Pattern Matching - LIKE • SQL has two special pattern matching symbols: • %: sequence of zero or more characters; • _ (underscore): any single character. • LIKE '%Glasgow%' means a sequence of characters of any length containing 'Glasgow'. Query: Find all staff with the string 'Glasgow' in their address. SELECT sno, fname, lname, address, salary FROM staff WHERE address LIKE '%Glasgow%';

16. NULL Search Condition • NULL value can not be checked by comparison operators • Have to test for null explicitly using special keyword IS NULL • IS NOT NULL can test for non-null values. • Example query: List details of all viewings on property PG4 where a comment has not been supplied. SELECT rno, date FROM viewing WHERE pno = 'PG4' AND comment IS NULL;

17. ORDER BY • Example Query: List salaries for all staff, arranged in descending order of salary. SELECT sno, fname, lname, salary FROM staff ORDER BY salary DESC; • When no (ASC/DESC) is indicated, default order is ascending (ASC). • Can sort on multiple columns. SELECT pno, type, rooms, rent FROM property_for_rent ORDER BY type, rent DESC;

18. GROUP BY and HAVING • Use GROUP BY clause to get sub-totals. • SELECT and GROUP BY closely integrated: each item in SELECT list must be single-valued per group, and SELECT clause may only contain: • Column names. • Aggregate functions. • Constants. • An expression involving combinations of the above. • If WHERE is used with GROUP BY, WHERE is applied first, then groups are formed from remaining rows satisfying predicate. • Considers two nulls to be equal for purposes of GROUP BY.

19. Use of GROUP BY Example: Find number of staff in each branch and their total salaries. SELECT bno, COUNT(sno) AS count, SUM(salary) AS sum FROM staff GROUP BY bno ORDER BY bno; 59

20. Restricted Grouping - HAVING • HAVING clause is designed for use with GROUP BY clause to restrict groups that appear in final result table. • HAVING filters groups. • Column names in HAVING clause must also appear in the GROUP BY list or be contained within an aggregate function. • Example: For each branch with more than 1 member of staff, find number of staff in each branch and sum of their salaries. SELECT bno, COUNT(sno) AS count, SUM(salary) AS sum FROM staff GROUP BY bno HAVING COUNT(sno) > 1 ORDER BY bno;

21. Subquery with Aggregate • Example: List all staff whose salary is greater than the average salary. SELECT sno, fname, lname, position, salary FROM staff WHERE salary > (SELECT avg(salary) FROM staff); • Cannot write 'WHERE salary > avg(salary)' • Instead, use subquery to find average salary (17000), and then use outer SELECT to find those staff with salary greater than 17000 • Subquery SELECT list must consist of a single column name or expression, except for subqueries that use EXISTS.

22. ANY/SOME and ALL • If subquery preceded by ALL, condition will only be true if it is satisfied by all values produced by subquery. • If subquery preceded by ANY, condition will be true if it is satisfied by any values produced by subquery. • If subquery is empty, ALL returns true, ANY returns false.

23. Example of ANY and ALL • Example1: Find staff whose salary is larger than salary of at least 1 member of staff at branch B3. SELECT sno, fname, lname, position, salary FROM staff WHERE salary >ANY (SELECT salary FROM staff WHERE bno = 'B3'); • Example 2: Find staff whose salary is larger than salary of every member of staff at branch B3. SELECT sno, fname, lname, position, salary FROM staff WHERE salary > ALL (SELECT salary FROM staff WHERE bno = 'B3');

24. Sorting a join • Consider: For each branch, list names of staff who manage properties. SELECT s.bno, s.sno, fname, lname, pno FROM staff s, property_for_rent p WHERE s.sno = p.sno ORDER BY s.bno, s.sno, pno;