The Permit table

Create the Permit table

CREATE TABLE Permit
    ("PermitNo" varchar2(10), "CarType" varchar2(20), "StudentID" varchar2(10), "ExpiryDate" date)
;

Insert 2 rows of values into the table

Just for illustration, we can also insert these altogether

INSERT INTO Permit 
VALUES ('P111', 'Toyota', 'ABC123456', TO_DATE('06-Jun-1966', 'DD-MM-YYYY'));

INSERT INTO Permit 
VALUES ('P222', 'Honda', 'DEF456123', TO_DATE('08-Aug-1988','DD-MM-YYYY'));

Display the table

SELECT * FROM Permit;

The Student Table

Create the table of students

CREATE TABLE Student
    ("StudentID" varchar2(10) PRIMARY KEY, "StudentName" varchar2(50), "StudentEmail" varchar2(60))
;

Insert 4 student records into the table

INSERT ALL 
    INTO Student ("StudentID", "StudentName", "StudentEmail")
         VALUES ('ABC123456', 'Jesse', 'jesse@car.edu')
    INTO Student ("StudentID", "StudentName", "StudentEmail")
         VALUES ('DEF456123', 'Chester', 'chester@car.edu')
    INTO Student ("StudentID", "StudentName", "StudentEmail")
         VALUES ('GHJ456789', 'Wanda', 'wanda@car.edu')
    INTO Student ("StudentID", "StudentName", "StudentEmail")
         VALUES ('KLM789456', 'Wilma', 'wilma@car.edu')
SELECT * FROM dual
;

Display the table

SELECT * FROM Student;

The Student_Class table

Create the Student_Class table

This is needed to resolve the many-to-many relationship between Student and Class because each student can take many classes and a class can have many students.

CREATE TABLE Student_Class
    ("StudentID_ClassNo" varchar2(50) PRIMARY KEY, "StudentID" varchar2(10), "ClassNo" varchar2(10))
;

Insert the values to the table

INSERT ALL 
    INTO Student_Class ("StudentID_ClassNo", "StudentID", "ClassNo")
         VALUES ('ABC123456-OMBA5305', 'ABC123456', 'OMBA5305')
    INTO Student_Class ("StudentID_ClassNo", "StudentID", "ClassNo")
         VALUES ('ABC123456-IS5310', 'ABC123456', 'IS5310')
    INTO Student_Class ("StudentID_ClassNo", "StudentID", "ClassNo")
         VALUES ('GHJ456789-OMBA5305', 'GHJ456789', 'OMBA5305')
    INTO Student_Class ("StudentID_ClassNo", "StudentID", "ClassNo")
         VALUES ('GHJ456789-BRMB5240', 'GHJ456789', 'BRMB5240')
SELECT * FROM dual
;

DIsplay the table

SELECT * FROM Student_Class;

The Class table

Create the Class table

CREATE TABLE Class
    ("ClassNo" varchar2(8), "ClassName" varchar2(15), "InstructorID" varchar2(10), "ClassSize" int)
;

Insert 3 rows of values into the table, this time altogether

INSERT ALL 
    INTO Class ("ClassNo", "ClassName", "InstructorID", "ClassSize")
         VALUES ('OMBA5305', 'Best Class', 'Y666', 300)
    INTO Class ("ClassNo", "ClassName", "InstructorID", "ClassSize")
         VALUES ('IS5310', 'Fantastic Class', 'Y666', 250)
    INTO Class ("ClassNo", "ClassName", "InstructorID", "ClassSize")
         VALUES ('BRMB5240', 'Awesome Class', 'Y666', 250)
SELECT * FROM dual;

Display the table

SELECT * FROM Class;

The Instructor table

Create the Instructor table

CREATE TABLE Instructor
    ("InstructorID" varchar2(10) PRIMARY KEY, "InstructorName" varchar2(60), "Department" varchar2(20))
;

Insert 2 rows of values into the table

Just for illustration, we can also insert these individually

INSERT INTO Instructor VALUES('Y666', 'yeoda', 'Management');
INSERT INTO Instructor VALUES('J888', 'jedi', 'Management');

Display the table

SELECT * FROM Instructor;

UPDATE

Modify an existing instructor record

UPDATE Instructor
SET "Department" = 'Force'
WHERE "InstructorID" = 'J888';

SELECT *
FROM Instructor;

Change data type

Change the data type of an existing column

ALTER TABLE Instructor
MODIFY "Department" varchar(100);

ALTER

Add a new column to the Student table

ALTER TABLE Student
ADD ("Telephone" varchar(20));

SELECT * 
FROM Student;

Delete a column from the student table

ALTER TABLE Student
DROP COLUMN "Telephone";

SELECT *
FROM Student;

Add values to existing table

Add a telephone column to the Student table

ALTER TABLE Student
ADD ("Telephone" varchar(20));

SELECT * FROM Student;

Populate the new Student table with telephone numbers

UPDATE Student 
SET "Telephone" = '1234567890' 
WHERE "StudentName" = 'Jesse';

SELECT * FROM Student;

DELETE

Add a new instructor to the instructor table

INSERT INTO Instructor VALUES('Z999', 'voldermort', 'Necromancy');

SELECT * 
FROM Instructor;

Remove the new instructor from the instructor table

DELETE
FROM
    Instructor
WHERE
    "InstructorID" = 'Z999'
    AND "Department" = 'Necromancy';

SELECT *
FROM Instructor;