Information Resources Management

1. Information Resources Management March 6, 2001

2. Agenda • Administrivia • SQL Part 2 • Homework #6

3. Administrivia • Mid-term Exam • Homework #4 • Homework #5

4. SQLStructured Query Language • The standard relational database language • Two Parts • DDL - Data Definition Language • DML - Data Manipulation Language

5. SQL - DDL • Data Definition: Define schemas, delete relations, create indices, modify schemas • View Definition • Authorization • Integrity

6. SQL - DML • Insert, Modify, Delete Tuples • Interactive • Embedded • Transaction Control

7. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 tableX (b, c, e) tableY(a, c) tableZ (a, d)

8. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 1 - Join all tables (Cartesian product)

9. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 2 - For each row, apply WHERE conditions

10. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 3 - WHERE subquery, each row evaluated separately

11. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 4 - GROUP BY (with HAVING) - Order remaining data (all rows) by groups

12. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 5 - Apply HAVING to each group

13. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= ALL (SELECT AVG(d) from tableZ as Z) 5a - HAVING with “standalone” SELECT

14. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 6 - Calculate aggregate functions for each (remaining) group

15. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 7 - SELECT desired output columns

16. Evaluating SQL Statements SELECT y.a, AVG(x.b) FROM tableX as x, tableY as y WHERE x.c = y.c AND x.b IN (SELECT d FROM tableZ as z WHERE z.a = y.a) GROUP BY y.a HAVING COUNT (x.e) >= 5 8 - ORDER BY (sort) is processed last

17. SQL - DML • INSERT • UPDATE • DELETE

18. INSERT INSERT INTO table VALUES (………) INSERT INTO table(attributes) VALUES (………)

19. INSERT Example • Add an employee INSERT INTO Employee VALUES(‘123456789’,’John Smith’,,212) INSERT INTO Employee(EmpID, Name, OfficeNBR) VALUES(‘123456789’,’John Smith’,212) MgrFlag is NULL in both cases

20. UPDATE UPDATE table SET attribute = value or calculation UPDATE table SET attribute = value or calculation WHERE conditions

21. UPDATE Example • Increase the prices of all properties by 5% UPDATE Property SET Price = Price * 1.05

22. UPDATE Example • Increase the prices of all properties in St. Paul, MN by 7.5%

23. UPDATE Example • Increase the prices of all properties in St. Paul, MN by 7.5% UPDATE Property SET Price = Price * 1.075 WHERE City = ‘St. Paul’ AND State = ‘MN’

24. UPDATE Example • Change the zip code of all offices in 15214 to 15217-0173

25. UPDATE Example • Change the zip code of all offices in 15214 to 15217-0173 UPDATE Office SET Zip = ‘15217-0173’ WHERE Zip LIKE ‘15214%’

26. DELETE DELETE FROM table DELETE FROM table WHERE conditions

27. DELETE Example • Delete everything from the gift table DELETE FROM Gift

28. DELETE Example • Delete all employees who do not have access to a PC

29. DELETE Example • Delete all employees who do not have access to a PC DELETE FROM Employee WHERE EmpID NOT IN (SELECT EmpID FROM PCAccess)

30. SQL - DDL • CREATE TABLE • DROP TABLE • ALTER TABLE

31. CREATE TABLE CREATE TABLE name (attribute defn <constraint>, attribute defn <constraint>, … <integrity constraints>)

32. CREATE TABLE CREATE TABLE name (attribute defn <constraint>, attribute defn <constraint>, … <integrity constraints>) • Attribute Definitions - Table 9-1, p. 329 • Attribute Constraints • NOT NULL • UNIQUE

33. CREATE TABLE CREATE TABLE name (attribute defn <constraint>, attribute defn <constraint>, … <integrity constraints>) • Integrity Constraints • PRIMARY KEY (attribute, …) • FOREIGN KEY (attribute,…) REFERENCES (table name)

34. CREATE TABLE Example • Create the Office table CREATE TABLE Office (OfficeNbr INTEGER NOT NULL UNIQUE, Address VARCHAR(50), City VARCHAR(25), State CHAR(2), Zip CHAR(10), PhoneNbr CHAR(13), PRIMARY KEY (OfficeNbr))

35. CREATE TABLE Example • Create the Manager table CREATE TABLE Manager (EmpID CHAR(9), OfficeNbr INTEGER, PRIMARY KEY (EmpID), FOREIGN KEY (EmpID) REFERENCES (Employee), FOREIGN KEY (OfficeNbr) REFERENCES (Office))

36. CREATE TABLE Example • Create the MgrPCAccess table -- access type is required

37. CREATE TABLE Example • Create the MgrPCAccess table -- access type is required CREATE TABLE MgrPCAccess (PC# INTEGER, EmpID CHAR(9), AccessType CHAR(15) NOT NULL, PRIMARY KEY (PC#, EmpID), FOREIGN KEY (EmpID) REFERENCES (Employee), FOREIGN KEY (PC#) REFERENCES (PC))

38. DROP TABLE • DROP TABLE name • DROP TABLE Office • DROP vs. DELETE

39. ALTER TABLE • ALTER TABLE name ADD attributes • ALTER TABLE name DROP attributes • Add - existing tuples get NULLs • Nulls must be allowed • Drop - cannot drop the primary key

40. Other SQL DDL • CREATE INDEX • DROP INDEX • CREATE VIEW • DROP VIEW • CREATE SCHEMA

41. Multiple Tables - JOINs • FROM multiple tables WHERE cond • “INNER” join • Equi-join & Natural join variations • What if second table is “optionally” included? • “OUTER” join

42. Outer Join • List the address, city, and manager name (if any) of all offices. SELECT address, city, e.name FROM Office as O, Employee as E WHERE O.OfficeNbr = E.OfficeNbr AND MgrFlag = 1 What happens to offices without a mgr?

43. Outer Join • List the address, city, and manager name (if any) of all offices. SELECT address, city, e.name FROM Office as O, Employee as E WHERE O.OfficeNbr = E.OfficeNbr AND MgrFlag = 1 UNION SELECT address, city, ‘’ FROM Office as O WHERE O.OfficeNbr NOT IN (SELECT OfficeNbr FROM Employee WHERE MgrFlag = 1)

44. Outer Join - Access • List the address, city, and manager name (if any) of all offices. SELECT address, city, e.name FROM Office as O LEFT JOIN Employee as E WHERE O.OfficeNbr = E.OfficeNbr AND MgrFlag = 1 LEFT JOIN - all rows for table on left included (RIGHT JOIN)

45. Outer Join - Oracle • List the address, city, and manager name (if any) of all offices. SELECT address, city, e.name FROM Office as O, Employee as E WHERE O.OfficeNbr = E.OfficeNbr (+) AND MgrFlag = 1 (+) here LEFT JOIN WHERE O.OfficeNbr (+) = E.OffficeNbr RIGHT JOIN

46. Outer Join • Outer Joins are not SQL standard • Not always available • Not consistent • Can always do the same query using standard SQL (UNION & NOT IN)

47. Other Relational Languages • Chapter 10 of book • Query-by Example (QBE) • Access

48. In-Class Exercise • SQL • All 21 queries

49. Homework #6 • Do remaining 5 from HW #5 • Keep problem numbers