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:

 

Question 1 - image 1..jpg

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

Question 1 - image 2..jpg

 

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.

(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:

Question 1 (i) - image 1.jpg

Checking the options

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

Question 1 (i) - image 2.jpg

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

Question 1 (i) - image 3.jpg

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

Question 1 - image 4..jpg

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

Question 1 (i) - image 5.jpg

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:

Question 1 (ii) - image 3.jpg

Checking the options

  • (A) ALTER TABLE STUDENTS DROP PHONE; 

Question 1 (ii) - image 2.jpg

Table after executing the above statement:

Question 1 (ii) - image 3.jpg

  • (B) DROP PHONE;

Question 1 (ii) - image 4.jpg

  • (C) UPDATE DROP PHONE; 

Question 1 (ii) - image 5.jpg

  • (D) DELETE FROM STUDENTS WHERE DROP  PHONE;

Question 1 (ii) - image 6.jpg

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:

Question 1 (ii) - image 7.jpg

Checking the options

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

Question 1 (iii) - image 2.jpg

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

Question 1 (iii) - image 3.jpg

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

Question 1 (iii) - image 4.jpg

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

Question 1 (iii) - image 5.jpg

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;  

Question 1 (iv) - image 1.jpg

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

Question 1 (iv) - image 2.jpg

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

Question 1 (iv) - image 3.jpg

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

Question 1 (iv) - image 4.jpg

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; 

Question 1 (iv) - image 5.jpg

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

Question 1 (v) - image 1.jpg

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

Question 1 (v) - image 2.jpg

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

Question 1 (v) - image 3.jpg

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


Transcript

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

Ask a doubt
Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 13 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.