Program 5 (Create and Insert)

Creation of Tables

CREATE TABLE DEPARTMENT(Dno int PRIMARY KEY, Dname varchar(30), Mgrssn int,Mgrstartdate date );

CREATE TABLE EMPLOYEE(Ssn int PRIMARY KEY,Name varchar(15), Address varchar(15),Gender char,Salary number(8,2),Superssn int REFERENCES EMPLOYEE(Ssn),Dno int REFERENCES DEPARTMENT(Dno));

CREATE TABLE DLOCATION(Dno int REFERENCES DEPARTMENT(Dno),Dloc varchar(15),PRIMARY KEY(Dno,Dloc));

CREATE TABLE PROJECT(Pno int PRIMARY KEY,Pname varchar(15),Plocation varchar(15),Dno intREFERENCES DEPARTMENT(Dno));

CREATE TABLE WORKS_ON(Ssn int REFERENCES EMPLOYEE(Ssn), Pno int REFERENCES PROJECT(Pno),Hours int,PRIMARY KEY(Ssn,Pno));

Insertion of rows to the table

INSERT INTO DEPARTMENT VALUES('&Dno','&Dname',NULL,NULL);

INSERT  INTO  EMPLOYEE VALUES('&Ssn','&Name','&Address','&Gender','&Salary',NULL,'&Dno');

Before updating Superssn of EMPLOYEE Table:

SELECT * FROM EMPLOYEE;

Note1: Update Superssn of EMPLYOEE table

UPDATE EMPLOYEE SET Superssn=101 where Ssn in(102,103,104);

UPDATE EMPLOYEE SET Superssn=102 where Ssn in(105,106);

UPDATE EMPLOYEE SET Superssn=201 where Ssn=202;

UPDATE EMPLOYEE SET Superssn=301 where Ssn in(302,303,304,305,306);

After updating Superssn of EMPLOYEE table:

SELECT * FROM EMPLOYEE;

Note2: Alter the DEPARTMENT table to refer Mgrssn attribute to Ssn attribute of EMPLOYEE.

Alter table DEPARTMENT add constraint FR_KEY1 FOREIGN KEY(Mgrssn) REFERENCES EMPLOYEE(Ssn);

Before updating Mgrssn, Mgrstartdate of DEPARTMENT table.

SELECT * FROM DEPARTMENT;

Note3: Update DEPARTMENT table’s Mgrssn, Mgrstartdate attribute values.

UPDATE DEPARTMENT SET Mgrssn=101, Mgrstartdate='01-Feb-2014' where Dno=5;

UPDATE DEPARTMENT SET Mgrssn=201, Mgrstartdate='01-Jul-2010' where Dno=2;

UPDATE DEPARTMENT SET Mgrssn=301, Mgrstartdate='01-Jul-2010' where Dno=1;

After updating Mgrssn, Mgrstartdate of DEPARTMENT table.

SELECT * FROM DEPARTMENT;

INSERT INTO DLOCATION VALUES('&Dno','&Dloc');

INSERT INTO PROJECT VALUES('&Pno','&Pname','&Plocation','&Dno');

INSERT INTO WORKS_ON VALUES('&Ssn','&Pno','&Hours');