Your Question:

Question \#02: Total The following relations keep track of university database information: Student(snum: integer, sname: string, major: string, level: string, age: integer) Class( name: string, meets_at: time, room: 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 answers. 1. Find the names of all Juniors (level = JR) who are enrolled in a class taught by I. Teach. 2. Find the age of the oldest student who is either a History major or enrolled in a course taught by I. Teach. 3. Find the names of all classes that either meet in room R128 or have five or more students enrolled. 4. Find the names of all students who are enrolled in two classes that meet at the same time. 5. Find the names of faculty members who teach in every room in which some class is taught. 6. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five. 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, find 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)

Step By Step Answers with Explanation

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

JOIN Class C ON E.cname = C.name

JOIN Faculty F ON C.fid = F.fid

```sql

SELECT MAX(age) AS oldest_age

JOIN Class C ON E.cname = C.name

JOIN Faculty F ON C.fid = F.fid

```sql

SELECT DISTINCT C.name

Explanation: This query uses a LEFT JOIN to connect the Class and Enrolled tables. It selects class names that either meet in 'R128' or have five or more students enrolled.

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

JOIN Enrolled E2 ON S.snum = E2.snum

JOIN Class C1 ON E1.cname = C1.name

Query 5: Find the names of faculty members who teach in every room in which some class is taught.

```sql

HAVING COUNT(DISTINCT C.room) = (SELECT COUNT(DISTINCT room) FROM Class);

```

FROM Faculty F

JOIN Class C ON F.fid = C.fid

Explanation: This query connects the Faculty, Class, and Enrolled tables. It groups the results by faculty and calculates the combined enrollment. It selects faculty with enrollments less than five.

Query 7: Print the level and the average age of students for each level.

```

Explanation: This simple query groups students by their level and calculates the average age for each level.

WHERE level <> 'JR'

GROUP BY level;

SELECT F.fname, COUNT(*) AS total_classes_taught

FROM Faculty F

```

Explanation: This query finds faculty members who have taught classes only in 'R128' and calculates the total classes they've taught in that room.

JOIN (SELECT snum, COUNT(*) AS num_of_classes

FROM Enrolled

Explanation: This query first counts the number of classes each student is enrolled in and identifies the maximum count. Then, it selects students who are enrolled in that maximum number of classes.

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

```

Explanation: This query selects students whose student numbers do not appear in the Enrolled table, which means they are not enrolled in any class.

FROM Student

GROUP BY age, level

GROUP BY age

)

Explanation: This query calculates, for each age value, the level value that appears most often in the Student table. It uses common table expressions (CTEs) for better readability.