drinker Sellsbeer from Frequents Sells where Frequentsbar Sellsbar as t on

Drinker sellsbeer from frequents sells where

This preview shows page 107 - 121 out of 167 pages.

(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
Image of page 107
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
Image of page 108
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
Image of page 109
Agenda SQL DML (Data Manipulation Language) – SQL query – Relations as bags – Grouping and aggregation – Database modification SQL DDL (Data Definition Language) – Define schemas 110
Image of page 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.
Image of page 111
112 Example: Aggregation From Sells(bar, beer, price), find the average price of Bud: SELECT AVG(price) FROM Sells WHERE beer = 'Bud';
Image of page 112
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';
Image of page 113
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.
Image of page 114
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'
Image of page 115
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.
Image of page 116
117 Example: Grouping From Sells(bar, beer, price), find the average price for each beer: SELECT beer, AVG(price) FROM Sells GROUP BY beer;
Image of page 117
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;
Image of page 118
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.
Image of page 119
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.
Image of page 120
Image of page 121

You've reached the end of your free preview.

Want to read all 167 pages?

  • Fall '14
  • From Sells

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture