Database Systems SQL Queries and Aggregations Guide
Learn about SQL basic query statements, aggregations like SUM, AVG, COUNT, MIN, and MAX, along with examples and techniques for eliminating duplicates in aggregations using the DISTINCT keyword. Dive into the world of database systems with practical insights and visual aids.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
CPSC-310 Database Systems Professor Jianer Chen Room 315C HRBB Lecture #14 1
Our Running Example Our SQL queries will be based on the following database schema (Underline indicates key attributes) Beers(name, manf) Bars(name, addr, license) Drinkers(name, addr, phone) Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar)
SQL Basic Query Statement: SELECT attributes FROM tables WHERE conditions
SQL Basic Query Statement: SELECT attributes FROM tables WHERE conditions Other structures: -- subqueries -- set operations -- joins
Aggregations SUM, AVG, COUNT, MIN, and MAX can be applied to a column in a SELECT clause to produce that aggregation on the column.
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.
Example: Aggregation From Sells(bar, beer, price), find the average price of Bud: SELECT AVG(price) FROM Sells WHERE beer = Bud ;
Sells(bar, beer, price) Eliminating Duplicates in Aggregation
Sells(bar, beer, price) Eliminating Duplicates in Aggregation Use DISTINCT inside an aggregation.
Sells(bar, beer, price) Eliminating Duplicates in Aggregation Use DISTINCT inside an aggregation. Example: find the number of different prices charged for Bud:
Sells(bar, beer, price) Eliminating Duplicates in Aggregation Use DISTINCT inside an aggregation. Example: find the number of different prices charged for Bud: SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = Bud ;
NULL is Ignored in Aggregation NULL never contributes to a sum, average, or count, and can never be the minimum or maximum of a column.
NULL is Ignored in Aggregation NULL never contributes to a sum, average, or count, and can never be the minimum or maximum of a column. But if there are no non-NULL values in a column, then the result of the aggregation is NULL.
Sells(bar, beer, price) Example: Effect of NULL s SELECT count(*) FROM Sells WHERE beer = Bud ; SELECT count(price) FROM Sells WHERE beer = Bud ;
Sells(bar, beer, price) Example: Effect of NULL s SELECT count(*) FROM Sells WHERE beer = Bud ; The number of bars that sell Bud. SELECT count(price) FROM Sells WHERE beer = Bud ;
Sells(bar, beer, price) Example: Effect of NULL s SELECT count(*) FROM Sells WHERE beer = Bud ; The number of bars that sell Bud. SELECT count(price) FROM Sells WHERE beer = Bud ; The number of bars that sell Bud at a known price.
Grouping We may follow a SELECT-FROM- WHERE expression by GROUP BY and a list of attributes.
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.
Example: Grouping From Sells(bar, beer, price), find the average price for each beer:
Example: Grouping From Sells(bar, beer, price), find the average price for each beer: SELECT beer, AVG(price) FROM Sells GROUP BY beer;
Example: Grouping From Sells(bar, beer, price), find the average price for each beer: SELECT beer, AVG(price) FROM Sells GROUP BY beer; Output one tuple for each group
Sells(bar, beer, price) Frequents(drinker, bar) Example: Grouping From Sells and Frequents, find for each drinker the average price of Bud at the bars they frequent: 22
Sells(bar, beer, price) Frequents(drinker, bar) Example: Grouping From Sells and Frequents, 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; 23
Sells(bar, beer, price) Frequents(drinker, bar) Example: Grouping From Sells and Frequents, find for each drinker the average price of Bud at the bars they frequent: compute drinker-bar- price for Bud tuples first, then group by drinker. SELECT drinker, AVG(price) FROM Frequents, Sells WHERE beer = Bud AND Frequents.bar = Sells.bar GROUP BY drinker; 24
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.
Sells(bar, beer, price) Illegal Query Example You might think you could find the bar that sells Bud the cheapest by: SELECT SELECT bar, bar, MIN MIN(price) FROM FROM Sells Sells WHERE WHERE beer = Bud ; beer = Bud ; But this query is illegal in SQL. (price)
HAVING Clauses HAVING <condition> may follow a GROUP BY clause.
HAVING Clauses HAVING <condition> may follow a GROUP BY clause. If so, the condition applies to each group, and groups not satisfying the condition are eliminated.
HAVING Clauses HAVING <condition> may follow a GROUP BY clause. If so, the condition applies to each group, and groups not satisfying the condition are eliminated.
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s.
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. SELECT beer, AVG(price) FROM Sells GROUP BY beer
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 (SELECT name FROM Beers WHERE manf = Pete s ); at least 3 bars appear in the beer group beer IN
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = Pete s ); at least 3 bars appear in the beer group
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = Pete s ); at least 3 bars appear in the beer group
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = Pete s ); beers made by Pete s at least 3 bars appear in the beer group
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. group tuples (bar, beer, price) in Sells in terms of beer SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = Pete s ); beers made by Pete s at least 3 bars appear in the beer group the beer is made by Pete s
Sells(bar, beer, price) Beers(name, manf) Example. From Sells and Beers, find the average price of those beers that are either served in at least three bars or are manufactured by Pete s. SELECT beer, AVG(price) FROM Sells GROUP BY beer HAVING COUNT(bar) >= 3 OR beer IN (SELECT name FROM Beers WHERE manf = Pete s );
Requirements on HAVING Conditions These conditions may refer to any relation or tuple-variable in the FROM clause.
Requirements on HAVING Conditions These conditions may refer to any relation or tuple-variable in the FROM clause. They may refer to attributes of those relations, as long as the attribute makes sense within a group; i.e., it is either: 1. A grouping attribute, or 2. Aggregated.
Requirements on HAVING Conditions It is easier to understand this from an implementation viewpoint: SELECT FROM WHERE GROUP BY HAVING
Requirements on HAVING Conditions It is easier to understand this from an implementation viewpoint: SELECT FROM WHERE GROUP BY HAVING step 1, input
Requirements on HAVING Conditions It is easier to understand this from an implementation viewpoint: SELECT FROM WHERE GROUP BY HAVING step 1, input step 2, pick the proper tuples
Requirements on HAVING Conditions It is easier to understand this from an implementation viewpoint: SELECT FROM WHERE GROUP BY HAVING step 1, input step 2, pick the proper tuples step 3, group the picked tuples
Requirements on HAVING Conditions It is easier to understand this from an implementation viewpoint: SELECT FROM WHERE GROUP BY HAVING step 4, pick the proper groups step 1, input step 2, pick the proper tuples step 3, group the picked tuples
Requirements on HAVING Conditions It is easier to understand this from an implementation viewpoint: SELECT FROM WHERE GROUP BY HAVING step 4, pick the proper groups step 5, compute the output step 1, input step 2, pick the proper tuples step 3, group the picked tuples
Database Modifications A modification command does not return a result (as a query does), but changes the database in some way.
Database Modifications A modification command does not return a result (as a query does), but changes the database in some way. Three kinds of modifications: 1. Insert a tuple or tuples. 2. Delete a tuple or tuples. 3. Update the value(s) of an existing tuple or tuples.
