Database Questions and Answers – Aggregate Functions and Nested Subqueries – 1

This set of Database Multiple Choice Questions & Answers (MCQs) focuses on “Aggregate Functions and Nested Subqueries – 1”.

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

Answer: a
Explanation: 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)
View Answer

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

3.

Note: Join free Sanfoundry classes at Telegram or Youtube
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.
advertisement

4. 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. 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.
advertisement

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

Sanfoundry Global Education & Learning Series – Database Management System.

If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.