Lab-4
Objective:  Join Operations , Intetegrity constraints


Consider the following relational schema
Employe(emp-id, name, address, telephone, salary, age)
Works- on (emp-id, project-id, join-date )
Project(project-id, project-name, city, duration, budget)
Write SQL statements for the following
1.	SQL DDL for creating the above relations. Couple the above table with necessary primary key, foreign key and enforce cascade update and cascade delete referential integrity constraints. The field telephone should be unique and age should be in the range 0 to 100.
2.	Insert any records of any five employees.
3.	Insert any 4 project records.
4.	Insert any 2 record in work relation.
5.	Perform join on
i.	employye inner join works-on
ii.	employee join works join projects
iii.	employee left outer join works--on
iv.	works_on right outer join Project
v.	employee full outer join works-on
6.	Find the name of employees with the name of project they work on.
7.	Find the name and address all employees whose salary of employee is greater than or equal to 20000 and works in DBMS project.
8.	List the name of employees whose name starts with “r” and ends with “m”
9.	Display the name of all employee who work in atleast one project.
10.	Display the name of all employee who work in atleast two project.

11.	Display the name of all employee who do not work in any project using
i.	Concept of inner join and  set operation
ii.	Concept of left outer join
12.	Display the name of all Project on which no employee work
iii.	Concept inner join and set operation
iv.	Concept of right outer join
13.	Delete the record of employee who work in project-name ‘DBMS-project’
14.	Update database such that employee with employee id 2 no more works in project named ‘Network’
15.	Increase the salary of all the employees by 15% who work in DBMS-project
16.	List the name of Employee whose name starts with “s”, not having “C” has third character and ends with any character in the range “m” to “y”
17.	Display the name of employee with maximum age.






1.

create table Project
(
	project_id int primary key,
	project_name varchar(50),
	city varchar(40),
	duration int,
	budget float
)

create table Employee
(
	emp_id int primary key,
	name varchar(50),
	address varchar(100),
	telephone varchar(20) unique,
	salary float,
	age int check(age>=0 and age<=100)

)


create table works_on
(
	emp_id int foreign key references Employee(emp_id) on delete cascade on update cascade,
	project_id int foreign key references Project(project_id) on delete cascade on update cascade,
	join_date date,
primary key(emp_id,project_id)
)





2.
insert into Employee values(1,'Ram','Pkr','90950',55000,25)
insert into Employee values(2,'Shyam','ktm','905590',65000,25)
insert into Employee values(3,'Rita','Pkr','90930',55700,25)
insert into Employee values(4,'hari','btl','90950',58800,25)
insert into Employee values(5,'bipin','ktm','90590',590000,25)

3.
insert into project values (1001,'DBMS','Pkr',5,100000)
insert into project values (1002,'ABC','Pkr',5,100000)
insert into project values (1003,'NETWORK','KTM',5,100000)
insert into project values (1004,'Programming','Btl',5,30000)





4
insert into works_on values(1,1001,'2015/07/09')
insert into works_on values(1,1002,'2015/07/09')
insert into works_on values(2,1003,'2015/07/09')



5.
i.
select *  from employee e join works_on w on  e.emp_id=w.emp_id



ii.
select *  from employee e join works_on w on  e.emp_id=w.emp_id join project p on w.project_id=p.project_id

iii.
select *  from employee e left outer join works_on w on  e.emp_id=w.emp_id




iv.
select *  from works_on w right outer join project p on  w.project_id=p.project_id



v.

select *  from employee e full outer join works_on w on  e.emp_id=w.emp_id


6.
select *  from employee e join works_on w on  e.emp_id=w.emp_id join Project p on w.project_id=p.project_id


7.
select name,address  from employee e join works_on w on  e.emp_id=w.emp_id join Project p on w.project_id=p.project_id where p.project_name='DBMS' and salary>20000


8.
select * from Employee where name like 'r%m'



9.
select * from Employee e join works_on w on e.emp_id=w.emp_id












10.
select name from  (  select e.emp_id,e.name, COUNT(*) as number from Employee e join works_on w on e.emp_id=w.emp_id group by e.emp_id,e.name having COUNT(*)>=2)as tmp

11
i.
select name from Employee
except
select e.name from Employee e join works_on w on e.emp_id=w.emp_id


ii.
select e.name from Employee e left outer join works_on w on e.emp_id=w.emp_id where w.emp_id is null


12.
i.
select project_name from project
except
select project_name from works_on w  join Project p on w.project_id=p.project_id

ii.
select project_name from works_on w  right outer join Project p on w.project_id=p.project_id where w.emp_id is null




13.
Delete from employee where emp_id in ( select e.emp_id from employee e join works_on w on  e.emp_id=w.emp_id join project p on w.project_id=p.project_id where project_name='DBMS')

It will result employee,works_on and project relation as below




14
delete from works_on where project_id in (select project_id from Project where project_name='Network') and emp_id=2

15
Update Employee set salary =salary*1.15 where emp_id in ( select e.emp_id from employee e join works_on w on  e.emp_id=w.emp_id join project p on w.project_id=p.project_id where project_name='DBMS')

Previously
-


After update




16,
select * from Employee where name like 'S_[^c]%[m-y]'



18.


select * from Employee where age in (select MAX(age) from Employee)