Przykładowe pytania / zadania na kolokwium nr 2. 1. Wymienić i opisać 3 pierwsze postacie normalne, na przykładach. 2. Czym jest redundancja danych. 3. Wymienić i opisać wszystkie rodzaje złączeń. 4. Wymienić i opisać szczegółowo 6 funkcji operujących na ciągach. 5. Mamy następujące tabele: 6. Wymienić i opisać typy parametrów funkcji i procedur. Studenci: ID_studenci, imie, nazwisko, indeks, PESEL, data_urodzenia Uczelnia: ID_uczelnia, nazwa Wydzialy: ID_wydzialy, nazwa, ID_uczelnia Kierunki: ID_kierunki, nazwa, ID_wydzial, ID_stopien Przedmioty: ID_przedmioty, nazwa, ID_wydzial Pracownicy: ID_pracownicy, imie, nazwisko, ID_instytuty Instytuty: ID_instytuty, nazwa, ID_wydzial Oceny: ID_oceny, ocena_liczba, ocena_slownie Oceny_studentow: ID_os, ID_studenci, ID_oceny, ID_przedmioty, data_wystawienia, ID_pracownicy, ID_ra Zaliczenia: ID_zaliczenia, ID_studenci, ID_przedmioty, ID_oceny, data_zaliczenia, ID_ra Rok_akademicki: ID_ra, nazwa, data_od, data_do --(np. '1,'2012/2013', ...) Tryb_studiow: ID_ts, nazwa --(np. stacjonarne, niestacjonarne) Stopien: ID_stopien, nazwa --(np. 'I', 'II', 'III') Status_studenta: ID_ss, nazwa --(np. 'studiuje', 'skreślony') Studenci_statusy: ID_stst, ID_studenci, ID_ss, ID_wydzial, ID_stopien, ID_ts, ID_ra 7. Obliczyć średnią ocen uzyskanych przez wszystkich studentów w roku akademickim 2012/2013 i wyświetlić ją w postaci litery dla poszczególnych uczelni. select u.nazwa, avg(ocena_liczba) from oceny_studentow o natural join oceny natural join przedmioty join wydzialy w on id_wydzial=id_wydzialy join uczelnia u on w.id_uczelnia = u.id_uczelnia join rok_akademicki r on o.id_ra = r.id_ra where data_od = 2012 and data_do = 2013 group by u.nazwa; select uczelnia, case when srednia<3 then 'F' when srednia>=3 and srednia <3.5 then 'E' when srednia>=3.5 and srednia <4 then 'D' when srednia>=4 and srednia <4.5 then 'C' when srednia>=4.5 and srednia <5 then 'B' when srednia>=5 then 'A' end as srednia from (select uczelnia.nazwa as uczelnia, avg(ocena_liczba) as srednia from oceny_studentow natural join oceny natural join rok_akademicki inner join przedmioty using(id_przedmioty) inner join wydzialy using(id_wydzialy) inner join uczelnia using(id_uczelnia) where rok_akademicki.nazwa = '1' group by uczelnia.nazwa); 8. Obliczyć średnią ocen uzyskanych na poszczególnych kierunkach w ramach zaliczeń. Wyświetlić nazwę nazwę uczelni, wydziału, kierunku, średnią ocen. 9. Utworzyć widok zwracający nazwy przedmiotów, nazwę kierunku, nazwę wydziału, nazwę uczelni. create or replace view v_Uczelnia as select (select listagg(nazwa,', ') within group (order by nazwa) from przedmioty where id_wydzial = w.id_wydzialy) "Nazwy przedmiotow", k.nazwa "Nazwa kierunku", w.nazwa "Nazwa wydzialu", u.nazwa "Nazwa uczelni" from kierunki k join wydzialy w on k.id_wydzial = w.id_wydzialy join uczelnia u on w.id_uczelnia=u.id_uczelnia; 10. Napisać polecenie uniwersalne, wyświetlające oceny studentów (ocena, imię, nazwisko), którzy ukończyli 21 lat i studiują na kierunku 'informatyka' z uwzględnieniem wszystkich uczelni. select imie,nazwisko, ocena_liczba from oceny_studentow natural join oceny natural join studenci where to_number(to_char(sysdate, 'YYYY')) - to_number(to_char(data_urodzenia, 'YYYY')) >21; 11. Utworzyć widok wyświetlający tych studentów, którzy mieli zajęcia przynajmniej raz z pracownikiem Instytutu Informatyki. 12. Utworzyć widok wyświetlający tych studentów, którzy mieli zajęcia przynajmniej raz z pracownikiem Instytutu Informatyki i są na studiach niestacjonarnych. Wyświetlić w kolumnie o nazwie student: imię, nazwisko, indeks oraz pracownik: imię, nazwisko. Posrtować listę względem nazwisk i imion studentów rosnąco. 13. Wyświetlić listę uczelni oraz liczbę kierunków na danej uczelni. select nazwa, ( select count(*) from kierunki k join wydzialy w on k.id_wydzial=w.id_wydzialy where id_uczelnia = u.id_uczelnia ) "Liczba kierunków" from uczelnia u ; 14. Obliczyć procent liczby kobiet (mężczyzn) wśród studentów na danych uczelniach. W oparciu o PESEL. select nazwa, case s when 0 then 0 else (sk/s)*100 end stosunek from ( select nazwa, ( select count(*) from studenci_statusy s natural join status_studenta ss join wydzialy w on s.id_wydzial=w.id_wydzialy where id_uczelnia = u.id_uczelnia and ss.nazwa ='studiuje' ) s, ( select count(*) from studenci_statusy s natural join studenci natural join status_studenta ss join wydzialy w on s.id_wydzial=w.id_wydzialy where id_uczelnia = u.id_uczelnia and ss.nazwa ='studiuje' and mod(substr(to_char(pesel),10, 1),2) = 0 ) sk from uczelnia u ); 15. Obliczyć procnt liczby kobiet (mężczyzn) wśród studentów na poszczególnych wydziałach danych uczelni. W oparciu o PESEL. select nu "Nazwa uczelni",nw "Nazwa wydzialu", case s when 0 then 0 else (sk/s)*100 end stosunek from ( select u.nazwa nu, w.nazwa nw, ( select count(*) from studenci_statusy s natural join status_studenta ss join wydzialy w on s.id_wydzial=w.id_wydzialy where id_wydzial = w.id_wydzialy and ss.nazwa ='studiuje' ) s, ( select count(*) from studenci_statusy s natural join studenci natural join status_studenta ss join wydzialy w on s.id_wydzial=w.id_wydzialy where id_wydzial = w.id_wydzialy and ss.nazwa ='studiuje' and mod(substr(to_char(pesel),10, 1),2) = 0 ) sk from wydzialy w join uczelnia u on w.id_uczelnia=u.id_uczelnia); 16. W którym roku akademickim na danych uczelniach był największy odsetek kobiet (mężczyzn) wśród studentów. 17. W którym roku akademickim na danych uczelniach był największy odsetek kobiet (mężczyzn) wśród studentów studiów niestacjonarnych. 18. Uaktualnić oceny w tabeli Zaliczenia na bazie ocen z przedmiotów w aktualnym roku akademickim. Użyć case end. select i srednia, case when i<3 then 2 when i>=3 and i <3.5 then 3 when i>=3.5 and i <4 then 3.5 when i>=4 and i <4.5 then 4 when i>=4.5 and i <5 then 4.5 when i>=5 then 5 end ocena from ( select i from tmp ) 19. itd. itp.