accessTelevisionTSQL-queries.sql

--TO USE: Highlight the 'USE Access_TV' line below then execute the query to select the database. Note that the database
--must be created first. Once those steps are complete, highlight the text beneath each question and execute the query 
--to retrieve data.

USE Access_TV

--QUESTION 1: How many access producers work at X station?
SELECT STN_ID AS 'Station ID', COUNT(AP_ID) AS 'Number of Access Producers'
FROM ACCESS_PRODUCER
GROUP BY STN_ID
ORDER BY STN_ID

--QUESTION 2: Which station has the most access producers?
SELECT TOP 1 WITH TIES STN_ID AS 'Station with Most Access Producers',
	COUNT(AP_ID) AS 'Number of Access Producers'
FROM ACCESS_PRODUCER
GROUP BY STN_ID
ORDER BY COUNT(AP_ID) DESC

--QUESTION 3: How many volunteers do we have?
SELECT COUNT(SP_ID) AS 'Number of Volunteers'
FROM SHOW_PERSONNEL
WHERE SP_IS_VOLUNTEER = 1

--QUESTION 4: What volunteers worked on show X?
SELECT SHOW.SHOW_ID AS 'Show ID', SHOW_NAME AS 'Show Name',
	CREW_MEMBER.SP_ID AS 'Volunteer SP ID', 
	SP_LNAME AS 'Last Name', SP_FNAME AS 'First Name'
FROM SHOOT_LIST
	JOIN SHOOT ON SHOOT.SHOOT_ID = SHOOT_LIST.SHOOT_ID
	JOIN CREW_MEMBER ON CREW_MEMBER.SHOOT_ID = SHOOT.SHOOT_ID
	JOIN SHOW_PERSONNEL ON SHOW_PERSONNEL.SP_ID = CREW_MEMBER.SP_ID
	JOIN EPISODE ON EPISODE.SHOW_ID = SHOOT_LIST.SHOW_ID
	JOIN SHOW ON SHOW.SHOW_ID = EPISODE.SHOW_ID
WHERE CREW_MEMBER.CM_IS_VOLUNTEER = 1
GROUP BY SHOW.SHOW_ID, SHOW_NAME, CREW_MEMBER.SP_ID, SP_LNAME, SP_FNAME
ORDER BY SHOW_NAME

--QUESTION 5: How many hours did the volunteers work?
SELECT SUM(SP_TOTAL_HOURS) AS 'Total Volunteer Hours'
FROM SHOW_PERSONNEL
WHERE SP_IS_VOLUNTEER = 1

--QUESTION 6: Which volunteers worked the most hours?
SELECT TOP 3 WITH TIES SP_ID AS 'Volunteer SP ID', SP_LNAME AS 'Last Name',
	SP_FNAME AS 'First Name', SP_TOTAL_HOURS AS 'Hours Worked'
FROM SHOW_PERSONNEL
WHERE SP_IS_VOLUNTEER = 1
GROUP BY SP_ID, SP_LNAME, SP_FNAME, SP_TOTAL_HOURS
ORDER BY SP_TOTAL_HOURS DESC

--QUESTION 7: How many volunteers worked over X amount of hours?
SELECT COUNT(SP_ID) AS 'Volunteers That Worked More Than 100 Hours'
FROM SHOW_PERSONNEL
WHERE SP_TOTAL_HOURS > 100 AND SP_IS_VOLUNTEER = 1

--QUESTION 8: What task do volunteers work most?
SELECT TOP 3 WITH TIES CREW_MEMBER.TASK_ID AS 'Task ID', TASK_NAME AS 'Task Name',
	COUNT(CREW_MEMBER.TASK_ID) AS 'Number of Times Worked by Volunteer'
FROM CREW_MEMBER
	JOIN TASK ON TASK.TASK_ID = CREW_MEMBER.TASK_ID
WHERE CM_IS_VOLUNTEER = 1
GROUP BY CREW_MEMBER.TASK_ID, TASK_NAME
ORDER BY COUNT(CREW_MEMBER.TASK_ID) DESC

--QUESTION 9: What crew member worked X task the most?
--Using TASK_ID 001, CAMERA_FIXED
SELECT TOP 1 WITH TIES CREW_MEMBER.TASK_ID AS 'Task ID', TASK_NAME AS 'Task Name',
	COUNT(CREW_MEMBER.TASK_ID) as 'Number of Times Worked', CREW_MEMBER.SP_ID AS 'SP ID',
	SP_LNAME AS 'Last Name', SP_FNAME AS 'First Name'
