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

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)

From the tables above, translate the following from English into SQL

1.  List the student id, name, and completed credit hours of all freshman born in or after 1976.

2. List the IDs and Mentor IDs of students who are taking some course, offered by their mentor.

Explanation / Answer

1. List the student id, name, and completed credit hours of all freshman born in or after 1976.
SELECT s.StudentID, p.name, s.CreditHours
FROM student s INNER JOIN person p ON s.StudentID = p.ID
WHERE UPPER(s.Classification) = 'FRESHMAN'
AND to_number(to_char(p.DOB, 'YYYY')) > 1976;

2. List the IDs and Mentor IDs of students who are taking some course, offered by their mentor.
SELECT s.StudentID, s.MentorID
FROM student s, enrollment e, offering o
WHERE s.StudentID = e.StudentID
AND s.MentorID = o.InstructorID
AND e.CourseCode = o.CourseCode;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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