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.

Get weekly updates about new MCQs and other posts by joining 18000+ community of active learners