SQL Structured Query Language (SQL) CS 235: The language of - PDF document

SQL • Structured Query Language (SQL) CS 235: – The language of databases – Based on relational algebra Introduction to Databases • extended algebra operations • other extensions. Svetlozar Nestorov Lecture Notes #9 SQL Queries Running Example • Beers(name, manf) • General form: • Bars(name, addr, license) SELECT attributes you want • Drinkers(name, addr, phone) FROM relations • Likes(drinker, beer) WHERE conditions about tuples from • Sells(bar, beer, price) relations; • Frequents(drinker, bar) • Read and write in this order: from-where-select Formal Semantics of Example Query Single-Relation SQL Query 1. Start with the relation in the FROM • What beers are made by Anheuser-Busch? clause. • Beers(name, manf) 2. Apply (bag) σ , using condition in WHERE clause. SELECT name FROM Beers 3. Apply (extended, bag) π using attributes in SELECT clause. WHERE manf = 'Anheuser-Busch'; • Result: name BudLite Bud Michelob 1

Star as List of All Attributes Equivalent Operational Semantics • Imagine a tuple variable ranging over all • Beers(name, manf) tuples of the relation. For each tuple: SELECT * – Check if it satisfies the WHERE clause. FROM Beers – Print the values of terms in SELECT, if so. WHERE manf = 'Anheuser-Busch'; • Result: name manf BudLite Anheuser-Busch Bud Anheuser-Busch Michelob Anheuser-Busch Renaming Columns Expressions as Values in Columns • Beers(name, manf) • Sells(bar, beer, price) SELECT bar, beer, price*0.74 AS priceInEuros SELECT name AS beer FROM Sells; bar beer priceInEuros FROM Beers Spoon Amstel 2.96 WHERE manf = 'Anheuser-Busch'; Spoon Guinness 5.18 • Result: Whiskey Guinness 5.18 Whiskey Bud 3.7 beer • Note: no WHERE clause is OK. BudLite Bud Michelob Constant Values Example • If you want an answer with a particular string in • Find the price Spoon charges for Bud. each row, use that constant as an expression. Sells(bar, beer, price) SELECT price • Likes(drinker, beer) FROM Sells SELECT drinker, ‘connoisseur' AS status WHERE bar = 'Spoon' AND beer = 'Bud'; FROM Likes • Conditions in WHERE clause can use logical operators AND, OR, NOT and parentheses in the usual way. WHERE beer = ‘Guinness'; • SQL is case insensitive. Keywords like SELECT or AND • Result: drinker status can be written upper/lower case as you like. David connoisseur • Only inside quoted strings does case matter. Ryan connoisseur Paul connoisseur 2

Example 2 String Patterns • Find the names of all bars that sell for less • % stands for any string. than $4 at least one beer that’s not Bud. • _ stands for any one character. • “Attribute LIKE pattern” is a condition that is true if the string value of the attribute matches the pattern. – Also NOT LIKE for negation. Example Nulls • Find drinkers whose phone has exchange • In place of a value in a tuple's component. 555. • Interpretation is not exactly missing value . • Drinkers(name, addr, phone) • There could be many reasons why no value is present, e.g ., value inappropriate. SELECT name FROM Drinkers WHERE phone LIKE '%555-_ _ _ _'; • Note patterns must be quoted, like strings Comparing Nulls to Values Example SELECT bar • 3rd truth value UNKNOWN. FROM Sells • A query only produces tuples if the WHERE price < 2.00 OR price >= 2.00; WHERE-condition evaluates to TRUE (UNKNOWN is not sufficient). UNKNOWN UNKNOWN UNKNOWN • The result is empty, even though the WHERE condition is a tautology. 3

3-Valued Logic Some Key Laws Do Not Hold • Think of true = 1; false = 0, and unknown • Example: Law of the excluded middle, i.e ., = 1/2. p OR NOT p = TRUE • Then: • For 3-valued logic: if p = unknown, then – AND = min. left side = max(1/2,(1-1/2)) = 1/2 ≠ 1. – OR = max. • Like bag algebra, there is no way known to – NOT( x ) = 1 – x . make 3-valued logic conform to all the laws we expect for sets/2-valued logic, respectively. Example Query • Find all bars that do not sell Bud for more than $5. – Two interpretations? 4