Q.Find the output of the following SQL Queries :
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?"
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.
A sales report with numbers like ₹1,247.83, ₹532.19, and ₹2,108.56 is hard to scan. Round those to the nearest ten — ₹1,250, ₹530, ₹2,110 — and suddenly you can compare them at a glance. A manager doesn't need to know the paise; they need to know the trend.
Rounding also prevents false precision. If you calculate the average of 10 students' test scores and get 83.333333..., reporting that as 83.33 is honest and readable. Reporting all those decimal places suggests a level of accuracy that doesn't really exist.
A Common Trap
Here's something that trips up beginners: rounding a number and then using it in further calculations can introduce small errors. If you round ₹1,247.83 to ₹1,248 and then multiply by 12 months, you get ₹14,976. But the exact total would be ₹14,973.96. That difference of ₹2.04 might not matter for a rough estimate, but it could matter in accounting.
Always round at the end of a calculation, not in the middle. Rounding early compounds errors. If you need precision, keep the full number in your database and only round when displaying or reporting.
Putting It Together
Rounding functions are about choosing the right level of detail for your audience. A data analyst might need six decimal places. A CEO might need whole numbers. A warehouse manager might need CEILING to ensure they order enough boxes. A budget planner might need FLOOR to stay within limits.
SQL gives you all three tools. The skill is knowing which one to use and when.
Part (a): ROUND(7658.345,2) = 7658.35 and MOD(ROUND(13.9,0),3) = 2.
Part (b): POWER() is a single-row numeric (math) function, while SUM() is an aggregate that totals many rows.
SQL numeric functions transform values right inside the query. ROUND(number, d) returns the number rounded to d decimal places, and MOD(a, b) returns the remainder after dividing a by b. Nested calls are evaluated from the inside out.
(i) SELECT ROUND(7658.345, 2); — keep two decimals. The digit just after the second decimal is 5, so standard rounding pushes the second decimal from 4 to 5.
SELECT ROUND(7658.345, 2);
7658.35
(ii) SELECT MOD(ROUND(13.9, 0), 3); — first ROUND(13.9, 0) = 14 (nearest whole number, since .9 > .5). Then MOD(14, 3) = 2.
SELECT MOD(ROUND(13.9, 0), 3);
2
(i) 7658.35 (ii) 2
Concept understanding — SQL Aggregate Functions
Imagine you have a giant box of receipts from a month of sales at a small shop. You don't want to look at every single receipt one by one — you want a single, useful number that summarises the whole pile. How much total money came in? What was the average sale? How many customers bought something? That instinct — to collapse a pile of raw details into one meaningful number — is exactly what an SQL aggregate function does.
In a database, a table might hold thousands of rows of data: one row for every sale, every student, every product. An aggregate function takes all those rows in a column and boils them down to a single value. It doesn't change the data; it answers a question about the whole set. The most common ones are COUNT, SUM, AVG, MIN, and MAX. Each one has a clear, everyday meaning.
COUNT tells you how many rows there are — like counting how many students are in a class list. SUM adds up all the numbers in a column — like totalling the marks of every student in an exam. AVG gives the average of those numbers. MIN and MAX find the smallest and largest values — the lowest temperature recorded or the highest sale of the day.
Aggregate functions work on a column of data, not across rows. You always write them as FUNCTION_NAME(column_name). For example, AVG(price) gives the average of all prices in that column.
The real power comes when you combine them with the GROUP BY clause. Without GROUP BY, an aggregate function summarises the entire table. With GROUP BY, you split the table into groups first, then apply the function to each group separately. Think of it like this: you don't just want the average sale for the whole year — you want the average sale per month. GROUP BY month creates twelve groups, and AVG(sale_amount) runs once for each group, giving you twelve averages.
This is why aggregate functions matter in business, economics, and any data-driven decision. A manager doesn't ask "what was every single transaction?" They ask "what was the total revenue last quarter?" or "which product category had the highest average rating?" Aggregate functions turn raw, overwhelming data into actionable summaries.
When you use an aggregate function in a SELECT statement, any column that is not inside an aggregate function must appear in the GROUP BY clause. Otherwise, SQL won't know which group's value to show — it's a rule you must follow to avoid errors.
A few practical points to keep in mind. COUNT(*) counts every row, including those with missing values. COUNT(column_name) counts only rows where that column has a value. SUM and AVG only work on numeric columns — you cannot sum names or average dates. And MIN and MAX work on numbers, text, and dates alike, because they just compare values to find the extremes.
COUNT(*)— total number of rowsSUM(column)— total of all numeric values in that columnAVG(column)— arithmetic mean of those valuesMIN(column)— smallest value in the columnMAX(column)— largest value in the column
Think of aggregate functions as your data's summarising toolkit. They don't change the original table; they answer the big-picture questions that raw data alone cannot. Every time you see a dashboard showing "total sales," "average customer rating," or "highest score," you are looking at the result of an aggregate function at work.
Part (a): ROUND(7658.345,2) = 7658.35 and MOD(ROUND(13.9,0),3) = 2.
Part (b): POWER() is a single-row numeric (math) function, while SUM() is an aggregate that totals many rows.
POWER() and SUM() belong to two different families of SQL functions:
-
Kind of function.
POWER(base, exponent)is a numeric/mathematical function that operates on a single value and returnsbaseraised toexponent, e.g.POWER(2,3)= 8.SUM(column)is an aggregate function that operates over a set of rows. -
Scope of operation.
POWER()produces one result per row and needs noGROUP BY.SUM()collapses many rows into a single total (or one total per group when used withGROUP BY) and ignores NULL values.
Aggregate functions (SUM, AVG, COUNT, MAX, MIN) work vertically down a column across rows; scalar functions (POWER, ROUND, MOD) work horizontally on a value within one row.
POWER() is a single-row numeric function that returns base^exponent (POWER(2,3)=8); SUM() is an aggregate function that adds up the values of a column across many rows and returns one total.
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.