Classwork_env_lab_3

SET LINESIZE 150
SET PAGESIZE 200

DROP TABLE STUDENT;
DROP TABLE DEPT;

CREATE TABLE DEPT(
DEPT_ID NUMBER(20),
DEPT_NAME VARCHAR(30),
FACULTY VARCHAR(30),
NO_OF_STUDENT NUMBER(20),
PRIMARY KEY(DEPT_ID)
);

INSERT INTO DEPT (DEPT_ID,DEPT_NAME,FACULTY,NO_OF_STUDENT) VALUES (3,'EEE','EE',120);
INSERT INTO DEPT (DEPT_ID,DEPT_NAME,FACULTY,NO_OF_STUDENT) VALUES (1,'CE','CE',120);
INSERT INTO DEPT (DEPT_ID,DEPT_NAME,FACULTY,NO_OF_STUDENT) VALUES (23,'BECM','CE',30);
INSERT INTO DEPT (DEPT_ID,DEPT_NAME,FACULTY,NO_OF_STUDENT) VALUES (5,'ME','ME',120);
INSERT INTO DEPT (DEPT_ID,DEPT_NAME,FACULTY,NO_OF_STUDENT) VALUES (13,'ESE','ME',60);
INSERT INTO DEPT (DEPT_ID,DEPT_NAME,FACULTY,NO_OF_STUDENT) VALUES (9,'ECE','EE',60);
INSERT INTO DEPT (DEPT_ID,DEPT_NAME,FACULTY,NO_OF_STUDENT) VALUES (7,'CSE','EE',120);

CREATE TABLE BSC_STUDENT(
NAME VARCHAR(20),
BATCH VARCHAR(50),
ROLL_NO NUMBER(20),
CGPA NUMBER(3,2),
DEPT_ID NUMBER(20),
PRIMARY KEY(ROLL_NO),
FOREIGN KEY(DEPT_ID) REFERENCES DEPT(DEPT_ID)
);

INSERT INTO BSC_STUDENT VALUES ('A','2K19', 1903122, 3.96, 3);
INSERT INTO BSC_STUDENT VALUES ('B','2K19', 1903123, 3.86, 3);
INSERT INTO BSC_STUDENT VALUES ('C','2K19', 1903125, 3.76, 3);
INSERT INTO BSC_STUDENT VALUES ('D','2K19', 1903126, 3.26, 3);

INSERT INTO BSC_STUDENT VALUES ('CA','2K19', 1907122, 3.19, 7);
INSERT INTO BSC_STUDENT VALUES ('CB','2K19', 1907123, 3.75, 7);
INSERT INTO BSC_STUDENT VALUES ('CC','2K19', 1907125, 3.72, 7);
INSERT INTO BSC_STUDENT VALUES ('CD','2K19', 1907126, 3.60, 7);

INSERT INTO BSC_STUDENT VALUES ('EA','2K19', 1909122, 3.89, 9);
INSERT INTO BSC_STUDENT VALUES ('EB','2K19', 1909123, 3.15, 9);
INSERT INTO BSC_STUDENT VALUES ('EC','2K19', 1909125, 3.75, 9);
INSERT INTO BSC_STUDENT VALUES ('ED','2K19', 1909126, 3.30, 9);

INSERT INTO BSC_STUDENT VALUES ('BEA','2K19', 1913122, 3.69, 13);
INSERT INTO BSC_STUDENT VALUES ('BEB','2K19', 1913123, 3.5, 13);
INSERT INTO BSC_STUDENT VALUES ('BEC','2K19', 1913125, null, 13);
INSERT INTO BSC_STUDENT VALUES ('BED','2K19', 1913126, null, 13);

CREATE TABLE MSC_STUDENT(
NAME VARCHAR(20),
BATCH VARCHAR(50),
ROLL_NO NUMBER(20),
CGPA NUMBER(3,2),
DEPT_ID NUMBER(20),
PRIMARY KEY(ROLL_NO),
FOREIGN KEY(DEPT_ID) REFERENCES DEPT(DEPT_ID)
);

INSERT INTO MSC_STUDENT VALUES ('MCA','2K19', 2307512, 4.00, 7);
INSERT INTO MSC_STUDENT VALUES ('MCB','2K19', 2307503, 3.75, 7);
INSERT INTO MSC_STUDENT VALUES ('MCC','2K19', 2307522, 3.72, 7);
INSERT INTO MSC_STUDENT VALUES ('MCD','2K19', 2307590, 4.00, 7);

SELECT * FROM DEPT;
SELECT * FROM BSC_STUDENT;
SELECT * FROM MSC_STUDENT;