--//// Start ps1
desc emp;
desc dept;
desc salgrade;
desc implemp;
desc project;
desc implproject;
select * from project;
select * from implemp;
select * from implproject;

select distinct dname from dept;
select dname from dept group by dname;

select distinct job from emp;

select sal from emp where sal >= 2500 and sal <= 3200;
select sal from emp where sal between 2500 and 3000;

select ename, hiredate from emp where job in ('SALESMAN', 'MANAGER', 'CLERK');

select sal + NVL(comm, 0) from emp;

select ename, deptno from emp where deptno = 20 or deptno = 10 order by ename asc, deptno desc;

select ename from emp where mgr is null;

--//// Koniec ps1
--//// Start ps2
select ename, sal, hiredate from emp where deptno != 10 and sal >=1000 and sal < 2000;

select ename, deptno from emp where job = 'CLERK' and deptno in (10, 20);

select ename from emp where ename like '%TH%' or ename like '%LL%';

select ename, empno from emp where comm is null and mgr > 7800;

select emp.ename, emp.deptno, dept.dname from emp, dept where emp.deptno = dept.deptno;

select ename, grade from emp, salgrade where emp.sal between losal and hisal;

select ename from emp, dept where emp.deptno = dept.deptno and sal >= 1200 and loc = 'CHICAGO';

select ename from emp, dept where emp.DEPTNO = dept.DEPTNO and hiredate >= '83-01-21' and hiredate <= '83-06-30' and loc in ('DALLAS', 'CHICAGO');

select ename from emp, salgrade, dept where (sal between losal and hisal) and emp.DEPTNO = dept.DEPTNO and grade = 2 and loc='NEW YORK';

select ename, dept.deptno from emp, dept where emp.DEPTNO = dept.DEPTNO and loc='NEW YORK';

select ename, deptno, grade from emp, salgrade where (sal between losal and hisal) and job='PRESIDENT';
--////Koniec ps2
--////Start ps3
select ename Nazwisko, deptno "Nazwa departamentu" from emp;

select E.ename from emp E, emp D where E.empno = D.empno and E.sal > D.sal and D.deptno = 10;

select ename, round(sal*1.25) from emp where sal*1.25 >= 3000;

select empno, ename, ceil((sal + NVL(comm, 0))*1.25), floor((sal + NVL(comm, 0))*1.25) from emp;

select POWER(2, 5), SQRT(16), ABS(-16), MOD(5, 3)  from dual;

select POWER(sal, 2) from emp;

select SYSDATE+100 from dual;
select SYSDATE from dual;

select trunc(MONTHS_BETWEEN(sysdate, hiredate)) from emp;

select ADD_MONTHS(SYSDATE, 10) from dual;

select LAST_DAY(SYSDATE) from dual;

select 'Pracownik ' || ename || ' Nie posiada zwierzchnika' from emp where mgr is null;

select grade from salgrade where rownum <=4 order by grade desc;

select length(ename), ename from emp;

select INITCAP(ename), LOWER(job) from emp;

select TRANSLATE(ename,'E','a') from emp;

select rpad(dname, 15, 'x') from dept;

select substr(dname, 0, length(dname)-1) from dept;

select ename from emp where ename like 'BL%';

--////Koniec ps3
--////Start ps4
select count(1) from emp;

select min(sal), max(sal), avg(sal), sum(sal) from emp;

select count(distinct D.mgr) from emp E, emp D where E.empno = D.mgr;

select min(sal), max(sal) from emp, dept where emp.DEPTNO = dept.DEPTNO and dname = 'RESEARCH';

select count(1) from emp, salgrade where sal between losal and hisal and grade = 1;

select count(1), sum(NVL(emp.comm, 0)) from emp, dept where loc = 'DALLAS' and emp.deptno = dept.deptno and comm is not null;

select min(sal), max(sal), job from emp group by job;

select count(1) from emp, salgrade where sal between losal and hisal group by grade;

select sum(sal + NVL(comm,0)) from emp, dept where dept.deptno = emp.deptno (+)  group by emp.deptno;

select count(1) from emp, salgrade where sal between losal and hisal group by grade;

select sum(sal + NVL(comm,0)), dept.deptno from emp, dept where dept.deptno = emp.deptno(+) group by dept.deptno;

select job from emp group by job having max(sal) < 2500;

select distinct dname from emp, dept where emp.DEPTNO = dept.DEPTNO group by job, dname having count(1) > 2;

select grade from emp, salgrade where sal between losal and hisal group by grade having count(1) between 2 and 5;

select job from emp group by job having (max(sal) - min(sal)) < 1000;

select job from emp, salgrade where sal between losal and hisal and grade = 1 group by job having count(1) = 3;

