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)

Question

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% discount.
(iii) A 12% discount is given on cars other than the 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 Car4.
(e) List the total number of cars having no discount.

Accepted Answer

> [!TLDR]
> Every part of this exercise runs against the real 8-row INVENTORY table already established in this chapter (§1.1) — never a hypothetical or assumed schema.

This is a **code/query task**. Starting data (Table 1.1, INVENTORY):

| CarId | CarName | Price | Model | YearManufacture | FuelType |
|---|---|---|---|---|---|
| D001 | Car1 | 582613.00 | LXI | 2017 | Petrol |
| D002 | Car1 | 673112.00 | VXI | 2018 | Petrol |
| B001 | Car2 | 567031.00 | Sigma1.2 | 2019 | Petrol |
| B002 | Car2 | 647858.00 | Delta1.2 | 2018 | Petrol |
| E001 | Car3 | 355205.00 | 5 STR STD | 2017 | CNG |
| E002 | Car3 | 654914.00 | CARE | 2018 | CNG |
| S001 | Car4 | 514000.00 | LXI | 2017 | Petrol |
| S002 | Car4 | 614000.00 | VXI | 2018 | Petrol |

**(a)** Add the column with `ALTER TABLE`:

```sql
ALTER TABLE INVENTORY ADD Discount Numeric(5,2);
```

**(b)** A `CASE` expression assigns a different discount per model in a single `UPDATE`:

```sql
UPDATE INVENTORY SET Discount =
    CASE
        WHEN Model='LXI' THEN 0
        WHEN Model='VXI' THEN 10
        ELSE 12
    END;
```

Applying this to every row: LXI cars (D001, S001) get 0; VXI cars (D002, S002) get 10; everything else — Sigma1.2, Delta1.2, 5 STR STD, CARE (B001, B002, E001, E002) — gets 12:

| CarId | CarName | Model | Price | Discount |
|---|---|---|---|---|
| D001 | Car1 | LXI | 582613.00 | 0 |
| D002 | Car1 | VXI | 673112.00 | 10 |
| B001 | Car2 | Sigma1.2 | 567031.00 | 12 |
| B002 | Car2 | Delta1.2 | 647858.00 | 12 |
| E001 | Car3 | 5 STR STD | 355205.00 | 12 |
| E002 | Car3 | CARE | 654914.00 | 12 |
| S001 | Car4 | LXI | 514000.00 | 0 |
| S002 | Car4 | VXI | 614000.00 | 10 |

**(c)** Filter to Petrol, sort by Price descending, keep only the top row:

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

Petrol cars are D001, D002, B001, B002, S001, S002 (E001 and E002 are CNG). Their prices: 582613, 673112, 567031, 647858, 514000, 614000. The highest is D002 at 673112.00, w

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

Concept understanding — SQL Aggregate Functions

CarId	Model	Discount
D001	LXI	0
D002	VXI	10
B001	Sigma1.2	12
B002	Delta1.2	12
E001	5 STR STD	12
E002	CARE	12
S001	LXI	0
S002	VXI	10

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

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

🎓Unlock everything free for 14 days

Unlock everything free for 14 days