SQL Data Manipulation (CB Chapter 5)SQLSQL is Declarative, not ProceduralSQL Data Manipulation LanguageBasic Select StatementSQL SELECT: Naïve implementationExample TranslationThree-valued LogicRemoving DuplicatesComplex Condition in WHEREMore WHERE conditionsSorting the ResultsCreating Calculated AttributesAggregate “Attributes”3 Ways to CountAggregation ExampleMore Aggregation ExamplesGrouped QueriesGrouped Query ExampleGrouping by Multiple FieldsHAVINGRevised SQL EvaluationHAVING exampleImplicit vs. Explicit JoinsNested Query (attribute)Nested Query (attribute) ExampleNested Query (attribute) in HAVINGNested Query (set)Nested Query (set) ExampleSlide 30Straight Set OperationsIntersection ExampleUnion ExampleSet Difference ExampleInserting into the DatabaseViews as MacrosUpdating the DatabaseDeleting from the DatabaseSQL Data Manipulation(CB Chapter 5)CPSC 356 DatabaseEllen WalkerHiram College(Includes figures from Database Systems by Connolly & Begg, © Addison Wesley 2002)SQL•Widely used (only?) standard query language for relational databases•Once SEQUEL (Structured English QUEry Language), now Structured Query Language•Still evolving (SQL-92, SQL:1999, SQL:2003)–Vendors have their own versions that (mostly) follow standards•Objectives–Easy to learn, easy to use–Create and modify the database and query from it•DDL defines database schemas•DML manipulates database instancesSQL is Declarative, not Procedural•Statements describe the desired results•Statements do not specify a sequence of operations to get those results•(Contrast with relational algebra)SQL Data Manipulation Language•Select•Insert•Delete•Update•Add clauses to specify constraints–From (tables)–Where (condition)Basic Select Statement•SELECT attributes FROM relations WHERE conditions•e.g. Create a table of hotel names, room numbers and room types for all hotelsSELECT hotelName, RoomNo, RoomTypeFROM Hotel, RoomWHERE Hotel.HotelNo = Room.HotelNo;SQL SELECT: Naïve implementation1. Make a temporary table consisting of the Cartesian Product of all the tables in the FROM clause2. Copy rows that match the condition in the WHERE clause (relational algebra select operation)3. Choose columns specified in SELECT statement (relational algebra project operation)Example TranslationSELECT P.NameFROM Professor P, Teaching TWHERE P.Id = T.ProfId AND T.Semester=‘F1994’Name(Id=ProfID & Semester=‘F1994’ (Professor x Teaching)OrName(Semester=‘F1994’ (Professor |x|Id=ProfID Teaching)Three-valued Logic•SELECT ... WHERE grade = ‘A’–If grade is A, expression is TRUE–If grade is B, C, D or F, expression is FALSE–If grade is NULL , expression is UNKNOWN•Once one value is UNKNOWN, the expression is–TRUE AND UNKNOWN = UNKNOWN–FALSE AND UNKNOWN = UNKNOWN–(same for OR and NOT)Removing Duplicates•SELECT hotelNo FROM Booking;–Returns as many hotelNo’s as bookings, including duplicates•SELECT DISTINCT hotelNo FROM Booking;–Returns a list of unique hotelNo’s (no more than the number of hotels!)Complex Condition in WHERE•Create a table of hotel names, room numbers and room types for all hotels but the RitzSELECT hotelName, roomNo, roomTypeFROM Hotel, RoomWHERE Hotel.hotelNo = Room.roomNo and hotelName < > ‘Ritz’;•Constants like ‘Ritz’ go in single quotes.•Operators include =, < >, < , >, < =, > = •AND, OR, and NOT (and parentheses) to combine expressionsMore WHERE conditionsSELECT staffNO, fName, lName, salaryFROM StaffWHERE……salary BETWEEN 20,000 and 30,000…position IN {‘Manager’, ‘Supervisor’}…fName LIKE {‘Al%n’} Allen or Alan or Allison ……lName LIKE {‘_ _ _ _’} all 4-letter names…mName IS NOT NULL has a middle nameSorting the Results•List all rooms, sorted by price (most expensive first)SELECT hotelName, roomNo, roomType, priceFROM Hotel, RoomWHERE Hotel.hotelNo = Room.hotelNoORDER BY price DESC;•Multiple sort keys can be specified, e.g.…ORDER BY lName, fName ASC;Creating Calculated Attributes•Get room numbers, starting date and length of stay for all rooms in the Ritz (Hotel #1).SELECT roomNo, DateFrom, (DateTo-DateFrom) AS lengthOfStayFROM BookingWHERE hotelNo = 1;•SQL supports the usual mathematical operations, also Now().•AS allows you to name a calculated attributeAggregate “Attributes”•Aggregate functions–COUNT–SUM–AVG–MIN–MAX•Used in SELECT clause•Operate on non-NULL values of the given attribute3 Ways to Count•COUNT (attribute)–Count the number of tuples that have non-NULL values of attribute•COUNT DISTINCT (attribute)–Count the number of different values of attribute that appear in the relation•COUNT (*)–Count the total number of tuples that appear in the relation (its cardinality)Aggregation Example•How many rooms does the Ritz have?SELECT count (*) as RitzRoomsFROM Hotel, RoomWHERE Hotel.hotelNo=Room.hotelNo and Hotel.hotelName=‘Ritz’More Aggregation Examples•What is the average price of all rooms in the database?SELECT AVG(Price) AS averageRoomPriceFROM Room;•How much is the least expensive room in the database?SELECT MIN(Price) AS minRitzPriceFROM Room;Grouped Queries•Results are grouped by one or more attributes specified in GROUP BY clause.•For each “group”, a single output tuple is generated.•Every attribute that is not aggregated must be mentioned in the GROUP BY clause, or must have the same value in every tuple in the group.Grouped Query Example•List the minimum and maximum room price for each hotelSELECT hotelName, MIN(price) as MinPrice, MAX(price) as MaxPriceFROM Hotel, RoomWHERE Hotel.hotelNo = Room.hotelNoGROUP BY HotelNameGrouping by Multiple Fields•Create a table of Hotel, Room, Number of Days Booked for all roomsSELECT hotelName, roomNo,SUM(dateTo – dateFrom) AS daysBookedFROM Hotel AS h, Booking AS bWHERE h.hotelNo = b.hotelNoGROUP BY hotelName, roomNo;HAVING•HAVING lets you put conditions on groups, like WHERE lets you put conditions on tuples•HAVING doesn’t make sense without GROUP BY•HAVING conditions must refer to aggregates or you could put them in a WHERE clause!Revised SQL Evaluation1. Make a temporary table consisting of the Cartesian Product of all the tables in the FROM clause2. Keep only the rows that match the condition in the WHERE clause (relational algebra select operation)3. Split result of step 2 into groups of tuples that agree on all attributes of the GROUP BY