(select Frequents.drinker, Sells.beer from Frequents, Sells where Frequents.bar = Sells.bar) as t on l.drinker = t.drinker and l.beer = t.beer
Another Example 108 Select t.* from Likes l right outer join (select Frequents.drinker, Sells.beer from Frequents, Sells where Frequents.bar = Sells.bar) as t on l.drinker = t.drinker and l.beer = t.beer Where l.drinker is null
Full outer join • MySQL does not support full outer join • Alternative: 109 A B 1 2 2 3 A C 1 3 1 3 3 4 3 5 join Right dangling R S Select A, B, C From R natural left outer join S where C is null Union all Select A, B, C From R natural right outer join S; Left dangling
Agenda • SQL DML (Data Manipulation Language) – SQL query – Relations as bags – Grouping and aggregation – Database modification • SQL DDL (Data Definition Language) – Define schemas 110
111 Aggregations • SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column. • Also, COUNT(*) counts the number of tuples.
112 Example: Aggregation • From Sells(bar, beer, price), find the average price of Bud: SELECT AVG(price) FROM Sells WHERE beer = 'Bud';
113 Eliminating Duplicates in an Aggregation • DISTINCT inside an aggregation causes duplicates to be eliminated before the aggregation. • Example: find the number of different prices charged for Bud: SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = 'Bud';
114 NULL's Ignored in Aggregation • NULL never contributes to a sum, average, or count of a specific column (e.g., count(price)), and can never be the minimum or maximum of a column (unless the column has only null values). • If there are no non-NULL values in a column, then the result of the aggregation is NULL.
115 Example: Effect of NULL's The number of bars that sell Bud at a known price. SELECT count(price) FROM Sells WHERE beer = 'Bud'; SELECT count(*) FROM Sells WHERE beer = 'Bud'; The number of bars that sell 'Bud'
116 Grouping • We may follow a SELECT-FROM-WHERE expression by GROUP BY and a list of attributes. • The relation that results from the SELECT- FROM-WHERE is grouped according to the values of all those attributes, and any aggregation is applied only within each group.
117 Example: Grouping • From Sells(bar, beer, price), find the average price for each beer: SELECT beer, AVG(price) FROM Sells GROUP BY beer;
118 Example: Grouping • From Sells(bar, beer, price) and Frequents(drinker, bar), find for each drinker the average price of Bud at the bars they frequent: SELECT drinker, AVG(price) FROM Frequents, Sells WHERE beer = 'Bud' AND Frequents.bar = Sells.bar GROUP BY drinker;
119 Restriction on SELECT Lists With Aggregation • If any aggregation is used, then each element of the SELECT list must be either: 1. Aggregated, or 2. An attribute on the GROUP BY list.
120 Illegal Query Example • You might think you could find the bar that sells Bud the cheapest by: SELECT bar, MIN(price) FROM Sells WHERE beer = 'Bud'; • But this query is illegal in SQL.
You've reached the end of your free preview.
Want to read all 167 pages?
- Fall '14
- From Sells