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
3 жыл бұрын
That may not work if two or more employee has the same salary as highest salary.
@unboxingsillystuffs4920
3 жыл бұрын
@@crdave1988 what if we apply 'Distinct' as well, this might work
@crdave1988
3 жыл бұрын
@@unboxingsillystuffs4920 I am not getting your idea. Can u share more?
@StoneVicarious
2 жыл бұрын
@@crdave1988 select distinct top(1) salary from employee
@EriaPinyi
2 жыл бұрын
I think it is just another way of doing it. That is the flexibility of programming.
@Salim_TravelVlog
5 жыл бұрын
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.
@srinikethvydya806
4 жыл бұрын
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
@rvffrd6917
7 жыл бұрын
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
7 жыл бұрын
That is correct, I was about to write a similar note. I would use Row_Number(), but your way is more elegant
@saibhargavLanka21
6 жыл бұрын
Even group by not required after adding where condition
@mildtime1995
6 жыл бұрын
Its Working efficiently buti didnt understand the concept how it worked. Can you please explain.
@srikanthb903
6 жыл бұрын
group by is required to get single record for a department.
@thambithurai4115
6 жыл бұрын
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)
@MmmBopsPops
5 жыл бұрын
Query #1 - SELECT * FROM employee ORDER BY salary DESC LIMIT 1.
@carloramundo9013
5 жыл бұрын
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
5 жыл бұрын
@@carloramundo9013 SQL-Server: ... TOP 1 WITH TIES ...
@robhunn4835
5 жыл бұрын
subqueries are slower because they actually run 2 queries, so they should only be used on small data sets...
@carloramundo9013
5 жыл бұрын
@@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
5 жыл бұрын
@@carloramundo9013 No subquery needed here: SELECT TOP (1) WITH TIES * FROM employee ORDER BY salary DESC
@alpha3305
7 жыл бұрын
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.
@mso4324
2 жыл бұрын
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.
@siriusgd4753
5 жыл бұрын
"So, what did you learn from this demonstration?" "That a job in Sales is better than a job in I.T."
@jasonwong8315
4 жыл бұрын
SiriusGD haha you are right
@GiacaloneSalvatore
4 жыл бұрын
Haha fools
@atikhanovesy2152
4 жыл бұрын
Lol
@LF100-03
4 жыл бұрын
Less job security included.
@chad55009
4 жыл бұрын
probably pays better too.
@damienbates
3 жыл бұрын
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.
@shreyansjain1853
4 жыл бұрын
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;
@SonnyWest87
5 жыл бұрын
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
5 жыл бұрын
Did the companies ask for specific SQL certification? Also which one would you recommend?
@sureshpatra6384
4 жыл бұрын
it is very easy 😊
@spicytuna08
6 жыл бұрын
2nd highest salary: select Max(Salary) from Employee where salary < select Max(Salary) from Employee. THis makes more intuitive to me.
@aboalhassan448
4 жыл бұрын
and add limit 1 to the end
@gginnj
4 жыл бұрын
aboalhassan you shouldn't need the limit 1, as max() will only return 1 value
@aboalhassan448
4 жыл бұрын
youre right thank u
@TechandArt
3 жыл бұрын
It's very important
@kstevens0915
3 жыл бұрын
This way is the simplest...select (Max(Salary)-1) from Employee
@mjrobins
5 жыл бұрын
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
2 жыл бұрын
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
2 жыл бұрын
@@glennbabic5954 I agree. The way to solve this question is using a loop, which is not often done in SQL.
@glennbabic5954
2 жыл бұрын
@@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
2 жыл бұрын
@@glennbabic5954 Yep, that's simpler than a loop. Thank you for the reply.
@glennbabic5954
2 жыл бұрын
@@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;
@amitpatelpatel144
7 жыл бұрын
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
7 жыл бұрын
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
6 жыл бұрын
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
5 жыл бұрын
DURING INTERVIEW HOW MUCH CGPA MATTERS TO GET A GOOD JOB
@chazsmith20
5 жыл бұрын
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
2 жыл бұрын
Or much more simply: ORDER BY salary DESC LIMIT n-1, x+1; when you want the nth salary through to n+xth salary.
@vmir88
3 жыл бұрын
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);
@DistantGlowingStar
4 жыл бұрын
These are entry level questions, most complex ones are those that require to retrieve hierarchical data , indexes, rank etc..
@The-Right-is-Right
2 жыл бұрын
@Guru H Please do a video and explain some complex SQL questions with answers. Thanks.
@superkutta
6 жыл бұрын
for 2nd higest salary Select * from ( Select salary, dense_rank () over ( order by salary desc) ranking from employee ) Where ranking =2
@madhaviravoori6466
6 жыл бұрын
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
6 жыл бұрын
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
6 жыл бұрын
this is correct!
@Osta165
6 жыл бұрын
wen we use distinct i think this is when we need to return unique values.
@13abesssssssssssssss
5 жыл бұрын
what if we are looking for 3rd or 4th highest salary?
@cwillison94
5 жыл бұрын
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
4 жыл бұрын
please explain why not use * in production? also, what is production? sorry, very beginner here!
@cwillison94
4 жыл бұрын
@@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
4 жыл бұрын
Cole Willison that makes sense, thank you!
@nikhilvaidya587
6 жыл бұрын
Your 6th query is incorrect. For example if sales department has an employee with 80000 salary the query will return one more record.
@fabioneves9224
3 жыл бұрын
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
3 жыл бұрын
@@fabioneves9224 I would use window function Max salary partition by department and only select records where salary = the window Max
@charbelbejjani5541
3 жыл бұрын
@@bobbygia1198 Yeah easier
@dallasitnerds2321
6 жыл бұрын
730K? Can you query stacys phone number?
@johnmadsen37
6 жыл бұрын
Dallas IT Nerds 555-she-ahoe
@PriyankaSharma-dy8dg
6 жыл бұрын
What will you do with that...
@kirakira4ever
6 жыл бұрын
hhahahhahaha
@vatsaakhil
6 жыл бұрын
What she sellin
@pickler_pickler
6 жыл бұрын
hahhahhhhah
@ishwargoudar1851
6 жыл бұрын
Good one. Helped me to understand these queries in quick and simple way.
@waqaracheema
4 жыл бұрын
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!
@unvunb7157
Жыл бұрын
How do you select the second-highest record?
@steenteudt
5 жыл бұрын
#1: select top 1 * from employee order by salary desc
@seporokey
6 жыл бұрын
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.
@TechSolutionDesk
6 жыл бұрын
Awesome! I just had my interview today and the first two questions were ask. Thank you very much..
@prithalove
6 жыл бұрын
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
4 жыл бұрын
2 table scans?......argh. Just dont do this ever.
@sujitkumarnayak101
7 жыл бұрын
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
7 жыл бұрын
Select * from (select DENSE_RANK() OVER (ORDER BY Salary desc) AS SN,* from Employee ) as NewTable where SN =N
@sarikabiwalkar4037
7 жыл бұрын
Your answer is correct since it will give the result for nth salary.
@nikhilb3880
5 жыл бұрын
Or use limit
@enanodb
6 жыл бұрын
Is there any suggest between using 'IN' and equals '='. Why you change in where clause on query 1 and 5? Subquery are the same
@beingyourself9824
5 жыл бұрын
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
4 жыл бұрын
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
4 жыл бұрын
@@stanson5850 SELECT * FROM employees GROUP BY salary ORDER BY salary DESC LIMIT 1,1
@reylencatungal4593
3 жыл бұрын
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.
@GracefulTalesPluto
2 жыл бұрын
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!
@kevinclarke3485
6 жыл бұрын
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.
@dhanasekars3622
6 жыл бұрын
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?
@kwabenaodameakomeah3374
5 жыл бұрын
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
4 жыл бұрын
this will fail in case of repeating values
@unboxingsillystuffs4920
3 жыл бұрын
@@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);
@shreyansjain1853
4 жыл бұрын
Question 2 Answer SELECT Salary as "2ndHighest_Salary" from employee Where Salary (select MAX(Salary) from employee) order by salary desc Limit 1;
@udaynayak4788
7 жыл бұрын
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
6 жыл бұрын
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
6 жыл бұрын
Yes you can use with less than operator
@charlesbyrneShowComments4all
6 жыл бұрын
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
5 жыл бұрын
@@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
5 жыл бұрын
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
@avinashgogineni4u
3 жыл бұрын
Well explained and easy to understand beginners 👏👏
@loam
6 жыл бұрын
for 2nd I would go with: SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET 1
@nataliatimakova9446
6 жыл бұрын
DISTINCT(salary) застрахует от дубликатов. Ведь может быть две одинаковые MAX(salary)
@manit77
4 жыл бұрын
What if two people have the same salaries?
@ciruzzi7
5 жыл бұрын
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
@ALIRAZA-cp4fs
5 жыл бұрын
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
5 жыл бұрын
no it is not displaying second highest salary
@stanson5850
4 жыл бұрын
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
4 жыл бұрын
bro u will fail in repeating values
@frankzelazko
Жыл бұрын
very useful and educational video. cheers
@purnapp4012
6 жыл бұрын
These sql queries are very helpful for interview.
@TechandArt
3 жыл бұрын
Awesome
@markjsound
3 жыл бұрын
This is for a nontechnical entry level job, correct? What type of job would this really be applicable? I wouldn’t answer the question instead would ask, how did you decide to use a relational database for this data? If this is employee data, why is it not encrypted? Are you using the same tables for reads and writes?
@bobDotJS
6 жыл бұрын
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
@manit77
4 жыл бұрын
Not exactly wrong but, You might want to return all employees that have the highest salary.
@ivanhaidarli7163
4 жыл бұрын
easy to understand, informative. Thank you. watching in 2020
@darioa2827
3 жыл бұрын
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
3 жыл бұрын
So how'd it go?
@darioa2827
3 жыл бұрын
@@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
@swarupghadei3590
4 жыл бұрын
Sir at @8:34 you used where clause but what we are essentially doing is finding max (which is an aggregate function) shouldn't it be having instead of where clause ? ..I did try substituting having clause instead of where clause but it is not working .
@johnspencer772
6 жыл бұрын
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......
@avonstar8893
6 жыл бұрын
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')
@vitaliysamofal2866
6 жыл бұрын
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
4 жыл бұрын
Table scan for the love of god. Dont do this.
@ppranda
2 жыл бұрын
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?
@robertmckee9272
3 жыл бұрын
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.
@shaikkhaja8984
3 жыл бұрын
What if they asked about third highest salary?
@robertmckee9272
3 жыл бұрын
@@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.
@Shoikan71
3 жыл бұрын
Much more efficient, that's the way I would do it, avoid subquerys whenever possible
@Evan-zj5mt
3 жыл бұрын
@@robertmckee9272 Thanks for the offset / fetch tip. Didn't know that!
@jiejenn
2 жыл бұрын
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.
@linqishao471
3 жыл бұрын
for the very last answer, does it still working if the highest salary in IT also shown up for one of the employee in sales department?
@owoeyegbenga8657
3 жыл бұрын
For 2nd Highest Salary, We can also do select salary FROM employee ORDER BY salary DESC LIMIT 1,1
@axlderks4022
3 жыл бұрын
Select top 1 also works
@HeartHealerTV
7 жыл бұрын
Hi at 16:28 query-Capitalizing the words Employee and Department means what. How come you couldn't leave it lowercase? Thank you
@karanjawalkar4768
6 жыл бұрын
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
5 жыл бұрын
Impressed bro
@stanson5850
4 жыл бұрын
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
@libbybyrd6192
6 жыл бұрын
Would 'select max(salary from employee' not produce the same result as what you did for the first task?
@AAA-bo1uo
5 жыл бұрын
Some questions: For the first, is that more efficient than: SELECT * FROM employee ORDER BY salary DESC LIMIT 1 ?
@jk2l
5 жыл бұрын
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
@yashtrivedi1511
4 жыл бұрын
after wirying where clause can we write just a condition and not select statement in where clause as we have already mentioned above..
@mohammadarifulla797
7 жыл бұрын
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
7 жыл бұрын
The 1st sql statement you mentioned will definitely get you the 2nd highest salary. I'm not too sure about using rownum yet.
@nirmalwewitavidana2592
6 жыл бұрын
how do we find all the details(row) of the employee who gets the 2nd highest salary
@madhaviravoori6466
6 жыл бұрын
Hi, In your second query, using ORDER BY Clause in the inner select doesn't work , it is invalid
@srikanthb903
6 жыл бұрын
the first statement gets you all the salaries less than max salary. so wrong.
@mehranofff
5 жыл бұрын
@@srikanthb903 No. Actually the first query returns the correct result. It is SELECT MAX(Salary) which returns only one value
@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
@MK-je7kz
6 жыл бұрын
#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
6 жыл бұрын
You can't select multiple values into a variable as you did in the #3 example.
@zettwire
6 жыл бұрын
@@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
5 жыл бұрын
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
5 жыл бұрын
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
5 жыл бұрын
@@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.
@skillsandthrills7054
2 жыл бұрын
Do you have more videos for interview questions ,? Can you please share ?
@AdnanSabah
5 жыл бұрын
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!
@faisalshaikh5856
3 жыл бұрын
thank you for this queries i am having a question if table 1 having 2 columns (s.no, marks) and in table 2 having 2 columns same (s.no, marks) we having to find the max of marks in both the tables how to write the querry
@cassondrad2280
7 жыл бұрын
Awesome sauce. You make it so much clearer than these other websites. THE light bulb finally came on, thank you. I so get it.....
@vadiyarhemadri1903
2 жыл бұрын
Super learnt lot of knowledge from you thank you so much
@tropicalseedlings
6 жыл бұрын
#1 no sub-query needed. select top 1 * from employee order by salary desc; The same rule for MySQL and Oracle accordingly.
@gloriabukachi1
Жыл бұрын
Thank you for the videos. I am adding to my knowledge
@joseassumpcao624
5 жыл бұрын
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
5 жыл бұрын
there is limit/offest in the fucking SQL-SERVER
@AKHILESHKUMAR-nk2rk
4 жыл бұрын
wht about when we have repeating values
@RoseOginga2
4 жыл бұрын
Great ! Love your explanation very much on point - Thanks
@aitch749
5 жыл бұрын
/* 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
4 жыл бұрын
select * from emp aa where 3-1=(select count(*) from emp where salary>aa.salary)
@learnthealphabet5561
4 жыл бұрын
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
4 жыл бұрын
also can use row_number()
@RutgerOlthuis
4 жыл бұрын
@@刘晓虎-k4m if I remember correctly, row_number is done before filtering. So you'll get the wrong record back.
@patrickproctor3462
4 жыл бұрын
@@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.
@c.danielpremkumar8495
6 жыл бұрын
Does "Range" apply only to the enmp-id (which is a number) or could it apply to any column in the table ?
@mayanksharma8518
6 жыл бұрын
The last query will fail when we have an employee in sales department with 80000 salary!
@AKHILESHKUMAR-nk2rk
4 жыл бұрын
yes thats the point
@monday6740
5 жыл бұрын
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
@erickha6232
5 жыл бұрын
Great Video Man. Can we have more video interview questions like this one?
@gflight295
6 жыл бұрын
Why did you add the letter E. ? how did you know how to select the letter E? Thx
@nenenartey4266
7 жыл бұрын
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
7 жыл бұрын
How do you get a better answer? He got the answer he sought. Problem solved.
@johndrury
6 жыл бұрын
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
6 жыл бұрын
You could also use a rank.
@cgalon6781
5 жыл бұрын
@@johndrury agreed, also crafting a solution valid for any Nth salary shows the interviewer you really understand how to query
@munnaharun2431
5 жыл бұрын
Suppose if salary having null value, then it returns in nth highest salary when u select 1st highest salary
@harrymary100
3 жыл бұрын
Wonderful tutorial on SQL interview questions: very helpful keep it up
@ras_tesfa5148
5 жыл бұрын
For the record, "NOT IN" doesn't mean "NOT EQUAL", technically. 😉
@RutgerOlthuis
4 жыл бұрын
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
@deepakpandey9406
4 жыл бұрын
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.
@robertablack8363
3 жыл бұрын
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 🙏.
@Cube8
5 жыл бұрын
Query 1: select top 1 * from employee order by salary desc
@enix4141
5 жыл бұрын
or Select * from employee ODER BY Salary DESC LIMIT 1; For second highest LIMIT 1 OFFSET 1;
@nmlokman8538
5 жыл бұрын
What if there are two employees with highest salary ? 🤔
@sunilk.c5367
5 жыл бұрын
Excellent !! This is one of the easiest and simplest explanation I have seen .
@harditsingh8291
7 жыл бұрын
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
7 жыл бұрын
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
7 жыл бұрын
We all know there's more than 1 way to skin a cat. His objective was clearly stated. He achieved it. Mission accomplished.
@miamac63
7 жыл бұрын
Well Said.
@tom_nqld
7 жыл бұрын
You have answered my question what if you're asked to display the 3rd highest salary, thanks!
@acedeno23
6 жыл бұрын
Hardit Singh We need programmers @acebooks.xyz!
@akhilesh_39
3 жыл бұрын
It's a very useful video. Thanks
@mandiramitra7203
5 жыл бұрын
Hey, what will be the query for 3rd or 4th highest salary then?
@devinkemp662
5 жыл бұрын
I was wondering the same thing?
@alesmohoric9130
Жыл бұрын
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".
@this.channel
6 жыл бұрын
Nice refresher. I tend to forget the specifics if I haven't been working on databases for a while.
@atikhanovesy2152
4 жыл бұрын
True
@Ddx_4
3 жыл бұрын
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.
@SachinGupta-km3no
6 жыл бұрын
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!
@JoshyHJoshy
4 жыл бұрын
Is there a script to generate and populate the required tables for following this tutorial?
@FIXProtocol
7 жыл бұрын
Great stuff! I just subscribed! You did a really good job! Let's help these folks learn and grow! This is REAL KZitem content!
@cryptotec7887
4 жыл бұрын
Very nice - basic and easy to see the sql code work
@theno1.tribitfan507
4 жыл бұрын
/* 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
4 жыл бұрын
Jenius
@usertuserb7057
4 жыл бұрын
verry goooooood
@adityabalki1909
4 жыл бұрын
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.
@khaledbds
2 жыл бұрын
Thanks for the video, how to find 3rd highest salary or 4th highest salary, from employee table?
@renepirolt
7 жыл бұрын
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.
@anuradhadontha6227
7 жыл бұрын
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;
@kartikchauhan5498
6 жыл бұрын
Wow, you just made me fall in love with you :D. Great solution btw.
@bombayboys100
6 жыл бұрын
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
@madhaviravoori6466
6 жыл бұрын
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
@lusibitch2731
6 жыл бұрын
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.
@cassondrad2280
4 жыл бұрын
Excellent. Verbally clear and so concise. I enjoyed learning and have full understanding as well. Well Done...
@kerwin2323
4 жыл бұрын
Query #6: SELECT first_name, last_name, MAX(salary), department_name FROM employee NATURAL JOIN department GROUP BY department_name;
@bretcj7
6 жыл бұрын
Only suggestion is to use CTE when you can to clean up the efficiency of the multiple select queries.
Пікірлер: 1,2 М.