Database Management Systems Chapter 4 Queries Jerry Post

D A T A B A S E Why do we Need Queries ² Natural languages (English) are too vague ª With complex questions, it can be hard to verify that the question was interpreted correctly, and that the answer we received is truly correct. ª Consider the question: Who are our best customers? ² We need a query system with more structure ² We need a standardized system so users and developers can learn one method that works on any (most) systems. ª Query By Example (QBE) ª SQL 2

D A T A B A S E Four Questions to Create a Query ² What output do you want to see? ² What do you already know (or what constraints are given)? ² What tables are involved? ² How are the tables joined together? 3

D A T A B A S E Tables 4

D A T A B A S E Organization ² Single table ² Constraints ² Computations ² Groups/Subtotals ² Multiple Tables 5

D A T A B A S E Sample Questions ² List all animals with yellow in their color. ² List all dogs with yellow in their color born after 6/1/04. ² List all merchandise for cats with a list price greater than $10. ² List all dogs who are male and registered or who were born before 6/1/04 and have white in their color. ² What is the average sale price of all animals? ² What is the total cost we paid for all animals? ² List the top 10 customers and total amount they spent. ² How many cats are in the animal list? ² Count the number of animals in each category. ² List the Customer. ID of everyone who bought something between 4/1/04 and 5/31/04. ² List the first name and phone of every customer who bought something between 4/1/04 and 5/31/04. ² List the last name and phone of anyone who bought a registered white cat between 6/1/04 and 12/31/04. ² Which employee has sold the most items? 6

D A T A B A S E Query 04_01 Query By Example & SQL What tables? SELECTAnimal. ID, Category, Breed, Color FROM Animal WHERE (Color LIKE ‘%Yellow%’); Animal. ID Name Category Breed Date. Born Gender What to see? What conditions? Field Animal. ID Category Breed Color Table Animal Sort Criteria Like ‘%Yellow%’ Or List all animals with yellow in their color 7

D A T A B A S E Basic SQL SELECT columns What do you want to see? FROM tables What tables are involved? JOIN conditions How are the tables joined? WHERE criteria What are the constraints? 8

D A T A B A S E ORDER BY SELECT FROM JOIN WHERE ORDER BY Animal. ID Name Category Breed Date. Born Gender columns tables join columns conditions columns (ASC DESC) SELECT Name, Category, Breed FROM Animal ORDER BY Category, Breed; Field Name Category Breed Table Animal Ascending Sort Criteria Or Name Cathy Category Bird Debbie Bird Terry Bird Charles Bird Curtis Bird Ruby Bird Sandy Bird Hoyt Bird Breed African Grey Canary Cockatiel Lovebird Other Parakeet Parrot 9

D A T A B A S E DISTINCT SELECT Category FROM Animal; Category Fish Dog Fish Cat Dog Fish Dog Dog Fish Cat Dog. . . SELECT DISTINCT Category FROM Animal; Category Bird Cat Dog Fish Mammal Reptile Spider 10

D A T A B A S E Query 04_02 Constraints: And Animal SELECTAnimal. ID, Category, Date. Born FROM Animal WHERE ((Category=‘Dog’) AND (Color Like ‘%Yellow%’) AND (Date. Born>’ 01 -Jun-2004’)); Animal. ID Name Category Breed Date. Born Gender Field Animal. ID Category Date. Born Color Table Animal >’ 01 -Jun-2004’ Like ‘%Yellow%’ Sort Criteria ‘Dog’ Or List all dogs with yellow in their color born after 6/1/04. 11

D A T A B A S E Boolean Algebra And: Both must be true. Or: Either one is true. Not: Reverse the value. a=3 b = -1 c=2 (a > 4) And (b < 0) F T F (a > 4) Or (b < 0) F T T NOT (b < 0) T F 12

D A T A B A S E Boolean Algebra The result is affected by the order of the operations. Parentheses indicate that an operation should be performed first. With no parentheses, operations are performed left-to-right. a=3 b = -1 c=2 ( (a > 4) AND (b < 0) ) OR (c > 1) F T T Always use parentheses, so other people can read and understand your query. (a > 4) AND ( (b < 0) OR (c > 1) ) F T T F 13

