Chapter 6 The database Language sol-as a tutorial ●About SQL SQL is a standard database language, adopted by many commercial systems. ANSI SQL,SQL-92 or SQL2,SQL99 or SQL3 extends SQL2 with object- relational features.SQL2003 is the collection of extensions to SQL3. How to query the database How to make modifications on database ●Transactions in SQL
Chapter 6 The database Language SQL –as a tutorial z About SQL SQL is a standard database language, adopted by many commercial systems. ANSI SQL, SQL-92 or SQL2, SQL99 or SQL3 extends SQL2 with objectrelational features. SQL2003 is the collection of extensions to SQL3. z How to query the database z How to make modifications on database z Transactions in SQL
Why SQL? SQL is a very-high-level language. - Say "what to do"rather than "how to do it.” Avoid a lot of data-manipulation details needed in procedural languages like C++or Java. o Database management system figures out best"way to execute query. - Called "query optimization
Why SQL? zSQL is a very-high-level language. – Say “what to do” rather than “how to do it.” – Avoid a lot of data-manipulation details needed in procedural languages like C++ or Java. zDatabase management system figures out “best” way to execute query. – Called “query optimization
SQL:structured query language o Components of language: Schema definition,Data retrieval,Data modification,Indexes,Constraints,Views. Triggers,Transactions,authorization,etc 0 DDL data definition language 0 DML data Manipulation Language ●' Two forms of usage: o Interactive SOL 0 Embedded SQL
SQL:structured query language zComponents of language: Schema definition, Data retrieval, Data modification, Indexes, Constraints, Views, Triggers, Transactions, authorization,etc o DDL = data definition language o DML = data Manipulation Language z Two forms of usage: o Interactive SQL o Embedded SQL
SQL:Structured Query Language Form SELECT FROM WHERE GROUP BY HAVING ORDER BY list of attributes> Queries on one relation Queries on more than one relations Subqueries and correlated subqueries Full-relation operations
SQL:Structured Query Language Form SELECT FROM WHERE GROUP BY HAVING ORDER BY z Queries on one relation z Queries on more than one relations z Subqueries and correlated subqueries z Full-relation operations
SQL Queries Principal form: SELECT desired attributes FROM tuple variables-range over relations WHERE condition about tuple variables; 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)
SQL Queries z Principal form: SE L E C T desired attributes FROM t uple variables –– range over relations W HER E condition about tuple variables; Running example relation schema: B eers ( n am e , m anf) Ba r s ( na m e , addr, lic ense ) D rinker s ( na m e , a d dr, phon e ) Li kes( drink er,be er) S ells(b ar,be er, price ) F requ e nt s ( drinker, b ar)
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 "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 ” 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