1 Lecture'02:'SQL'Wednesday,'March'31st,'2010'Dan Suciu -- 444 Spring 20102 Accessing'S QL'Server'• Host:''IISQLSRV.cs.washington.edu'• AuthenCcaCon:'SQL'Server'AuthenCcaCon'• User:'[email protected]'• Password:''cse444login!'''(without'the'quotes)'• Change'your'password'!'Dan Suciu -- 444 Spring 20103 Outline'• Data!in'SQL'• Simple'Queries'in'SQL'(6.1)'• Queries'with'more'than'one'relaCon'(6.2)'• Subqueries'(6.3)'Dan Suciu -- 444 Spring 20104 SQL'• Data'DefiniCon'Language'(DDL)'– Create/alter/delete'tables'and'their'a[ributes'– Following'lectures...'• Data'Manipu laCon'Language'(DML)'– Query'one'or'more'tables'–'discussed'next'!'– Insert/delete/modify'tuples'in'tables'Dan Suciu -- 444 Spring 20105 Tables'in'SQL'PName 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 Tuples or rows Key Dan Suciu -- 444 Spring 20106 Data'Types'in'SQL'• Atomic'types:'– Characters:'CHAR(20),'VARCHAR(50)'– Numbers:'INT,'BIGINT,'SMALLINT,'FLOAT'– Others:'MONEY,'DATETIME ,'…'• Record'(aka'tu ple)'– Has'atomic'a[ributes'• Table'(relaCon)'– A'set'of'tuples'Dan Suciu -- 444 Spring 20107 Simple'SQL'Query'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 * FROM Product WHERE category=‘Gadgets’ Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks “selection” Dan Suciu -- 444 Spring 20108 Simple'SQL'Query'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 PName Price Manufacturer SingleTouch $149.99 Canon MultiTouch $203.99 Hitachi “selection” and “projection” Dan Suciu -- 444 Spring 20109 Details'• Case'insensiCve:'SELECT'='Select'='select'Product'=''product'BUT:'‘Sea[le’'≠'‘sea[le’'• Constants:'‘abc’''j'yes'“abc”'j'no'Dan Suciu -- 444 Spring 201010 EliminaCng'Duplicates'SELECT DISTINCT category FROM Product Compare to: SELECT category FROM Product Category Gadgets Gadgets Photography Household Category Gadgets Photography Household Dan Suciu -- 444 Spring 201011 Ordering'the'Results'SELECT pname, price, manufacturer FROM Product WHERE category=‘gizmo’ AND price > 50 ORDER BY price, pname Ties are broken by the second attribute on the ORDER BY list. Ordering is ascending, unless you specify the DESC keyword. Dan Suciu -- 444 Spring 201012 SELECT Category FROM Product ORDER BY PName 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 DISTINCT category FROM Product ORDER BY category SELECT DISTINCT category FROM Product ORDER BY PName ? ? Dan Suciu -- 444 Spring 201013 Keys'and'Foreign'Keys'PName 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 Key Foreign key Dan Suciu -- 444 Spring 201014 Joins'Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) 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 between Product and Company Dan Suciu -- 444 Spring 201015 Joins'PName 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 <= 200 Dan Suciu -- 444 Spring 201016 Tuple'Variables'SELECT DISTINCT pname, address FROM Person, Company WHERE worksfor = cname Which address ? Person(pname, address, worksfor) Company(cname, address) SELECT DISTINCT Person.pname, Company.address FROM Person, Company WHERE Person.worksfor = Company.cname SELECT DISTINCT x.pname, y.address FROM Person AS x, Company AS y WHERE x.worksfor = y.cname Dan Suciu -- 444 Spring 201017 In'Class'Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all Chinese companies that manufacture products both in the ‘toy’ category SELECT cname FROM WHERE Dan Suciu -- 444 Spring 201018 In'Class'Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all Chinese companies that manufacture products both in the ‘electronic’ and ‘toy’ categories SELECT cname FROM WHERE Dan Suciu -- 444 Spring 201019 Meaning'(SemanCcs)'of'SQL'Queries'SELECT'a1,'a2,'…,'ak'FROM''''R1'AS'x1,'R2'AS'x2,'…,'Rn'AS'xn'WHERE''CondiCons'Answer = {} for x1 in R1 do for x2 in R2 do ….. for xn in Rn do if Conditions then Answer = Answer ∪ {(a1,…,ak)} return Answer Dan Suciu -- 444 Spring 201020 SELECT DISTINCT R.A FROM R, S, T WHERE R.A=S.A OR R.A=T.A Using'the'Formal'SemanCcs'If S ≠ ∅ and T ≠ ∅ then returns R ∩ (S ∪ T) else returns ∅#What do these queries compute ? SELECT DISTINCT R.A FROM R, S WHERE R.A=S.A Returns R ∩ S Dan Suciu -- 444 Spring 201021 Joins'Introduce'Duplicates'Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all countries that manufacture some product in the ‘Gadgets’ category. SELECT Country FROM Product, Company WHERE Manufacturer=CName AND Category=‘Gadgets’ Dan Suciu -- 444 Spring 201022 Joins'Introduce'Duplicates'Name Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets
View Full Document
Unlocking...