worksheet 24091202 GROUP BY, HAVING, ORDER BY, and equi-joins

MCQs :

1. Which SQL function is used to find the total sum of values in a column?
   • (A) SUM()
   • (B) AVG()
   • (C) MAX()
   • (D) COUNT()
2. Which function is used to return the average of a numeric column?
   • (A) MAX()
   • (B) MIN()
   • (C) AVG()
   • (D) COUNT()
3. What does the COUNT(*) function do in SQL?
   • (A) Counts all rows in a table
   • (B) Counts only non-null rows
   • (C) Counts distinct values
   • (D) Counts numeric values only
4. Which SQL keyword is used to group rows that have the same values in a specified column?
   • (A) GROUP BY
   • (B) ORDER BY
   • (C) HAVING
   • (D) WHERE
5. Which SQL clause is used to filter groups created by GROUP BY?
   • (A) HAVING
   • (B) WHERE
   • (C) ORDER BY
   • (D) GROUP BY
6. In an SQL query, which keyword sorts the result set in ascending or descending order?
   • (A) GROUP BY
   • (B) ORDER BY
   • (C) HAVING
   • (D) COUNT
7. Which of the following aggregate functions is used to find the largest value in a column?
   • (A) MIN()
   • (B) SUM()
   • (C) MAX()
   • (D) COUNT()
8. Which join type combines rows from two tables based on a matching condition in both tables?
   • (A) INNER JOIN
   • (B) CROSS JOIN
   • (C) LEFT JOIN
   • (D) FULL OUTER JOIN

Assertion-Reasoning :

9. Assertion (A): The AVG() function can only be applied to numeric columns in SQL.
   Reason (R):AVG() computes the average of non-numeric values by converting them to integers.
   • (A) Both A and R are true, and R is the correct explanation of A.
   • (B) Both A and R are true, but R is not the correct explanation of A.
   • (C) A is true, but R is false.
   • (D) A is false, but R is true.
10. Assertion (A): The GROUP BY clause is used to arrange identical data into groups.
    Reason (R):GROUP BY works with aggregate functions like SUM(), AVG(), and COUNT() to return a single value per group.
    • (A) Both A and R are true, and R is the correct explanation of A.
    • (B) Both A and R are true, but R is not the correct explanation of A.
    • (C) A is true, but R is false.
    • (D) A is false, but R is true.

---

Table Structures for SQL Queries

1. employees Table

emp_id	emp_name	salary	department_id
101	John	50000	1
102	Jane	60000	2
103	Mike	55000	1
104	Sarah	62000	2
105	Alex	48000	3

2. departments Table

department_id	department_name
1	HR
2	Finance
3	IT

3. products Table

product_id	product_name	price	category_id
1	Laptop	800	1
2	Mouse	20	2
3	Keyboard	50	2
4	Monitor	150	1
5	Printer	120	3

4. sales Table

sale_id	product_id	sale_amount
1	1	800
2	2	40
3	3	100
4	4	150
5	5	120

5. students Table

student_id	student_name	class
1	Alice	10
2	Bob	10
3	Charlie	11
4	David	12
5	Eve	11

6. marks Table

student_id	subject	marks
1	Math	95
2	Science	85
3	Math	78
4	History	88
5	Science	90

---

Programming Questions:

1. Write an SQL query to find the maximum and minimum salary from the employees table.
2. Write an SQL query to find the average price of products from the products table.
3. Write an SQL query to find the total sales amount using the SUM() function from the sales table.
4. Write an SQL query to count the number of students in a classroom table using COUNT(*).
5. Write an SQL query to group employees by their department and find the total salary for each department using GROUP BY and SUM().
6. Write an SQL query to find departments that have more than 5 employees using the GROUP BY and HAVING clauses.
7. Write an SQL query to display the name and salary of employees, ordered by salary in descending order.
8. Write an SQL query to find the count of products in each category from the products table, using GROUP BY and COUNT().
9. Write an SQL query to join the employees table and departments table using an equi-join based on department ID.
10. Write an SQL query to display the name of students who scored the highest marks in each subject using GROUP BY, MAX(), and a join between the students and marks tables.