# Database: Exercises: Summaries, GROUP BY

Name __________________________________________
These questions all apply to the Aviation Database.

1. How many total charter flights have been made (number of records in CHARTER)?

       SELECT COUNT(*)
           FROM CHARTER;

2. What is the average miles per gallon for all flights? You can compute the average by dividing the total distance by the total gallons.

       SELECT SUM(CHAR_DISTANCE) / SUM(CHAR_FUEL_GALLONS)
           FROM CHARTER;

3. What is the average miles per gallon for each aircraft (AC_NUMBER)? Use aliases to get the right column headers

       SELECT AC_NUMBER, SUM(CHAR_DISTANCE) / SUM(CHAR_FUEL_GALLONS) AS MPG
           FROM CHARTER
           GROUP BY AC_NUMBER;

4. How many trips (number of records in CHARTER) has each customer made? List the number of trips, then the customer info. Show this in order of most frequent to least frequent travelers.

       SELECT COUNT(*) AS Trips, CUSTOMER.CUST_CODE AS Code, CUST_LNAME, CUST_FNAME
           FROM CUSTOMER, CHARTER
           WHERE CUSTOMER.CUST_CODE = CHARTER.CUST_CODE
           GROUP BY CUSTOMER.CUST_CODE, CUST_LNAME, CUST_FNAME
           ORDER BY COUNT(*) DESC;

5. How many times has each pilot flown each aircraft as copilot? Order by pilot's last name, then aircraft.

       SELECT COUNT(*), AC_NUMBER, EMP_LNAME, EMP_FNAME
           FROM CHARTER, EMPLOYEE
           WHERE CHARTER.CHAR_COPILOT = EMPLOYEE.EMP_NUM
           GROUP BY EMP_NUM, EMP_LNAME, EMP_FNAME, AC_NUMBER
           ORDER BY EMP_LNAME, EMP_FNAME, AC_NUMBER;

   Note that by putting grouping by the EMP_NUM (the primary key) in addition to the name fields, employees with the same name will show as separate records.