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.

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 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.

Awesome sauce. You make it so much clearer than these other websites. THE light bulb finally came on, thank you. I so get it.....

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.

Wonderful tutorial on SQL interview questions: very helpful keep it up

Awesome! I just had my interview today and the first two questions were ask. Thank you very much..

Great Video Man. Can we have more video interview questions like this one?

730K? Can you query stacys phone number?

Excellent !! This is one of the easiest and simplest explanation I have seen .

"So, what did you learn from this demonstration?" "That a job in Sales is better than a job in I.T."

Query #1 - SELECT * FROM employee ORDER BY salary DESC LIMIT 1.

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 --

Great examples and very intuitive and simple explanation. Thank you!

Good one. Helped me to understand these queries in quick and simple way.

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;

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

Thanks for uploading it. Really helpful for people who want to brush up there SQL skills before an interview.

This was a great refresher for an interview I have tomorrow

These are entry level questions, most complex ones are those that require to retrieve hierarchical data , indexes, rank etc..

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 ....................

Nice job made it easier to Understand

This is a great video, keep up the good work.

Thanks for this video the exact thing I was looking for,this is very helpful :) I just subbed you

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;

Great vdo... Simple to understand.. Thnzlot... I just subscribed .

Excelent video. Great Job. Thanks

These sql queries are very helpful for interview.

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!

Great ! Love your explanation very much on point - Thanks

great video!! nice work..keep it up

#1: select top 1 * from employee order by salary desc

Great stuff! I just subscribed! You did a really good job! Let's help these folks learn and grow! This is REAL KZfaq content!

awesome, useful video. thanks

I subbed! Thanks bro! really loved the vid. keep up the good work!

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

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.

Fantastic SQL video by clear explanation ,Thank you

easy to understand, informative. Thank you. watching in 2020

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 :)

Your 6th query is incorrect. For example if sales department has an employee with 80000 salary the query will return one more record.

Even though in the video I addressed how to find the 2nd highest salary. I did see some discussion in the comments about finding the nth highest salary. In order to find the nth highest salary see below: SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP N salary FROM Employee ORDER BY salary DESC ) AS nthSalary ORDER BY salary Explanation of the above query: 1.) First I select the TOP 1 salary which is supposed to specify the number of records to be returned. 2.)Next in my From, inside I have a query containing the DISTINCT keyword which removes duplicates. That query will also take note of the salary you want returned. 3.) Finally the outer query will select the top most salary based on the value of N that you pass in Hope this makes sense. I plan on making more videos like this in the future related to SQL, where I'll make sure to take time and take a more comprehensive approach. Thanks guys for all your feedback.

Well explained and easy to understand beginners 👏👏

Thank you

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.

You are great at giving instructions. Very good explanation. Thank you

Gr8 Explanantion...!!!

2nd highest salary: select Max(Salary) from Employee where salary < select Max(Salary) from Employee. THis makes more intuitive to me.

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

Awesome... keep it up.

Every helpful, thanks for sharing!!!

Nice refresher. I tend to forget the specifics if I haven't been working on databases for a while.

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.

Thanks for sharing the video, Nicely explained.

Very very good video!!! wah 👌

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

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

Very well done! Thank you so much for making the video. Have a wonderful day!

Great video and good explanation.

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??

Thank you. its really useful and good explanation.

Great Stuff, Clear explanation....

Good explain and i am understand everything questions.

for 2nd higest salary Select * from ( Select salary, dense_rank () over ( order by salary desc) ranking from employee ) Where ranking =2

Some questions: For the first, is that more efficient than: SELECT * FROM employee ORDER BY salary DESC 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

Indeed great video and excellent explanation

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*/

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

Excellent. Verbally clear and so concise. I enjoyed learning and have full understanding as well. Well Done...

very useful and educational video. cheers

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......

really good explanation!

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 🙏.

Great information. Very straight forward.

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

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

Super learnt lot of knowledge from you thank you so much

Nice one mate! Easy to understand. Thanks.

Sir can you please answer the questions without using sub query. That would be great.

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)

awesome nice.........gud video

Excellent video :)

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

for 2nd I would go with: SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1

Thank you for the videos. I am adding to my knowledge

Nice video really ok informative

For the record, "NOT IN" doesn't mean "NOT EQUAL", technically. 😉

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!

This is my first SQL video and I learnt it easily....You taught me very quicky 😬 Thanks

Great stuff👏🏻👏🏻👍🏻

Hey, what will be the query for 3rd or 4th highest salary then?

For 2nd Highest Salary, We can also do select salary FROM employee ORDER BY salary DESC LIMIT 1,1

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!

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.

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

Question 2: For Nth highest salary go SELECT salary FROM (SELECT salary FROM employee ORDER BY salary DESC LIMIT N) ORDER BY salary ASC LIMIT 1

So very helpful thanks a ton!!!! :) expecting some more tutorials as this

Precise & adequate. Thanks alot :)