In order to increase sales, suppose the car dealer decides to offer his customers to pay the total amount in 10 easy EMIs (equal monthly instalments). Assume that EMIs are required to be in multiples of 10000. For that, the dealer wants to list the CarID and Price along with the

Question

In order to increase sales, suppose the car dealer decides to offer his customers to pay the total amount in 10 easy EMIs (equal monthly instalments). Assume that EMIs are required to be in multiples of 10000. For that, the dealer wants to list the CarID and Price along with the following data from the Inventory table:
a) Calculate GST as 12 per cent of Price and display the result after rounding it off to one decimal place.
b) Add a new column FinalPrice to the table inventory which will have the value as sum of Price and 12 per cent of the GST.
c) Calculate and display the amount to be paid each month (in multiples of 1000) which is to be calculated after dividing the FinalPrice of the car into 10 instalments.
d) After dividing the amount into EMIs, find out the remaining amount to be paid immediately, by performing modular division.

Accepted Answer

> [!TLDR]
> This walks through the real CARSHOWROOM `INVENTORY` table (Table 9.9) using SQL's numeric functions: `ROUND()` for the GST, `ALTER TABLE` + `UPDATE` to add and fill `FinalPrice`, and a combined `ROUND()`/`MOD()` expression to get the EMI (in multiples of 1000) and the balance left to pay right away.

## The real INVENTORY table (Table 9.9)

| 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) GST = 12% of Price, rounded to one decimal place

> [!FORMULA]
> `GST = ROUND(12/100 × Price, 1)`

```sql
SELECT ROUND(12/100*Price,1) "GST" FROM INVENTORY;
```

**Output** (rows follow CarId order D001, D002, B001, B002, E001, E002, S001, S002)

```
+------------+
| GST        |
+------------+
| 69913.6    |
| 80773.4    |
| 68043.7    |
| 77743.0    |
| 42624.6    |
| 78589.7    |
| 61680.0    |
| 73680.0    |
+------------+
8 rows in set (0.00 sec)
```

---

## (b) Add and fill the FinalPrice column

`ALTER TABLE` (a DDL command) creates the empty column; `UPDATE` (a DML command) fills it as Price + the rounded 12% GST.

```sql
ALTER TABLE INVENTORY ADD(FinalPrice Numeric(10,1));

UPDATE INVENTORY SET FinalPrice=Price+Round(Price*12/100,1);

SELECT * FROM INVENTORY;
```

**Output**

| CarId | Price | Model | FinalPrice |
|---|---|---|---|
| D001 | 582613.00 | LXI | 652526.6 |
| D002 | 673112.00 | VXI | 753885.4 |
| B001 | 567031.00 | Sigma1.2 | 635074.7 |
| B002 | 647858.00 | Delta1.2 | 725601.0 |
| E001 | 355205.00 | 5 STR STD | 397829.6 |
| E002 | 654914.00 | CARE | 733503.7 |
| S001 | 514000.00 | LX

Concept understanding — Rounding And Arithmetic

Rounding and Arithmetic: A Concept-First Guide

The Precise Meaning

Why It Matters in Commerce and Humanities

The Core Principle: What Gets Preserved

The real INVENTORY table (Table 9.9)

(a) GST = 12% of Price, rounded to one decimal place

(b) Add and fill the FinalPrice column

(c) and (d) EMI (multiples of 1000) and the balance payable now

CarId	Price	FinalPrice
D001	582613.00	652526.6
D002	673112.00	753885.4
S002	614000.00	687680.0

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

The real INVENTORY table (Table 9.9)

(a) GST = 12% of Price, rounded to one decimal place

(b) Add and fill the FinalPrice column

(c) and (d) EMI (multiples of 1000) and the balance payable now

🎓Unlock everything free for 14 days

Unlock everything free for 14 days