Subqueries and Joins

I continue my studies to pass the SQL and PL/SQL exam for Oracle certification. And I am finding that I get only 70% of the review questions right at the end of each chapter. I need to step up my game if I want to be sure to pass the certification exam.

This past chapter I read in my study guide was about subqueries and joins. I have been writing queries for a while. However I still learned a lot from this chapter. For example, if you define an alias for tables in your FROM clause, you cannot qualify column names with the table name any more. Go figure.

In a similar vein, you cannot use columns that you outer join in either IN or OR clauses. You also cannot put an ORDER BY clause in the subquery of a WHERE clause. Of course such an ORDER BY clause would not make much sense. The WHERE does not control the order of anything. However sometime Oracle let's you do things that don't make sense. Not here though.

Finally I am starting to learn what correlated subqueries are. They are when the subquery reference the table from the parent query. The subquery in this scenario is evaluated once for each row of the parent query. I am still not confident with correlated subqueries. So I think it is time for some real world practice.