7-28 A. B. C. of that were introduced of the temp extensions Chapter 7 Advanced

Question

7-28
A.
B.
C.

of that were introduced of the temp extensions Chapter 7 Advanced SQL 331 Discuss in ere to SQL 724. This chapter discusses the data dictionary views for as the temporal used 12e, Research another RDBMS, such as Microsoft introduced extensions for the SQL Server, and report on its data dictionary fa and before they how it compares with Oracle Problems and Exercises ablems and Exercises relations rongh 7-30 are on the class schedule 7-26. write an soL query to answer the following question: with some based in Fi 2.16. For Proble show the data you expect n or ER di gram and mark 15. Write SQL your qu to use to produce tive results. queries: retrieval commands for each of the following 7-27. Which instructors are qualified to teach ISM 3113? an query answer followi ng question: instructor qualified to teach ISM not a. Display the course ID and course name for all courses qualified to teach ISM 4930? 7-28. Write SQL queries to answer the following questions: b. Display all courses for which Professor Berndt has qualified a. What are the names of the course(s) that student Altwater took during the semester I-2015? b. List names of the students ha c. Display the class roster, including student name, for all students enrolled in section 2714 of ISM 4212 taken at least one course that Professor Collins is qualified to teach. c. How many students did Professor Collins teach during the semester I-2015 STUDENT (StudenuD, StudentName) OUALIFIED (FacultID, CourselD, DateQualified) Stud FacultyMD CourselD Dateoualified 38214 etersky 54907 2143 SM 3 9/2005 Altvater 2143 SM 3113 66324 Aiken 3467 SM 4212 9/2012. 70542 3467 SM 4930 ISM 3113 9/2008 4758 SM 3112 9/2008 4756 FACULTY (Facul D, Faculty Name) SECTION (SectionNo, Semester, CourseID SectionNo semester courselD Facul Facul REGISTRATION (StudentID, SectionNo) COURSE (CourselD, CourseName Student ID SectionNo CourseName CourselD 2714 38214 Syst Analysis SM 3113 2714 54907 Syst Design ISM 3112 2715 54907 Database ISM 4212 2713 66324 Networking ISM 4930 FIGURE 7-16 Class scheduling relations (for Problems and Exercises 7-25-7-30)

Explanation / Answer

(7-25):
a)SELECT CourseID, CourseName
FROM Course WHERE CourseID LIKE ‘ISM%’;

b)SELECT Course.CourseID, CourseName
FROM Faculty, Course, Qualified
WHERE Faculty.FacultyName = ‘Berndt’ AND Faculty.FacultyID= Qualified.FacultyID AND Course.CourseID=Qualified.CourseID;

c)SELECT Student.StudentID, StudentName, Section.CourseID, Registration.SectionNo, Semester
FROM Student, Registration, Section
WHERE Section.SectionNo= Registration.SectionNo AND Student.StudentID= Registration.StudentID AND Registration.SectionNo=2714
ORDER BY StudentName;

(7-26) SELECT Faculty.FacultyName
FROM Faculty, Qualified
WHERE Qualified.FacultyID=Faculty. FacultyID AND Qualified.CourseID=‘ISM 3113’;

(7-27)
SELECT Faculty.FacultyID, Faculty.FacultyName
FROM Faculty, Qualified
WHERE Qualified.FacultyID=Faculty.FacultyID AND Qualified.CourseID=‘ISM 3113’ MINUS
   SELECT Faculty.FacultyID, Faculty.FacultyName
   FROM Faculty, Qualified
   WHERE Qualified.FacultyID=Faculty.FacultyID AND Qualified.CourseID=‘ISM 4930’;

(7-28)
a)SELECT C.courseName
FROM STUDENT as S,COURSE as C,REGISTRATION as R,SECTION as X
WHERE S.StudentID=R.StudentID AND R.SectionNo=X.SectionNo AND X.CourseID=C.CourseID AND S.StudentName='Altvater' AND X.Semester=‘I-2008’;

b)SELECT S.StudentName
FROM STUDENT as S,Course as C,REGISTRATION as R,SECTION as X,QUALIFIED as Q,FACULTY as F
WHERE S.StudentID=R.StudentID AND R.SectionNo=X.SectionNo AND X.CourseID=C.CourseID AND
   C.CourseID=Q.CourseID AND Q.FacultyID=F.FacultyID AND F.FacultyName='Collins';

b)SELECT COUNT(DISTINCT(StudentID))
FROM REGISTRATION as R,SECTION as X,QUALIFIED as Q,FACULTY as F
WHERE R.SectionNo=X.SectionNo AND X.CourseID=Q.CourseID AND Q.FacultyID=F.FacultyID AND
F.FacultyName='Collins' AND X.Semester=‘I-2008’;

Related Questions

Navigate

• Browse (All)
• Browse 7
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.