a) Find sum of Sale Price of the cars purchased by the customer having ID C0001 from table SALE. b) Find the m

Activities · Activity 9.14

Q. a) Find sum of Sale Price of the cars purchased by the customer having ID C0001 from table SALE.

b) Find the maximum and minimum commission from the SALE table.

Odisha ChseTextbookSubjective· 4mImportance★★★★★

29% · 28/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 →

Concept understanding — SQL Rounding Functions

SQL Rounding Functions

Imagine you're keeping a record of your monthly expenses. You spend ₹1,247.83 on groceries, ₹532.19 on transport, and ₹2,108.56 on rent. When you tell a friend how much you spent, you don't say "one thousand two hundred forty-seven point eight three rupees." You say "about ₹1,250" or "roughly ₹1,200." That instinct to simplify a number to something easier to work with — that's exactly what rounding functions do in SQL.

The Core Idea

Rounding functions take a number with many decimal places and give you back a cleaner version. They don't change the kind of number — it's still a number — but they make it shorter, neater, and often more useful for reports, summaries, or display.

Think of it like this: you have a precise measurement, but you don't always need that precision. A bank statement might show ₹1,247.83, but a budget summary might only need ₹1,248. A sales report for the whole year might round to the nearest thousand. SQL gives you the tools to decide exactly how much precision you want to keep.

The Main Rounding Functions

SQL provides several ways to round, and each one behaves a little differently. Here's what you need to know:

ROUND — This is the most intuitive one. It takes a number and rounds it to a specified number of decimal places. If you round 3.14159 to two decimal places, you get 3.14. If you round it to zero decimal places, you get 3. The rule is standard: look at the next digit; if it's 5 or above, round up; otherwise, round down. This is what most people mean when they say "round."

CEILING — This function always rounds up to the nearest whole number, no matter what. If your number is 3.001, CEILING gives you 4. If it's 3.999, you still get 4. It's like saying "I don't care how close you are — go to the next integer." This is useful when you're counting things that can't be split, like people or items. You can't have 3.2 employees; you need 4.

FLOOR — This is the opposite of CEILING. It always rounds down to the nearest whole number. 3.999 becomes 3. 3.001 becomes 3. It's like saying "cut off everything after the decimal point, no questions asked." This is handy when you're dealing with quantities where you can't exceed a limit, like "how many full boxes can I pack?"

Note

CEILING and FLOOR always return whole numbers (integers). ROUND can return numbers with decimals if you ask it to keep some. That's a key difference: ROUND gives you control over precision; CEILING and FLOOR give you control over direction.

Why Rounding Matters in the Real World

You might wonder: why not just store the exact number? Because in practice, data is messy and human beings need simplicity. …

Start your 14-day free trial to unlock the full solution →

Both parts run against the real SALE table of the CARSHOWROOM database.

The real SALE table

InvoiceNo	CustId	SalePrice	Commission
I00001	C0001	613248.00	73589.64
I00002	C0002	590321.00	70838.52
I00003	C0004	604000.00	72480.00
I00004	C0001	659982.00	79198.84
I00005	C0003	369310.00	44318.20
I00006	C0002	620214.00	74425.68

(a) Sum of Sale Price for customer C0001

SELECT SUM(SalePrice) AS TotalSalePrice
FROM SALE
WHERE CustId = 'C0001';

Customer C0001 bought cars on invoices I00001 (613248.00) and I00004 (659982.00).

Output

TotalSalePrice
1273230.00

(b) Maximum and minimum commission

Start your 14-day free trial to unlock the full solution →

Use SQL aggregate functions SUM(), MAX(), and MIN() on the real SALE table of the CARSHOWROOM database — the real customer-ID column is CustId. Commission values are as printed for the full SALE table in Section 9.9 of the textbook.

The real SALE table

InvoiceNo	CarId	CustId	SalePrice	Commission
I00001	D001	C0001	613248.00	73589.64
I00002	S001	C0002	590321.00	70838.52
I00003	S002	C0004	604000.00	72480.00
I00004	D002	C0001	659982.00	79198.84
I00005	E001	C0003	369310.00	44318.20
I00006	S002	C0002	620214.00	74425.68

(a) Sum of Sale Price for Customer C0001

SELECT SUM(SalePrice) AS TotalSalePrice
FROM SALE
WHERE CustId = 'C0001';

Customer C0001 made two purchases: I00001 (613248.00) and I00004 (659982.00).

Total = 613248.00 + 659982.00 = 1273230.00.

Output

TotalSalePrice
1273230.00

(b) Maximum and Minimum Commission

SELECT MAX(Commission) AS MaxCommission,
       MIN(Commission) AS MinCommission
FROM SALE;
``` …

Start your 14-day free trial to unlock the full solution →

←Previous question · 9.8.1Activity 9.13 — Using the table EMPLOYEE of CARSHOWROOM database, list the day of birt…→Next question · 9.8.1Q9 — Can we use arithmetic operators (+, -. *, or /) on date functions?

Q.a) Find sum of Sale Price of the cars purchased by the customer having ID C0001 from table SALE.

The real SALE table

(a) Sum of Sale Price for Customer C0001

(b) Maximum and Minimum Commission

🎓Unlock everything free for 14 days

Unlock everything free for 14 days