What is a correlated subquery? Include an example. What will ...

[mpobi80]

What is a correlated subquery? Include an example.
 
  What will be an ideal response?

Question 2

To access documentation about the MySQL Workbench, use the:
 
  A) Help  MySQL Workbench command.
  B) Help  Help Index command.
  C) Help  MySQL Website command.
  D) Help  MySQL.com Website command.
  E) Help  Documentation command.

[patma1981]

Answer to Question 1

A correlated subquery uses the standard SQL subquery structure of a SELECT statement (called the lower SELECT) within the WHERE clause of a controlling (or upper) SELECT. However, where a non-correlated subquery uses different tables in the upper and lower SELECTS, the correlated subquery uses the same table in both SELECTS. SQL aliases are used to provide different table names within the query. When a non-correlated subquery is processed, the lower SELECT is processed first and the entire result set of the lower SELECT is passed to the upper SELECT. When a correlated subquery is processed, a nested processing is used where each individual result of the lower SELECT is returned one at a time to the upper SELECT for processing. Here is an example that finds the last names of all students advised by a faculty whose last name is 'Smith':
SELECT S.LastName
FROM Student S
WHERE EXISTS
(SELECT
FROM FACULTY F
WHERE F.FacultyID = S.AdvisorID AND F.LastName = 'Smith'));

Answer to Question 2

B