Skip to main content

Posts

SQL Set Operators

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                                                         AS FirstName, FacLastName                                                         AS LastName, FacCity                                                         AS City, FacState                                                         AS State                      FROM Faculty                                               UNION                      SELECT StdNo AS PerNo, StdFirstName                                              
Post a Comment
Read more
Recent posts

Query Formulation

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 NumStudents                      FROM Enrollment, Offering                      WHERE Offering.OfferNo = Enrollment.OfferNo                      AND OffYear = 2017                      GROUP BY Offering.OfferNo; Summarization and Joins 2:      Example 2: List the offering number, course number, and average GPA.  Onl
Post a Comment
Read more

Join Operation

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            WHERE OffTerm = 'FALL'            AND OffYear = 2016            AND FacRank = 'ASST'            AND CourseNo LIKE 'IS%'; Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  Our Services
Post a Comment
Read more

Major SQL Statements

Major SQL Statements Statement                                              Statement Type CREATE TABLE                                                              Definitional, Control CREATE VIEW                                                     Definitional CREATE TYPE                                                     Definitional SELECT                                                                  Manipulation INSERT, UPDATE, DELETE                                   Manipulation COMMIT, ROLLBACK                                            Manipulation CREATE TRIGGER                                                     Control, Manipulation GRANT, REVOKE                                                         Control Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  Our Services
Post a Comment
Read more

Database Insert Statements

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-7890','CANDY','KENDALL','TACOMA','WA','ACCT','JR',3.50,'99042-3321');   INSERT INTO student         (stdNo, stdFirstName, stdLastName, stdCity,      
Post a Comment
Read more