The following schemas describe Presidents, Judges and the appointment relationsh

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' ) ) ) ;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.