Question 1 - Case Based MCQ Questions - Chapter 9 - Structured Query Language

This question asked in CBSE Board Question Bank 2021

In a Database, there are two tables with the instances given below:

Choose the command to display name and game of those students whose address is available in students’ table.

(A) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS. ADMNO=SPORTS.ADMNO AND ADDRESS IS NOT NULL; (B) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS. ADMNO=SPORTS.ADMNO AND ADDRESS IS NULL;

(C) SELECT NAME, GAME FROM STUDNETS, SPORTS WHERE STUDENTS. ADMNO=SPORTS.ADMNO, ADDRESS IS NULL;

(D) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS. ADMNO=SPORTS.ADMNO NOT ADDRESS IS NULL;

Answer:

The names and games of students whose address is available in students table are:

Checking the options

• (A) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS.  ADMNO=SPORTS.ADMNO AND ADDRESS  IS NOT NULL;  

• (B) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS.  ADMNO=SPORTS.ADMNO AND ADDRESS  IS NULL;  

• (C) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS.  ADMNO=SPORTS.ADMNO, ADDRESS IS  NULL; 

• (D) SELECT NAME, GAME FROM STUDENTS, SPORTS WHERE STUDENTS.  ADMNO=SPORTS.ADMNO NOT ADDRESS  IS NULL; 

The SQL statement in option (A) will match the ADMNO of students in both STUDENTS table and SPORTS table and returns the NAME and GAME of those students whose ADDRESS is available in the STUDENTS table.

So, the correct answer is (A)

Question 1 (ii)

Identify the statement to delete a column phone from the table students.

(A) ALTER TABLE STUDENTS DROP PHONE;

(B) DROP PHONE;

(C) UPDATE DROP PHONE;

(D) DELETE FROM STUDENTS WHERE DROP PHONE;

Answer:

Upon deleting the column phone, the table should look like this:

Checking the options

• (A) ALTER TABLE STUDENTS DROP PHONE; 

Table after executing the above statement:

• (B) DROP PHONE;

• (C) UPDATE DROP PHONE; 

• (D) DELETE FROM STUDENTS WHERE DROP  PHONE;

The SQL statement in Option (A) will delete the column phone from the table STUDENTS.

So, the correct answer is (A)

Question 1 (iii)

Choose the command to display Name of the students who are studying in class 12 and their corresponding Coach names

(A) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE “12%” AND STUDENTS.ADMNO = SPORTS. ADMNO;

(B) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE "%12" AND STUDENTS.ADMNO = SPORTS.ADMNO; (C) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE “12%” AND ADMNO.STUDENTS = ADMNO. SPORTS;

(D) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE “12%” AND STUDENTS.ADMNO=SPORTS. ADMNO;

Answer:

Name of the students who are studying in class 12 and their corresponding Coach names are:

Checking the options

• (A) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE “12%”  AND STUDENTS.ADMNO = SPORTS. ADMNO;  

• (B) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE "%12"  AND STUDENTS.ADMNO = SPORTS.ADMNO;

• (C) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE “12%”  AND ADMNO.STUDENTS = ADMNO.  SPORTS;  

• (D) SELECT NAME, COACHNAME FROM STUDENTS, SPORTS WHERE CLASS LIKE= “12%” AND STUDENTS.ADMNO=SPORTS.  ADMNO; 

The SQL statement in Option (A) will display the Name of the students who are studying in class 12 and their corresponding Coach names.

So, the correct answer is (A)

Question 1 (iv)

Which two select queries will give the same output

(A) SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NULL AND STUDENTS.ADMNO = SPORTS.ADMNO;

(B) SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NOT NULL AND STUDENTS.ADMNO = SPORTS.ADMNO; (C) SELECT NAME, GRADE FROM STUDENTS, SPORTS WHERE ADDRESS IS NULL OR STUDENTS.ADMNO=SPORTS.ADMNO;

(D) SELECT ST.NAME, SP.GRADE FROM STUDENTS ST, SPORTS SP WHERE ADDRESS IS NULL AND ST.ADMNO=SP.ADMNO;

(i) A AND B

(ii) B AND D

(iii)A AND D

(iv)C AND D

Answer:

Checking the options

• (A) SELECT NAME, GRADE FROM STUDENTS,  SPORTS WHERE ADDRESS IS NULL AND  STUDENTS.ADMNO = SPORTS.ADMNO;  

• (B) SELECT NAME, GRADE FROM STUDENTS,  SPORTS WHERE ADDRESS IS NOT NULL  AND STUDENTS.ADMNO = SPORTS.ADMNO; 

• (C) SELECT NAME, GRADE FROM STUDENTS,  SPORTS WHERE ADDRESS IS NULL OR STUDENTS.ADMNO=SPORTS.ADMNO;  

• (D) SELECT ST.NAME, SP.GRADE FROM STUDENTS ST, SPORTS SP WHERE ADDRESS IS  NULL AND ST.ADMNO=SP.ADMNO;  

Now, checking the options

• (i) A AND B - The outputs are not the same.

• (ii) B AND D - The outputs are not the same.  

• (iii)A AND D - The outputs are the same. 

• (iv)C AND D - The outputs are not the same.  

From the above output, the outputs of option (A) and option (D) are the same .

So, the correct answer is (iii)

Question 1 (v)

Choose the command to count the number of students who play volleyball

(A) SELECT COUNT(*) FROM STUDENTS, SPORTS WHERE GAME=”VOLLEYBALL” AND STUDENTS.ADMNO = SPORTS.ADMNO;

(B) SELECT COUNT(GAME) FROM STUDENTS, SPORTS WHERE GAME=”VOLLEYBALL” AND STUDENTS.ADMNO=SPORTS.ADMNO; (C) SELECT COUNT(*) FROM STUDENTS, SPORTS WHERE GAME=”VOLLEYBALL”;

(D) SELECT COUNT(*) FROM STUDENTS, SPORTS WHERE SPORTS=”VOLLEYBALL” AND STUDENTS. ADMNO=SPORTS.ADMNO

Answer:

The count of students who play volleyball is 2.

Checking the options

• (A) SELECT COUNT(*) FROM STUDENTS,  SPORTS WHERE GAME=”VOLLEYBALL”  AND STUDENTS.ADMNO = SPORTS.ADMNO; 

• (B) SELECT COUNT(GAME) FROM STUDENTS,  SPORTS WHERE GAME=”VOLLEYBALL”  AND STUDENTS.ADMNO=SPORTS.ADMNO;

• (C) SELECT COUNT(*) FROM STUDENTS,  SPORTS WHERE GAME=”VOLLEYBALL”;

• (D) SELECT COUNT(*) FROM STUDENTS,  SPORTS WHERE SPORTS=”VOLLEYBALL”  AND STUDENTS. ADMNO=SPORTS.ADMNO 

Even though both option (A) and option (B) give count as 2, option (B) gives the count on the number of times the game volleyball appears in the sports table. It is option (A) which gives the number of students who play volleyball.

So, the correct answer is (A)