SQL Questions

Q.1 Write a SQL query to fetch the count of employees working in project 'P2' or Salary Greater than 9000 ?

SELECT Count(*) from EmployeeDetails ed inner join EmployeeSalary es on ed.EmpId=es.EmpId

where es.Project='P2' or es.Salary>7000

Q.2 Write a SQL query to Delete “employee names” who is having project P1.

delete from EmployeeDetails where EmpId in(select EmpId from EmployeeSalary where Project='P2')

Q.3 Write a SQL query to fetch “employee names” having salary greater than or equal to 5000 and less than or equal 10000.

SELECT FullName from EmployeeDetails ed inner join EmployeeSalary es on ed.EmpId=es.EmpId

where es.Salary>=5000 and es.Salary<=9000

Q.4 Write a SQL query to fetch project-wise count of employees.

SELECT Count(es.Project),es.Project from EmployeeDetails ed inner join EmployeeSalary es on ed.EmpId=es.EmpId

group by es.Project

Q.5 Write a query to fetch only the first name(string before space) from the FullName column of EmployeeDetails table.

select SUBSTRING(FullName,0,CHARINDEX(' ',fullname)) from EmployeeDetails

Q.6 Write a SQL query to fetch all the Employees who are also managers from EmployeeDetails table.

select *from EmployeeDetails ed inner join EmployeeDetails ed1 on ed.EmpId=ed1.ManagerId

Q.7. Write a SQL query to fetch duplicate records from a table EmployeeSalary .

SELECT Salary,

COUNT(*) occurrences

FROM EmployeeSalary GROUP BY Salary

HAVING COUNT(*) > 1;

Q.8 Write a SQL query to remove duplicates from a table without using temporary table EmployeeSalary .

with temp as (

select Salary,ROW_NUMBER()over(partition by salary order by salary) rn from EmployeeSalary

)delete from temp where rn>1

Q.9 Write a SQL query to fetch only odd rows from table EmployeeSalary.

with temp as (

select Salary,ROW_NUMBER()over(order by salary) rn from EmployeeSalary

)select *from temp

where rn%2>0

Q.10 Write a SQL query to create a new table with data and structure copied from another table.

select * into EmployeeSalary_1 from EmployeeSalary

Q.11 Write a SQL query to fetch common records between two tables.

select *from Table_1 t1 inner join Table_2 t2 on t1.ID=t2.ID

Q. 12 Write a SQL query to fetch records that are present in one table but not in another table.

select *from Table_1 t1 left outer join Table_2 t2 on t1.ID=t2.ID where t2.ID is null

Q.13 Write a SQL query to update “project” with “P3” who is having project P2.

update es

set es.Project ='P3'

from EmployeeDetails ed inner join EmployeeSalary es on ed.EmpId=es.EmpId

where es.Project='P2'

Q.14 Write SQL query to find the nth highest salary from the table.

select * from (select DENSE_RANK() over (order by salary) as rn, salary from EmployeeSalary) a

where rn=3

Q.15 Create cluster index on EmpID column in EmployeeDetails table.

create clustered index ed1 on EmployeeDetails(Empid)

Q. 16 Insert a new record in EmployeeDetails table with EmpID = 521 , FullName = Naveen Rao, ManagerId = 110 , DateOfJoining = NULL

Insert into EmployeeDetails (Empid,FullName,ManagerID,DateofJoing)

Values(521,’Naveen Rao’,110,null)

Q.17 Update DateOfJoining = 08/08/2019 in EmployeeDetails table where DateOfJoining is null

Update EmployeeDetails

Set

DateofJoing= 08/08/2019 where DateofJoing is null