SQL Queries

 --1. Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table.

--select * from EmployeeDetails e inner join EmployeeDetails m on e.EmpId = m.ManagerId

--2. Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key – EmpId).

--select *, ROW_NUMBER() over (partition by [FullName], [DateOfJoining], [City] order by FullName) as rownum from EmployeeDetails

--select [FullName], [DateOfJoining], [City],count(*) from EmployeeDetails group by [FullName], [DateOfJoining], [City] HAVING COUNT(*) > 1;

--3. Write an SQL query to remove duplicates from a table without using a temporary table.

--WITH cteRowNum AS 

--(select *, ROW_NUMBER() over (partition by [FullName], [DateOfJoining], [City] order by FullName) as rownum from EmployeeDetails)

--delete from cteRowNum where rownum >1

--4. Write an SQL query to fetch only odd rows from the table.

--WITH cteRowNum AS 

--(select *, ROW_NUMBER() over (order by FullName) as rownum from EmployeeDetails)

--select * from cteRowNum where rownum%2!=0

--5. Write an SQL query to fetch only even rows from the table.

--WITH cteRowNum AS 

--(select *, ROW_NUMBER() over (order by FullName) as rownum from EmployeeDetails)

--select * from cteRowNum where rownum%2=0

--6. Write an SQL query to create a new table with data and structure copied from another table.

--select * into EmployeeDetails_Copy from EmployeeDetails

--7. Write an SQL query to create an empty table with the same structure as some other table.

--select * into EmployeeDetails_Copy3 from EmployeeDetails where 1=0;

---8. Write an SQL query to fetch top n records.

--select top 1 Salary from EmployeeSalary order by Salary desc

--9. Write an SQL query to find the nth highest salary from a table.

--With salaryCTE as

--(select *, ROW_NUMBER() OVER (order by salary desc) as NthSalary from EmployeeSalary)

--select * from salaryCTE where NthSalary =3

--10. Write an SQL query to find the nth highest salary from a table with duplicate salary

--WITH salartCTE as

--(select *, DENSE_RANK() OVER (order by salary desc) as NthSalary from EmployeeSalary)

--select * from salartCTE where NthSalary =3

--11. Consider a SalesData with columns SaleID, ProductID, RegionID, SaleAmount. Write a 

--query to find the total sales amount for each product in each region.

--select Project, SUM(salary) from EmployeeSalary group by Project

--12. Write a query to find employees who earn more than their managers.

--select e1.FullName as ManagerName, e1.EmpId as ManagerId, e2.FullName as empName, e2.EmpId as empId, es.Salary as empSalary,

--ems.Salary as Managersalary from EmployeeDetails e1 

--join EmployeeDetails e2 on e1.EmpId =e2.ManagerId  

--left join EmployeeSalary es on e2.EmpId=es.EmpId

--left join EmployeeSalary ems on e1.EmpId=ems.EmpId

--13. Consider a BookCheckout table with columns – CheckoutID, MemberID, BookID, CheckoutDate, ReturnDate. 

--Write an SQL query to find the number of books checked out by each member.

--select COUNT(*) AS NumberOfBooksCheckedOut as totalBooked , MemberID from BookCheckout goup by BookCheckout

--14. Consider a StudentGrades table with columns – StudentID, CourseID, Grade. 

--Write a query to find students who have scored an ‘A’ in more than three courses.

--select COUNT(*) Grade, StudentID from StudentGrades WHERE Grade='A' group by StudentID Having COUNT(*)>3

--15. Consider a table OrderDetails with columns – OrderID, CustomerID, ProductID, OrderDate, Quantity, Price. 

--Write a query to find the average order value for each customer.

--SELECT CustomerID, AVG(Quantity * Price) AS AvgOrderValue FROM OrderDetails GROUP BY CustomerID;

--16. Consider a table PatientVisits with Columns VisitID, PatientID, DoctorID, VisitDate, Diagnosis. 

--Write a query to find the latest visit date for each patient.

--SELECT PatientID, MAX(VisitDate) AS LatestVisitDate FROM PatientVisits GROUP BY PatientID;

--17. Consider a table Transactions with columns – TransactionID, CustomerID, ProductID, TransactionDate, Amount. 

--Write a query to find the total transaction amount for each month.

--SELECT MONTH(TransactionDate) AS Month, SUM(Amount) AS TotalAmount FROM Transactions GROUP BY MONTH(TransactionDate);

--18. Consider a table EmployeeAttendance with columns – AttendanceID, EmployeeID, Date, Status. 

--Write a query to find employees with more than 5 absences in a month.

--SELECT EmployeeID, MONTH(Date) AS Month, COUNT(*) AS Absences FROM EmployeeAttendance 

--WHERE Status = 'Absent' GROUP BY EmployeeID, MONTH(Date) HAVING COUNT(*) > 5;

--19. Get employee assigned project count

--WITH projectdtl as(

--select e2.empId, FullName

--from EmployeeDetails e1 right join EmployeeSalary e2 on e1.EmpId=e2.EmpId) 

--select empid,count(*) as totalprojects from projectdtl  group by empid