Information Technology TERM II
Database Management System
Class 10 Session 2021-2022
Case Study Questions 03
Direction Read the case and answer the following questions.
Consider the table STUDENT with following details.
Table : STUDENT
| STU_ID | Name | Stream | Marks | Class |
|---|---|---|---|---|
| 1 | Armaan | Science | 87.5 | 12A |
| 2 | Vicky | Commerce | 88.7 | 12B |
| 3 | Meeta | Humanities | 76.8 | 12C |
| 4 | Vanisha | Science | 79.5 | 12A |
| 5 | Kanika | Science | 77.9 | 12A |
| 6 | Anandi | Commerce | 86.7 | 12B |
(i) Command to select all Commerce students from the table STUDENT.
(a) SELECT * FROM STUDENT;
(b) SELECT Stream FROM STUDENT WHERE Stream= ‘Commerce’;
(c) SELECT * FROM STUDENT WHERE Stream = ‘Commerce’;
(d) SELECT Name FROM STUDENT WHERE Stream = ‘Commerce’;
(c) Command to select all Commerce students from the table STUDENT is
SELECT*FROM STUDENT WHERE Stream=‘Commerce’;
(ii) View all records other than ‘12A’ class.
(a) SELECT DISTINCT Class FROM STUDENT;
(b) SELECT Class FROM STUDENT WHERE (Class NOT = ‘Science’);
(c) SELECT * FROM STUDENT WHERE Class NOT IN (‘Science’);
(d) None of the above
(d) Here, none of the query statisfies the given condition. To solve the query SELECT statement should have been used. The correct statement is SELECT*FROM STUDENT WHERE CLASS NOT IN (’12A’);
(iii) Modify the marks of Kanika as 85.5.
(a) ALTER TABLE STUDENT SET Marks = 85.5 WHERE Name = ‘Kanika’;
(b) UPDATE STUDENT SET Marks=85.5 WHERE Name = Kanika’;
(c) UPDATE TABLE STUDENT SET Marks =85.5 WHERE Name= ‘Kanika’;
(d) ALTER STUDENT SET Marks=85.5 WHERE Name = ‘Kanika’;
(b) Command to modify the marks of Kanika as 85 5. is UPDATE STUDENT SET Marks = 85.5 WHERE Name = ‘Kanika’;
(iv) Command to delete all records that belongs to ‘Science’ stream .
(a) DELETE FROM STUDENT WHERE Stream=‘Science’;
(b) DROP FROM STUDENT WHERE Stream=‘Science’;
(c) DROP TABLE STUDENT WHERE Stream=‘Science’;
(d) None of the above
(a) To delete data from a table the SQL command DELETE is used.
(v) Add a record of new student ‘Rashmi’ of ‘Humanities’ stream in class ‘12B’ and her marks is ‘70.9’.
(a) INSERT INTO STUDENT VALUES (‘Rashmi’, ‘Humanities’, ‘12B’, 70.9);
(b) INSERT INTO STUDENT VALUES (7,‘Rashmi’, ‘Humanities’, ‘12B’, 70.9);
(c) INSERT INTO STUDENT VALUES (‘Rashmi’, ‘Humanities’, 70.9, ‘12B’);
(d) INSERT INTO STUDENT VALUES (7,‘Rashmi’, ‘Humanities’, 70.9, ‘12B’);
(d) INSERT INTO STUDENT VALUES (7, ‘Rashmi’, ‘Humanities’, 70.9, ‘12B’);