IQ15: 6 SQL Query Interview Questions

[@nik6920]

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

[@crdave1988]

That may not work if two or more employee has the same salary as highest salary.

[@unboxingsillystuffs4920]

@@crdave1988 what if we apply 'Distinct' as well, this might work

[@crdave1988]

@@unboxingsillystuffs4920 I am not getting your idea. Can u share more?

[@StoneVicarious]

@@crdave1988 select distinct top(1) salary from employee

[@EriaPinyi]

I think it is just another way of doing it. That is the flexibility of programming.

[@Salim_TravelVlog]

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.

[@mso4324]

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.

[@srinikethvydya806]

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;

[@bryanparis7779]

If i may say sth, the code you provided returns the 'r+1'th highest salary...right? Not the 'r'th

[@alpha3305]

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.

[@damienbates]

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.

[@mjrobins]

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!

[@glennbabic5954]

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]

@@glennbabic5954 I agree. The way to solve this question is using a loop, which is not often done in SQL.

[@glennbabic5954]

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

@@glennbabic5954 Yep, that's simpler than a loop. Thank you for the reply.

[@glennbabic5954]

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

[@MmmBopsPops]

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

[@carloramundo9013]

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]

@@carloramundo9013 SQL-Server: ... TOP 1 WITH TIES ...

[@robhunn4835]

subqueries are slower because they actually run 2 queries, so they should only be used on small data sets...

[@carloramundo9013]

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

[@HannesSchmiderer]

@@carloramundo9013 No subquery needed here: SELECT TOP (1) WITH TIES * FROM employee ORDER BY salary DESC

[@siriusgd4753]

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

[@jasonwong8315]

SiriusGD haha you are right

[@GiacaloneSalvatore]

Haha fools

[@atikhanovesy2152]

Lol

[@LF100-03]

Less job security included.

[@chad55009]

probably pays better too.

[@rvffrd6917]

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

[@maratgubaydullin8428]

That is correct, I was about to write a similar note. I would use Row_Number(), but your way is more elegant

[@saibhargavLanka21]

Even group by not required after adding where condition

[@mildtime1995]

Its Working efficiently buti didnt understand the concept how it worked. Can you please explain.

[@srikanthb903]

group by is required to get single record for a department.

[@thambithurai4115]

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)

[@SonnyWest87]

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

[@brendonoel1985]

Did the companies ask for specific SQL certification? Also which one would you recommend?

[@sureshpatra6384]

it is very easy 😊

[@ishwargoudar1851]

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

[@spicytuna08]

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

[@aboalhassan448]

and add limit 1 to the end

[@gginnj]

aboalhassan you shouldn't need the limit 1, as max() will only return 1 value

[@aboalhassan448]

youre right thank u

[@TechandArt]

It's very important

[@kstevens0915]

This way is the simplest...select (Max(Salary)-1) from Employee

[@DistantGlowingStar]

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

[@The-Right-is-Right]

@Guru H Please do a video and explain some complex SQL questions with answers. Thanks.

[@sujitkumarnayak101]

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

[@YogendraTamang]

Select * from (select DENSE_RANK() OVER (ORDER BY Salary desc) AS SN,* from Employee ) as NewTable where SN =N

[@sarikabiwalkar4037]

Your answer is correct since it will give the result for nth salary.

[@nikhilb3880]

Or use limit

[@ivanhaidarli7163]

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

[@cwillison94]

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.

[@MsSabBieber]

please explain why not use * in production? also, what is production? sorry, very beginner here!

[@cwillison94]

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

[@MsSabBieber]

Cole Willison that makes sense, thank you!

[@ciruzzi7]

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

[@amitpatelpatel144]

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.

[@MrGoDuck]

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.

[@ravieco]

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

[@noorhuda-xd2pu]

DURING INTERVIEW HOW MUCH CGPA MATTERS TO GET A GOOD JOB

[@sunilk.c5367]

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

[@waqaracheema]

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

[@sunaxes]

Came here to say that. Thank you!

[@mihiretumisganu7170]

Fantastic SQL video by clear explanation ,Thank you

[@TechSolutionDesk]

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

