Query Formulation with SQL

1. Query Formulation with SQL Asst.Prof.IntirapornMulasastra

2. Outline • Background • Getting started • Joining tables • Summarizing tables • Reasoning tools • Advanced problems • Data manipulation statements Asst.Prof.Intiraporn Mulasastra

3. What is SQL? • Structured Query Language • Language for database • definition, • manipulation, and • control • International standard • Standalone and embedded usage • Intergalactic database speak Asst.Prof.Intiraporn Mulasastra

4. SQL Statements • Definition: • CREATE TABLE, ALTER TABLE, CREATE VIEW, CREATE SCHEMA • Manipulation: • SELECT, INSERT, UPDATE, DELETE, COMMIT, ROLLBACK • Control: • GRANT, REVOKE, CREATE ASSERTION • Other statements: SET , CREATE TRIGGER, CREATE DOMAIN Asst.Prof.Intiraporn Mulasastra

5. SELECT Statement Overview SELECT<list of column expressions> FROM<list of tables and join operations> WHERE<list of logical expressions for rows> GROUP BY<list of grouping columns> HAVING<list of logical expressions for groups> ORDER BY<list of sorting specifications> • Expression: combination of columns, constants, operators, and functions Asst.Prof.Intiraporn Mulasastra

6. UNIVERSITY DATABASE นิสิต อาจารย์ ลงทะเบียน การเปิดสอน อาจารย์1 วิชา Asst.Prof.Intiraporn Mulasastra

7. First SELECT Examples Example 1 เลือกทุกแถว ทุกคอลัมน์(*) SELECT * FROM Faculty รหัส ชื่อ นามสกุล ตำแหน่ง เงินเดือน หัวหน้า Asst.Prof.Intiraporn Mulasastra

8. First SELECT Examples Example 2 (Access) เลือกเฉพาะแถว ทุกคอลัมน์(*) SELECT * FROM Faculty WHERE FacSSN = '543210987’ รหัส ชื่อ นามสกุล ตำแหน่ง เงินเดือน หัวหน้า Asst.Prof.Intiraporn Mulasastra

9. First SELECT Examples Example 3 เลือกทุกแถว เฉพาะคอลัมน์ SELECT FacFirstName, FacLastName, FacSalary FROM Faculty รหัส ชื่อ นามสกุล ตำแหน่ง เงินเดือน หัวหน้า Asst.Prof.Intiraporn Mulasastra

10. First SELECT Examples Example 4 เลือกบางแถว บางคอลัมน์ SELECT FacFirstName, FacLastName, FacSalary FROM Faculty WHERE FacSalary > 65000 AND FacRank = 'PROF' รหัส ชื่อ นามสกุล ตำแหน่ง เงินเดือน หัวหน้า Asst.Prof.Intiraporn Mulasastra

11. ค้นหาอาจารย์ที่เข้าทำงานหลังปี 1996 คำนวณเงินเดือนเพิ่ม 10% Using Expressions Example 5 (Access) SELECT FacFirstName, FacLastName, FacCity, FacSalary*1.1 ASIncreasedSalary, FacHireDate FROM Faculty WHERE year(FacHireDate) > 1996 เงินเดือนคูณ 1.1 Asst.Prof.Intiraporn Mulasastra

12. Inexact Matching • Match against a pattern: LIKE operator • Use meta characters to specify patterns • Wildcard (* or %) • Any single character (? or _) • Example 6 (Access) • SELECT * • FROM Offering • WHERE CourseNoLIKE‘IS*' Asst.Prof.Intiraporn Mulasastra

13. Join Operator • Most databases have many tables • Combine tables using the join operator • Specify matching condition • Can be any comparison but usually = • PK = FK most common join condition • Relationship diagram useful when combining tables Asst.Prof.Intiraporn Mulasastra

14. a a a A a a a 1 1 2 2 1 B 2 c c c c C c c D 2 1 1 2 1 2 E 2 2 1 1 1 2 Cartesian Product ตารางr, s: ไม่มีเงื่อนไขในการเชื่อมโยง s r A B C D E r xs Asst.Prof.Intiraporn Mulasastra

15. Asst.Prof.Intiraporn Mulasastra

16. Cross Product Style • List tables in the FROM clause • List join conditions in the WHERE clause • Example 10.1 (Access) • SELECT OffYear,OffTerm,CourseNo, • FacFirstName,FacLastName • FROM Offering, Faculty • WHEREFaculty.FacSSN = Offering.FacSSN details of offerings and assigned faculty for all course offerings Asst.Prof.Intiraporn Mulasastra

17. Cross Product Style Example 10.2 (Access) SELECT OfferNo, CourseNo, FacFirstName, FacLastName FROM Offering, Faculty WHERE OffTerm = 'FALL' AND OffYear = 2007 AND FacRank = 'ASST' AND CourseNo LIKE‘IS*' AND Faculty.FacSSN = Offering.FacSSN details of offerings and assigned faculty for fall 2007 IS courses taught by assistant professors Asst.Prof.Intiraporn Mulasastra

