The InstantRide Finance team wants to know the average discount amounts for each car in the InstantRide. Calculate the average discount amount as monetary value for the travels where a discount is applied. You need to create a subquery over the TRAVELS table to retrieve CAR_ID and DISCOUNT_AMOUNT, calculated with 2 decimals using the ROUND function.To calculate the DISCOUNT_AMOUNT, multiply the TRAVEL_PRICE by the TRAVEL_DISCOUNT where the TRAVEL_DISCOUNT value is not NULL. Round the result to 2 decimals.Then you can use this subquery to get the CAR_ID and AVG of DISCOUNT_AMOUNT values, once again using the ROUND function on the average results. Group the results by the CAR_ID. Use CAR_ID and DISCOUNT_AMOUNT as column aliases and return it back to the Finance team. Below is a transcribed table listing details of different cars. This information may be used for educational purposes, such as database management or vehicle inventory lessons.| **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 | The table provided shows detailed records of travel data, including information about each trip such as travel identification, timing, locations, pricing, and associated personnel. Below is a description of each column in the table:1. **TRAVEL_ID**: This column contains a unique identifier for each travel record, ranging from 5001 to 5010.2. **TRAVEL_START_TIME**: This column indicates the starting date and time of each travel entry. For instance, the first entry began on October 1, 2019, at 04:04:55.3. **TRAVEL_END_TIME**: This column records the ending date and time for each journey, with the first entry ending on October 1, 2019, at 04:14:19.4. **TRAVEL_START_LOCATION**: This column displays the initial location address where each trip commenced. Example: 9614 York Road.5. **TRAVEL_END_LOCATION**: This column denotes the final location address where each trip concluded. Example: 84 Church Lane.6. **TRAVEL_PRICE**: The column lists the price for each respective trip. For example, the first trip cost $15.44.7. **DRIVER_ID**: Each driver's unique identifier is listed here, with numbers like 2001, 2002, and 2003.8. **CAR_ID**: This column includes the car identification number used for each journey, such as 1003, 1002, or 1004.9. **USER_ID**: Here, the unique user identifier of the individual who undertook the journey is recorded, ranging from 3001 to 3007.10. **TRAVEL_DISCOUNT**: This column shows any discounts applied to the travel price. Some entries have a NULL value indicating no discount was applied, while others list specific discount amounts, e.g., 0.13 or 0.25.This table provides a comprehensive view of travel data, capturing essential aspects necessary for record-keeping and analysis in an educational or operational setting.

SINCE We are fetching data from same table we do not need to specify same table name twice.…

Answered: InstantRide Finance team wants to know…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

The InstantRide Finance team wants to know the average discount amounts for each car in the InstantRide. Calculate the average discount amount as monetary value for the travels where a discount is applied. You need to create a subquery over the TRAVELS table to retrieve CAR_ID and DISCOUNT_AMOUNT, calculated with 2 decimals using the ROUND function.

To calculate the DISCOUNT_AMOUNT, multiply the TRAVEL_PRICE by the TRAVEL_DISCOUNT where the TRAVEL_DISCOUNT value is not NULL. Round the result to 2 decimals.

Then you can use this subquery to get the CAR_ID and AVG of DISCOUNT_AMOUNT values, once again using the ROUND function on the average results. Group the results by the CAR_ID. Use CAR_ID and DISCOUNT_AMOUNT as column aliases and return it back to the Finance team.

Below is a transcribed table listing details of different cars. This information may be used for educational purposes, such as database management or vehicle inventory lessons.

| **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 |

The table provided shows detailed records of travel data, including information about each trip such as travel identification, timing, locations, pricing, and associated personnel. Below is a description of each column in the table:

1. **TRAVEL_ID**: This column contains a unique identifier for each travel record, ranging from 5001 to 5010.

2. **TRAVEL_START_TIME**: This column indicates the starting date and time of each travel entry. For instance, the first entry began on October 1, 2019, at 04:04:55.

3. **TRAVEL_END_TIME**: This column records the ending date and time for each journey, with the first entry ending on October 1, 2019, at 04:14:19.

4. **TRAVEL_START_LOCATION**: This column displays the initial location address where each trip commenced. Example: 9614 York Road.

5. **TRAVEL_END_LOCATION**: This column denotes the final location address where each trip concluded. Example: 84 Church Lane.

6. **TRAVEL_PRICE**: The column lists the price for each respective trip. For example, the first trip cost $15.44.

7. **DRIVER_ID**: Each driver's unique identifier is listed here, with numbers like 2001, 2002, and 2003.

8. **CAR_ID**: This column includes the car identification number used for each journey, such as 1003, 1002, or 1004.

9. **USER_ID**: Here, the unique user identifier of the individual who undertook the journey is recorded, ranging from 3001 to 3007.

10. **TRAVEL_DISCOUNT**: This column shows any discounts applied to the travel price. Some entries have a NULL value indicating no discount was applied, while others list specific discount amounts, e.g., 0.13 or 0.25.

This table provides a comprehensive view of travel data, capturing essential aspects necessary for record-keeping and analysis in an educational or operational setting.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions