The following schemas describe Presidents, Judges and the appointment relationsh
ID: 3590763 • Letter: T
Question
The following schemas describe Presidents, Judges and the appointment relationships between them.
Presidents(PName : string, PDateOfBirth : int, Party : string, HomeState : string)
Judges(JName : string, JDateOfBirth : int, LawSchool : string)
Appoints(PName : string, JName : string, Date : integer)
Formulate the following queries on these schemas using SQL
Retrieve the names of all the presidents.
Retrieve the names of all the judges graduated from Yale or Harvard.
Retrieve the names of the presidents who appointed judges from both Yale and Harvard?
Retrieve the pairs of names of judges attended the same law school
List the number of judges graduated from each law school.
What are the political parties whose presidents only appointed judges from Yale
What are the names of the presidents who appointed exactly two judges?
What are the names of the presidents who never appointed a judge?
Retrieve the names of judges that were appointed by more than 2 presidents.
List the oldest of judges graduated from each law school appointed by presidents in the Republican Party.
Explanation / Answer
SELECT UNIQUE PName FROM Presidents; SELECT UNIQUE JName FROM Judges WHERE LawSchool IN ( 'Yale' , 'Harvard' ); SELECT UNIQUE PName FROM Appoints WHERE JName IN (SELECT UNIQUE JName FROM Judges WHERE LawSchool IN ( 'Yale' , 'Harvard' ) ) ; SELECT UNIQUE JName FROM Judges ORDER BY LawSchool ; SELECT COUNT(JName), LawSchool FROM Judges GROUP BY LawSchool ; SELECT UNIQUE Party FROM Presidents WHERE PName IN ( SELECT UNIQUE PName FROM Appoints WHERE JName NOT IN (SELECT UNIQUE JName FROM Judges WHERE LawSchool NOT IN ( 'Yale' ) ) ; SELECT PName FROM Appoints GROUP BY PName HAVING COUNT(JName) = 2 ; SELECT PName FROM Presidents WHERE PName NOT IN( SELECT PName FROM Appoints ) ; SELECT JName FROM Appoints GROUP BY PName HAVING COUNT(PName) > 2 ; SELECT JName FROM Judges GROUP BY LawSchool HAVING MIN(JDateOfBirth) AND JName IN ( SELECT JName FROM Appoints WHERE PName IN ( SELECT PName FROM Presidents WHERE Party IN ( 'REPUBLICAN PARTY' ) ) ) ;