Informatics Practices

SQL GROUP BY, HAVING & ORDER BY MCQs | 25 Practice Questions with Answers | CBSE Class 12 Informatics Practices (IP)

Class 12 · Informatics Practices

SQL GROUP BY, HAVING & ORDER BY MCQs (25 Questions)

Practice these multiple-choice questions to strengthen your understanding of GROUP BY, HAVING, and ORDER BY. Click Show Answer to reveal the correct answer and explanation.

Q1. Which SQL clause is used to group rows having the same values?

A. ORDER BY
B. WHERE
C. GROUP BY
D. HAVING

Show Answer

Answer: C. GROUP BY

Explanation: The GROUP BY clause groups rows that have the same values in specified columns.

Q2. Which SQL clause is mainly used with aggregate functions to filter grouped records?

A. WHERE
B. GROUP BY
C. HAVING
D. ORDER BY

Show Answer

Answer: C. HAVING

Explanation: The HAVING clause filters groups after aggregation.

Q3. Which SQL clause is used to sort query results?

A. GROUP BY
B. ORDER BY
C. HAVING
D. WHERE

Show Answer

Answer: B. ORDER BY

Explanation: ORDER BY arranges the result set in ascending or descending order.

Q4. What is the default sorting order of the ORDER BY clause?

A. DESC
B. ASC
C. RANDOM
D. NONE

Show Answer

Answer: B. ASC

Explanation: If no sorting order is specified, SQL sorts the data in ascending order by default.

Q5. Which keyword is used to sort data in descending order?

A. ASC
B. DOWN
C. DESC
D. DECREASE

Show Answer

Answer: C. DESC

Explanation: The DESC keyword sorts records from highest to lowest or Z to A.

Q6. Which query groups employees department-wise?

A. SELECT Department FROM Employee GROUP BY Department;
B. SELECT Department FROM Employee ORDER BY Department;
C. SELECT Department FROM Employee HAVING Department;
D. SELECT Department FROM Employee WHERE Department;

Show Answer

Answer: A.

Explanation: GROUP BY Department groups all employees belonging to the same department.

Q7. Which clause is executed after GROUP BY?

A. WHERE
B. HAVING
C. INSERT
D. UPDATE

Show Answer

Answer: B. HAVING

Explanation: HAVING filters the grouped data produced by GROUP BY.

Q8. Which clause should be used to filter rows before grouping?

A. HAVING
B. WHERE
C. ORDER BY
D. DISTINCT

Show Answer

Answer: B. WHERE

Explanation: WHERE filters individual rows before they are grouped.

Q9. Which SQL clause is commonly used with aggregate functions like SUM() and COUNT()?

A. GROUP BY
B. HAVING
C. Both A and B
D. ORDER BY only

Show Answer

Answer: C. Both A and B

Explanation: Aggregate functions are often used with both GROUP BY and HAVING.

Q10. Which query displays employee names in alphabetical order?

A. SELECT Name FROM Employee ORDER BY Name;
B. SELECT Name FROM Employee GROUP BY Name;
C. SELECT Name FROM Employee HAVING Name;
D. SELECT Name FROM Employee WHERE Name;

Show Answer

Answer: A.

Explanation: ORDER BY Name sorts employee names alphabetically.

Q11. Which query displays student marks from highest to lowest?

A. ORDER BY Marks ASC
B. ORDER BY Marks DESC
C. GROUP BY Marks
D. HAVING Marks

Show Answer

Answer: B.

Explanation: DESC sorts values in descending order.

Q12. Which SQL clause cannot be used without an aggregate function in CBSE-level queries?

A. ORDER BY
B. GROUP BY
C. HAVING
D. DISTINCT

Show Answer

Answer: C. HAVING

Explanation: At the CBSE level, HAVING is primarily used with aggregate functions to filter grouped results.

Q13. Which statement is TRUE about GROUP BY?

A. It groups similar values together.
B. It is often used with aggregate functions.
C. It creates summary reports.
D. All of these.

Show Answer

Answer: D.

Explanation: GROUP BY is used to organize rows into groups for aggregate calculations.

Q14. Which statement is TRUE about ORDER BY?

A. It sorts query results.
B. It supports both ASC and DESC order.
C. ASC is the default order.
D. All of these.

Show Answer

Answer: D.

Explanation: ORDER BY sorts records in ascending or descending order, with ASC as the default.

Q15. Which statement correctly describes the purpose of HAVING?

A. It filters grouped data.
B. It is used after GROUP BY.
C. It is commonly used with aggregate functions.
D. All of these.

Show Answer

Answer: D.

Explanation: The HAVING clause filters groups created using GROUP BY.

Q16. A school wants to calculate the average marks class-wise. Which SQL clause should be used to group students according to their class?

