SQL – wykład i ćwiczenia z Politechniki Poznańskiej

Przez ostatnie dni byłem skupiony wyłącznie na SQL. Przerobiłem cały materiał wykładu z Politechniki Poznańskiej odnośnie Baz Danych i muszę przyznać, że miałem spore braki. Trening czyni mistrzem dlatego też przerobiłem masę złożonych zapytań.
Ostatnio robiłem test na Młodszego Programistę Baz Danych i niestety wykazał braki jakie miałem w SQL – dziś natomiast napisał bym go bez najmniejszych problemów.

A oto wycinek tego nad czym pracowałem:

 

1.Dla każdego pracownika wygeneruj kod składający się z dwoch pierwszych liter nazwy jego etatu
select id_prac,nazwisko,imie, substr(etat, 1,2) as etat ,id_szefa,zatrudniony ,placa_pod,placa_dod,id_zespolu from pracownicy

2. Pokaż nazwiska i lace podstawowe powiększone o 15% i zaokrąglone do liczy całkowitej bez przecinka.
select nazwisko, round(placa_pod*1.15,0) as placa_pod from pracownicy
3. Pokaż ile lat jest zatrudniony pracwonik do teraz
select nazwisko, imie, round(DATEDIFF(curdate(),zatrudniony)/365)  as zatrudniony from pracownicy

4. Wypisz dzień w którym został zatrudniony pracownik
select case when DATE_FORMAT(zatrudniony,”%a”)=”Mon” then „Poniedziałek”  when DATE_FORMAT(zatrudniony,”%a”)=”Tue” then „Wtorek ” when DATE_FORMAT(zatrudniony,”%a”)=”Wed” then „Środa” when DATE_FORMAT(zatrudniony,”%a”)=”Thu” then „Czwartek” when DATE_FORMAT(zatrudniony,”%a”)=”Fri” then „Piątek”END as zatrudniony from pracownicy

5.Wyświetl informacje o zespołach, przyjmij ze ul Mielzynskiego i Strzelecka to dzielnica STARE MIASTO,
Piotrowo to NOWE MIASTO a Wieniawskiego to GRUNWALD
SELECT id_zesp, nazwa_zesp,adres, case when adres like „Piotrowo%” then „Nowe miasto”  when adres like „MIELZYNSKIEGO%” OR adres like „strzelecka%” then „Stare miasto” when adres like „WIENIAWSKIEGO%” then „Grunwald” END as id_etat from zespoly

6.Wyświetl nazwy etatow, na które przyjeto pracowników w latach 1993 i 1992
select zatrudniony,etat from pracownicy where extract(year from zatrudniony)=”1993″ or extract(year from zatrudniony)=”1992″

7. znajdź max płace i min płace zespołu nr 10
select round(min(placa_pod)) as placa_pod, max(placa_pod) as placa_dod from pracownicy Where id_zespolu=10

8.Znajdź średnią wartosc płacy podstawowej wśród pracowników każdego zespołu, posortuj według id_zespołu
select id_zespolu,  round(avg(placa_pod)) as placa_pod from pracownicy group by id_zespolu order by id_zespolu
9.zlicza ilosc pracownikow w danym zespole
select id_zespolu,  count(*) as id_szefa from pracownicy group by id_zespolu

10.podaj id i średnie płace w zespołach zetrudniajacych conajmniej 3 pracownikow
select id_zespolu, round(avg(placa_pod)) as placa_pod from pracownicy group by id_zespolu having count(*)>=3

11. Podaj srednia place podstawowa, ale tylko wtsy gdy ilos pracownikow jest wieksza niz 10
select id_zespolu, avg(placa_pod) as placa_pod from pracownicy group by id_zespolu having count(*)>10

 12. Dla każdego zespolu, w ktorym srednia placa przekracza 1000 podaj liczbe pracownikow pomojajac tych na etacie profesor, wynik uporzadkuj ze wzgledu na sume plac w zespole  (uzyta konkatanacja)
select id_zespolu,count(*) as nazwisko, concat(„Średnia płaca zespołu „,round(avg(placa_pod))) as imie , concat(„Suma płac w zespole „,round(sum(placa_pod))) as placa_pod  from pracownicy where etat!=”profesor” group by id_zespolu  having avg(placa_pod)>1000 order by sum(placa_pod) desc

13. Podaj najwyższa i najniższa pensję podtawowa oraz jej róznice
select concat(„Pensja najwyższa „, max(placa_pod)) as placa_pod , concat(„Pensja najniższa „, min(placa_pod)) as placa_dod, concat(„Różnica w pensji max i min „, max(placa_pod)-(min(placa_pod))) as id_zespolu  from pracownicy

14. Wyświetl srednie pensje dla wszystkich etatow. Posortuyj wedlug sredniej pensji malejaco
select etat, round(avg(placa_pod)) as placa_pod from pracownicy group by etat order by avg(placa_pod) DESC

 15. wyświetl liczbe pracowników na etacie profesora
select etat, count(*) as placa_pod from pracownicy where etat=”profesor”

 16. Wyswietl miesieczne place kazdego z zespolow uwzgledniajac place podstawowa i dodatkowa
select id_zespolu, round(sum(placa_pod+placa_dod)) as placa_pod from pracownicy group by id_zespolu order by placa_pod desc

 17. wyswietl nr zespolow, krtore zatrudniaja wiecej niz 2 pracownikow. Pomin pracownikow bez etatu. Posortuj wynik wg malejacej liczby pracownikow
