zadania bazy poprawione

1. Utwórz tabele o nazwie FIRMA zawierającą kolumny: ID_PRACOWNIKA klucz główny typ NUMERIC, IMIE
CHAR(30), NAZWISKO CHAR(60), STANOWISKO CHAR(60), ZAROBEK NUMERIC, DZIAL CHAR(30)

CREATE TABLE firma(
    id_pracownika INT PRIMARY KEY,
    imie VARCHAR(30),
    nazwisko VARCHAR(50),
    stanowisko VARCHAR(30),
    zarobek INT,
    dzial VARCHAR(30)
);

2. Wypełnij pierwszy wiersz tabeli nie wstawiając żadnej wartości w pole DZIAL

INSERT INTO FIRMA (id_pracownika, imie, nazwisko, stanowisko, zarobek)
VALUES (7369, 'Jan', 'Kowalski', 'Sysadm', 15000);

3. Dodaj do tabeli FRIMA kolumnę MIASTO CHAR(30), gdzie domyślna wartość TO KATOWICE

ALTER TABLE firma ADD miasto VARCHAR(30);

4. Wyświetl zawartość tabeli aby zobaczyć rezultat

SELECT * FROM firma;

5. Wypełnij wartość w kolumnie DZIAL wstawiając KADRY dla wiersza w którym ID_PRACOWNIKA=7369

UPDATE firma SET dzial='kadry' WHERE id_pracownika=7369;

6. Zmień nazwę kolumny MIASTO na LOKALIZACJA

ALTER TABLE firma RENAME COLUMN miasto TO lokalizacja;

7. Zmień typ danych w kolumnie ZAROBEK z INTEGER na FLOAT

ALTER TABLE firma MODIFY zarobek FLOAT;

8. Usuń kolumny LOKALIZACJA i DZIAL

ALTER TABLE firma DROP COLUMN lokalizacja;
ALTER TABLE firma DROP COLUMN dzial;

9. Do tabeli FIRMA wstaw kolumnę NR_DZIALU INTEGER

ALTER TABLE firma ADD nr_dzialu INT;

10. Wstaw wartość 20 w kolumnie NR_DZIALU, dla wiersza w którym ID_PRACOWNIKA=7369

UPDATE firma SET nr_dzialu=20 WHERE id_pracownika=7369;

11. Utwórz tabelę DZIAL zawierającą kolumny: NR_DZIALU - klucz główny INTEGER, oraz NAZWA_DZIALU
CHAR(50), LOKALIZACJA CHAR(50)

CREATE TABLE dzial(
    nr_dzialu INT PRIMARY KEY,
    nazwa_dzialu VARCHAR(50),
    lokalizacja VARCHAR(50)
);

12. Usuń wszystkie wiersze z tabeli FIRMA

DELETE FROM firma;

13. Zmień nazwę tabeli FIRMA na PRACOWNICY

ALTER TABLE firma RENAME TO pracownicy;

14. Dodaj do tabeli Pracownicy powiązanie z tabelą DZIAL w postaci klucza obcego

ALTER TABLE pracownicy ADD FOREIGN KEY (nr_dzialu) REFERENCES dzial(nr_dzialu);

15. Wypełnij tabele DZIAL

INSERT INTO dzial VALUES(1, 'Foo', 'Sosnowiec');
INSERT INTO dzial VALUES(2, 'Bar', 'Chorzow');
INSERT INTO dzial VALUES(3, 'Ching', 'Katowice');
INSERT INTO dzial VALUES(4, NULL, 'Katowice');

16. Wyświetl zawartość tabeli DZIAL

SELECT * FROM dzial;

17. Wypełnij tabele PRACOWNICY

INSERT INTO pracownicy VALUES(1, 'Adam', 'Kowalski', 'Sysadm', 5, 1);
INSERT INTO pracownicy VALUES(2, 'Pawel', 'Pan', 'Dev', 5000, 2);
INSERT INTO pracownicy VALUES(3, 'Krzysztof', 'Muller', 'Dev', 6400, 2);
INSERT INTO pracownicy VALUES(4, 'Ricardo', 'Milos', 'Sysadm', 8400, 1);

18. Wyświetl zawartość tabeli PRACOWNICY

