--2
SELECT id_os "Identyfikator", INITCAP(imie1)||' '|| INITCAP(nazwisko) "Imię i nazwisko", SUBSTR(imie1, 1, 1)||SUBSTR(nazwisko, 1, 1) || TO_CHAR(d_ur, 'YYYY') "Kod pracowniczy"
FROM osoby
WHERE (nazwisko LIKE '%A%' OR nazwisko LIKE '%M%') AND to_char(d_ur, 'Q')=2
ORDER BY 1 ASC;
--3
SELECT s.nazwa "Nazwa stanowiska", COUNT(id_os) "Liczba aktualnych pracowników", MIN(pensja) "Minimalna aktualna pensja", MAX(pensja) "Maksymalna aktualna pensja"
FROM zatrudnienia JOIN stanowiska s USING(id_s) JOIN wydzialy w USING(id_w)
WHERE w.nazwa IN ('PRAWO', 'EKONOMIA', 'FILOLOGIA') AND do IS NULL
GROUP BY w.nazwa, s.nazwa
ORDER BY w.nazwa ASC;
--4
SELECT w.nazwa
FROM zatrudnienia JOIN wydzialy w USING (id_w) JOIN osoby USING(id_os)
WHERE do IS NULL AND plec='K'
GROUP BY w.nazwa
HAVING avg(pensja) BETWEEN 1200 AND 2000
ORDER BY 1 ASC;
--5
SELECT nazwisko, to_char(d_ur, 'dd month yyyy') "DATA URODZENIA"
FROM osoby
WHERE d_ur=(SELECT MAX(d_ur) FROM osoby WHERE imie1='EWA') AND imie1='EWA';
--6
SELECT nazwisko, UPPER(s.nazwa) "Nazwa stanowiska"
FROM zatrudnienia z JOIN osoby USING(id_os) JOIN stanowiska s USING (id_s)
WHERE do IS NULL AND pensja<(SELECT avg(pensja) FROM zatrudnienia
WHERE do IS NULL AND id_w=z.id_w)
ORDER BY nazwisko;
--7
SELECT to_char(d_ur, 'YYYY') "ROK" FROM osoby
WHERE plec='M'
GROUP BY to_char(d_ur, 'YYYY')
HAVING COUNT(id_os)=(SELECT MAX(COUNT(id_os)) FROM osoby WHERE plec='M' GROUP BY to_char(d_ur, 'YYYY'));
{"html5":"htmlmixed","css":"css","javascript":"javascript","php":"php","python":"python","ruby":"ruby","lua":"text\/x-lua","bash":"text\/x-sh","go":"go","c":"text\/x-csrc","cpp":"text\/x-c++src","diff":"diff","latex":"stex","sql":"sql","xml":"xml","apl":"apl","asterisk":"asterisk","c_loadrunner":"text\/x-csrc","c_mac":"text\/x-csrc","coffeescript":"text\/x-coffeescript","csharp":"text\/x-csharp","d":"d","ecmascript":"javascript","erlang":"erlang","groovy":"text\/x-groovy","haskell":"text\/x-haskell","haxe":"text\/x-haxe","html4strict":"htmlmixed","java":"text\/x-java","java5":"text\/x-java","jquery":"javascript","mirc":"mirc","mysql":"sql","ocaml":"text\/x-ocaml","pascal":"text\/x-pascal","perl":"perl","perl6":"perl","plsql":"sql","properties":"text\/x-properties","q":"text\/x-q","scala":"scala","scheme":"text\/x-scheme","tcl":"text\/x-tcl","vb":"text\/x-vb","verilog":"text\/x-verilog","yaml":"text\/x-yaml","z80":"text\/x-z80"}