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

This set of Database Interview Questions and Answers focuses on “Aggregate Functions and Nested Subqueries – 2”.

1.

SELECT dept_name, ID, avg (salary)
FROM instructor
GROUP BY dept_name;
This statement IS erroneous because

a) Avg(salary) should not be selected
b) Dept_id should not be used in group by clause
c) Misplaced group by clause
d) Group by clause is not valid in this query
View Answer

Answer: b
Explanation: Any attribute that is not present in the group by clause must appear only inside an aggregate function if it appears in the select clause, otherwise the query is treated as erroneous.
advertisement
advertisement

2. SQL applies predicates in the _______ clause after groups have been formed, so aggregate functions may be used.
a) Group by
b) With
c) Where
d) Having
View Answer

Answer: d
Explanation: The Having clause in SQL is used to apply predicates after groups have been formed using the Group By clause. This allows aggregate functions to be used and filters the grouped data based on specified conditions.

3. Aggregate functions can be used in the select list or the_______clause of a select statement or subquery. They cannot be used in a ______ clause.
a) Where, having
b) Having, where
c) Group by, having
d) Group by, where
View Answer

Answer: b
Explanation: To include aggregate functions having clause must be included after where.
Sanfoundry Certification Contest of the Month is Live. 100+ Subjects. Participate Now!

4. The ________ keyword is used to access attributes of preceding tables or subqueries in the from clause.
a) In
b) Lateral
c) Having
d) With
View Answer

Answer: b
Explanation:

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

advertisement

5. Which of the following creates a temporary relation for the query on which it is defined?
a) With
b) From
c) Where
d) Select
View Answer

Answer: a
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.
advertisement

6.

WITH max_budget (VALUE) AS
(SELECT MAX(budget)
FROM department)
SELECT budget
FROM department, max_budget
WHERE department.budget = MAX budget.value;

In the query given above which one of the following is a temporary relation?
a) Budget
b) Department
c) Value
d) Max_budget
View Answer

Answer: d
Explanation: With clause creates a temporary relation.

7. Subqueries cannot:
a) Use group by or group functions
b) Retrieve data from a table different from the one in the outer query
c) Join tables
d) Appear in select, update, delete, insert statements.
View Answer

Answer: c
Explanation: None.

8. Which of the following is not an aggregate function?
a) Avg
b) Sum
c) With
d) Min
View Answer

Answer: c
Explanation: With is used to create temporary relation and its not an aggregate function.

9. The EXISTS keyword will be true if:
a) Any row in the subquery meets the condition only
b) All rows in the subquery fail the condition only
c) Both of these two conditions are met
d) Neither of these two conditions is met
View Answer

Answer: a
Explanation: EXISTS keyword checks for existance of a condition.

10. How can you find rows that do not match some specified condition?
a) EXISTS
b) Double use of NOT EXISTS
c) NOT EXISTS
d) None of the mentioned
View Answer

Answer: b
Explanation: None.

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.