Consider the following relations: Pupil(student Num Integer, studentName: String
ID: 3904607 • Letter: C
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.Explanation / Answer
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
)
);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.