Q1.Table Structure: EMPLOYEE(EmpName, DeptName, Salary)
1. Write a SQL query to get highest salary in each Department.
2. Write a sql to get the number of employees in each Department.
3. Write a sql to split employee in two columns:
a) Employee above 5000(employee getting salary more than 5000)
b) Employee below 5000(employee getting salary below 5000)
No need to show the employee getting salary = 5000
A.
1. SELECT DeptName, MAX(salary) as "Highest salary"
FROM employee
GROUP BY DeptName
2. select DeptName, count(EmpName)
from Employee
group by DeptName
3a. select Salary, EmpName,
case when salary < 5000 then
'Less than 5000'
else
'Greater than 5000'
end
from employee
OR, alternative way,
3a. select case when salary < 5000 then
EmpName+' '+CAST(salary As Varchar(100))
else
NULL
end 'Less than 5000',
case when salary > 5000 then
EmpName+' '+CAST(salary As Varchar(100))
else
NULL
end 'Greater than 5000'
from employee
3b. select EMPNAME = EmpName,
EMPSAL =
case
when salary < 5000
then CAST(Salary as varchar(100))+ ' '+'Less than 5000'
when salary > 5000
then CAST(salary as varchar(100))+' ' +'Greater than 5000'
when salary = 5000
then CAST(salary as varchar(100))+' ' +'Equal to 5000'
end
from employee1
Q2. Write a sql to get the department name having highest number of employees.
A. Select DeptName From Employee1
Group by DeptName
Having count(EmpName) in(
Select Max(A.COUNTEMP)
From (select DeptName, count(EmpName)as COUNTEMP
from Employee1
group by DeptName) A)
|
No responses found. Be the first to respond and make money from revenue sharing program.
|