A. WHERE
B. GROUP BY
C. ORDER BY
D. HAVING

Show Answer

Answer: B. GROUP BY

Explanation: GROUP BY groups students by class so that aggregate functions such as AVG() can be applied to each class.

Q17. Which query displays departments having more than 10 employees?

A. SELECT Department, COUNT(*) FROM Employee GROUP BY Department HAVING COUNT(*) > 10;
B. SELECT Department FROM Employee WHERE COUNT(*) > 10;
C. SELECT Department ORDER BY COUNT(*) > 10;
D. SELECT Department GROUP COUNT(*);

Show Answer

Answer: A.

Explanation: The HAVING clause filters groups based on the result of an aggregate function.

Q18. Which clause is evaluated before GROUP BY?

A. HAVING
B. ORDER BY
C. WHERE
D. DISTINCT

Show Answer

Answer: C. WHERE

Explanation: WHERE filters individual rows before grouping takes place.

Q19. Which clause is evaluated after GROUP BY?

A. HAVING
B. WHERE
C. INSERT
D. UPDATE

Show Answer

Answer: A. HAVING

Explanation: After rows are grouped, the HAVING clause filters the resulting groups.

Q20. A company wants to display employee names in descending alphabetical order. Which query is correct?

A. SELECT Name FROM Employee ORDER BY Name DESC;
B. SELECT Name FROM Employee GROUP BY Name;
C. SELECT Name FROM Employee HAVING Name;
D. SELECT Name FROM Employee WHERE DESC;

Show Answer

Answer: A.

Explanation: ORDER BY Name DESC sorts names from Z to A.

Q21. Which query displays cities in ascending alphabetical order?

A. SELECT City FROM Student ORDER BY City;
B. SELECT City FROM Student GROUP BY City DESC;
C. SELECT City FROM Student HAVING City;
D. SELECT City FROM Student WHERE ASC;

Show Answer

Answer: A.

Explanation: The default sorting order of ORDER BY is ascending (ASC).

Q22. Which statement correctly differentiates WHERE and HAVING?

A. WHERE filters individual rows before grouping.

B. HAVING filters grouped records after aggregation.

C. Both A and B.

D. Neither A nor B.

Show Answer

Answer: C. Both A and B.

Explanation: WHERE works on individual rows before grouping, while HAVING works on grouped data after aggregation.

Q23. Which of the following statements is TRUE?

A. GROUP BY groups rows having the same values.

B. HAVING filters grouped data.

C. ORDER BY sorts the final result.

D. All of these.

Show Answer

Answer: D. All of these.

Explanation: These clauses perform different but complementary roles in SQL queries.

Q24. Which SQL query is correct for displaying department-wise average salaries in ascending order of department names?

A. SELECT Department, AVG(Salary) FROM Employee GROUP BY Department ORDER BY Department;
B. SELECT Department FROM Employee ORDER BY AVG(Salary);
C. SELECT AVG(Salary) GROUP Department;
D. SELECT Department HAVING AVG(Salary);

Show Answer

Answer: A.

Explanation: The query groups records by department, calculates the average salary, and sorts the results alphabetically by department.

Q25. Which statement best summarizes GROUP BY, HAVING, and ORDER BY?

A. GROUP BY creates groups of similar records.

B. HAVING filters grouped records.

C. ORDER BY sorts the final result.

D. All of these.

Show Answer

Answer: D. All of these.

Explanation: These three clauses are frequently used together to group, filter, and sort data in SQL queries.

Answer Key

Q.No. Answer Q.No. Answer Q.No. Answer
1C11B21A
2C12C22C
3B13D23D
4B14D24A
5C15D25D
6A16B
7B17A
8B18C
9C19A
10A20A

Practice Tips

  • Remember the purpose of each clause:
    • GROUP BY → Groups rows with the same values.
    • HAVING → Filters grouped data after aggregation.
    • ORDER BY → Sorts the final result.
  • Understand the difference between WHERE and HAVING:
    • WHERE filters individual rows before grouping.
    • HAVING filters groups after applying aggregate functions.
  • Sorting in SQL:
    • ASC → Ascending order (default).
    • DESC → Descending order.
  • Common combinations:
    • GROUP BY + COUNT()
    • GROUP BY + SUM()
    • GROUP BY + AVG()
    • HAVING + Aggregate Functions
    • ORDER BY after GROUP BY
  • Execution sequence (simplified for CBSE):
    1. WHERE
    2. GROUP BY
    3. HAVING
    4. ORDER BY
  • CBSE Tip: Competency-based questions frequently ask you to identify the correct clause for grouping, filtering grouped results, or sorting output. Focus on understanding the role of each clause and the order in which they are applied.