Write the SQL Command from MS SQL Server to answer the following questions. Ther
ID: 3715050 • Letter: W
Question
Write the SQL Command from MS SQL Server to answer the following questions.
There are two assumptions in this database;
All courses start with the prefix of ISM, meaning that this database only capture courses for ISM program.
I-2000 means the first semester of year 2000 and II-2000 means the second semester of year 2000. There are only 2 semesters in a year.
1.What is the name of students that has/have the highest number of sections enrolled?
2.Which course (s) has/have the highest number of student enrolled in I-2001 semester (identify COURSE_ID)? The course that did not have enrollment at all is not included.
3.What is/are the name(s) of instructor(s) who can teach the fewest number of courses and how many courses can they teach?
Faculty_ID Faculty Name Faculty_ID Course ID ate ualifie SM 3100 |Programmi ISM 3113 | 9,188 4756 Collins 5233 Potters SM 3150 Adv Progr ammin ISM 4212 Database 6325 Mayers ISM4930 Networkin M6100 Web Programmin ISM 4930 911199 ISM 6500 Know Mamt ISM8745 Seminar in l Registered emester I-2001 8759 8759 45874 54907 45874 45874 45874 54907 54907 54907 66324 66324 66324 66324 70542 70542 716 1-1999 ISM 2000 2 89754 2724Explanation / Answer
//selects student name who enrooled maximum no if sections
1)SELECT Student.Student_Name
WHERE MAX(COUNT(Student.Studen_ID))
FROM (Student
INNER JOIN Student ON Student.Student_ID==Is_Registered.Student_ID)
//select the maximum registered course id
2)SELECT Section.Course_ID
WHERE MAX(COUNT(Section.Course_ID) AND Is_Registered.Semester=='I2001'))
FROM (Section
INNER JOIN Section ON Section.Course_ID==Is_Registered.Course_ID)
//elects minimum count of faculty name teaches from table
3)SELECT Table1.Facutly_Name
WHERE MIN(COUNT(Table3.Faculty_ID))
FROM (Table1
INNER JOIN Table.Faculty_ID== Table3.Faculty_ID)
//1st tableis consider as table1 , next as tabel 2, and next as table 3
//for any clarification do comments..
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.