1 lecture 4: advanced sql. 2 intersect and except: (missing from mysql) (select r.a, r.b from r)...
TRANSCRIPT
![Page 1: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/1.jpg)
1
Lecture 4: Advanced SQL
![Page 2: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/2.jpg)
2
INTERSECT and EXCEPT:(missing from MySQL)
(SELECT R.A, R.BFROM R) INTERSECT(SELECT S.A, S.BFROM S)
(SELECT R.A, R.BFROM R) INTERSECT(SELECT S.A, S.BFROM S)
SELECT R.A, R.BFROM RWHERE EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B)
SELECT R.A, R.BFROM RWHERE EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B)
(SELECT R.A, R.BFROM R) EXCEPT(SELECT S.A, S.BFROM S)
(SELECT R.A, R.BFROM R) EXCEPT(SELECT S.A, S.BFROM S)
SELECT R.A, R.BFROM RWHERE NOT EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B)
SELECT R.A, R.BFROM RWHERE NOT EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B)
![Page 3: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/3.jpg)
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
First Last Birth_Date Immigration_Date
Ola Hansen 1989/09/01 1992/08/31
Han Shin-Cho 1987/07/17 NULL
Joe NULL 1990/04/21 1999/11/23
![Page 4: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/4.jpg)
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
![Page 5: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/5.jpg)
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 PersonWHERE (age < 25) AND (height > 6 OR weight > 190)
SELECT *FROM PersonWHERE (age < 25) AND (height > 6 OR weight > 190)
E.g.age=20height=NULLweight=200
![Page 6: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/6.jpg)
6
Three-Valued Logic
Unexpected behavior:
Some Persons are not included !
SELECT *FROM PersonWHERE age < 25 OR age >= 25
SELECT *FROM PersonWHERE age < 25 OR age >= 25
![Page 7: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/7.jpg)
7
Working with Null Values
Can test for NULL explicitly:– x IS NULL– x IS NOT NULL
Now it includes all Persons
SELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULL
SELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULL
![Page 8: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/8.jpg)
8
Explicit JoinsExplicit joins in SQL:
Product(name, category) Purchase(prodName, store)
Same as:
But Products that never sold will be lost !
SELECT Product.name, Purchase.storeFROM Product JOIN Purchase ON Product.name = Purchase.prodName
SELECT Product.name, Purchase.storeFROM Product JOIN Purchase ON Product.name = Purchase.prodName
SELECT Product.name, Purchase.storeFROM Product, PurchaseWHERE Product.name = Purchase.prodName
SELECT Product.name, Purchase.storeFROM Product, PurchaseWHERE Product.name = Purchase.prodName
![Page 9: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/9.jpg)
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
SELECT Product.name, Purchase.store FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodName
![Page 10: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/10.jpg)
10
Name Category
Gizmo gadget
Camera Photo
OneClick Photo
ProdName Store
Gizmo Wiz
Camera Ritz
Camera Wiz
Name Store
Gizmo Wiz
Camera Ritz
Camera Wiz
OneClick NULL
Product Purchase
![Page 11: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/11.jpg)
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
![Page 12: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/12.jpg)
12
Modifying the Database
Three kinds of modifications• Insertions• Deletions• Updates
Sometimes they are all called “updates”
![Page 13: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/13.jpg)
13
InsertionsGeneral form:
Missing attributes NULL.May drop attribute names if give them in order.
INSERT INTO R(A1,…., An) VALUES (v1,…., vn) INSERT INTO R(A1,…., An) VALUES (v1,…., vn)
INSERT INTO Purchase(buyer, seller, product, store) VALUES (‘Joe’, ‘Fred’, ‘wakeup-clock-espresso-machine’, ‘The Sharper Image’)
INSERT INTO Purchase(buyer, seller, product, store) VALUES (‘Joe’, ‘Fred’, ‘wakeup-clock-espresso-machine’, ‘The Sharper Image’)
Example: Insert a new purchase to the database:
![Page 14: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/14.jpg)
14
Insertions
INSERT INTO PRODUCT(name)
SELECT DISTINCT Purchase.product FROM Purchase WHERE Purchase.date > “10/26/01”
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
The number of columns must be identical
![Page 15: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/15.jpg)
15
Insertion: an Example
prodName is foreign key in Product.name
Suppose database got corrupted and we need to fix it:
name listPrice category
gizmo 100 gadgets
prodName buyerName price
camera John 200
gizmo Smith 80
camera Smith 225
Task: insert in Product all prodNames from Purchase
Product
Product(name, listPrice, category)Purchase(prodName, buyerName, price)
Product(name, listPrice, category)Purchase(prodName, buyerName, price)
Purchase
![Page 16: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/16.jpg)
16
Insertion: an Example
INSERT INTO Product(name)
SELECT DISTINCT prodName FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)
INSERT INTO Product(name)
SELECT DISTINCT prodName FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)
name listPrice category
gizmo 100 Gadgets
camera - -
![Page 17: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/17.jpg)
17
Insertion: an Example
INSERT INTO Product(name, listPrice)
SELECT DISTINCT prodName, price FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)
INSERT INTO Product(name, listPrice)
SELECT DISTINCT prodName, price FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product)
name listPrice category
gizmo 100 Gadgets
camera 200 -
camera ?? 225 ?? - ERROR!
![Page 18: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/18.jpg)
18
Deletions
DELETE FROM PURCHASE
WHERE seller = ‘Joe’ AND product = ‘Brooklyn Bridge’
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+k times in a relation.
Example:
![Page 19: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/19.jpg)
19
Updates
UPDATE ProductSET listPrice= listPrice/2WHERE Product.name IN (SELECT product FROM Purchase WHERE Date =‘Oct, 25, 1999’);
UPDATE ProductSET listPrice= listPrice/2WHERE Product.name IN (SELECT product FROM Purchase WHERE Date =‘Oct, 25, 1999’);
Example:
![Page 20: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/20.jpg)
20
Updates Using Exists
UPDATE ProductSET listPrice = (SELECT AVG(price)
FROM PurchaseWHERE prodName=name)
WHERE EXISTS (SELECT *FROM PurchaseWHERE prodName=name);
UPDATE ProductSET listPrice = (SELECT AVG(price)
FROM PurchaseWHERE prodName=name)
WHERE EXISTS (SELECT *FROM PurchaseWHERE prodName=name);
Example:
![Page 21: 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](https://reader035.vdocuments.us/reader035/viewer/2022062417/55162c7a550346b2068b4ae2/html5/thumbnails/21.jpg)
Table Modifications - Summary
21
INSERT INTO table_name (column1, column2,...)VALUES (value1, value2, ...)
INSERT INTO table_name (column1, column2,...)VALUES (value1, value2, ...)
INSERT INTO table_name (column1, column2,...)SELECT …
INSERT INTO table_name (column1, column2,...)SELECT …
DELETE FROM table_name WHERE cond
DELETE FROM table_name WHERE cond
UPDATE table_nameSET column1=expr1, column2=expr2,...WHERE cond
UPDATE table_nameSET column1=expr1, column2=expr2,...WHERE cond