dr_rautenberg_goldschmidt_complete.sql
-- ===========================================================
-- dr_rautenberg_goldschmidt_complete.sql
-- SQL-Script für BS28 (Goldschmidt)
-- Import → Erweiterung → Auswertung
-- ===========================================================

USE dr_rautenberg_goldschmidt;

-- 1) Neue Tabellen
CREATE TABLE IF NOT EXISTS krankenkasse (
    KKNr INT AUTO_INCREMENT PRIMARY KEY,
    Bezeichnung VARCHAR(50),
    Ansprechpartner VARCHAR(40)
);

CREATE TABLE IF NOT EXISTS facharzt (
    FaNr INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS therapie (
    TNr INT AUTO_INCREMENT PRIMARY KEY,
    Bezeichnung VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS diagnose_therapie (
    DNr INT,
    TNr INT,
    PRIMARY KEY (DNr, TNr),
    FOREIGN KEY (DNr) REFERENCES diagnose(DNr),
    FOREIGN KEY (TNr) REFERENCES therapie(TNr)
);

-- 2) Test-Daten
INSERT INTO krankenkasse (Bezeichnung, Ansprechpartner) VALUES
('AOK', 'Müller'),
('TK', 'Schmidt'),
('Barmer', 'Klein');

INSERT INTO facharzt (Name) VALUES
('Dr. Rautenberg'),
('Dr. Meyer'),
('Dr. Schulz');

INSERT INTO therapie (Bezeichnung) VALUES
('Fango-Packung'),
('Verbandskasten'),
('Medikamentengabe');

-- 3) Fremdschlüssel
ALTER TABLE patienten
ADD CONSTRAINT fk_patient_krankenkasse
FOREIGN KEY (KKNr) REFERENCES krankenkasse(KKNr);

ALTER TABLE diagnose
ADD CONSTRAINT fk_diagnose_facharzt
FOREIGN KEY (FaNr) REFERENCES facharzt(FaNr);

-- 4) Testbeziehungen
UPDATE diagnose SET FaNr = 1 WHERE FaNr IS NULL;

INSERT INTO diagnose_therapie (DNr, TNr) VALUES
(1,1),(1,2),(2,3);

-- 5) Auswertung 1 – PLZ-Bezirke
SELECT
    LEFT(Postleitzahl,2) AS PLZ_Bezirk,
    COUNT(*) AS Anzahl_Patienten
FROM patienten
WHERE LEFT(Postleitzahl,2) IN ('20','21')
GROUP BY LEFT(Postleitzahl,2)
ORDER BY Anzahl_Patienten DESC;

-- 6) Auswertung 2 – Telefonliste ab 2015
SELECT
    p.Vorname,
    p.Nachname,
    p.Telefon,
    p.MobilNr,
    k.Bezeichnung AS Krankheit,
    d.Feststellungsdatum
FROM patienten p
JOIN diagnose d ON p.PNr = d.PNr
JOIN krankheiten k ON d.KNr = k.KNr
WHERE k.Bezeichnung = 'Masern'
  AND d.Feststellungsdatum >= '2015-01-01';