For executing the query- Instead of selecting whole line of query and clicking on Execute ,u can put semicolon(;)in the end of query and just press cntr+enter it will execute directly.
As for the first query, you could select an employee without a sub-query. Just apply TOP(1) in the select clause and order by salary in descending order. That would be more readable
Thanks for the quick test. I just finished a SQL course and you made me realize that I need more practice. I knew 65% but forgot details on JOIN functions.
Thanks for the video. As for PostgreSql is concerned, to get the 2nd highest salary the query would be select * from employee order by salary desc limit 1 offset 1; The query can be generalized to get 'r'th highest salary (provided r < no. of records in employee table) select * from employee order by salary desc limit 1 offset r;
I thought the same, but what if there's two employees with the same salary on top? I think his Query makes a bit more sense, but both should have been accepted
@@HannesSchmiderer Did not know about TIES, thanks for letting me know about that, but as Robert said, you would need to sub-query it, which could lead to performance issues
Thanks for the clear explanation. The last question (highest salary by department) will not work if the Sales department also has an employee with 80000 salary. In that case you will get 2 rows from the Sales department, and 1 row from the IT department. Better approach would be create another subquery with highest salary by department and join it back to the main table to be used as a filter.
Query #6 (highest salary for each department) is not complete. You have only two departments and your query returns correct result. In case many departments with a lot of employees in each of them, query may return records with employee from one department that have salary equal to max salary from another department, but not is max salary in his department. Need to add WHERE clause to subquery : select e.first_name, e.last_name, e.salary, d.department_name from employee e join department d on e.department_id = d.department_id where salary in (select max(salary) from employee where department_id = e.department_id --
Just to add a note about DB2 - the SQL would error out, when the GROUP BY is NOT having the column name already in the SELECT (i.e., it'll be grouped by ONLY by the columns which are selected)
The problem with using inner joins in these examples is that employees that don’t have a department assigned will be excluded from the results. Unless the intent is to only get employees that have A department assigned, use left join to include all the records. This could be resolved be setting a constraint on the department table such that employees must have a department assigned. Then an inner join works fine.
thank you. nice tutorial and also better to find the Nth highest salary by using level select max(salary) from employee where level = Nth connect by prior salary > salary; if u want 1st highest salary thn replace Nth with 1 if u want 2nd highest salary thn replace Nth with 2 ....................
Yea the last example only works on your small set. That will pull any employee, from any department, with a salary that matches the highest salary in any department. If Joe is 65th in Sales but makes the same as the CEO, Joe shows up in the results. Could debate the rankings but I think that’s close enough to pass those questions. Nice work!
Yes if two employees in different departments earned the same and one was the highest earner in their department but the other was not then they'd both show as well as the true highest earners. I don't think this is good work at all, it shows a lack of understanding which results in errors the coder will not expect or be able to fix.
@@PCSExponent No the way to solve it is to inner join to a subquery with a window rank function joining by dep, emp and rank by salary desc and then a where clause rank = 1.
@@PCSExponent Something like this: select e.first_name, e.last_name, e.salary, d.department_name from (select e.*, d.*, rank() over (partition by e.department_id order by e.salary desc) salary_rank from employee e inner join department d on (e.department_id = e.department_Id)) where salary_rank = 1;
Question 4 Ans Select First_Name, Last_Name, Salary, Department_Name from employee Inner JOIN Department on employee.Department_Id = Department.Department_Id Order by Salary desc limit 1;
Finally code interview video where I can understand them. He’s actually showing relevant interview questions too. Legit had 5 companies give me interview questions like this
@@MsSabBieber * is subject to schema changes, which includes things like column order, name changes etc... Easiest example Insert into (column_a, column_b) Select * from some_table If some_table has 2 columns (of matching data types) you are ok. However, let's say you add a new column to some_table. You have now broken that SQL code, this can be a big problem in production environments which may have 100's or even 1000's of stored procedures.
Good one, never used dense_rank before in my queries. Thanks. Only thing is, alias is required for inner query. Select * from ( Select employee_id,first_name,last_name,salary, dense_rank () over ( order by salary desc) ranking from employee) as e1 Where e1.ranking =2
You might want to change it to SELECT DISTINCT in the derived table or change the top level select to SELECT TOP(1) since if there could be multiple people tied for the second highest salary, and they would have the same dense rank.
If you really want to impress on an interview when they ask about nth highest salary (assuming it's not just the 2nd highest salary) you would use SQL ranking functions. For instance to get 9th highest salary : Select * from (Select row_number() over(order by Salary desc) as rownumb,* from [Salarytable]) as x where rownumb = 9 to get between 5th and 11th salary same thing just change the end : Select * from (Select row_number() over(order by Salary desc) as rownumb,* from [Salarytable]) as x where rownumb between 5 and 11
indeed, is there any advantage to using these nested queries? I would use the MAX() formula on the first select and join department table followed by a last groupby department number.
same question asked me the interviewer in 2015. My answer: I sorted the salary in ascending order and search max salary and again search max salary excluding the max salary. Interviewer smiled at me, give me a blank paper and said: please write down the code . My nervousness got high as Everest top.
Sorting probably isn't the fastest way to go about this, that query will waste computing power trying to organize all the salaries in their respective place, finding max value is faster and consumes less resources, if you only care for the top 2 places you just query using MAX, and for second highest you query for max WHERE NOT IN max. this will return the highest value excluding the truly highest value, so 2nd highest basically. and you don't need to order the other hundreds or thousands of employees by salary.
you can also write in SAS like this PROC SORT DATA=EMPLOYEE OUT=EMPLOYEE1 ; BY DESCENDING SALARY; RUN; you can get highest , second highest and so on.....
Wow, you are a great teacher! Your pace is easy to follow and you take time to explain every single step. Perfect for both novice and intermediate query writers. I've been a DBA for a little while and I actually didn't know how to extract the 2nd highest salary, so thanks for fine-tuning my logical thinking! Looking forward to more advanced stuff from you.
Would it be wrong on the first question to answer as: Select * from employee Order by Salary desc Limit 1 I'm just curious if that would be frowned upon during and interview or if it's just another valid solution
Good video. There is a problem with last query which uses a group by on department. If there are duplicate salary values you may end up selecting the wrong employee and department record
another solution for 2nd highest salary: "SELECT salary FROM employee ORDER BY salary DESEC LIMIT 1 OFFSET 1" it will first order the salary attribute(column) then skip the 1st which is highest one and display the 2nd highest
Not ideal... what if salary #1 is 60k, #2 is 60k also, then #3 is 50k. If you offset 1, you are only returning the next line, which is the same value of 60k. You need to use dense_rank
select salary from employee order by salary desc limit 1,1 for 2nd highest I think select salary from employee order by salary desc limit 2,1 for 3rd highest
Not ideal... what if salary #1 is 60k, #2 is 60k also, then #3 is 50k. If you offset 1,1 for the second highest value, you are only returning the next line, which is the same value of 60k. You need to use dense_rank
It makes sense to add limit 1 to the first query, for the case where several employees may have the same highest salary. The third task may have such solution (I'm not sure about efficiency, but should be fine if we have sorted index): select * from ( select * from employee order by salary desc limit 2 ) r order by salary asc limit 1
Trying to get familiar with SQL and I enjoyed the video and most of the comments below. This is one of the better video's for someone like me...Beginner....I could truly follow and understand
Thank you so much for sharing this content with us. I found the walk-through of the Queries , very helpful! If I can ask for a bit of advise to those who are proficient with SQL server: For someone like me, I have a Bachelors degree but not in computer science. I also have no prior IT experience. What would be your advise for me in regards to trying to get more training and get into the DBA field? I took a SQL server developer class last summer, and i absolutely loved it. Never thought i would be interested in the computer science field, but i am. I have been self-study for the past couple months. Any suggestions would be helpful. If i should take another class somewhere(preferably without going back to college), or if i should take some exams to get certification? Thanks :)
for the question find the second highest salary we can also use the query: select top 1 * from ( select top 2 SALARY from Employees_test order by Salary desc )S order by Salary
Well for the second question we could try this.. Select * from employee where salary= (Select min(salary) from (select * from employee order by salary desc limit 2) as Top); This code actually returns Nth highest . Just change the desc limit to the nth digit. Everybody seemed to care about finding the nth heighest. I was thinking.. well what if you were asked to find the 5th highest??
@@AKHILESHKUMAR-nk2rk This might work Select * from employee where salary= (Select min(salary) from (select distinct(salary) as salary from employee order by salary desc limit 2) as Top);
it is trick question. the question want you to find the employee with highest salary, but if there are two or more with same highest salary LIMIT 1 will fail
I'm using MySQL, but for the third one I would do : SELECT salary FROM employee ORDER BY salary DESC LIMIT 1,1; It sorts the table in descending order by salary and grabs the second row.
Looking at the comments, I see many-many 'best ways' to execute the queries. And from my experience, there are many--many ways to execute the questions as queries - some ways 'better' than others (for performance [which is the only gauge??]).... Obviously, the tutorial was meant to be just that--a tutorial to answer basic SQL questions using a small set of data to query. From there it would be up the 'subjective' judgement' of the interviewer to determine if the 'best way' for each question has been developed during the interview......
Great value for a fresh TSQL developer. If want to move a step forward and get a serious job try getting MAX values using LEFT JOINS. Of course try to avoid sub-queries wherever possible unless you use Query Hints. Good luck!
Hi... Great video! However, I think that for an interview question the second highest salary could be better expressed as below. Your solution satisfies only the 2nd highest. It fails the test for any "N"th highest salary. SOLUTION: select salary from employees e where (select count(distinct salary) from employees where salary > e.salary) = 1 /*(n-1)*/ -------------------------------or------------------------------ select sal from (select sal, rownum position from (select distinct (salary) sal from employees e order by 1 desc)) where position = 2 /*n*/
It's a sample interview question, if there is a more robust solution or a solution that shows a more advanced understanding if T-SQL that might impress interviewer and improve your chances of being hired, therefore it's "better".
OMG, got an interview for monday morning, applying for data analysis position, SQL is a preferred and not a must, but still trying to learn as much as possible on this weekend. Wish me luck
@@azfarbakht2167 I failed, they never reached me out for the second interview. However I think I got rejected because non demostrable experience, no projects neither certificates, I'll pay for an online SQL, MySQL, Oracle certificate and I'll have to work on something myself to prove my skills whenever a new chance shows off
I love it. I just signed up to get in a SQL class. The test I took looked like algebra. This makes more sense. I think I can do this . I just need to learn to think In query speak. Pray for me son 🙏.
To make it a bit more challenging “choose best way” to do each. Sargable vs non sargable, different beast when table has 700 vs 700MM rows, with sub queries, CTEs, Fn calls, Outer Apply, #temp table joins, sql link joins, and all the other fun stuff.
#1 - Does not work. It should return one record. Without TOP 1 it might return more than one if several employees have the same salary SELECT TOP 1 * FROM employee ORDER BY salary DESC #3 - Use variable. Otherwise the select have to look up employee table twice and that's inefficient. DECLARE @s INT; SELECT TOP 2 @s = salary FROM employee GROUP BY salary ORDER BY salary DESC; SELECT @s -- or how ever the result is used; Explanation: It will return two highest salaries, but only the last (the second) result remains in the variable, because the record set is ordered (in similar case without ordering records would be returned in arbitrary order, however the database squirts them out). #5 - Weirdly worded, but I assume they want the name and the department of the highest payed employee. Like #1, the video's solution may return more than one record SELECT TOP 1 e.first_name, e.last_name, e.salary, d.department_name FROM employee e INNER JOIN department d ON d.department_id = e.department_id ORDER BY e.salary DESC; #6 - No. SELECT d.department_name, e.first_name, e.last_name, e.salary FROM department d CROSS APPLY ( SELECT TOP 1 * FROM employee WHERE department_id = d.department_id ORDER BY salary DESC ) e; Explanation: List all departments (because we want them all), and then find the employee with the highest salary for each of them in CROSS APPLY sub-query (using the method from #1). If there might be departments without employees, use OUTER APPLY instead. I dont have SQL Management Studio at hand so there might be typos.
@@cryogeneric wrong. You can select multiple Values into a Variable, the Variable just holds the last Value of the Select.. so in his case the 2nd highest Salary, because of the order by descending. I testet it in Microsoft SQL Server Management Studio with a SQL Server.
great input, however I think #1 from the video is more appropriate in general. if you have multiple employees have the same salary that happens to be the max, you should be interested in all those employees and not just choose 1 as though that employee is the only person with the max salary. thank you anyway :)
Thank you, I knew somebody would catch this. I would not hire somebody who wrote the first query from the video. Especially when top 1 is so easy and performs much better
@@BobRadu Really? What if you have 2 (or more) people with the same salary? (730k in this case): you'd only see one. Also if this query is recurrent (reporting for example) you could add an Index for Salary and now Max is optimized to use TOP as well, so it's basically the same but with one you're getting the full picture, the other just 1 person max. Do your own tests and check the actual Query Plan.
Not true. "Not in" can handle multiple values, where not equals only one. In this case a max returns only one value, so not equal would be fine too. If the sub query might return more values, you need IN/NOT IN
Not true. 'Not In' behind the scene doing 'Not Equal' comparison with each value. 'Not In' work with multiple values where as 'Not Equal ' work with single values to compare.
for second question - Max second highest salary can be achieved by using less than as well. select max(salary) from employee where salary < (select max(salary) from employee)
You can also use row_number and a cte, but the person doesn't account for duplicate salaries at the top. You would need to rank the records with another field such as years employed, etc.
@@charlesbyrneShowComments4all I would stay away from functions that are available only in sql server or some other RDMS's and use only pure sql. For the second highest salary I would go with this: select max(salary) from employees where salary(select max(salary) from employees) Simple, clean and it works everywhere.
The last Select is not right. When you have a onther coworker in Sales with 80000 Salary you will become a resultset with three rows. You have to make a subselect with max(salary) and department and join them to the employee. Sorry for my english!
For getting 2nd highest sal: Can we write like this 1) Select Max(Salary) from Employee where Salary < (select Max(Salary) from Employee ) 2) Select salary from Employee where salary = ( Select distinct Salary from Employee where Rownum = 1 order by desc) Please correct me if I'm wrong, I'm just learning
@@thoms1986 Maybe both. I've been doing SQL coding professionally for 20+ years and I don't do job interviews because I own my own business. I agree the questions were easy, but it was still fun to play along and imagine interviewing for an entry level SQL job.
@@shaikkhaja8984 SELECT * FROM employee ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY would work, but that makes assumptions on how you want ties treated, and doesn't have a discriminator in case of a tie. Since the question was "return employee record with max salary", I consider this a better answer. If the question was "return the employee record(s) with max salary", then you would need a different solution, but that wasn't the question. In addition to his answer possibly returning multiple records, he has no discriminator either, so ties will be returned in a random/unpredictable order, which is never good. At the very least a discriminator like employee_id should be added so the order is predictable and repeatable.
Might a year late to reply to this post, but top 2 highest earners are earning the same salary, your SQL statement will return incorrect answer. Just want to point it out so other people who are reading the comment don't get mislead by the answer.
![CENTRAL CEE - GEN Z LUV [VERTICAL VIDEO]](http://i.ytimg.com/vi/VIbidJm9q34/mqdefault.jpg)
For executing the query- Instead of selecting whole line of query and clicking on Execute ,u can put semicolon(;)in the end of query and just press cntr+enter it will execute directly.
As for the first query, you could select an employee without a sub-query. Just apply TOP(1) in the select clause and order by salary in descending order. That would be more readable
That may not work if two or more employee has the same salary as highest salary.
@@crdave1988 what if we apply 'Distinct' as well, this might work
@@unboxingsillystuffs4920 I am not getting your idea. Can u share more?
@@crdave1988 select distinct top(1) salary from employee
I think it is just another way of doing it. That is the flexibility of programming.
Thanks for the quick test. I just finished a SQL course and you made me realize that I need more practice. I knew 65% but forgot details on JOIN functions.
Thanks for the video.
As for PostgreSql is concerned, to get the 2nd highest salary the query would be
select * from employee order by salary desc limit 1 offset 1;
The query can be generalized to get 'r'th highest salary (provided r < no. of records in employee table)
select * from employee order by salary desc limit 1 offset r;
If i may say sth, the code you provided returns the 'r+1'th highest salary...right? Not the 'r'th
Query #1 - SELECT * FROM employee ORDER BY salary DESC LIMIT 1.
I thought the same, but what if there's two employees with the same salary on top? I think his Query makes a bit more sense, but both should have been accepted
@@carloramundo9013 SQL-Server: ... TOP 1 WITH TIES ...
subqueries are slower because they actually run 2 queries, so they should only be used on small data sets...
@@HannesSchmiderer Did not know about TIES, thanks for letting me know about that, but as Robert said, you would need to sub-query it, which could lead to performance issues
@@carloramundo9013 No subquery needed here: SELECT TOP (1) WITH TIES * FROM employee ORDER BY salary DESC
Thanks for the clear explanation. The last question (highest salary by department) will not work if the Sales department also has an employee with 80000 salary. In that case you will get 2 rows from the Sales department, and 1 row from the IT department. Better approach would be create another subquery with highest salary by department and join it back to the main table to be used as a filter.
"So, what did you learn from this demonstration?" "That a job in Sales is better than a job in I.T."
SiriusGD haha you are right
Haha fools
Lol
Less job security included.
probably pays better too.
Query #6 (highest salary for each department) is not complete. You have only two departments and your query returns correct result.
In case many departments with a lot of employees in each of them, query may return records with employee from one department that have salary equal to max salary from another department, but not is max salary in his department.
Need to add WHERE clause to subquery
:
select e.first_name, e.last_name, e.salary, d.department_name
from employee e join department d on e.department_id = d.department_id
where salary in (select max(salary) from employee
where department_id = e.department_id --
That is correct, I was about to write a similar note. I would use Row_Number(), but your way is more elegant
Even group by not required after adding where condition
Its Working efficiently buti didnt understand the concept how it worked. Can you please explain.
group by is required to get single record for a department.
Just to add a note about DB2 - the SQL would error out, when the GROUP BY is NOT having the column name already in the SELECT (i.e., it'll be grouped by ONLY by the columns which are selected)
The problem with using inner joins in these examples is that employees that don’t have a department assigned will be excluded from the results. Unless the intent is to only get employees that have A department assigned, use left join to include all the records. This could be resolved be setting a constraint on the department table such that employees must have a department assigned. Then an inner join works fine.
730K? Can you query stacys phone number?
Dallas IT Nerds
555-she-ahoe
What will you do with that...
hhahahhahaha
What she sellin
hahhahhhhah
Wonderful tutorial on SQL interview questions: very helpful keep it up
thank you. nice tutorial
and also better to find the Nth highest salary by using level
select max(salary) from employee
where level = Nth connect by prior salary > salary;
if u want 1st highest salary thn replace Nth with 1
if u want 2nd highest salary thn replace Nth with 2
....................
Select * from (select DENSE_RANK() OVER (ORDER BY Salary desc) AS SN,* from Employee
) as NewTable where SN =N
Your answer is correct since it will give the result for nth salary.
Or use limit
Awesome sauce. You make it so much clearer than these other websites. THE light bulb finally came on, thank you. I so get it.....
Yea the last example only works on your small set. That will pull any employee, from any department, with a salary that matches the highest salary in any department. If Joe is 65th in Sales but makes the same as the CEO, Joe shows up in the results.
Could debate the rankings but I think that’s close enough to pass those questions. Nice work!
Yes if two employees in different departments earned the same and one was the highest earner in their department but the other was not then they'd both show as well as the true highest earners. I don't think this is good work at all, it shows a lack of understanding which results in errors the coder will not expect or be able to fix.
@@glennbabic5954 I agree. The way to solve this question is using a loop, which is not often done in SQL.
@@PCSExponent No the way to solve it is to inner join to a subquery with a window rank function joining by dep, emp and rank by salary desc and then a where clause rank = 1.
@@glennbabic5954 Yep, that's simpler than a loop. Thank you for the reply.
@@PCSExponent Something like this: select e.first_name, e.last_name, e.salary, d.department_name
from (select e.*, d.*, rank() over (partition by e.department_id order by e.salary desc) salary_rank
from employee e inner join department d on (e.department_id = e.department_Id)) where salary_rank = 1;
Question 4
Ans
Select First_Name, Last_Name, Salary, Department_Name
from employee
Inner JOIN Department on employee.Department_Id = Department.Department_Id
Order by Salary desc
limit 1;
Awesome! I just had my interview today and the first two questions were ask. Thank you very much..
Finally code interview video where I can understand them. He’s actually showing relevant interview questions too. Legit had 5 companies give me interview questions like this
Did the companies ask for specific SQL certification? Also which one would you recommend?
it is very easy 😊
2nd highest salary: select Max(Salary) from Employee where salary < select Max(Salary) from Employee. THis makes more intuitive to me.
and add limit 1 to the end
aboalhassan you shouldn't need the limit 1, as max() will only return 1 value
youre right thank u
It's very important
This way is the simplest...select (Max(Salary)-1) from Employee
Great examples and very intuitive and simple explanation. Thank you!
I subbed! Thanks bro! really loved the vid. keep up the good work!
Great Video Man.
Can we have more video interview questions like this one?
These are entry level questions, most complex ones are those that require to retrieve hierarchical data , indexes, rank etc..
@Guru H Please do a video and explain some complex SQL questions with answers. Thanks.
Excellent !! This is one of the easiest and simplest explanation I have seen .
awesome, useful video. thanks
These sql queries are very helpful for interview.
Awesome
Never use * in production! Make sure you tell them that in the interview.
Also some of your queries will be very inefficient on large datasets.
please explain why not use * in production? also, what is production? sorry, very beginner here!
@@MsSabBieber * is subject to schema changes, which includes things like column order, name changes etc...
Easiest example
Insert into (column_a, column_b)
Select * from some_table
If some_table has 2 columns (of matching data types) you are ok. However, let's say you add a new column to some_table. You have now broken that SQL code, this can be a big problem in production environments which may have 100's or even 1000's of stored procedures.
Cole Willison that makes sense, thank you!
Every helpful, thanks for sharing!!!
Good one. Helped me to understand these queries in quick and simple way.
Great stuff! I just subscribed! You did a really good job! Let's help these folks learn and grow! This is REAL RUclips content!
for 2nd higest salary
Select * from
(
Select salary, dense_rank () over ( order by salary desc) ranking from employee
)
Where ranking =2
Good one, never used dense_rank before in my queries. Thanks.
Only thing is, alias is required for inner query.
Select * from
( Select employee_id,first_name,last_name,salary, dense_rank () over ( order by salary desc) ranking from employee) as e1
Where e1.ranking =2
You might want to change it to SELECT DISTINCT in the derived table or change the top level select to SELECT TOP(1) since if there could be multiple people tied for the second highest salary, and they would have the same dense rank.
this is correct!
wen we use distinct i think this is when we need to return unique values.
what if we are looking for 3rd or 4th highest salary?
Nice job made it easier to Understand
Excelent video. Great Job. Thanks
If you really want to impress on an interview when they ask about nth highest salary (assuming it's not just the 2nd highest salary) you would use SQL ranking functions.
For instance to get 9th highest salary :
Select * from (Select row_number() over(order by Salary desc)
as rownumb,* from [Salarytable]) as x where rownumb = 9
to get between 5th and 11th salary same thing just change the end :
Select * from (Select row_number() over(order by Salary desc)
as rownumb,* from [Salarytable]) as x where rownumb between 5 and 11
Or much more simply:
ORDER BY salary DESC LIMIT n-1, x+1;
when you want the nth salary through to n+xth salary.
Your 6th query is incorrect. For example if sales department has an employee with 80000 salary the query will return one more record.
indeed, is there any advantage to using these nested queries? I would use the MAX() formula on the first select and join department table followed by a last groupby department number.
@@fabioneves9224 I would use window function Max salary partition by department and only select records where salary = the window Max
@@bobbygia1198 Yeah easier
Fantastic SQL video by clear explanation ,Thank you
Thanks for uploading it.
Really helpful for people who want to brush up there SQL skills before an interview.
Thanks for this video the exact thing I was looking for,this is very helpful :) I just subbed you
same question asked me the interviewer in 2015. My answer: I sorted the salary in ascending order and search max salary and again search max salary excluding the max salary.
Interviewer smiled at me, give me a blank paper and said: please write down the code .
My nervousness got high as Everest top.
Sorting probably isn't the fastest way to go about this, that query will waste computing power trying to organize all the salaries in their respective place, finding max value is faster and consumes less resources, if you only care for the top 2 places you just query using MAX, and for second highest you query for max WHERE NOT IN max. this will return the highest value excluding the truly highest value, so 2nd highest basically. and you don't need to order the other hundreds or thousands of employees by salary.
you can also write in SAS like this PROC SORT DATA=EMPLOYEE OUT=EMPLOYEE1 ; BY DESCENDING SALARY; RUN; you can get highest , second highest and so on.....
DURING INTERVIEW HOW MUCH CGPA MATTERS TO GET A GOOD JOB
great video!!
nice work..keep it up
Great vdo... Simple to understand.. Thnzlot... I just subscribed .
Wow, you are a great teacher!
Your pace is easy to follow and you take time to explain every single step. Perfect for both novice and intermediate query writers.
I've been a DBA for a little while and I actually didn't know how to extract the 2nd highest salary, so thanks for fine-tuning my logical thinking!
Looking forward to more advanced stuff from you.
Would it be wrong on the first question to answer as:
Select * from employee
Order by Salary desc
Limit 1
I'm just curious if that would be frowned upon during and interview or if it's just another valid solution
Not exactly wrong but, You might want to return all employees that have the highest salary.
This was a great refresher for an interview I have tomorrow
Great ! Love your explanation very much on point - Thanks
Good video. There is a problem with last query which uses a group by on department. If there are duplicate salary values you may end up selecting the wrong employee and department record
Came here to say that. Thank you!
#1: select top 1 * from employee order by salary desc
This is a great video, keep up the good work.
Thanks for sharing the video, Nicely explained.
Thank you
create table department (
department_id INT PRIMARY KEY,
department_name VARCHAR(255)
);
create table employee (
employee_id BIGINT(20) PRIMARY KEY,
first_name VARCHAR(255),
last_name VARCHAR(255),
gender VARCHAR(1),
position VARCHAR(255),
department_id INT,
salary INT,
FOREIGN KEY (department_id) REFERENCES department(department_id)
);
insert into department values (1, "IT");
insert into department values (2, "Sales");
insert into employee values (2002, "Super", "Man", "M", "Tester", 1, 75000);
insert into employee values (2003, "Jessica", "Liyers", "F", "Architect", 1, 60000);
insert into employee values (2004, "Bonnie", "Adams", "F", "Project Manager", 1, 80000);
insert into employee values (2005, "James", "Madison", "M", "Software Developer", 1, 55000);
insert into employee values (2006, "Michael", "Greenback", "M", "Sales Assistant", 2, 85000);
insert into employee values (2007, "Leslie", "Peters", "F", "Sales Engineer", 2, 76000);
insert into employee values (2008, "Max", "Powers", "M", "Sales Representative", 2, 59000);
insert into employee values (2009, "Stacy", "Jacobs", "F", "Sales Manager", 2, 730000);
insert into employee values (2010, "John", "Henery", "M", "Sales Director", 2, 90000);
Well explained and easy to understand beginners 👏👏
Indeed great video and excellent explanation
another solution for 2nd highest salary:
"SELECT salary FROM employee ORDER BY salary DESEC LIMIT 1 OFFSET 1"
it will first order the salary attribute(column) then skip the 1st which is highest one and display the 2nd highest
no it is not displaying second highest salary
Not ideal... what if salary #1 is 60k, #2 is 60k also, then #3 is 50k. If you offset 1, you are only returning the next line, which is the same value of 60k. You need to use dense_rank
bro u will fail in repeating values
Nice refresher. I tend to forget the specifics if I haven't been working on databases for a while.
True
Great Stuff, Clear explanation....
Good explain and i am understand everything questions.
select salary from employee order by salary desc limit 1,1
for 2nd highest
I think
select salary from employee order by salary desc limit 2,1
for 3rd highest
Not ideal... what if salary #1 is 60k, #2 is 60k also, then #3 is 50k. If you offset 1,1 for the second highest value, you are only returning the next line, which is the same value of 60k. You need to use dense_rank
@@stanson5850 SELECT * FROM employees
GROUP BY salary
ORDER BY salary DESC
LIMIT 1,1
select * from employee order by salary desc offset 1 rows fetch next 1 rows only.
Limit is not applicable in this tutorial. He is using SQL Server.
It makes sense to add limit 1 to the first query, for the case where several employees may have the same highest salary.
The third task may have such solution (I'm not sure about efficiency, but should be fine if we have sorted index):
select * from (
select * from employee
order by salary desc limit 2
) r
order by salary asc limit 1
Table scan for the love of god. Dont do this.
easy to understand, informative. Thank you. watching in 2020
Trying to get familiar with SQL and I enjoyed the video and most of the comments below. This is one of the better video's for someone like me...Beginner....I could truly follow and understand
Thank you so much for sharing this content with us. I found the walk-through of the Queries , very helpful! If I can ask for a bit of advise to those who are proficient with SQL server: For someone like me, I have a Bachelors degree but not in computer science. I also have no prior IT experience. What would be your advise for me in regards to trying to get more training and get into the DBA field? I took a SQL server developer class last summer, and i absolutely loved it. Never thought i would be interested in the computer science field, but i am. I have been self-study for the past couple months. Any suggestions would be helpful. If i should take another class somewhere(preferably without going back to college), or if i should take some exams to get certification? Thanks :)
I would like recommend to go for Python :)
for the question find the second highest salary we can also use the query:
select top 1 * from
(
select top 2 SALARY from Employees_test order by Salary desc
)S order by Salary
2 table scans?......argh. Just dont do this ever.
Gr8 Explanantion...!!!
Very very good video!!! wah 👌
Well for the second question we could try this..
Select * from employee where salary= (Select min(salary) from
(select * from employee order by salary desc limit 2) as Top);
This code actually returns Nth highest . Just change the desc limit to the nth digit.
Everybody seemed to care about finding the nth heighest. I was thinking.. well what if you were asked to find the 5th highest??
this will fail in case of repeating values
@@AKHILESHKUMAR-nk2rk This might work
Select * from employee where salary= (Select min(salary) from
(select distinct(salary) as salary from employee order by salary desc limit 2) as Top);
Some questions:
For the first, is that more efficient than:
SELECT * FROM employee ORDER BY salary DESC LIMIT 1
?
it is trick question. the question want you to find the employee with highest salary, but if there are two or more with same highest salary LIMIT 1 will fail
Great information. Very straight forward.
very useful and educational video.
cheers
for 2nd I would go with:
SELECT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1
DISTINCT(salary) застрахует от дубликатов. Ведь может быть две одинаковые MAX(salary)
What if two people have the same salaries?
I'm using MySQL, but for the third one I would do :
SELECT salary FROM employee
ORDER BY salary DESC LIMIT 1,1;
It sorts the table in descending order by salary and grabs the second row.
You are great at giving instructions. Very good explanation. Thank you
Great video and good explanation.
Looking at the comments, I see many-many 'best ways' to execute the queries. And from my experience, there are many--many ways to execute the questions as queries - some ways 'better' than others (for performance [which is the only gauge??])....
Obviously, the tutorial was meant to be just that--a tutorial to answer basic SQL questions using a small set of data to query.
From there it would be up the 'subjective' judgement' of the interviewer to determine if the 'best way' for each question has been developed during the interview......
Thank you. its really useful and good explanation.
Great value for a fresh TSQL developer. If want to move a step forward and get a serious job try getting MAX values using LEFT JOINS. Of course try to avoid sub-queries wherever possible unless you use Query Hints. Good luck!
Thank you for the videos. I am adding to my knowledge
Hi... Great video!
However, I think that for an interview question the second highest salary could be better expressed as below.
Your solution satisfies only the 2nd highest. It fails the test for any "N"th highest salary.
SOLUTION:
select salary
from employees e
where (select count(distinct salary) from employees where salary > e.salary) = 1 /*(n-1)*/
-------------------------------or------------------------------
select sal
from (select sal, rownum position
from (select distinct (salary) sal from employees e order by 1 desc))
where position = 2 /*n*/
How do you get a better answer? He got the answer he sought. Problem solved.
It's a sample interview question, if there is a more robust solution or a solution that shows a more advanced understanding if T-SQL that might impress interviewer and improve your chances of being hired, therefore it's "better".
You could also use a rank.
@@johndrury agreed, also crafting a solution valid for any Nth salary shows the interviewer you really understand how to query
Suppose if salary having null value, then it returns in nth highest salary when u select 1st highest salary
OMG, got an interview for monday morning, applying for data analysis position, SQL is a preferred and not a must, but still trying to learn as much as possible on this weekend. Wish me luck
So how'd it go?
@@azfarbakht2167 I failed, they never reached me out for the second interview.
However I think I got rejected because non demostrable experience, no projects neither certificates, I'll pay for an online SQL, MySQL, Oracle certificate and I'll have to work on something myself to prove my skills whenever a new chance shows off
I love it. I just signed up to get in a SQL class. The test I took looked like algebra. This makes more sense. I think I can do this . I just need to learn to think In query speak. Pray for me son 🙏.
To make it a bit more challenging “choose best way” to do each. Sargable vs non sargable, different beast when table has 700 vs 700MM rows, with sub queries, CTEs, Fn calls, Outer Apply, #temp table joins, sql link joins, and all the other fun stuff.
#1 - Does not work. It should return one record. Without TOP 1 it might return more than one if several employees have the same salary
SELECT TOP 1 * FROM employee ORDER BY salary DESC
#3 - Use variable. Otherwise the select have to look up employee table twice and that's inefficient.
DECLARE @s INT;
SELECT TOP 2 @s = salary FROM employee GROUP BY salary ORDER BY salary DESC;
SELECT @s -- or how ever the result is used;
Explanation: It will return two highest salaries, but only the last (the second) result remains in the variable, because the record set is ordered (in similar case without ordering records would be returned in arbitrary order, however the database squirts them out).
#5 - Weirdly worded, but I assume they want the name and the department of the highest payed employee. Like #1, the video's solution may return more than one record
SELECT TOP 1 e.first_name, e.last_name, e.salary, d.department_name
FROM employee e
INNER JOIN department d ON d.department_id = e.department_id
ORDER BY e.salary DESC;
#6 - No.
SELECT d.department_name, e.first_name, e.last_name, e.salary
FROM department d
CROSS APPLY (
SELECT TOP 1 *
FROM employee
WHERE department_id = d.department_id
ORDER BY salary DESC
) e;
Explanation: List all departments (because we want them all), and then find the employee with the highest salary for each of them in CROSS APPLY sub-query (using the method from #1). If there might be departments without employees, use OUTER APPLY instead.
I dont have SQL Management Studio at hand so there might be typos.
You can't select multiple values into a variable as you did in the #3 example.
@@cryogeneric wrong. You can select multiple Values into a Variable, the Variable just holds the last Value of the Select.. so in his case the 2nd highest Salary, because of the order by descending. I testet it in Microsoft SQL Server Management Studio with a SQL Server.
great input, however I think #1 from the video is more appropriate in general. if you have multiple employees have the same salary that happens to be the max, you should be interested in all those employees and not just choose 1 as though that employee is the only person with the max salary. thank you anyway :)
Thank you, I knew somebody would catch this. I would not hire somebody who wrote the first query from the video. Especially when top 1 is so easy and performs much better
@@BobRadu Really? What if you have 2 (or more) people with the same salary? (730k in this case): you'd only see one. Also if this query is recurrent (reporting for example) you could add an Index for Salary and now Max is optimized to use TOP as well, so it's basically the same but with one you're getting the full picture, the other just 1 person max. Do your own tests and check the actual Query Plan.
For the record, "NOT IN" doesn't mean "NOT EQUAL", technically. 😉
Not true. "Not in" can handle multiple values, where not equals only one. In this case a max returns only one value, so not equal would be fine too. If the sub query might return more values, you need IN/NOT IN
Not true. 'Not In' behind the scene doing 'Not Equal' comparison with each value. 'Not In' work with multiple values where as 'Not Equal ' work with single values to compare.
Very well done! Thank you so much for making the video. Have a wonderful day!
Nice one mate! Easy to understand. Thanks.
For 2nd Highest Salary, We can also do
select salary FROM employee
ORDER BY salary DESC
LIMIT 1,1
Select top 1 also works
for second question - Max second highest salary can be achieved by using less than as well.
select max(salary) from employee where salary < (select max(salary) from employee)
uday nayak I was wondering about that, because when I write queries in access I use the less than or greater than in my script also.
Yes you can use with less than operator
You can also use row_number and a cte, but the person doesn't account for duplicate salaries at the top. You would need to rank the records with another field such as years employed, etc.
@@charlesbyrneShowComments4all I would stay away from functions that are available only in sql server or some other RDMS's and use only pure sql.
For the second highest salary I would go with this:
select max(salary) from employees
where salary(select max(salary) from employees)
Simple, clean and it works everywhere.
select salary from employee order by salary desc limit 1,1
for 2nd highest
select salary from employee order by salary desc limit 2,1
for 3rd highest
Clearly explained. Thank you!
very much helpful
Thanks a lot Sir.
really good explanation!
The last Select is not right. When you have a onther coworker in Sales with 80000 Salary you will become a resultset with three rows. You have to make a subselect with max(salary) and department and join them to the employee.
Sorry for my english!
Excellent. Verbally clear and so concise. I enjoyed learning and have full understanding as well. Well Done...
One of the best tutorials. Appreciate your time and effort. Well explained. Thank you!
For getting 2nd highest sal: Can we write like this
1) Select Max(Salary) from Employee where Salary < (select Max(Salary) from Employee )
2) Select salary from Employee where salary = ( Select distinct Salary from Employee where Rownum = 1 order by desc)
Please correct me if I'm wrong, I'm just learning
The 1st sql statement you mentioned will definitely get you the 2nd highest salary. I'm not too sure about using rownum yet.
how do we find all the details(row) of the employee who gets the 2nd highest salary
Hi, In your second query, using ORDER BY Clause in the inner select doesn't work , it is invalid
the first statement gets you all the salaries less than max salary. so wrong.
@@srikanthb903 No. Actually the first query returns the correct result. It is SELECT MAX(Salary) which returns only one value
The last query will fail when we have an employee in sales department with 80000 salary!
yes thats the point
Nice video really ok informative
Precise & adequate. Thanks alot :)
Well, I'm glad to know that if I had to interview for a job, I could ace the SQL interview questions. Nice video - keep it up.
Or the questions are too damn easy...
@@thoms1986 Maybe both. I've been doing SQL coding professionally for 20+ years and I don't do job interviews because I own my own business. I agree the questions were easy, but it was still fun to play along and imagine interviewing for an entry level SQL job.
Not very good answers to those questions. For example, the first query should have just been SELECT TOP 1 * FROM employee ORDER BY salary DESC.
What if they asked about third highest salary?
@@shaikkhaja8984 SELECT * FROM employee ORDER BY salary DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY would work, but that makes assumptions on how you want ties treated, and doesn't have a discriminator in case of a tie. Since the question was "return employee record with max salary", I consider this a better answer. If the question was "return the employee record(s) with max salary", then you would need a different solution, but that wasn't the question. In addition to his answer possibly returning multiple records, he has no discriminator either, so ties will be returned in a random/unpredictable order, which is never good. At the very least a discriminator like employee_id should be added so the order is predictable and repeatable.
Much more efficient, that's the way I would do it, avoid subquerys whenever possible
@@robertmckee9272 Thanks for the offset / fetch tip. Didn't know that!
Might a year late to reply to this post, but top 2 highest earners are earning the same salary, your SQL statement will return incorrect answer.
Just want to point it out so other people who are reading the comment don't get mislead by the answer.
This is my first SQL video and I learnt it easily....You taught me very quicky 😬 Thanks
That is great .... God bless you ... thank you very much