create table employee ( ename varchar(10), street varchar(10), city varchar(10), primary key(ename) ); create table works ( ename varchar(10), cname varchar(10), salary money, primary key(ename,cname) ); create table company ( cname varchar(10), city varchar(10), primary key(cname) ); drop table manages; create table manages( ename varchar(10), mname varchar(10), cname varchar(10), primary key(ename,mname,cname) ); select * from manages; -- Find the comapny with highest average difference -- between manager salary and non-manager employee salary select cname,ename from manages a where not exists ( select cname,mname from manages b where a.cname = b.cname and a.ename = e.mname ) select cname,mname from manages a where not exists ( select cname,mname from manages b where a.cname = b.cname and a.ename = b.ename ) select w.ename,w.cname,w.salary from works w, (select cname,ename from manages a where not exists ( select cname,mname from manages b where a.cname = b.cname and a.ename = b.mname ) ) e where w.cname = e.cname and w.ename = e.ename --- non managers average salary per corp select w.cname,avg(w.salary) from works w, (select cname,ename from manages a where not exists ( select cname,mname from manages b where a.cname = b.cname and a.ename = b.mname ) ) e where w.cname = e.cname and w.ename = e.ename group by w.cname --- managers average salary per corp select w.cname,avg(w.salary) from works w, (select cname,mname as ename from manages ) e where w.cname = e.cname and w.ename = e.ename group by w.cname select m.cname,m.average - nm.average from ( --- non managers average salary per corp select w.cname,avg(w.salary) as average from works w, (select cname,ename from manages a where not exists ( select cname,mname from manages b where a.cname = b.cname and a.ename = b.mname ) ) e where w.cname = e.cname and w.ename = e.ename group by w.cname ) nm, ( --- managers average salary per corp select w.cname,avg(w.salary) as average from works w, (select cname,mname as ename from manages ) e where w.cname = e.cname and w.ename = e.ename group by w.cname ) m where nm.cname = m.cname select m.cname,m.average - nm.average from ( --- non managers average salary per corp select w.cname,avg(w.salary) as average from works w, (select cname,ename from manages a where not exists ( select cname,mname from manages b where a.cname = b.cname and a.ename = b.mname ) ) e where w.cname = e.cname and w.ename = e.ename group by w.cname ) nm, ( --- managers average salary per corp select w.cname,avg(w.salary) as average from works w, (select cname,mname as ename from manages ) e where w.cname = e.cname and w.ename = e.ename group by w.cname ) m where nm.cname = m.cname --- dob database select day,count(*) from person