D A T A B A S E De. Morgan’s Law Example Customer: "I want to look at a cat, but I don’t want any cats that are registered or that have red in their color. " Animal. ID Name Category Breed Date. Born Gender SELECT Animal. ID, Category, Registered, Color FROM Animal WHERE (Category=‘Cat’) AND NOT ((Registered is NOT NULL) OR (Color LIKE ‘%Red%’)). Field Animal. ID Category Registered Color Table Animal ‘Cat’ Is Null Not Like ‘%Red%’ Sort Criteria Or 14

D A T A B A S E De. Morgan’s Law ² Negation of clauses ª Not (A And B) becomes Not A Or Not B ª Not (A Or B) becomes Not A And Not B Registered=ASCF Color=Black NOT ((Registered is NOT NULL) OR (Color LIKE ‘%Red%’)) T F or F T not (Registered is NULL) AND NOT (Color LIKE ‘%Red%’) F not F and T F 15

D A T A B A S E Query 04_03 Conditions: AND, OR SELECT Animal. ID, Category, Gender, Registered, Date. Born, Color FROM Animal WHERE (( Category=‘Dog’) AND ( ( (Gender=‘Male’) AND (Registered Is Not Null) ) OR ( (Date. Born<’ 01 -Jun-2004’) AND (Color Like ‘%White%’) ) ) ); Animal. ID Name Category Breed Date. Born Gender Field Animal. ID Category Gender Registered Date. Born Color Table Animal Animal Criteria ‘Dog’ ‘Male’ Is Not Null Or ‘Dog’ < ’ 01 -Jun-2004’ Like ‘%White%’ Sort List all dogs who are male and registered or who were born before 6/1/2004 and have white in their color. 16

D A T A B A S E Useful Where Conditions Comparisons Examples Operators <, =, >, <>, BETWEEN, LIKE, IN Numbers Account. Balance > 200 Text Simple Pattern match one Pattern match any Name > ‘Jones’ License LIKE ‘A_ _82_’ Name LIKE ‘J%’ Dates Sale. Date BETWEEN ’ 15 -Aug-2004’ AND ’ 31 -Aug-2004’ Missing Data City IS NULL Negation Name IS NOT NULL Sets Category IN (‘Cat’, ‘Dog’, ‘Hamster’) 17

D A T A B A S E Simple Computations Sale. Item(Order. ID, Item. ID, Sale. Price, Quantity) Select Order. ID, Item. ID, Sale. Price, Quantity, Sale. Price*Quantity As Extended From Sale. Item; Order. ID Item. ID Price Quantity Extended 151 9764 7653 19. 50 8. 35 2 3 39. 00 25. 05 151 8673 6. 89 2 13. 78 Basic computations (+ - * /) can be performed on numeric data. The new display column should be given a meaningful name. 24

D A T A B A S E Query 04_04 Computations: Aggregation--Avg SELECT Avg(Sale. Price) AS Avg. Of. Sale. Price FROM Sale. Animal; Sale. Animal Sale. ID Animal. ID Sale. Price Field Sale. Price Table Sale. Animal Total Avg Sum Avg Min Max Count St. Dev or Std. Dev Var Sort Criteria Or What is the average sale price of all animals? 25

D A T A B A S E Query 04_05 Computations (Math Operators) Order. Item PONumber Item. ID Quantity Cost SELECT Sum(Quantity*Cost) AS Order. Total FROM Order. Item WHERE (PONumber=22); Field PONumber Order. Total: Quantity*Cost Table Order. Item Total Sort Criteria =22 Order. Total 1798. 28 Or ² What is the total value of the order for PONumber 22? ª Use any common math operators on numeric data. ª Operate on data in one row at a time. 26

D A T A B A S E SQL Differences 27

D A T A B A S E Query 04_06 Subtotals (Where) Animal. ID Name Category Breed Date. Born Gender SELECT Count(Animal. ID) AS Count. Of. Animal. ID FROM Animal WHERE (Category = ‘Cat’); Field Animal. ID Category Table Animal Total Count Where Sort Criteria ‘Cat’ Or How many cats are in the Animal list? 28

