SQL Data Definition & Data Manipulation

SQL Plus is a very powerful language that performs various tasks ranging from defining database structure to ad hoc queries. We will use an exercise to demonstrate how to create a Oracle database structure, populate a table, and alter table structure. The situation we wish to represent here is that of students enrolled in courses. For each student we need to store student number, name, street address, city, and enrolment date. A course code, course name, and the teacher's name describe each course.

COURSE(Cou_code,Cou_Name,Cou_Teacher)

STUDENT(Stu_No,Stu_LName, Stu_Fname, Stu_Street, Stu_City, Stu_Enroll_Date, Stu_GPA)

ENROLL(Cou_Code, Stu_No, Grade)

1. Use the Create Table statement to define the table structures.

CREATE TABLE Student(

Stu_Code Number(3) CONSTRAINT Student_Code_pk PRIMARY KEY,

Stu_Lname Varchar2(15),

Stu_Fname Varchar2(15),

Stu_Address Varchar2(15),

Stu_City Varchar2(15),

Stu_Enroll_date DATE,

Stu_GPA Number(3,2) CONSTRAINT Student_GPA_cc CHECK (Stu_GPA <= 4.0

AND Stu_GPA >= 0) );

CREATE TABLE Course(

Cou_Code Varchar2(8) CONSTRAINT Course_Code_pk PRIMARY KEY,

Cou_name Varchar2(15),

Cou_Prof Varchar2(10));

CREATE TABLE Enroll(

Stu_Code Number CONSTRAINT Enroll_StuCode_fk References Student(Stu_Code),

Cou_Code Varchar2(8) CONSTRAINT Enroll_CouCode_fk References

Course(Cou_Code),

Enroll_Grade Char(1) CONSTRAINT Enroll_grade_cc CHECK (Enroll_Grade

IN('A','B','C','D','F') ),

CONSTRAINT Enroll_pk PRIMARY KEY (Stu_Code, Cou_Code) );

2. Modifying a table definition i.e. adding or deleting a column or a relationship is easy using Oracle SQL.

·