Worksheets › Class 12 › SQL: Functions, GROUP BY and Joins
Class 12 · Computer Science worksheetSQL: Functions, GROUP BY and Joins
CBSE Computer Science. COUNT, SUM, AVG, MIN and MAX with NULL, GROUP BY and HAVING, Cartesian product, equi-join and natural join, on tables with computed results. Every sheet has 20 questions in three sections and an answer key, and the questions change every time.
Sample questions
Real questions from this chapter, as the app sets them. Tap Answer to see the answer and working.
Section A · multiple choice
- 1.
Which of these is NOT an aggregate function?
- AVG()
- COUNT()
- SUM()
- LENGTH()
Answer
(d) 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?
- 8
- 73000
- 50001
- 50000
Answer
(d) 50000
MIN(Salary) over rows where Dept = 'IT'
- 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?
- 146.8750
- 167.8571
- 1175
- 148.1250
Answer
(a) 146.8750
1175 ÷ 8; AVG skips NULL
Section B · short answer
- 4.
Name the five aggregate functions of SQL.
Answer
COUNT(), SUM(), AVG(), MIN() and MAX().
- 5.
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?
Answer
12500
SUM(Bonus) over rows where Dept = 'HR'; NULL is skipped
Section C · long answer
- 6.
Explain the five aggregate functions with an example of each on ITEM(ICode, IName, Category, Price, Qty).
Answer
COUNT(*) 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.
Fresh sheets for your child
In the app, every SQL: Functions, GROUP BY and Joins sheet is new: the app remembers the questions your child has already had and leaves them out. Print the sheet as a PDF, or use notebook mode and write the answers in a notebook.
Coming soon for Android and iPhone.