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