The Car Maintenance team wants to learn how many times each car is used in every month and day to organize their maintenance schedules. The team wants a table with the following column names and information: Car ID Month Day Count
Question Setup:
Scenario and
InstantRide is the new ride sharing application in the city and it has just started its operations. With the help of the InstantRide mobile application, the users request a ride with their location. Drivers and cars are assigned to the request; and then the driver picks up the user to ride their requested location. Information for the users, drivers and cars are stored in the database as well as the travel transactions.
In the USERS table, information for the users are stored with their first name, last name and email:
| USER_ID | USER_FIRST_NAME | USER_LAST_NAME | USER_EMAIL |
USERS Table
In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information:
| DRIVER_ID | DRIVER_FIRST_NAME | DRIVER_LAST_NAME | DRIVER_DRIVING LICENSE_ID | DRIVER_START_DATE | DRIVER_DRIVING_LICENSE_CHECKED | DRIVER_RATING |
DRIVERS Table
In the CARS table, all the cars in the InstantRide system are kept with the license plate, model and year:
| CAR_ID | CAR_PLATE | CAR_MODEL | CAR_YEAR |
CARS Table
Finally, the transactions of the rides are stored in the TRAVELS table. For each travel, start and end time with location are stored. In addition, the involved driver, car and user are listed for each drive. Price and discount information are also available in the database:
| TRAVEL_ID | TRAVEL_START_TIME | TRAVEL_END_TIME | TRAVEL_START_LOCATION | TRAVEL_END_LOCATION | TRAVEL_PRICE | DRIVER_ID | CAR_ID | USER_ID | TRAVEL_DISCOUNT |
TRAVELS Table
You are assigned as the database administrator to collect and manage transactional data of the InstantRide operations. Your main task is to create SQL scripts to help other teams to retrieve the requested data. In the following activities, you will create the scripts, run against the database and send the result to the corresponding teams.
QUESTION:
The Car Maintenance team wants to learn how many times each car is used in every month and day to organize their maintenance schedules. The team wants a table with the following column names and information:
- Car ID
- Month
- Day
- Count
You need to create a summary table using the WITH ROLLUP modifier and grouped by the specific column names, listed above, and send the data back to the team.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
