hi guys can anyone explain to me what the codes in line 5 and 6 do? How does the WHERE works in those lines? Any explanation would be truly appreciated. Thanks in advance.
SELECT LAST_NAME ||', '|| FIRST_NAME AS "NAME"
FROM EMPLOYEES E, (SELECT JOB_ID, MAX(SALARY) SALARY
FROM EMPLOYEES
GROUP BY JOB_ID) M
WHERE E.JOB_ID = M.JOB_ID
AND E.SALARY = M.SALARY
This is treating the parenthesized "select" statement as a joinable table.
So, assuming that the table EMPLOYEES has columns JOB_ID and SALARY, lines 5 and 6 specify the join criteria between the actual table EMPLOYEES and the derived table identified as "M".
"where" works as a condition here.
@Bitbit it's a little clear now, but what is the use of the "E" right after Employees and the "M" after the parenthesis?
Hi King03,
E and M are known as Aliases. You can give a table or a column a different name through the use of an alias. This is useful and helpful if you have very long or complex table or column names. An alias name could be anything, but usually it is short and they can make queries easier to read and write.
You could google and read more about Alias in Sql
ok those named tables are crystal clear now for me I understand it already, ok back to the where clause.. again in the 5th row(still confusing for me) of my code it says:
WHERE E.JOB_ID = M.JOB_ID
so what exactly happened to my retrieved data when I included this in my code?
Still not clear because the following code:
SELECT LAST_NAME ||', '|| FIRST_NAME AS "NAME"
only showed me the full names of the employees which is not enough to explain how my 5th row code actually worked and affected the whole code. please help me and I really hope you'll have some more patience in explaining this to me.
Okay, so I'm just going to give you a narrative of what this statement is doing. Perhaps when you see how the whole thing works together it will give you a good overall understanding of how each part fits.
First, the outside select shows a concatenation of the parts of the name held in the Employees table. We use the abbreviation "E" as an alias to represent that table so we don't have to type out "Employees" every time. And, since the column names LastName and FirstName don't appear anywhere else, we don't have to include the alias to qualify which table a specific column comes from.
Next, inside the parentheses, is a query that retrieves the largest salary for each job id in the Employees table. Since there is only one table in that query, there is no need for any alias. However, by wrapping that in parentheses, it now can be treated as a single unit...a "derived table", if you will. As such, we can assign it an alias as well, "M" (probably to represent "Maximum" or something like that).
Now that we have two items which can be treated as tables, "E" and "M", we can join them. Since the join column has the same name in each of the "tables", we have to include the alias to qualify which column comes from which table (line 5). Therefore, when we say E.Job_Id we mean the Job_Id column from the Employees table with alias "E", and when we say M.Job_Id we mean the Job_Id column from the derived table with the alias "M".
So, when we include the Salary columns in the join, that means that the query will return the list of Employee names where their salary is equal to the Maximum for that job id.
Example:
Let's say you have the following sample data:
Name Job Id Salary
Fred Smith 5 100.00
Joe Jones 5 200.00
Bill Johnson 5 100.00
Mary Kelly 2 300.00
Sue Adams 2 250.00
Fern Dixon 2 200.00
When you run your query against this data, it will give you the following result set:
Jones, Joe ( 5 200.00 )
Kelly, Mary ( 2 300.00 )
I included the parenthesized columns just for ease of comparison.
I hope this clears up some of the confusion for you.
Good luck, and Happy Coding!
ok I'm almost there, just wanted to confirm this. what did the equal sign do in the code below aside from it joined the tables' job_id????
WHERE E.JOB_ID = M.JOB_ID
Nothing.
what data from both tables were joined?
Job_ID and Salary.
Keep in mind that "Salary" in the context of the derived table is just the alias given to the aggregated value MAX(Salary) in the sub-select that defines the derived table. Again, it's that alias concept...you can give a name to a column or table or view. It makes it easier to reference it elsewhere and in this case is required so you can reference a derived aggregate column. If you didn't do this you wouldn't be able to reference that column.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.