Query Formulation with SQL in Software

Generation QR Code in Software Query Formulation with SQL

4

Join with Grouping on Multiple Columns List the course number, the offering number, and the number of students enrolled Only include courses offered in spring 2 0 0 6

SELECT CourseNo, EnrollmentOfferNo, Count(*) AS NumStudents FROM Offering, Enrollment WHERE OfferingOfferNo = EnrollmentOfferNo AND OffYear = 2006 AND OffTerm = 'SPRING' GROUP BY EnrollmentOfferNo, CourseNo

Example 438 demonstrates another problem involving joins and grouping An impor tant part of this problem is the need for the Student table and the HAVING condition They are needed because the problem statement refers to an aggregate function involving the Student table

Joins, Grouping, a n d Group Conditions List the course number, the offering number, and the average GPA of students enrolled Only include courses offered in fall 2 0 0 5 in which the average GPA of enrolled students is greater than 30

SELECT CourseNo, EnrollmentOfferNo, Avg(StdGPA) AS AvgGPA FROM Student, Offering, Enrollment WHERE OfferingOfferNo = EnrollmentOfferNo AND EnrollmentStdSSN = StudentStdSSN AND OffYear = 2005 AND OffTerm = 'FALL' GROUP BY CourseNo, EnrollmentOfferNo HAVING Avg(StdGPA) > 30

In SQL, you can directly use the traditional set operators with the UNION, INTERSECT, and EXCEPT keywords Some DBMSs including Microsoft Access do not support the INTERSECT and EXCEPT keywords As with relational algebra, the problem is always to make sure that the tables are union compatible In SQL, you can use a SELECT statement to make tables compatible by listing only compatible columns Examples 439 through 441 demonstrate set operations on column subsets of the Faculty and Student tables The columns have been renamed to avoid confusion

UNION Query Show all faculty and students Only s h o w the c o m m o n columns in the result SELECT 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 Student SSN 098765432 123456789 124567890 234567890 345678901 456789012 543210987 567890123 654321098 678901234 765432109 789012345 876543210 890123456 901234567 987654321 FirstName LEONARD HOMER BOB CANDY WALLY JOE VICTORIA MARIAH LEONARD TESS NICKI ROBERTO CRISTOPHER LUKE WILLIAM JULIA LastName VINCE WELLS NORBERT KENDALL KENDALL ESTRADA EMMANUEL DODGE FIBON DODGE MACON MORALES COLAN BRAZZI PILGRIM MILLS City SEATTLE SEATTLE BOTHELL TACOMA SEATTLE SEATTLE BOTHELL SEATTLE SEATTLE REDMOND BELLEVUE SEATTLE SEATTLE SEATTLE BOTHELL SEATTLE State WA WA WA WA WA WA WA WA WA WA WA WA WA WA WA WA

EXAMPLE 440 (Oracle)

INTERSECT Query Show teaching assistants, faculty w h o are students Only s h o w the c o m m o n columns in the result SELECT FacSSN AS SSN, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty INTERSECT SELECT StdSSN AS SSN, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM Student SSN 876543210 FirstName CRISTOPHER LastName COLAN City SEATTLE State WA

4

E X A M P L E 441 (Oracle)

Difference Query Show faculty w h o are not students (pure faculty) Only s h o w the c o m m o n columns in the result Oracle uses the MINUS keyword instead of the EXCEPT keyword used in S Q L 2 0 0 3 SELECT FacSSN AS SSN, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty MINUS SELECT StdSSN AS SSN, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM Student SSN 098765432 543210987 654321098 765432109 987654321 FirstName LEONARD VICTORIA LEONARD NICKI JULIA LastName VINCE EMMANUEL FIBON MACON MILLS City SEATTLE BOTHELL SEATTLE BELLEVUE SEATTLE State WA WA WA WA WA

B y default, duplicate rows are removed in the results o f S Q L statements with the U N I O N , INTERSECT, and E X C E P T ( M I N U S ) keywords If you want to retain duplicate rows, use the A L L keyword after the operator For example, the U N I O N A L L keyword per forms a union operation but does not remove duplicate rows

-+()