ps7.
zad1. select w.empno a, w.ename b, '|' z.empno c, nvl(z.ename, 'NIKT') d
from emp w, emp z
where nvl(w.mgr, -9)=z.empno(+)
order by w.mgr;
zad2. select w.empno, w.ename, '|' z.empno, nvl(z.ename, 'NIKT'), '|' t.empno, nvl(t.ename, 'NIKT')
from emp w, emp z, emp t
where nvl(w.mgr, -9)=z.empno(+) and nvl(z.mgr, -9)=t.empno(+)
order by w.mgr;
zad3. select w.emono, w.ename, '|' z.empno, nvl(z.ename, 'NIKT'), '|' t.empno, nvl(t.ename, 'NIKT'), '|' s.empno, nvl(s.ename, 'NIKT')
from emp w, emp z, emp t, emp s
where nvl(w.mgr, -9)=z.empno(+) and nvl(z.mgr, -9)=t.empno(+) and nvl(t.mgr, -9)=s.empno(+);
zad4. a) select empno, ename, job, mgr from emp connect by prior mgr=empno start with empno=7902;
b) select empno, ename, job, mgr from emp connect by prior empno=mgr start with empno=7839;
zad5. a) select level, empno, ename, job, mgr from emp connect by prior mgr=empno start with empno=7902;
b) select level, empno, ename, job, mgr from emp connect by prior empno=mgr start with empno=7839;

ps8.
zad1. select ename, job from emp where job in('SALESMAN', 'CLERK') union all select ename, job from emp where job in('SALESMAN', 'MANAGER');
zad2. select ename, job from emp where job in('SALESMAN', 'CLERK') intersect select ename, job from emp where job in('SALESMAN', 'MANAGER');
zad3. select ename, job from emp where job in('SALESMAN', 'CLERK') minus select ename, job from emp where job in('SALESMAN', 'MANAGER');
zad4. select ename, sal, nvl(comm, 0) from emp where (sal+nvl(comm, 0))>1500 and comm is not NULL
union
select ename, sal, nvl(comm, 0) from emp where comm is not NULL
order by 1;
zad5. select 1 A, '2' B, '3' C, 4 D from emp union select empno, ename, job, sal from emp;
zad6. select ' ', ename, hiredate from emp union select '*', ename, hiredate from emp where hiredate=(select min(hiredate) from emp);
zad7. col sal noprint
select ' ', ename, hiredate from emp union select '*', ename, hiredate from emp where hiredate=(select min(hiredate from emp)) order by sal;
clear columns

ps10.
zad1. select ename, job, dname from emp natural join dept;
zad2. select ename, count(*) from emp1 join implemp1 using(empno) group by ename;
zad3. select proname, end_date-start_date, (select count(*) from implproject1 IM where IM.prono=P.prono)
from project1 P inner join implproject1 R on(P.prono=R.prono);
zad4. select ename, grade, dname from emp E inner join dept D on(E.deptno=D.deptno) inner join salgrade on(sal between losal and hisal);
zad5.
zad6. select deptno, sum(nvl(comm, 0)+nvl(sal, 0)) from emp right outer join dept on(emp.empno=dept.deptno) group by dept.deptno;
zad7. select proname, count(impl)*A.budget from project1 A left outer join implproject1 B on(A.prono=B.prono) group by A.proname, A.budget order by 2;
zad8. select ename, job from emp1 cross join job1;
zad9. select ename, job from emp1 full outer join job1 on(emp1.job=job1.job);
zad10. (select ename, job from emp1 cross join job1) minus (select ename, job from emp1 full outer join job1 on(emp1.job, job1.job))
union
(select ename, job from emp1 full outer join job1 on(emp1.job, job1.job)) minus (select ename, job from emp1 cross join job1);

ps11.
zad1. create table NAUCZ_AK (id number(2) constraint Id_Naucz_Pk primary key, nazwisko varchar(20) not null, tytul varchar(10) not null);
create table PRZEDMIOT (id number(2) constraint Id_Przedm_Pk primary key, nazwa varchar(10) not null);
create table ZAJECIA (id number(2) constraint Id_Zaj_Pk primary key,
typ varchar(2) check (typ in ('w', 'ps', 'l', 's', 'c ')),
Id_Naucz number(2) not null,
Id_Zaj references PRZEDMIOT(id),
foregin key (Id_Naucz) references NAUCZ_AK(id));
zad2. alter table ZAJECIA add(constraint zajecia_naucz_ak foregin key (Id_Naucz) references NAUCZ_AK(id));
alter table ZAJECIA add(constraint zajecia_przedm foregin key (Id_Przedm) references PRZEDMIOT(id));
zad3. create table NAUCZ_2 as select nazwisko, tytul from NAUCZ_AK;
zad4. alter table NAUCZ_AK modify tytul check (tytul in('mgr', 'inż', 'lic'));
alter table NAUCZ_AK modify tytul default 'mgr';
zad5. alter table NAUCZ_AK add (stanowisko varchar2(20) check(stanowisko in('wykladowca', 'asystent', 'adiunkt', 'profesor')));
zad6. insert into NAUCZ_AK(id, nazwisko, tytul, stanowisko) values (1, 'Kowalski', 'mgr', 'wykladowca');
zad7. insert into PRZEDMIOT(id, nazwa) values (1, 'Bazy danych');
delete from ZAJECIA where id=1;
lub
delete from ZAJECIA where Id_Przedm=(select id from PRZEDMIOT where nazwa='Bazy danych');
zad8. alter table NAUCZ_AK add (staz number(2) check(staz between 1 and 40));
zad9. update NAUCZ_AK
set staz=5
where id=1;