For this tutorial I will use the well known HR Schema of Oracle.There are several ways we can proceed to get the results. But here I am not showing the efficiency but the technique. This is a open question for the readers to find the more efficient result. The tricky part about Sub Queries and join are to figure that out which part will be on the sub query.
Scenario:
Find All employee and their job id and title.
Using Natural Join:
A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.
Using Sub Query inside from. When we use sub query inside from then the fields are virtually becomes table name.
As we have seen some of the sub query. Let's try another one:
Scenario:
What about to find the Max Salary of all department?
Scenario:
Find First name, Last Name, department name and salary of all employees Descending order
Scenario:
Find Employee id, department name, Employee First name and the city where the department belongs.