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.
@shaikkhaja89843 жыл бұрын
What if they asked about third highest salary?
@robertmckee92723 жыл бұрын
@@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.
@Shoikan713 жыл бұрын
Much more efficient, that's the way I would do it, avoid subquerys whenever possible
@Evan-zj5mt3 жыл бұрын
@@robertmckee9272 Thanks for the offset / fetch tip. Didn't know that!
@jiejenn2 жыл бұрын
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.
@nikhilvaidya5876 жыл бұрын
Your 6th query is incorrect. For example if sales department has an employee with 80000 salary the query will return one more record.
@fabioneves92243 жыл бұрын
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.
@bobbygia11983 жыл бұрын
@@fabioneves9224 I would use window function Max salary partition by department and only select records where salary = the window Max
@charbelbejjani55413 жыл бұрын
@@bobbygia1198 Yeah easier
@Salim_TravelVlog5 жыл бұрын
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.
@nik69204 жыл бұрын
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
@crdave19883 жыл бұрын
That may not work if two or more employee has the same salary as highest salary.
@unboxingsillystuffs49203 жыл бұрын
@@crdave1988 what if we apply 'Distinct' as well, this might work
@crdave19883 жыл бұрын
@@unboxingsillystuffs4920 I am not getting your idea. Can u share more?
@StoneVicarious2 жыл бұрын
@@crdave1988 select distinct top(1) salary from employee
@EriaPinyi2 жыл бұрын
I think it is just another way of doing it. That is the flexibility of programming.
@vmir883 жыл бұрын
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);
@MmmBopsPops5 жыл бұрын
Query #1 - SELECT * FROM employee ORDER BY salary DESC LIMIT 1.
@carloramundo90135 жыл бұрын
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
@HannesSchmiderer5 жыл бұрын
@@carloramundo9013 SQL-Server: ... TOP 1 WITH TIES ...
@robhunn48355 жыл бұрын
subqueries are slower because they actually run 2 queries, so they should only be used on small data sets...
@carloramundo90135 жыл бұрын
@@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
@HannesSchmiderer5 жыл бұрын
@@carloramundo9013 No subquery needed here: SELECT TOP (1) WITH TIES * FROM employee ORDER BY salary DESC
@damienbates3 жыл бұрын
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.
@siriusgd47535 жыл бұрын
"So, what did you learn from this demonstration?" "That a job in Sales is better than a job in I.T."
@jasonwong83154 жыл бұрын
SiriusGD haha you are right
@GiacaloneSalvatore4 жыл бұрын
Haha fools
@atikhanovesy21524 жыл бұрын
Lol
@LF100-034 жыл бұрын
Less job security included.
@chad550094 жыл бұрын
probably pays better too.
@rvffrd69177 жыл бұрын
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 --
@maratgubaydullin84287 жыл бұрын
That is correct, I was about to write a similar note. I would use Row_Number(), but your way is more elegant
@saibhargavLanka216 жыл бұрын
Even group by not required after adding where condition
@mildtime81466 жыл бұрын
Its Working efficiently buti didnt understand the concept how it worked. Can you please explain.
@srikanthb9036 жыл бұрын
group by is required to get single record for a department.
@thambithurai41156 жыл бұрын
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)
@mso43242 жыл бұрын
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.
@shreyansjain18534 жыл бұрын
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;
@alpha33057 жыл бұрын
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.
@cwillison945 жыл бұрын
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.
@MsSabBieber4 жыл бұрын
please explain why not use * in production? also, what is production? sorry, very beginner here!
@cwillison944 жыл бұрын
@@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.
@MsSabBieber4 жыл бұрын
Cole Willison that makes sense, thank you!
@srinikethvydya8064 жыл бұрын
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
@mjrobins5 жыл бұрын
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!
@glennbabic59542 жыл бұрын
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.
@PCSExponent2 жыл бұрын
@@glennbabic5954 I agree. The way to solve this question is using a loop, which is not often done in SQL.
@glennbabic59542 жыл бұрын
@@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.
@PCSExponent2 жыл бұрын
@@glennbabic5954 Yep, that's simpler than a loop. Thank you for the reply.
@glennbabic59542 жыл бұрын
@@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;
@waqaracheema4 жыл бұрын
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!
@bobDotJS5 жыл бұрын
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
@manit774 жыл бұрын
Not exactly wrong but, You might want to return all employees that have the highest salary.
@AdnanSabah5 жыл бұрын
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!
@DistantGlowingStar4 жыл бұрын
These are entry level questions, most complex ones are those that require to retrieve hierarchical data , indexes, rank etc..
@The-Right-is-Right2 жыл бұрын
@Guru H Please do a video and explain some complex SQL questions with answers. Thanks.
@amitpatelpatel1447 жыл бұрын
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.
@MrGoDuck7 жыл бұрын
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.
@ravieco6 жыл бұрын
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-xd2pu5 жыл бұрын
DURING INTERVIEW HOW MUCH CGPA MATTERS TO GET A GOOD JOB
@Ddx_43 жыл бұрын
Hi, first question - It is not bad, but from my personal experience I can say, that it is not optimal either. The thing is using the MAX function. This is ok in small databases, but you dont want to use in-line functions unless you have to. So what you should do is this: SELECT TOP 1 FROM Employee ORDER by salary DESC. This will return first line ordered by salary. We dont have to use function in here and we shouldnt do it, because it is unnecesary load for the database. I can say from personal experience that some companies are really sensitive to this. Hope this will help you guys.
@mayanksharma85186 жыл бұрын
The last query will fail when we have an employee in sales department with 80000 salary!
@AKHILESHKUMAR-nk2rk4 жыл бұрын
yes thats the point
@TechSolutionDesk6 жыл бұрын
Awesome! I just had my interview today and the first two questions were ask. Thank you very much..
@SonnyWest874 жыл бұрын
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
@brendonoel19854 жыл бұрын
Did the companies ask for specific SQL certification? Also which one would you recommend?
@sureshpatra63844 жыл бұрын
it is very easy 😊
@salmanel-farsi37444 жыл бұрын
The last query is incorrect because as long as an employee has one of the max salaries for *any* of the departments, then the query reports that employee. What you want is to restrict each employee by the department to which s/he belongs. (SELECT ......WHERE (e.salary, e.department.id) IN (SELECT MAX(x.salary), x.department_id FROM department x GROUP BY x.department_id) --OR-- you can write what is called a correlated subquery as follows SELECT ... WHERE e.salary IN (SELECT MAX(x.salary) FROM department x WHERE x.department_id = e.department_id).
@johnspencer7726 жыл бұрын
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......
@jusdare2do6902 жыл бұрын
Answer for last query in your video is wrong (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 its not a max salary in his department.) and can be solved as below: select e.firstname, e.lastname, e.salary, d.deptname from (select max(salary) as maxsal, deptid from employee group by deptid) maxsaldept join employee e on maxsaldept.deptid=e.deptid and maxsaldept.maxsal=e.salary join department d on e.deptid=d.deptid
@spicytuna086 жыл бұрын
2nd highest salary: select Max(Salary) from Employee where salary < select Max(Salary) from Employee. THis makes more intuitive to me.
@aboalhassan4484 жыл бұрын
and add limit 1 to the end
@gginnj4 жыл бұрын
aboalhassan you shouldn't need the limit 1, as max() will only return 1 value
@aboalhassan4484 жыл бұрын
youre right thank u
@TechandArt3 жыл бұрын
It's very important
@kstevens09153 жыл бұрын
This way is the simplest...select (Max(Salary)-1) from Employee
@AramisNasirianfar Жыл бұрын
the challenging ones are all wrong, last question is wrong if the highest salary in IT somehow becomes the second highest in Marketing for example and it will return 3 columns, 2 for Marketing and 1 for IT because it is basically looking for a number IN a list and if it matches, it will use it. also for the question about range, you been asked to get the range from data, not to pick a range and show it. anyway, nice video, thanks for making it
@steenteudt5 жыл бұрын
#1: select top 1 * from employee order by salary desc
@erickha62325 жыл бұрын
Great Video Man. Can we have more video interview questions like this one?
@darioa28273 жыл бұрын
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
@azfarbakht21673 жыл бұрын
So how'd it go?
@darioa28273 жыл бұрын
@@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
@MDevion4 жыл бұрын
If you want to blow the interviewer away, for the first question use SELECT TOP(1) WITH TIES * FROM Employee ORDER BY Salary DESC. With ties is the most unknown clause in SQL You would get a straight pass from me. Also this question could be handle be analytic function like DENSE_RANK which also would answer the second question. Also anybody answering with LIMIT would fail. There could be 2 people having the highest salary.
@owoeyegbenga86573 жыл бұрын
For 2nd Highest Salary, We can also do select salary FROM employee ORDER BY salary DESC LIMIT 1,1
@axlderks40223 жыл бұрын
Select top 1 also works
@shreyansjain18534 жыл бұрын
Question 2 Answer SELECT Salary as "2ndHighest_Salary" from employee Where Salary (select MAX(Salary) from employee) order by salary desc Limit 1;
@AAA-bo1uo5 жыл бұрын
Some questions: For the first, is that more efficient than: SELECT * FROM employee ORDER BY salary DESC LIMIT 1 ?
@jk2l5 жыл бұрын
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
@chazsmith205 жыл бұрын
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
@PCSExponent2 жыл бұрын
Or much more simply: ORDER BY salary DESC LIMIT n-1, x+1; when you want the nth salary through to n+xth salary.
@ALIRAZA-cp4fs5 жыл бұрын
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
@artipathak66565 жыл бұрын
no it is not displaying second highest salary
@stanson58504 жыл бұрын
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-nk2rk4 жыл бұрын
bro u will fail in repeating values
@aamir_mohammad3 жыл бұрын
Hey , Last query won't work if we have more departments and suppose IT department max salary is 7000 and HR department max salary is 8000 but in HR department there is employee who have the salary 7000 thousand then it will return 2 value of HR department ( one for 8000 and one for 7000) as the subquery is based on salary columns. I guess the correct way to get this through analytical function . Appreciate your work!. Thanks,
@superkutta6 жыл бұрын
for 2nd higest salary Select * from ( Select salary, dense_rank () over ( order by salary desc) ranking from employee ) Where ranking =2
@madhaviravoori64666 жыл бұрын
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
@johndrury6 жыл бұрын
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.
@Osta1656 жыл бұрын
this is correct!
@Osta1656 жыл бұрын
wen we use distinct i think this is when we need to return unique values.
@13abesssssssssssssss5 жыл бұрын
what if we are looking for 3rd or 4th highest salary?
@foqy_rao5 жыл бұрын
i had an interview today and two questions were from this video, lol i couldn't answer
@ibrahimolayiwola24475 жыл бұрын
Oh. How did you go about it
@foqy_rao5 жыл бұрын
@@ibrahimolayiwola2447 overall interview gone well but these two database questions just ruined it,anyways i hope they'd call me
@foqy_rao5 жыл бұрын
@@ibrahimolayiwola2447 interviewer asked me to find second highest salary from the table
@ibrahimolayiwola24475 жыл бұрын
@@foqy_rao ohhh. Best of luck.
@foqy_rao5 жыл бұрын
@@ibrahimolayiwola2447 thanks man
@ras_tesfa51485 жыл бұрын
For the record, "NOT IN" doesn't mean "NOT EQUAL", technically. 😉
@RutgerOlthuis4 жыл бұрын
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
@deepakpandey94064 жыл бұрын
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.
@jp2162 жыл бұрын
For question with second salary, I was thinking SELECT * FROM employee ORDER BY salary DESC LIMIT 2; And so we would get the 2 records with highest salaries.
@kwabenaodameakomeah33745 жыл бұрын
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-nk2rk4 жыл бұрын
this will fail in case of repeating values
@unboxingsillystuffs49203 жыл бұрын
@@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);
@ukas1505 жыл бұрын
For #2 SELECT salary FROM employee ORDER BY salary DESC LIMIT 1, 1 - basicly is for nth where you just have to change LIMIT N - 1 , 1 N-nth
@MK-je7kz6 жыл бұрын
#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.
@cryogeneric5 жыл бұрын
You can't select multiple values into a variable as you did in the #3 example.
@zettwire5 жыл бұрын
@@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.
@kojo59465 жыл бұрын
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 :)
@BobRadu5 жыл бұрын
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
@nicolash8105 жыл бұрын
@@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.
@kevinmartin77604 жыл бұрын
Some of the questions are ill-defined if several employees have the same salary.
@beingyourself98245 жыл бұрын
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
@stanson58504 жыл бұрын
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
@Boomeringo4 жыл бұрын
@@stanson5850 SELECT * FROM employees GROUP BY salary ORDER BY salary DESC LIMIT 1,1
@reylencatungal45933 жыл бұрын
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.
@johnallred31245 жыл бұрын
“Not in” is not the same as “not equal to.” “Not in” means a value does not exist within an array. “Not equal to” means one discrete value is not the same as another discrete value.
@theno1.tribitfan5074 жыл бұрын
/* select 2nd highest salary in employee table */ -- easier approach and more readable SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);
@atikhanovesy21524 жыл бұрын
Jenius
@usertuserb70574 жыл бұрын
verry goooooood
@adityabalki19094 жыл бұрын
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.
@patrickproctor34624 жыл бұрын
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.
@joseassumpcao6245 жыл бұрын
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
@aminshoman15 жыл бұрын
there is limit/offest in the fucking SQL-SERVER
@AKHILESHKUMAR-nk2rk4 жыл бұрын
wht about when we have repeating values
@prithalove6 жыл бұрын
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
@MDevion4 жыл бұрын
2 table scans?......argh. Just dont do this ever.
@loam5 жыл бұрын
for 2nd I would go with: SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1
@nataliatimakova94465 жыл бұрын
DISTINCT(salary) застрахует от дубликатов. Ведь может быть две одинаковые MAX(salary)
@manit774 жыл бұрын
What if two people have the same salaries?
@ivanhaidarli71634 жыл бұрын
easy to understand, informative. Thank you. watching in 2020
@mandiramitra72035 жыл бұрын
Hey, what will be the query for 3rd or 4th highest salary then?
@devinkemp6625 жыл бұрын
I was wondering the same thing?
@bobtony60364 жыл бұрын
13 years experience, if any interviewer actually asked me for a specific query, i honestly would just laugh in their face.
@sujitkumarnayak1017 жыл бұрын
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 ....................
@YogendraTamang7 жыл бұрын
Select * from (select DENSE_RANK() OVER (ORDER BY Salary desc) AS SN,* from Employee ) as NewTable where SN =N
@sarikabiwalkar40377 жыл бұрын
Your answer is correct since it will give the result for nth salary.
@nikhilb38805 жыл бұрын
Or use limit
@synkuk3 жыл бұрын
Watched the vid .. expected them to be more difficult :D
@sandeepcsonawane30296 жыл бұрын
Your last query is incorrect man, consider following - E1 D1 2000 E2 D1 3000 E3 D2 2000 Your query will return all three records which is incorrect.
@aloksaxena79055 жыл бұрын
WITH CTE AS (SELECT E.EmpId,E.EmpName,E.Salary, e.DeptId, RANK() OVER(ORDER BY E.Salary desc) 'RankNumber' FROM Employee E ) select * from CTE where RankNumber = 1 GO WITH CTE1 AS (SELECT E.EmpId,E.EmpName,E.Salary, e.DeptId, DENSE_RANK() OVER( PARTITION BY E.DeptId ORDER BY E.Salary desc) 'RankNumber' FROM Employee E ) select * from CTE1 where RankNumber = 1
@DonOB4 жыл бұрын
To crush your next SQL interview use this Udemy course Link: www.udemy.com/course/the-complete-sql-master-bootcamp-sql-database-beginner-to-mysql-expert/?referralCode=626274F46FBDB4D10D0B Title: 2020 Complete SQL Master Bootcamp: MySQL Beginner to Expert
@learn365days6 жыл бұрын
Sir can you please answer the questions without using sub query. That would be great.
@erwinekkel96765 жыл бұрын
Sub query should be avoided at all costs agreed?!
@GracefulTalesPluto2 жыл бұрын
I have a question: when you said Where Salary NOT IN (Max(Salary) you got the second highest? how, the 3rd highest and the 4th highest are also NOT In max (salary)? do you understand my doubt? so when I say Where Salary NOT IN (Max(Salary) then I would expect SQL to pull all rows that do not have a max(salary)... I am confused!
@0xc0ffee_4 жыл бұрын
Your solutions are all pretty bad to be honest.
@1flovera4 жыл бұрын
why? In general it is ok,
@0xc0ffee_4 жыл бұрын
@@1flovera They are ok for really small databases. But there's no reason to write such queries that are more complex than needed when there are better and more scalable solutions.
@stefanmiletic64244 жыл бұрын
Query3 SELECT * FROM employee order by salary desc limit 1,1; for mysql. Can anyone confirm? I'm still learning.
@mohammadarifulla7977 жыл бұрын
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
@TheCodingInterview7 жыл бұрын
The 1st sql statement you mentioned will definitely get you the 2nd highest salary. I'm not too sure about using rownum yet.
@nirmalwewitavidana25926 жыл бұрын
how do we find all the details(row) of the employee who gets the 2nd highest salary
@madhaviravoori64666 жыл бұрын
Hi, In your second query, using ORDER BY Clause in the inner select doesn't work , it is invalid
@srikanthb9036 жыл бұрын
the first statement gets you all the salaries less than max salary. so wrong.
@mehranofff5 жыл бұрын
@@srikanthb903 No. Actually the first query returns the correct result. It is SELECT MAX(Salary) which returns only one value
@unvunb7157 Жыл бұрын
How do you select the second-highest record?
@renepirolt7 жыл бұрын
Great tutorial, but the last example is NOT quite correct! It only works because the data-set is small and all salaries are unique. Once you add employees that have the same salaries in other departments the query results will not satisfy the conditions. For example, the query would return an additional record if you add another person into department ‘Sales’ with a salary of $80,000. To correct this, you would also need to add the Department to your query criteria.
@anuradhadontha62277 жыл бұрын
Hi Rene, if you add department_id to inner query it does not work. since inner should return single record values. I tried did not work. if you do inner query alone it will work. select department_id, max(salary) from employees group by department_id; below query works select e.first_name, e.last_name, max(e.salary), d.department_name from employees e inner join department d on e.department_id = d.department_id group by d.department_name;
@kartikchauhan54986 жыл бұрын
Wow, you just made me fall in love with you :D. Great solution btw.
@bombayboys1006 жыл бұрын
How did the query work mam? As in the end group by d.department_name alone would not work as in select we also have e.first_name and e.last_name. So for both firstname and lastname there will be an error of "Not a group by function." Please help. Thanks a lot, Kunder Akshay
@madhaviravoori64666 жыл бұрын
His query for last example works for large data too. Select employee_id,first_name,last_name,salary,d.dept_id,Dept_Name FROM employee e Join dept d on e.dept_id = d.dept_id WHERE e.salary IN (SELECT MAX(e1.salary) FROM employee e1 Group BY e1.Dept_id) ORDER BY 5 I tried with 4 depts, this was my results: employee_id first_name last_name salary dept_id Dept_Name 2013 Jenny Winter 400000.00 1 IT 2010 Stacy Jacobs 350000.00 2 Sales 2005 Vinny Ravoori 65000.00 3 Accounting 2012 Bob Raliegy 300000.00 4 Legal
@lusibitch27316 жыл бұрын
Why the guy from the video uses subquery to show the Maximum salary? It could be easily done by putting it into first row where the SELECT clause is, as MAX(e.salary) column without having to write a subquery.
@theulkaz3 жыл бұрын
SELECT * FROM KZbin WHERE KZbin_GOOD_INFORMATION = SELECT MAX(KZbin_GOOD_INFORMATION) FROM KZbin RESULT: The Coding Interview
@FIXProtocol7 жыл бұрын
Great stuff! I just subscribed! You did a really good job! Let's help these folks learn and grow! This is REAL KZbin content!
@cubiciel.27044 жыл бұрын
Hi! Could anyone explain why can't we just use select * from employee where salary=Max(salary)? Many thanks
@poseidon27354 жыл бұрын
I was also thinking about it
@poseidon27354 жыл бұрын
If you gets this answer kindly share with me...👍
@kerwin23234 жыл бұрын
Query #6: SELECT first_name, last_name, MAX(salary), department_name FROM employee NATURAL JOIN department GROUP BY department_name;
@ssarkar29964 жыл бұрын
The last query is not correct. It worked for this small dataset. But I can easily come up with a dataset where this query would give wrong results.
@seporokey6 жыл бұрын
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.
@Doshvari5 жыл бұрын
There is a problem at your last example If a employee from team 'a' salary was 2000$ is maximum salary And other employees in oder in team 'b' salary is 2000 but he's not the maximum salary By your code he be showed at final result
@tropicalseedlings5 жыл бұрын
#1 no sub-query needed. select top 1 * from employee order by salary desc; The same rule for MySQL and Oracle accordingly.
@dhanasekars36226 жыл бұрын
I have one question here. If 3rd question would be like return 5th max salary of Employee table then how could we make the query for that?
@kuldeep8637 жыл бұрын
Last Query is not accurate. What if the max salary of IT department is also present in Sales Department. That would also come in the output which is not expected. :)
@srikanthb9036 жыл бұрын
no it groups on department basis and then picks the highest salary.
@turn12106 жыл бұрын
Kuldeep is correct, the video example only works because he has a dataset that is tailored for this video. The subquery picks the highest salary in each department, but there is no link to the sub-queried department in the main query. If the second highest earner in department 1 earns the same as the top salary in department 2, they will both be returned.
@rahulajith21105 жыл бұрын
Sorry to say that those questions were utter blunder. They were not properly framed. And the sixth query is totally wrong.
@cassondrad22807 жыл бұрын
Awesome sauce. You make it so much clearer than these other websites. THE light bulb finally came on, thank you. I so get it.....
@rahulsethione6 жыл бұрын
How about the query below to return second highest salary: SELECT salary from EMPLOYEE ORDER BY salary DESC OFFSET 1 LIMIT 1;
@mohammedsardar37794 жыл бұрын
Why getting below with 11 G Oracle Version. Any idea ? ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: Error at Line: 31 Column: 12
@SachinGupta-km3no6 жыл бұрын
You can access record at any position depending on their salary by using following query select * from employee order by salary desc limit x,y ; where x is for the starting position 0 for 1st , 1 for 2nd and so on. y is number of elements if you want to access only one element simply write 1. select * from employee order by salary desc limit 0 , 1 ; This is for max salary.
@Eds_Salcedo Жыл бұрын
Thanks!
@bretcj76 жыл бұрын
Only suggestion is to use CTE when you can to clean up the efficiency of the multiple select queries.
@maheshjamdade15 жыл бұрын
Thanks for this video the exact thing I was looking for,this is very helpful :) I just subbed you
@shanedetsch6 жыл бұрын
Select 2nd highest salary in the employee table alternative query => SELECT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1;
@MrBoomBoom2255 жыл бұрын
This - seems alot more practical. The method used in the video seemed like it would only be good for finding the 2nd highest value. Wouldn't work for finding 3rd, 4th etc.
@Cube85 жыл бұрын
Query 1: select top 1 * from employee order by salary desc
@enix41415 жыл бұрын
or Select * from employee ODER BY Salary DESC LIMIT 1; For second highest LIMIT 1 OFFSET 1;
@nmlokman85385 жыл бұрын
What if there are two employees with highest salary ? 🤔
@monday67405 жыл бұрын
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
@nenenartey42667 жыл бұрын
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*/
@ChrisSmithFW7 жыл бұрын
How do you get a better answer? He got the answer he sought. Problem solved.
@johndrury6 жыл бұрын
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".
@kevinsiedenburg49556 жыл бұрын
You could also use a rank.
@cgalon67815 жыл бұрын
@@johndrury agreed, also crafting a solution valid for any Nth salary shows the interviewer you really understand how to query
@munnaharun24315 жыл бұрын
Suppose if salary having null value, then it returns in nth highest salary when u select 1st highest salary
@Callum29D5 жыл бұрын
Your third question is ok, but I think you should have spoken about the order in which sql server will apply the predicate. From and Where are applied first, then after that the select is performed. This will stop people getting confused about selecting something that looks like it's being filtered out...
@purnapp40126 жыл бұрын
These sql queries are very helpful for interview.
@TechandArt3 жыл бұрын
Awesome
@pradeepanumala35516 жыл бұрын
Nice tutorial. The last query is incorrect. If there was another employee in Sales with salary 80000, it would have fetched that employee too. The correction to the last query is the sub query should be corrected to - ( select max(salary) from employee e2 where e.department_id=e2.department_id).
@karanjawalkar47686 жыл бұрын
For finding 2nd highest salary: select min(salary) from (select salary from employee order by salary desc limit 2). This can be easy solution
@shrikant3019915 жыл бұрын
Impressed bro
@stanson58504 жыл бұрын
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
@fuckthesystemm995 жыл бұрын
I wouldn't thought that Samuel L. Jackson can write query code
@lifecoachjess9676 жыл бұрын
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 :)
@Ittherapist6656 Жыл бұрын
I would like recommend to go for Python :)
@diamondcode20804 жыл бұрын
the last query is not right because I will get the employee in Sales that has salary 80000 in the result set ,you can not use IN clause , I think it should be: SELECT e.first_name,e.last_name,e.salary,d.department_name FROM Employee e INNER JOIN Department d ON e.department_id = d.department_id INNER JOIN (SELECT e.department_id,MAX(e.salary) AS salary FROM Employee e GROUP BY e.department_id) AS t ON e.department_id = t.department_id AND e.salary = t.salary
@udaynayak47887 жыл бұрын
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)
@thequeenreads26 жыл бұрын
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.
@shubhampatil15576 жыл бұрын
Yes you can use with less than operator
@charlesbyrneShowComments4all6 жыл бұрын
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.
@bogdanionut77685 жыл бұрын
@@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.
@beingyourself98245 жыл бұрын
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
@preetichouksey74603 жыл бұрын
/*get the max salary*/ SELECT max(salary) FROM public."Employee" /* Second higest salary*/ select salary from public."Employee" where salary in (SELECT salary FROM public."Employee" order by salary DESC limit 2) order by salary ASC limit 1 SELECT salary FROM public."Employee" order by salary ASC /*select a range of employee based on id*/ select * from public."Employee" where employee_id between 2004 and 2006 /* Select employee name ,highest sals and depat name*/ select e.*, d.department_name from public."Employee" e , public."Departments" d where e.department_id = d.department_id /*Max salary by each department */ select e.department_id,max(salary) from public."Employee" e group by e.department_id /* Select employee name ,highest sals by each depat name*/ select e.*, d.department_name from public."Employee" e , public."Departments" d where e.department_id = d.department_id and e.salary in (select max(salary) from public."Employee" e group by e.department_id)
@Cam-p2z6 жыл бұрын
Couldn't you just use (for the first question) ORDER BY Salary DESC LIMIT 1:
@Rarius5 жыл бұрын
While that will work in some SQL dialects, it won't in SQLServer
@GreatLakesDrifta4 жыл бұрын
@@Rarius Yeah, in sql server, you would follow up the SELECT statement with the TOP directive, but yeah, same concept.
@timlevey3 жыл бұрын
This is absolutely horrible as an SQL standard! DO NOT DO THIS EXCEPT IN THE SIMPLIST OF USE CASES NEEDING ONLY 1 ROW WITH THE MAX VALUE!!! If you read the Microsoft Document on MAX() you will quickly find "It is nondeterministic when specified with the OVER and ORDER BY clauses" I This means that any ID associated with the return will be random as it selects the MAX() record in the field and returns it with a set of randomly selected ID's (Usually just the first or last ID in a group)... This post is just WRONG, There are several other methods that will get the desired result. As for Interview questions? Use something more than one function for the basis of your interview maybe???