Max Min Find highest status of any supplier

Max, Min • Find highest status of any supplier SELECT MAX(STATUS) FROM S • Find highest status of any supplier who supplies P 2 SELECT MAX(STATUS) FROM S WHERE S# IN (SELECT S# FROM SP WHERE P# = `P 2’) • Conceptual evaluation strategy same as before • First eliminate S# which don’t meet condition • Then do the aggregate operation: – SELECT MAX(STATUS) FROM S 1

Average • get the average weight SELECT AVG( WEIGHT) FROM P • Find P# whose weight is > average SELECT P# FROM P WHERE WEIGHT > ( SELECT AVG(WEIGHT) FROM P) • Is OK to compare WEIGHT to a table? • Is OK: the inner query returns a table with a single value (AVG(WEIGHT)) so makes sense to compare WEIGHT with that value. 2

Comparing value to a table • What about: SELECT P# FROM P WHERE WEIGHT > ( SELECT WEIGHT FROM P) • Is this OK? • No : can’t compare a single value WEIGHT to a table with multiple values 3

Aggregate Operator Eg • Which part has the largest weight. Consider: SELECT P# FROM P WHERE WEIGHT = MAX( WEIGHT) • Is this OK ? • Will not work: inner query has to be fully specified: DBMS will not understand MAX( WEIGHT) • Correct query ? SELECT P# FROM P WHERE WEIGHT = (SELECT MAX(WEIGHT ) FROM P); 4

Aggregate Operators Eg • Get name, weight of heaviest part: Is this OK? SELECT PNAME, MAX( WEIGHT) FROM P • Not OK. Why not ? • In SQL if in SELECT there is an aggregate op. , can only be aggregate ops. Why? • Which PNAME is to be selected ? – Correct query ? SELECT PNAME, WEIGHT FROM P WHERE WEIGHT = (SELECT MAX(WEIGHT ) FROM P); 5

Group By • For each part supplied, get the P# and the total quantity supplied. SELECT P#, SUM (QTY)AS (TQTY) FROM SP GROUP BY P# • Conceptual Eval. Strategy : – Split up the SP table by part P# – For each part, find the total qty: do this by applying the SUM operator only to the group 6

Access Group By Eg: Eg 22 • For each sales rep, list the rep number, the number of customers assigned to the rep, and the average balance of the rep’s customers. Group the records by rep number and order the records by rep number. SELECT Rep. Num, COUNT(*), AVG(Balance) FROM Customer GROUP BY Rep. Num ORDER BY Rep. Num; 7

Figures 3. 42 -3. 43: SQL Query to Group Records 8

Group By • For each part supplied, get the P# and the total quantity supplied by S 1, S 2, S 3 combined. • Group the parts, within each group find the total qty supplied by S 1, S 2, S 3. SELECT P#, SUM (QTY) AS TQTY FROM SP WHERE S# IN (`S 1’, `S 2’ , `S 3’ ) GROUP BY P# ; • Conceptual evaluation strategy: – First do the WHERE then do the GROUP BY – Then apply agg. op to each group separately 9

Having • Suppose we wanted to eliminate some groups – Like a where condition for groups • get P# of parts where total quantity supplied is greater than 500. SELECT P# FROM SP GROUP BY P# HAVING SUM(QTY) > 500; 10

Group, Having: Conc. Eval. Strategy SELECT FROM WHERE GROUP BY HAVING • • • target-list relation-list qualification grouping-list group-qualification Conceptual evaluation strategy: do in this order: Cross product of tables from relation-list Eliminate rows : don’t meet qualification Do grouping by grouping-list Eliminate groups: don’t meet group-qualification Pick target-list including aggregate operators. 11

Find age of the youngest sailor with age >= 18, for each rating with at least 2 such sailors SELECT S. rating, MIN (S. age) AS minage FROM Sailors S WHERE S. age >= 18 GROUP BY S. rating HAVING COUNT (*) > 1; Sailors instance: 12

Find age of the youngest sailor with age >= 18, for each rating with at least 2 such sailors 13

Group, Having: Conc. Eval. Strategy • Does it matter which of WHERE/HAVING applied first ? • Eg: Find the avg status of suppliers with status >= 15 for each city, for those cities having at least 2 such suppliers. SELECT CITY, AVG(STATUS) FROM S WHERE STATUS >= 15 GROUP BY CITY HAVING COUNT(*) >= 2 • Since WHERE applied first, S 2 is eliminated and PARIS will not appear (< 2 suppliers left) • If HAVING applied first, PARIS included (>= 2 suppliers), and then S 2 is eliminated 14

Access Having Eg: Eg 23 • For each sales rep with fewer than four customers list the rep number, the number of customers assigned to the rep, and the average balance of the rep’s customers. Rename these last two columns as Num. Customers and Average. Balance and order the records by rep number. SELECT Rep. Num, COUNT(*) AS Num. Customers , AVG(Balance) AS Average. Balance FROM Customer GROUP BY Rep. Num HAVING COUNT(*) < 4 ORDER BY Rep. Num; 15

Figures 3. 44 -3. 45: SQL Query to Restrict Groups 16

Access Having Eg: Eg 23 b • Change the previous query (eg 23) so only those customers with less than $10, 000 credit limit are counted, and do this only for reps with fewer than 3 such customers. SELECT Rep. Num, COUNT(*) AS Num. Customers , AVG(Balance) AS Average. Balance FROM Customer WHERE Credit. Limit < 10000 GROUP BY Rep. Num HAVING COUNT(*) < 3 ORDER BY Rep. Num; 17

