Question

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

In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information:

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

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:

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 Car Maintenance team wants to pre-order some replacement parts for the cars registered in the InstantRide system. The team wants three sets of data:

• Number of cars with their build years as CAR_YEAR and COUNT columns
• Number of cars with the model number/details as CAR_MODEL and COUNT columns
• A summary of total cars with their model and build year together with CAR_MODEL, CAR_YEAR and COUNT columns with ROLLUP grouping

You need to send the requested data in three tables to the Car Maintenance team

Task 2:

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.

Task 3:

The Driver Relationship team wants to analyze drivers and their car usages in InstantRide for the month October. Thus, they want to focus on each driver and respective car allocation occurred. In more detail, the team requires the DRIVER_ID and the CAR_ID with their usage counts as the TOTAL column for the travels occurred in the month of October. Since the team wants to also learn the total number of the travels by the drivers you need to use GROUP BY ROLLUP functionality.

Task 4:

As a part of marketing strategy, the Marketing team continuously conducting an advertising campaign on different channels. The team would like to know how this campaign is impacting the overall rides being taken through the InstantRide, specifically the rides which cost $5 or more.

You need to send the number of travels calculated for each day and month with their totals using Month, Day and Count column names. Furthermore, you can name your subquery as PREMIUM_RIDES to work only with the travels with the price of 5 or higher.

Answer 1

Task 1:

-Select build_year, count (distinct car_ID) from CarTbl group by build_year;

-Select car_model ,count (distinct car_ID) from CarTbl group by car_model;

-Select build_year, car_model count (distinct car_ID) from CarTbl;

Task 2:

- select month, day, count( distinct carID ) from TravelTbl

Task 3:

- select month, count (distinct carID,driverID) from TravelTbl where month= "October" ;

Task 4:

- select month,day,count (distinct carID, driverID) where price>=5$

-