Answered step by step
Verified Expert Solution
Question
1 Approved 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
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: USER ID 3001 3002 3003 3004 3005 3006 3007 3008 USER FIRST NAME Jack Ryan Nursin Sarah Bobby Randy Jose Nursin USERS Table USER LAST NAME Cons VIMA Frie Grmn Clark Troms USER EMAIL @mal.com calgama.com nata@gmail.com spricemal.com bprmn@mail.com cork@mal.com thomas@mail.com nyman@mail.com In the DRIVERS table, all the drivers in the InstantRide are stored with their name, driving license number and check and rating information: DRIVER ID 2001 2002 2003 2004 CAR ID 1001 1002 DRIVER FIRST NAME DRIVERS Table 1003 Willie 1004 Justin Anthony Ece DRIVER_LAST_NAME DRIVER DRIVING LICENSE_ID CARS Table Butler Howard Walker Yilmaz CAR PLATE BB-542-AB BB-883-EE B8-451-ZN 1874501 BB-189-MM 1953853 1735487 1734747 CAR MODEL In the CARS table, all the cars in the Instant Ride system are kept with the license plate, model and year: TOYOTA PRIUS TESLA MODEL 3 TOYOTA AURIS DRIVER START DATE MERCEDES E200 2022-09-12 2022-09-09 2022-09-15 2022-08-15 DRIVER DRIVING LICENSE CHECKED 1 1 1 1 CAR_YEAR 2020 2021 2021 DRIVER RATING 2021 44 4.8 3.5 0.0 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: information are also available in the database: TRAVEL ID 5001 5002 5003 5004 5005 5006 5007 5006 5009 5010 TRAVEL START TIME 2022-10-01 01:04:55 2022-10-01 06:57:33 2022-10-01 13:35:20 2022-10-02 08:44:48 2022-10-02 16:38:54 2022-10-03 19:12:14 2022-10-03 16:06:36 2022-10-03 17:17:12 2022-10-03 21:1608 2022-10-03 23:21:40 TRAVEL_END_TIME 2022-10-01 04:14:19 TRAVELS Table 2022-10-01 06:12:33 2022-10-01 13:45:10 2022-10-02 09:15:28 2022-10-02 1648:10 2022-10-03 19:23:45 2022-10-03 16:08:56 2022-10-03 17:37:42 2022-10-03 21:26:18 2022-10-03 23:39:10 TRAVEL START LOCATION 9614 York Road 47 Church Street 2 Windsor Road 9060 Mill Lane 2 Queensway 50 Main Road 39 Park Road 37 The Drive 77 Mill Road 16 Church Road TRAVEL END LOCATION 84 Church Lane 60 High Street 95 West Street 27 Main Road 24 Mill Lane 93 Broadway 91 West Street 17 Stanley Road 724 Springfield Road 30 North Road TRAVEL PRICE 1544 20.56 12:32 3049 11.15 1461 4.41 25.12 13.55 25.62 DRIVER ID 2001 2001 2002 2003 2001 2003 2002 2001 2001 2000 CAR ID USER_ID TRAVEL_DISCOUNT 1003 1003 1001 1002 1003 1002 1004 1003 1003 1002 3005 3006 3002 3001 3007 3007 3003 3001 3005 3000 NULL NULL NULL 013 NULL 0.10 014 0.25 NULL 0.20 You are assigned as the database administrator to collect and manage transactional data of the Instant Ride 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. Grading After you have completed a problem and clicked the Run Query button, mark the task as complete. Checks will run to verify your work. Task 6: 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 Create a procedure to collect emails formatted as a mailing list. >
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Task VI The Marketing team of InstantStay wants to create mailing lists for the users H...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started