Quiz Discussion

SELECT __________
FROM instructor
WHERE dept name= ’Comp. Sci.’;

Which of the following should be used to find the mean of the salary ?

• 1]

  Mean(salary)

• 2]

  Avg(salary)

• 3]

  Sum(salary)

• 4]

  Count(salary)

Aggregate functions are functions that take a ___________ as input and return a single value.

• 1]

  Collection of values

• 2]

  Single value

• 3]

  Aggregate value

• 4]

  Both Collection of values & Single value

SELECT COUNT (____ ID)
FROM teaches
WHERE semester = ’Spring’ AND YEAR = 2010;

If we do want to eliminate duplicates, we use the keyword ______in the aggregate expression.

• 1]

  Distinct

• 2]

  Count

• 3]

  Avg

• 4]

  Primary key

Which of the following is used to find all courses taught in both the Fall 2009 semester and in the Spring 2010 semester .

• 1]

  SELECT course id
  FROM SECTION AS S
  WHERE semester = ’Fall’ AND YEAR= 2009 AND
  EXISTS (SELECT *
  FROM SECTION AS T
  WHERE semester = ’Spring’ AND YEAR= 2010 AND
  S.course id= T.course id);

   

• 2]

  SELECT name
  FROM instructor
  WHERE salary > SOME (SELECT salary
  FROM instructor
  WHERE dept name = ’Biology’);

   

• 3]

  SELECT COUNT (DISTINCT ID)
  FROM takes
  WHERE (course id, sec id, semester, YEAR) IN (SELECT course id, sec id, semester, YEAR
  FROM teaches
  WHERE teaches.ID= 10101);

   

• 4]

  (SELECT course id
  FROM SECTION
  WHERE semester = ’Spring’ AND YEAR= 2010)

   

The phrase “greater than at least one” is represented in SQL by _____

• 1]

  < all

• 2]

  < some

• 3]

  > all

• 4]

  > some

We can test for the nonexistence of tuples in a subquery by using the _____ construct.

• 1]

  Not exist

• 2]

  Not exists

• 3]

  Exists

• 4]

  Exist

The ____ connective tests for set membership, where the set is a collection of values produced by a select clause. The ____ connective tests for the absence of set membership.

• 1]

  Or, in

• 2]

  Not in, in

• 3]

  In, not in

• 4]

  In, or

All aggregate functions except _____ ignore null values in their input collection.

• 1]

  Count(attribute)

• 2]

  Count(*)

• 3]

  Avg

• 4]

  Sum

Which of the following should be used to find all the courses taught in the Fall 2009 semester but not in the Spring 2010 semester.

• 1]

  SELECT DISTINCT course id
  FROM SECTION
  WHERE semester = ’Fall’ AND YEAR= 2009 AND
  course id NOT IN (SELECT course id
  FROM SECTION
  WHERE semester = ’Spring’ AND YEAR= 2010);

   

• 2]

  SELECT DISTINCT course_id
  FROM instructor
  WHERE name NOT IN (’Fall’, ’Spring’);

   

• 3]

  (SELECT course id
  FROM SECTION
  WHERE semester = ’Spring’ AND YEAR= 2010)

   

• 4]

  SELECT COUNT (DISTINCT ID)
  FROM takes
  WHERE (course id, sec id, semester, YEAR) IN (SELECT course id, sec id, semester, YEAR
  FROM teaches
  WHERE teaches.ID= 10101);