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:
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:
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 toSELECT all available data for the drivers and return back to the team.
Task: 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 theDRIVER_START_DATE column inside aSELECT statement for the DRIVERS table.
Task: Query the start date for each driver
Task 3:
The Driver Relationship team requires the following details about the drivers:
- All drivers with their rating in descending order
- All drivers currently having a rating higher than4
You need to return theDRIVER_ID andDRIVER_RATING couples in two separate tables.
Tasks: Query all drivers by their driver rating in descending order, Query all drivers with a rating higher than
4,
Extracted text: USER ID USER_FIRST_NAME USER LAST_NAME USER EMAIL 3001 Jack Hill
[email protected] 3002 Ryan Collins
[email protected] 3003 Nursin Yilmaz
[email protected] 3004 Sarah Price
[email protected] 3005 Bobby Griffin
[email protected] 3006 Randy Clark
[email protected] 3007 Jose Thomas
[email protected] 3008 Nursin Yilmaz
[email protected] DRIVER_ID DRIVER_FIRST_NAME DRIVER_LAST_NAME DRIVER_DRIVING_LICENSE_ID DRIVER_START_DATE DRIVER_DRIVING_LICENSE_CHECKED DRIVER_RATING 2001 Willie Butler 1874501 2019-09-12 4.4 2002 Justin Howard 1953853 2019-09-09 4.8 2003 Anthony Walker 1735487 2019-09-15 1 3.5 2004 Есe Yilmaz 1734747 2019-08-15 CAR_ID CAR_PLATE CAR_MODEL CAR YEAR 1001 BB-542-AB TOYOTA PRIUS 2018 1002 ВВ-883-ЕE TESLA MODEL 3 2019 1003 BB-451-ZN TOYOTA AURIS 2019 1004 BB-189-MM MERCEDES E200 2019
Extracted text: TRAVEL_ID TRAVEL_START_TIME TRAVEL_END_TIME TRAVEL_START_LOCATION TRAVEL_END_LOCATION TRAVEL_PRICE DRIVER_ID CAR_ID USER_ID TRAVEL_DISCOUNT 5001 2019-10-01 04:04:55 2019-10-01 04:14:19 9614 York Road 84 Church Lane 15.44 2001 1003 3005 NULL 5002 2019-10-01 05:57:33 2019-10-01 06:12:33 47 Church Street 68 High Street 20.56 2001 1003 3006 NULL 5003 2019-10-01 13:35:20 2019-10-01 13:45:10 2 Windsor Road 95 West Street 12.32 2002 1001 3002 NULL 5004 2019-10-02 08:44:48 2019-10-02 09:15:28 9060 Mill Lane 27 Main Road 30,49 2003 1002 3001 0.13 5005 2019-10-02 16:38:54 2019-10-02 16:48:10 2 Queensway 24 Mill Lane 11.15 2001 1003 3007 NULL 5006 2019-10-03 19:12:14 2019-10-03 19:23:45 50 Main Road 93 Broadway 14.61 2003 1002 3007 0.10 5007 2019-10-03 16:06:36 2019-10-03 16:08:56 39 Park Road 91 West Street 4.41 2002 1004 3003 0.14 5008 2019-10-03 17:17:12 2019-10-03 17:37:42 37 The Drive 17 Stanley Road 25.12 2001 1003 3001 0.25 5009 2019-10-03 21:16:48 2019-10-03 21:26:18 77 Mill Road 724 Springfield Road 13.55 2001 1003 3005 NULL 5010 2019-10-03 23:21:40 2019-10-03 23:39:10 16 Church Road 30 North Road 25.62 2003 1002 3003 0.20