Engineering Questions with Answers - Multiple Choice Questions
Database MCQ – 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.