SQL NFZ

1. Rysunek

Lekarz
-------
kod_lekarza VARCHAR(5)
nazwisko VARCHAR(20)
specjalizacja VARCHAR(20)
-------
Lekarz_PK(kod_lekarza)

        |
        | Wiz_lek_FK
        |

Wizyta
-------
nr_wizyty NUMERIC(4)
koszt DECIMAL(10,2)
kod_lekarza VARCHAR(5)
kod_pacjenta NUMERIC(4)
-------
Wizyta_PK(nr_wizyty)
Wiz_lek_FK(kod_lekarza)
Wiz_pac_FK(kod_pacjenta)

        |
        | Wiz_pac_FK
        |

Pacjent
------
kod_pacjenta NUMERIC(4)
nazwisko VARCHAR(30)
typ_pacjenta VARCHAR(15)
------
Pacjent_PK(kod_pacjenta)

2.

INSERT INTO Lekarz VALUES('A1000','Chudy','Patologia');
-||-

INSERT INTO Pacjent VALUES(1010,'Wozniak','NFZ');
-||-

INSERT INTO Wizyta VALUES(2000,0.0,'A1000',1010);
-||-

3 i 4.

CREATE SEQUENCE SEQ minvalue 1 maxvalue 100000 INCREMENT BY 1;

CREATE TABLE Typy AS
(
    SELECT SEQ.NEXTVAL "nr_typu" VARCHAR(20), specjalizacja FROM Lekarz
)

ALTER TABLE Typy ADD CONSTRAINT Typy_PK PRIMARY KEY (nr_typu);
ALTER TABLE Lekarz ADD CONSTRAINT Lek_typ_FK FOREIGN KEY (specjalizacja) REFERENCES Typy(nr_typu);

UPDATE Lekarz l SET l.specjalizacja = Typy.nr_typu FROM Typy t JOIN Lekarz ON l.specjalizacja = t.specjalizacja;

5.
CREATE OR REPLACE TRIGGER T_Wizyta
BEFORE INSERT ON Wizyta
FOR each ROW
DECLARE
    v_typ VARCHAR(15);
BEGIN
    SELECT rodzaj INTO v_typ FROM Pacjent p JOIN Wizyta w ON p.kod_pacjenta = w.kod_pacjenta WHERE nr_wizyty = :NEW.nr_wizyty;
    IF v_typ IN ('NFZ') THEN
        :NEW.koszt = 0.0;
    END IF
END;

6.
CREATE OR REPLACE FUNCTION F_Zysk(v_spec IN VARCHAR(20))
RETURN DECIMAL(10,2) AS
    v_id_spec NUMERIC(4);
    v_suma DECIMAL(10,2);
BEGIN
    SELECT nr_typu INTO v_id_spec FROM Typy WHERE specjalizacja = v_spec;
    SELECT SUM(koszt) INTO v_suma FROM Wizyta w JOIN Lekarz l ON w.kod_lekarza = l.kod_lekarza WHERE specjalizacja = v_id_spec;
    RETURN v_suma;
END;

7.
CREATE OR REPLACE VIEW v_perspektywa_1 AS
SELECT Typy.specjalizacja, COUNT(Wizyta.nr_wizyty), SUM(Wizyta.koszt) FROM
    Typy t JOIN Lekarz l ON t.nr_typu = l.specjalizacja JOIN Wizyta w ON l.kod_lekarza = w.kod_lekarza JOIN Pacjent p ON w.kod_pacjenta = p.kod_pacjenta WHERE p.typ_pacjenta IN ('Prywatny') GROUP BY t.specjalizacja
;

//ewentualnie NOT IN ('NFZ')

8.
CREATE OR REPLACE VIEW v_perspektywa_2 AS
SELECT Lekarz.nazwisko, Typy.specjalizacja, COUNT(Wizyta.nr_wizyty) "liczba_wizyt" FROM
    FROM Typy t JOIN Lekarz l ON t.nr_typu = l.specjalizacja JOIN Wizyta w ON l.kod_lekarza = w.kod_lekarza GROUP BY l.nazwsko, t.specjalizacja
    HAVING liczba_wizyt > (SELECT avg(SELECT COUNT(*) FROM wizyty GROUP BY kod_lekarza) FROM dual)
;