The figure below shows a sample database containing information about:· Students and their registrations· Courses and their sections· Faculty and their qualified coursesThe sample data is shown in each table for your understanding. Write SQL DML commands to do the following tasks:List the IDs of all faculty members who are qualified to teach both ISM 3112 and ISM 3113.Modify the query above in part 16 so that both qualifications must have been earned after the year 2005.List the ID of the faculty member who has been assigned to teach ISM 4212 during the semester II-2015.Display the course ID and course name for all courses with an ISM prefix.Display all courses for which Professor Berndt has been qualified.Display the class roster, including student name, for all students enrolled in section 2714 of ISM 4212. Class roster means attendance list.Which instructors are qualified to teach ISM 3113?Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930?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?How many students were enrolled in ISM 3113 during semester I-2015?Which students were not enrolled in any courses during semester I-2015?Find the name of faculty member who taught ISM 3113 course in I-2015 semester. 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. STUDENT (StudentID, StudentName)QUALIFIED (FacultylD. CourselD, DateQualified)StudentIDStudentNameFacultylDCourselDDateQualifiedIŠM 3112ISM 311338214LeterskyAltvater21439/20055490721439/2005ISM 4212IŠM 4930ISM 3113ISM 311266324AikenMarra34679/20129/201370542346747569/20089/20084756FACULTY (FacultylD, FacultyName)SECTION (SectionNo, Semester, CourselD)FacultyIDFacultyNameSectionNoSemesterCourselDISM 3113ISM 3113ISM 42122143Birkin2712-20153467Berndt2713-2015Il-20154756Collins27142715Il-2015ISM 4930COURSE (CourselD, CourseName)REGISTRATION (StudentID, SectionNo)CourselDStudentIDCourseNameSectionNoISM 3113Syst Analysis382142714ISM 3112IŠM 4212ISM 4930Syst DesignDatabase549072714549072715Networking663242713

Answered: he figure below shows a sample database…

Np Ms Office 365/Excel 2016 I Ntermed

1st Edition

ISBN:9781337508841

Author:Carey

Publisher:Carey

Chapter7: Developing An Excel Application

Section: Chapter Questions

Problem 3RA

See similar textbooks

Related questions

Concept explainers

Question

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:

List the IDs of all faculty members who are qualified to teach both ISM 3112 and ISM 3113.
Modify the query above in part 16 so that both qualifications must have been earned after the year 2005.
List the ID of the faculty member who has been assigned to teach ISM 4212 during the semester II-2015.
Display the course ID and course name for all courses with an ISM prefix.
Display all courses for which Professor Berndt has been qualified.
Display the class roster, including student name, for all students enrolled in section 2714 of ISM 4212. Class roster means attendance list.
Which instructors are qualified to teach ISM 3113?
Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930?
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?
How many students were enrolled in ISM 3113 during semester I-2015?
Which students were not enrolled in any courses during semester I-2015?
Find the name of faculty member who taught ISM 3113 course in I-2015 semester.

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.

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

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.