FOR FREE MATERIALS

Employee (empno, ename, deptno, job)

Department (deptno, dname, loc)

a)      Include the following constraints on column of Employee and Department table.

                  i. To make the empno as primary key of the table.

                  ii. To ensure that the ename column does not contain NULL values.

                  iii. The job column to have only UPPERCASE entries.

                  iv To make deptno as primary key.

                   v. To ensure dname, loc columns does not have NULL values.

             vi. Also enforce REFERENTIAL INTEGRITY, declare deptno field of dept table as primary key and dept no field of emp table as foreign key with on delete set null.

          vii.  Modify the above table to add three more columns Salary, mgr, comm. To the emp table. Add salary column with constraint greater than zero and modify the width of salary column.

CREATE Table EMPLOYEE :

CREATE TABLE EMPLOYEE (

Employee_No

NUMBER(5) PRIMARY KEY,

EName VARCHAR(50),

Deptno VARCHAR(5),

JOB VARCHAR(5)); 

Table EMPLOYEE is created.

Insert the values in the table "EMPLOYEE" :

INSERT INTO Employee VALUES(110, 'RAM', ‘A1’,’MANAGER’)

INSERT INTO Employee VALUES(141, 'SHYAM', ‘A2’,’STAFF’)

INSERT INTO Employee VALUES(109, 'MADHU', ‘A4’,’STAFF’)

INSERT INTO Employee VALUES(167, 'BIMAL', ‘A2’,’MANAGER’)

INSERT INTO Employee VALUES(102, 'KAMAL', ‘A1’,’STAFF’) ;

Output:

5 rows created

Employee

CREATE Table DEPARTMENT :

CREATE TABLE DEPARTMENT (

deptno

VARCHAR(5) PRIMARY KEY,

dName VARCHAR(20),

loc VARCHAR(5));

Table DEPARTMENT is created.

Department

Insert the values in the table "DEPARTMENT":

INSERT INTO Employee VALUES(‘A1’, 'Technician', ‘Kolkata’)

INSERT INTO Employee VALUES(‘A2’, 'Electrician', ‘Mumbai’)

INSERT INTO Employee VALUES(‘A4’, 'Maintainance', ‘Kolkata’);

Output:

3 rows created