In a Database, there are two tables with the instances given below:
Table: STUDENTS
| 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 |
Table : SPORTS
| ADM NO. |
GAME |
COACH NAME |
GRADE |
| 1215 |
CRICKET |
MR. RAVI |
A |
| 1213 |
VOLLEYBALL |
MR. AMANDEEP |
B |
| 1211 |
VOLLEYBALL |
MR. GOVARDHAN |
A |
| 1212 |
BASKETBALL |
MR TEWARI |
B |
I 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 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;
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;
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;
VI 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 ;
a. A AND B
b. B AND C
c. A AND D
d. C AND D
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
