Lecture 02: SQLAdministriviaToday’s Reading AssigmentOutlineSQL IntroductionSQLTables in SQLTables ExplainedData Types in SQLSlide 10SQL QuerySimple SQL QuerySlide 13NotationDetailsThe LIKE operatorEliminating DuplicatesOrdering the ResultsPowerPoint PresentationKeys and Foreign KeysJoinsSlide 22More JoinsA Subtlety about JoinsSlide 25Tuple VariablesMeaning (Semantics) of SQL QueriesAn Unintuitive QuerySubqueries Returning RelationsSlide 30Removing DuplicatesSlide 32Question for Database Fans and their FriendsCorrelated QueriesComplex Correlated QueryReading Assignment for Monday1Lecture 02: SQLFriday, September 29, 20062Administrivia•Homework 1 is out. Due: Wed., Oct. 11•Did you login on IISQLSRV ?•Did you change your password ?•Did you subscribe to CSE444 ?3Today’s Reading Assigment•Did you read it ?•What does ACID mean ?A = atomicityC = consistencyI = isolationD = durability4Outline•Data in SQL•Simple Queries in SQL (6.1)•Queries with more than one relation (6.2)5SQL IntroductionStandard language for querying and manipulating data Structured Query LanguageMany standards out there: • ANSI SQL, SQL92 (a.k.a. SQL2), SQL99 (a.k.a. SQL3), ….• Vendors support various subsets: watch for fun discussions in class !6SQL•Data Definition Language (DDL)–Create/alter/delete tables and their attributes–Following lectures...•Data Manipulation Language (DML)–Query one or more tables – discussed next !–Insert/delete/modify tuples in tables7Tables in SQLPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiProductAttribute namesTable nameTuples or rows8Tables Explained•The schema of a table is the table name and its attributes:Product(PName, Price, Category, Manfacturer)•A key is an attribute whose values are unique;we underline a keyProduct(PName, Price, Category, Manfacturer)9Data Types in SQL•Atomic types:–Characters: CHAR(20), VARCHAR(50)–Numbers: INT, BIGINT, SMALLINT, FLOAT–Others: MONEY, DATETIME, …•Every attribute must have an atomic type–Hence tables are flat–Why ?10Tables Explained•A tuple = a record–Restriction: all attributes are of atomic type•A table = a set of tuples–Like a list…–…but it is unorderd: no first(), no next(), no last().11SQL QueryBasic form: (plus many many more bells and whistles) SELECT <attributes> FROM <one or more relations> WHERE <conditions> SELECT <attributes> FROM <one or more relations> WHERE <conditions>12Simple SQL QueryPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiSELECT *FROM ProductWHERE category=‘Gadgets’SELECT *FROM ProductWHERE category=‘Gadgets’ProductPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorks“selection”13Simple SQL QueryPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiSELECT PName, Price, ManufacturerFROM ProductWHERE Price > 100SELECT PName, Price, ManufacturerFROM ProductWHERE Price > 100ProductPName Price ManufacturerSingleTouch $149.99 CanonMultiTouch $203.99 Hitachi“selection” and“projection”14NotationProduct(PName, Price, Category, Manfacturer)Answer(PName, Price, Manfacturer)Input SchemaOutput SchemaSELECT PName, Price, ManufacturerFROM ProductWHERE Price > 100SELECT PName, Price, ManufacturerFROM ProductWHERE Price > 10015Details•Case insensitive:–Same: SELECT Select select–Same: Product product–Different: ‘Seattle’ ‘seattle’•Constants:–‘abc’ - yes–“abc” - no16The LIKE operator•s LIKE p: pattern matching on strings•p may contain two special symbols:–% = any sequence of characters–_ = any single characterSELECT *FROM ProductsWHERE PName LIKE ‘%gizmo%’SELECT *FROM ProductsWHERE PName LIKE ‘%gizmo%’17Eliminating DuplicatesSELECT DISTINCT categoryFROM ProductSELECT DISTINCT categoryFROM ProductCompare to:SELECT categoryFROM ProductSELECT categoryFROM ProductCategoryGadgetsGadgetsPhotographyHouseholdCategoryGadgetsPhotographyHousehold18Ordering the ResultsSELECT pname, price, manufacturerFROM ProductWHERE category=‘gizmo’ AND price > 50ORDER BY price, pnameSELECT pname, price, manufacturerFROM ProductWHERE category=‘gizmo’ AND price > 50ORDER BY price, pnameTies are broken by the second attribute on the ORDER BY list, etc.Ordering is ascending, unless you specify the DESC keyword.19SELECT CategoryFROM ProductORDER BY PNameSELECT CategoryFROM ProductORDER BY PNamePName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household Hitachi?SELECT DISTINCT categoryFROM ProductORDER BY categorySELECT DISTINCT categoryFROM ProductORDER BY categorySELECT DISTINCT categoryFROM ProductORDER BY PNameSELECT DISTINCT categoryFROM ProductORDER BY PName??20Keys and Foreign KeysPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiProductCompanyCName StockPrice CountryGizmoWorks 25 USACanon 65 JapanHitachi 15 JapanKeyForeignkey21Joins Product (pname, price, category, manufacturer)Company (cname, stockPrice, country)Find all products under $200 manufactured in Japan;return their names and prices. SELECT PName, PriceFROM Product, CompanyWHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200SELECT PName, PriceFROM Product, CompanyWHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200Joinbetween Productand Company22JoinsPName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiProductCompanyCname StockPrice CountryGizmoWorks 25 USACanon 65 JapanHitachi 15 JapanPName PriceSingleTouch $149.99SELECT PName, PriceFROM Product, CompanyWHERE Manufacturer=CName AND Country=‘Japan’ AND Price <= 200SELECT PName, PriceFROM
View Full Document