FROM CREW_MEMBER
	JOIN TASK ON TASK.TASK_ID = CREW_MEMBER.TASK_ID
	JOIN SHOW_PERSONNEL ON SHOW_PERSONNEL.SP_ID = CREW_MEMBER.SP_ID
WHERE CREW_MEMBER.TASK_ID = '001'
GROUP BY CREW_MEMBER.TASK_ID, TASK_NAME, CREW_MEMBER.SP_ID, SP_LNAME, SP_FNAME
ORDER BY COUNT(CREW_MEMBER.TASK_ID) DESC

--QUESTION 10: Which volunteers worked the most unique tasks and had the most work hours?
SELECT SHOW_PERSONNEL.SP_ID AS 'Volunteer ID', SP_LNAME AS 'Last Name', SP_FNAME AS 'First Name',
	COUNT(DISTINCT CREW_MEMBER.TASK_ID) AS 'Number of Unique Tasks Worked',
	SP_TOTAL_HOURS AS 'Total Hours Worked'
FROM CREW_MEMBER
	JOIN TASK ON TASK.TASK_ID = CREW_MEMBER.TASK_ID
	JOIN SHOW_PERSONNEL ON SHOW_PERSONNEL.SP_ID = CREW_MEMBER.SP_ID
WHERE SP_IS_VOLUNTEER = 1
GROUP BY SHOW_PERSONNEL.SP_ID, SP_TOTAL_HOURS, SP_LNAME, SP_FNAME
ORDER BY COUNT(DISTINCT CREW_MEMBER.TASK_ID) DESC, SP_TOTAL_HOURS DESC

--QUESTION 11: How many volunteers worked on each show?
--Counting TOTAL volunteers
SELECT Subquery.SHOW_ID AS 'Show ID', SUM(CAST(Subquery.CM_IS_VOLUNTEER AS INT)) AS Volunteers
FROM
	(SELECT SHOOT_LIST.SHOW_ID AS SHOW_ID, SHOOT_LIST.EP_NUM, SHOOT_LIST.SHOOT_ID, SP_ID,
		CM_IS_VOLUNTEER, (CASE WHEN CM_IS_VOLUNTEER = 0 THEN 1 ELSE 0 END) AS CM_IS_STAFF
	FROM CREW_MEMBER
		LEFT JOIN SHOOT ON SHOOT.SHOOT_ID = CREW_MEMBER.SHOOT_ID
		LEFT JOIN SHOOT_LIST ON SHOOT.SHOOT_ID = SHOOT_LIST.SHOOT_ID
		LEFT JOIN EPISODE ON EPISODE.EP_NUM = SHOOT_LIST.EP_NUM
		JOIN SHOW ON SHOW.SHOW_ID = EPISODE.SHOW_ID
	GROUP BY SHOOT_LIST.SHOW_ID, SHOOT_LIST.EP_NUM, SHOOT_LIST.SHOOT_ID, SP_ID,
		CM_IS_VOLUNTEER) as Subquery
GROUP BY Subquery.SHOW_ID
ORDER BY SUM(CAST(Subquery.CM_IS_VOLUNTEER AS INT)) DESC

--QUESTION 11: How many volunteers worked on each show?
--Counting DISTINCT volunteers
SELECT Subquery.SHOW_ID AS 'Show ID', SUM(CAST(Subquery.CM_IS_VOLUNTEER AS INT)) AS Volunteers
FROM
	(SELECT DISTINCT SP_ID, SHOOT_LIST.SHOW_ID AS SHOW_ID, CM_IS_VOLUNTEER
	FROM CREW_MEMBER
		LEFT JOIN SHOOT ON SHOOT.SHOOT_ID = CREW_MEMBER.SHOOT_ID
		LEFT JOIN SHOOT_LIST ON SHOOT.SHOOT_ID = SHOOT_LIST.SHOOT_ID
		LEFT JOIN EPISODE ON EPISODE.EP_NUM = SHOOT_LIST.EP_NUM
		JOIN SHOW ON SHOW.SHOW_ID = EPISODE.SHOW_ID
	GROUP BY SP_ID, SHOOT_LIST.SHOW_ID, CM_IS_VOLUNTEER) as Subquery
