Consider the following relations Pupil(studentNum Integer, studentName: String,

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 registered

Explanation / Answer

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

-- 1)

SELECT studentName

FROM Pupil P, Course C, Registered R, Teacher T

WHERE P.studentNum = R.studentNum AND R.courseName = C.courseName AND C.TeacherID = T.TeacherID AND Year = 'Y1' AND teacherName = 'John';

-- 2)

SELECT MAX(age)

FROM Pupil P, Course C, Registered R, Teacher T

WHERE (P.studentNum = R.studentNum AND R.courseName = C.courseName AND C.TeacherID = T.TeacherID AND teacherName = 'John') OR Degree = 'Chemistry' ;

-- 3)

SELECT C.courseName

FROM Course

WHERE classroom = 'J341' OR courseName IN(

SELECT courseName

FROM Registered

GROUP BY courseName

HAVING COUNT(*)>=15

);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.