select id_zespolu, count(*) as placa_dod from pracownicy where (etat=”adiunkt” or etat =”asystent” or etat=”doktorant” or etat=”profesor” or etat=”sekretarka”) group by id_zespolu having count(*)>2 order by  count(*) desc

 18. Pokaż srednie pensje wypłacane w ramach etatów i liczbe pracowników zatrudnionych na danym etacie. Pomiń pracowników zatrudnionych po 1990 i posortuj wedlug najwyzszej sredniej
select etat, concat(„Średnia płaca „,round(avg(placa_pod))) as placa_pod, concat(„Ilośc pracowników „,count(*)) as placa_dod from pracownicy where DATE_FORMAT(zatrudniony,”%Y”)<1990 group by etat order by placa_pod desc

 19. wyświetl wszystkie kompinacje nazw etatow zaczynajacych si ena litere „N” i nazwisk pracowników na litere „N”
select nazwa_etat, nazwisko from etaty cross join  pracownicy  where nazwa_etat like „A%” and nazwisko like „N%”

 20. Dla kazdego pracownika zatrudnionego na etacie DYREKTOR lub SEKRETARKA wyswietl widełki lacowe oraz pensje podstawowa
select pracownicy.nazwisko, pracownicy.imie, pracownicy.etat, pracownicy.placa_pod, etaty.placa_od, etaty.placa_do  from pracownicy, etaty where (pracownicy.etat=”dyrektor” or pracownicy.etat=”sekretarka”) and  pracownicy.etat=etaty.nazwa_etat

 21. dla kazdego zespolu wyswietl ilosc  pracujacych pracowników
select concat(„Ilośc pracowników „,count(*)) as placa_dod , id_zespolu from pracownicy group by id_zespolu

 22. Wyświetl nazwiska i ertaty pracowników, których rzeczywiste zarobki odpowiadają widełkom płacowym przewidzianym dla sekretarki
select pracownicy.nazwisko, pracownicy.imie, pracownicy.etat from pracownicy, etaty where  pracownicy.placa_pod between etaty.placa_od AND etaty.placa_do and  etaty.nazwa_etat=”Sekretarka”

 23.wyświetl nazwiska wszysttkich pracowników, którzy zarabniaja wiecej od nowickiego
select p.placa_pod, p.nazwisko from pracownicy p join pracownicy r  on p.placa_pod>r.placa_pod where r.nazwisko= „nowicki”

 24. wyświetl nazwiska, etaty, numery zespołów i nazwy zespołów wszytskich pracownikow
select pracownicy.nazwisko, pracownicy.etat, pracownicy.id_zespolu, zespoly.nazwa_zesp from pracownicy, zespoly where zespoly.id_zesp=pracownicy.id_zespolu

 25. wyświetl pracowników z ulicy PIOTROWO 3a. Sortuj wedlug nazwiska
SELECT pracownicy.id_zespolu,pracownicy.nazwisko, pracownicy.imie from zespoly, pracownicy where zespoly.adres like „piotrowo 3a%” and zespoly.id_zesp=pracownicy.id_zespolu order by pracownicy.nazwisko ASC

 26. wyświetl nazwiska, miejse pracy oraz nazwy zespołów pracowników których miesięczna pensja przekracza 1000
SELECT zespoly.adres, zespoly.nazwa_zesp,pracownicy.placa_pod,pracownicy.id_zespolu,pracownicy.nazwisko, pracownicy.imie from zespoly, pracownicy where pracownicy.placa_pod>1000
and zespoly.id_zesp=pracownicy.id_zespolu
27. Dla każdego pracownika wyświetl jego kategorię płacową i widełki płącowe w jakich mieści się jego pensja
select pracownicy.nazwisko, pracownicy.imie, pracownicy.placa_pod, etaty.placa_od, etaty.placa_do from pracownicy, etaty where pracownicy.etat=etaty.nazwa_etat

28. wyświetl dane pracowników, wynagrodzenie , nazwy zespolu pracownikow nie bedacych asystentami. sortuj wedlug malejacego wynagrodzenia
select pracownicy.etat, pracownicy.nazwisko, pracownicy.imie,pracownicy.placa_pod, zespoly.nazwa_zesp, etaty.placa_od, etaty.placa_do from pracownicy, etaty, zespoly where pracownicy.etat!=”asystent” and pracownicy.id_zespolu=zespoly.id_zesp and pracownicy.etat=etaty.nazwa_etat order by pracownicy.placa_pod desc

29. nazwiska, etat, dochody (placa podstawowo i dodatkowa), naza zespolu dla kategoru placowej PROFESOR i ASYSTENT gdzie suma zarobkow jest wieksza niz 15000
SELECT pracownicy.nazwisko, pracownicy.imie, pracownicy.etat, round(sum(placa_pod+placa_dod)) as placa_pod from pracownicy, zespoly  where (pracownicy.etat=”asystent” or pracownicy.etat=”profesor”)  group by pracownicy.nazwisko having sum(placa_pod+placa_dod)>15000

30. Wyświetl nazwiska i numery pracowników wraz z numerami i nazwiskami ich szefów
SELECT r.nazwisko, r.imie, r.id_szefa , l.nazwisko as placa_od , l.imie , l.id_szefa as placa_do from pracownicy r join pracownicy l  on (r.id_szefa=l.id_prac)

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *