Computer Science - Class 12
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:

 ADMNO NAME CLASS SEC RNO ADDRESS PHONE 1211 MEENA 12A D 4 A-26 3245678 1212 VANI 10A D 1 B-25 5456789 1213 MEENA 12B A 1 NULL NULL 1214 KARISH 10B B 3 AB-234 4567890

 ADMNO GAME COACHNAME GRADE 1215 CRICKET MR.RAVI A 1213 VOLLEYBALL MR.MANDEEP B 1211 VOLLEYBALL MR.GOVARDHAN A 1212 BASKETBALL MR.TIWARY B

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

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

Checking the options

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)

## (D) DELETE FROM STUDENTS WHERE DROP PHONE;

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)

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

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)

## (iv)C AND D

Checking the options

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)

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

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;

• (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)

Learn in your speed, with individual attention - Teachoo Maths 1-on-1 Class