Engineering Questions with Answers - Multiple Choice Questions

MCQs on Aggregate Functions and Nested Subqueries – 1

1 - Question

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

View Answer

Answer: a
Explanation: None.




2 - Question

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)

View Answer

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




3 - Question

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

View Answer

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




4 - Question

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

View Answer

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




5 - Question

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

View Answer

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




6 - Question

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

View Answer

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




7 - Question

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

Answer: a
Explanation: None.




8 - Question

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

View Answer

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




9 - Question

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)
View Answer

Answer: a
Explanation: None.




10 - Question

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

View Answer

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

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