Answer to – SQL Assignment-2

The answer to assignment-2 is provided below.

If you haven’t visited the post, please do so by clicking here.

-- Answer to 1st Question
SELECT DName, LocationName, COUNT(*)  FROM Emp 
JOIN DeptLocation ON DeptLocation.DeptLocationId = Emp.DeptLocationId
JOIN Dept ON Dept.DeptNo = DeptLocation.DeptNo
JOIN Locations ON Locations.LocationId = DeptLocation.LocationId
GROUP BY DName, LocationName

-- Answer to 2nd Question
SELECT Dept.DeptNo, Dept.DName, Locations.LocationId, Locations.LocationName  FROM Dept
LEFT JOIN DeptLocation ON Dept.DeptNo = DeptLocation.DeptNo
LEFT JOIN Emp ON DeptLocation.DeptLocationId = Emp.DeptLocationId
LEFT JOIN Locations ON Locations.LocationId = DeptLocation.LocationId
WHERE EmpNo IS NULL

-- Answer to 3rd Question
SELECT Dept.DeptNo, Dept.DName, Locations.LocationId, Locations.LocationName, COUNT(EmpNo)  FROM Dept
LEFT JOIN DeptLocation ON Dept.DeptNo = DeptLocation.DeptNo
LEFT JOIN Emp ON DeptLocation.DeptLocationId = Emp.DeptLocationId
LEFT JOIN Locations ON Locations.LocationId = DeptLocation.LocationId
GROUP BY Dept.DeptNo, Dept.DName, Locations.LocationId, Locations.LocationName

-- Answer to 4th Question
SELECT 
		Dept.DeptNo, 
		Dept.DName, 
		Locations.LocationId, 
		Locations.LocationName, 
		COUNT(EmpNo) TotalEmployees, 
		MAX(Sal) HighestSalary, 
		MIN(Sal) LowestSalary, 
		SUM(ISNULL(Sal,0)+ISNULL(Comm,0)) TotalAmount FROM Dept
LEFT JOIN DeptLocation ON Dept.DeptNo = DeptLocation.DeptNo
LEFT JOIN Emp ON DeptLocation.DeptLocationId = Emp.DeptLocationId
LEFT JOIN Locations ON Locations.LocationId = DeptLocation.LocationId
GROUP BY Dept.DeptNo, Dept.DName, Locations.LocationId, Locations.LocationName

-- Answer to 5th Question
SELECT 
		EmpNo,
		CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName,
		DATEDIFF(YEAR, HireDate, GETDATE())
FROM Emp

-- Answer to 6th Question
SELECT 
		EmpNo,
		CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName,
		DATEDIFF(YEAR, HireDate, GETDATE()),
		SUM(ISNULL(Sal,0)+ISNULL(Comm,0)),
		SUM(ISNULL(Sal,0)+ISNULL(Comm,0))+1000
FROM Emp
WHERE DATEDIFF(YEAR, HireDate, GETDATE())> 2
GROUP BY EmpNo, CONCAT(FirstName, ' ', MiddleName, ' ', LastName), 		DATEDIFF(YEAR, HireDate, GETDATE())

-- Answer to 7th Question
SELECT 
	EmpNo, 
	CONCAT(FirstName, ' ', MiddleName, ' ', LastName) AS FullName,
	Job, 
	Sal,
	DName,
	LocationName
FROM Emp E
JOIN DeptLocation DL ON DL.DeptLocationId = E.DeptLocationId
JOIN Dept D ON D.DeptNo = DL.DeptNo
JOIN Locations L ON L.LocationId = DL.LocationId
WHERE Sal = 
(SELECT MAX(Sal) FROM Emp WHERE Sal < (SELECT MAX(Sal) FROM Emp))

-- Answer to 8th Question
SELECT EmpNo, Sal, Comm, Comm+Comm*0.10 FROM Emp WHERE Comm IS NOT NULL

Hope you had fun with this assignment.