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:

USERS Table

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:

DRIVERS Table

DRIVERS Table

In the CARS table, all the cars in the InstantRide system are kept with the license plate, model and year:

CARS Table

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:

TRAVELS Table

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.

Task $1$:

The InstantRide Driver Relationship team wants the details of the drivers and the number of cars they use. Calculate the count of the CAR$\_$ID field in the TRAVELS table and return a table grouped by DRIVER$\_$ID and in descending order by the calculated COUNT column, displayed as CARS

Task $1$: Calculate the number of cars each driver uses.

Task $2$:

The InstantRide Driver Relationship team requires the detail of the drivers who has used more than one car for rides more than twice. Send them the DRIVER$\_$ID with the calculated COUNT of used cars, displayed as CARS in decreasing order.

Task $2$: Query the drivers that have used more than one car more than once.

Task $3$:

The InstantRide Marketing team is planning to create a heatmap of the start locations of the travels in InstantRide. The team requires the DISTINCT TRAVEL$\_$START$\_$LOCATION values for all travels in the system.

Task $3$: Query all the distinct start locations.

Task $4$:

The InstantRide User Satisfaction team requires the average and maximum number of rides users have taken so far with InstantRide. In addition, they would like to know the total number of travels. However, they need these details with the corresponding column names Average, Maximum and Total by using the AVG, MAX and SUM functions. In order to accomplish this, you will first need to create a derived table from the TRAVELS table to pass the TRAVEL$\_$ID count to the three mathematical functions.

Task $4$: Query the average, maximum, and total number of rides users have taken.

Task $5$:

The InstantRide Driver Relationship team wants to learn how many travels each driver has done in the month of October. You need to send them the DRIVER$\_$ID and two calculated columns: DAY and RIDES.The DAY column is calculated using the DAY$()$ function with the TRAVEL$\_$START$\_$TIME as the argument. The RIDES column is calculated by using the COUNT$()$ function to determine the number of rides given for each day. Filter the results with the MONTH function.

Task $5$: Query all travels given by drivers in the month of October.

Task $6$:

The InstantRide User Satisfaction team received a phone call from a user who might have left her wallet in the car. She indicated that the license plate of the car was starting with BB$-883$ but unfortunately, she could not remember the full license plate number. The team wants to get all travel information for the cars with the license plate starting with BB$-883$ You need to return all travel data from the TRAVELS table for the CAR$\_$ID which has a plate number compared with SUBSTR and UPPER functions.

Task $6$: Get all travel information for cars with a license plate starting in BB$-883.$