The figure below shows a sample database containing information about:
· Students and their registrations
· Courses and their sections
· Faculty and their qualified courses
The sample data is shown in each table for your understanding.
Write SQL DML commands to do the following tasks:
- What are the names of the course(s) that student Altvater took during the semester I-2015?
- List names of the students who have taken at least one course that Professor Collins is qualified to teach.
- How many students did Professor Collins teach during the semester I-2015?
- How many students were enrolled in section 2714 during semester I-2015?
Note:
If a faculty member has qualified a course, then he is eligible to teach that course.
Moreover, you can also consider the Qualified table as the Faculty Course Allocation table.
Extracted text: STUDENT (StudentID, StudentName) QUALIFIED (FacultylD. CourselD, DateQualified) StudentID StudentName FacultylD CourselD DateQualified IŠM 3112 ISM 3113 38214 Letersky Altvater 2143 9/2005 54907 2143 9/2005 ISM 4212 IŠM 4930 ISM 3113 ISM 3112 66324 Aiken Marra 3467 9/2012 9/2013 70542 3467 4756 9/2008 9/2008 4756 FACULTY (FacultylD, FacultyName) SECTION (SectionNo, Semester, CourselD) FacultyID FacultyName SectionNo Semester CourselD ISM 3113 ISM 3113 ISM 4212 2143 Birkin 2712 -2015 3467 Berndt 2713 -2015 Il-2015 4756 Collins 2714 2715 Il-2015 ISM 4930 COURSE (CourselD, CourseName) REGISTRATION (StudentID, SectionNo) CourselD StudentID CourseName SectionNo ISM 3113 Syst Analysis 38214 2714 ISM 3112 IŠM 4212 ISM 4930 Syst Design Database 54907 2714 54907 2715 Networking 66324 2713