Engineering Questions with Answers - Multiple Choice Questions
MCQs on Modification of Database
1 - Question
A Delete command operates on ______ relation.
a) One
b) Two
c) Several
d) Null
View Answer
Answer: a
Explanation: Delete can delete from only one table at a time.
2 - Question
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
View Answer
Answer: a
Explanation: Here P gives the condition for deleting specific rows.
3 - Question
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;
View Answer
Answer: d
Explanation: Absence of condition deletes all rows.
4 - Question
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
View Answer
Answer: b
Explanation: Using select statement in insert will include rows which are the result of the selection.
5 - Question
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
View Answer
Answer: c
Explanation: The query must include building=watson condition to filter the tuples.
6 - Question
UPDATE instructor _____ salary= salary * 1.05;
Fill in with correct keyword to update the instructor relation.
a) Where
b) Set
c) In
d) Select
View Answer
Answer: b
Explanation: Set is used to update the particular value.
7 - Question
_________ 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
View Answer
Answer: d
Explanation: None.
8 - Question
The problem of ordering the update in multiple updates is avoided using
a) Set
b) Where
c) Case
d) When
View Answer
Answer: c
Explanation: The case statements can add the order of updating tuples.
9 - Question
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
View Answer
Answer: b
Explanation: None.
10 - Question
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
View Answer
Answer: a
Explanation: 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.