SELECT * FROM pracownicy;

19. Zakładając, że urzędnikom odciąga się 25% pensji na podatek dochodowy, oblicz wartość tego podatku
i wyświetl wraz z kolumną ZAROBEK.

SELECT zarobek, zarobek * 0.25 AS podatek FROM pracownicy;

20. Wylicz dochody za cały rok pracowników obejmujących stanowisko sprzedawcy. Dane te wyświetl wraz
z ID_Pracownika, Imie, Nazwisko, Stanowisko, Zarobek.

SELECT id_pracownika, imie, nazwisko, stanowisko, zarobek, zarobek * 12 AS roczny_zarobek FROM
pracownicy WHERE stanowisko = 'Dev';

21. Wyświetl średnią pensję sprzedawców

SELECT avg(zarobek) FROM pracownicy WHERE stanowisko='Sysadm';

22. Do tabeli PRACOWNICY dodaj kolumnę PROWIZJA, która będzie przechowywać wartości tego samego
typu co ZAROBEK.

ALTER TABLE pracownicy ADD COLUMN prowizja FLOAT;

23. Dla pracowników obejmujących stanowiska sprzedawców, wypełnij kolumnę Prowizja wartością 300.

updatee pracownicy SET prowizja=300 WHERE stanowisko='sprzedawca';

24. Wyświetl roczny dochód sprzedawców (dochód obejmuje także prowizję) . Kolumna zawierająca
wartości dochodu musi mieć nazwę. Wyświetl także kolumny: Imie, Nazwisko, Stanowisko, Zarobek.

SELECT imie, nazwisko, stanowisko, zarobek * 12 - podatek AS roczny_dochod;
FROM nazwa_tabeli;

25. Wyświetl pensję sprzedawców (z uwzględnieniem prowizji) jako kolumnę o nazwie
DOCHOD_MIESIACA oraz Imie, Nazwisko, Stanowisko, Pensja.

SELECT imie, nazwisko, stanowisko, pensja - prowizja AS dochod_miesiaca FROM nazwa_tabeli WHERE stanowisko='sprzedawca';

26. Wyświetl pracowników, których wartość prowizji jest większa niż 20% pensji.

SELECT * FROM  nazwa_tabeli WHERE prowizja > pensja*0.20;

27. Wyświetl dane dotyczące sprzedawców: ID_Pracownika, Imie, Zarobek, Prowizja, różnica między
pensją a wartością prowizji. Uporządkuj wyniki według tej różnicy.

SELECT id_pracownika, imie, zarobek, prowizja, zarobek - prowizja AS roznica FROM nazwa_tabeli ORDER BY roznica;

28. Uporządkuj dane z poprzedniego zapytania według Nazwiska, nie używając we właściwym poleceniu
nazwy kolumny.

...

29. Pogrupuj pracowników według numeru działu. Wyświetl te dane wraz z liczebnością grup.

SELECT nr_dzialu, COUNT(*) AS liczba_pracownikow FROM pracownicy GROUP BY nr_dzialu;

30. Wyświetl nazwę stanowiska oraz liczbę osób na danym stanowisku

SELECT stanowisko AS nazwa_stanowiska, COUNT(*) AS liczba_na_stanowisku FROM pracownicy GROUP BY stanowisko;

31. Ile wynosi średnia pensja w każdym dziale. Wyniki uporządkuj według numer działu.



32. Wyświetl stanowiska występujące w dziale numer 30 oraz liczę pracowników obejmujących te
stanowiska. (Pogrupuj pracowników, którzy pracują w dziale numer 30, według numeru działu i
stanowiska. Wyświetl te dane wraz z liczebnością grup.)

SELECT stanowisko, COUNT(*) AS liczba_pracownikow FROM pracownicy GROUP BY stanowisko, nr_dzialu HAVING nr_dzialu=2;

33. Wyświetl wszystkie stanowiska z wyjątkiem kierownika. Określ ile osób pracuje na danym stanowisku i
jak jest średnia pensja na danym stanowisku



