1Lecture 04: SQLWednesday, October 4, 20062Outline• The Project• Nulls (6.1.6)• Outer joins (6.3.8)• Database Modifications (6.5)3The Project• Application:– Boutique online music and book store• Project:– Create database, access through a Web interface– Import real data and develop inventory logic– Customer checkout– Advanced functionality (TBD)4The Project• Team:– Two people– Find partner by Friday• Tools:– SQL Server 2005– Visual Studio 2005– C# 2.0– ASP.NET 2.05The ProjectPhase 1: posted today, due October 18• Find partner by Friday (“Phase 0”)• Create a schema• Populate the database: fake data for now• Access through a simple Web interface6NULLS in SQL• Whenever we don’t have a value, we can put a NULL• Can mean many things:– Value does not exists– Value exists but is unknown– Value not applicable– Etc.• The schema specifies for each attribute if can be null (nullable attribute) or not• How does SQL cope with tables that have NULLs ?7Null 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 = 0UNKNOWN = 0.5TRUE = 18Null Values• C1 AND C2 = min(C1, C2)• C1 OR C2 = max(C1, C2)• NOT C1 = 1 – C1Rule in SQL: include only tuples that yield TRUESELECT *FROM PersonWHERE (age < 25) AND (height > 6 OR weight > 190)SELECT *FROM PersonWHERE (age < 25) AND (height > 6 OR weight > 190)E.g.age=20heigth=NULLweight=2009Null ValuesUnexpected behavior:Some Persons are not included !SELECT *FROM PersonWHERE age < 25 OR age >= 25SELECT *FROM PersonWHERE age < 25 OR age >= 2510Null ValuesCan test for NULL explicitly:– x IS NULL– x IS NOT NULLNow it includes all PersonsSELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULLSELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULL11OuterjoinsExplicit joins in SQL = “inner joins”:Product(name, category)Purchase(prodName, store)SELECT Product.name, Purchase.storeFROM Product JOIN Purchase ONProduct.name = Purchase.prodNameSELECT Product.name, Purchase.storeFROM Product JOIN Purchase ONProduct.name = Purchase.prodNameSELECT Product.name, Purchase.storeFROM Product, PurchaseWHERE Product.name = Purchase.prodNameSELECT Product.name, Purchase.storeFROM Product, PurchaseWHERE Product.name = Purchase.prodNameSame as:But Products that never sold will be lost !12OuterjoinsLeft outer joins in SQL:Product(name, category)Purchase(prodName, store)SELECT Product.name, Purchase.storeFROM Product LEFT OUTER JOIN Purchase ONProduct.name = Purchase.prodNameSELECT Product.name, Purchase.storeFROM Product LEFT OUTER JOIN Purchase ONProduct.name = Purchase.prodName13PhotoOneClickPhotoCameragadgetGizmoCategoryNameWizCameraRitzCameraWizGizmoStoreProdNameNULLOneClickWizCameraRitzCameraWizGizmoStoreNameProduct Purchase14ApplicationCompute, for each product, the total number of sales in ‘September’Product(name, category)Purchase(prodName, month, store)SELECT Product.name, count(*)FROM Product, Purchase WHERE Product.name = Purchase.prodNameand Purchase.month = ‘September’GROUP BY Product.nameSELECT Product.name, count(*)FROM Product, Purchase WHERE Product.name = Purchase.prodNameand Purchase.month = ‘September’GROUP BY Product.nameWhat’s wrong ?15ApplicationCompute, for each product, the total number of sales in ‘September’Product(name, category)Purchase(prodName, month, store)SELECT Product.name, count(*)FROM Product LEFT OUTER JOIN Purchase ONProduct.name = Purchase.prodNameand Purchase.month = ‘September’GROUP BY Product.nameSELECT Product.name, count(*)FROM Product LEFT OUTER JOIN Purchase ONProduct.name = Purchase.prodNameand Purchase.month = ‘September’GROUP BY Product.nameNow we also get the products who sold in 0 quantity16Outer Joins• Left outer join:– Include the left tuple even if there’s no match• Right outer join:– Include the right tuple even if there’s no match• Full outer join:– Include the both left and right tuples even if there’s no match17Modifying the DatabaseThree kinds of modifications• Insertions• Deletions• UpdatesSometimes they are all called “updates”18InsertionsGeneral form:Missing attribute → 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:19InsertionsINSERT INTO PRODUCT(name)SELECT DISTINCT Purchase.productFROM PurchaseWHERE Purchase.date > “10/26/01”INSERT INTO PRODUCT(name)SELECT DISTINCT Purchase.productFROM PurchaseWHERE Purchase.date > “10/26/01”The query replaces the VALUES keyword.Here we insert many tuples into PRODUCT20Insertion: an ExampleprodName is foreign key in Product.nameSuppose database got corrupted and we need to fix it:gadgets100gizmocategorylistPricename225Smithcamera80Smithgizmo200JohncamerapricebuyerNameprodNameTask: insert in Product all prodNames from PurchaseProductProduct(name, listPrice, category)Purchase(prodName, buyerName, price)Product(name, listPrice, category)Purchase(prodName, buyerName, price)Purchase21Insertion: an ExampleINSERT INTO Product(name)SELECT DISTINCT prodNameFROM PurchaseWHERE prodName NOT IN (SELECT name FROM Product)INSERT INTO Product(name)SELECT DISTINCT prodNameFROM PurchaseWHERE prodName NOT IN (SELECT name FROM Product)--cameraGadgets100gizmocategorylistPricename22Insertion: an ExampleINSERT INTO Product(name, listPrice)SELECT DISTINCT prodName, priceFROM PurchaseWHERE prodName NOT IN (SELECT name FROM Product)INSERT INTO Product(name, listPrice)SELECT DISTINCT prodName, priceFROM PurchaseWHERE prodName NOT IN (SELECT name FROM Product)-225 ??camera ??-200cameraGadgets100gizmocategorylistPricenameDepends on the implementation23DeletionsDELETE FROM PURCHASEWHERE seller = ‘Joe’ ANDproduct = ‘Brooklyn Bridge’DELETE FROM PURCHASEWHERE seller = ‘Joe’ ANDproduct = ‘Brooklyn Bridge’Factoid about SQL: there is no way to delete only a singleoccurrence of a tuple that appears twicein a
View Full Document