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(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 queries to answer the following questions:

Which students have an ID number that is less than 50000?

What is the name of the faculty member whose ID is 4756?

What is the smallest section number used in the first semester of 2008?

How many stuents are enrolled in Section 2714 in the first semester of 2008?

Which faculty members have qualified to teach a course since 1993? List the faculty ID, course and date of qualification.

Explanation / Answer

First sql query

SELECT Student.ID, Student.Name
FROM Student
WHERE (((Student.ID)<50000));

---------------------------------------------------------------------------------------------------

second query

SELECT Faculty.ID, Faculty.Name
FROM Faculty
WHERE (((Faculty.ID)=4756));

------------------------------------------------------------------------------------

Third query

SELECT Section.[No], Section.Semester
FROM [Section]
WHERE (((Section.[No])<=2712));

----------------------------------------------------------------------------------------------

Fourth Query

SELECT Registration.StudentID, Registration.SectionNo
FROM Registration
WHERE (((Registration.SectionNo)=2714));

--------------------------------------------
Fifth query

SELECT Qualified.[FacultyID], Qualified.CourseID, Qualified.DateQualified
FROM Qualified WHERE (((Qualified.DateQualified)>="9/1993"));

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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