Introduction to Database Systems CSE 444 Lecture 2: SQL h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7Announcements Project717&7Hw717are7posted7on7class7website7 Project717(SQL)7due7in7two7weeks7 Homework717(E/R7models7etc)7due7in7three7weeks7 Remember:73me7goes7by7very7fast!7Start7early!7 On7the7course7website7you7will7find7 Recommended7readings7from7the7book7 PDF7of7lecture7notes7(~morning7of7class)7 Other717h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7Outline Data!in7SQL7 Simple7Queries7in7SQL7(6.1)7 Queries7with7more7than7one7rela3on7(6.2)7 Subqueries7(6.3)727h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7Structured Query Language (SQL) Data7Defini3on7Language7(DDL)7 Create/alter/delete7tables7and7their7a"ributes7 Following7lectures...7 Data7Manipula3on7Language7(DML)7 Query7one7or7more7tables7–7discussed7next7!7 Insert/delete/modify7tuples7in7tables737h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7Tables in SQL 47h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi Product Attribute names Table name Key Tuple / row AttributeData Types in SQL Atomic7types7 Character7strings:7CHAR(20),7VARCHAR(50)7 Can7be7of7fixed7or7variable7length7 Numbers:7INT,7BIGINT,7SMALLINT,7FLOAT7 Others:7MONEY,7DATETIME,7…7 Record7(aka7tuple)7 Has7atomic7a"ributes7 Table7(aka7rela3on)7 A7set!of7tuples757Book Sec. 2.3.2 h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7Simple SQL Query 67h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT * FROM Product WHERE category='Gadgets' Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SelectionSimple SQL Query 77h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7PName Price Manufacturer SingleTouch $149.99 Canon MultiTouch $203.99 Hitachi PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT pName, price, manufacturer FROM Product WHERE price > 100 Product Selection & ProjectionDetails SQL7is7case7insensi3ve7 SELECT7=7Select7=7select7 Product7=77product7 but7'Sea"le'7≠7'sea"le'7(in7general)7 Constants7must7use7single7quotes7 'abc'77h7yes7 "abc"7h7no787h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7Eliminating Duplicates 97h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7Category Gadgets Gadgets Photography Household Category Gadgets Photography Household SELECT DISTINCT category FROM Product SELECT category FROM Product Set vs. Bag semantics PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks PowerGizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi ProductOrdering the Results Ties in price attribute broken by pname attribute Ordering is ascending by default. Descending:7107h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7SELECT pName, price, manufacturer FROM Product WHERE category='Gadgets' and price > 10 ORDER BY price, pName ... ORDER BY price, pname DESC117PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT category FROM Product ORDER BY pName SELECT DISTINCT category FROM Product ORDER BY category SELECT DISTINCT category FROM Product ORDER BY pName Product ? ? ?127PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi Category Gadgets Household Photography Category Gadgets Household Gadgets Photography Syntax error* SELECT category FROM Product ORDER BY pName SELECT DISTINCT category FROM Product ORDER BY category SELECT DISTINCT category FROM Product ORDER BY pName Product x *7Error7actually7happens7during7"seman3c"7analysis7of7query.7Keys and Foreign Keys 137h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi Product Company CName StockPrice Country GizmoWorks 25 USA Canon 65 Japan Hitachi 15 Japan Foreign key Key KeyJoins 147h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7 Q: Find all products under $200 manufactured in Japan; return their names and prices! SELECT pName, price FROM Product, Company WHERE manufacturer=cName and country='Japan' and price <= 200 Join b/w Product and Company Product (pName, price, category, manufacturer) Company (cName, stockPrice, country)Joins 157h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi Product Company CName StockPrice Country GizmoWorks 25 USA Canon 65 Japan Hitachi 15 Japan PName Price SingleTouch $149.99 SELECT pName, price FROM Product, Company WHERE manufacturer=cName and country='Japan' and price <= 200Tuple Variables 167SELECT DISTINCT pName, address FROM Person, Company WHERE works_for = cName SELECT DISTINCT Person.pName, Company.address FROM Person, Company WHERE Person.works_for = Company.cName SELECT DISTINCT X.pName, Y.address FROM Person as X, Company as Y WHERE X.works_for = Y.cName which address? h"p://www.cs.washington.edu/educa3on/courses/cse444/11wi/7"as" is optional Person (pName, address, works_for) Company (cName, address xIn Class
View Full Document
Unlocking...