MySQL Questions and Answers – Performing Multiple – Table Retrievals with Subqueries

This set of MySQL Interview Questions and Answers for freshers focuses on “Performing Multiple – Table Retrievals with Subqueries”.

1. The facility that allows nesting one select statement into another is called __________
a) nesting
b) binding
c) subquerying
d) encapsulating
View Answer

Answer: c
Explanation: The ‘subquerying’ support provided by MySQL is a capability that allows writing one ‘SELECT’ statement within parentheses and nesting within another. This allows logically selecting content from tables.

2. Which subquery returns a single value?
a) scalar
b) column
c) row
d) table
View Answer

Answer: a
Explanation: The subqueries in MySQL can return different types of information. A scalar query returns a single value. Column subquery return a single column and row subquery returns a single row.

3. Usage of aggregates in WHERE clause is allowed.
a) True
b) False
View Answer

Answer: b
Explanation: The usage of aggregates inside ‘WHERE’ clauses is not allowed. For example, the following statement will not work : ‘SELECT * FROM my_table WHERE attribute_name = MAX(attribute_name)’, because the MAX value is not known yet.
advertisement
advertisement

4. Which operators are used when a subquery returns multiple rows to be evaluated in comparison to the outer query?
a) IN and NOT IN
b) EXISTS and NOT EXISTS
c) OUTER JOIN and INNER JOIN
d) LEFT JOIN and RIGHT JOIN
View Answer

Answer: a
Explanation: When there is a need to evaluate multiple rows in comparison to the outer query, the ‘IN’ and ‘NOT IN’ operators are used. They are used for testing whether a comparison value is present in a set of values.

5. The ALL subquery performs which operation?
a) row
b) column
c) table
d) database
View Answer

Answer: b
Explanation: The operators ‘ALL’ and ‘ANY’ are used to perform operations on columns. They are used in conjunction with a comparison operator in order to test the result of a column subquery.
Note: Join free Sanfoundry classes at Telegram or Youtube

6. Which of these operators perform similar operations like ALL and ANY?
a) SOME
b) MANY
c) SELECT
d) GROUP
View Answer

Answer: a
Explanation: The operators ‘SOME’, ‘ALL’ and ‘ANY’ perform operations on columns. They can filter column results. They are used in conjunction with a comparison operator in order to test the result of a column subquery.

7. Which operators test whether a subquery returns any rows?
a) IN and NOT IN
b) EXISTS and NOT EXISTS
c) PRESENT
d) ABSENT
View Answer

Answer: b
Explanation: The operators ‘EXISTS’ and ‘NOT EXISTS’ operators only test whether a subquery returns any rows. If it returns a row, ‘EXISTS’ results into true and ‘NOT EXISTS’ results into false.
advertisement

8. An uncorrelated subquery does not contain any reference to the values from the outer query.
a) True
b) False
View Answer

Answer: a
Explanation: MySQL has two kinds of subqueries, namely, uncorrelated subquery and correlated subquery. An uncorrelated subquery does not contain any reference to the values from the outer query.

9. Which subquery cannot be executed by itself as a separate statement?
a) Correlated
b) Uncorrelated
c) EXISTS
d) NOT EXISTS
View Answer

Answer: a
Explanation: An uncorrelated subquery contains references to the values from the outer query. So, it is dependent on it. Therefore, a correlated subquery cannot be executed by itself as a separate statement.
advertisement

10. Which of these operators does not perform relative-value comparisons?
a) =
b) ==
c) <=
d) >=
View Answer

Answer: b
Explanation: The operators =, < >, >, >=, <, and <= perform relative value comparisons in MySQL. ‘==’ is not a valid comparison operator in MySQL. Such operators are useful in filtering information from a table.

Sanfoundry Global Education & Learning Series – MySQL Database.

To practice all areas of MySQL for Interviews, here is complete set of 1000+ Multiple Choice Questions and Answers.

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.