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 asCAR_YEARandCOUNTcolumns
• Number of cars with the model number/details asCAR_MODELandCOUNTcolumns
• A summary of total cars with their model and build year together withCAR_MODEL,CAR_YEARandCOUNTcolumns withROLLUPgrouping

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

Tasks

Query the car year count from theCARStable

0

Query the car model from theCARStable

0

Combine the car year and car model in a single summary results table.

0

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 theWITH ROLLUPmodifier and grouped by the specific column names, listed above, and send the data back to the team.

Task

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 theDRIVER_IDand theCAR_IDwith their usage counts as theTOTALcolumn for the travels occurred in the month ofOctober. Since the team wants to also learn the total number of the travels by the drivers you need to useGROUP BY ROLLUPfunctionality.

Task

Create a query to analyze car usage for the month ofOctober.

0

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$5or more.

You need to send the number of travels calculated for each day and month with their totals usingMonth,DayandCountcolumn names. Furthermore, you can name your subquery asPREMIUM_RIDESto work only with the travels with the price of5or higher.