Question

4. Write SQL queries to answer the following questions, a. How many students were enrolled in secoon 14 b. How many students were enrolled in ISM 3113 5. Write an SOL query to answer the following question Which students were not enrolled in any courses on Problems and Exercises 6 through 14 are based on Figure 7-15. The problem set continues from Chapter 6, Problems and Exercises 16 6. Determine the relationships among the four entities Figure 7-15. List primary keys for each entity and a foreign keys necessary to establish the relationships and maintain referential integrity. Pay particular attentions the data contained in TUTOR REPORTS when you set up 264 Part IV. Implementation ing semester 1-2008? semester 1-2008? semester I-2008? Problems and Exercises Problems and Exercises 1 through 5 are based on the class sched- ule 3NF relations along with some sample data in Figure 7-14. For Problems and Exercises through 5, draw a Venn or ER diagram and mark it to show the data you expect your query to use to produce the results, 1. Write SQL retrieval commands for each of the following queries a. Display the course ID and course name for all courses with an ISM prefix b. Display all courses for which Professor Berndt has been qualified, c. Display the class roster, including student name, for all students enrolled in section 2714 of ISM 4212. 2. Write an SQL query to answer the following question: Which instructors are qualified to teach ISM 3113? 3. Write an SQL query to answer the following question: Is any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930 through 15, which were based on Figure 6-11. its primary key. QUALIFIED (FacultyID, CourselD, Date Qualified) STUDENT (StudentID, StudentName) FacultyID CourselD Date Qualified StudentID Student Name 9/1988 38214 54907 66324 70542 Letersky Altvater Aiken Marra 2143 2143 3467 3467 4756 4756 ISM 3112 ISM 3113 ISM 4212 ISM 4930 ISM 3113 ISM 3112 2/1988 9/1995 9/1996 9/1991 9/1991 FACULTY (FacultyID, FacultyName) SECTION (SectionNo, Semester, CourselD) FacultyID Faculty Name Section No Semester CourselD 2712 2143 3467 4756 Birkin Berndt Collins 2713 2714 2715 |-2008 1-2008 1-2008 1-2008 ISM 3113 ISM 3113 ISM 4212 ISM 4930 COURSE (CourselD, CourseName) REGISTRATION (StudentID, SectionNo, Semester) CourselD CourseName StudentID Section No Semester ISM 3113 ISM 3112 ISM 4212 ISM 4930 Syst Analysis Syst Design Database Networking 38214 54907 54907 66324 2714 2714 2715 2713 1-2008 1-2008 1-2008 1-2008

Problem 1-5 using the table below in the picture to be answered in Microsoft SQL. Thanks

Answer 1

PLEASE LIKE THIS ANSWER, IT HELPS ME A LOT. THANK YOU!!!

(1)

a.       Display the course ID and course name for all courses with an ISM prefix:

Query:

SELECT COURSE_ID, COURSE_NAME

                 FROM COURSE

                 WHERE COURSE_ID LIKE 'ISM%';

b.      Display all courses for which Professor Berndt has been qualified:

Query:

SELECT FACULTY_NAME, COURSE.COURSE_ID, COURSE_NAME

                 FROM FACULTY, COURSE, QUALIFIED

                 WHERE QUALIFIED.FACULTY_ID = 3467

                 AND FACULTY.FACULTY_ID= QUALIFIED.FACULTY_ID

                 AND COURSE.COURSE_ID=QUALIFIED.COURSE_ID;

c.       Display the class roster, including student name, for all students enrolled in section 2714 of ISM 4212:

Query:
SELECT STUDENT.STUDENT_ID, STUDENT_NAME,

                        SECTION.COURSE_ID, REGISTRATION.SECTION_NO, SEMESTER

                 FROM STUDENT, REGISTRATION, SECTION

                 WHERE SECTION.SECTION_NO= REGISTRATION.SECTION_NO

                 AND STUDENT.STUDENT_ID= REGISTRATION.STUDENT_ID AND REGISTRATION.SECTION_NO=2714

                 AND SECTION.SEMESTER='I-2008'

                 AND REGISTRATION.SEMESTER='I-2008'

                 ORDER BY STUDENT_NAME;

(2)

SELECT FacultyName
FROM FACULTY,QUALIFIED
WHERE QUALIFIED.FacultyID=FACULTY. FacultyID
AND QUALIFIED.CourseD='ISM 3113';

(3)

SELECT FACULTY.FacultyNAME
FROM FACULTY, QUALIFIED
WHERE QUALIFIED.FacultyID=FACULTY. FacultyID
AND QUALIFIED.CourseID='ISM 3113'
AND NOT (QUALIFIED.CourseID='ISM 4930')

(4)

a) SELECT COUNT (DISTINCT (StudentID))
        FROM REGISTRATION
        WHERE SectionNo = 2714
        AND SEMESTER = 'I-2008'

b) SELECT COUNT (DISTINCT (StudentID))
        FROM SECTION, REGISTRATION
        WHERE SECTION.SectionNO = REGISTRATION.SectionNO
        AND CourseID = 'ISM 3113'
        AND Semester = 'I-2008'

(5)

Select STUDENT.StudentID, STUDENT.StudentName from

STUDENT Where NOT EXISTS

(

Select * from REGISTRATION where REGISTRATION.StudentID = STUDENT.StudentID and REGISTRATION.Semester = 'I-2008')

PLEASE LIKE THIS ANSWER, IT HELPS ME A LOT. THANK YOU!!!