GROUP BY Subquery.SHOW_ID
ORDER BY SUM(CAST(Subquery.CM_IS_VOLUNTEER AS INT)) DESC

--QUESTION 12: Which shows staffed more volunteers vs staff?
--Counting TOTAL number of staff/volunteers working on each show:
SELECT Subquery.SHOW_ID AS 'Show ID', SUM(CAST(Subquery.CM_IS_VOLUNTEER AS INT)) AS Volunteers,
	SUM(CAST(Subquery.CM_IS_STAFF AS INT)) AS Staff
FROM
	(SELECT SHOOT_LIST.SHOW_ID AS SHOW_ID, SHOOT_LIST.EP_NUM, SHOOT_LIST.SHOOT_ID, SP_ID,
		CM_IS_VOLUNTEER, (CASE WHEN CM_IS_VOLUNTEER = 0 THEN 1 ELSE 0 END) AS CM_IS_STAFF
	FROM CREW_MEMBER
		LEFT JOIN SHOOT ON SHOOT.SHOOT_ID = CREW_MEMBER.SHOOT_ID
		LEFT JOIN SHOOT_LIST ON SHOOT.SHOOT_ID = SHOOT_LIST.SHOOT_ID
		LEFT JOIN EPISODE ON EPISODE.EP_NUM = SHOOT_LIST.EP_NUM
		JOIN SHOW ON SHOW.SHOW_ID = EPISODE.SHOW_ID
	GROUP BY SHOOT_LIST.SHOW_ID, SHOOT_LIST.EP_NUM, SHOOT_LIST.SHOOT_ID, SP_ID,
		CM_IS_VOLUNTEER) as Subquery
GROUP BY Subquery.SHOW_ID
ORDER BY SUM(CAST(Subquery.CM_IS_VOLUNTEER AS INT)) DESC

--QUESTION 12: Which shows staffed more volunteers vs staff?
--Counting DISTINCT number of staff/volunteers working on each show:
SELECT Subquery.SHOW_ID AS 'Show ID', SUM(CAST(Subquery.CM_IS_VOLUNTEER AS INT)) AS Volunteers,
	SUM(CAST(Subquery.CM_IS_STAFF AS INT)) AS Staff
FROM
	(SELECT DISTINCT SP_ID, SHOOT_LIST.SHOW_ID AS SHOW_ID,
		CM_IS_VOLUNTEER, (CASE WHEN CM_IS_VOLUNTEER = 0 THEN 1 ELSE 0 END) AS CM_IS_STAFF
	FROM CREW_MEMBER
		LEFT JOIN SHOOT ON SHOOT.SHOOT_ID = CREW_MEMBER.SHOOT_ID
		LEFT JOIN SHOOT_LIST ON SHOOT.SHOOT_ID = SHOOT_LIST.SHOOT_ID
		LEFT JOIN EPISODE ON EPISODE.EP_NUM = SHOOT_LIST.EP_NUM
		JOIN SHOW ON SHOW.SHOW_ID = EPISODE.SHOW_ID
	GROUP BY SP_ID, SHOOT_LIST.SHOW_ID, CM_IS_VOLUNTEER) as Subquery
GROUP BY Subquery.SHOW_ID
ORDER BY SUM(CAST(Subquery.CM_IS_VOLUNTEER AS INT)) DESC

--QUESTION 13: How many shows do we have?
SELECT COUNT(DISTINCT SHOW_ID) AS 'Number of Shows'
FROM SHOW

--QUESTION 14: Which station produced the most shows?
SELECT TOP 1 WITH TIES STATION.STN_ID AS 'Station ID',
	COUNT(PRODUCTION.SHOW_ID) AS 'Number of Shows Produced From Station'
FROM STATION
	JOIN ACCESS_PRODUCER ON ACCESS_PRODUCER.STN_ID = STATION.STN_ID
	JOIN PRODUCTION ON PRODUCTION.AP_ID = ACCESS_PRODUCER.AP_ID
GROUP BY STATION.STN_ID
ORDER BY COUNT(PRODUCTION.SHOW_ID) DESC

--QUESTION 15: What season is X show on?
SELECT SHOW.SHOW_ID AS 'Show ID', SHOW_NAME AS 'Show Name',
	MAX(SSN_NUM) AS 'Most Recent Season'
FROM SEASON
	JOIN SHOW ON SHOW.SHOW_ID = SEASON.SHOW_ID
