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.
Yanam CbseNCERTSubjective· 4mImportance★★★★★
36% · 34/95 Questions
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)
| InvoiceNo | CarId | CustId | SaleDate | PaymentMode | EmpID | SalePrice |
|---|---|---|---|---|---|---|
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613248.00 |
| I00002 | S001 | C0002 | 2018-12-12 | Online | E001 | 590321.00 |
| I00003 | S002 | C0004 | 2019-01-25 | Cheque | E010 | 604000.00 |
| I00004 | D002 | C0001 | 2018-10-15 | Bank Finance | E007 | 659982.00 |
| I00005 | E001 | C0003 | 2018-12-20 | Credit Card | E002 | 369310.00 |
| I00006 | S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.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
| InvoiceNo | CarId | CustId | SaleDate | PaymentMode | EmpID | SalePrice | Commission |
|---|---|---|---|---|---|---|---|
| I00001 | D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613248.00 | 73589.64 |
| I00004 | D002 | C0001 | 2018-10-15 | Bank Finance | E007 | 659982.00 | 79198.84 |
| I00006 | S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 | 74425.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.