Consider the following relations Pupil(studentNum Integer, studentName: String,
ID: 3904154 • Letter: C
Question
Consider the following relations Pupil(studentNum Integer, studentName: String, Degree: String, Year: String, Age: Integer) Name.Sitring, class Time: String, classroom: String, facultylD: Integer) Registered Faculty(facultyID: Integet,faculty Name: String, departmentld: Integer) ntNum: Integer, courneName: String) Attributes of the Pupil relation: The student number is an & 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, Physics 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. Write the following queries in SQ)L. No duplicates should be printed in any of the answers 1. Find the names of all undergraduates (Year -Yl) who are registered in a course taught by John 2. Find the age of the oldest pupil who is either doing a Chemistry degree or registered in a course taught by John. 3. Find the names of all courses that either meet in classroom 1341 or have fifteen or more pupils registeredExplanation / Answer
here is your two quesion answer : ----------->>>>>>>>
Question 1 :
SELECT studentName FROM (
SELECT * FROM pupil WHERE year = Y1
) AS P INNER JOIN (
SELECT * FROM registered INNER JOIN
(
SELECT * FROM Course INNER JOIN
(
SELECT * FROM faculty WHERE facultyName = 'john'
) AS R ON R.facultyId = Course.facultyId
) AS C ON C.courseName = registered.courseName
) AS R1 ON R1.studentNum = P.studentNum;
Question 2:
SELECT age FROM (
SELECT age FROM pupil
WHERE pupil.degree = 'chemestry' OR studentNum IN (
SELECT studentNum FROM pupil AS P INNER JOIN (
SELECT * FROM registered INNER JOIN
(
SELECT * FROM Course INNER JOIN
(
SELECT * FROM faculty WHERE facultyName = 'john'
) AS R ON R.facultyId = Course.facultyId
) AS C ON C.courseName = registered.courseName
) AS R1 ON R1.studentNum = P.studentNum;
)
ORDER BY age DESC
)
WHERE ROWNUM <= 1;