Formulate the following queries on these schemas in SQL. The following schemas d
ID: 3778985 • Letter: F
Question
Formulate the following queries on these schemas in SQL.
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)
a) Retrieve the names of all the presidents.
b) Retrieve the names of all the judges graduated from Yale or Harvard.
c) Retrieve the names of the presidents who appointed judges from both Yale and Harvard?
d) List the number of judges graduated from each law school.
e) Retrieve the pairs of names of judges attended the same law school
f) What are the political parties whose presidents only appointed judges from Yale
g) What are the names of the presidents who appointed exactly two judges?
h) What are the names of the presidents who never appointed a judge?
i) Retrieve the names of judges that were appointed by more than 2 presidents.
j) List the oldest of judges graduated from each law school appointed by presidents in the Republican Party.
Explanation / Answer
a) select pname from presidents;
b) select jname from judges where laswchool='Yale' or lawschool='Harvard'
c) select distinct pname from ,judges,appoints where appoints.jname=judges.jname and (laswchool='Yale' or lawschool='Harvard')
d) select count(*),lawschool from judges group by lawschool