SELECT *
FROM wyk_prac;


SELECT *
FROM wyk_dzial;


ALTER TABLE wyk_prac ADD CONSTRAINT fk_klucz
FOREIGN KEY (d_id) REFERENCES wyk_dzial(d_id);


SELECT SUM(pensja),
       nazwa
FROM wyk_prac p1
JOIN wyk_dzial d1 ON d1.d_id = p1.d_id
GROUP BY nazwa
ORDER BY SUM(pensja);


SELECT p1.imie,
       p1.nazwisko,
       AVG(p2.pensja)
FROM wyk_prac p1
JOIN wyk_dzial d1 ON d1.k_id = p1.p_id
JOIN wyk_prac p2 ON p2.d_id = d1.d_id
GROUP BY p1.imie,
         p1.nazwisko;


SELECT *
FROM wyk_prac
WHERE d_id IN
    (SELECT d_id
     FROM wyk_dzial
     WHERE k_id IN
         (SELECT p_id
          FROM wyk_prac
          WHERE nazwisko = 'Ayala'
            AND imie = 'Xander'));

SELECT SUM(pensja)
FROM wyk_prac p1
JOIN wyk_dzial d1 ON p1.d_id = d1.d_id
HAVING d1.d_id IN
  (SELECT d_id
   FROM wyk_dzial
   WHERE k_id IN
       (SELECT p_id
        FROM wyk_prac
        WHERE imie= 'Xander'
          AND nazwisko = 'Ayala')
    )
GROUP BY d1.d_id;