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(SectionID, Semester));

Write SQL retrieval commands for each of the following queries:

Give an example of an query that can be written using the subquery approach can also be written using the joining approach, but not vice versa.

Explanation / Answer

1.) SELECT CourseID,CourseName FROM Course_T WHERE CourseName LIKE 'ISM%';

2.) SELECT CourseID,CourseName FROM Course_T,Qualified,Faculty_T WHERE Course_T.CourseID = Qualified.CourseID AND Qualified.FacultyID=Faculty_T.FacultyID AND FacultyName='Professor Berndt';

3.)

SELECT StudentID,StudentName FROM Student_T,Registration_T,Section_T,Course_T WHERE Student_T.StudentID = Registration_T.StudentID AND Registration_T.SectionNo=Section_T.SectionNo AND Section_T.CourseID=Course_T.Course_ID AND SectionNo=2714 AND CourseName='ISM 4212';

4.)

SELECT FacultyID,FacultyName FROM Faculty_T,Qualified,Course_T WHERE Faculty_T.FacultyID = Qualified.FacultyID AND Qualified.CourseID=Course_T.CourseID AND CourseName='ISM 3113';

5.)

Query no.4 above.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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