1 / 100

Prof. Sin-Min Lee

Final Revision 2. CS 157A Lecture 26. Prof. Sin-Min Lee. 3) Student (ufid, major) Quiz(Q_num, point_pos) Q_score(q_num,ufid,points_scored) (in TRC). A) Which quizzes did a “CE” major score 100% on? { a[Q_num]: a  Quiz , s STUDENT ; t  Q_score, s[ufid]=t[ufid]  s[ufid]=“CE”

jason
Download Presentation

Prof. Sin-Min Lee

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Final Revision 2 CS 157A Lecture 26 Prof. Sin-Min Lee

  2. 3) Student (ufid, major)Quiz(Q_num, point_pos)Q_score(q_num,ufid,points_scored) (in TRC) A) Which quizzes did a “CE” major score 100% on? { a[Q_num]: a  Quiz ,s STUDENT ; t  Q_score, s[ufid]=t[ufid]  s[ufid]=“CE”  a[Q_num]=t[q_num] t[points_scored]=100% } Relational Algebra: q_num [ Quiz |x| (major=“CE” Student) |x| (points_scored=“100” Q_score))

  3. Consider the following relational database schema: • Pizza(pid, pname, size) • Store(sname, phone, quality) • Soldby(pid, sname, price) • Express each of the following queries in the relational algebra. • Find the name of all stores that sell both veggie and cheese pizza. • Answer: • sname(pname = ‘veggie’ Pizza |X| Soldby) •  • sname(pname = ‘cheese’ Pizza |X| Soldby)

  4. Consider the following relational database schema: Pizza(pid, pname, size) Store(sname, phone, quality) Soldby(pid, sname, price) Express each of the following queries in the relational algebra. b) Find the names and phone numbers of all stores that sell good or excellent veggie pizza under $10. Answer: sname, phone((pname = ‘veggie’ Pizza) |X| (quality = ‘good’ Store) |X| (price < 10 Soldby))  sname, phone((pname = ‘veggie’ Pizza) |X| (quality = ‘excellent’ Store) |X| (price < 10 Soldby))

  5. Student (ufid, major)Quiz(Q_num, point_pos)Q_score(q_num,ufid,points_scoredb) Who are the “CE” major who missed Quiz 3? { s [ufid]:s  STUDENT. t Q_SCORE  s[ufid]=“CE” t[q_num ]=“3”  t[points_scored] =“null”  s[ufid]=t[ufid] }

  6. Student (ufid, major)Quiz(Q_num, point_pos)Q_score(q_num,ufid,points_scoredc) Which students have a 0 score for 2 different quizzes? { s[ufid]: s  STUDENT t1, t2 Q_SCORE s[ufid]=t1[ufid]= t2[ufid]  t1[q_num] t2 [q_num]  t1 [point_score]=t2 [point_score]=“0” }

  7. 7) Author (aid, name, phone) with key =(aid)wrote(aid, isbn, order) with key =(aid, Isbn)book(isbn, title, publisher,dte) with key=(isbn) a) Find the names of authors who wrote or co wrote books published in 1995 {a [name]: a Author w  WROTE b  Book  a.aid=w.aid  w.isbn=b.isbn  b.date=1995}

  8. Author (aid, name, phone) with key =(aid)wrote(aid, isbn, order) with key =(aid, Isbn)book(isbn, title, publisher,dte) with key=(isbn)b) Find the names of authors who were always the first author of books they wrote { a[name]: a  Author,  w  Wrote, b  Book  a.aid=w.aid  w.isbn = b.isbn  w.order=1 }

  9. Author (aid, name, phone) with key =(aid)wrote(aid, isbn, order) with key =(aid, Isbn)book(isbn, title, publisher,dte) with key=(isbn) c) Find the AID of author (if any) who have written or co written books published by every publisher of book? Let Publisher = {b[publisher]: b  Book } { a[aid]: a  Author  p  Publisher  w  Wrote & b*  Book & a [aid]=w[aid] & w[isbn]= b*[isbn] & b*[publisher]=p }

  10. 6 c) Author (aid, name, phone) with key =(aid)wrote(aid, isbn, order) with key =(aid, Isbn)book(isbn, title, publisher,dte) with key=(isbn Find the aid of authors (if any) who have written or co-written books published by every publisher of books? (Relational Algebra, TRC) Relational Algebra: Let T = publisherBook, aid [ (Author |x| Wrote|x| Book) T ]

  11. Data Definition in SQL • So far we have see the Data Manipulation Language, DML • Next: Data Definition Language (DDL) • Data types: • Defines the types. • Data definition: defining the schema. • Create tables • Delete tables • Modify table schema • Indexes: to improve performance

  12. Data Types in SQL • Characters: • CHAR(20) -- fixed length • VARCHAR(40) -- variable length • Numbers: • INT, REAL plus variations • Times and dates: • DATE, TIME (Pointbase)

  13. Creating Tables Example: CREATE TABLE Person( name VARCHAR(30), social-security-number INT, age SHORTINT, city VARCHAR(30), gender BIT(1), Birthdate DATE );

  14. 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)

  15. 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

  16. Union, Intersection, Difference (SELECT name FROM Person WHERE City=“Seattle”) UNION (SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”) Similarly, you can use INTERSECT and EXCEPT. You must have the same attribute names (otherwise: rename).

  17. Formal Semanticsof Single-Relation SQL Query • Start with the relation in the FROM clause. • Apply (bag) , using condition in WHERE clause. • 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.

  18. 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

  19. Renaming columns Beers(name, manf) SELECT name AS beer FROM Beers WHERE manf = 'Anheuser-Busch'; beer Bud Bud Lite Michelob

  20. 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.

  21. Trick: If you want an answer with a particular string in each row, use that constant as an expression. Likes(drinker, beer) SELECT drinker, 'likes Bud' AS whoLikesBud FROM Likes WHERE beer = 'Bud'; drinker whoLikesBud Sally likes Bud Fred likes Bud … …

  22. Example • Find the price Joe's Bar charges for Bud. Sells(bar, beer, price) SELECT price FROM Sells WHERE bar = 'Joe''s Bar' AND beer = 'Bud'; • Note: two single-quotes in a character string represent one single quote. • Conditions in WHERE clause can use logical operators AND, OR, NOT and parentheses in the usual way. • Remember: SQL is case insensitive. Keywords like SELECT or AND can be written upper/lower case as you like. • Only inside quoted strings does case matter.

  23. Updates UPDATE relation SET list of assignments WHERE condition. Example Drinker Fred's phone number is 555-1212. Drinkers(name, addr, phone) UPDATE Drinkers SET phone = '555-1212' WHERE name = 'Fred'; Example Make $4 the maximum price for beer. • Updates many tuples at once. Sells(bar, beer, price) UPDATE Sells SET price = 4.00 WHERE price > 4.00;

  24. Modifying the Database Three kinds of modifications • Insertions • Deletions • Updates Sometimes they are all called “updates”

  25. Deleting or Modifying a Table Deleting: Exercise with care !! Example: DROP Person; Altering: (adding or removing an attribute). ALTER TABLE Person ADD phone CHAR(16); ALTER TABLE Person DROP age; Example: What happens when you make changes to the schema?

  26. Default Values Specifying default values: CREATE TABLE Person( name VARCHAR(30), social-security-number INT, age SHORTINT DEFAULT 100, city VARCHAR(30) DEFAULT ‘Seattle’, gender CHAR(1) DEFAULT ‘?’, Birthdate DATE The default of defaults: NULL

  27. Conserving Duplicates (SELECT name FROM Person WHERE City=“Seattle”) UNIONALL (SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)

  28. Insertions General form: INSERT INTO R(A1,…., An) VALUES (v1,…., vn) Example: Insert a new purchase to the database: INSERT INTO Purchase(buyer, seller, product, store) VALUES (‘Joe’, ‘Fred’, ‘wakeup-clock-espresso-machine’, ‘The Sharper Image’) Missing attribute  NULL. May drop attribute names if give them in order.

  29. Insertions INSERT INTO PRODUCT(name) SELECT DISTINCT Purchase.product FROM Purchase WHERE Purchase.date > “10/26/01” The query replaces the VALUES keyword. Here we insert many tuples into PRODUCT

  30. Insertion: an Example Product(name, listPrice, category) Purchase(prodName, buyerName, price) prodName is foreign key in Product.name Suppose database got corrupted and we need to fix it: corrupted Purchase Product Task: insert in Product all prodNames from Purchase

  31. Insertion: an Example INSERT INTO Product(name) SELECT DISTINCT prodName FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)

  32. Insertion: an Example INSERT INTO Product(name, listPrice) SELECT DISTINCT prodName, price FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product) Depends on the implementation

  33. Deletions Example: DELETE FROM PURCHASE WHERE seller = ‘Joe’ AND product = ‘Brooklyn Bridge’ Factoid about SQL: there is no way to delete only a single occurrence of a tuple that appears twice in a relation.

  34. Deletion DELETE FROM relation WHERE condition. • Deletes all tuples satisfying the condition from the named relation. Example Sally no longer likes Bud. Likes(drinker, beer) DELETE FROM Likes WHERE drinker = 'Sally' AND beer = 'Bud'; Example Make the Likes relation empty. DELETE FROM Likes;

  35. Updates Example: UPDATE PRODUCT SET price = price/2 WHERE Product.name IN (SELECT product FROM Purchase WHERE Date =‘Oct, 25, 1999’);

  36. Patterns • % stands for any string. • _ 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 Find drinkers whose phone has exchange 555. Drinkers(name, addr, phone) SELECT name FROM Drinkers WHERE phone LIKE '%555-_ _ _ _’; • Note patterns must be quoted, like strings.

  37. Nulls In place of a value in a tuple's component. • Interpretation is not exactly “missing value.” • There could be many reasons why no value is present, e.g., “value inappropriate.” Comparing Nulls to Values • 3rd truth value UNKNOWN. • A query only produces tuples if the WHERE-condition evaluates to TRUE(UNKNOWN is not sufficient).

  38. Example bar beer price Joe's bar Bud NULL SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00; UNKNOWN UNKNOWN UNKNOWN • Joe's Bar is not produced, even though the WHERE condition is a tautology.

  39. 3-Valued Logic Think of true = 1; false = 0, and unknown = 1/2. Then: • AND = min. • OR = max. • NOT(x) = 1 – x. Some Key Laws Fail to Hold Example: Law of the excluded middle, i.e., pOR NOT p = TRUE • For 3-valued logic: if p = unknown, then left side = max(1/2,(1–1/2)) = 1/2 ≠ 1. • Like bag algebra, there is no way known to make 3-valued logic conform to all the laws we expect for sets/2-valued logic, respectively.

  40. Testing for NULL • The condition value = NULL always evaluates to UNKNOWN, even if the value is NULL! • Use value IS NULL or value IS NOT NULL instead.

  41. Multi-relation Queries • List of relations in FROM clause. • Relation-dot-attribute disambiguates attributes from several relations. Example Find the beers that the frequenters of Joe's Bar like. Likes(drinker, beer) Frequents(drinker, bar) SELECT beer FROM Frequents, Likes WHERE bar = 'Joe''s Bar' AND Frequents.drinker = Likes.drinker;

  42. Formal Semantics of Multi-relation Queries Same as for single relation, but start with the product of all the relations mentioned in the FROM clause. Operational Semantics Consider a tuple variable for each relation in the FROM. • Imagine these tuple variables each pointing to a tuple of their relation, in all combinations (e.g., nested loops). • If the current assignment of tuple-variables to tuples makes the WHERE true, then output the attributes of the SELECT.

  43. Explicit Tuple Variables Sometimes we need to refer to two or more copies of a relation. • Use tuple variables as aliases of the relations. Example Find pairs of beers by the same manufacturer. Beers(name, manf) SELECT b1.name, b2.name FROM Beers b1, Beers b2 WHERE b1.manf = b2.manf AND b1.name < b2.name; • SQL permits AS between relation and its tuple variable;Oracle does not. • Note that b1.name < b2.name is needed to avoid producing (Bud, Bud) and to avoid producing a pair in both orders.

  44. Subqueries Result of a select-from-where query can be used in the where-clause of another query. Simplest Case: Subquery Returns a Single, Unary Tuple Find bars that serve Miller at the same price Joe charges for Bud. Sells(bar, beer, price) SELECT bar FROM Sells WHERE beer = 'Miller' AND price = (SELECT price FROM Sells WHERE bar = 'Joe''s Bar' AND beer = 'Bud'); • Notice the scoping rule: an attribute refers to the most closely nested relation with that attribute. • Parentheses around subquery are essential.

  45. The IN Operator “Tuple IN relation” is true iff the tuple is in the relation. Example Find the name and manufacturer of beers that Fred likes. Beers(name, manf) Likes(drinker, beer) SELECT * FROM Beers WHERE name IN (SELECT beer FROM Likes WHERE drinker = 'Fred’); • Also: NOT IN.

  46. EXISTS “EXISTS(relation)” is true iff the relation is nonempty. Example Find the beers that are the unique beer by their manufacturer. Beers(name, manf) SELECT name FROM Beers b1 WHERE NOT EXISTS (SELECT * FROM Beers WHERE manf = b1.manf AND name <> b1.name); • Note scoping rule: to refer to outer Beers in the inner subquery, we need to give the outer a tuple variable, b1 in this example. • A subquery that refers to values from a surrounding query is called a correlated subquery.

  47. Quantifiers ANY and ALL behave as existential and universal quantifiers, respectively. • Beware: in common parlance, “any” and “all” seem to be synonyms, e.g., “I am fatter than any of you” vs. “I am fatter than all of you.” But in SQL: Example Find the beer(s) sold for the highest price. Sells(bar, beer, price) SELECT beer FROM Sells WHERE price >= ALL( SELECT price FROM Sells); Class Problem Find the beer(s) not sold for the lowest price.

  48. Union, Intersection, Difference “(subquery) UNION (subquery)” produces the union of the two relations. • Similarly for INTERSECT, EXCEPT = intersection and set difference. • But: in Oracle set difference is MINUS, not EXCEPT. Example Find the drinkers and beers such that the drinker likes the beer and frequents a bar that serves it. Likes(drinker, beer) Sells(bar, beer, price) Frequents(drinker, bar) (SELECT * FROM Likes) INTERSECT (SELECT drinker, beer FROM Sells, Frequents WHERE Frequents.bar = Sells.bar );

  49. Example Find the different prices charged for beers. Sells(bar, beer, price) SELECT DISTINCT price FROM Sells;

More Related