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 data definitions commands for each of the following queries:

How would you add an attribute, Class, to the Student table?

How would you remove the Registration table?

How would you change the FacultyName field from 25 characters to 40 characters?

Write SQL commands for the following:

Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table.

Write a command that will remove Lopez from the Student table.

Create an SQL command that will modify the name of course ISM 4212 from Database to Introduction to Relational Databases.

Explanation / Answer

The following are the SQL queries to the above question:

1) alter table Student_T add (Class varchar2(10));

2) drop table Registration_T;

3) alter table Faculty_T modify FacultyName varchar2(40);

4) insert into Student_T values (65798,'Lopez');

insert into Student_T (StudentName, StudentID) values ('Lopez', 65798);

5) delete from Student_T where StudentName='Lopez';

6) update Course_T Set CourseName='Introduction to Relational Databases' where CourseID='ISM 4212';

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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