34. Wyświetl ile osób zajmuje poszczególne stanowiska, z wyjątkiem stanowiska prezesa. W poleceniu
SELECT zastosuje kolejność kolumn: ‘średnia pensja=’, średnia pensja, liczba pracowników na
stanowisku, ‘pracownik(ów) na stanowisku’, stanowisko

SELECT stanowisko, COUNT(*) AS liczba_pracownikow, avg(zarobek) AS srednia_pensja FROM pracownicy GROUP b
y stanowisko HAVING stanowisko <> 'Sysadm';

35. Pokaż na dwa sposoby instrukcję wyświetlającą pracowników pracujących w dziale 10 lub w dziale 30

SELECT * FROM pracownicy WHERE nr_dzialu = 1 OR nr_dzialu = 3;
SELECT * FROM pracownicy WHERE nr_dzialu BETWEEN 3 AND 3 OR nr_dzialu BETWEEN 1 AND 1;

36. Wyświetl pracowników, których pensje są równe prowizji

SELECT * FROM pracownicy WHERE zarobek = prowizja;

37. Pokaż na dwa sposoby instrukcję wyświetlającą pracowników, których imiona TO Marcin lub Wojtek lub
Adam. Wiersze uporządkuj według imienia, nie wykorzystując nazwy tej kolumny

SELECT * FROM pracownicy WHERE imie = 'Marcin' OR imie = 'Wojtek' OR imie = 'Adam';
SELECT * FROM pracownicy WHERE imie IN ('Marcin', 'Wojtek', 'Adam');

38. Pokaż na dwa sposoby instrukcję wyświetlającą pracowników, których pensja jest w przedziale od 2000
do 3000

SELECT * FROM pracownicy WHERE zarobek BETWEEN 3000 AND 5000;
SELECT * FROM pracownicy WHERE zarobek >= 3000 AND zarobek <= 5000;

39. Pokaż na dwa sposoby instrukcję wyświetlającą pracowników, którzy pracują w dziale 30 lub 10 i
których nazwisko kończy się na ”er”

SELECT * FROM pracownicy WHERE nr_dzialu BETWEEN 1 AND 3 AND nazwisko LIKE "%er";
SELECT * FROM pracownicy WHERE nr_dzialu = 1 OR nr_dzialu = 3 AND nazwisko LIKE "%er";

40. Wyświetl pracowników, których ID zaczyna się od 77

SELECT * FROM pracownicy WHERE id_pracownika LIKE '77%';

41. Wypisz nazwy działów, w których pracują urzędnicy

SELECT nazwa_dzialu FROM dzial WHERE stanowisko='urzednik';

42. Wypisz listę pracowników dołączając informacje o ich działach. Wyniki posortuj wg numeru działu.

SELECT imie, nazwisko, pracownicy.nr_dzialu, dzial.nazwa_dzialu FROM pracownicy JOIN dzial ON
   -> pracownicy.nr_dzialu = dzial.nr_dzialu
   -> ORDER BY pracownicy.nr_dzialu;

43. Wypisz nazwy wszystkich działów oraz nazwiska ich kierowników

SELECT nazwisko, dzial.nazwa_dzialu FROM pracownicy JOIN dzial
   -> ON pracownicy.nr_dzialu = dzial.nr_dzialu
   -> GROUP BY pracownicy.nr_dzialu, stanowisko
   -> HAVING stanowisko = 'Sysadm';

44. Którzy pracownicy zarabiają mniej niż wynosi średnia pensja?

SELECT imie, nazwisko FROM pracownicy WHERE zarobek > (SELECT avg(zarobek) FROM pracownicy);

45. Wskaż pracowników, którzy pracują w Warszawie

SELECT imie, nazwisko FROM pracownicy WHERE nr_dzialu = (SELECT nr_dzialu FROM dzial WHERE lokalizacja = 'Warszawa');

46. Podaj nazwiska pracowników zarabiających więcej niż wynosi średnia pensja

SELECT nazwisko FROM pracownicy WHERE zarobek > (SELECT avg(zarobek) FROM pracownicy);

47. Podaj SUMę pensji wszystkich pracowników pracujących w Katowicach

SELECT SUM(zarobek) FROM pracownicy WHERE nr_dzialu = any (SELECT nr_dzialu FROM dzial WHERE lokalizacja = 'Katowice');