Database Management System - Chapter 4: Query Formulation with SQL

Chapter 4Query Formulation with SQLOutline BackgroundGetting startedJoining tablesSummarizing tablesProblem solving guidelinesAdvanced problemsData manipulation statementsWhat is SQL?Structured Query LanguageLanguage for database definition, manipulation, and controlInternational standardStandalone and embedded usageIntergalactic database speakSQL StatementsStatementChapterCREATE TABLE3, 18SELECT3, 9, 10INSERT, UPDATE3, 10DELETE3, 9, 10CREATE VIEW10CREATE TRIGGER11GRANT, REVOKE14COMMIT, ROLLBACK15CREATE TYPE18SQL StandardizationRelatively simple standard: SQL-86 and revision (SQL-89)Modestly complex standard: SQL-92Complex standards: SQL:1999 and SQL:2003SQL ConformanceNo official conformance testingVendor claims about conformanceReasonable conformance on Core SQLLarge variance on conformance outside of Core SQLDifficult to write portable SQL code outside of Core SQLSELECT Statement OverviewSELECT FROM WHERE GROUP BY HAVING ORDER BY Expression: combination of columns, constants, operators, and functionsUniversity DatabaseFirst SELECT ExamplesExample 1 SELECT * FROM FacultyExample 2 (Access) SELECT * FROM Faculty WHERE FacSSN = '543210987'Example 3 SELECT FacFirstName, FacLastName, FacSalary FROM FacultyExample 4 SELECT FacFirstName, FacLastName, FacSalary FROM Faculty WHERE FacSalary > 65000 AND FacRank = 'PROF'Using ExpressionsExample 5 (Access) SELECT FacFirstName, FacLastName, FacCity, FacSalary*1.1 AS IncreasedSalary, FacHireDate FROM Faculty WHERE year(FacHireDate) > 1996 Example 5 (Oracle)SELECT FacFirstName, FacLastName, FacCity, FacSalary*1.1 AS IncreasedSalary, FacHireDate FROM Faculty WHERE to_number(to_char(FacHireDate, 'YYYY')) > 1996Inexact 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 CourseNo LIKE 'IS*' Example 6 (Oracle) SELECT * FROM Offering WHERE CourseNo LIKE 'IS%'Using Dates Dates are numbers Date constants and functions are not standardExample 7 (Access)SELECT FacFirstName, FacLastName, FacHireDate FROM Faculty WHERE FacHireDate BETWEEN #1/1/1999# AND #12/31/2000#Example 7 (Oracle)SELECT FacFirstName, FacLastName, FacHireDate FROM Faculty WHERE FacHireDate BETWEEN '1-Jan-1999' AND '31-Dec-2000'Other Single Table ExamplesExample 8: Testing for null values SELECT OfferNo, CourseNo FROM Offering WHERE FacSSN IS NULL AND OffTerm = 'SUMMER' AND OffYear = 2006Example 9: Mixing AND and OR SELECT OfferNo, CourseNo, FacSSN FROM Offering WHERE (OffTerm = 'FALL' AND OffYear = 2005) OR (OffTerm = 'WINTER' AND OffYear = 2006)Join OperatorMost databases have many tablesCombine tables using the join operatorSpecify matching conditionCan be any comparison but usually =PK = FK most common join conditionRelationship diagram useful when combining tablesJoin ExampleCross Product Style List tables in the FROM clause List join conditions in the WHERE clauseExample 10 (Access)SELECT OfferNo, CourseNo, FacFirstName, FacLastName FROM Offering, Faculty WHERE OffTerm = 'FALL' AND OffYear = 2005 AND FacRank = 'ASST' AND CourseNo LIKE 'IS*' AND Faculty.FacSSN = Offering.FacSSNJoin Operator Style Use INNER JOIN and ON keywords FROM clause contains join operationsExample 11 (Access)SELECT OfferNo, CourseNo, FacFirstName, FacLastName FROM Offering INNER JOIN Faculty ON Faculty.FacSSN = Offering.FacSSN WHERE OffTerm = 'FALL' AND OffYear = 2005 AND FacRank = 'ASST' AND CourseNo LIKE 'IS*'Name QualificationAmbiguous column referenceMore than one table in the query contains a column referenced in the queryAmbiguity determined by the query not the databaseUse column name alone if query is not ambiguousQualify with table name if query is ambiguousReadability versus writabilitySummarizing TablesRow summaries important for decision-making tasksRow summaryResult contains statistical (aggregate) functionsConditions involve statistical functionsSQL keywordsAggregate functions in the output listGROUP BY: summary columnsHAVING: summary conditionsGROUP BY ExamplesExample 12: Grouping on a single column SELECT FacRank, AVG(FacSalary) AS AvgSalary FROM Faculty GROUP BY FacRankExample 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 SQL Summarization RulesColumns in SELECT and GROUP BYSELECT: non aggregate and aggregate columns GROUP BY: list all non aggregate columnsWHERE versus HAVINGRow conditions in WHEREGroup conditions in HAVINGSummarization and Joins Powerful combination List join conditions in the WHERE clauseExample 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 = 2006 GROUP BY Offering.OfferNoConceptual Evaluation ProcessConceptual Evaluation LessonsRow operations before group operationsFROM and WHERE before GROUP BY and HAVING Check row operations firstGrouping occurs only one timeUse small sample tablesConceptual Evaluation ProblemExample 15: List the number of offerings taught in 2006 by faculty rank and department. Exclude combinations of faculty rank and department with less than two offerings taught. SELECT FacRank, FacDept, COUNT(*) AS NumOfferings FROM Faculty, Offering WHERE Offering.FacSSN = Faculty.FacSSN AND OffYear = 2006 GROUP BY FacRank, FacDept HAVING COUNT(*) > 1Query Formulation ProcessProblem StatementDatabase RepresentationDatabase Language StatementCritical QuestionsWhat tables?Columns in outputConditions to test (including join conditions)How to combine the tables?Usually join PK to FKMore complex ways to combineIndividual rows or groups of rows?Aggregate functions in outputConditions with aggregate functionsEfficiency ConsiderationsLittle concern for efficiencyIntelligent SQL compilersCorrect and non redundant solutionNo extra tablesNo unnecessary groupingUse HAVING for group conditions onlyChapter 8 provides additional tips for avoiding inefficient SELECT statementsAdvanced ProblemsJoining multiple tablesSelf joinsGrouping after joining multiple tablesTraditional set operatorsJoining Three TablesExample 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, Course, Offering WHERE Faculty.FacSSN = Offering.FacSSN AND Offering.CourseNo = Course.CourseNo AND OffYear = 2005 AND OffTerm = 'FALL' AND FacFirstName = 'Leonard' AND FacLastName = 'Vince'Joining Four TablesExample 17: List Bob Norbert’s course schedule in spring 2006. For each course, list the offering number, course number, days, location, time, and faculty name. SELECT Offering.OfferNo, Offering.CourseNo, OffDays, OffLocation, OffTime, FacFirstName, FacLastName FROM Faculty, Offering, Enrollment, Student WHERE Offering.OfferNo = Enrollment.OfferNo AND Student.StdSSN = Enrollment.StdSSN AND Faculty.FacSSN = Offering.FacSSN AND OffYear = 2006 AND OffTerm = 'SPRING' AND StdFirstName = 'BOB' AND StdLastName = 'NORBERT'Self-JoinJoin a table to itselfUsually involve a self-referencing relationshipUseful to find relationships among rows of the same tableFind subordinates within a preset number of levelsFind subordinates within any number of levels requires embedded SQLSelf-Join ExampleExample 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.SELECT Subr.FacSSN, Subr.FacLastName, Subr.FacSalary, Supr.FacSSN, Supr.FacLastName, Supr.FacSalary FROM Faculty Subr, Faculty Supr WHERE Subr.FacSupervisor = Supr.FacSSN AND Subr.FacSalary > Supr.FacSalary Multiple Joins Between TablesExample 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 2006.SELECT FacFirstName, FacLastName, O1.CourseNo FROM Faculty, Offering O1, Offering O2 WHERE Faculty.FacSSN = O1.FacSSN AND Faculty.FacSupervisor = O2.FacSSN AND O1.OffYear = 2006 AND O2.OffYear = 2006 AND O1.CourseNo = O2.CourseNo Multiple Column GroupingExample 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 Traditional Set OperatorsA UNION BA INTERSECT BA MINUS BUnion CompatibilityRequirement for the traditional set operatorsStrong requirementSame number of columnsEach corresponding column is compatiblePositional correspondenceApply to similar tables by removing columns firstSQL UNION ExampleExample 21: Retrieve basic data about all university peopleSELECT FacSSN AS SSN, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty UNION SELECT StdSSN AS SSN, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM StudentOracle INTERSECT ExampleExample 22: Show teaching assistants, faculty who are students. Only show the common columns in the result.SELECT FacSSN AS SSN, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty INTERSECTSELECT StdSSN AS SSN, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM Student Oracle MINUS ExampleExample 23: Show faculty who are not students (pure faculty). Only show the common columns in the result.SELECT FacSSN AS SSN, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty MINUSSELECT StdSSN AS SSN, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM StudentData Manipulation StatementsINSERT: adds one or more rowsUPDATE: modifies one or more rowsDELETE: removes one or more rowsUse SELECT statement to INSERT multiple rowsUPDATE and DELETE can use a WHERE clauseNot as widely used as SELECT statementINSERT ExampleExample 24: Insert a row into the Student table supplying values for all columns.INSERT INTO Student (StdSSN, StdFirstName, StdLastName, StdCity, StdState, StdZip, StdClass, StdMajor, StdGPA) VALUES ('999999999','JOE','STUDENT','SEATAC', 'WA','98042-1121','FR','IS', 0.0) UPDATE ExampleExample 25: Change the major and class of Homer Wells.UPDATE Student SET StdMajor = 'ACCT', StdClass = 'SO' WHERE StdFirstName = 'HOMER' AND StdLastName = 'WELLS' DELETE ExampleExample 26: Delete all IS majors who are seniors.DELETE FROM Student WHERE StdMajor = 'IS' AND StdClass = 'SR' SummarySQL is a broad languageSELECT statement is complexUse problem solving guidelinesLots of practice to master query formulation and SQL