Advanced SQL Concepts and Handling NULL Values
Explore advanced SQL concepts such as INTERSECT and EXCEPT, Three-Valued Logic, and working with NULL values. Understand how to deal with unexpected behaviors in SQL queries due to NULL values. Learn about explicit joins and left outer joins in SQL for more efficient data retrieval.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
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 FROM S) SELECT R.A, R.B FROM R WHERE NOT EXISTS(SELECT * FROM S WHERE R.A=S.A and R.B=S.B) 2
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 Ola Han Joe Last Hansen Shin-Cho NULL Birth_Date 1989/09/01 1987/07/17 1990/04/21 Immigration_Date 1992/08/31 NULL 1999/11/23 3
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 = UNKNOWN = TRUE = 0 0.5 1 4
Three-Valued Logic C1 AND C2 = min(C1, C2) C1 OR C2 = max(C1, C2) NOT C1 = 1 C1 SELECT * FROM Person WHERE (age < 25) AND (height > 6 OR weight > 190) E.g. age=20 height=NULL weight=200 Rule in SQL: include only tuples that yield TRUE 5
Three-Valued Logic Unexpected behavior: SELECT * FROM Person WHERE age < 25 OR age >= 25 Some Persons are not included ! 6
Working with Null Values Can test for NULL explicitly: x IS NULL x IS NOT NULL SELECT * FROM Person WHERE age < 25 OR age >= 25 OR age IS NULL Now it includes all Persons 7
Explicit Joins Explicit joins in SQL: Product(name, category) Purchase(prodName, store) SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Same as: SELECT Product.name, Purchase.store FROM Product, Purchase WHERE Product.name = Purchase.prodName But Products that never sold will be lost ! 8
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 9
Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Camera Ritz Camera Wiz OneClick NULL 10
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 11
Modifying the Database Three kinds of modifications Insertions Deletions Updates Sometimes they are all called updates 12
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. 13
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 The number of columns must be identical 14
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 prodName buyerName price name listPrice category camera John 200 gizmo Smith 80 gizmo 100 gadgets camera Smith 225 Task: insert in Product all prodNames from Purchase 15
Insertion: an Example INSERT INTO Product(name) SELECT DISTINCT prodName FROM Purchase WHERE prodName NOT IN (SELECT name FROM Product) name listPrice category gizmo 100 Gadgets camera - - 16
Insertion: an Example 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 - ERROR! camera ?? 225 ?? - 17
Deletions Example: DELETE FROM PURCHASE WHERE seller = Joe AND product = Brooklyn Bridge Factoid about SQL: there is no way to delete only x occurrences of a tuple that appears x+k times in a relation. 18
Updates Example: UPDATE Product SET listPrice= listPrice/2 WHERE Product.name IN (SELECT product FROM Purchase WHERE Date = Oct, 25, 1999 ); 19
Updates Using Exists Example: UPDATE Product SET listPrice = (SELECT AVG(price) FROM Purchase WHERE prodName=name) WHERE EXISTS (SELECT * FROM Purchase WHERE prodName=name); 20
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 UPDATE table_name SET column1=expr1, column2=expr2,... WHERE cond 21
