SQL - dz5.pl

SQL: zadania + odpowiedzi, wszystkie
podstawowe schematy.
*Plik z przykładową bazą danych do przećwiczenia tutaj:
http://stud.ics.p.lodz.pl/~adrstud/pomoce_pliki/SQLPracownicy_dane.sql
1. W dniu 30.11.2011 przestali pracować pracownicy o numerze akt 9731 oraz 9411. Napisać zapytanie,
które pozwoli zaktualizować dane.
update pracownicy set data_zwol='2011-11-30' where nr_akt='9731' or nr_akt='9411';
2. Dane pracowników dopisać do tabeli prac_archiw
insert into prac_archiw select
nr_akt,nazwisko,stanowisko,kierownik,data_zatr,data_zwol,placa,dod_funkcyjny,prowizja,id_dzialu from
pracownicy where nr_akt='9731' or nr_akt='9411';
3. Usunąć dane tych pracowników z tabeli pracownicy
delete from pracownicy where nr_akt='9731' or nr_akt='9411';
4. Wypisać nazwisko, datę zatrudnienia, miesiąc i dzień dla wszystkich pracowników
select nazwisko,month(data_zatr) miesiac, day(data_zatr) dzien from pracownicy;
5. Wypisać nazwisko i rok zatrudnienia wszystkich pracowników
select nazwisko,year(data_zatr) rok from pracownicy;
6. Wpisać pracowników, którzy zostali zatrudnieni w kwietniu
select * from pracownicy where month(data_zatr)=4;
7. Wypisać nazwiska i liczbę lat pracy pracowników zapisanych w tabeli prac_archiw
select nazwisko, year(data_zwol)-year(data_zatr) as 'lata pracy' from prac_archiw;
8. Wypisać dzisiejszą datę
select getdate();
9. Podać ile lat zatrudnieni są pracownicy o numerze akt 9345 i 9403 (podać nr_akt, nazwisko i liczbę lat)
select nr_akt,nazwisko, year(getdate())-year(data_zatr) from pracownicy where nr_akt='9345' or
nr_akt='9403';
10. Podać ilu pracowników pracuje w poszczególnych działach (nazwa działu, liczba pracowników)
select dzialy.nazwa,count(*) from pracownicy join dzialy on pracownicy.id_dzialu=dzialy.id_dzialu
group by dzialy.nazwa;
11. Podać ile pracowników pracuje w dziale produkcja
select count(*) from pracownicy where id_dzialu = (select id_dzialu from dzialy where
dzialy.nazwa='PRODUKCJA');
12. Podać ile osób pracuje na każdym stanowisku
select count(*),stanowisko from pracownicy group by stanowisko;
13. Podać łączną płace dla stanowiska dyrektor i logistyk
select sum(placa) from pracownicy where stanowisko='dyrektor' or stanowisko='logistyk';
14. Wypisać wartość średniej płacy
select avg(placa) from pracownicy;
15. Wypisać nazwisko i płacę pracowników, których płaca jest większa od średniej płacy
select nazwisko,placa from pracownicy where placa>(select avg(placa) from pracownicy) order by
nazwisko;
16. Wpisać pracowników, których płaca jest najwyższa
select nazwisko from pracownicy where placa=(select max(placa) from pracownicy);
17. Napisać zapytanie, które zmieni płacę prezesa (nr_akt 8901) na 5000.
update pracownicy set placa=5000 where nr_akt=8901;
18. Wypisać nazwisko pracownika, stanowisko, płacę, płacę minimalną na tym stanowisku i płacę
maksymalną.
select
pracownicy.nazwisko,pracownicy.stanowisko,pracownicy.placa,stanowiska.placa_min,stanowiska.placa_
max from pracownicy, stanowiska where pracownicy.stanowisko=stanowiska.stanowisko order by
pracownicy.stanowisko;
19. Wypisać nazwiska pracowników, których płaca jest równa płacy minimalnej na tym stanowisku.
select nazwisko from pracownicy join stanowiska on pracownicy.stanowisko=stanowiska.stanowisko
where pracownicy.placa=stanowiska.placa_min;
20. Podać płacę minimalną i maksymalną w tabeli Pracownicy
select min(placa) minimalna,max(placa) maksymalna from pracownicy;
21. Wypisać nazwisko, stanowisko i nazwę działu uporządkowane działami
select pracownicy.nazwisko, pracownicy.stanowisko, dzialy.nazwa from pracownicy,dzialy where
pracownicy.id_dzialu=dzialy.id_dzialu order by dzialy.nazwa;
22. Wypisać nazwisko pracownika i wypłatę (płaca + wszystkich dodatki)
select nazwisko, isnull(placa,0)+isnull(dod_funkcyjny,0) from pracownicy;
23. Podać jaką wypłatę otrzyma pracownik Wierzbicki.
select nazwisko,isnull(placa,0)+isnull(dod_funkcyjny,0) from pracownicy where nazwisko='Wierzbicki';
24. Wypisać nazwiska pracowników, którzy nie są przypisani do działu.
select nazwisko from pracownicy where id_dzialu is null;
25. Wypisać identyfikator oraz nazwę działu, który nie zatrudnia pracowników
select distinct dzialy.id_dzialu,dzialy.nazwa from dzialy,pracownicy where dzialy.id_dzialu not in (select
distinct id_dzialu from pracownicy where id_dzialu is not null);
26. Wypisać nazwisko, stanowisko i nazwę działu. Wynik zapytania powinien wypisać nazwiska wszystkich
pracowników i nazwy wszystkich działów
select pracownicy.nazwisko,pracownicy.stanowisko,dzialy.nazwa from pracownicy,dzialy where
pracownicy.id_dzialu=dzialy.id_dzialu union all select pracownicy.nazwisko,pracownicy.stanowisko,
null from pracownicy where pracownicy.id_dzialu is null union select null, null, dzialy.nazwa from dzialy
where dzialy.id_dzialu not in (select distinct id_dzialu from pracownicy where id_dzialu is not null);
27. Podać łączną sumę wszystkich wypłat
select sum(placa+isnull(dod_funkcyjny,0)+isnull(prowizja,0)) as 'suma wyplat' from pracownicy;
28. Podać sumę wypłat dla każdego działu
select dzialy.nazwa, sum(placa+isnull(dod_funkcyjny,0)+isnull(prowizja,0))as 'suma wyplat' from
pracownicy,dzialy where pracownicy.id_dzialu=dzialy.id_dzialu group by dzialy.nazwa;