Exercise 5.1  Consider the following  relations:

 

Student(snum: integer, sname:  string,   string,  level:  string,  age:   integer) Class( name: string, meets_at: time,                                                                                string,  fid:   integer)

Enrolled(snum: integer, cname:  string)

Faculty (fid:   integer, fnarne:   string,  deptid:  integer)

 

The meaning of these relations is straightforward; for example, Enrolled has one record per student-class pair such  that  the  student is  enrolled  in  the class.

 

Write the following  queries in  SQL.  No  duplicates should  be  printed in any of the   ans\vers.

 

1.    Find the nari1es of all Juniors (level = JR) who are enrolled in a class taught by 1. Teach.

2.    Find the age of the oldest student who is either a History major or enrolled in a  course taught by I. Teach.

  Find the names of all  classes  that either meet  in  room R128 or have or  more  students enrolled.

4.    Find   Ilames of all students who are enrolled  in  two  classes  that  meet  at  the same time.

 

 

 

 Find   names  of  faculty  members  \vho  teach  in  every  room  in   some class is taught.

6.    Find the names of faculty members for  \vhorn  the  combined enrollment  of the  courses that they teach is less than

7.    Print the level and the average age of students for that level, for each level.

8.  Print the level and the average age of students for that level, for  all levels except    JR.

9.    For each faculty member that has taught classes only in room R128, print the faculty member's name and the total number of classes she or he has taught.

10.    Find the names of students enrolled in the maximum number of classes.

11.    Find the names of students not enrolled in any class.

12.    For each age value that appears in Students, the level value that appears most often. For example, if there are more FR level students aged 18 than SR, JR, or SO students aged 18, you should print the pair (18, FR).