Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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,

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. 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 s
The Finance team wants to calculate a VAT (8%) on the amount of the stay reservations in the database. However, the team wants to calculate the VAT after the discounts are applied on the prices. In addition, it is important to remember if there is no discount/offers, then the value is NULL in the TRAVELS table. Create a procedure called VATCalculator to calculate the VAT amount and return with the corresponding travel ID. In addition, execute the procedure to account for NULL values and send the respective result to the team. The VAT value should be rounded to 2 decimals.
Remember to redefine the MYSQL delimiter (;) when creating the procedure. This will prevent the commands from executing separately.
Task 1: Create a procedure to calculate the VAT.
The Driver Relationship team wants to arrange workshops and education materials to the drivers. However, the team wants to create clusters of the drivers based on their experience in InstantStay. To collect these detail, you will need to create a SQL function called DRIVER_STATUS to determine the level of the driver as follows:
MASTER more than 4 travels
PRO more than 2 travels
ROOKIE 2 or less travels
In addition, run the function to verify it works as expected and send them back the driver levels.
TASK 2: Create the DRIVER_STATUS function to create clusters of drivers based on experience.
The Development team of InstantStay wants to ensure that all email data of the users are lowercase. Otherwise, their login system could have problems finding the users. Therefore, they want you to ensure that each email in the table will always be lowercase after a new user is added. Create a TRIGGER called email_insert that will SET new email addresses to lowercase and run before new emails are added to the USERS table.
Task 4: Create a TRIGGER to ensure all new email address are lowercase.
The Developers team also wants you to ensure that emails are converted to lowercase after an update operation. Currently, new insertions are guaranteed to have lowercase emails, but there is no such guarantee for legacy emails. Therefore, the team wants to ensure that emails are being retained in a lowercase irrespective of any changes in the database. Create a new TRIGGER called email_update for the USERS table that runs before an UPDATE operation.
Task 5: Create a TRIGGER to run on the USERS table before any UPDATE operation.
The Marketing team of InstantStay wants to create mailing lists for the users. However, they need a single string which consists of all emails of the users combined (delimited) with a ;. You need to create a procedure called EmailList which uses a CURSOR that iteratively collects the email addresses in the USERS table and appends them to the string value. In addition, run the procedure and return back them the email list.
Task 6: Create a procedure to collect emails formatted as a mailing list.
I need help with all tasks, but Task 1, my output doesn't match what is expected: Found:
+---------+-----+
| travelID | VAT |
Error : AssertionError -
Timestamp: 2024-07-0522:15:32.635298
image text in transcribed

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

Beyond Big Data Using Social MDM To Drive Deep Customer Insight

Authors: Martin Oberhofer, Eberhard Hechler

1st Edition

0133509796, 9780133509793

More Books

Students also viewed these Databases questions