Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Please help with task 7 . MySQL Lab Instructions Before running queries, check the selected database matches the database indicated in the instructions. The Run

Please help with task 7.
MySQL Lab Instructions
Before running queries, check the selected database matches the database indicated in the instructions.
The "Run Code" button will run the contents of the query.sql file only.
Complete each task in order and press the Run Test button for that specific task before moving on.
Only select Calculate Grade when you are satisfied with your answer.
Scenario and Database Model: InstantRide
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:
Drivers are essential for InstantRide, and the Driver Relationship team is responsible for their integration and success. The team requires all the driver detail in the system for creating a new dashboard. You need to SELECT all available data for the drivers and return back to the team.
Task 1: Query all the driver data from the DRIVERS table.
Task 2:
The Driver Relationship team also requests the joining dates of the drivers to create a timeline. In the table, you only need to return the joining date of the drivers. You need to only return the DRIVER_START_DATE column inside a SELECT statement for the DRIVERS table.
Task 2: Query the start date for each driver.
Task 3-4:
The Driver Relationship team would like the DRIVER_ID and DRIVER_RATING of drivers currently having a rating higher than 4 in descending order.
Task 3: Query all drivers by their driver rating in descending order.
Task 4: Query all drivers with a rating higher than 4.
Task 5:
The InstantRide User Satisfaction team is a core team for InstantRide, and they focus on increasing the customer satisfaction. They want to learn the travel time for each ride in the system. You need to return the **USER_ID##and the **TRAVEL_TIME column which is calculated using the TIMEDIFF function on the TRAVEL_END_TIME and the TRAVEL_START_TIME.
Task 5: Query the travel time for each ride.
Task 6:
User Satisfaction team wants to send monthly summaries for each user. They need the following details with the user ID:
The last day of the month when the users traveled most recently
One week after the last day of the month when the users traveled most recently
You need to return a three-column output with
USER_ID
LAST_TRAVEL_MONTH
NOTIFICATION
You need to return a three-column output with USER_ID, LAST_TRAVEL_MONTH and NOTIFICATION, LAST_TRAVEL_MONTH should be calculated using the MAX of the LAST_DAY of the TRAVEL_END_TIME field. Similarly, NOTIFICATION should be calculated with DATE_ADD function to add one week.
Task 6: Query monthly summaries for each user.
Task 7:
The Marketing team of InstantRide wants to know that how many discounts have been offered for each ride. You need to calculate this information for each travel where a discount is applied and return two columns: TRAVEL_ID and **DISCOUNT_AMOUNT##In addition, you need to return the calculation as a money value using the ROUND function to **2 decimals.
Task 7: Calculate the total amount of discounts given.
Test Feedback:
Status: FAILED!
Check: 1
Test: Calculate the total amount of discounts given
Reason: The following error is preventing tests from completing: 1054(42S22): Unknown column 'DISCOUNT_AMOUNT' in 'field list'
Error : ProgrammingError -1054(42S22) Unknown column 'DISCOUNT_AMOUNT' in 'field list'
The following was used for task 7:
SELECT
TRAVEL_ID,
ROUND(DISCOUNT_AMOUNT, 2) AS DISCOUNT_AMOUNT
FROM TRAVELS
WHERE DISCOUNT_AMOUNT >0;

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Conceptual Database Design An Entity Relationship Approach

Authors: Carol Batini, Stefano Ceri, Shamkant B. Navathe

1st Edition

0805302441, 978-0805302448

More Books

Students also viewed these Databases questions