-- 1
SELECT PACJENT.NAZWISKO, PACJENT.IMIE, COUNT(WIZYTA.ID_WIZYTA)
FROM PACJENT,
     WIZYTA
WHERE PACJENT.ID_PACJENT = WIZYTA.ID_PACJENT
GROUP BY WIZYTA.ID_WIZYTA, PACJENT.NAZWISKO, PACJENT.IMIE
HAVING COUNT(WIZYTA.ID_WIZYTA) > (SELECT COUNT(WIZYTA.ID_LEKARZ)
                                  FROM WIZYTA,
                                       LEKARZ,
                                       SPECJALNOSC
                                  WHERE WIZYTA.ID_LEKARZ = LEKARZ.ID_LEKARZ
                                    AND LEKARZ.NAZWISKO = 'Nowak'
                                    AND WIZYTA.DATA > TO_DATE('01/01/2016', 'DD/MM/YY')
                                    AND LEKARZ.ID_SPEC = SPECJALNOSC.ID_SPEC
                                    AND SPECJALNOSC.NAZWA = 'LARYNGOLOG');

-- 2
SELECT SPECJALNOSC.NAZWA, PACJENT.NAZWISKO, PACJENT.IMIE
FROM SPECJALNOSC,
     LEKARZ,
     PACJENT,
     WIZYTA
WHERE WIZYTA.ID_PACJENT = PACJENT.ID_PACJENT
  AND WIZYTA.ID_LEKARZ = LEKARZ.ID_LEKARZ
  AND LEKARZ.ID_SPEC = SPECJALNOSC.ID_SPEC
  AND WIZYTA.DATA < (SELECT MIN(WIZYTA.DATA)
                     FROM PACJENT,
                          WIZYTA,
                          LEKARZ,
                          SPECJALNOSC
                     WHERE WIZYTA.ID_PACJENT = PACJENT.ID_PACJENT
                       AND WIZYTA.ID_LEKARZ = LEKARZ.ID_LEKARZ
                       AND LEKARZ.ID_SPEC = SPECJALNOSC.ID_SPEC
                       AND SPECJALNOSC.NAZWA = 'KARDIOLOG');

-- 3
SELECT PACJENT.IMIE, PACJENT.NAZWISKO, LEKARZ.IMIE, LEKARZ.NAZWISKO, WIZYTA.DATA
FROM PACJENT,
     LEKARZ,
     WIZYTA
WHERE PACJENT.ID_PACJENT = WIZYTA.ID_PACJENT
  AND LEKARZ.ID_LEKARZ = WIZYTA.ID_LEKARZ
  AND WIZYTA.DATA < (SELECT WIZYTA.DATA
                     FROM WIZYTA,
                          PACJENT
                     WHERE WIZYTA.ID_PACJENT = PACJENT.ID_PACJENT
                       AND PACJENT.NAZWISKO = 'Kowalski'
                       AND PACJENT.IMIE = 'Jan'
                       AND WIZYTA.DATA > ADD_MONTHS(SYSDATE, -12)
                     ORDER BY WIZYTA.DATA DESC FETCH FIRST ROW ONLY)
  AND WIZYTA.DATA > (SELECT WIZYTA.DATA
                     FROM WIZYTA,
                          PACJENT
                     WHERE WIZYTA.ID_PACJENT = PACJENT.ID_PACJENT
                       AND PACJENT.NAZWISKO = 'Kowalski'
                       AND PACJENT.IMIE = 'Jan'
                       AND WIZYTA.DATA > ADD_MONTHS(SYSDATE, -12)
                     ORDER BY WIZYTA.DATA ASC FETCH FIRST ROW ONLY);

-- 4
SELECT PACJENT.NAZWISKO, PACJENT.IMIE, AVG(WIZYTA.CENA)
FROM PACJENT,
     WIZYTA
WHERE WIZYTA.ID_PACJENT = PACJENT.ID_PACJENT
GROUP BY WIZYTA.ID_PACJENT, PACJENT.NAZWISKO, PACJENT.IMIE
HAVING AVG(WIZYTA.CENA) > SUM((SELECT AVG(WIZYTA.CENA)
                               FROM WIZYTA,
                                    LEKARZ,
                                    SPECJALNOSC
                               WHERE WIZYTA.ID_LEKARZ = LEKARZ.ID_LEKARZ
                                 AND LEKARZ.ID_SPEC = SPECJALNOSC.ID_SPEC
                                 AND SPECJALNOSC.NAZWA = 'OKULISTA'
                               GROUP BY LEKARZ.ID_SPEC));

-- 5
ALTER TABLE PACJENT
    ADD PESEL NUMBER(11) DEFAULT 1 NOT NULL;

-- 6
ALTER TABLE PACJENT
    DROP COLUMN DATA_UR;

-- 7
ALTER TABLE PACJENT
    MODIFY IMIE VARCHAR(25);

-- 8
ALTER TABLE WIZYTA
    ADD CHECK (CENA > 0 AND CENA < 100);

-- 9
UPDATE WIZYTA
SET CENA = CENA * 0.95
WHERE WIZYTA.CENA > (SELECT AVG(WIZYTA.CENA) FROM WIZYTA WHERE WIZYTA.DATA > ADD_MONTHS(SYSDATE, -12));

-- 10
UPDATE WIZYTA
SET CENA = CENA + 50
WHERE WIZYTA.ID_PACJENT IN (SELECT PACJENT.ID_PACJENT
                            FROM PACJENT,
                                 WIZYTA,
                                 LEKARZ,
                                 SPECJALNOSC
                            WHERE WIZYTA.ID_PACJENT = PACJENT.ID_PACJENT
                              AND WIZYTA.ID_LEKARZ = LEKARZ.ID_LEKARZ
                              AND LEKARZ.ID_SPEC = SPECJALNOSC.ID_SPEC
                              AND SPECJALNOSC.NAZWA = 'KARDIOLOG');

-- 11
UPDATE LEKARZ
SET LEKARZ.ID_SPEC = (SELECT SPECJALNOSC.ID_SPEC FROM SPECJALNOSC WHERE SPECJALNOSC.NAZWA = 'SUPER LEKARZ')
WHERE LEKARZ.ID_LEKARZ IN (SELECT LEKARZ.ID_LEKARZ, COUNT(WIZYTA.ID_LEKARZ)
                           FROM LEKARZ,
                                WIZYTA
                           WHERE WIZYTA.ID_LEKARZ = LEKARZ.ID_LEKARZ
                           GROUP BY WIZYTA.ID_LEKARZ
                           HAVING COUNT(WIZYTA.ID_LEKARZ)
                                      > (SELECT COUNT(WIZYTA.ID_LEKARZ)
                                         FROM WIZYTA
                                         WHERE WIZYTA.DATA
                                                   > ADD_MONTHS(SYSDATE
                                                   , -12)));

-- 12
-