Educational Setup

SQL Set Operators   Traditional Set Operators: 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. SQL UNION Example:           Example 1: Retrieve basic data about all university people.                     SELECT FacNo AS PerNo, FacFirstName                                      ...

Query Formulation   Query Formulation Process: Critical Questions: What tables?           - Columns in result.          - Conditions to test (including join conditions). How to combine the tables?           - Usually join of PK to FK. More complex ways to combine Individual rows or groups of rows?           - Aggregate functions in result.          - Conditions with aggregate functions. University Database Diagram: Summarization and Joins 1:      Example 1: List the number of students enrolled in each 2017 course offering showing the offer number and number of students in the result.                SELECT Offering.OfferNo,                                     COUNT (*) AS NumSt...

Join Operation Using SELECT Statement   Cross Product Style: List tables in the FROM clause. List join conditions in the WHERE clause. Example:           SELECT OfferNo, CourseNo, FacFirstName, FacLastName            FROM Offering, Faculty            WHERE OffTerm = 'FALL'            AND OffYear = 2016 AND FacRank = 'ASST'            AND CourseNo LIKE 'IS%'            AND Faculty.FacNo = Offering.FacNo; Join Operator Style: Use INNER JOIN and ON keywords. FROM clause contains join operations. Example:           SELECT OfferNo, CourseNo, FacFirstName, FacLastName            FROM Offering INNER JOIN Faculty            ON Faculty.FacNo = Offering.FacNo        ...

Major SQL Statements Statement                                              Statement Type CREATE TABLE                                                              Definitional, Control CREATE VIEW                                                     Definitional CREATE TYPE                                                     Definitional SELECT                      ...

Database Insert Statements University Database Insert Statements : INSERT INTO student         (stdNo, stdFirstName, stdLastName, stdCity,          stdState, stdMajor, stdClass, stdGPA, stdZip)         VALUES ('123-45-6789','HOMER','WELLS','SEATTLE','WA','IS','FR',3.00,'98121-1111');   INSERT INTO student         (stdNo, stdFirstName, stdLastName, stdCity,          stdState, stdMajor, stdClass, stdGPA, stdZip)         VALUES ('124-56-7890','BOB','NORBERT','BOTHELL','WA','FIN','JR',2.70,'98011-2121');   INSERT INTO student         (stdNo, stdFirstName, stdLastName, stdCity,          stdState, stdMajor, stdClass, stdGPA, stdZip)         VALUES ('234-56-78...