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 <desired attributes> FROM <tuple variables or relation name> WHERE <conditions> GROUP BY <attributes> HAVING <conditions> 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 <desired attributes> FROM <tuple variables or relation name> WHERE <conditions> GROUP BY <attributes> HAVING <conditions> ORDER BY < list of attributes> 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)