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 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. Only include courses offered in fall 2016 in which the average GPA is greater than 3.0.
SELECT Enrollment.OfferNo, CourseNo,
AVG(StdGPA) AS AvgGPA
FROM Offering, Enrollment, Student
WHERE Offering.OfferNo = Enrollment.OfferNo
AND Student.StdNo = Enrollment.StdNo
AND OffYear = 2016
AND OffTerm = 'FALL'
GROUP BY Enrollment.OfferNo, CourseNo
HAVING AVG(StdGPA) > 3.0;
Efficiency Considerations:
- Little concern for efficiency.
- Intelligent SQL compilers.
- Correct and non redundant solution.
-No extra tables.
-No unnecessary grouping.
-No missing join conditions.
Extra Table Redundancy:
Example 3: List the offering number, course number, and average GPA. Only include courses offered in fall 2016 in which the average GPA is greater than 3.0.
SELECT Enrollment.OfferNo, Offering.CourseNo,
AVG(StdGPA) AS AvgGPA
FROM Offering, Enrollment, Student, Course
WHERE Offering.OfferNo = Enrollment.OfferNo
AND Student.StdNo = Enrollment.StdNo
AND Course.CourseNo = Offering.CourseNo
AND OffYear = 2016 AND OffTerm = 'FALL'
GROUP BY Enrollment.OfferNo, Offering.CourseNo
HAVING AVG(StdGPA) > 3.0;
Our Official Website : Web Conquerors (https://www.webconquerors.com/)
Want to get digital services? Contact US
Want to know about our services? Our Services
Comments
Post a Comment