/*Q10. Show Products that are no longer in stock (Quantity Zero)*/
USE AdventureWorks2017;
SELECT PP.ProductID,
       PP.Name,
       PINV.Quantity
FROM Production.[Product] AS PP
    LEFT OUTER JOIN Production.[ProductInventory] AS PINV
        ON PP.ProductID = PINV.ProductID
GROUP BY PP.ProductID,
         PP.Name,
         PINV.Quantity
HAVING SUM(PINV.Quantity) = 0;

/*Q1: Shows Instructors that teach in Multiiple Departments*/
SELECT DISTINCT Multi_Department_Instructors.InstructorFullName, 
MAX(Multi_Department_Instructors.Quantity) AS Num_Departments
FROM (
	SELECT DISTINCT InstructorFullName, 
	DENSE_RANK() OVER
		(PARTITION BY InstructorFullName ORDER BY DepartmentName) AS Quantity
	FROM Department.[Instructor]
	GROUP BY InstructorFullName,
			 DepartmentName
	HAVING InstructorFullName <> 'TBA') AS Multi_Department_Instructors
GROUP BY Multi_Department_Instructors.InstructorFullName
HAVING MAX(Multi_Department_Instructors.Quantity) > 1
ORDER BY Num_Departments DESC

/*Q2: Shows the Amount & Percentage of Professors Teaching in Each Department*/
SELECT DISTINCT DepartmentName, COUNT(InstructorFullName) AS Num_Intstructors, 
CONCAT( CAST(COUNT(InstructorFullName) * 100.00/ 
	(SELECT COUNT(InstructorFullName) FROM Department.Instructor) AS DECIMAL (5,2) ), '%') 
	AS Pct_Of_Instructors

FROM Department.[Instructor]
GROUP BY DepartmentName
ORDER BY Num_Intstructors DESC