/*#1*/

CREATE DIRECTORY INF19338_OCENY
AS '/home/oracle/hd_cwiczenia';

/*#2*/
create table OCENY_LEKTORAT_ZEWN(
student_id number(6),rok_semestr_akademicki varchar(10),semestr_studiow varchar(2),przedmiot varchar(60),ocena varchar(20))
organization external
(type oracle_loader
default directory INF19338_OCENY
access parameters (
records delimited by newline
badfile 'odrzucone.txt' logfile 'dziennik.txt' skip 2
fields terminated by ';' missing field values are null)
location ('oceny_lektorat.txt'))

/*#3*/
select count(*) from OCENY_LEKTORAT_ZEWN;

/*#4*/
create or replace type tOceny as object(
student_id number(6),
rok_akademicki varchar(7),
rodzaj_semestru varchar(6),
semestr_studiow number(2),
przedmiot varchar(60),
rodzaj_zajec varchar(2),
rodzaj_zaliczenia varchar(2),
ocena number(2,3) --tu jest błąd
)

/*#5*/
create or replace type tTablicaOceny
as table of tOceny

/*#6*/
create or replace function OCENY_LEKTORAT_ZEW_TAB return tTablicaOceny pipelined is
cursor cRaty is
select student_id, rok_semestr_akademicki, semestr_studiow, przedmiot, ocena--tu wypisuje wartosci z tabeli z której będę mapowa na nowe wartości #1
from OCENY_LEKTORAT_ZEWN;
begin
for R in cRaty loop
pipe row(tOceny(
R.student_id,
substr(R.rok_semestr_akademicki,0,7),
(case
when substr(R.rok_semestr_akademicki,9) ='l' then 'letni'
when substr(R.rok_semestr_akademicki,9) ='z' then 'zimowy'
else 'error' end),
R.semestr_studiow,
(case
when R.przedmiot ='J. francuski' then 'Język francuski'
when R.przedmiot ='J. angielski' then 'Język angielski'
when R.przedmiot ='J. niemiecki' then 'Język niemiecki'
else R.przedmiot end)
,
'LK',
'O',
(case
when R.ocena ='niedostateczny' then '1.0'
when R.ocena ='dopuszczający' then '2.0'
when R.ocena ='dostateczny' then '3.0'
when R.ocena ='dobry' then '4.0'
when R.ocena ='bardzo dobry' then '4.0'
when R.ocena ='celujący' then '6.0'
else R.ocena end)
)
);
end loop;
return;
end;

/*#7*/
SELECT * from table(OCENY_LEKTORAT_ZEW_TAB) order by student_id;

/*#8 Nie można stworzyć widoku "insufficient privileges"*/

SELECT
STUDENT_ID,
ROK_AKADEMICKI,
przedmiot.NAZWA ,
RODZAJ_ZAJEC,
RODZAJ_ZALICZENIA,
SEMESTR_STUDIOW,
rodzaj_semestru,
ocena
FROM oceny tab
JOIN przedmioty przedmiot ON przedmiot.PRZEDMIOT_ID=tab.PRZEDMIOT_ID

UNION ALL

SELECT
STUDENT_ID,
ROK_AKADEMICKI,
funk.przedmiot ,
RODZAJ_ZAJEC,
RODZAJ_ZALICZENIA,
SEMESTR_STUDIOW,
rodzaj_semestru,
ocena
FROM
table(ar2) funk

/*#9 wersja alternatywna */

SELECT count(*)
FROM (
SELECT
STUDENT_ID,
ROK_AKADEMICKI,
przedmiot.NAZWA ,
RODZAJ_ZAJEC,
RODZAJ_ZALICZENIA,
SEMESTR_STUDIOW,
rodzaj_semestru,
ocena
FROM oceny tab
JOIN przedmioty przedmiot ON przedmiot.PRZEDMIOT_ID=tab.PRZEDMIOT_ID

UNION ALL

SELECT
STUDENT_ID,
ROK_AKADEMICKI,
funk.przedmiot ,
RODZAJ_ZAJEC,
RODZAJ_ZALICZENIA,
SEMESTR_STUDIOW,
rodzaj_semestru,
ocena
FROM
table(ar2) funk
ORDER BY student_id

);

/*#10 wersja alternatywna */

SELECT *
FROM (
SELECT
STUDENT_ID,
ROK_AKADEMICKI,
przedmiot.NAZWA ,
RODZAJ_ZAJEC,
RODZAJ_ZALICZENIA,
SEMESTR_STUDIOW,
rodzaj_semestru,
ocena
FROM oceny tab
JOIN przedmioty przedmiot ON przedmiot.PRZEDMIOT_ID=tab.PRZEDMIOT_ID

UNION ALL

SELECT
STUDENT_ID,
ROK_AKADEMICKI,
funk.przedmiot ,
RODZAJ_ZAJEC,
RODZAJ_ZALICZENIA,
SEMESTR_STUDIOW,
rodzaj_semestru,
ocena
FROM
table(ar2) funk
ORDER BY student_id

);