D A T A B A S E Query 04_07 Groups and Subtotals Animal. ID Name Category Breed Date. Born Gender SELECT FROM GROUP BY ORDER BY Field Category Animal. ID Table Animal Total Group By Count Sort Criteria Descending Category, Count(Animal. ID) AS Count. Of. Animal. ID Animal Category Count(Animal. ID) DESC; Category Count. Of. Animal. ID Dog 100 Cat 47 Bird 15 Fish 14 Reptile 6 Mammal 6 Spider 3 Or ² Count the number of animals in each category. ª You could type in each WHERE clause, but that is slow. ª And you would have to know all of the Category values. 29

D A T A B A S E Query 04_08 Conditions on Totals (Having) Animal. ID Name Category Breed Date. Born Gender SELECT FROM GROUP BY HAVING ORDER BY Field Category Animal. ID Table Animal Total Group By Count Sort Descending Criteria >10 Category, Count(Animal. ID) AS Count. Of. Animal. ID Animal Category Count(Animal. ID) > 10 Count(Animal. ID) DESC; Category Count. Of. Animal. ID Dog 100 Cat 47 Bird 15 Fish 14 Or Count number of Animals in each Category, but only list them if more than 10. 30

D A T A B A S E Query 04_09 Where (Detail) v Having (Group) Animal. ID Name Category Breed Date. Born Gender SELECT FROM WHERE GROUP BY HAVING ORDER BY Category, Count(Animal. ID) AS Count. Of. Animal. ID Animal Date. Born > ’ 01 -Jun-2004’ Category Count(Animal. ID) > 10 Count(Animal. ID) DESC; Field Category Animal. ID Date. Born Table Animal Total Group By Count Where Sort Descending Criteria >10 >’ 01 -Jun-2004’ Category Count. Of. Animal. ID Dog 30 Cat 18 Or Count Animals born after 6/1/2004 in each Category, but only list Category if more than 10. 31

D A T A B A S E Query 04_10 Multiple Tables (Intro & Distinct) Sale. ID Sale. Date Employee. ID Customer. ID Sales. Tax SELECT DISTINCT Customer. ID FROM Sale WHERE (Sale. Date Between ’ 01 -Apr-2004’ And ’ 31 -May-2004’) ORDER BY Customer. ID; Field Customer. ID Sale. Date Table Sale Sort Ascending Criteria Or Between ’ 01 -Apr-2004’ And ’ 31 -May-2004’ Customer. I D 6 8 14 19 22 24 28 36 37 38 39 42 50 57 58 63 74 80 90 List the Customer. ID of everyone who bought something between 01 -Apr 2004 and 31 -May-2004. 32

D A T A B A S E Query 04_11 Joining Tables SELECT DISTINCT Sale. Customer. ID, Customer. Last. Name FROM Customer INNER JOIN Sale ON Customer. ID = Sale. Customer. ID WHERE (Sale. Date Between ’ 01 -Apr-2004’ And ’ 31 -May-2004’) ORDER BY Customer. Last. Name; Sale Customer Sale. ID Sale. Date Employee. ID Customer. ID Phone First. Name Last. Name Field Customer. ID Last. Name Sale. Date Table Sale Customer Sale Sort Criteria Or Ascending Between ’ 01 -Apr-2004’ And ’ 31 -May-2004’ Customer. ID 22 57 38 42 63 74 36 6 50 58 … Last. Name Adkins Carter Franklin Froedge Grimes Hinton Holland Hopkins Lee Mc. Cain List Last. Names of Customers who bought between 4/1/2004 and 5/31/2004. 33

D A T A B A S E SQL JOIN FROM table 1 INNER JOIN table 2 ON table 1. column = table 2. column SQL 92 syntax (Access and SQL Server) FROM table 1, table 2 WHERE table 1. column = table 2. column SQL 89 syntax (Oracle) FROM table 1, table 2 JOIN table 1. column = table 2. column Informal syntax 34

D A T A B A S E Syntax for Three Tables SQL ‘ 92 syntax to join three tables FROM Table 1 INNER JOIN (Table 2 INNER JOIN Table 3 ON Table 2. Col. A = Table 3. Col. A) ON Table 1. Col. B = Table 2. Col. B Easier notation, but not correct syntax FROM Table 1, Table 2, Table 3 JOIN Table 1. Col. B = Table 2. Col. B Table 2. Col. A = Table 3. Col. A 35

D A T A B A S E Query 04_12 Multiple Tables (Many) SELECT DISTINCTROW Customer. Last. Name, Customer. Phone FROM Customer INNER JOIN (Sale INNER JOIN (Animal INNER JOIN Sale. Animal ON Animal. ID = Sale. Animal. ID) ON Sale. ID = Sale. Animal. Sale. ID) ON Customer. ID = Sale. Customer. ID WHERE ((Animal. Category=‘Cat’) AND (Animal. Registered Is Not Null) AND (Color Like ‘%White%’) AND (Sale. Date Between ’ 01 -Jun-2004’ And ’ 31 -Dec-2004’)); Animal Sale. Animal. ID Name Category Breed Sale. ID Animal. ID Sale. Price Sale Customer Sale. ID Sale. Date Employee. ID Customer. ID Phone First. Name Last. Name Field Last. Name Phone Category Registered Color Sale. Date Table Customer Animal Sale Sort Ascending ‘Cat’ Is Not Null Like ‘%White%’ Between ’ 01 -Jun-2004’ And ’ 31 -Dec-2004’ Criteria Or ² List the Last Name and Phone of anyone who bought a registered White cat between 6/1/2004 and 12/31/2004. 36

D A T A B A S E Oracle select lastname, phone from customer inner join sale on customerid = sale. customerid inner join saleanimal on saleid = saleanimal. saleid inner join animal on saleanimalid = animalid where (category = 'Cat') and (Registered is not null) and (color like '%White%') AND (saledate between '01 -Jun-2004' and '31 -Dec-2004') ; 37

D A T A B A S E Building a Query ² List the Last Name and Phone of anyone who bought a registered White cat between 6/1/04 and 12/31/04. ² Identify the tables involved. ª Look at the columns you want to see. © Last. Name, Phone: Customer ª Look at the columns used in the constraints. © Registered, Color, Category: Animal © Sale Date: Sale ª Find connector tables. © To connect Animal to Sale: Sale. Animal ² ² ² Select the desired columns and test the query. Enter the constraints. Set Order By columns. Add Group By columns. Add summary computations to the SELECT statement. 38

D A T A B A S E Joining Tables (Hints) ² Build Relationships First ª Drag and drop ª From one side to many side ² Avoid multiple ties between tables ² SQL ª FROM Table 1 ª INNER JOIN Table 2 ª ON Table 1. Col. A = Table 2. Col. B ² Join columns are often keys, but they can be any columns--as long as the domains (types of data) match. ² Multiple Tables ª FROM (Table 1 ª INNER JOIN Table 2 ª ON T 1. Col. A = T 2. Col. B ) ª INNER JOIN Table 3 ª ON T 3. Col. C = T 3. Col. D ² Shorter Notation ª FROM T 1, T 2, T 3 ª JOIN T 1. Col. A = T 2. Col. B ª T 1. Col. C = T 3. Col. D ² Shorter Notation is not correct syntax, but it is easier to write. 39

D A T A B A S E Tables with Multiple Joins ² Potential problem with three or more tables. ² Access uses predefined relationships to automatically determine JOINs. ² JOINS might loop. ² Most queries will not work with loops. A query with these four tables with four JOINS would only return rows where the Employee had the same Zip. Code as the Supplier. If you only need the Supplier city, just delete the JOIN between Employee and Zip. Code. If you want both cities, add the Zip. Code table again as a fifth table. 40

D A T A B A S E Table Alias City Supplier City. ID Zip. Code City State Supplier. ID Address Zip. Code City. ID Animal. Order Employee Order. Date Supplier. ID Shipping. Cost Employee. ID Last. Name Zip. Code City. ID City 2 City. ID Zip. Code City State SELECT Supplier. SID, Supplier. City. ID, City, Employee. EID, Employee. Last. Name, Employee. City. ID, City 2. City FROM (City INNER JOIN Supplier ON City. ID = Supplier. City. ID) INNER JOIN ((City AS City 2 INNER JOIN Employee ON City 2. City. ID = Employee. City. ID) INNER JOIN Animal. Order ON Employee. ID = Animal. Order. Employee. ID) ON Supplier. ID = Animal. Order. Supplier. ID; SID 4 2 4 9 5 Supplier. City. ID 7972 10896 7972 10740 10893 City Middlesboro Springfield Middlesboro Columbia Smyrna EID 5 1 3 8 3 Last. Name James Reeves Reasoner Carpenter Reasoner Employee. City. ID 7083 9201 8313 10592 8313 City 2. City Orlando Lincoln Springfield Philadelphia Springfield 41

