1 / 51

Query Formulation with SQL

COT 4730: Applied Database Systems. Query Formulation with SQL. Outline. Background Getting started Joining tables Summarizing tables Problem solving guidelines Advanced problems Data manipulation statements. What is SQL?. S tructured Q uery L anguage

jarede
Télécharger la présentation

Query Formulation with SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. COT 4730: Applied Database Systems Query Formulation with SQL

  2. Outline • Background • Getting started • Joining tables • Summarizing tables • Problem solving guidelines • Advanced problems • Data manipulation statements

  3. What is SQL? • Structured Query Language • Language for database definition, manipulation, and control • International standard

  4. SQL Standardization • Relatively simple standard: SQL-86 and revision (SQL-89) • Modestly complex standard: SQL-92 • Complex standards: SQL:1999, SQL:2003, SQL:2008, SQL:2011, and SQL:2016

  5. SQL Conformance • No official conformance testing • Vendor claims about conformance • Reasonable conformance on basic parts of SQL foundation • Large variance on conformance of optional parts • Difficult to write portable SQL code outside of Core SQL

  6. SELECT Statement Overview SELECT <list of column expressions> FROM <list of tables and join operations> WHERE <row conditions connected by logical operators> GROUP BY <list of grouping columns> HAVING <group conditions connected by logical operators> ORDER BY <list of sorting specifications> • Expression: combination of columns, constants, operators, and functions

  7. University Database

  8. First SELECT Examples Example 1 SELECT * FROM Faculty Example 2 SELECT * FROM Faculty WHEREFacNo = '543210987' Example 3 SELECTFacFirstName, FacLastName, FacSalary FROM Faculty Example 4 SELECTFacFirstName, FacLastName, FacSalary FROM Faculty WHEREFacSalary > 65000 AND FacRank = 'PROF'

  9. Using Expressions Example 5 (Oracle) SELECTFacFirstName, FacLastName, FacCity, FacSalary*1.1 AS IncreasedSalary, FacHireDate FROM Faculty WHEREto_number(to_char(FacHireDate, 'YYYY')) > 2005

  10. Inexact Matching • Match against a pattern: LIKE operator • Use meta characters to specify patterns • Wildcard ( % ) • Any single character ( _ ) • Example 6 (Oracle) • SELECT * • FROM Offering • WHERECourseNoLIKE 'IS%'

  11. Using Dates • Dates are numbers • Date constants and functions are not standard • Example 7 (Oracle) • SELECTFacFirstName, FacLastName, FacHireDate • FROM Faculty • WHEREFacHireDate BETWEEN '1-Jan-2008' AND '31-Dec-2009'

  12. Other Single Table Examples Example 8: Testing for null values SELECTOfferNo, CourseNo FROM Offering WHEREFacNoIS NULL AND OffTerm = 'SUMMER' AND OffYear = 2017 Example 9: Mixing AND and OR SELECTOfferNo, CourseNo, FacNo FROM Offering WHERE (OffTerm = 'FALL' ANDOffYear = 2016) OR (OffTerm = 'WINTER' ANDOffYear = 2017)

  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

  14. Join Example

  15. Cross Product Style • List tables in the FROM clause • List join conditions in the WHERE clause • Example 10 • SELECT O.OfferNo,O.CourseNo,F.FacFirstName,F.FacLastName • FROM Offering O, Faculty F • WHERE F.FacNo = O.FacNo • AND O.OffTerm = 'FALL' AND O.OffYear = 2016 • AND F.FacRank = 'ASST' AND O.CourseNo LIKE ‘IS%'

  16. Join Operator Style • Use INNER JOIN and ON keywords • FROM clause contains join operations • Example 11 • SELECTO.OfferNo,O.CourseNo,F.FacFirstName,F.FacLastName • FROM Offering O INNER JOIN Faculty F ONF.FacNo = O.FacNo • WHEREO.OffTerm = 'FALL' AND O.OffYear = 2016 • ANDF.FacRank = 'ASST' AND O.CourseNo LIKE ‘IS%'

  17. Name Qualification • Ambiguous column reference • More than one table in the query contains a column referenced in the query • Ambiguity determined by the query not the database • Use column name alone if query is not ambiguous • Qualify with table name if query is ambiguous • Readability versus writability

  18. Summarizing Tables • Row summaries important for decision-making tasks • Row summary • Result contains statistical (aggregate) functions • Conditions involve statistical functions • SQL keywords • Aggregate functions in the output list • GROUP BY: summary columns • HAVING: summary conditions

  19. GROUP BY Examples Example 12: Grouping on a single column SELECTFacRank, AVG(FacSalary) AS AvgSalary FROM Faculty GROUP BY FacRank Example 13: Row and group conditions SELECTStdMajor, AVG(StdGPA) AS AvgGpa FROM Student WHEREStdClass IN ('JR', 'SR') GROUP BY StdMajor HAVING AVG(StdGPA) > 3.1

  20. SQL Summarization Rules • Columns in SELECT and GROUP BY • SELECT: non aggregate and aggregate columns • GROUP BY: list all non aggregate columns • WHERE versus HAVING • Row conditions in WHERE • Group conditions in HAVING

  21. Summarization and Joins • Powerful combination • List join conditions in the WHERE clause • Example 14: List the number of students enrolled in each 2016 offering. • SELECTO.OfferNo, COUNT(*) AS NumStudents • FROM Enrollment E, Offering O • WHEREO.OfferNo = E.OfferNo • ANDO.OffYear = 2016 • GROUP BY O.OfferNo

  22. Conceptual Evaluation Process

  23. Conceptual Evaluation Lessons • Row operations before group operations • FROM and WHERE before GROUP BY and HAVING • Check row operations first • Grouping occurs only one time • Use small sample tables

  24. Conceptual Evaluation Problem Example 15: List the number of offerings taught in 2016 by faculty rank and department. Exclude combinations of faculty rank and department with less than two offerings taught. SELECTF.FacRank, F.FacDept, COUNT(*) AS NumOfferings FROM Faculty F, Offering O WHEREO.FacNo = F.FacNo ANDO.OffYear = 2016 GROUP BY F.FacRank, F.FacDept HAVING COUNT(*) > 1

  25. Query Formulation Process Problem Statement Database Representation Database Language Statement

  26. Critical Questions • What tables? • Columns in output • Conditions to test (including join conditions) • How to combine the tables? • Usually join PK to FK • More complex ways to combine • Individual rows or groups of rows? • Aggregate functions in output • Conditions with aggregate functions

  27. Efficiency Considerations • Little concern for efficiency • Intelligent SQL compilers • Correct and non redundant solution • No extra tables • No unnecessary grouping • Use HAVING for group conditions only • Chapter 8 provides additional tips for avoiding inefficient SELECT statements

  28. Advanced Problems • Joining multiple tables • Self joins • Grouping after joining multiple tables • Traditional set operators

  29. Joining Three Tables Example 16: List Leonard Vince’s teaching schedule in fall 2016. For each course, list the offering number, course number, number of units, days, location, and time. SELECTO.OfferNo, O.CourseNo, O.OffDays, C.CrsUnits, O.OffLocation, O.OffTime FROM Faculty F, Course C, Offering O WHEREF.FacNo = O.FacNoANDO.CourseNo = C.CourseNo AND O.OffYear = 2016 AND O. OffTerm = 'FALL' AND F.FacFirstName = 'LEONARD' AND F.FacLastName = 'VINCE'

  30. Joining Four Tables Example 17: List Bob Norbert’s course schedule in spring 2017. For each course, list the offering number, course number, days, location, time, and faculty name. SELECTO.OfferNo, C.CourseNo, O.OffDays, O.OffLocation, O.OffTime, F.FacFirstName, F.FacLastName FROM Faculty F, Offering O, Enrollment E, Student S WHEREO.OfferNo = E.OfferNoANDS.StdNo = E.StdNo ANDF.FacNo = O.FacNo AND O.OffYear = 2017 AND O.OffTerm = 'SPRING' AND S.StdFirstName = 'BOB' AND S.StdLastName = 'NORBERT'

  31. Self-Join • Join a table to itself • Usually involve a self-referencing relationship • Useful to find relationships among rows of the same table • Find subordinates within a preset number of levels • Find subordinates within any number of levels requires extensions to the SELECT statement

  32. Self-Join Example Example 18: List faculty members who have a higher salary than their supervisor. List the social security number, name, and salary of the faculty and supervisor. SELECTSubr.FacNo, Subr.FacLastName, Subr.FacSalary, Supr.FacNo, Supr.FacLastName, Supr.FacSalary FROMFacultySubr, FacultySupr WHERESubr.FacSupervisor = Supr.FacNo ANDSubr.FacSalary > Supr.FacSalary

  33. Multiple Joins Between Tables Example 19: List the names of faculty members and the course number for which the faculty member teaches the same course number as his or her supervisor in 2017. SELECTF.FacFirstName, F.FacLastName, O1.CourseNo FROM Faculty F, Offering O1, Offering O2 WHEREF.FacNo = O1.FacNo AND F.FacSupervisor = O2.FacNo AND O1.CourseNo = O2.CourseNo AND O1.OffYear = 2017 AND O2.OffYear = 2017

  34. Multiple Column Grouping Example 20: List the course number, the offering number, and the number of students enrolled. Only include courses offered in spring 2017. SELECTO.CourseNo, E.OfferNo, Count(*) AS NumStudents FROM Offering O, Enrollment E WHEREO.OfferNo = E.OfferNo ANDOffYear = 2017 AND OffTerm = 'SPRING’ GROUP BYE.OfferNo, O.CourseNo

  35. Traditional Set Operators A UNION B A INTERSECT B A MINUS B

  36. Union Compatibility • Requirement for the traditional set operators • Strong requirement • Same number of columns • Each corresponding column is compatible • Positional correspondence • Apply to similar tables by removing columns first

  37. SQL UNION Example Example 21: Retrieve basic data about all university people SELECTFacNo AS PerNo, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty UNION SELECTStdNo AS PerNo, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM Student

  38. INTERSECT Example Example 22: Show teaching assistants, faculty who are students. Only show the common columns in the result. SELECT FacNo AS PerNo, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty INTERSECT SELECT StdNo AS PerNo, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM Student

  39. Oracle MINUS Example Example 23: Show faculty who are not students (pure faculty). Only show the common columns in the result. SELECTFacNo AS PerNo, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty MINUS SELECTStdNo AS PerNo, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM Student

  40. Data Manipulation Statements • INSERT: adds one or more rows • UPDATE: modifies one or more rows • DELETE: removes one or more rows • Use SELECT statement to INSERT multiple rows • UPDATE and DELETE can use a WHERE clause • Not as widely used as SELECT statement

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

  42. UPDATE Example Example 25: Change the major and class of Homer Wells. UPDATE Student SETStdMajor = 'ACCT', StdClass = 'SO' WHEREStdFirstName = 'HOMER' AND StdLastName = 'WELLS'

  43. DELETE Example Example 26: Delete all IS majors who are seniors. DELETE FROM Student WHEREStdMajor = 'IS' AND StdClass = 'SR'

  44. Query Errors • Correct examples and guidelines not sufficient • Need awareness of incorrect examples and error types • Avoid errors • Diagnose incorrect statements • Reduce frustration and increase confidence

  45. Query Error Severity • Syntax: no execution • Redundancy: execution with correct rows but excessive resources • Semantic: execution with incorrect rows and sometimes excessive resources

  46. Query Error Examples • Syntax • Missing table • Unqualified column name • Misspelled keyword • Redundancy • Extra table • Unneeded GROUP BY clause • Semantic • Missing join or row condition • Missing parentheses

  47. Missing Join Condition Example 27: List the student name and offering number in which the grade is greater than 3.7 and the offering is given in fall 2016. SELECTS.StdFirstName, S.StdLastName, E.OfferNo FROM Student S, Enrollment E, Offering O WHERES.StdNo = E.StdNo AND O.OffYear = 2016 AND O.OffTerm = 'FALL' AND E.EnrGrade >= 3.7

  48. Unnecessary GROUP BY Example 28: List the student name and the offering number in which the grade is greater than 3.7 and the offering is given in fall 2016. SELECTS.StdFirstName, S.StdLastName, E.OfferNo FROM Student S, Enrollment E, Offering O WHERES.StdNo = E.StdNo AND O.OfferNo = E.OfferNo AND O.OffYear = 2016 AND O.OffTerm = 'FALL' AND E.EnrGrade >= 3.7 GROUP BY S.StdFirstName, S.StdLastName, E.OfferNo

  49. Missing Parentheses Example 29: List the offer number, course number, and faculty number for course offerings scheduled in spring or summer of 2016. SELECTOfferNo, CourseNo, FacNo, OffYear, OffTerm FROM Offering WHEREOffTerm = 'SPRING' OR OffTerm = 'SUMMER' AND OffYear = 2016

  50. Poor Coding Practices • Mixed join styles • Incompatible constant • LIKE operator in a date comparison • Poor clause alignment • LIKE operator without pattern matching characters

More Related