250+ TOP MCQs on Aggregate Functions and Nested Subqueries

Database Multiple Choice Questions on “Aggregate Functions and Nested Subqueries”.

1. Aggregate functions are functions that take a ___________ as input and return a single value.
a) Collection of values
b) Single value
c) Aggregate value
d) Both Collection of values & Single value

Answer: a
Clarification: None.

2.

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

Which of the following should be used to find the mean of the salary ?
a) Mean(salary)
b) Avg(salary)
c) Sum(salary)
d) Count(salary)

Answer: b
Clarification: Avg() is used to find the mean of the values.

3.

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.
a) Distinct
b) Count
c) Avg
d) Primary key

Answer: a
Clarification: Distinct keyword is used to select only unique items from the relation.

4. All aggregate functions except _____ ignore null values in their input collection.
a) Count(attribute)
b) Count(*)
c) Avg
d) Sum

Answer: b
Clarification: * is used to select all values including null.

5. A Boolean data type that can take values true, false, and________
a) 1
b) 0
c) Null
d) Unknown

Answer: d
Clarification: Unknown values do not take null value but it is not known.

6. 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.
a) Or, in
b) Not in, in
c) In, not in
d) In, or

Answer: c
Clarification: In checks, if the query has the value but not in checks if it does not have the value.

7. 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 .
a)

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);

b)

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

c)

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

d)

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);

Answer: a
Clarification: None.

8. The phrase “greater than at least one” is represented in SQL by _____
a) < all
b) < some
c) > all
d) > some

Answer: d
Clarification: >some takes atlest one value above it .

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

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);

b)

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

c)

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);

d)

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

Answer: a
Clarification: None.

10. We can test for the nonexistence of tuples in a subquery by using the _____ construct.
a) Not exist
b) Not exists
c) Exists
d) Exist

Answer: b
Clarification: Exists is used to check for the existence of tuples.