AmbifulWorksheets
Practice that never repeats
CBSE · Class 12 · Computer Science

SQL: Functions, GROUP BY and Joins

Standard difficulty · 20 questions · 40 marks · Time: 45 minutes

Name: Date: Score: ____ / 40

Show your working for Sections B and C. Answers are on the last page — try every question first!

Section A — Choose the correct answer8 × 1 = 8 marks
1.
Which of these is NOT an aggregate function?
  1. AVG()
  2. COUNT()
  3. SUM()
  4. LENGTH()
2.
Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Ananya | IT | 50000 | 6500
E2 | Priya | IT | 73000 | NULL
E3 | Rohan | Accounts | 25000 | NULL
E4 | Zoya | Accounts | 86000 | 2500
E5 | Meera | Accounts | 31000 | 10000
E6 | Dev | Sales | 49000 | 3000
E7 | Isha | Sales | 81000 | 7500
E8 | Vikram | Sales | 29000 | NULL

SELECT MIN(Salary) FROM EMPLOYEE WHERE Dept = 'IT';
What does this query return?
  1. 8
  2. 73000
  3. 50001
  4. 50000
3.
Table: ITEM
ICode | IName | Category | Price | Qty
I01 | Crayons | Writing | 60 | 75
I02 | Pen | Writing | 220 | 145
I03 | Eraser | Desk | 280 | 20
I04 | Folder | Paper | 65 | 70
I05 | Notebook | Paper | 130 | 55
I06 | Ruler | Desk | 95 | 35
I07 | Marker | Writing | 145 | 65
I08 | Pencil | Writing | 180 | 35

SELECT AVG(Price) FROM ITEM;
What does this query return?
  1. 146.8750
  2. 167.8571
  3. 1175
  4. 148.1250
4.
Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Rohan | HR | 83000 | NULL
E2 | Ananya | Accounts | 67000 | 3500
E3 | Isha | Sales | 51000 | 10000
E4 | Priya | HR | 59000 | NULL
E5 | Rahul | HR | 75000 | 9500
E6 | Zoya | HR | 78000 | 10000
E7 | Vikram | Sales | 79000 | 9500

SELECT COUNT(*) FROM EMPLOYEE;
What does this return?
  1. 3
  2. 2
  3. 5
  4. 7
5.
Table: ITEM
ICode | IName | Category | Price | Qty
I01 | Pencil | Writing | 210 | 140
I02 | Crayons | Writing | 40 | 20
I03 | Pen | Writing | 170 | 55
I04 | Stapler | Desk | 110 | 60
I05 | Eraser | Desk | 95 | 35
I06 | Folder | Paper | 195 | 50

SELECT Category, MAX(Price) FROM ITEM GROUP BY Category ORDER BY Category;
How many rows does this query return?
  1. 4
  2. 6
  3. 2
  4. 3
6.
Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Ananya | HR | 40000 | 5500
E2 | Arjun | Sales | 29000 | 3000
E3 | Kabir | IT | 33000 | 7000
E4 | Rahul | Sales | 58000 | 4500
E5 | Asha | Sales | 47000 | NULL
E6 | Isha | Sales | 63000 | 9500

SELECT Dept, AVG(Salary) FROM EMPLOYEE GROUP BY Dept HAVING COUNT(*) > 2 ORDER BY Dept;
How many rows does this query return?
  1. 0
  2. 2
  3. 1
  4. 3
7.
Table: EMP
EmpNo | Name | DeptID | Salary
101 | Ananya | D2 | 65000
102 | Asha | D2 | 32000
103 | Rahul | D4 | 26000
104 | Priya | D3 | 56000
105 | Arjun | D5 | 85000

Table: DEPT
DeptID | DName | City
D1 | IT | Pune
D2 | Accounts | Shimla
D3 | HR | Indore
D4 | Sales | Delhi

SELECT Name, City FROM EMP, DEPT WHERE EMP.DeptID = DEPT.DeptID ORDER BY Name;
How many rows does this query return?
  1. 3
  2. 4
  3. 20
  4. 5
8.
Table: EMP
EmpNo | Name | DeptID | Salary
101 | Isha | D3 | 70000
102 | Zoya | D2 | 77000
103 | Meera | D1 | 29000
104 | Vikram | D1 | 47000
105 | Asha | D5 | 35000

Table: DEPT
DeptID | DName | City
D1 | HR | Shimla
D2 | Accounts | Indore
D3 | IT | Jaipur
D4 | Sales | Pune

