Given the following database: CREATE TABLE Student_T (StudentID NUMBER NOT NULL,

Question

Given the following database:

            CREATE TABLE Student_T

                        (StudentID                  NUMBER                   NOT NULL,

                        StudentName              VARCHAR2(25),

                        CONSTRAINT Student_PK PRIMARY KEY (StudentID));

            CREATE TABLE Faculty_T

                        (FacultyID                  NUMBER                   NOT NULL,

                        FacultyName               VARCHAR2(25),

                        CONSTRAINT Faculty_PK PRIMARY KEY (FacultyID));

            CREATE TABLE Course_T

                        (CourseID                               CHAR(8)                    NOT NULL,

                        CourseName               VARCHAR2(15),

                                    CONSTRAINT Course_PK PRIMARY KEY (CourseID));

            CREATE TABLE Section_T

                        (SectionNo                  NUMBER                   NOT NULL,

                        Semester                                  CHAR(7)                    NOT NULL,

                        CourseID                                CHAR(8),

                        CONSTRAINT Section_PK

                                    PRIMARY KEY(CourseID, SectionNo, Semester),

                        CONSTRAINT Section_FK FOREIGN KEY (CourseID)

                                                REFERENCES Course_T (CourseID));

            CREATE TABLE Qualified

                        (FacultyID                  NUMBER                   NOT NULL ,

                        CourseID                                CHAR(8)                    NOT NULL,

                        DateQualified             DATE,

                        CONSTRAINT IsQualified_PK PRIMARY KEY (FacultyID,

                                                CourseID),

CONSTRAINT QualifiedFaculty_FK FOREIGN KEY (FacultyID) REFERENCES Faculty_T (FacultyID),

CONSTRAINT QualifiedCourse_FK FOREIGN KEY (CourseID) REFERENCES Course_T (CourseID));

            CREATE TABLE Registration_T

                        (StudentID                  NUMBER                   NOT NULL,

                        SectionNo                   NUMBER                   NOT NULL,

                        Semester                                  CHAR(7)                    NOT NULL,

CONSTRAINT IsRegistered_PK PRIMARY KEY (StudentID,

            SectionNo, Semester),

CONSTRAINT StudentIsRegistered_FK

            FOREIGN KEY(StudentID)

            REFERENCES Student_T(StudentID),

CONSTRAINT CourseIsRegistered_FK

            FOREIGN KEY (SectionNo, Semester)

            REFERENCES Section_T(SectionNo, Semester));

Write SQL retrieval commands for each of the following queries:

Display the course ID and the 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.

Write an SQL query to answer the following question: Which instructors are qualified to teach ISM 3113?

Explanation / Answer

1.
SELECT * FROM COURSE WHERE COURSEID LIKE 'ISM%'

2.

SELECT C.COURSEID, COURSENAME FROM COURSE C, QUALIFIED Q, FACULTY F
WHERE (C.COURSEID = Q.COURSEID) AND (Q.FACULTYID = F.FACULTYID) AND (F.FACULTYID = 3467)

3.
SELECT Student.StudentID, StudentName,
FROM Student, Registration, Section
WHERE Section.SectionNo= Registration.SectionNo
AND Student.StudentID= Registration.StudentID
AND Registration.SectionNo=2714
AND Section.courseid = ‘ISM 4212’

4.

SELECT facultyname
FROM faculty f, qualified q
WHERE (f.facultyid = q.facultyid)
AND (q.courseid = ‘ISM 3113’)

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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