Database Multiple Choice Questions on “Modification of Database”.
1. A Delete command operates on ______ relation.
a) One
b) Two
c) Several
d) Null
Answer: a
Clarification: Delete can delete from only one table at a time.
2.
Delete from r where P;
The above command
a) Deletes a particular tuple from the relation
b) Deletes the relation
c) Clears all entries from the relation
d) All of the mentioned
Answer: a
Clarification: Here P gives the condition for deleting specific rows.
3. Which one of the following deletes all the entries but keeps the structure of the relation.
a) Delete from r where P;
b) Delete from instructor where dept name= ’Finance’;
c) Delete from instructor where salary between 13000 and 15000;
d) Delete from instructor;
Answer: d
Clarification: Absence of condition deletes all rows.
4. Which of the following is used to insert a tuple from another relation?
a)
INSERT INTO course (course id, title, dept name, credits) VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4);
b)
INSERT INTO instructor SELECT ID, name, dept name, 18000 FROM student WHERE dept name = ’Music’ AND tot cred > 144;
c)
INSERT INTO course VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4);
d) Not possible
Answer: b
Clarification: Using select statement in insert will include rows which are the result of the selection.
5. Which of the following deletes all tuples in the instructor relation for those instructors associated with a department located in the Watson building which is in department relation.
a)
DELETE FROM instructor WHERE dept_name IN 'Watson';
b)
DELETE FROM department WHERE building='Watson';
c)
DELETE FROM instructor WHERE dept_name IN (SELECT dept name FROM department WHERE building = ’Watson’);
d) None of the mentioned
Answer: c
Clarification: The query must include building=watson condition to filter the tuples.
6.
UPDATE instructor _____ salary= salary * 1.05;
Fill in with correct keyword to update the instructor relation.
a) Where
b) Set
c) In
d) Select
Answer: b
Clarification: Set is used to update the particular value.
7. _________ are useful in SQL update statements, where they can be used in the set clause.
a) Multiple queries
b) Sub queries
c) Update
d) Scalar subqueries
Answer: d
Clarification: None.
8. The problem of ordering the update in multiple updates is avoided using
a) Set
b) Where
c) Case
d) When
Answer: c
Clarification: The case statements can add the order of updating tuples.
9. Which of the following is the correct format for case statements.
a)
CASE WHEN pred1 ... result1 WHEN pred2 ... result2 . . . WHEN predn ... resultn ELSE result0 END
b)
CASE WHEN pred1 THEN result1 WHEN pred2 THEN result2 . . . WHEN predn THEN resultn ELSE result0 END
c)
CASE WHEN pred1 THEN result1 WHEN pred2 THEN result2 . . . WHEN predn THEN resultn ELSE result0
d) All of the mentioned
Answer: b
Clarification: None.
10. Which of the following relation updates all instructors with salary over $100,000 receive a 3 percent raise, whereas all others receive a 5 percent raise.
a)
UPDATE instructor SET salary = salary * 1.03 WHERE salary > 100000; UPDATE instructor SET salary = salary * 1.05 WHERE salary <= 100000;
b)
UPDATE instructor SET salary = salary * 1.05 WHERE salary < (SELECT avg (salary) FROM instructor);
c)
UPDATE instructor SET salary = CASE WHEN salary <= 100000 THEN salary * 1.03 ELSE salary * 1.05 END
d) None of the mentioned
Answer: a
Clarification: The order of the two update statements is important. If we changed the order of the two statements, an instructor with a salary just under $100,000 would receive an over 8 percent raise. SQL provides a case construct that we can use to perform both the updates with a single update statement, avoiding the problem with the order of updates.