18. GROUP BY Examples Example 12: Grouping on a single column SELECT FacRank, AVG(FacSalary) AS AvgSalary FROM Faculty GROUP BY FacRank รหัส ชื่อ นามสกุล ตำแหน่งเงินเดือนหัวหน้า Asst.Prof.Intiraporn Mulasastra

19. GROUP BY Examples Example 12: Grouping on a single column SELECT FacRank, AVG(FacSalary) AS AvgSalary FROM Faculty GROUP BY FacRank Asst.Prof.Intiraporn Mulasastra

20. GROUP BY Examples Example 13: Row and group conditions SELECT StdMajor, AVG(StdGPA) AS AvgGPA FROM Student WHERE StdClass IN('JR', 'SR') GROUP BY StdMajor HAVING AVG(StdGPA) > 3.1    Asst.Prof.Intiraporn Mulasastra

21. SELECT StdMajor, AVG(StdGPA) AS AvgGPA FROM Student WHERE StdClassIN('JR', 'SR')GROUP BY StdMajor Asst.Prof.Intiraporn Mulasastra

22. GROUP BY Examples Example 13: Row and group conditions SELECT StdMajor, AVG(StdGPA) AS AvgGPA FROM Student WHERE StdClassIN('JR', 'SR') GROUP BY StdMajor HAVING AVG(StdGPA) > 3.1 Asst.Prof.Intiraporn Mulasastra

23. Summarization and Joins • Powerful combination • List join conditions in the WHERE clause • Example 14: List the number of students enrolled in each fall 2003 • offering. • SELECT Offering.OfferNo, • COUNT(*) AS NumStudents • FROM Enrollment, Offering • WHERE Offering.OfferNo = Enrollment.OfferNo • AND OffYear = 2005 • GROUP BY Offering.OfferNo Asst.Prof.Intiraporn Mulasastra

24. มี Key เชื่อมโยง Joining Three Tables SELECT * FROM Faculty, Offering,Course WHERE Faculty.FacSSN=Offering.FacSSN AND Offering.CourseNo=Course.CourseNo Results of 2 tables joined 1 2 2 tables 3 Asst.Prof.Intiraporn Mulasastra Table 3 3 tables joined

25. Joining Three Tables Example 16: List Leonard Vince’s teaching schedule in fall 2005. For each course, list the offering number, course number, number of units, days, location, and time. SELECT OfferNo, Offering.CourseNo, OffDays, CrsUnits, OffLocation, OffTime FROM Faculty, Offering,Course WHERE Faculty.FacSSN = Offering.FacSSN AND Offering.CourseNo = Course.CourseNo AND OffYear = 2007 AND OffTerm = 'FALL' AND FacFirstName = 'Leonard' AND FacLastName = 'Vince' Asst.Prof.Intiraporn Mulasastra

26. Multiple Column Grouping Example 20: List the course number, the offering number, and the number of students enrolled. Only include courses offered in spring 2006. SELECT CourseNo, Enrollment.OfferNo, Count(*) AS NumStudents FROM Offering, Enrollment WHERE Offering.OfferNo = Enrollment.OfferNo AND OffYear = 2006 AND OffTerm = 'SPRING' GROUP BY Enrollment.OfferNo, CourseNo สมาชิกของกลุ่มต้องมีค่า 2 ค่านี้เหมือนกัน Asst.Prof.Intiraporn Mulasastra

27. คำสั่งอื่นๆ • ตรวจสอบการคงอยู่ของข้อมูล • EXIST, NOT EXIST • เช่น รายชื่อนักเรียนที่ไม่เคยลงเรียนวิชา ภาษาอังกฤษ เลย • การเป็นสมาชิก • IN, NOT IN • นักเรียนที่เป็นสมาชิกทุกชมรม Asst.Prof.Intiraporn Mulasastra

28. INSERT Example Example 24: Insert a row into the Student table supplying values for all columns. INSERT INTOStudent (StdSSN, StdFirstName, StdLastName, StdCity, StdState, StdZip, StdClass, StdMajor, StdGPA) VALUES ('999999999','JOE','STUDENT','SEATAC', 'WA','98042-1121','FR','IS', 0.0) Asst.Prof.Intiraporn Mulasastra

29. UPDATE Example Example 25: Change the major and class of Homer Wells. UPDATE Student SET StdMajor = 'ACCT', StdClass = 'SO' WHERE StdFirstName = 'HOMER' AND StdLastName = 'WELLS' Asst.Prof.Intiraporn Mulasastra

30. DELETE Example Example 26: Delete all IS majors who are seniors. DELETE FROM Student WHEREStdMajor = 'IS' AND StdClass = 'SR' Asst.Prof.Intiraporn Mulasastra

31. DELETE Example • Use Type I nested queries to test conditions on other tables • Use for UPDATE statements also • Example 8: Delete offerings taught by Leonard Vince. • DELETE FROM Offering • WHERE Offering.FacSSN IN • ( SELECT FacSSN FROM Faculty • WHERE FacFirstName = 'Leonard' • AND FacLastName = 'Vince' ) Asst.Prof.Intiraporn Mulasastra

32. SQL Asst.Prof.Intiraporn Mulasastra