SQL
Tables for reference:
Questions:
In the USERS table, information for the users are stored with their first name, last name and email: USER_ID USER_FIRST_NAME USER_LAST_NAME USER_EMAIL 3001 Jack Hill j.hill@xmail.com 3002 Ryan Collins r.collins@xmail.com 3003 Nursin Yilmaz natak@gmail.com 3004 Sarah Price s.price@xmail.com 3005 Bobby Griffin b.griffin@xmail.com 3006 Randy Clark r.clark@xmail.com 3007 Jose Thomas j.thomas@xmail.com 3008 Nursin Yilmaz n.yilmaz@xmail.com 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: 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 1 4.4 2002 Justin Howard 1953853 2019-09-09 1 4.8 2003 Anthony Walker 1735487 2019-09-15 1 3.5 2004 Ece Yilmaz 1734747 2019-08-15 1 0 DRIVERS Table In the CARS table, all the cars in the InstantRide system are kept with the license plate, model and year: CAR ID CAR PLATE CAR_MODEL CAR_YEAR 1001 BB-542-AB TOYOTA PRIUS 2018 1002 BB-883-EE TESLA MODEL 3 2019 1003 BB-451-ZN TOYOTA AURIS 2019 1004 BB-189-MM MERCEDES E200 2019 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: TRAVEL_ID TRAVEL END TIME TRAVEL END LOCATION TRAVEL PRICE DRIVER_ID CAR ID USER ID TRAVEL_DISCOUNT TRAVEL_START_TIME 2019-10-01 04:04:55 TRAVEL_START_LOCATION 9614 York Road 5001 2019-10-01 04:14:19 15.44 2001 1003 3005 NULL 84 Church Lane 68 High Street 5002 2019-10-01 05:57:33 2019-10-01 06:12:33 47 Church Street 20.56 2001 1003 3006 NULL 5003 2019-10-01 13:35:20 2019-10-01 13:45:10 2 Windsor Road 2 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 TRAVELS Table 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 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 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 than 4 You need to return the DRIVER_ID and DRIVER_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. >
