SQL = S tructured Q uery L anguage Standard language for querying and manipulating CS411 data • Has similar capabilities for queries to those in Database Systems relational algebra • Support statements for modifying a database (e.g., inserting and deleting tuples) and for declaring a database schema 06: SQL Many standards: SQL92, SQL2, SQL3, SQL99 Kazuhiro Minami • We cover features that conform with SQL99 The basic form of a SQL query is What is special about SQL? select-from-where Project out everything not in You describe what you want, the final answer SELECT desired attributes and the job of the DBMS is to figure out how to compute what you want efficiently. FROM one or more tables Every table you (at least in theory) want to join, WHERE condition on the rows of together the tables All the join and selection conditions
What beers are made by Anheuser-Busch? Beers(name, manf) Name Manf Name Single-Relation Queries Bud Anheuser-Busch Bud Bud Lite Anheuser-Busch Bud Lite Michelob Anheuser-Busch Michelob Super Dry Asahi SELECT name SELECT name FROM Beers FROM Beers WHERE manf = ‘Anheuser-Busch’; WHERE manf = ‘Anheuser-Busch’; In relational algebra : σ [manf = “Anheuser-Busch”] Beers Here is a way to think about how the These simple queries can be query might be implemented translated to relational algebra 1. Imagine a tuple variable A B C ranging over each tuple 1. Begin with the of the relation SELECT A1, …, An relation in the FROM mentioned in FROM. FROM R clause. 2. Check if the “current” WHERE condition 2. Apply the selection tuple satisfies the indicated by the WHERE clause. WHERE clause. 3. If so, output the R [ condition ][ A1 , …, An ] 3. Apply the projection attributes/expressions indicated by the of the SELECT clause A B SELECT clause. π [ A1 , …, An ] σ [ condition ] R using the components of this tuple.
Put * in the SELECT clause if you Find all US companies whose stock don’t want to project out any is > $500 attributes Company(sticker, name, country, stockPrice) Beers(name, manf) SELECT * SELECT * SELECT * SELECT * FROM Beers FROM Company FROM Beers FROM Company WHERE country=‘USA’ AND stockPrice > 500 WHERE manf = ‘Anheuser-Busch’; WHERE country=‘USA’ AND stockPrice > 500 WHERE manf = ‘Anheuser-Busch’; Name Manf Sticker Name Country StockPrice Bud Anheuser-Busch GOOG Google USA 550 Bud Lite Anheuser-Busch GOOG Apple USA 485 Michelob Anheuser-Busch You can rename the attributes in You can use math in the the result, using “as <new name>” SELECT clause Sells(bar, beer, price) Beers(name, manf) d e i s i n t p e x c e Case-insensitive , SELECT name AS beer, manf s g SELECT name AS beer, manf n r i s t d t e o u q FROM Beers FROM Beers WHERE manf = ‘Anheuser-Busch’; SELECT bar, bEeR, price*120 AS priceInYen WHERE manf = ‘Anheuser-Busch’; SELECT bar, bEeR, price*120 AS priceInYen FROM Sells; FROM Sells; Beer Manf Bud Anheuser-Busch Bar Beer PriceInYen Bud Lite Anheuser-Busch Joe’s Bud 300 Michelob Anheuser-Busch Sue’s Asahi 360 … … …
You can create a new column and Find the price Joe’s Bar give it a constant value, in the charges for Bud. SELECT clause Sells(bar, beer, price) Likes(Drinker, beer) SELECT price SELECT price FROM Sells SELECT drinker, FROM Sells SELECT drinker, WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’; ‘Likes Bud’ AS WhoLikesBud WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’; ‘Likes Bud’ AS WhoLikesBud FROM Likes FROM Likes WHERE beer = ‘Bud’; WHERE beer = ‘Bud’; Drinker Beer Drinker WhoLikesBud Two single quotes inside Sally Bud Sally Likes Bud a string = one apostrophe Fred Bud Fred Likes Bud What you can use in the attr LIKE pattern does pattern WHERE clause conditions: matching on strings pattern is a quoted string that may contain two constants of any supported type special symbols: attribute names of the relation(s) used in the FROM arithmetic operations: stockprice*2 Symbol What It Matches operations on strings (e.g., “||” for concatenation) % matches any sequence of characters _ matches any single character comparison operators: =, <>, <, >, <=, >= lexicographic order on strings (<) string pattern matching: s LIKE p special functions for comparing dates and times phone LIKE ‘%555-_ _ _ _’ address LIKE “%Mountain%” and combinations of the above using AND, OR, NOT, and parentheses
Find all US companies whose Find the drinkers with phone address contains “Mountain” prefix 555 Drinkers(name, addr, phone) Company(sticker, name, address, country, stockPrice) SELECT name SELECT * SELECT name SELECT * FROM Drinkers FROM Company FROM Drinkers FROM Company WHERE country=“USA” AND WHERE phone LIKE ‘%555-____’; WHERE country=“USA” AND WHERE phone LIKE ‘%555-____’; address LIKE ‘%Mountain%’; address LIKE ‘%Mountain%’; What if an attribute value is unknown, Conditions involving NULL evaluate or the attribute is inapplicable (e.g., my to unknown , rather than true or false daughter’s spouse) ? Example condition Evaluates to Bar Beer Price true tru ‘Smith’ = ‘Smith’ A tuple only Jillian’s Bud 2.00 false fal 2 > 6 goes in the White Horse Inn Asahi NULL un unkno known ‘Smith’ = NULL answer if its unkno un known 2 < NULL truth value SELECT bar true AND unkn tru unknown unkno un known SELECT bar for the FROM Sells FROM Sells tru true OR unkno unknown tru true WHERE WHERE price < 2.00 OR price >= 2.00; WHERE price < 2.00 OR price >= 2.00; false AND un fal unkno known fal false clause is fal false OR unknown unknown un unkno known Bar true. un unkno known OR un unknown unkno un known Jillian’s Why???
The “law of the excluded SQL code writers spend a lot of space dealing with NULL values middle” doesn’t hold in this 3- valued logic Can test for NULL explicitly: x IS NULL SELECT bar unknown unknown x IS NOT NULL SELECT bar FROM Sells FROM Sells WHERE price < 2.00 OR price >= 2.00; WHERE price < 2.00 OR price >= 2.00; SELECT * nown unkno FROM Person WHERE age < 25 OR age >= 25 OR Bar Beer Price age IS NULL White Horse Inn Asahi NULL The answer includes all Persons! Exercise 1: online bookstore Book(isbn, title, publisher, price) Author(assn, aname, isbn) Customer(cid, cname, state, city, zipcode) Multi-Relation Queries Buy(tid, cid, isbn, year, month, day) Q1: Make a list of the ISBNs and titles of books whose price is greater than $1000? SELECT isbn, title FROM Book WHERE price > 1000
If you need to join several Find the beers liked by at least one relations, you can list them all in person who frequents Murphy’s Pub the FROM clause Likes(drinker, beer) Frequents(drinker, bar) List the bars that serve a beer that Alice likes. Likes(drinker, beer) Sells(bar, beer, price) SELECT beer AS beersWorthKeeping SELECT beer AS beersWorthKeeping FROM Likes, Frequents SELECT bar FROM Likes, Frequents SELECT bar WHERE bar = ‘Murphy’’s Pub’ AND FROM Sells, Likes WHERE bar = ‘Murphy’’s Pub’ AND FROM Sells, Likes Frequents.drinker = Likes.drinker; WHERE drinker = ‘Alice’ AND Frequents.drinker = Likes.drinker; WHERE drinker = ‘Alice’ AND Likes.beer = Sells.beer; Likes.beer = Sells.beer; BeersWorthKeeping how we his is ho Samuel Adams Pale Ale Thi disambiguate … attribute names. π [beer] (Likes ⋈ σ [bar = “Murphy’s Pub”] Frequents) π [bar](Sells ⋈ σ [drinker =“Alice”] Likes) Find names of people living in Champaign who You can also join three or more relations, bought snow shovels, and the names of the just like in relational algebra stores where they bought them Find names and phone numbers of people buying telephony products. Purchase (buyer, seller, store, product) Person(pname, phoneNumber, city ) Product (name, price, category, maker) Purchase (buyer, seller, store, product) Person (name, phoneNumber, city) SELECT pname, store SELECT pname, store FROM Person, Purchase FROM Person, Purchase SELECT Person.name, Person.phoneNumber SELECT Person.name, Person.phoneNumber WHERE pname = buyer AND city = ‘Champaign’ WHERE pname = buyer AND city = ‘Champaign’ FROM Person, Purchase, Product FROM Person, Purchase, Product AND product = ‘snow shovel’; AND product = ‘snow shovel’; WHERE Person.name=Purchase.buyer WHERE Person.name=Purchase.buyer AND Purchase.product=Product.name AND Purchase.product=Product.name π [pname, store]( σ [city = “Champaign”] Person ⋈ Pname = buyer AND Product.category=“telephony” AND Product.category=“telephony” σ [product=“snow shovel”] Purchase)
Recommend
More recommend
