MFTzYApg

1-- DROP IF EXISTS TO AVOID CONFLICT
2DROP TABLE STUDENTS CASCADE CONSTRAINTS;
3DROP TABLE COURSES CASCADE CONSTRAINTS;
4DROP TABLE COURSE_TAKEN CASCADE CONSTRAINTS;
5
6-- 1. Creating the tables
7
8CREATE TABLE STUDENTS (
9	ROLL		NUMBER(6),
10	NAME		VARCHAR(30) NOT NULL,
11	DEPARTMENT	VARCHAR(3) NOT NULL,
12	PRIMARY KEY(ROLL)
13);
14/*
15ALTER TABLE STUDENTS ADD PRIMARY KEY(ROLL);
16ALTER TABLE STUDENTS MODIFY NAME NOT NULL;
17ALTER TABLE STUDENTS MODIFY DEPARTMENT NOT NULL;
18*/
19
20
21CREATE TABLE COURSES (
22	C_ID	NUMBER(4),
23	C_NAME	VARCHAR(10) UNIQUE,
24	YEAR	NUMBER(1) CHECK(YEAR >= 1 AND YEAR <= 4),
25	TERM	NUMBER(1) CHECK(TERM = 1 OR TERM = 2),
26	CREDIT	NUMBER(3, 2) DEFAULT 3.00.
27	PRIMARY KEY(C_ID)
28);
29/*
30ALTER TABLE COURSES ADD PRIMARY KEY(C_ID);
31ALTER TABLE COURSES ADD UNIQUE (C_NAME);
32ALTER TABLE COURSES MODIFY YEAR CHECK(YEAR >= 1 AND YEAR <= 4);
33ALTER TABLE COURSES MODIFY TERM CHECK(TERM = 1 OR TERM = 2);
34ALTER TABLE COURSES MODIFY CREDIT DEFAULT 3.00;
35*/
36
37
38CREATE TABLE COURSE_TAKEN (
39	ROLL	        NUMBER(6),
40	C_ID	        NUMBER(4),
41	SESSION_NAME	VARCHAR(10),
42	FOREIGN KEY(ROLL) REFERENCES STUDENTS(ROLL) ON DELETE CASCADE
43);
44/*
45ALTER TABLE COURSE_TAKEN ADD FOREIGN KEY(ROLL) REFERENCES STUDENTS(ROLL) ON DELETE CASCADE;
46*/
47
48-- 2. Describing created tables
49DESCRIBE STUDENTS;
50DESCRIBE COURSES;
51DESCRIBE COURSE_TAKEN;
52
53-- 3. Set C_ID of COURSE_TAKEN a foreign key of COURSES table
54ALTER TABLE COURSE_TAKEN ADD FOREIGN KEY(C_ID) REFERENCES COURSES(C_ID);
55
56-- 4. Inserting 3 sets of demo data
57
58INSERT INTO STUDENTS VALUES (1, 'Shakil', 'CSE');
59INSERT INTO STUDENTS VALUES (2, 'Alex', 'EEE');
60INSERT INTO STUDENTS VALUES (3, 'Hannah', 'CSE');
61
62INSERT INTO COURSES VALUES (1, 'OOP', 1, 2, 3.00);
63INSERT INTO COURSES VALUES (2, 'OOP Lab', 1, 2, 1.00);
64INSERT INTO COURSES VALUES (3, 'OS', 3, 1, 3.00);
65
66INSERT INTO COURSE_TAKEN VALUES(1, 1, 'Winter');
67INSERT INTO COURSE_TAKEN VALUES(2, 3, 'Spring');
68INSERT INTO COURSE_TAKEN VALUES(3, 2, 'Winter');
69
70-- 5. Showing all tables with data
71SELECT * FROM STUDENTS;
72SELECT * FROM COURSES;
73SELECT * FROM COURSE_TAKEN;
74
75-- 6. Deleting a record from STUDENTS table
76DELETE FROM STUDENTS WHERE ROLL = 3;
77
78-- 7. Showing course table
79SELECT * FROM COURSE_TAKEN;