# DDL: Definition des Datenbankschemas
CREATE TABLE klasse (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name TEXT
);

CREATE TABLE schueler (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vorname TEXT,
    nachname TEXT,
    geburtsdatum DATE,
    klasse_id INT,
    FOREIGN KEY (klasse_id) REFERENCES klasse(id)
);

CREATE TABLE fach (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name TEXT
);

CREATE TABLE schueler_fach (
    schueler_id INT,
    fach_id INT,
    PRIMARY KEY(schueler_id, fach_id),
    FOREIGN KEY (schueler_id) REFERENCES schueler(id),
    FOREIGN KEY (fach_id) REFERENCES fach(id)
);

# DML: Hinzufuegen von Datensaetzen
INSERT INTO klasse SET name = 'T2A';
INSERT INTO klasse SET name = 'T4A';
INSERT INTO klasse SET name = 'I2A';
INSERT INTO klasse SET name = 'I4A';

INSERT INTO fach SET name = 'DB';
INSERT INTO fach SET name = 'KN';
INSERT INTO fach SET name = 'SWT';

INSERT INTO schueler SET vorname = 'Anna', nachname = 'Arm', geburtsdatum = '1999-02-13', klasse_id = 3;
INSERT INTO schueler SET vorname = 'Berta', nachname = 'Bein', geburtsdatum = '1998-07-16', klasse_id = 4;
INSERT INTO schueler SET vorname = 'Carla', nachname = 'Copf', geburtsdatum = '1996-11-09', klasse_id = 4;
INSERT INTO schueler SET vorname = 'Dieter', nachname = 'Darm', geburtsdatum = '1995-06-23', klasse_id = 4;
INSERT INTO schueler SET vorname = 'Emma', nachname = 'Elle', geburtsdatum = '1999-01-28', klasse_id = 2;

# DML: Schueler waehlen Fach
INSERT INTO schueler_fach SET schueler_id = 2, fach_id = 1;
INSERT INTO schueler_fach SET schueler_id = 3, fach_id = 1;
INSERT INTO schueler_fach SET schueler_id = 4, fach_id = 1;

INSERT INTO schueler_fach SET schueler_id = 2, fach_id = 3;
INSERT INTO schueler_fach SET schueler_id = 4, fach_id = 3;

# DML/DQL: Lesen
# Die Namen aller Schueler der Klasse I4A
SELECT schueler.vorname, schueler.nachname
FROM schueler
JOIN klasse ON schueler.klasse_id = klasse.id
WHERE klasse.name = 'I4A';

# Die Namen aller Schueler im Fach SWT
SELECT schueler.vorname, schueler.nachname
FROM schueler
JOIN schueler_fach ON schueler.id = schueler_fach.schueler_id
JOIN fach ON fach.id = schueler_fach.fach_id
WHERE fach.name = 'SWT';

# Die Namen aller Faecher des Schuelers Berta 
SELECT fach.name
FROM fach
JOIN schueler_fach ON fach.id = schueler_fach.fach_id
JOIN schueler ON schueler.id = schueler_fach.schueler_id
WHERE schueler.vorname = 'Berta';