This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Aggregation of Data”.
1. Aggregate functions are functions that take a ___________ as input and return a single value.
a) Collection of values
b) Single value
c) Double value
d) All of the mentioned
Explanation: Aggregate functions perform a calculation on a set of values.
2. SQL applies predicates in the _______ clause after groups have been formed, so aggregate functions may be used.
a) Group by
Explanation: The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs.
3. The ________ keyword is used to access attributes of preceding tables or subqueries in the from clause.
Explanation: Select name, salary, avg salary from instructor I1, lateral (select avg(salary) as avg salary from instructor I2 where I2.dept name= I1.dept name);
Without the lateral clause, the subquery cannot access the correlation variable I1 from the outer query.
4. Which of the following is not a aggregate function ?
Explanation: With is used to create temporary relation and its not a aggregate function.
5. If we do want to eliminate duplicates, we use the keyword __________in the aggregate expression.
d) Primary key
Explanation: Distinct keyword is used to select only unique items from the relation.
6. All aggregate functions except __________ ignore null values in their input collection.
Explanation: * is used to select all values including null.
7. A Boolean data type that can take values true, false, and ____________
Explanation: Unknown values do not take null value but it is not known.
8. 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 ?
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);
SELECT DISTINCT course_id FROM instructor WHERE name NOT IN (’Fall’, ’Spring’);
SELECT course id FROM SECTION WHERE semester = 'Spring' AND YEAR= 2010);
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);
Explanation: Aggregate function can be used inside nested subqueries.
9. The phrase “greater than at least one” is represented in SQL by ___________
a) < all
b) < some
c) > all
d) > some
Explanation: > some takes at lest one value above it .
10. We can test for the nonexistence of tuples in a subquery by using the __________ construct.
a) Not exist
b) Not exists
Explanation: Exists is used to check for existence of tuples.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.