slides6 - Schedule Today: Jan. 22 (T) x x SQL Queries. Read...

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

View Full Document Right Arrow Icon
Winter 2002 Arthur Keller – CS 180 6–1 Schedule Today: Jan. 22 (T) SQL Queries. Read Sections 6.1-6.2. Assignment 2 due. Jan. 24 (TH) Subqueries, Grouping and Aggregation. Read Sections 6.3-6.4. Project Part 2 due. Jan. 29 (T) Modifications, Schemas, Views. Read Sections 6.5-6.7. Assignment 3 due. Jan. 31 (TH) Constraints. Read Sections 7.1-7.3, 7.4.1. Project Part 3 due.
Background image of page 1

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

View Full DocumentRight Arrow Icon
Winter 2002 Arthur Keller – CS 180 6–2 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 )
Background image of page 2
Winter 2002 Arthur Keller – CS 180 6–3 Example What beers are made by Anheuser-Busch? Beers(name , manf) SELECT name FROM Beers WHERE manf = 'Anheuser-Busch'; Note: single quotes for strings. name Bud Bud Lite Michelob
Background image of page 3

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

View Full DocumentRight Arrow Icon
Winter 2002 Arthur Keller – CS 180 6–4 Formal Semantics of Single-Relation SQL Query 1. Start with the relation in the FROM clause. 2. Apply (bag) σ , using condition in WHERE clause. 3. Apply (extended, bag) π 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 , if so.
Background image of page 4
Winter 2002 Arthur Keller – CS 180 6–5 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
Background image of page 5

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

View Full DocumentRight Arrow Icon
Winter 2002 Arthur Keller – CS 180 6–6 Renaming columns Beers(name , manf) SELECT name AS beer FROM Beers WHERE manf = 'Anheuser- Busch'; beer Bud Bud Lite Michelob
Background image of page 6
Winter 2002 Arthur Keller – CS 180 6–7 Expressions as Values in Columns Sells(bar , beer , price) SELECT bar, beer, price*120 AS priceInYen FROM Sells; bar beer priceInYen Joe’s Bud 300 Sue’s Miller 360 Note: no WHERE clause is OK.
Background image of page 7

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

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

Page1 / 22

slides6 - Schedule Today: Jan. 22 (T) x x SQL Queries. Read...

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

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