Retrieval Queries
Consider the following
Cars are identified by their license plates and are associated with their drivers. Cars can be of different types, e.g., ‘compact’, ‘standard’, ‘premium’, ‘van’, etc.
Hint: Sample data is shown in Figure 2.
A. Create an SQL statement to serve the described information needs
(i) Retrieve the name and revenue of the driver with the highest revenue.
(ii) Retrieve the total price of all journeys for each category of cars according to the number of seats of the cars (not type).
(iii) Retrieve fully-utilized rides, i.e., the number of passengers is the same as the number of seats.
Step by step
Solved in 4 steps
(iv) Retrieve the names and cities of all drivers who have cars of type ‘Premium’
(v) Show the cities and the number of drivers, where at least 1000 drivers operate.
(i.e., do not include cities where less than 1000 drivers operate).
(vi) Retrieve the names and emails of passengers who have not booked any ride yet