Example:Query on one relation What beers are made by Anheuser-Busch? Beers(name,manf) SELECT name FROM Beers W HERE manf =Anheuser-Busch'; o Note:single quotes for strings. name The answer is a Bud relation with a single Bud Lite attribute Michelob
Example: Query on one relation What beers are made by Anheuser-Busch? B eers ( n am e , m anf) SE L E C T n a m e FROM B e er s W HER E m a nf ='An h e u s e r- B u s c h'; z Note: single quotes for strings. name Bud Bud Lite Michelob The answer is a relation with a single attribute
Formal Semantics of Single-Relation SQL Query Start with the relation in the FRoM clause. Apply (bag)o,using condition in WHERE clause. Apply (extended,bag)x using attributes in SELECT clause. Equivalent Operational Semantics Imagine a tuple variable ranging over all tuples of the relation.For each tuple: Check if it satisfies the wHERE clause. Print the values of terms in SELECT
Formal Semantics of Single-Relation SQL Query 1. Start with the relation in the FROM cla u s e. 2. Apply (bag) σ, using condition in WHE R E clause. 3. Apply (extended, bag) π using attributes in S ELEC T clause. Equivalent Operational Semantics Imagine a tuple variable ranging over all tuples of the relation. For each tuple: z Check if it satisfies the WHE R E clause. z Print the values of terms in S ELEC T, i f s o
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
Star as List of All Attributes Be e rs ( na m e ,man f) SEL ECT * FRO M B e e rs W H ERE m a nf = 'An h e userB usch'; name manf Bud Anheuser-Busch Bud Lite Anheuser-Busch Michelob Anheuser-Busch
Renaming columns If you want the result to have different attribute names,use "AS <new name>"to rename an attribute.For example: Beers(name,manf) SELECT name AS beer FROM Beers WH ERE manf =Anheuser-Busch'; beer Bud Bud Lite Michelob
Renaming columns Be e rs ( name , man f) SELE C T nam e A S b e er FROM B e er s W H E R E man f = 'An h euserB u sch'; beer Bud Bud Lite Michelob If you want the result to have different attribute names, use “AS <new name>” to rename an attribute. For example:
Expressions as Values in Columns Sells(bar,beer,price) ◆Any expression that makes sense can SELECT bar,beer, appear as an element of a SELECT clause. price*120 AS priceInYen FROM Sells; bar beer pricelnYen Joe's Bud 300 Sue's Miller 360 Note:no WHERE clause is OK
Expressions as Values in Columns S ells ( b a r, beer, pric e) S E L E C T bar, be e r, price*1 2 0 A S p ri ceI n Y e n FROM S ells ; bar beer priceInYen Joe ’ s Bud 300 Sue ’ s Miller 360 … … … z Note: no W H E R E clause is OK. Any expression that makes sense can appear as an element of a SELECT clause