e-Activity Log into Oracle to create 2 queries -- a poorly written query and a w

Question

e-Activity

Log into Oracle to create 2 queries -- a poorly written query and a well written query. Take a screenshot using the keyboard shortcut or a snipping tool.

From the e-Activity, provide an example of a poorly written query. Explain why it is poorly written. (Online students must attach the screenshot to the discussion thread.)

From the e-Activity, provide an example of a well-written query. Explain why it is better than the previous example you provided. (Online students must attach the screenshot to the discussion thread.)

Explanation / Answer

Correlated vs. Non-Correlated subquery

For those that don't know what a correlated subquery is: it means that the subquery relies on values from the outer query. If the subquery could be executed by itself, then it would be non-correlated. Here's an example of a non-correlated query. Using the `world` database, I want to return all cities that have a population larger than New York's population:

SELECT name FROM City WHERE population > (SELECT population FROM City WHERE name = 'new york');

Since the subquery can be run by itself, it's non-correlated. These queries are generally okay, though if they are slow you should check to see if indexes are being used or consider re-writing as a join.

Now here's an example of a correlated subquery. The query returns the countries that have a city bigger than 9 million people:

SELECT name FROM Country AS co WHERE EXISTS (SELECT * FROM City WHERE population>9000000 AND co.code = countrycode);

Notice that the subquery references the outer query (co.code). This causes the subquery to execute for each row of the outer query. That's a lot of work!

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.