Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
An introduction to sql queries, focusing on the principal concepts and examples using a beer schema. Topics include query structure, single-relation queries, expressions as values, joins, conditions, and null values.
Typology: Slides
1 / 20
This page cannot be seen from the preview
Don't miss anything!
SQL Queries
Principal form:
SELECT desired attributes FROM tuple variables | range over relations WHERE condition ab out t.v.'s;
Running example relation schema:
Beers(name , manf) Bars(name , addr, license) Drinkers(name , addr, phone) Likes(drinker , beer ) Sells(bar , beer , price) Frequents(drinker , bar)
Example
What b eers are made by Anheuser-Busch?
Beers(name , manf)
SELECT name FROM Beers WHERE manf = 'Anheuser-Busch' ;
Note single quotes for strings.
name Bud Bud Lite Michelob
Star as List of All Attributes
Beers(name , manf)
SELECT * FROM Beers WHERE manf = 'Anheuser-Busch' ;
name manf
Bud Anheuser-Busch Bud Lite Anheuser-Busch Michelob Anheuser-Busch
Renaming columns
Beers(name , manf)
SELECT name AS beer FROM Beers WHERE manf = 'Anheuser-Busch' ;
b eer Bud Bud Lite Michelob
Trick: If you want an answer with a particular string in each row, use that constant as an expression.
Likes(drinker , beer )
SELECT drinker, 'likes Bud' AS whoLikesBud FROM Likes WHERE beer = 'Bud';
drinker whoLikesBud Sally likes Bud Fred likes Bud
Example
Find the price Jo e's Bar charges for Bud.
Sells(bar , beer , price)
SELECT price FROM Sells WHERE bar = 'Joe''s Bar' AND beer = 'Bud';
Note: two single-quotes in a character string represent one single quote.
Conditions in WHERE clause can use logical op erators AND, OR, NOT and parentheses in the usual way.
Rememb er: SQL is case insensitive. Keywords like SELECT or AND can b e written upp er/lower case as you like.
F Only inside quoted strings do es case matter.
Nulls
In place of a value in a tuple's comp onent.
Interpretation is not exactly \missing value."
There could b e many reasons why no value is present, e.g., \value inappropriate."
Comparing Nulls to Values
3rd truth value UNKNOWN.
A query only pro duces tuples if the WHERE- condition evaluates to TRUE (UNKNOWN is not sucent).
Example
bar b eer price Jo e's bar Bud NULL
SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00;
UNKNOWN UNKNOWN
UNKNOWN
Jo e's Bar is not pro duced, even though the WHERE condition is a tautology.
Multirelatio n Queries
List of relations in FROM clause.
Relation-dot-att ribut e disambiguates attributes from several relations.
Example
Find the b eers that the frequenters of Jo e's Bar like.
Likes(drinker , beer ) Frequents(drinker , bar)
SELECT beer FROM Frequents, Likes WHERE bar = 'Joe''s Bar' AND Frequents.drinke r = Likes.drinker;
Formal Semantics of Multirelation Queries
Same as for single relation, but start with the pro duct of all the relations mentioned in the FROM clause.
Op erational Semantics
Consider a tuple variable for each relation in the FROM.
Imagine these tuple variables each p ointing to a tuple of their relation, in all combinations (e.g., nested lo ops).
If the current assignment of tuple-variabl es to tuples makes the WHERE true, then output the terms of the SELECT.
Explicit Tuple Variables
Sometimes we need to refer to two or more copies of a relation.
Use tuple variables as aliases of the relations.
Example
Find pairs of b eers by the same manufacturer.
Beers(name ,^ manf)
SELECT b1.name, b2.name FROM Beers b1, Beers b WHERE b1.manf = b2.manf AND b1.name < b2.name;
SQL p ermits AS b etween relation and its tuple variable; Oracle do es not.
Note that b1.name < b2.name is needed to avoid pro ducing (Bud, Bud) and to avoid pro ducing a pair in b oth orders.
Sub queries
Result of a select-from-where query can b e used in the where-clause of another query.
Simplest Case: Sub query Returns a Single, Unary Tuple
Find bars that serve Miller at the same price Jo e charges for Bud.
Sells(bar , beer , price)
SELECT bar FROM Sells WHERE beer = 'Miller' AND price = (SELECT price FROM Sells WHERE bar = 'Joe''s Bar' AND beer = 'Bud' );
Notice the scoping rule: an attribute refers to the most closely nested relation with that attribute.
Parentheses around sub query are essential.
EXISTS
\EXISTS(relati on)" is true i the relation is nonempty.
Example
Find the b eers that are the unique b eer by their manufacturer.
Beers(name , manf)
SELECT name FROM Beers b WHERE NOT EXISTS( SELECT * FROM Beers WHERE manf = b1.manf AND name <> b1.name );
Note scoping rule: to refer to outer Beers in the inner sub query, we need to give the outer a tuple variable, b1 in this example.
A sub query that refers to values from a surrounding query is called a correlated subquery.
Quanti ers
ANY and ALL b ehave as existential and universal quanti ers, resp ectively.
Beware: in common parlance, \any" and \all" seem to b e synonyms, e.g., \I am fatter than any of you" vs. \I am fatter than all of you." But in SQL:
Example
Find the b eer(s) sold for the highest price.
Sells(bar , beer , price)
SELECT beer FROM Sells WHERE price >= ALL( SELECT price FROM Sells );
Class Problem
Find the b eer(s) not sold for the lowest price.
