Question

Consider the following relations: Pupil(student Num Integer, studentName: String, Degree: String, Year: String, Age: Integer) Course(course Name: String, classTime: String, classroom: String, facultylD: Integer) Registered(student Num: Integer.courseName: String) Faculty(facultyID: Integet, faculty Name: String, departmentid: Integer) Attributes of the Pupil relation The student number is an 8 digit code, for example, 98712201. The student name is a combination of the first and last name, for example, Mary Jane. The degree option can be any program offered by the university, for example, Physies or Mathematics. Year has values: Y1 (year 1), Y2 (year 2) or Y3 (year Attributes of the Course relation: Course name is a 5 digit code, for example, CS11A or MS10A. Class time is a 15 digit string, for example, 09:00AM-11:00AM. Classroom names are a letter followed by three digits, for example, A123 The faculty ID is a 4 digit code, for example, 1001 (Natural Sciences). Attributes of the Registered relation: The student number is as described in the Pupil relation. The course name is as described in the Course relation Note: Registered has one record per pupil-course pair such that the pupil is registered in the course. Attributes of the Faculty relation: The faculty ID is as described in the Course relation. The faculty name is a 6 letter code, for example, Nat Sci (Natural Sciences). The department ID is a 5 digit code, for example, 12345.
FACULTY RELATION REPLACED WITH TEACHER ; FACULTYID REPLACED WITH TEACHERID ; FACULTYNAME REPLACED WITH TEACHERNAME

Answer 1

If you have any doubts, please give me comment...

-- 4)

SELECT DISTINCT P.studentName

FROM Pupil P

WHERE P.studentNum IN (

SELECT R1.studentNum

FROM Registered R1, Registered R2, Course C1, Course C2

WHERE R1.studentNum = R2.studentNum AND R1.courseName <> R2.courseName

AND R1.courseName = C1.courseName

AND R2.courseName = C2.courseName AND C1.classTime = C2.classTime)

-- 5.

SELECT DISTINCT T.teacherID

FROM Teacher T

WHERE NOT EXISTS (

( SELECT * FROM Course C) EXCEPT

(SELECT C1.classroom

FROM Course C1

WHERE C1.teacherID = T.teacherID)

);

--6)

SELECT DISTINCT T.teacherName

FROM Teacher T

WHERE 5 > (

SELECT COUNT (R.studentNum)

FROM Course C, Registered R

WHERE C.courseName = R.courseName

AND C.teacherID = T.teacherID

);

-- 7.

SELECT P.Year, AVG(P.Age)

FROM Pupil P

GROUP BY P.Year;

-- 8.

SELECT P.Year, AVG(P.Age)

FROM Pupil P

WHERE P.Year <> 'Y1'

GROUP BY P.Year;

-- 9.

SELECT T.teacherName, COUNT(*) AS CourseCount

FROM Teacher T, Course C

WHERE T.teacherID = C.teacherID

GROUP BY T.teacherID, T.teacherName

HAVING EVERY(C.classroom = 'J341');

-- 10.

SELECT DISTINCT P.studentName

FROM Pupil P

WHERE P.studentNum IN (

SELECT R.studentNum

FROM Registered R

GROUP BY R.studentNum

HAVING COUNT (*) >= ALL (

SELECT COUNT (*)

FROM Registered R2

GROUP BY E2.studentNum)

);

-- 11.

SELECT DISTINCT P.studentName

FROM Pupil P

WHERE P.studentNum NOT IN (

SELECT R.studentNum

FROM Registered R

);

-- 12.

SELECT P.Age, P.Year

FROM Pupil P

GROUP BY P.Age, P.Year

HAVING P.Year IN (

SELECT S1.Year FROM Pupil P1

WHERE S1.Age = P.Age

GROUP BY S1.Year, S1.Age

HAVING COUNT (*) >= ALL (

SELECT COUNT (*)

FROM Pupil P2

WHERE s1.Age = S2.Age

GROUP BY S2.Year, S2.Age

)

);