[@RoseOginga2]

Great ! Love your explanation very much on point - Thanks

[@superkutta]

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

[@madhaviravoori6466]

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

[@johndrury]

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.

[@Osta165]

this is correct!

[@Osta165]

wen we use distinct i think this is when we need to return unique values.

[@13abesssssssssssssss]

what if we are looking for 3rd or 4th highest salary?

[@monday6740]

6:20 - select avg(salary) from employee where salary = ( select salary from employee where rownum = 2 group by salary order by salary desc ) ; ? The AVG function is used to group all records into 1, the salary returned would be equal anyway

[@shreyansjain1853]

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;

[@cassondrad2280]

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

[@dallasitnerds2321]

730K? Can you query stacys phone number?

[@johnmadsen37]

Dallas IT Nerds 555-she-ahoe

[@PriyankaSharma-dy8dg]

What will you do with that...

[@kirakira4ever]

hhahahhahaha

[@vatsaakhil]

What she sellin

[@pickler_pickler]

hahhahhhhah

[@kuelexx5451]

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

[@vmir88]

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

[@avinashgogineni4u]

Well explained and easy to understand beginners 👏👏

[@chazsmith20]

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

[@PCSExponent]

Or much more simply: ORDER BY salary DESC LIMIT n-1, x+1; when you want the nth salary through to n+xth salary.

[@vadiyarhemadri1903]

Super learnt lot of knowledge from you thank you so much

[@kevinclarke3485]

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.

[@harrymary100]

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

[@nikhilvaidya587]

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

[@fabioneves9224]

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.

[@bobbygia1198]

@@fabioneves9224 I would use window function Max salary partition by department and only select records where salary = the window Max

[@FreeLevant-b1d]

@@bobbygia1198 Yeah easier

[@beingyourself9824]

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

[@stanson5850]

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

[@Boomeringo]

@@stanson5850 SELECT * FROM employees GROUP BY salary ORDER BY salary DESC LIMIT 1,1

[@reylencatungal4593]

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.

[@steenteudt]

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

[@avonstar8893]

This is very good. Thanks for doing this. You can practice on SQL Fiddle. Paste this into the schema window and then click on "Build Schema" CREATE TABLE Employee(employee_id int IDENTITY(2002,1) PRIMARY KEY, first_name varchar(25), last_name varchar(25), gender char(1), position varchar(30), department_id int, salary int); INSERT INTO Employee (first_name, last_name, gender, position, department_id, salary) VALUES('Super', 'Man', 'M', 'Tester', 1, 75000), ('Jessica', 'Liyers', 'F', 'Architect', 1, 60000), ('Bonnie', 'Adams', 'F', 'Project Manager', 1, 80000), ('James', 'Maddison', 'M', 'Software Developer', 1, 55000), ('Michael', 'Greenback', 'M', 'Sales Assistant', 2, 85000), ('Leslie', 'Peter', 'F', 'Sales Engineer', 2, 76000), ('Max', 'Powers', 'M', 'Sales Representative', 2, 59000), ('Stacy', 'Jacobs', 'F', 'Sales Manager', 2, 730000), ('John', 'Henery', 'M', 'Sales Director', 2, 90000) CREATE TABLE Department(department_id int IDENTITY(1,1) PRIMARY KEY, department_name varchar(25)); INSERT INTO Department(department_name) VALUES ('IT'), ('SALES')

[@cassondrad2280]

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

[@auspicio9720]

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

[@kwabenaodameakomeah3374]

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 will fail in case of repeating values

[@unboxingsillystuffs4920]

​@@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);

[@robertablack8363]

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

[@ALIRAZA-cp4fs]

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

[@artipathak6656]

no it is not displaying second highest salary

[@stanson5850]

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

[@AKHILESHKUMAR-nk2rk]

bro u will fail in repeating values

[@micky992]

Nice one mate! Easy to understand. Thanks.

[@erickha6232]

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

[@DrunkenEngineer]

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

[@loam]

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

[@nataliatimakova9446]

DISTINCT(salary) застрахует от дубликатов. Ведь может быть две одинаковые MAX(salary)

