Introduction to Database Systems CSE 444 Lecture 2: SQL 1 Magda Balazinska - CSE 444, Fall 20102 Announcements • Project 1 & Hw 1 are posted on class website – Project 1 due in two weeks – Homework 1 due in three weeks – Remember: time goes by very fast! Start early! • On the course website you will find – PDF of lecture notes – Recommended readings from the book Magda Balazinska - CSE 444, Fall 20103 Outline • Data in SQL • Simple Queries in SQL (6.1) • Queries with more than one relation (6.2) • Subqueries (6.3) Magda Balazinska - CSE 444, Fall 20104 Structured Query Language (SQL) • 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 tables Magda Balazinska - CSE 444, Fall 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 Magda Balazinska - CSE 444, Fall 20106 Data Types in SQL • Atomic types – Character strings: CHAR(20), VARCHAR(50) • Can be of fixed or variable length – Numbers: INT, BIGINT, SMALLINT, FLOAT – Others: MONEY, DATETIME, … • Record (aka tuple) – Has atomic attributes • Table (aka relation) – A set of tuples Magda Balazinska - CSE 444, Fall 2010 Book Sec. 2.3.27 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” Magda Balazinska - CSE 444, Fall 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” Magda Balazinska - CSE 444, Fall 20109 Details • SQL is case insensitive – SELECT = Select = select – Product = product – BUT ‘Seattle’ ≠ ‘seattle’ (in general) • Constants must use single quotes – ‘abc’ - yes – “abc” - no Magda Balazinska - CSE 444, Fall 201010 Eliminating Duplicates SELECT DISTINCT category FROM Product Compare to: SELECT category FROM Product Category Gadgets Gadgets Photography Household Category Gadgets Photography Household Magda Balazinska - CSE 444, Fall 201011 Ordering the Results SELECT pname, price, manufacturer FROM Product WHERE category=‘gadgets’ AND price > 10 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. Magda Balazinska - CSE 444, Fall 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 ? ?13 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 Category Gadgets Household Photography Category Gadgets Household Gadgets Photography Error14 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 key15 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 Magda Balazinska - CSE 444, Fall 201016 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 Magda Balazinska - CSE 444, Fall 201017 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.cname18 In Class Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all Chinese companies that manufacture products in the ‘toy’ category SELECT cname FROM WHERE Magda Balazinska - CSE 444, Fall 201019 In Class Product (pname, price, category, manufacturer) Company (cname, stockPrice, country) Find all Chinese companies that manufacture products in the ‘toy’ category SELECT DISTINCT cname FROM Product, Company WHERE country = ‘China’ AND category = ‘toy’ AND manufacturer = cname Magda Balazinska - CSE 444, Fall 201020 In Class Product (pname, price,
View Full Document
Unlocking...