DOC PREVIEW
UW CSE 444 - SQL

This preview shows page 1-2-17-18-19-35-36 out of 36 pages.

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

Unformatted text preview:

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

UW CSE 444 - SQL

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 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 SQL
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 SQL 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 SQL 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?