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 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,
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 to SELECT all available data for the drivers and return back to the team.
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 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.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps