Information Resources Management

1. Information Resources Management February 27, 2001

2. Agenda • Administrivia • Exam • SQL Part 1 • Homework #5

3. Administrivia • Homework #4

4. Exam • Results • Review

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

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

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

8. SQL - DML • SELECT • Single table • Multiple tables • INSERT • UPDATE • DELETE

9. SELECT SELECT attributes FROM table(s) WHERE conditions Result is a relation/table

10. SELECT Example • List all information in the Office table SELECT * FROM Office

11. SELECT Example • List all offices in New York state SELECT * FROM Office WHERE State = ‘NY’

12. SELECT Example • List owners with at least 75% of some property SELECT * FROM PctOwned WHERE PctOwned >= 75

13. Select Example • List all offices in Springfield, IL

14. Select Example • List all offices in Springfield, IL SELECT * FROM Office WHERE City = ‘Springfield’ AND State = ‘IL’

15. Select Example • List all properties listed by office 100 or 150

16. Select Example • List all properties listed by office 100 or 150 SELECT * FROM Property WHERE OfficeNbr = 100 OR OfficeNbr = 150

17. Select Example • List the office number and phone number for all offices in New York

18. Select Example • List the office number and phone number for all offices in New York SELECT OfficeNbr, PhoneNbr FROM Office WHERE State = ‘NY’

19. String Operations - LIKE • % - match any substring (*) • __ - match any character (?) SELECT EmpID, Name FROM Employee WHERE Name LIKE ‘Tom%’

20. LIKE Examples • List all properties whose description includes a fireplace

21. LIKE Examples • List all properties whose description includes a fireplace SELECT * FROM Property WHERE Description LIKE ‘%fireplace%’

22. LIKE Examples • List all employees whose name is Rita and whose last name is 4 characters long

23. LIKE Examples • List all employees whose name is Rita and whose last name is 4 characters long SELECT * FROM Employee WHERE Name LIKE ‘Rita _ _ _ _’

24. LIKE Examples • List all employees whose name is Rita and whose last name is at least 4 characters long

25. LIKE Examples • List all employees whose name is Rita and whose last name is at least 4 characters long SELECT * FROM Employee WHERE Name LIKE ‘Rita _ _ _ _%’

26. Nulls • An attribute that does not have any value is assigned a value of NULL • not the same as zero • not the same as empty string • indicates no or unknown value

27. Testing for Nulls • WHERE attribute IS NULL • WHERE attribute IS NOT NULL

28. Selecting Expressions • A mathematical expression can be selected instead of an attribute SELECT col1, col2*col3, col4+100 FROM table WHERE conditions

29. Expressions Example • Assuming 5% commission, give the address and potential commission for all Erie properties SELECT address, price*0.05 FROM property WHERE city=‘Erie’ AND state = ‘PA’

30. Selecting Expressions - 2 • Selected expressions can be given a name using AS SELECT col1, col2*col3 AS newname FROM table WHERE conditions

31. Expressions Example - 2 • Assuming 5% commission, give the address and potential commission for all Erie properties SELECT address, price*0.05 AS comm FROM property WHERE city=‘Erie’ AND state = ‘PA’

32. Renaming with AS • Can also use AS in FROM • SELECT * FROM somelongtbl AS A • rename table • useful with multiple tables

33. Eliminating Duplicates • SELECT DISTINCT … • Entire tuple must be the same to be eliminated as a duplicate

34. Specifying the Order • SELECT … ORDER BY attribute(s) • SELECT … ORDER BY attrib ASC, attrib DESC • attributes must be SELECTed

35. Order & Duplicate Example • List all office numbers for offices with employees; sort by office number SELECT DISTINCT OfficeNbr FROM Employee ORDER BY OfficeNbr

36. Order & Duplicate Example • List the name, city, and state for all owners that own at least 50% of a property; sort by state (descending) and city

37. Order & Duplicate Example • List the name, city, and state for all owners that own at least 50% of a property; sort by state (descending) and city SELECT DISTINCT Name, City, State FROM Owner AS O, PctOwned AS P WHERE O.OwnerSSN = P.OwnerSSN AND PctOwned >= 50 ORDER BY State DESC, City

38. Set Operations • UNION () • INTERSECT () • EXCEPT (-) • Add ALL to keep duplicates

39. Aggregation Functions • AVG • MIN • MAX • SUM • COUNT

40. Aggregation Examples • How many employees work at office 200? SELECT COUNT(*) FROM Employee WHERE OfficeNbr = 200

41. Aggregation Examples • What is the average price of the listings for office 225?

42. Aggregation Examples • What is the average price of the listings for office 225? SELECT AVG(Price) FROM Property WHERE OfficeNbr = 225

43. GROUP BY & HAVING SELECT attributes, aggregation(attributes) FROM table WHERE conditions GROUP BY attributes HAVING condition • HAVING only applies to each group (optional)

44. GROUP BY Example • What is the average price of a listing for each office? SELECT OfficeNbr, AVG(Price) FROM Property GROUP BY OfficeNbr

45. GROUP BY Example • For offices with more than 100 listings, what is the total value of each office’s listings?

46. GROUP BY Example • For offices with more than 100 listings, what is the total value of each office’s listings? SELECT OfficeNbr, SUM(Price) FROM Property GROUP BY OfficeNbr HAVING COUNT(PropertyID) > 100

47. Cartesian Product Example • List the names of all employees who work in offices in PA SELECT name FROM Employee, Office WHERE state = ‘PA’

48. Employee

49. Office

50. FROM Employee, Office