cs411-06a-sql1 - CS411 Database Systems 06a SQL-1 The...

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

View Full Document Right Arrow Icon
1 CS411 Database Systems 06a: SQL-1 The Basics– Select-From-Where
Background image of page 1

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

View Full Document Right Arrow Icon
2 Why Do We Learn This?
Background image of page 2
3 SQL Introduction Standard language for querying and manipulating data S tructured Q uery L anguage Many standards out there: SQL92, SQL2, SQL3, SQL99 Vendors support various subsets of these, but all of what we’ll be talking about.
Background image of page 3

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

View Full Document Right Arrow Icon
4 Why SQL? • SQL is a very-high-level language, in which the programmer is able to avoid specifying a lot of data-manipulation details that would be necessary in languages like C++. • What makes SQL viable is that its queries are “optimized” quite well, yielding efficient query executions.
Background image of page 4
5 Select-From-Where Statements • The principal form of a query is: SELECT desired attributes FROM one or more tables WHERE condition about tuples of the tables
Background image of page 5

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

View Full Document Right Arrow Icon
6 Single-Relation Queries
Background image of page 6
7 Our Running Example • Most of 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 )
Background image of page 7

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

View Full Document Right Arrow Icon
8 Example • Using Beers(name, manf), what beers are made by Anheuser-Busch? SELECT name FROM Beers WHERE manf = ‘Anheuser-Busch’;
Background image of page 8
9 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.
Background image of page 9

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

View Full Document Right Arrow Icon
10 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.
Background image of page 10
11 Operational Semantics • To implement this algorithm think of a tuple variable ranging over 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.
Background image of page 11

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

View Full Document Right Arrow Icon
12 * 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’;
Background image of page 12
13 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.
Background image of page 13

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

View Full Document Right Arrow Icon
14 Renaming Attributes • If you want the result to have different attribute names, use “AS <new name>” to rename an attribute. • Example based on Beers(name, manf): SELECT name AS beer, manf FROM Beers WHERE manf = ‘Anheuser-Busch’
Background image of page 14
15 Result of Query: beer manf ‘Bud’ ‘Anheuser-Busch’ ‘Bud Lite’ ‘Anheuser-Busch’ ‘Michelob’ ‘Anheuser-Busch’
Background image of page 15

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

View Full Document Right Arrow Icon
16 Expressions in SELECT Clauses • Any expression that makes sense can appear as an element of a SELECT clause.
Background image of page 16
Image of page 17
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 66

cs411-06a-sql1 - CS411 Database Systems 06a SQL-1 The...

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

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