D A T A B A S E Saved Query: Create View ² Save a query ª Faster: only enter once ª Faster: only analyze once ² Any SELECT statement ² Can use the View within other SQL queries. CREATE VIEW Kittens AS SELECT * FROM Animal WHERE (Category = ‘Cat’) AND (Today - Date. Born < 180); SELECT Avg(List. Price) FROM Kittens WHERE (Color LIKE ‘%Black%’); 42

D A T A B A S E Updateable Views Order. Item(Order. ID, Item. ID, Quantity) Item(Item. ID, Description) Order. Line(Order. ID, Item. ID, Description, Quantity) ² To be updateable, a view must focus on one primary table. (Order. Item) ª Goal is to change data in only one table. (Order. Item) ª Data can be displayed from other tables. (Item) ª Never include or attempt to change primary keys from more than one table. (Item. ID) 43

D A T A B A S E Non Updateable View Order. Item(Order. ID, Item. ID, Quantity) 121 122 57 82 57 3 2 1 Item(Item. ID, Description) 57 58 59 Cat food Dog food Bird food Order. Line(Order. ID, Item. ID, Description, Quantity) 121 122 57 82 57 32 Cat food Bird feeder Cat food 3 2 1 If you attempt to change the Item. ID in the Order. Line. View: You will simply change the primary key value in the Item table. It will not add a new row to the Order. Item table. 44

D A T A B A S E SQL Syntax: ALTER TABLE table ADD COLUMN column datatype (size) DROP COLUMN column CREATE TABLE See also: DROP TABLE 45

D A T A B A S E SQL Syntax: COMMIT WORK See also: ROLLBACK 46

D A T A B A S E SQL Syntax: CREATE INDEX CREATE [UNIQUE] INDEX index ON table (column 1, column 2, … ) WITH {PRIMARY | DISALLOW NULL | IGNORE NULL} See also: CREATE TABLE 47

D A T A B A S E SQL Syntax: CREATE TABLE table ( column 1 datatype (size) [NOT NULL] [index 1] , column 2 datatype (size) [NOT NULL] [index 2], …, CONSTRAINT pkname PRIMARY KEY (column, …), CONSTRAINT fkname FOREIGN KEY (column) REFERENCES existing_table (key_column) ON DELETE CASCASDE ) ALTER TABLE See also: DROP TABLE 48

D A T A B A S E SQL Syntax: CREATE VIEW viewname AS SELECT … See also: SELECT 49

D A T A B A S E SQL Syntax: DELETE FROM table WHERE condition See also: DROP 50

D A T A B A S E SQL Syntax: DROP INDEX index ON table DROP TABLE DROP VIEW See also: DELETE 51

D A T A B A S E SQL Syntax: INSERT INTO table (column 1, column 2, …) VALUES (value 1, value 2, … ) INSERT INTO newtable (column 1, column 2, …) SELECT … See also: SELECT 52

D A T A B A S E SQL Syntax: GRANT privilege ON object TO user | PUBLIC See also: privileges ALL, ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE REVOKE 53

D A T A B A S E SQL Syntax: REVOKE privilege ON object FROM user | PUBLIC See also: privileges ALL, ALTER, DELETE, INDEX, INSERT, SELECT, UPDATE GRANT 54

D A T A B A S E SQL Syntax: ROLLBACK SAVEPOINT savepoint {optional} ROLLBACK WORK TO savepoint See also: COMMIT 55

D A T A B A S E SQL Syntax: SELECT DISTINCT table. column {AS alias} , . . . FROM table/query INNER JOIN table/query ON T 1. Col. A = T 2. Col. B WHERE (condition) GROUP BY column HAVING (group condition) ORDER BY table. column { UNION, INTERSECT, EXCEPT … } 56