[@RayZde]

What if two people have the same salaries?

[@gloriabukachi1]

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

[@purnapp4012]

These sql queries are very helpful for interview.

[@TechandArt]

Awesome

[@lucianocorrea4823]

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

[@darioa2827]

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]

So how'd it go?

[@darioa2827]

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

[@TheCodingInterview]

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.

[@udaynayak4788]

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)

[@thequeenreadsTV]

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.

[@shubhampatil1557]

Yes you can use with less than operator

[@charlesbyrneShowComments4all]

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.

[@bogdanionut7768]

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

[@beingyourself9824]

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

[@vvstiya]

One of the best tutorials. Appreciate your time and effort. Well explained. Thank you!

[@seporokey]

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.

[@suciugianni900]

Excelent video. Great Job. Thanks

[@vitaliysamofal2866]

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

[@MDevion]

Table scan for the love of god. Dont do this.

[@SpooningTreesap86]

I was able to get most of them before watching ahead to your solutions, thank you that was great practice

[@johnspencer772]

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

[@g.s.1757]

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!

[@nenenartey4266]

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

[@ChrisSmithFW]

How do you get a better answer? He got the answer he sought. Problem solved.

[@johndrury]

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

[@kevinsiedenburg4955]

You could also use a rank.

[@cgalon6781]

@@johndrury agreed, also crafting a solution valid for any Nth salary shows the interviewer you really understand how to query

[@munnaharun2431]

Suppose if salary having null value, then it returns in nth highest salary when u select 1st highest salary

[@bretcj7]

Only suggestion is to use CTE when you can to clean up the efficiency of the multiple select queries.

[@karanjawalkar4768]

For finding 2nd highest salary: select min(salary) from (select salary from employee order by salary desc limit 2). This can be easy solution

[@shrikant301991]

Impressed bro

[@stanson5850]

Not ideal... what if salary #1 is 60k, #2 is 60k also. Your search would only list the first two salaries, not the 1st most and 2nd most salary. You need to use dense_rank

[@akhilesh_39]

It's a very useful video. Thanks

[@this.channel]

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

[@atikhanovesy2152]

True

[@MohamedSuleiman]

That is great .... God bless you ... thank you very much

[@owoeyegbenga8657]

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

[@axlderks4022]

Select top 1 also works

[@cryptotec7887]

Very nice - basic and easy to see the sql code work

[@maheshj01]

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

[@frankzelazko]

very useful and educational video. cheers

[@MK-je7kz]

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

You can't select multiple values into a variable as you did in the #3 example.

[@zettwire]

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

[@kojo5946]

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

[@BobRadu]

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

[@nicolash810]

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

[@Mbibas1]

Very good explanation thank you this is very helpful

[@prithalove]

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

[@MDevion]

2 table scans?......argh. Just dont do this ever.

[@primetime2392]

Excellent explanations man! Learned a lot.

[@bobDotJS]

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

[@RayZde]

Not exactly wrong but, You might want to return all employees that have the highest salary.

[@Tech_Simplivity]

Wow thanku bro I am in final year of b.tech and you help me a lot for interview preparation

[@sucreation1154]

miljyga to batana hm v try karenge

[@mayanksharma8518]

The last query will fail when we have an employee in sales department with 80000 salary!

[@AKHILESHKUMAR-nk2rk]

yes thats the point

[@drtareq9256]

you guys explain very nicely ..go ahead

[@joseassumpcao624]

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

[@aminshoman1]

there is limit/offest in the fucking SQL-SERVER

[@AKHILESHKUMAR-nk2rk]

wht about when we have repeating values

[@TheSchmed]

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.

[@tropicalseedlings]

#1 no sub-query needed. select top 1 * from employee order by salary desc; The same rule for MySQL and Oracle accordingly.

[@BradBartram]

Good video, but I would only mention that unless you specifically know the tech stack being used and on the road map, it would be best to define solutions that are not tech dependent. For example, use solutions that adhere to good cross-platform SQL standard without relying too heavily on engine specific functions and implementations.

[@williamsquires3070]

Exactly, or your cutesy ass will be doing a lot of cutesy recoding! 😏

