1.Dla każdego pracownika odpowiedzialnego za jakiś przedmiot podaj liczbę przedmiotów z których prowadzi zajęcia (tabela ROZKLADY).
select nazwisko, count(distinct nr_przedm) from pracownicy p, rozklady r where p.nr_prac=r.nr_prac and p.nr_prac in (select distinct nr_odp_prac from przedmioty) group by nazwisko;
2. Podaj nazwiska i średnią ocen z przedmiotu ALGEBRA tych studentów, którzy z tego przedmiotu mają więcej niż jedną ocenę.
select nazwisko, avg(ocena) from oceny o, studenci s, przedmioty p where o.nr_stud=s.nr_stud and p.nr_przedm=o.nr_przedm and p.nazwa_przedm="ALGEBRA" group by nazwisko having count(ocena)>1;
3. Podaj przedmioty realizowane przez największą liczbę pracowników.
select nazwa_przedm from przedmioty p, rozklady r where p.nr_przedm=r.nr_przedm group by nazwa_przedm having count(distinct nr_prac)>=all(select count(distinct nr_prac) from przedmioty p, rozklady r where p.nr_przedm=r.nr_przedm group by nazwa_przedm);
4. Podaj numery i nazwiska pracowników, którzy w temacie DYSK otrzymali wypłatę mniejszą od co najmniej jednej z wypłat pracownika o nazwisku GRZYBEK w dowolnym temacie.
select p.nr_prac, nazwisko from pracownicy p, tematy t, wyplaty w where p.nr_prac=w.nr_prac and w.nr_tem=t.nr_tem and temat="DYSK" and kwota<any(select kwota from pracownicy p, wyplaty w where p.nr_prac=w.nr_prac and nazwisko="GRZYBEK");
5. Podaj numery, nazwiska i daty urodzenia mężczyzn zatrudnionych w zespole OPROGRAMOWANIE, którzy są młodsi od każdego pracownika zespołu BUDOWA.
select nr_prac, nazwisko, data_ur from pracownicy p, zespoly z where p.nr_zesp=z.nr_zesp and plec='M' and nazwa_zesp='OPROGRAMOWANIE' and data_ur>all(select data_ur from pracownicy p, zespoly z where p.nr_zesp=z.nr_zesp and nazwa_zesp='BUDOWA');
6. Podaj nazwiska najstarszych studentów na poszczególnych kierunkach.
select nazwisko from studenci s, kierunki k where s.nr_kier=k.nr_kier and data_ur=(select min(data_ur) from studenci s2 where s2.nr_kier=k.nr_kier );
7. Podaj nazwy zespołów, w których nie pracuje żadna kobieta.
select nazwa_zesp from zespoly z where nr_zesp not in (select distinct nr_zesp from pracownicy p where p.plec='K');
8. Zestawienie zespołów z liczbą pracowników o postaci:
Nrz nazwa_zespołu liczebność
W zestawieniu powinny znaleźć się również zespoły z liczebnością zerową.
select nr_zesp as 'Nrz', nazwa_zesp as 'nazwa_zespołu', (select count(*) from pracownicy p where p.nr_zesp=z.nr_zesp) as 'liczebność' from zespoly z;
9. Utworzyć perspektywę zawierającą wyszczególnione informacje, oraz określić, czy można aktualizować dane w tej perspektywie. Jeżeli nie można - podać wszystkie tego przyczyny.
Po pomyślnym wykonaniu zadania należy usunąć utworzoną przez siebie perspektywę.
Zestawienie pracowników mających wypłaty w poszczególnych tematach w postaci:
nr_tem nazwa_tematu nr_prac nazwisko
Rekordy nie powinny powtarzać się nawet w sytuacji, gdy pracownik uzyskał wielokrotnie dochód w tym samym temacie.
create view zad9_michalik as(select distinct t.nr_tem, temat as 'nazwa_tematu', p.nr_prac, nazwisko from pracownicy p, tematy t, wyplaty w where p.nr_prac=w.nr_prac and w.nr_tem=t.nr_tem);
10. 1.Zrealizować zestawienie średnich, minimalnych i maksymalnych zarobków pracowników w poszczególnych zespołach.
Uwaga!
Zestawienie zrealizować w dwóch krokach:
w pierwszym utworzyć perspektywę (widok) o postaci:
nrz nazwa_zesp nr_prac nazwisko suma_zarobków_prac
uwzględnić również fakt, że pracownik mógł nie dostać żadnej wypłaty,
utworzoną perspektywę wykorzystać w zadaniu.
create view zad10_michalik as
(select z.nr_zesp as nrz, nazwa_zesp, p.nr_prac, nazwisko, sum(kwota) as suma
from (zespoly z join pracownicy p on p.nr_zesp=z.nr_zesp) left outer join wyplaty w on w.nr_prac=p.nr_prac group by nrz, nazwa_zesp, p.nr_prac, nazwisko);
select nazwa_zesp, avg(suma), max(suma), min(suma) from zad10_michalik group by nazwa_zesp;
11. Podaj nazwiska pracowników, którzy zostali przydzieleni do tematu, lecz nie otrzymali w tym temacie ani jednej wypłaty.
select distinct nazwisko from (pracownicy p join przydzialy pd on p.nr_prac=pd.nr_prac) left outer join wyplaty w on p.nr_prac=w.nr_prac where kwota is null;
12. Sprawdzić poprawność przydziału kierowników do tematu (jest to niezależnie określone za pomocą klucza obcego nr_prac_kt w tabeli TEMATY oraz przez zapis w tabeli PRZYDZIAŁY – należy sprawdzić czy oba te zapisy sobie odpowiadają).
select * from tematy t left outer join przydzialy pd on t.nr_tem=pd.nr_tem and pd.nr_prac=t.nr_prac_kt and (kod_funkcji is null or kod_funkcji not like 'KRW')
13. Podaj numery i nazwiska pracowników nie biorących udziału w żadnym z tematów, w którym pracuje pracownik NIEZALEZNY.
select distinct p0.nr_prac, p0.nazwisko from pracownicy p0 where p0.nr_prac not in (select p1.nr_prac from pracownicy p1, przydzialy pd1 where pd1.nr_prac=p1.nr_prac and pd1.nr_tem in (select t0.nr_tem from pracownicy p2, przydzialy pd2, tematy t0 where pd2.nr_prac=p2.nr_prac and pd2.nr_tem=t0.nr_tem and p2.nazwisko="NIEZALEZNY"));
14. Podaj nazwiska pracowników którzy realizowali wszystkie tematy, których kierownikiem jest JASKOLA.
select p.nazwisko from pracownicy p where p.nr_prac not in (select p1.nr_prac from pracownicy p1, przydzialy pd1 where p1.nr_prac=pd1.nr_prac and pd1.nr_tem not in (select t2.nr_tem from pracownicy p2 join tematy t2 on p2.nr_prac=t2.nr_prac_kt where p2.nazwisko='JASKOLA'))