Class 12 Querying and SQL Functions Mock Test (CBSE)

Topics in Querying and SQL Functions

• Functions in SQL
• Single row functions: math, string and date
• Aggregate functions
• GROUP BY in SQL
• HAVING clause
• Operations on relations: union, intersect and minus
• Cartesian product
• Using two relations in a query: joins

Querying and SQL Functions practice questions with answers

1. Q1Multiple choice·1 mark
   A clinic stores two tables: PATIENT(PID, PNAME, DOCID) and DOCTOR(DOCID, DNAME, SPECIALITY). Which SQL query displays the name of each patient along with the name of the doctor treating them, for only those doctors whose SPECIALITY is 'Cardiology'?

   1. (a)SELECT PNAME, DNAME FROM PATIENT, DOCTOR WHERE SPECIALITY = 'Cardiology';
   2. (b)SELECT PNAME, DNAME FROM PATIENT, DOCTOR WHERE PATIENT.DOCID = DOCTOR.DOCID AND SPECIALITY = 'Cardiology';
   3. (c)SELECT PNAME, DNAME FROM PATIENT, DOCTOR WHERE PATIENT.PID = DOCTOR.DOCID AND SPECIALITY = 'Cardiology';
   4. (d)SELECT PNAME, DNAME FROM PATIENT, DOCTOR WHERE PATIENT.DOCID = DOCTOR.DOCID OR SPECIALITY = 'Cardiology';
   Show answer and step marks

   1. Correct option: (b) SELECT PNAME, DNAME FROM PATIENT, DOCTOR WHERE PATIENT.DOCID = DOCTOR.DOCID AND SPECIALITY = 'Cardiology';+1

   Answer: (b)
2. Q2Multiple choice·1 mark
   Consider the SQL statement SELECT MOD(-17, 5); What value does it return?

   1. (a)$3$
   2. (b)$-3$
   3. (c)$2$
   4. (d)$-2$
   Show answer and step marks

   1. Correct option: (d) $-2$+1

   Answer: (d)
3. Q3Short answer·2 marks
   Table ORDERS has five rows. The column Qty holds the values 4, 10, NULL, 6 and 2, in that order. Give the output that each query displays. (a) SELECT COUNT(*) FROM ORDERS; [1] (b) SELECT AVG(Qty) FROM ORDERS; [1]
   Show answer and step marks

   1. (a) COUNT(*) counts every row, including the row where Qty is NULL, so the output is 5+1
   2. (b) AVG ignores NULL values, so the divisor is the 4 non-NULL values, not 5+0.5
   3. (b) Sum of the non-NULL values is 4 + 10 + 6 + 2 = 22, so AVG(Qty) = $22/4 = 5.5$+0.5

   Answer: (a) 5; (b) 5.5
4. Q4Short answer·3 marks
   A table ORDERS has columns Order_ID, Region and Amount, and stores these rows: (1, North, 500), (2, South, 300), (3, North, 700), (4, East, 200), (5, South, 450). Query Q1 is: SELECT COUNT(*) FROM ORDERS; Query Q2 is: SELECT Region, COUNT(*) FROM ORDERS GROUP BY Region; (a) Write the output of Q2. [1] (b) Write the number of rows and the value displayed by Q1, and give a reason why Q1 and Q2 differ in the number of rows. [2]
   Show answer and step marks

   1. Rows are grouped by Region into North, South and East+0.5
   2. Count for each group: North 2, South 2, East 1+0.5
   3. Q1 has no GROUP BY, so the whole table is one group and returns a single row with value 5+1
   4. GROUP BY gives one output row for each distinct Region, so Q2 returns 3 rows while Q1 returns 1 row+1

   Answer: (a) North 2, South 2, East 1 (three rows). (b) Q1 gives 1 row with value 5; Q2 gives one row per distinct Region, so it has 3 rows while Q1 has 1.
5. Q5Short answer·3 marks
   Two relations of a school club have the same columns (Roll, Name) and these rows: ART: (1, Asha), (2, Ravi), (3, Meena) MUSIC: (2, Ravi), (4, Kabir), (3, Meena) (a) Name the set operation that returns only the rows present in both ART and MUSIC, and list those rows. [1] (b) Name the set operation that returns the rows of ART that are not present in MUSIC, and list those rows. [2]
   Show answer and step marks

   1. Operation: intersect+0.5
   2. Rows common to both: (2, Ravi) and (3, Meena)+0.5
   3. Operation: minus, computed as ART minus MUSIC+1
   4. Row of ART not in MUSIC: (1, Asha)+1

   Answer: (a) Intersect; (2, Ravi) and (3, Meena). (b) Minus (ART minus MUSIC); (1, Asha).

More Class 12 Informatics Practices mock tests