Question

Using a HAVING statement, produce a unique list of pilot IDs of pilots who piloted more than 20 departures—order by pilot ID ascending.

a) SELECT DISTINCT pilot_id FROM pilots HAVING COUNT(departures) > 20 ORDER BY pilot_id ASC
b) SELECT UNIQUE pilot_id FROM pilots WHERE departures > 20 ORDER BY pilot_id ASC
c) SELECT pilot_id FROM pilots GROUP BY pilot_id HAVING COUNT(departures) > 20 ORDER BY pilot_id ASC
d) SELECT DISTINCT pilot_id FROM pilots WHERE departures > 20 ORDER BY pilot_id ASC

Answer 1

The correct answer is c) SELECT pilot_id FROM pilots GROUP BY pilot_id HAVING COUNT(departures) > 20 ORDER BY pilot_id ASC.

Step-by-step explanation:

The correct answer is c) SELECT pilot_id FROM pilots GROUP BY pilot_id HAVING COUNT(departures) > 20 ORDER BY pilot_id ASC.

In this query, we want to produce a unique list of pilot IDs of pilots who piloted more than 20 departures.

1. The SELECT statement is used to specify the columns we want to retrieve from the table.
2. The pilot_id column is selected to retrieve the pilot IDs.
3. The GROUP BY clause is used to group the results by pilot_id.
4. The HAVING clause is used to filter the groups based on the count of departures.
5. The COUNT(departures) function counts the number of departures for each pilot_id.
6. The ORDER BY clause is used to sort the result in ascending order based on pilot_id.