/* 
 * A script to test the queries for President/Justice database
 */

use justice;

-- Retrieve names of all Presidents
 
SELECT PName 
FROM Presidents;


-- "Retrieve the names of judges from Yale or Harvard" 
 
SELECT JName 
FROM Judges
WHERE LawSchool LIKE 'Harvard'
OR LawSchool LIKE 'Yale';


--  "Retrieve the names of Presidents who appointed judges from both Yale and Harvard"
 
SELECT PName
FROM Presidents
WHERE PName IN (
		SELECT PName
		FROM Judges j INNER JOIN Appoints a ON j.JName LIKE a.JName
		WHERE LawSchool LIKE 'Yale' 
		)
AND PName IN (
		SELECT PName
		FROM Judges j INNER JOIN Appoints a ON j.JName LIKE a.JName
		WHERE LawSchool LIKE 'Harvard'
	     );


-- "Pairs of Judges who attended the same law school" 

SELECT j1.JName, j2.JName
FROM Judges j1 INNER JOIN Judges j2 ON j1.LawSchool LIKE j2.LawSchool
WHERE j1.JName NOT LIKE j2.JName
AND j1.JName < j2.JName;


--  "Number of judges graduated from each law school" 

SELECT LawSchool, COUNT(*)
FROM Judges
GROUP BY LawSchool;


-- "Political parties whose presidents only appointed judges from yale" 

SELECT DISTINCT Party
FROM Presidents
WHERE PName IN (SELECT PName
		FROM Appoints a INNER JOIN Judges j ON a.JName LIKE j.JName
		WHERE LawSchool LIKE 'Yale')
AND PName NOT IN (SELECT PName
		  FROM Appoints a INNER JOIN Judges j ON a.JName LIKE j.JName
		  WHERE LawSchool NOT LIKE 'Yale');


-- "Presidents who appointed exactly 2 judges" 

SELECT PName
FROM Appoints
GROUP BY PName
HAVING COUNT(*) = 2;


-- "Presidents who never appointed a judge" 

SELECT PName
FROM Presidents
WHERE PName NOT IN (
		   SELECT  PName
		   FROM Appoints
		   GROUP BY PName
		   HAVING COUNT(*) > 0
		   );


-- "List oldest judges from each law school appointed by presidents in Republican party"

SELECT j.JName, j.LawSchool, DOB_TABLE.min_dob
FROM Judges j INNER JOIN
(SELECT LawSchool, MIN(JDateOfBirth) as min_dob
FROM Judges
WHERE JName IN ( 
		SELECT JName
		FROM Presidents p INNER JOIN Appoints a ON p.PName LIKE a.PName
		WHERE p.Party LIKE 'Republican'
     		)
GROUP BY LawSchool) AS DOB_TABLE
ON j.JDateOfBirth = DOB_TABLE.min_dob
AND j.LawSchool LIKE DOB_TABLE.LawSchool;
