3. Correlated nested queries often are based on the EXISTS keyword in SQL. EXISTS is used in the WHERE clause with syntax WHERE... EXISTS (SELECT FROM... WHERE.. If the inner SELECT FROM WHERE query returns 1 or more rows (i.e. any rows at al then EXISTS)is true. If it returns no rows, then EXISTS is false. Since it's only based on the number of rows and not the contents, never use column names in SELECT for the inner query. NOT EXISTS (SELECT FROM WHERE..) is the opposite of this behavior. EXISTS is equivalent to the nested aggregation query WHERE.. (SELECT COUNT) FROM.. WHERE .>0 but EXISTS is much easier/clearer for people to read and more importantly tells the database it can stop computing after finding one row. It may be faster! Finally the actual thing you do for number 3: Write down a correlated nested query that answers "find the frst and last names of all golfers that have not entered a tournament Do this by rewriting the query as "Find the first and last name of all golfers where there does not exist an entry in the tournament for that golfer". (The part in italics tells you its a correlated query). 4. Write a correlated nested query in SQL using NOT EXISTS that answers "find all tournaments that have not been entered by a golfer 5. SQL queries that involve "all" or every can be complicated. Combine SQL from 3 and 4 to write SQL for the query "Find the first and last names of golfers that have entered every tournament." Hint: Rewrite the query in such a way that the word "exists" appears twice in the query. "Find the first and last names of all golfers where there does not exist a tournament they have not entered" is the first step. Revise the last part of the query to have existot exist as well. 6. Write the query "Find the first and last names of all golfers that have entered every tournament. as a correlated, nested aggregation query 3. Correlated nested queries often are based on the EXISTS keyword in SQL. EXISTS is used in the WHERE clause with syntax WHERE... EXISTS (SELECT FROM... WHERE.. If the inner SELECT FROM WHERE query returns 1 or more rows (i.e. any rows at al then EXISTS)is true. If it returns no rows, then EXISTS is false. Since it's only based on the number of rows and not the contents, never use column names in SELECT for the inner query. NOT EXISTS (SELECT FROM WHERE..) is the opposite of this behavior. EXISTS is equivalent to the nested aggregation query WHERE.. (SELECT COUNT) FROM.. WHERE .>0 but EXISTS is much easier/clearer for people to read and more importantly tells the database it can stop computing after finding one row. It may be faster! Finally the actual thing you do for number 3: Write down a correlated nested query that answers "find the frst and last names of all golfers that have not entered a tournament Do this by rewriting the query as "Find the first and last name of all golfers where there does not exist an entry in the tournament for that golfer". (The part in italics tells you its a correlated query). 4. Write a correlated nested query in SQL using NOT EXISTS that answers "find all tournaments that have not been entered by a golfer 5. SQL queries that involve "all" or every can be complicated. Combine SQL from 3 and 4 to write SQL for the query "Find the first and last names of golfers that have entered every tournament." Hint: Rewrite the query in such a way that the word "exists" appears twice in the query. "Find the first and last names of all golfers where there does not exist a tournament they have not entered" is the first step. Revise the last part of the query to have existot exist as well. 6. Write the query "Find the first and last names of all golfers that have entered every tournament. as a correlated, nested aggregation query
