6 common SQL Query Questions
Excellent, engaging, and challenging questions! Especially the 2nd highest sallary. I do not mean that as bad as sarcastic, but it seems you were completely oblivious to "order by".
The second highest salary doesn't need a "NOT IN" comparison... "<>" is better in this case.
The last one is better this way:
SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM Employees e
INNER JOIN (SELECT MAX(salary) max_salary, department_id FROM Employees GROUP BY department_id) f
ON e.department_id = f.department_id AND e.salary = f.max_salary
INNER JOIN Department d ON e.department_id = d.department_id
This way you get all MAX salaries by department even if there are multiple employees making that much in the same department.
Selecting the nth salary could be better queried using the following, for example the 5th highest salary
Select * From (Select *, ROW_NUMBER() Over(Order By Salary DESC) rn From employee) as t
Where rn = 5
But, I agree to choose the 2nd your query is elegant.
The coding Interview: Why you didn't use ORDER BY DESC or ASC combined with LIMIT 1 ; ? I think it's very easy
Do you have more videos for interview questions ,? Can you please share ?
Very useful video for interviews .
Now how to get the 3rd or 4th highest salary ?
On the 1st question... Why not simply top 1 and order by salary desc? Much faster than al sub.
2nd question... A cte with top 2 is the the best option for easy future maintenance.
Got bored. Won't see the rest.
Super learnt lot of knowledge from you thank you so much
How to find non matching records from two tables: youtu.be/j9HL_2NIfcE youtu.be/j9HL_2NIfcE
In that last query implementation, if you happen to have a sales record with a salary of 80000 wouldn’t that line also return in your result?
Was indeed good stuff
highly informative about:invalid#zCSafez
the way you write SQL keywords irritates me. some lower case, some PascalCase and some uppercase.
Choose one style god damnit
For the last one we can also do like this ==> Select e.name/ e.name ,max(e.salary),d.deptname from employee e inner join dept d on e.deptid =d.deptid
Group by e.name/ e.name ,d,deptname