DOC PREVIEW
UW CSE 444 - Lecture Notes

This preview shows page 1-2-3-18-19-37-38-39 out of 39 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 39 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 39 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 39 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 39 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 39 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 39 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 39 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 39 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 39 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Lecture 3: SQL and Project IssuesWelcome to an Award Course!The ProjectYour NumbersOutlineFirst Unintuitive SQLismRenaming ColumnsUnion, Intersection, DifferencePowerPoint PresentationConserving DuplicatesSubqueriesSlide 12Subqueries Returning RelationsSlide 14Removing DuplicatesSlide 16Slide 17Question for Database Fans and their FriendsConditions on TuplesCorrelated QueriesComplex Correlated QueryAggregationAggregation: CountSlide 24Simple AggregationSimple AggregationsGrouping and AggregationSlide 28First compute the FROM-WHERE clauses (date > “10/1”) then GROUP BY product:Then, aggregateGROUP BY v.s. Nested QueriesAnother ExampleHAVING ClauseGeneral form of Grouping and AggregationSlide 35Slide 36Slide 37Slide 38Slide 391Lecture 3: SQL and Project IssuesFriday, January 10th, 20032Welcome to an Award Course!•Who I am.•Where I was.•Our Award.•The course project•Down to business3The Project•3 Phases.•I’ll tell you about them as they happen.•Phase 1:–You get a number between 1 and 3.–The number determines one of 3 domains.–You create a schema, and some data, and send it to us (by Wednesday)–You implement a database with a simple web interface (end of week 3).4Your Numbers•A-H: Domain 1 - Inventory•I-ME(Mehlhorn): Domain 2 - Billing•MO(Moore) - Z: Domain 3 - Shipping5Outline•Unions, intersections, differences(6.2.5, 6.4.2)•Subqueries (6.3)•Aggregations (6.4.3 – 6.4.6)Hint for reading the textbook: read the entire chapter 6 !Reading assignment from “SQL for Nerds”: chapter 4, “Morecomplex queries” (you will find it very useful for subqueries)6First Unintuitive SQLismSELECT DISTINCT R.AFROM R, S, TWHERE R.A=S.A OR R.A=T.ASELECT DISTINCT R.AFROM R, S, TWHERE R.A=S.A OR R.A=T.ALooking for R  (S  T)But what happens if T is empty?7Renaming ColumnsPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiSELECT Pname AS prodName, Price AS askPriceFROM ProductWHERE Price > 100SELECT Pname AS prodName, Price AS askPriceFROM ProductWHERE Price > 100ProductprodName askPriceSingleTouch $149.99MultiTouch $203.99Query withrenaming8Union, Intersection, Difference(SELECT name FROM Person WHERE City=“Seattle”) UNION(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)(SELECT name FROM Person WHERE City=“Seattle”) UNION(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)Similarly, you can use INTERSECT and EXCEPT.You must have the same attribute names (otherwise: rename).9(SELECT DISTINCT R.A FROM R) INTERSECT ( (SELECT S.A FROM S) UNION (SELECT T.A FROM T))(SELECT DISTINCT R.A FROM R) INTERSECT ( (SELECT S.A FROM S) UNION (SELECT T.A FROM T))10Conserving Duplicates(SELECT name FROM Person WHERE City=“Seattle”) UNION ALL(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)(SELECT name FROM Person WHERE City=“Seattle”) UNION ALL(SELECT name FROM Person, Purchase WHERE buyer=name AND store=“The Bon”)11SubqueriesA subquery producing a single value:In this case, the subquery returns one value.If it returns more, it’s a run-time error.SELECT Purchase.productFROM PurchaseWHERE buyer = (SELECT name FROM Person WHERE ssn = ‘123456789‘);SELECT Purchase.productFROM PurchaseWHERE buyer = (SELECT name FROM Person WHERE ssn = ‘123456789‘);12Can say the same thing without a subquery:This is equivalent to the previous one when the ssn is a keyand ‘123456789’ exists in the database;otherwise they are different.SELECT Purchase.productFROM Purchase, PersonWHERE buyer = name AND ssn = ‘123456789‘SELECT Purchase.productFROM Purchase, PersonWHERE buyer = name AND ssn = ‘123456789‘13Subqueries Returning Relations SELECT Company.name FROM Company, Product WHERE Company.name=Product.maker AND Product.name IN (SELECT Purchase.product FROM Purchase WHERE Purchase .buyer = ‘Joe Blow‘); SELECT Company.name FROM Company, Product WHERE Company.name=Product.maker AND Product.name IN (SELECT Purchase.product FROM Purchase WHERE Purchase .buyer = ‘Joe Blow‘);Find companies who manufacture products bought by Joe Blow.Here the subquery returns a set of values: no moreruntime errors.14Subqueries Returning Relations SELECT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’Equivalent to:Is this query equivalent to the previous one ?Beware of duplicates !15Removing Duplicates SELECT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT DISTINCT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT DISTINCT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’Multiple copies Single copies16Removing Duplicates SELECT DISTINCT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND Purchase.buyer = ‘Joe Blow’ SELECT DISTINCT Company.name FROM Company, Product, Purchase WHERE Company.name= Product.maker AND Product.name = Purchase.product AND


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?