Lecture 2: SQLAgendaThe Study of DBMSCourse Outline (may vary slightly)OutlineThe Relational Model (Codd)SQL IntroductionSQLData in SQLData Types in SQLTables ExplainedSlide 12SQL QuerySimple SQL QuerySlide 15A Notation for SQL QueriesSelectionsThe LIKE operatorEliminating DuplicatesOrdering the ResultsSlide 21Slide 22Joins in SQLJoinsSlide 25Slide 26Slide 27When are two tables related?Disambiguating AttributesTuple VariablesSlide 31Meaning (Semantics) of SQL QueriesSlide 33First Unintuitive SQLismExercises1Lecture 2: SQLWednesday, January 7, 20042Agenda•Leftovers from Monday•The relational model (very quick)•SQL•Homework #1 given out later this week.•Project, phase 1, details coming.•Enrollment is closed (sorry!)3The Study of DBMS•Several aspects:–Modeling and design of databases–Database programming: querying and update operations–Database implementation•DBMS study cuts across many fields of Computer Science: OS, languages, AI, Logic, multimedia, theory...4Course Outline (may vary slightly)Part I•SQL (Chapter 6)•The relational data model (Chapter 3)•Database design (Chapters 2, 3, 7)•XML, XPath, XQueryMidtermPart II•Data storage, indexes (Chapters 11-13)•Query execution and optimization (Chapter 15,16)•Data integration5Outline•Data in SQL•Simple Queries in SQL (6.1)•Queries with more than one relation (6.2)Reading assignment:Chapter 3, “Simple Queries” from SQL for Web Nerds, by Philip Greenspunhttp://philip.greenspun.com/sql/6The Relational Model (Codd)PName Price Category ManufacturerGizmo $19.99 Gadgets GizmoWorksPowergizmo $29.99 Gadgets GizmoWorksSingleTouch $149.99 Photography CanonMultiTouch $203.99 Household HitachiProductAttribute namesTable nameTuples or rows7SQL 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 of these• What we discuss is common to all of them8SQL•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•Transact-SQL–Idea: package a sequence of SQL statements server–Won’t discuss in class9Data in SQL1. Atomic types, a.k.a. data types2. Tables built from atomic typesUnlike XML, no nested tables, only flat tables are allowed!–We will see later how to decompose complex structures into multiple flat tables10Data Types in SQL•Characters: –CHAR(20) -- fixed length–VARCHAR(40) -- variable length•Numbers:–BIGINT, INT, SMALLINT, TINYINT–REAL, FLOAT -- differ in precision–MONEY•Times and dates: –DATE–DATETIME -- SQL Server•Others... All are simple11Tables Explained•A tuple = a record–Restriction: all attributes are of atomic type•A table = a set of tuples–Like a list…–…but it is unordered: no first(), no next(), no last().12Tables 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)13SQL QueryBasic form: (plus many many more bells and whistles) SELECT attributes FROM relations (possibly multiple) WHERE conditions (selections) SELECT attributes FROM relations (possibly multiple) WHERE conditions (selections)14Simple 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”15Simple 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”16A Notation for SQL QueriesSELECT PName, Price, ManufacturerFROM ProductWHERE Price > 100SELECT PName, Price, ManufacturerFROM ProductWHERE Price > 100Product(PName, Price, Category, Manfacturer)Answer(PName, Price, Manfacturer)Input SchemaOutput Schema17SelectionsWhat goes in the WHERE clause:• x = y, x < y, x <= y, etc–For numbers, they have the usual meanings–For CHAR and VARCHAR: lexicographic ordering•Expected conversion between CHAR and VARCHAR–For dates and times, what you expect...•Pattern matching on strings...18The LIKE operator•s LIKE p: pattern matching on strings•p may contain two special symbols:–% = any sequence of characters–_ = any single characterProduct(PName, Price, Category, Manufacturer)Find all products whose name mentions ‘gizmo’:SELECT *FROM ProductsWHERE PName LIKE ‘%gizmo%’SELECT *FROM ProductsWHERE PName LIKE ‘%gizmo%’19Eliminating DuplicatesSELECT DISTINCT categoryFROM ProductSELECT DISTINCT categoryFROM ProductCompare to:SELECT categoryFROM ProductSELECT categoryFROM ProductCategoryGadgetsGadgetsPhotographyHouseholdCategoryGadgetsPhotographyHousehold20Ordering 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, pnameOrdering is ascending, unless you specify the DESC keyword.Ties are broken by the second attribute on the ORDER BY list, etc.21Ordering the ResultsSELECT 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?22Ordering the ResultsSELECT DISTINCT categoryFROM ProductORDER BY categorySELECT DISTINCT categoryFROM ProductORDER BY categoryCompare to:CategoryGadgetsHouseholdPhotographySELECT DISTINCT categoryFROM ProductORDER BY pnameSELECT DISTINCT
View Full Document
Unlocking...