-Person (Name, ID, Address, DOB) - Instructor (InstructorID, Rank, Salary) - Stu

Question

-Person (Name, ID, Address, DOB)
- Instructor (InstructorID, Rank, Salary)
- Student (StudentID, Classification, GPA, MentorID, CreditHours)
-Course (CourseCode, CourseName, PreReq)
-Offering (CourseCode, SectionNo, InstructorID)
- Enrollment (CourseCode, SectionNo, StudentID, Grade)
Note:PreReq=Pre-Requirest

From the SQL tables commands above,use the English statement below the write SQL statements:-
(1). Find the IDs, names and DOB of the oldest and the youngest students. Item 20. List the IDs, DOB, and Names of Persons who are neither a student nor a instructor.
(2). List the courses that are prerequisites of prerequisites for the course ‘CS511’. We only want second level prerequisites. You should ignore immediate prerequisites.
(3). List the number of students and average GPA for each classification. Your query should not use constants such as "Freshman".
(4). List the distinct IDs of the students who are taking a course such that some section of that course is taught by their mentors.
(5). List the IDs of students who are taking some course, which is a prerequisite for a course taught by their mentor.
(6). For every classification, list the ID and date of birth of mentors who have students with highest GPA in that classification. Your query should not use constants such as "Freshman".

Explanation / Answer

Dear, 1. select ID, Name, DOB from Person, Student where Person.ID=Student.StudentID and (DOB=(select min(DOB) from Person) or DOB=(selct max(DOB) from Perosn)) select ID,DOB,Name from Person,Student,Instructor where Person.ID <> Student.StudentID and Person.ID <> Student.InstructorD 2. select PreReq from Course where CourseCode= (select PreReq from Course where CourseCode= 'CS511') 3. select count(*) as NumOfStudents, avg(GPA) as AvgGPA from Student GROUP BY Classification 4. select DISTINCT StudentID from Student where MentorID = NULL 5. select StudentID from Student where Student.StudentID=Enrollment.StudentID and Enrollment.CourseCode=Course.CourseCode and PreRqe = ( select PreReq from Course where CourseCode= (select CourseCode from Enrollment where Enrollment.StudentID=Student.StudentID and Student.MentorID <> NULL)) 6. select ID,DOB from Person where Person.ID= Instuctor.InstructorID and Student.MentorID=Instructor.InstructorID and GPA = (select( max(GPA) from Student) GROUP BY Classifiaction

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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