Figures 3. 46 -3. 47: SQL Query with ‘WHERE’ and ‘HAVING’ Clauses 18

Group by, Having: rules Consider the following – is this OK ? SELECT S#, P#, SUM (QTY) FROM SP GROUP BY S# • What is wrong here ? • What value of P# would appear ? • Error: is not associated with any of the groups. SELECT column-names, aggregate ops. . . GROUP BY grouping-list • Each of the column-names has to appear in grouping-list 19

Group by, Having: rules • For each red part, find # suppliers supplying it – Consider the following – is this OK ? SELECT P. P#, COUNT (SP. S#) FROM P, SP WHERE P. P# = SP. P# GROUP BY P. P# HAVING P. COLOR = RED • Wrong : color is not a property of the group but of individual parts – What is the right way ? SELECT P. P#, COUNT (SP. S#) FROM P, SP WHERE P. P# = SP. P# AND P. COLOR = RED GROUP BY P. P# 20

Group by, Having: rules SELECT GROUP BY grouping-list HAVING group-qualification • Everything in group-qualification : – has to be an aggregate op or appear in grouping-list 21

Nested subquery in HAVING • Similar to nested subquery in WHERE. • Same query as before: find the avg status of suppliers with status >= 15 for each city, for those cities having at least two such suppliers. SELECT CITY, AVG(STATUS) FROM S FIRST WHERE STATUS >= 15 GROUP BY CITY HAVING (SELECT COUNT (*) FROM S SECOND WHERE SECOND. CITY = FIRST. CITY AND SECOND. STATUS >= 15) >= 2; • Suppose we want to change query to: cities having at least two suppliers. How to do ? • Drop the AND SECOND. STATUS >= 15 part 22

Find cities for which the avg status of suppliers based in that city is the biggest over all cities. SELECT CITY FROM S GROUP BY CITY HAVING AVG (STATUS) = (SELECT MAX (AVG (SECOND. STATUS )) FROM S SECOND GROUP BY SECOND. CITY) • This will not work – why? • MAX (AVG ()) is illegal: – no nested aggregate ops. Why? • Since AVG (SECOND. STATUS )) returns one value, can’t take MAX of one value 23

Nested subquery in From • Find cities for which the avg status of suppliers based in that city is the biggest over all cities. • Idea: get avg status for each city, store this info in a temporary table (via a nested subquery), and then find the city with the max avg. SELECT TEMP. CITY FROM (SELECT CITY, AVG (S. STATUS ) AS AVGSTAT FROM S GROUP BY S. CITY) AS TEMP WHERE TEMP. AVGSTAT = (SELECT MAX (TEMP. AVGSTAT) FROM TEMP) 24

Find those ratings for which the average is the minimum over all ratings • Basic idea ? • Save the avg ratings in a temp table • Find the minimum from this temp table • Find the ratings who have this minimum SELECT Temp. rating FROM (SELECT S. rating, AVG (S. age) AS avgage FROM Sailors S GROUP BY S. rating) AS Temp WHERE Temp. avgage = (SELECT MIN (Temp. avgage) FROM Temp); 25

Nested From in Access • Works in Access in non-standard way • Find the warehouse with the most parts SELECT WPcount. Warehouse FROM (SELECT Warehouse, COUNT (Part. Num) AS Numberof. Parts FROM Part GROUP BY Warehouse) AS WPcount WHERE WPcount. Numberof. Parts = (SELECT MAX (WPcount. Numberof. Parts) FROM WPcount); • This will not work because the WPCount table will not be found in the WHERE part. 26

Nested From in Access • This will work SELECT WPcount. Warehouse FROM (SELECT Warehouse, COUNT (Part. Num) AS Numberof. Parts FROM Part GROUP BY Warehouse) AS WPcount WHERE WPcount. Numberof. Parts = (SELECT MAX (WPcount. Numberof. Parts) FROM (SELECT Warehouse, COUNT (Part. Num) AS Numberof. Parts FROM Part GROUP BY Warehouse) AS WPcount ); 27

Access: Create a Table from a Query, Eg 31 SELECT * INTO Small. Cust 3 FROM Customer WHERE Credit. Limit <= 7500; • INTO clause used to save the results of a query into a new table – Specified before FROM and WHERE clauses • Small. Cust 3 table did not exist before INTO • Data added to the new table is separate – if change in Small. Cust 3, no change in Customer 28

Figures 3. 59 -3. 60: SQL Query to Create New Table 29

Access: Create a Table from a Query • Can query newly created table like any other table. SELECT Small. Cust 3. Customer. Num FROM Small. Cust 3; • Useful if have complex query: – Break up into two parts – Do 1 st part and store into table – Then do 2 nd part as query on stored table. • Can do same with views or saved query in Access 30

Make-Table : QBE version of INTO. Eg: 2_18 • Create new table with customer# and cname, and num, fname, lname of of customer’s sales rep. • Do as normal SELECT query and then choose Make-Table from Query. • SQL: SELECT Customer. Num, Customer. Name, Customer. Rep. Num, Rep. First. Name, Rep. Last. Name INTO Customer. Rep 2 FROM Rep INNER JOIN Customer ON Rep. Num = Customer. Rep. Num; 31

Figure 2. 37: Make-Table Query 32

Figure 2. 39: Make-Table Query (con’t. ) 33