SELECT * FROM EMP NATURAL JOIN DEPT; How many columns does it return?
  1. 6
  2. 7
  3. 12
  4. 9
Section B — Short answer8 × 2 = 16 marks
9.
Name the five aggregate functions of SQL.
10.
Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Zoya | HR | 74000 | 4000
E2 | Rahul | Sales | 67000 | 5500
E3 | Ananya | HR | 60000 | NULL
E4 | Dev | Sales | 41000 | 4000
E5 | Meera | HR | 44000 | 8500
E6 | Priya | IT | 27000 | 9500

SELECT SUM(Bonus) FROM EMPLOYEE WHERE Dept = 'HR';
What does this query return?
11.
Table: STUDENT
RollNo | Name | Sec | City | Marks
101 | Isha | B | Shimla | 48
102 | Rahul | B | Shimla | 46
103 | Asha | A | Pune | 60
104 | Arjun | B | Pune | 71
105 | Rohan | A | Shimla | 97
106 | Meera | C | Indore | 93
107 | Zoya | B | Shimla | 86
108 | Vikram | B | Shimla | 44

SELECT Sec, MAX(Marks) FROM STUDENT GROUP BY Sec ORDER BY Sec;
Write the output.
12.
Table: ITEM
ICode | IName | Category | Price | Qty
I01 | Pen | Writing | 255 | 105
I02 | Notebook | Paper | 75 | 110
I03 | Pencil | Writing | 260 | 65
I04 | Marker | Writing | 155 | 20
I05 | Stapler | Desk | 170 | 95
I06 | Diary | Paper | 95 | 120

SELECT Category, SUM(Price) FROM ITEM GROUP BY Category HAVING SUM(Price) > 170 ORDER BY Category;
Write the output.
13.
Table: EMP
EmpNo | Name | DeptID | Salary
101 | Rahul | D3 | 38000
102 | Priya | D3 | 28000
103 | Rohan | D1 | 59000
104 | Zoya | D2 | 78000
105 | Meera | D4 | 30000

Table: DEPT
DeptID | DName | City
D1 | HR | Pune
D2 | Sales | Indore
D3 | Accounts | Shimla

SELECT Name, City FROM EMP, DEPT WHERE EMP.DeptID = DEPT.DeptID ORDER BY Name;
Write the output.
14.
Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Arjun | HR | 42000 | NULL
E2 | Dev | HR | 27000 | 7000
E3 | Vikram | HR | 55000 | NULL
E4 | Priya | Sales | 76000 | 2000
E5 | Isha | Accounts | 48000 | 9000
E6 | Meera | Accounts | 41000 | 5500
E7 | Zoya | HR | 78000 | 9000
What do (a) SELECT COUNT(*) FROM EMPLOYEE; and (b) SELECT COUNT(Bonus) FROM EMPLOYEE; return?
15.
Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Dev | HR | 38000 | NULL
E2 | Arjun | Accounts | 52000 | 5000
E3 | Vikram | HR | 26000 | 9500
E4 | Isha | HR | 54000 | 6000
E5 | Ananya | IT | 40000 | 7000
E6 | Meera | HR | 89000 | 9500
E7 | Rohan | Accounts | 88000 | NULL
E8 | Asha | HR | 57000 | 3000

SELECT Name FROM EMPLOYEE WHERE Bonus IS NULL ORDER BY Name;
Write the names it returns, in order, separated by commas.
16.
Table: EMP
EmpNo | Name | DeptID | Salary
101 | Meera | D1 | 83000
102 | Rahul | D2 | 46000
103 | Priya | D3 | 36000
104 | Ananya | D3 | 69000
105 | Arjun | D2 | 28000
106 | Rohan | D4 | 73000

Table: DEPT
DeptID | DName | City
D1 | Accounts | Shimla
D2 | HR | Delhi
D3 | Sales | Jaipur
For SELECT * FROM EMP, DEPT; find (a) the number of rows (b) the number of columns.
Section C — Word problems4 × 4 = 16 marks
17.
Explain the five aggregate functions with an example of each on ITEM(ICode, IName, Category, Price, Qty).
18.
Write the output of each query.
(a)
Table: ITEM
ICode | IName | Category | Price | Qty
I01 | Ruler | Desk | 185 | 130
I02 | Glue | Desk | 200 | 20
I03 | Stapler | Desk | 160 | 65
I04 | Notebook | Paper | 35 | 120
I05 | Marker | Writing | 75 | 115
I06 | Sharpener | Desk | 245 | 105
I07 | Diary | Paper | 70 | 120
I08 | Folder | Paper | 45 | 35