[@mohammadarifulla797]

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

[@TheCodingInterview]

The 1st sql statement you mentioned will definitely get you the 2nd highest salary. I'm not too sure about using rownum yet.

[@nirmalwewitavidana2592]

how do we find all the details(row) of the employee who gets the 2nd highest salary

[@madhaviravoori6466]

Hi, In your second query, using ORDER BY Clause in the inner select doesn't work , it is invalid

[@srikanthb903]

the first statement gets you all the salaries less than max salary. so wrong.

[@mehranofff]

@@srikanthb903 No. Actually the first query returns the correct result. It is SELECT MAX(Salary) which returns only one value

[@xoeriphenburg4413]

This is such an excellent tutorial because the author speaks each word of code. I was able to listen to this and build a desk at the same time, and I caught almost every word!

[@aitch749]

/* select 2nd highest salary in employee table --- alternate method */ select Min(salary) from (select distinct salary from employee order by salary desc limit 2)

[@ggjoseph8839]

select * from emp aa where 3-1=(select count(*) from emp where salary>aa.salary)

[@learnthealphabet5561]

I mean it's very inefficient cause you use 2 queries to do that. For large databases you would spend a lot of computational resources. You can do it with only one query. SELECT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1;

[@刘晓虎-k4m]

also can use row_number()

[@RutgerOlthuis]

@@刘晓虎-k4m if I remember correctly, row_number is done before filtering. So you'll get the wrong record back.

[@patrickproctor3462]

@@learnthealphabet5561 It's not (very) inefficient. The query optimizer runs the first half of the query only on the data brought into memory by the second half. On 10,000 repetitions loading from an NVMe SSD, the difference in execution times is barely 10 microseconds. It's practically noise.

[@shakib651]

Precise & adequate. Thanks alot :)

[@learn365days]

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

[@erwinekkel9676]

Sub query should be avoided at all costs agreed?!

[@natavesta2010]

Very good video indeed! Thank you! ....Made me thinking to switch my job from IT to Sales :)

[@AAA-bo1uo]

Some questions: For the first, is that more efficient than: SELECT * FROM employee ORDER BY salary DESC LIMIT 1 ?

[@jk2l]

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

[@surendravishwakarma5244]

Good explain and i am understand everything questions.

[@dragawam2225]

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.

[@thoms1986]

Or the questions are too damn easy...

[@dragawam2225]

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

[@GoddamnAxl]

these are like the kind of queries you learn in your first sql learning session...

[@FIXProtocol]

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

[@umakasibatla6389]

Thanks for sharing the video, Nicely explained.

[@harditsingh8291]

the 2nd highest salary can be better solved with a correlated query instead of sub query as it is salable. Select * from employee e1 where 2 = (select count(distinct(salary)) from employee e2 where e2.salrary >= e1.salary)

[@robby0716]

or: SELECT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 2; (in this case it would select the 3rd highest salary, you can mess around with the OFFSET to pick whichever salary you want).

[@ChrisSmithFW]

We all know there's more than 1 way to skin a cat. His objective was clearly stated. He achieved it. Mission accomplished.

[@miamac63]

Well Said.

[@tom_nqld]

You have answered my question what if you're asked to display the 3rd highest salary, thanks!

[@acedeno23]

Hardit Singh We need programmers @acebooks.xyz!

[@gplus46]

Great information. Very straight forward.

[@theno1.tribitfan507]

/* select 2nd highest salary in employee table */ -- easier approach and more readable SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);

[@atikhanovesy2152]

Jenius

[@usertuserb7057]

verry goooooood

[@adityabalki1909]

What if when you want 10th or Nth Highest salary ? You might need to increase the sub queries . This is not much practical in real life.

[@patrickproctor3462]

I would like to point out that operators like between are unique to SQL Server and have no true peer in other database engines. Also, it appears to be new to SQL Server 2012, and there will doubtlessly be legacy environments where you can't use the flashy tools, so for some of these, you should have multiple answers prepared and be able to explain drawbacks in performance or verbosity if you're going for an intermediate/senior developer/programmer position, even if SQL won't be your primary work.