STUDENT StudentID StudentName StudentID StudentName Letersk

STUDENT (StudentID. StudentName) StudentID StudentName Letersk 54907 66324 70542 FACULTY (Faculty ID, FacultyName) Faculty Name Birkin 2143 3467 Berndt 4756 Collins COURSE (Course ID, CourseName) Coursel D Course Name ISM 3113 Syst Analysis SM 3112 Syst Design ISM 4212 Database ISM 4930 Networking QUALIFIED (FacultyID, CourselD, Dateaualified) Facul CourselD Dateaualified ISM 3112 ISM 3113 ISM 4212 9/2012 ISM 4930 9/2008 4756 ISM 3113 4756 ISM 3112 SECTION (SectionNo, Semester, CourselD) Section No Semester Course ID ISM 3113 2712 2015 ISM 3113 27 13 2015 ISM 4212 2714 2015 ISM 4930 2715 2015 REGISTRATION StudentID, SectionNo) Section No 38214 2714 54907 2714 54907 2715 66324 2713

Solution

/** What are the courses included in the Section table? List each course only once */ SELECT DISTINCT course.CourseName FROM section, course WHERE section.CourseID = course.CourseID; /*** List all students in alphabetical order by StudentName */ SELECT StudentID, StudentName FROM student ORDER BY StudentName; /** List the students who are enrolled in each course in Semester I, 2015 */ SELECT st.StudentID, st.StudentName, s.SectionNo, s.Semester FROM student st, registration r, section s WHERE r.StudentID = st.StudentID AND r.SectionNo = s.SectionNo AND s.Semester = \"I-2015\"; /** Group the students by the sections in which they are enrolled */ SELECT count(*) AS \'count\', SectionNo FROM registration GROUP BY SectionNo; /** List the courses available */ SELECT CourseName FROM course; /** Group them by course prefix */ SELECT count(c.CourseName) AS \'courses\', c.prifix FROM (SELECT CourseName, substr(CourseID, 1, 4) AS \'prifix\' FROM course) AS c GROUP BY c.prifix;