Skip to content
Worked Examples · Example 9.19

Q.a) Let us now add a new column Commission to the SALE table. The column Commission should have a total length of 7 in which 2 decimal places to be there.

b) Let us now calculate commission for sales agents as 12% of the SalePrice, insert the values to the newly added column Commission and then display records of the table SALE where commission > 73000.
c) Display InvoiceNo, SalePrice and Commission such that commission value is rounded off to 0.
Sikkim CbseNCERTSubjective· 4mImportance★★★★★
36% · 34/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 →

This uses the real SALE table (Table 9.11) of the CARSHOWROOM database: ALTER TABLE to add Commission, UPDATE to fill it as 12% of SalePrice, a WHERE filter, and ROUND() to display it to 0 decimal places.

The real SALE table (Table 9.11)

InvoiceNoCarIdCustIdSaleDatePaymentModeEmpIDSalePrice
I00001D001C00012019-01-24Credit CardE004613248.00
I00002S001C00022018-12-12OnlineE001590321.00
I00003S002C00042019-01-25ChequeE010604000.00
I00004D002C00012018-10-15Bank FinanceE007659982.00
I00005E001C00032018-12-20Credit CardE002369310.00
I00006S002C00022019-01-30Bank FinanceE007620214.00

(a) Add the Commission column (total length 7, 2 decimal places)

ALTER TABLE SALE ADD(Commission Numeric(7,2));

Numeric(7,2) means 7 digits in total, 2 of them after the decimal point — e.g. 73589.64 fits (5 digits + 2 decimals = 7).


(b) Fill Commission as 12% of SalePrice, then show rows where Commission > 73000

UPDATE SALE SET Commission=12/100*SalePrice;

SELECT * FROM SALE WHERE Commission > 73000;

Output

InvoiceNoCarIdCustIdSaleDatePaymentModeEmpIDSalePriceCommission
I00001D001C00012019-01-24Credit CardE004613248.0073589.64
I00004D002C00012018-10-15Bank FinanceE007659982.0079198.84
I00006S002C00022019-01-30Bank FinanceE007620214.0074425.68

3 rows in set (0.02 sec)

Only these three invoices clear the 73000 mark; I00002 (70838.52), I00003 (72480.00) and I00005 (44318.20) do not.

--- …

Unlock everything free for 14 days

  • Full step-by-step solutions
  • Concept-first explanations
  • Methods, shortcuts & mistakes
  • PYQ mapping + timed mock tests

Full access for 14 days. No credit card required.