← Zurück zum Dashboard Exklusiv in AP2

AP2 Modul 07: Enterprise SQL & Datenbank-Administration (LF 10/11)

ZPA-SQL-Syntax: DDL (CREATE/ALTER TABLE), DCL (GRANT/REVOKE), Multi-Table JOINs, DATEDIFF & ACID.

🗄️ Enterprise SQL & Datenbank-Administration (LF 10 & 11)

ZPA-Prüfungskatalog 2. Auflage Standard: Alle SQL-Befehle (DDL, DML, DCL, Joins, Unterabfragen und spezifische ZPA-Funktionen wie DATEDIFF) wurden aus AP1 entfernt und sind nun exklusiv in der AP2 verortet!

1. DDL: Tabellenstruktur & Constraints

CREATE TABLE mitarbeiter (
    mit_nr INTEGER PRIMARY KEY,
    nachname VARCHAR(50) NOT NULL,
    vorname VARCHAR(50) NOT NULL,
    gehalt DECIMAL(10,2) CHECK (gehalt > 0),
    eintrittsdatum DATE NOT NULL,
    abt_nr INTEGER,
    CONSTRAINT fk_abt FOREIGN KEY (abt_nr) 
        REFERENCES abteilung(abt_nr) ON DELETE RESTRICT
);

-- Tabellenstruktur nachträglich ändern
ALTER TABLE mitarbeiter ADD COLUMN email VARCHAR(100);
ALTER TABLE mitarbeiter MODIFY COLUMN nachname VARCHAR(80);
ALTER TABLE mitarbeiter DROP COLUMN email;

2. DCL: Benutzer- & Rechteverwaltung

-- Benutzer anlegen & Rechte vergeben
CREATE USER 'reporting'@'localhost' IDENTIFIED BY 'Start2025!Secure';
GRANT SELECT, INSERT ON unternehmen.* TO 'reporting'@'localhost' WITH GRANT OPTION;
REVOKE INSERT ON unternehmen.* FROM 'reporting'@'localhost';

3. Multi-Table JOINs, Aggregation & ZPA-Funktionen

- JOINs: INNER JOIN (nur übereinstimmende Paare), LEFT JOIN (alle Zeilen der linken Tabelle, auch ohne Treffer rechts).
- Datumsdifferenz: DATEDIFF(end_datum, start_datum) berechnet Tage zwischen Daten.
- GROUP BY & HAVING: SELECT abt_nr, AVG(gehalt) FROM mitarbeiter GROUP BY abt_nr HAVING COUNT(*) >= 5;
- ACID: Atomicity (Alles oder Nichts), Consistency (Konsistenz), Isolation (Transaktionsisolierung), Durability (Dauerhaftigkeit).

📝 Authentische IHK-Prüfungsaufgaben nach ZPA 2. Auflage

Bearbeiten Sie die Aufgaben und vergleichen Sie Ihre Antwort mit dem didaktischen Erwartungshorizont:

SQL DDL CREATE TABLE mit Constraints 10 Punkte

Für eine Ticket-Verwaltung soll die Tabelle "ticket" per SQL angelegt werden: id (Ganzzahl, Primärschlüssel mit Autoinkrement), titel (Text, max. 100 Zeichen, Pflichtfeld), status (Text, Standardwert "OFFEN"), prioritaet (Ganzzahl, Werte nur von 1 bis 5 erlaubt), ersteller_id (Ganzzahl, Fremdschlüssel auf benutzer.id mit Kaskadierung beim Löschen).

Schreiben Sie das syntaktisch korrekte SQL-Statement nach ANSI/ISO-Standard.

💡 Didaktischer Erwartungshorizont & Punkteverteilung (nach KMK-Kriterien)

SQL DML Aggregation und GROUP BY mit HAVING 10 Punkte

Gegeben sind die Tabellen kunden (id, name, stadt) und bestellungen (id, kunde_id, betrag, datum).

Formulieren Sie eine SQL-Abfrage, die den Kundennamen und die Gesamtsumme aller Bestellungen für jeden Kunden ausgibt, dessen Gesamtbestellwert 10.000 EUR überschreitet. Das Ergebnis soll absteigend nach der Summe sortiert sein.

💡 Didaktischer Erwartungshorizont & Punkteverteilung (nach KMK-Kriterien)

SQL JOIN-Typen INNER vs LEFT vs FULL 6 Punkte

In einer Datenbank existieren mitarbeiter (id, name, abt_id) und abteilungen (id, bezeichnung). Einige Mitarbeiter haben keine Abteilung, und einige Abteilungen haben keine Mitarbeiter.

Erläutern Sie den Unterschied im Abfrageergebnis zwischen INNER JOIN und LEFT OUTER JOIN bezüglich der Ergebniszeilen.

💡 Didaktischer Erwartungshorizont & Punkteverteilung (nach KMK-Kriterien)

SQL Transaktionen ACID-Eigenschaften 8 Punkte

Bei einer Banküberweisung von 500 EUR von Konto A auf Konto B wird ein Transaktionsblock ausgeführt.

Nennen und erläutern Sie die vier ACID-Eigenschaften und beschreiben Sie, was passiert, wenn nach dem Abbuchen von Konto A das Datenbanksystem abstürzt.

💡 Didaktischer Erwartungshorizont & Punkteverteilung (nach KMK-Kriterien)

SQL Subqueries mit IN und EXISTS 8 Punkte

Gegeben sind die Tabellen kunden (id, name, land) und bestellungen (id, kunde_id, summe).

Formulieren Sie zwei SQL-Abfragen, die alle Kundennamen ausgeben, die mindestens eine Bestellung getätigt haben: Einmal unter Verwendung einer Subquery mit IN und einmal mit EXISTS.

💡 Didaktischer Erwartungshorizont & Punkteverteilung (nach KMK-Kriterien)

SQL Window Functions ROW_NUMBER & RANK 8 Punkte

In einer Vertriebsdatenbank sollen die Top-3-Umsätze jedes Verkäufers ermittelt werden.

Erläutern Sie die Funktionsweise von SQL Window Functions gegenüber GROUP BY und erklären Sie die Syntax von ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...).

💡 Didaktischer Erwartungshorizont & Punkteverteilung (nach KMK-Kriterien)

SQL Datums- und Zeitfunktionen (DATEDIFF & DATEADD) 8 Punkte

In einem Support-System (Tabelle tickets mit id, erstelldatum, schliessdatum) soll die Bearbeitungsdauer analysiert werden.

Formulieren Sie eine SQL-Abfrage nach ZPA-Standard, die für alle im laufenden Jahr geschlossenen Tickets die Ticket-ID und die Bearbeitungsdauer in Tagen berechnet (unter Verwendung von DATEDIFF) und nur Tickets anzeigt, deren Bearbeitung länger als 14 Tage gedauert hat.

💡 Didaktischer Erwartungshorizont & Punkteverteilung (nach KMK-Kriterien)

Statistische SQL-Funktionen (STDDEV, VARIANCE, CORR) 6 Punkte

Im Rahmen einer Performance-Analyse von Server-Antwortzeiten (Tabelle server_metrics mit server_id, response_time_ms, cpu_load) werden statistische Kennzahlen benötigt.

Erläutern Sie die Aufgaben und mathematische Bedeutung der SQL-Aggregatfunktionen STDDEV(), VARIANCE() und CORR() und beschreiben Sie einen Anwendungsfall zur Erkennung von Lastanomalien.

💡 Didaktischer Erwartungshorizont & Punkteverteilung (nach KMK-Kriterien)

← Vorheriges Modul 🏠 Kursübersicht Nächstes Modul →