DOC PREVIEW
UW CSE 444 - Lecture Notes

This preview shows page 1-2-16-17-18-33-34 out of 34 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 34 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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

UW CSE 444 - Lecture Notes

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Lecture Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Lecture Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lecture Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?