ORACEL TABLE PROGRAMMING CODE


TABLE CREATION SYNTAX CODES (SQL+): Clearwater Traders database #1
1.

CREATE TABLE location
(loc_id NUMBER(6),
bldg_code VARCHAR2(10),
room VARCHAR2(6),
capacity NUMBER(5),
CONSTRAINT location_loc_pk PRIMARY KEY (loc_id));

2.

CREATE TABLE faculty
(f_id NUMBER(6),
f_last VARCHAR2(30),
f_first VARCHAR2(30),
f_mi CHAR(1),
loc_id NUMBER(6),
f_phone VARCHAR2(10),
f_rank VARCHAR2(2),
f_super NUMBER(6),
f_pin NUMBER(4),
f_image BLOB,
CONSTRAINT faculty_f_id_pk PRIMARY KEY (f_id),
CONSTRAINT faculty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id));

3.

CREATE TABLE student
(s_id NUMBER(6),
s_last VARCHAR2(30),
s_first VARCHAR2(30),
s_mi CHAR(1),
s_address VARCHAR2(30),
s_city VARCHAR2(30),
s_state CHAR(2),
s_zip CHAR(5),
s_phone CHAR(10),
s_class CHAR(6),
s_dob DATE,
s_pin NUMBER(10),
f_id NUMBER(6),
time_enrolled interval year to month,
CONSTRAINT student_s_pk PRIMARY KEY (s_id),
CONSTRAINT student_f_id_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));

4.

CREATE TABLE course
(course_no VARCHAR2(6),
course_name VARCHAR2(30),
credits VARCHAR2(3),
CONSTRAINT course_course_no_pk PRIMARY KEY (course_no));

5.

CREATE TABLE term
(term_id NUMBER(6),
term_desc VARCHAR2(30),
status CHAR(3),
start_date DATE,
CONSTRAINT term_term_id_pk PRIMARY KEY (term_id));

6.

CREATE TABLE course_section
(c_sec_id NUMBER(6),
course_no VARCHAR2(6),
term_id NUMBER(6),
sec_num CHAR(4),
f_id NUMBER(6),
c_sec_day VARCHAR2(30),
c_sec_time DATE,
loc_id  NUMBER(6),
c_sec_duration INTERVAL DAY TO SECOND,
max_enrl VARCHAR2(30),
CONSTRAINT course_section_c_sec_id_pk PRIMARY KEY (c_sec_id),
CONSTRAINT course_section_course_no_fk FOREIGN KEY (course_no) REFERENCES course(course_no),
CONSTRAINT course_section_term_id_fk FOREIGN KEY (term_id) REFERENCES term(term_id),
CONSTRAINT course_section_f_id_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id),
CONSTRAINT course_section_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id));

7.

CREATE TABLE enrollment
(s_id NUMBER(6),
c_sec_id NUMBER(6),
grade CHAR(1),
CONSTRAINT enrollment_s_id_c_sec_id_pk PRIMARY KEY (s_id, c_sec_id),
CONSTRAINT enrollment_c_sec_id_fk FOREIGN KEY (c_sec_id) REFERENCES course_section(c_sec_id));



TABLE DELETION SYNTAX:

DROP TABLE enrollment;
DROP TABLE course_section;
DROP TABLE term;
DROP TABLE course;
DROP TABLE student;
DROP TABLE faculty;
DROP TABLE location;



TABLE DELETION CASCADING SYNTAX:

DROP TABLE location;
DROP TABLE faculty CASCADE CONSTRAINTS;
DROP TABLE student CASCADE CONSTRAINTS;
DROP TABLE course;
DROP TABLE term;
DROP TABLE course_section CASCADE CONSTRAINTS;
DROP TABLE enrollment CASCADE CONSTRAINTS;



TABLE DESCRIPTION SYNTAX:

DESC location;
DESC faculty;
DESC student;
DESC course;
DESC term;
DESC course_section;
DESC enrollment;