02 SQL INTERVIEW QUESTIONS Nth HIGHEST SALARY, GROUP BY, HAVING,ORDER BY



Published
In this video will cover interview questions around, Nth highest salary, group by, order by, having and where clause.


===================================
=================SQLs==============
--========CREATE TABLES=============


create table Department
(
dept_id int not null PRIMARY KEY,
dept_name varchar(64)
)


create table Employee
(
emp_id int not null PRIMARY KEY,
emp_name varchar(64),
emp_salary float,
dept_id int FOREIGN KEY REFERENCES Department(dept_id)
)




--# to describe the table schema and details information use sp_help


EXEC sp_help 'dbo.Department';
EXEC sp_help 'dbo.Employee';


select * from Employee -- 5 records
select * from Department -- 5 records




---==================INSERT RECORDS==============================================


insert into Department values (1,'Sales')
insert into Department values (2,'Account')
insert into Department values (3,'Finance')
insert into Department values (4,'IT')
insert into Department values (5,'Marketing')


insert into Employee values (1,'Virag',54000,1)
insert into Employee values (2,'Jayesk',54000,2)
insert into Employee values (3,'Sanej',10000,2)
insert into Employee values (4,'Deejo',20000,4)
insert into Employee values (5,'Manor',40000,3)


insert into Employee values (6,'Jack',51000,null)
insert into Employee values (7,'Neo',34000,4)
insert into Employee values (8,'Tom',16000,null)
insert into Employee values (9,'Kalara',48000,1)
insert into Employee values (10,'Jeff',43000,5)




select * from [dbo].[Employee] order by emp_salary
select * from [dbo].[Employee] order by emp_salary ASC
select * from [dbo].[Employee] order by emp_salary DESC


---- Nth Highest Salary----------
select max(emp_salary) from Employee


select top 1 emp_salary from Employee order by emp_salary desc


select top 1 emp_salary from Employee order by emp_salary


select min(emp_salary) from Employee
---


-------4th highest salary---------------
select top 1 emp_salary from (
select distinct top 2 emp_salary from Employee order by emp_salary desc
) g order by emp_salary asc


-------Nth highest salary---------------
declare @n int;
set @n=5;
select top 1 emp_salary from (
select distinct top (@n) emp_salary from Employee order by emp_salary desc
) g order by emp_salary asc


-----------------------------------------------------------
------------------------GROUP BY------------------------
select * from Employee
---dept-name, empcount-----


select dept_id,count(*) as 'EmpCount' from Employee group by dept_id order by count(*) desc




select s.dept_name,count(*) as 'EmpCount' from (
select e.*,d.dept_name from Employee e left join Department d on e.dept_id=d.dept_id
) s group by s.dept_name order by s.dept_name desc




select s.dept_name,count(*) as 'EmpCount' from (
select e.*,d.dept_name from Employee e left join Department d on e.dept_id=d.dept_id
) s group by s.dept_name order by count(*) desc






select s.dept_name,count(*) as 'EmpCount' from (
select e.*,d.dept_name from Employee e left join Department d on e.dept_id=d.dept_id
) s group by s.dept_name
having count(*)=2


select s.dept_name,count(*) as 'EmpCount' from (
select e.*,d.dept_name from Employee e left join Department d on e.dept_id=d.dept_id
) s where s.dept_name ='IT'
group by s.dept_name
having count(*)=2
--------------------------------------------------------------------------------










--------------------------------------------------------------------------------
Linked In: https://www.linkedin.com/in/viragjagtap/
Category
Job
Be the first to comment