dba3

create table department(dno int primary key,dname varchar(20),mgrsnn varchar(10),mgrstartdate date);
create table employee(ssn varchar(20) primary key,name varchar(20),address varchar(30),sex varchar(7),salary int,supersnn varchar(20),dno int,foreign key (supersnn) references employee(ssn)on delete cascade,foreign key (dno) references department(dno)on delete cascade)
create table dloction(dno int,dloc varchar(20),foreign key (dno) references department(dno)on delete cascade);
create table project(pno int primary key,pname varchar(20),ploction varchar(30),dno int,foreign key (dno) references department(dno)on delete cascade);
create table works_on(ssn varchar(20),pno int,hours decimal(5,2),foreign key (ssn) references employee(ssn)on delete cascade,foreign key (pno) references project(pno)on delete cascade)
insert into department values(1,'enginner','101','01-jan-2017');
insert into department values(2,'hr','102','05-march-2017');
insert into department values(3,'account','103','23-jan-2018');
insert into department values(4,'sales','104','16-jan-2019');
insert into department values(5,'social','105','11-jun-2017');
insert into employee values('101','sam hedge','xxx','F',30000,'101',1);
insert into employee values('102','ram roy','yyy','M',40000,'102',2);
insert into employee values('103','ham hedge','zzz','F',50000,'103',3);
insert into employee values('104','lam shetty','aaa','M',60000,'104',4);
insert into employee values('105','raju','bbb','M',70000,'105',5);
insert into dloction values(1,'new york');
insert into dloction values(2,'LA');
insert into dloction values(3,'UK');
insert into dloction values(4,'new york');
insert into dloction values(5,'USA');
insert into project values(1001,'p1','new york',1);
insert into project values(1002,'p2','LA',2);
insert into project values(1003,'ai','UK',3);
insert into project values(1004,'p4','new york',4);
insert into project values(1005,'p5','USA',5);
insert into works_on values('101',1001,20);
insert into works_on values('102',1002,10);
insert into works_on values('103',1003,7);
insert into works_on values('104',1004,8);
insert into works_on values('105',1005,15);
select distinct p.pno from project p,department d,employee e where d.dno=p.dno and d.mgrsnn=e.ssn and e.name like '%hedge%' union select distinct p1.pno from project p1,works_on w,employee e1 where w.pno=p1.pno  and e1.ssn=w.ssn and e1.name like '%hedge%';
select e.name,1.1*e.salary as incr from employee e,works_on w,project p where e.ssn=w.ssn and w.pno=p.pno and p.pname='ai';
select sum(e.salary),min(e.salary),max(e.salary),avg(e.salary) from employee e, department d where e.dno=d.dno and d.dname='account';
select e.name from employee e where not exists(select pno from project where dno='5'and pno not in (select pno from works_on where e.ssn=ssn));
SELECT D.DNo, COUNT(E.SSN) AS NumEmployees
FROM DEPARTMENT D
JOIN EMPLOYEE E ON D.DNo = E.DNo
WHERE E.Salary > 60000
GROUP BY D.DNo
HAVING COUNT(E.SSN) >= 1;