select job, dname from emp, dept where emp.DEPTNO = dept.DEPTNO group by job, dname having count(1) >= 2;

--////Koniec ps4
--////Start ps5
select ename from emp where sal = (select sal from emp where ename = 'FORD') and job = (select job from emp where ename = 'FORD');
select ename from emp where (sal, job) = (select sal, job from emp where ename = 'FORD');

select ename, job, sal from emp where sal > (select sal from emp where ename = 'MILLER') and sal < (select sal from emp where ename = 'FORD');

select ename, sal from emp where empno = (select mgr from emp where ename='ADAMS');

select proname from project, implproject where project.PRONO = implproject.PRONO group by proname having count(1) > (select count(1) from implproject where prono = 1001);

select ename from emp, implemp, implproject where implemp.EMPNO = emp.EMPNO and implproject.IMPL = implemp.IMPL group by ename having count(1) > (select count(1) from emp, implemp where implemp.EMPNO = emp.EMPNO and deptno = 30);

select ename, job, sal from emp E where sal > (select avg(sal) from emp where job = E.job group by job);

select ename from emp E, implemp where implemp.EMPNO = E.EMPNO group by deptno, ename having count(1) = (select max(count(1)) from emp D, implemp A where D.empno = A.empno and D.deptno = E.deptno group by deptno, ename) ;

select job from emp group by job having count(1) >= 2 and count(1) <= 4;

select proname from project, implproject where project.PRONO = implproject.PRONO and implproject.START_DATE >= '08/01/01' and implproject.END_DATE <= '08/12/31' and project.BUDGET >= (select max(budget) from project, implproject where project.PRONO = implproject.PRONO and implproject.START_DATE >= '09/01/01' and implproject.END_DATE <= '09/12/31') ;

select proname from project, implproject where project.prono = implproject.prono and months_between(implproject.END_DATE, implproject.START_DATE) <= 3;

--////Koniec ps5
--////START ps6
select ename from emp E, dept D where E.deptno = D.DEPTNO and loc = 'DALLAS';

select * from emp;

select ename, job from emp, dept where emp.DEPTNO = dept.DEPTNO and dept.deptno = 20 and emp.job not in (select job from emp where deptno = 10);

select ename, sal from emp where sal > ALL(select sal from emp where job='MANAGER');

select ename from emp, dept, implemp where emp.deptno = 20 and implemp.EMPNO = emp.EMPNO
group by ename having count(1) > ALL(select count(ename) from emp, implemp where emp.deptno = 30 and implemp.empno = emp.empno);

select ename from emp E where exists(select 1 from emp, dept where mgr = E.empno and emp.deptno = dept.deptno and loc = 'CHICAGO');

select ename from emp E where not exists(select 1 from implemp where E.empno = implemp.empno);
select ename from emp E where ename not in (select ename from implemp, emp where emp.empno = implemp.empno);

select E.ename, F.ename, abs(K.grade-L.grade) from emp E, emp F, salgrade K, salgrade L where E.empno <> F.empno and E.sal between K.losal and K.hisal and F.sal between L.losal and L.hisal and abs(K.grade-L.grade) in (2,3);

select distinct proname from project, implproject, implemp, emp, dept where project.PRONO = implproject.PRONO and implproject.START_DATE >= '10/01/01'
and implproject.END_DATE <= '10/12/31' and implproject.IMPL = implemp.IMPL and emp.DEPTNO = dept.DEPTNO and dname = 'RESEARCH';


--////Koniec ps6
--////START ps7
select proname, (select count(1) from implproject where PRONO = K.prono) from project K;

select ename, empno, (select count(1) from implemp where empno = E.empno) from emp E;

select dname, count(1) from dept, emp, implemp where emp.DEPTNO = dept.DEPTNO and implemp.EMPNO = emp.EMPNO group by dname having count(1) >= ALL(select count(1) from dept, emp, implemp where emp.DEPTNO = dept.DEPTNO and implemp.EMPNO = emp.EMPNO group by dname);

select E.proname, F.proname, E.budget, F.budget from project E, project F where E.budget = (select min(budget) from project) and F.budget = (select max(budget) from project);

select dname, (select (count(1)/(select count(1) from emp))*100 from emp where D.deptno = emp.deptno group by deptno ) from dept D;

select sum(budget) from project, implproject where project.PRONO = implproject.PRONO and implproject.START_DATE >= '08/01/01' and implproject.END_DATE <= '08/12/31';

select proname from (select implproject.PRONO, proname, months_between(END_DATE, START_DATE) as dataa from project, implproject where project.PRONO = implproject.PRONO) where dataa = (select min(months_between(END_DATE, START_DATE)) from implproject) ;

select proname from (select proname, (select count(1) from implproject where P.PRONO = implproject.PRONO) as quant from project P) where quant = 0;