Exp 15 - Part 2 -Create the following tables ( primary keys are underlined.)

 

2.  Create the following tables ( primary keys are underlined.).

Emp(eno,ename,designation,sal)
Dept(dno,dname,dloc)
There exists a one-to-many relationship between emp & dept.

Create the Relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF).

a) Insert 5 records into department table
b) Insert 2 employee records for each department.
c) increase salary of “managers” by 15%;
d) delete all employees of deparment 30;
e) delete all employees who are working as a “clerk”
f) change location of department 20 to ‘KOLKATA’

Ans.

create table dept (
dno int primary key,
dname text,
dloc text
);

create table emp(
dno int,
eno int primary key,
ename text,
designation text,
sal money,
foreign key (dno) references dept (dno) on delete cascade
);

a] Insert 5 records into department table

insert into dept values(11,'computer','pune');
insert into dept values(22,'computer1','pune');
insert into dept values(33,'computer2','pune');
insert into dept values(44,'computer3','pune');
insert into dept values(55,'computer4','pune');

b] Insert 2 employee records for each department.

insert into emp values(11,1,'xyz','manager',7000);
insert into emp values(11,2,'xyz','staff',7000);
insert into emp values(22,3,'pqr','clerk',2000);

c] increase salary of “managers” by 15%;

update emp set sal=sal*15/100 where ename='staff';

d] delete all employees of deparment 30;

delete from emp where dno=11;

e] delete all employees who are working as a “clerk”

delete from emp where designation='clerk';

f] change location of department 20 to ‘KOLKATA’

update  dept set dloc='KOLKATA' where dno=20;

******************************************************************************