% utworz tabele
create table oceny(nr_indeksu integer, kod_przedmiotu varchar(255), ocena_lab decimal, ocena_egz decimal, primary key(nr_indeksu));
create table zamieszkanie(nr_indeksu integer, nr_ds integer, primary key(nr_indeksu));
create table dane(nr_indeksu integer, imie varchar(255), nazwisko varchar(255), primary key(nr_indeksu));
create table przedmioty(prowadzacy varchar(255), przedmiot varchar(255), typ varchar(255), primary key(przedmiot,typ));
create table zajecia(przedmiot varchar(255), typ varchar(255), sala integer, primary key(przedmiot,typ));

% wprowadz dane
insert into zajecia values
    ('Hiszpanski', 'WYK', 100),
    ('Hiszpanski', 'LAB', 22),
    ('GEOLOGIA', 'WYK', 100),
    ('GEOLOGIA', 'LAB', 12)
;

insert into przedmioty values
    ('Nowacka', 'Hiszpanski', 'WYK'),
    ('Majka', 'Hiszpanski', 'LAB'),
    ('Baka', 'GEOLOGIA', 'WYK'),
    ('Majka', 'GEOLOGIA', 'LAB')
;

insert into dane values
    (123456, 'Ala', 'Nowak'),
    (123123, 'Krzysztof', 'Kowalski'),
    (456456, 'Jerzy', 'Pszoniak'),
    (456123, 'Jolanta', 'Ostrowska'),
    (111111, 'Maja', 'Pach'),
    (666666, 'Olaf', 'Bach')
;

insert into zamieszkanie values
    (123456, 12),
    (123123,12),
    (456456,6),
    (456123,12)
;

insert into oceny values
    (111111, 'Hisz', 3, 4),
    (666666, 'Geol', 3, 3),
    (123456, 'Geol', 4, 5),
    (123123, 'Geol', 5, 4.5),
    (456123, 'Hisz', 5, 6)
;

% 1) wyswietl wszystkie numery indeksow, jakie sa uzywane w tabelach oceny i zamieszkanie
select nr_indeksu from oceny union select nr_indeksu from zamieszkanie;

% 2) wyswietl te numery indeksow, dla ktorych sa oceny, a ktorych wlasciciele mieszkaja w akademikach
select nr_indeksu from oceny INTERSECT select nr_indeksu from zamieszkanie;

% 3) wyswietl te numery indeksow, dla ktorych sa oceny, a ktorych wlasiciele nie mieszkaja w akademikach
% lub brak jest danych na ten temat
select nr_indeksu from oceny EXCEPT select nr_indeksu from zamieszkanie;

% 4) wyswietl iloczyn krzyzowy relacji przedmioty i zajecia
select * FROM przedmioty CROSS JOIN zajecia;

% 5) wyswietl zlaczenie naturalne relacji przedmioty i zajecia
select * FROM przedmioty,zajecia where przedmioty.przedmiot=zajecia.przedmiot;

% 6) wyswietl imie i nazwisko osob, ktore nie mieszkaja w akademikach (lub brak danych) i maja srednia mniejsza niz 4.0
select imie,nazwisko FROM dane,oceny,zamieszkanie
    where oceny.nr_indeksu=dane.nr_indeksu and zamieszkanie.nr_indeksu=dane.nr_indeksu and (ocena_egz+ocena_lab)/2 < 4
;

% 7) złaczenie naturalne tabel przedmioty i zajecia (dwa sposoby)
% pierwszy sposob jest w zad 5
% sposob drugi:
 select * FROM przedmioty inner join zajecia USING(przedmiot);