Q.In order to increase sales, a car dealer decides to offer customers the option to pay the total amount in 10 easy EMIs (equal monthly installments), where the EMIs must be in multiples of 10,000. Using the INVENTORY table of the CARSHOWROOM database, list the CarID and Price along with the following:

This is a code / query task — four SQL steps on the INVENTORY table, each building on the previous one.

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

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

(b) Add FinalPrice = Price + GST

ALTER TABLE INVENTORY ADD FinalPrice Numeric(10,1);
UPDATE INVENTORY SET FinalPrice = Price + ROUND(Price*12/100, 1);

Displaying the table now shows the newly filled column:

SELECT * FROM INVENTORY;

FinalPrice is the Price plus the (already-rounded) GST amount from part (a) — not a second 12% on top of the GST.

(c) and (d) — the monthly instalment and the remaining amount

The book answers (c) and (d) together with one query, since both come from splitting FinalPrice by 10,000:

SELECT CarId, FinalPrice,
       ROUND((FinalPrice - MOD(FinalPrice,10000))/10, 0) "EMI",
       MOD(FinalPrice, 10000) "Remaining Amount"
FROM INVENTORY;

This is a code/query task that chains four numeric functions together — ROUND() twice and MOD() twice — to turn a car's Price into an EMI plan.

(a) GST. GST is defined as 12% of Price, rounded to one decimal place:

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

12/100*Price computes the 12% amount; ROUND(..., 1) keeps one decimal place, exactly as asked. The quoted "GST" after the expression names the output column.

(b) FinalPrice. The question says FinalPrice is "the sum of Price and 12% of the GST" — read together with the book's own query, this means Price plus the GST amount computed in (a) (GST itself already is 12% of Price):

ALTER TABLE INVENTORY ADD FinalPrice Numeric(10,1);
UPDATE INVENTORY SET FinalPrice = Price + ROUND(Price*12/100, 1);

The ALTER TABLE creates the column; the UPDATE fills every row with Price + its rounded GST.

Displaying the table now shows the newly filled column:

SELECT * FROM INVENTORY;

(c) and (d) — EMI and the remaining amount. With FinalPrice known, splitting it into 10 EMIs that must land on a multiple of 1,000 is exactly what MOD() is for:

SELECT CarId, FinalPrice,
       ROUND((FinalPrice - MOD(FinalPrice,10000))/10, 0) "EMI",
       MOD(FinalPrice, 10000) "Remaining Amount"
FROM INVENTORY;

• MOD(FinalPrice, 10000) — the part of FinalPrice that is NOT a clean multiple of 10,000. That fragment is what the dealer collects immediately, as the "Remaining Amount".
• FinalPrice - MOD(FinalPrice, 10000) — what's left is now an exact multiple of 10,000, so dividing it by 10 gives an EMI that is itself a clean multiple of 1,000, as the question requires. …