What is a correlated subquery? Include an example
What will be an ideal response?
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'));
You might also like to view...
Other things held constant, the value of an option depends on the stock's price, the risk-free rate, and the
A. Variability of the stock price. B. Option's time to maturity. C. Strike price. D. All of the above. E. None of the above.
Which of the following is NOT a goal of a repetitive process?
a. high customer satisfaction b. low unit costs c. high efficiency d. high volume of production
When an issue of the parties' intent is in question, courts will use an objective standard as the test
Indicate whether the statement is true or false
You need ________ diagrams only for those objects whose states affect specific behaviors of the system
A) sequence B) statechart C) collaborative D) activity