Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following: a) Add a new col

Exercises · Q8

Q. Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

a) Add a new column Discount in the INVENTORY table.

b) Set appropriate discount values for all cars keeping in mind the following:

(i) No discount is available on the LXI model.

(ii) VXI model gives a 10 per cent discount.

(iii) A 12 per cent discount is given on cars other than LXI model and VXI model.

c) Display the name of the costliest car with fuel type "Petrol".

d) Calculate the average discount and total discount available on Baleno cars.

e) List the total number of cars having no discount.

Uttar Pradesh UpmspTextbookSubjective· 4mImportance★★★★★

51% · 48/95 Questions

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — SQL Aggregate Functions

Imagine you have a giant box of receipts from a month of sales at a small shop. You don't want to look at every single receipt one by one — you want a single, useful number that summarises the whole pile. How much total money came in? What was the average sale? How many customers bought something? That instinct — to collapse a pile of raw details into one meaningful number — is exactly what an SQL aggregate function does.

In a database, a table might hold thousands of rows of data: one row for every sale, every student, every product. An aggregate function takes all those rows in a column and boils them down to a single value. It doesn't change the data; it answers a question about the whole set. The most common ones are COUNT, SUM, AVG, MIN, and MAX. Each one has a clear, everyday meaning.

COUNT tells you how many rows there are — like counting how many students are in a class list. SUM adds up all the numbers in a column — like totalling the marks of every student in an exam. AVG gives the average of those numbers. MIN and MAX find the smallest and largest values — the lowest temperature recorded or the highest sale of the day.

Note

Aggregate functions work on a column of data, not across rows. You always write them as FUNCTION_NAME(column_name). For example, AVG(price) gives the average of all prices in that column.

The real power comes when you combine them with the GROUP BY clause. Without GROUP BY, an aggregate function summarises the entire table. With GROUP BY, you split the table into groups first, then apply the function to each group separately. Think of it like this: you don't just want the average sale for the whole year — you want the average sale per month. GROUP BY month creates twelve groups, and AVG(sale_amount) runs once for each group, giving you twelve averages.

This is why aggregate functions matter in business, economics, and any data-driven decision. A manager doesn't ask "what was every single transaction?" They ask "what was the total revenue last quarter?" or "which product category had the highest average rating?" Aggregate functions turn raw, overwhelming data into actionable summaries. …

Start your 14-day free trial to unlock the full solution →

Here are the SQL queries for the CARSHOWROOM database, specifically for the INVENTORY table, as requested.

a) Add a new column Discount in the INVENTORY table.

To add a new column named Discount to the INVENTORY table, we use the ALTER TABLE statement. Since discounts are typically percentages, a DECIMAL data type is suitable for storing values like 10.00 or 12.50. DECIMAL(5,2) allows for up to 3 digits before the decimal point and 2 digits after.

ALTER TABLE INVENTORY
ADD COLUMN Discount DECIMAL(5,2);

b) Set appropriate discount values for all cars keeping in mind the following:

To set discount values based on car models, we use the UPDATE statement with a CASE expression. This allows us to apply different discount percentages based on the Model column in a single query, ensuring all rows are updated according to the specified conditions.

UPDATE INVENTORY
SET Discount = CASE
    WHEN Model = 'LXI' THEN 0.00
    WHEN Model = 'VXI' THEN 10.00
    ELSE 12.00
END;

c) Display the name of the costliest car with fuel type "Petrol".

To find the name of the costliest car with a specific fuel type, we select the CarName from the INVENTORY table, filter for FuelType = 'Petrol', order the results by Price in descending order, and then limit the output to the top 1 row. This ensures we get the car with the highest price among petrol cars.

SELECT CarName
FROM INVENTORY
WHERE FuelType = 'Petrol'
ORDER BY Price DESC
LIMIT 1;

d) Calculate the average discount and total discount available on Baleno cars. …

Start your 14-day free trial to unlock the full solution →

This solution uses the real INVENTORY table of the CARSHOWROOM database, given earlier in this chapter as Table 9.9 (Section 9.8). It demonstrates ALTER TABLE to add a column, UPDATE with CASE for conditional discount logic, and SELECT with aggregate functions and WHERE/ORDER BY/LIMIT to retrieve the required information.

The real INVENTORY table (Table 9.9, Section 9.8)

CarName is already a column of INVENTORY — the chapter never introduces a separate CARS table, so no join is needed for any part of this question.

CarId	CarName	Price	Model	YearManufacture	FuelType
D001	Dzire	582613.00	LXI	2017	Petrol
D002	Dzire	673112.00	VXI	2018	Petrol
B001	Baleno	567031.00	Sigma1.2	2019	Petrol
B002	Baleno	647858.00	Delta1.2	2018	Petrol
E001	EECO	355205.00	5 STR STD	2017	CNG
E002	EECO	654914.00	CARE	2018	CNG
S001	SWIFT	514000.00	LXI	2017	Petrol
S002	SWIFT	614000.00	VXI	2018	Petrol

a) Add a new column Discount in the INVENTORY table.

ALTER TABLE INVENTORY
ADD Discount DECIMAL(10,2);

DECIMAL(10,2) stores the discount as a rupee amount (matching Price's own type), initially NULL for every row.

b) Set appropriate discount values for all cars

No discount on LXI.
VXI gets 10%.
12% on every other model (Sigma1.2, Delta1.2, 5 STR STD, CARE).

UPDATE INVENTORY
SET Discount = CASE
    WHEN Model = 'LXI' THEN 0
    WHEN Model = 'VXI' THEN Price * 0.10
    ELSE Price * 0.12
END;

State of INVENTORY after the update:

CarId	CarName	Price	Model	Discount
D001	Dzire	582613.00	LXI	0.00
D002	Dzire	673112.00	VXI	67311.20
B001	Baleno	567031.00	Sigma1.2	68043.72
B002	Baleno	647858.00	Delta1.2	77742.96
E001	EECO	355205.00	5 STR STD	42624.60
E002	EECO	654914.00	CARE	78589.68
S001	SWIFT	514000.00	LXI	0.00
S002	SWIFT	614000.00	VXI	61400.00

c) Display the name of the costliest car with fuel type "Petrol".

SELECT CarName
FROM INVENTORY
WHERE FuelType = 'Petrol'
ORDER BY Price DESC
LIMIT 1;

Among the six Petrol-fuelled rows (D001 582613, D002 673112, B001 567031, B002 647858, S001 514000, S002 614000 — the two EECO rows are CNG and are excluded), the highest price is D002 at 673112.00.

Output

CarName
Dzire

d) Calculate the average discount and total discount available on Baleno cars.

SELECT AVG(Discount) AS AverageDiscount, SUM(Discount) AS TotalDiscount …

Start your 14-day free trial to unlock the full solution →

←Previous questionQ7 — Consider the following table named "Product", showing details of produ…→Next questionQ1 — While creating a table, which constraint does not allow insertion of d…

Q.Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

The real INVENTORY table (Table 9.9, Section 9.8)

a) Add a new column Discount in the INVENTORY table.

b) Set appropriate discount values for all cars

c) Display the name of the costliest car with fuel type "Petrol".

d) Calculate the average discount and total discount available on Baleno cars.

🎓Unlock everything free for 14 days

Unlock everything free for 14 days