1 / 21

Lecture 4: Advanced SQL

Lecture 4: Advanced SQL. INTERSECT and EXCEPT: (missing from MySQL ). (SELECT R.A, R.B FROM R) INTERSECT (SELECT S.A, S.B FROM S). SELECT R.A, R.B FROM R WHERE EXISTS ( SELECT * FROM S WHERE R.A=S.A and R.B=S.B). (SELECT R.A, R.B FROM R) EXCEPT (SELECT S.A, S.B

mimis
Download Presentation

Lecture 4: Advanced SQL

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. Lecture 4: Advanced SQL

  2. INTERSECT and EXCEPT:(missing from MySQL) (SELECT R.A, R.B FROM R)INTERSECT (SELECT S.A, S.B FROM S) SELECT R.A, R.B FROM RWHEREEXISTS(SELECT *FROM SWHERE R.A=S.A and R.B=S.B) (SELECT R.A, R.B FROM R)EXCEPT (SELECT S.A, S.B FROM S) SELECT R.A, R.B FROM RWHERENOTEXISTS(SELECT *FROM SWHERE R.A=S.A and R.B=S.B)

  3. Null Values • NULL in SQL is used as a placeholder for a missing or unknown values • By default, a table column can hold a NULL value

  4. Use of Null Values • If x=Null then 4*(3-x)/7 is still NULL • If x=Null then x=“Joe” is UNKNOWN • In SQL there are three boolean values: FALSE = 0 UNKNOWN = 0.5 TRUE = 1

  5. Three-Valued Logic • C1 AND C2 = min(C1, C2) • C1 OR C2 = max(C1, C2) • NOT C1 = 1 – C1 Rule in SQL: include only tuples that yield TRUE SELECT * FROM Person WHERE (age < 25) AND (height > 6 OR weight > 190) E.g.age=20height=NULLweight=200

  6. Three-Valued Logic Unexpected behavior: Some Persons are not included ! SELECT * FROM Person WHERE age < 25 OR age >= 25

  7. Working with Null Values Can test for NULL explicitly: • x IS NULL • x IS NOT NULL Now it includes all Persons SELECT * FROM Person WHERE age < 25 OR age >= 25 OR age IS NULL

  8. Explicit Joins Explicit joins in SQL: Product(name, category) Purchase(prodName, store) Same as: But Products that never sold will be lost ! SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName SELECT Product.name, Purchase.store FROM Product, Purchase WHERE Product.name = Purchase.prodName

  9. Left Outer Joins Left outer joins in SQL: Product(name, category) Purchase(prodName, store) SELECT Product.name, Purchase.store FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodName

  10. Product Purchase

  11. Types of Joins - Summary • Inner join: same as Join, implicit Join • Left outer join: • Same as Left join • Include the left tuple even if there’s no match • Right outer join: • Same as right join • Include the right tuple even if there’s no match • Full outer join: • Missing from MySQL(but can be emulated! how?) • Include the both left and right tuples even if there’s no match

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

  13. 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 attributes  NULL. May drop attribute names if give them in order.

  14. 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 manytuples into PRODUCT The number of columns must be identical

  15. 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: Purchase Product Task: insert in Product all prodNames from Purchase

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

  17. Insertion: an Example INSERT INTO Product(name, listPrice) SELECT DISTINCTprodName, price FROM Purchase WHEREprodNameNOT IN (SELECT name FROM Product) ERROR!

  18. Deletions Example: DELETE FROM PURCHASE WHERE seller = ‘Joe’ AND product = ‘Brooklyn Bridge’ Factoid about SQL: there is no way to delete only xoccurrences of a tuple that appears x+ktimes in a relation.

  19. Updates Example: UPDATEProduct SETlistPrice= listPrice/2 WHERE Product.name IN (SELECT product FROM Purchase WHERE Date =‘Oct, 25, 1999’);

  20. Updates Using Exists Example: UPDATE Product SETlistPrice = (SELECT AVG(price)FROM PurchaseWHERE prodName=name)WHERE EXISTS (SELECT *FROM PurchaseWHERE prodName=name);

  21. Table Modifications - Summary INSERT INTO table_name(column1, column2,...)VALUES(value1, value2, ...) INSERT INTO table_name(column1, column2,...)SELECT … DELETE FROM table_name WHERE cond UPDATEtable_nameSETcolumn1=expr1, column2=expr2,...WHEREcond

More Related