# Database: Exercises: GROUP BY, HAVING, subqueries

Name __________________________________________
These questions apply to either the Aviation Database or University Database.

1. How many different pilots has customer Mickey Mouse, who always flies somewhere between semesters, flown with? Just give the number as below, which shows 6 pilots.

   Pilots
   6

       SELECT COUNT(DISTINCT CHAR_PILOT) AS Pilots
           FROM CHARTER, CUSTOMER
           WHERE CHARTER.CUS_CODE = CUSTOMER.CUS_CODE
             AND CUS_FNAME='Mickey' AND CUS_LNAME='Mouse';

   Or this can be done with a subquery.

       SELECT COUNT(DISTINCT CHAR_PILOT) AS Pilots
           FROM CHARTER
           WHERE CUS_CODE IN (SELECT CUS_CODE
                                FROM CUSTOMER
                                WHERE CUS_FNAME='Mickey' AND CUS_LNAME='Mouse');

   A different way to reduce the output to distinct pilots is to use GROUP BY.

       SELECT COUNT(*) AS Pilots
           FROM CHARTER, CUSTOMER
           WHERE CHARTER.CUS_CODE = CUSTOMER.CUS_CODE
             AND CUS_FNAME='Mickey' AND CUS_LNAME='Mouse'
           GROUP BY CHAR_PILOT;

2. How many hours (CHAR_HOURS_FLOWN) has each pilot flown as copilot?

   EMP_NUM | EMP_LNAME | EMP_FNAME | Hours
   etc

       SELECT EMP_NUM, EMP_LNAME, EMP_FNAME, SUM(CHAR_HOURS_FLOWN) AS Hours
           FROM CHARTER, EMPLOYEE
           WHERE CHARTER.COPILOT = EMPLOYEE.EMP_NUM
           GROUP BY EMP_NUM, EMP_LNAME, EMP_FNAME;

   There is no need to join thru the PILOT table because all the COPILOT foreign key has a primary key in both the PILOT and the EMPLOYEE tables. Also note that a NATURAL JOIN is not possible between CHARTER and PILOT or EMPLOYEE.
3. (1 point) Same as above, but only list those who have flown more than 10 hours as copilot.

   EMP_NUM | EMP_LNAME | EMP_FNAME | Hours
   etc

       above plus
           HAVING SUM(CHAR_HOURS_FLOWN) > 10;

   You may be able to use Hours in place of the expression, but Access doesn't support that use of aliases in the HAVING expression.
4. The university has a rule that students who have a GPA less than 1.5 may not register for more than 9 hours of credit (from `crs_credits` field). Write an SQL command that will find students who are violating this rule.

   stu_id | stu_lname | stu_fname | stu_gpa | Hours
   etc

       SELECT stu_id, stu_lname, stu_fname, stu_gpa, SUM(crs_credits)
           FROM Students, Enrollment, Sections, Courses
           WHERE Students.stu_id   = Enrollment.stu_id
             AND Enrollment.sec_id = Sections.sec_id
             AND Sections.crs_id   = Courses.crs_id
             AND stu_gpa < 1.5
           GROUP BY stu_id, stu_lname, stu_fname, stu_gpa
           HAVING SUM(crs_credits) > 9;

   It is much more efficient to make the `stu_gpa > 1.5` test in the WHERE clause than in the HAVING clause because it will eliminate most records before doing the work of grouping. Altho it doesn't seem necessary to use because of what we know about the database, SQL requires that all fields that are used directly (not just in a summary function) in the SELECT clause must also be mentioned in the GROUP BY clause.
5. Which courses offer the maximum number of credit hours (`crs_credits`)?

   crs_id | crs_credits
   etc

       SELECT crs_id, crs_credits
           FROM Courses
           WHERE crs_credits = (SELECT MAX(crs_credits)
                                  FROM Courses);