sql1 - 1 Introduction to SQL Select-From-Where Statements...

Info iconThis preview shows pages 1–15. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 1 Introduction to SQL Select-From-Where Statements Multirelation Queries Subqueries Slides are reused by the approval of Jeffrey Ullman’s 2 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. ◆ Database management system figures out “best” way to execute query. ◗ Called “query optimization.” 3 Select-From-Where Statements SELECT desired attributes FROM one or more tables WHERE condition about tuples of the tables 4 Our Running Example ◆ All 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 ) 5 Example ◆ Using Beers(name, manf) , what beers are made by Anheuser-Busch? SELECT name FROM Beers WHERE manf = ’Anheuser-Busch’; 6 Result of Query name Bud Bud Lite Michelob . . . The answer is a relation with a single attribute, name, and tuples with the name of each beer by Anheuser-Busch, such as Bud. 7 Meaning of Single-Relation Query ◆ Begin with the relation in the FROM clause. ◆ Apply the selection indicated by the WHERE clause. ◆ Apply the extended projection indicated by the SELECT clause. 8 Operational Semantics Check if Anheuser-Busch name manf Bud Anheuser-Busch Include t.name in the result, if so Tuple-variable t loops over all tuples 9 Operational Semantics --- General ◆ Think of a tuple variable visiting each tuple of the relation mentioned in FROM. ◆ Check if the “current” tuple satisfies the WHERE clause. ◆ If so, compute the attributes or expressions of the SELECT clause using the components of this tuple. 10 * In SELECT clauses ◆ When there is one relation in the FROM clause, * in the SELECT clause stands for “all attributes of this relation.” ◆ Example : Using Beers(name, manf) : SELECT * FROM Beers WHERE manf = ’Anheuser-Busch’; 11 Result of Query: name manf Bud Anheuser-Busch Bud Lite Anheuser-Busch Michelob Anheuser-Busch . . . . . . Now, the result has each of the attributes of Beers. 12 Renaming Attributes ◆ If you want the result to have different attribute names, use “AS <new name>” to rename an attribute. ◆ Example : Using Beers(name, manf) : SELECT name AS beer, manf FROM Beers WHERE manf = ’Anheuser-Busch’ 13 Result of Query: beer manf Bud Anheuser-Busch Bud Lite Anheuser-Busch Michelob Anheuser-Busch . . . . . . 14 Expressions in SELECT Clauses ◆ Any expression that makes sense can appear as an element of a SELECT...
View Full Document

This note was uploaded on 12/13/2011 for the course CSCE 520 taught by Professor Farkas during the Spring '11 term at South Carolina.

Page1 / 61

sql1 - 1 Introduction to SQL Select-From-Where Statements...

This preview shows document pages 1 - 15. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online