SELECT Category, COUNT(*) FROM ITEM GROUP BY Category HAVING COUNT(*) > 2 ORDER BY Category;
(b)
Table: STUDENT
RollNo | Name | Sec | City | Marks
1 | Rahul | B | Jaipur | 70
2 | Rohan | C | Pune | 55
3 | Dev | B | Jaipur | 60
4 | Ananya | B | Jaipur | 85
5 | Isha | C | Jaipur | 69
6 | Arjun | B | Jaipur | 84

SELECT COUNT(DISTINCT Sec) FROM STUDENT WHERE Sec IN ('B', 'C');
19.
Table: EMP
EmpNo | Name | DeptID | Salary
101 | Arjun | D1 | 72000
102 | Dev | D1 | 52000
103 | Ananya | D4 | 34000
104 | Meera | D4 | 46000
105 | Rohan | D5 | 83000

Table: DEPT
DeptID | DName | City
D1 | Accounts | Jaipur
D2 | IT | Kochi
D3 | Sales | Shimla
D4 | HR | Indore

SELECT Name, City FROM EMP, DEPT WHERE EMP.DeptID = DEPT.DeptID ORDER BY Name;
(a) Write the output. (b) How many rows would SELECT * FROM EMP NATURAL JOIN DEPT; return, and how many columns? (c) What is the difference between an equi-join and a natural join?
20.
Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Priya | HR | 52000 | 8000
E2 | Meera | IT | 39000 | 1500
E3 | Ananya | HR | 82000 | 8500
E4 | Rahul | HR | 53000 | NULL
E5 | Arjun | HR | 60000 | 5000
E6 | Asha | HR | 67000 | NULL
Write SQL queries to display (a) the number of employees in each department; (b) the highest and lowest salary in HR; (c) the departments whose total salary is more than 100000; (d) the names of employees who get no bonus.
AmbifulWorksheets
Practice that never repeats
CBSE · Class 12 · Computer Science

Answer key

SQL: Functions, GROUP BY and Joins · AW-12CS-SQG-2026-V1 · For parents — fold back or tear off before handing the sheet to your child.

QAnswerWorking
1(d) LENGTH()—
2(d) 50000MIN(Salary) over rows where Dept = 'IT'
3(a) 146.87501175 ÷ 8; AVG skips NULL
4(d) 7COUNT(*) counts rows
5(d) 3one row for each Category: Desk, Paper, Writing
6(c) 1groups: HR 40000.0000; IT 33000.0000; Sales 49250.0000; HAVING keeps 1
7(b) 4only pairs with equal DeptID (and the extra condition) are kept
8(a) 6NATURAL JOIN shows the common column DeptID once
9COUNT(), SUM(), AVG(), MIN() and MAX().—
1012500SUM(Bonus) over rows where Dept = 'HR'; NULL is skipped
11Sec | MAX(Marks)
A | 97
B | 86
C | 93		one row per Sec
12Category | SUM(Price)
Writing | 670		groups before HAVING: Desk 170; Paper 170; Writing 670
13Name | City
Priya | Shimla
Rahul | Shimla
Rohan | Pune
Zoya | Indore		pair each EMP row with the DEPT row of the same DeptID
14(a) 7; (b) 52 Bonus values are NULL, and COUNT(column) skips NULL
15Dev, RohanNULL is tested with IS NULL, never with = NULL; 2 such rows
16(a) 18; (b) 76 × 3 rows; 4 + 3 columns
17COUNT(*) gives the number of items; SUM(Qty) the total quantity; AVG(Price) the mean price; MIN(Price) the cheapest price; MAX(Price) the dearest. Each returns one value for the rows selected, ignoring NULL.—
18(a)
Category | COUNT(*)
Desk | 4
Paper | 3
(b) 2		WHERE filters rows before grouping; HAVING filters groups after
19(a)
Name | City
Ananya | Indore
Arjun | Jaipur
Dev | Jaipur
Meera | Indore
(b) 4 rows, 6 columns; (c) an equi-join keeps both copies of the common column; a natural join shows it once.		join on equal DeptID
20(a) SELECT Dept, COUNT(*) FROM EMPLOYEE GROUP BY Dept; (b) SELECT MAX(Salary), MIN(Salary) FROM EMPLOYEE WHERE Dept = 'HR'; (c) SELECT Dept FROM EMPLOYEE GROUP BY Dept HAVING SUM(Salary) > 100000; (d) SELECT Name FROM EMPLOYEE WHERE Bonus IS NULL;GROUP BY with an aggregate; HAVING for a condition on groups; IS NULL