SQL -Structured Query Language - SQL Table - Alter - Add Column Quiz

Which of the following is used to insert a tuple from another relation?

• 1]

  INSERT INTO course (course id, title, dept name, credits)
  VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4);

   

• 2]

  INSERT INTO instructor
  SELECT ID, name, dept name, 18000
  FROM student
  WHERE dept name = ’Music’ AND tot cred > 144;

   

• 3]

  INSERT INTO course VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4);

   

• 4]

  Not possible

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.

• 1]

  DELETE FROM instructor
  WHERE dept_name IN 'Watson';

   

• 2]

  DELETE FROM department 
  WHERE building='Watson';

   

• 3]

  DELETE FROM instructor
  WHERE dept_name IN (
  SELECT dept name FROM department
  WHERE building = ’Watson’);

   

• 4]

   None of the mentioned

Fill in with correct keyword to update the instructor relation.

UPDATE instructor
_____ salary= salary * 1.05;

• 1]

  Where

• 2]

  Set

• 3]

  In

• 4]

  Select

 _________ are useful in SQL update statements, where they can be used in the set clause.

• 1]

  Multiple queries

• 2]

  Sub queries

• 3]

  Update

• 4]

  Scalar subqueries

The problem of ordering the update in multiple updates is avoided using

• 1]

  Set

• 2]

  Where

• 3]

  Case

• 4]

  When

Which of the following is the correct format for case statements.

• 1]

  CASE
  WHEN pred1 ... result1
  WHEN pred2 ... result2
  . . .
  WHEN predn ... resultn
  ELSE result0
  END

   

• 2]

  CASE
  WHEN pred1 THEN result1
  WHEN pred2 THEN result2
  . . .
  WHEN predn THEN resultn
  ELSE result0
  END

   

• 3]

  CASE
  WHEN pred1 THEN result1
  WHEN pred2 THEN result2
  . . .
  WHEN predn THEN resultn
  ELSE result0

   

• 4]

  All of the mentioned

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.

• 1]

  UPDATE instructor
  SET salary = salary * 1.03
  WHERE salary > 100000;
  UPDATE instructor
  SET salary = salary * 1.05
  WHERE salary <= 100000;

   

• 2]

  UPDATE instructor
  SET salary = salary * 1.05
  WHERE salary < (SELECT avg (salary)
  FROM instructor);

   

• 3]

  UPDATE instructor
  SET salary = CASE
  WHEN salary <= 100000 THEN salary * 1.03
  ELSE salary * 1.05
  END

   

• 4]

  None of the mentioned