Temple University – CIS Dept. CIS616– Principles of Data Management

1. Temple University – CIS Dept.CIS616– Principles of Data Management V. Megalooikonomou Relational Model – SQL (based on notes by Silberchatz,Korth, and Sudarshan and notes by C. Faloutsos at CMU)

2. General Overview - rel. model • Formal query languages • rel algebra and calculi • Commercial query languages • SQL (combination of rel algebra and calculus) • QBE, (QUEL)

3. Overview - detailed - SQL • Fundamental constructs and concepts • check users’ guide for particular implementation • DML • select, from, where, renaming • set operations • ordering • aggregate functions • nested subqueries • other parts: DDL, view definition, embedded SQL, integrity, authorization, etc

4. DML General form select a1, a2, … an from r1, r2, … rm where P [orderby ….] [groupby …] [having …]

5. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 Reminder: our Mini-U db TAKES SSN c-id grade 123 cis331 A 234 cis331 B

6. DML – e.g.: find the ssn(s) of everybody called “smith” select ssn from student where name=“smith”

7. DML - observation General form select a1, a2, … an from r1, r2, … rm where P equivalent rel. algebra query?

8. DML - observation General form select a1, a2, … an from r1, r2, … rm where P

9. DML - observation General form selectdistinct a1, a2, … an from r1, r2, … rm where P

10. select clause select [distinct | all ] name from student where address=“main”

11. where clause find ssn(s) of all “smith”s on “main” select ssn from student where address=“main” and name = “smith”

12. where clause • boolean operators (and,or,not, …) • comparison operators (<, >, =, …) • and more…

13. What about strings? find student ssns who live on “main” (st or str or street – i.e., “main st” or “main str” …)

14. What about strings? find student ssns who live on “main” (st or str or street) select ssn from student where address like “main%” %: variable-length don’t care _: single-character don’t care

15. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B from clause find names of people taking cis351

16. from clause find names of people taking cis351 select name from student, takes where ???

17. from clause find names of people taking cis351 select name from student, takes where student.ssn = takes.ssn and takes.c-id = “cis351”

18. renaming - tuple variables find names of people taking cis351 select name from ourVeryOwnStudent, studentTakingClasses where ourVeryOwnStudent.ssn = studentTakingClasses.ssn and studentTakingClasses.c-id = “cis351”

19. renaming - tuple variables find names of people taking cis351 select name from ourVeryOwnStudent as S, studentTakingClasses as T where S.ssn =T.ssn and T.c-id = “cis351”

20. renaming - self-join • self -joins: find Tom’s grandparent(s)

21. renaming - self-join find grandparents of “Tom” (PC(p-id, c-id)) select gp.p-id from PC as gp, PC where gp.c-id= PC.p-id and PC.c-id = “Tom”

22. renaming - theta join find course names with more units than cis351 select c1.c-name from class as c1, class as c2 where c1.units > c2.units and c2.c-id = “cis351”

23. find course names with more units than cis351 select c1.name from class as c1, class as c2 where c1.units > c2.units and c2.c-id = “cis351”

24. Overview - detailed - SQL • DML • select, from, where • set operations • ordering • aggregate functions • nested subqueries • other parts: DDL, view definition, embedded SQL, integrity, authorization, etc

25. TAKES SSN c-id grade 123 cis331 A 234 cis331 B set operations find ssn of people taking both cis351 and cis331

26. set operations find ssn of people taking both cis351 and cis331 select ssn from takes where c-id=“cis351” and c-id=“cis331” ?

27. set operations find ssn of people taking both cis351 and cis331 select ssn from takes where c-id=“cis351” and c-id=“cis331”

28. set operations find ssn of people taking both cis351 and cis331 (select ssn from takes where c-id=“cis351” ) intersect (select ssn from takes where c-id=“cis331” ) other ops: union , except

29. Overview - detailed - SQL • DML • select, from, where • set operations • ordering • aggregate functions • nested subqueries • other parts: DDL, embedded SQL, auth etc

30. Ordering find student records, sorted in name order select * from student where

31. Ordering find student records, sorted in name order select * from student order by name asc asc is the default

32. Ordering find student records, sorted in name order; break ties by reverse ssn select * from student order by name, ssn desc

33. Overview - detailed - SQL • DML • select, from, where • set operations • ordering • aggregate functions • nested subqueries • other parts: DDL, embedded SQL, auth etc

34. SSN c-id grade 123 cis331 4 234 cis331 3 Aggregate functions find avg grade, across all students select ?? from takes

35. SSN c-id grade 123 cis331 4 234 cis331 3 Aggregate functions find avg grade, across all students selectavg(grade) from takes • result: a single number • Which other functions?

36. Aggregate functions • A: sum,count,min, max (std)

37. SSN c-id grade 123 cis331 4 234 cis331 3 Aggregate functions find total number of enrollments selectcount(*) from takes

38. SSN c-id grade 123 cis331 4 234 cis331 3 Aggregate functions find total number of students in cis331 selectcount(*) from takes where c-id=“cis331”

39. Aggregate functions find total number of students in each course selectcount(*) from takes where ??? SSN c-id grade 123 cis331 4 234 cis331 3

40. c-id count cis331 2 Aggregate functions find total number of students in each course select c-id, count(*) from takes groupby c-id SSN c-id grade 123 cis331 4 234 cis331 3

41. c-id count cis331 2 Aggregate functions find total number of students in each course select c-id, count(*) from takes groupby c-id orderby c-id SSN c-id grade 123 cis331 4 234 cis331 3

42. Aggregate functions find total number of students in each course, and sort by count, decreasing select c-id, count(*) as pop from takes groupby c-id orderby pop desc SSN c-id grade 123 cis331 4 234 cis331 3 c-id pop cis331 2

43. Aggregate functions- ‘having’ find students with GPA > 3.0 SSN c-id grade 123 cis331 4 234 cis331 3

44. Aggregate functions- ‘having’ find students with GPA > 3.0 select???,avg(grade) from takes groupby ??? SSN c-id grade 123 cis331 4 234 cis331 3

45. Aggregate functions- ‘having’ find students with GPA > 3.0 select ssn, avg(grade) from takes groupby ssn ??? SSN c-id grade 123 cis331 4 234 cis331 3

46. Aggregate functions- ‘having’ find students with GPA > 3.0 select ssn, avg(grade) from takes groupby ssn having avg(grade)>3.0 ‘having’ <-> ‘where’ for groups SSN c-id grade 123 cis331 4 234 cis331 3

47. Aggregate functions- ‘having’ find students and GPA, for students with > 5 courses select ssn, avg(grade) from takes groupby ssn having count(*) > 5 SSN c-id grade 123 cis331 4 234 cis331 3

48. Overview - detailed - SQL • DML • select, from, where, renaming • set operations • ordering • aggregate functions • nested subqueries • other parts: DDL, view definition, embedded SQL, integrity, authorization, etc

49. DML General form select a1, a2, … an from r1, r2, … rm where P [orderby ….] [groupby …] [having …]

50. CLASS c-id c-name units cis331 d.b. 2 cis321 o.s. 2 TAKES SSN c-id grade 123 cis331 A 234 cis331 B Reminder: our Mini-U db