GROUP BY SHOW.SHOW_ID, SHOW_NAME
ORDER BY MAX(SSN_NUM) DESC

--QUESTION 16: What show has 3 or more seasons?
SELECT SHOW.SHOW_ID AS 'Shows With 3 or More Seasons', SHOW_NAME AS 'Show Name'
FROM SEASON
	JOIN SHOW ON SHOW.SHOW_ID = SEASON.SHOW_ID
WHERE SSN_NUM >= 3
GROUP BY SHOW.SHOW_ID, SHOW_NAME
ORDER BY MAX(SSN_NUM) DESC

--QUESTION 17: What are the occupations of the volunteers?
SELECT OCC_NAME AS 'Occupation', SP_ID AS 'Volunteer ID',
	SP_LNAME AS 'Last Name', SP_FNAME AS 'First Name'
FROM SHOW_PERSONNEL
	JOIN OCCUPATION ON OCCUPATION.OCC_ID = SHOW_PERSONNEL.OCC_ID
WHERE SP_IS_VOLUNTEER = 1
GROUP BY OCC_NAME, SP_ID, SP_LNAME, SP_FNAME
ORDER BY OCC_NAME

--QUESTION 18: What volunteers are students?
SELECT SP_ID AS 'SP ID of Student', SP_LNAME AS 'Last Name', SP_FNAME AS 'First Name'
FROM SHOW_PERSONNEL
	JOIN OCCUPATION ON OCCUPATION.OCC_ID = SHOW_PERSONNEL.OCC_ID
WHERE SP_IS_VOLUNTEER = 1 AND SHOW_PERSONNEL.OCC_ID = '002'

--QUESTION 19: How many shows were produced by X producer?
SELECT PRODUCTION.AP_ID AS 'Acess Producer ID', AP_LNAME AS 'AP Last Name',
	AP_FNAME AS 'AP First Name', COUNT(PRODUCTION.AP_ID) AS 'Number of Shows Produced'
FROM PRODUCTION
	JOIN ACCESS_PRODUCER ON ACCESS_PRODUCER.AP_ID = PRODUCTION.AP_ID
GROUP BY PRODUCTION.AP_ID, AP_LNAME, AP_FNAME
ORDER BY COUNT(PRODUCTION.AP_ID) DESC

--QUESTION 20: What is the average age of the volunteers?
SELECT AVG(SP_AGE) AS 'Average Age of Volunteers'
FROM SHOW_PERSONNEL
WHERE SP_IS_VOLUNTEER = 1

--QUESTION 21: What are the birthdays of the volunteers?
SELECT SP_ID as 'Volunteer ID', SP_LNAME AS 'Last Name', 
	SP_FNAME AS 'First Name', SP_DOB AS 'Birthday'
FROM SHOW_PERSONNEL
WHERE SP_IS_VOLUNTEER = 1
ORDER BY SP_DOB

--QUESTION 22: What volunteers are over 35?
SELECT SP_ID as 'Volunteer ID', SP_LNAME AS 'Last Name', 
	SP_FNAME AS 'First Name', SP_AGE AS 'Age'
FROM SHOW_PERSONNEL
WHERE SP_IS_VOLUNTEER = 1 AND SP_AGE > 35
ORDER BY SP_AGE DESC

--QUESTION 23: What staff members are former volunteers?
SELECT SHOW_PERSONNEL.SP_ID AS 'SP ID of Former Volunteer', SP_LNAME AS 'Last Name', 
	SP_FNAME AS 'First Name'
FROM SHOW_PERSONNEL
	JOIN CREW_MEMBER ON CREW_MEMBER.SP_ID = SHOW_PERSONNEL.SP_ID
	JOIN SHOOT ON SHOOT.SHOOT_ID = CREW_MEMBER.SHOOT_ID
WHERE SP_IS_VOLUNTEER = 0 AND CM_IS_VOLUNTEER = 1
GROUP BY SHOW_PERSONNEL.SP_ID, SP_LNAME, SP_FNAME

--QUESTION 24: How many episodes of X show were shot?
SELECT SHOW.SHOW_ID AS 'Show ID', SHOW_NAME AS 'Show Name',
	COUNT(EP_NUM) AS 'Number of Episodes'
FROM EPISODE
	JOIN SHOW ON SHOW.SHOW_ID = EPISODE.SHOW_ID
GROUP BY SHOW.SHOW_ID, SHOW_NAME
ORDER BY COUNT(EP_NUM) DESC