# Datenbanken und SQL – Modul 164 (MySQL) **Executive Summary:** Dieses Dokument fasst alle prüfungsrelevanten SQL-/MySQL-Kenntnisse zusammen – von der Installation (z.B. XAMPP/MariaDB) über Datenbank- und Tabellenbefehle bis hin zu komplexen Abfragen, Datentypen, Funktionen und referenzieller Integrität. Es enthält viele Beispiele, Code-Snippets, Tabellen zum Vergleich von Datentypen und Operatoren sowie mindestens 3 Übungsaufgaben mit Lösungen. ER-Modelle (Chen-Notation und Crow’s Foot) und Normalisierung (1NF–3NF) werden ebenso behandelt. Am Ende gibt es Hinweise zur Erstellung einer kompakten A5-Spickseite für die Prüfung. ## 1. Systemumgebung und Einstieg - **XAMPP/MariaDB starten:** In XAMPP Control Panel Apache und MySQL starten. In der Konsole `mysql -u root` eingeben (keine Passwortabfrage im Standard-Setup). - **Datenbank auswählen:** `USE datenbankname;` wählt die Datenbank. Mit `SHOW DATABASES;` alle DBs anzeigen. - **Neue DB erstellen/löschen:** ```sql CREATE DATABASE schuldb; -- Neue Datenbank anlegen DROP DATABASE schuldb; -- Löscht Datenbank (alle Tabellen gehen verloren) USE schuldb; -- Wechselt in diese DB ``` `CREATE DATABASE` erstellt eine Datenbank (InnoDB-Standardengine). ## 2. Tabellen erstellen und ändern - **CREATE TABLE:** Legt eine Tabelle fest. Beispiel: ```sql CREATE TABLE students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), age INT, grade VARCHAR(2) ); ``` `CREATE TABLE` erzeugt eine Tabelle mit Spalten und Datentypen. AUTO_INCREMENT erzeugt einen Zähler, PRIMARY KEY setzt den Primärschlüssel. - **Tabelle umbenennen:** ```sql ALTER TABLE students RENAME TO alumni; ``` - **Spalte hinzufügen (ADD):** ```sql ALTER TABLE students ADD COLUMN email VARCHAR(100) AFTER name; ``` (Mit `FIRST` vorne oder `AFTER spalte` Position setzen.) - **Spalte ändern (CHANGE):** ```sql ALTER TABLE students CHANGE COLUMN grade classement CHAR(2) NOT NULL; ``` Ändert Name/Typ der Spalte. - **Spalte löschen (DROP COLUMN):** ```sql ALTER TABLE students DROP COLUMN email; ``` - **Tabelle löschen (DROP TABLE):** ```sql DROP TABLE students; -- Löscht Tabelle komplett ``` - **SHOW-Kommandos:** - `SHOW TABLES;` listet alle (Nicht-TEMPORARY) Tabellen in der DB auf. - `DESCRIBE studenten;` oder `SHOW COLUMNS FROM studenten;` zeigt Spalten und Typen. - `SHOW CREATE TABLE students;` zeigt die komplette CREATE-Tabelle-Definition. ## 3. Datentypen in MySQL Wichtige Datentypen und Speichergrößen: | **Typ** | **Beschreibung / Bereich** | **Speicherbedarf** | |---------------|---------------------------------------------------------|------------------------| | **TINYINT** | Ganzzahl (-128…127 oder 0…255 unsigned) | 1 Byte | | **SMALLINT** | Ganzzahl (-32768…32767) | 2 Bytes | | **MEDIUMINT** | Ganzzahl | 3 Bytes (2^24 Werte) | | **INT** | Ganzzahl (-2,147M…+2,147M) | 4 Bytes | | **BIGINT** | Ganzzahl | 8 Bytes | | **FLOAT** | Gleitkommazahl (ca. 7 Dezimalstellen) | 4 Bytes | | **DOUBLE** | Gleitkommazahl (ca. 15 Dezimalstellen) | 8 Bytes | | **DECIMAL(p,s)** | Exakte Zahl mit Dezimalstellen (Größe variiert nach Präzision) | ca. `M/9+1` Bytes (siehe MySQL-Doku) | | **CHAR(n)** | Zeichenkette fester Länge (bis 255) | n (Feste Länge) | | **VARCHAR(n)**| Zeichenkette variabler Länge (bis 65,535) | L+1 oder L+2 Bytes (siehe Doku) | | **TEXT** | Länge bis 65 535 Zeichen | L+2 Bytes (L=Textlänge) | | **DATE** | Datum (Jahr-Monat-Tag) | 3 Bytes | | **TIME** | Uhrzeit (Stunde:Minute:Sekunde) | 3 Bytes (ohne Bruchteile) | | **DATETIME** | Datum und Uhrzeit | 5 Bytes (ohne Bruchteile) | | **TIMESTAMP** | Zeitstempel (UTC, 1970-2038) | 4 Bytes (ohne Bruchteile) | *(Weitere Typen: `BLOB` ähnlich `TEXT`, `YEAR` 1 Byte, `ENUM`/`SET` durch Index üblicherweise 1–2 Bytes.)* ## 4. Einfügen von Daten (INSERT & Import) - **INSERT (Kurzform):** Fügt Werte in alle Spalten in Tabellenreihenfolge ein. ```sql INSERT INTO students VALUES (NULL, 'Alice', 20, 'A'); ``` Hier steht `NULL` für AUTO_INCREMENT `id`. - **INSERT (Langform mit Spalten):** ```sql INSERT INTO students (name, age, grade) VALUES ('Bob', 21, 'B'); ``` - **Mehrere Reihen auf einmal:** ```sql INSERT INTO students (name, age, grade) VALUES ('Charlie',19,'A'), ('Diana',22,'B'), ('Eric',20,'C'); ``` Beispiel in MySQL-Handbuch: > `INSERT ... VALUES(1,2,3),(4,5,6);` fügt zwei Zeilen ein. - **LOAD DATA INFILE (CSV-Import):** Sehr schnelle Massen-Import-Funktion. Beispiel: 1. Excel als CSV speichern (UTF-8, Spaltennamen in erster Zeile). 2. In MySQL: ```sql LOAD DATA LOCAL INFILE 'students.csv' INTO TABLE students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; ``` *Anmerkung:* `LOCAL` liest Datei auf Client-Seite (kein FILE-Privileg nötig). Ohne `LOCAL` muss Datei auf MySQL-Server liegen. - Validierung nach Import z.B.: `SELECT COUNT(*) FROM students;`. - Tipp: Indexe vor dem Import deaktivieren (`ALTER TABLE ... DISABLE KEYS`), danach reaktivieren, um Geschwindigkeit zu erhöhen. - **phpMyAdmin-Import:** Unter „Import“ CSV-Datei auswählen, Zeichensatz UTF-8, Trennzeichen angeben, Spaltenüberschrift-Option setzen. Dann auf „OK“ klicken und mit `SELECT * FROM ...;` überprüfen. ## 5. Datenauswahl mit SELECT - **Grundlage:** ```sql SELECT spalte1, spalte2 FROM tabelle WHERE Bedingung; ``` `SELECT` holt Zeilen aus einer oder mehreren Tabellen. Ohne WHERE werden alle Zeilen ausgewählt. - **WHERE-Klausel:** Filtert Zeilen nach Bedingungen. Beispiel: `WHERE age > 18 AND city = 'Z\u00fcrich'` – hier müssen beide Bedingungen wahr sein. > *Zitat:* „The WHERE clause ... indicates the conditions that rows must satisfy to be selected“. - **DISTINCT:** Entfernt Duplikate im Ergebnis: ```sql SELECT DISTINCT grade FROM students; ``` *Beispiel:* `DISTINCT` entfernt doppelte Zeilen. - **AND, OR:** Logische Verknüpfungen. Beispiel: ```sql SELECT * FROM students WHERE age > 18 AND grade = 'A'; SELECT * FROM students WHERE city = 'Bern' OR city = 'Z\u00fcrich'; ``` - **IN-Liste:** Prüft auf Zugehörigkeit: ```sql SELECT * FROM students WHERE city IN ('Bern','Z\u00fcrich','Genf'); ``` (`IN()` prüft, ob ein Wert in einer Liste vorkommt.) - **BETWEEN ... AND:** Bereichstest (inklusive): ```sql SELECT * FROM students WHERE age BETWEEN 18 AND 25; ``` (`BETWEEN ... AND ...` testet, ob ein Wert in einem Bereich liegt.) - **LIKE (Mustervergleich):** Platzhalter `%` (beliebige Zeichenkette) und `_` (ein Zeichen). Beispiel: ```sql SELECT * FROM students WHERE name LIKE 'A%'; -- Namen beginnend mit A SELECT * FROM students WHERE name LIKE '%n'; -- Namen endend auf n ``` (`LIKE` führt einfachen Mustervergleich durch.) - **IS NULL / IS NOT NULL:** Prüft auf NULL-Werte (z.B. fehlende Daten). Beispiel: ```sql SELECT * FROM students WHERE email IS NULL; -- Kein Eintrag in email SELECT * FROM students WHERE city IS NOT NULL; ``` - **LIMIT:** Begrenzung der Zeilenanzahl, z.B.: ```sql SELECT * FROM students LIMIT 10; -- Erste 10 Zeilen SELECT * FROM students LIMIT 5, 10; -- 5 überspringen, dann 10 ausgeben ``` - **Beispiel SELECT mit WHERE und ORDER BY:** ```sql SELECT name, age FROM students WHERE grade='A' AND age < 21 ORDER BY name ASC; ``` ## 6. Gruppierung und Aggregatfunktionen - **Aggregatfunktionen:** Arbeiten über Zeilengruppen: ```sql SELECT MIN(age), MAX(age), AVG(age), SUM(age), COUNT(*) FROM students; ``` z.B. `MIN()` gibt Minimum, `AVG()` Durchschnitt, `COUNT(*)` Zeilenanzahl usw. zurück. - **GROUP BY:** Gruppiert nach Spalten und erlaubt Aggregation pro Gruppe. Beispiel: ```sql SELECT grade, COUNT(*) AS Anzahl FROM students GROUP BY grade; ``` - **HAVING:** Filtert Gruppenergebnisse (wie WHERE, aber nach GROUP BY). Beispiel: ```sql SELECT grade, AVG(age) AS MittelAlter FROM students GROUP BY grade HAVING AVG(age) > 20; ``` ## 7. Joins (Grundlagen) - Mit `JOIN` können Tabellen anhand von Schlüsseln verknüpft werden. Beispielsweise hat Tabelle **orders** eine Fremdschlüssel-Spalte `customer_id` referenzierend auf `customers(id)`. ```sql SELECT o.id, c.name, o.amount FROM customers c JOIN orders o ON c.id = o.customer_id; ``` Das sind **INNER JOINs**, die nur Zeilen zurückgeben, wenn beide Seiten übereinstimmen. Für **LEFT JOIN** (alle Kunden, auch ohne Bestellung) verwendet man `LEFT JOIN`. *Mermaid ERD-Beispiel (Crow’s Foot):* Veranschaulicht eine 1:n-Beziehung zwischen CUSTOMER und ORDER: ```mermaid erDiagram CUSTOMER ||--o{ ORDER : places ORDER ||--|{ LINE_ITEM : contains ``` Hier bedeutet `||--o{`: Ein **Customer** kann viele **Orders** (1:n) aufgeben. `||`=1, `o{`=n. ## 8. Aktualisieren und Löschen - **UPDATE:** Ändert vorhandene Daten. Syntax: ```sql UPDATE students SET age = 21, grade = 'B' WHERE id = 3; ``` Beispiel aus MySQL-Doku: `UPDATE tabelle SET spalte='NeuerWert' WHERE ...` aktualisiert Zeile. - **DELETE:** Löscht Zeilen. Achtung: `DELETE FROM tabelle;` ohne WHERE löscht alle Datensätze. Besser mit WHERE: ```sql DELETE FROM students WHERE id = 5; ``` - **TRUNCATE TABLE:** Entfernt *alle* Zeilen in einer Tabelle (schneller als DELETE ohne WHERE, setzt AUTO_INCREMENT zurück). - **DROP TABLE:** Löscht komplette Tabelle inklusive Struktur. ## 9. Primär- und Fremdschlüssel (Referentielle Integrität) - **PRIMARY KEY:** Einzigartige Kennung einer Tabelle (häufig `id AUTO_INCREMENT`). Ein Primärschlüsselwert darf **nicht NULL** sein und nicht doppelt. - **FOREIGN KEY:** Verknüpft Tabellen. Beispiel: ```sql CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE RESTRICT ); ``` Hier ist `customer_id` Fremdschlüssel, der auf `customers.id` verweist. - **Referentielle Aktionen:** Legen fest, was passiert, wenn ein Eltern-Datensatz gelöscht/aktualisiert wird (siehe MySQL-Doku). Wichtigste Optionen: - `CASCADE`: Löschen/Aktualisieren in der Eltern-Tabelle löscht/aktualisiert automatisch passende Zeilen in der Kind-Tabelle. - `SET NULL`: Löschen/Aktualisieren setzt den FK-Wert in den Kind-Zeilen auf NULL. (Die FK-Spalte muss NULL erlaubt haben.) - `RESTRICT` oder `NO ACTION`: Verhindert Löschung/Aktualisierung, solange abhängige Zeilen existieren. (Ohne Angabe von `ON DELETE/UPDATE` gilt implizit RESTRICT.) *Beispiel:* `ON DELETE CASCADE` in `orders` bedeutet, dass beim Löschen eines **customers** automatisch alle **orders** dieses Kunden gelöscht werden. Bei `ON UPDATE SET NULL` würden Kundenaktualisierungen den `customer_id` in **orders** auf NULL setzen. - **Index-Anforderung:** InnoDB erfordert, dass referenzierte Spalten indiziert sind (normalerweise automatisch für PK, unique). ## 10. Wichtige SHOW-Statements - `SHOW DATABASES;` – Liste der Datenbanken. - `SHOW TABLES;` – Liste der Tabellen (siehe oben). - `SHOW COLUMNS FROM table_name;` – Spalten und Typen in einer Tabelle. - `SHOW CREATE TABLE table_name;` – Original SQL zum Erstellen der Tabelle. ## 11. SQL-Operatoren (Vergleichsoperatoren) Wichtige Operatoren und ihre Bedeutung: | **Operator** | **Bedeutung** | **Quelle** | |------------------|-------------------------------------------------|--------------------------| | `=` | Gleich (a = b) | Gleichheits-Op | | `<>` bzw. `!=` | Ungleich (a ≠ b) | Not equal | | `>`, `>=`, `<`, `<=` | Größer/kleiner (oder gleich) | Vergleichs-Op | | `IN (a,b,...)` | Wert gehört zu Liste (z.B. `x IN (1,2,3)`) | Wert in Menge | | `NOT IN (...)` | Wert gehört *nicht* zu Liste | (analog zu IN) | | `BETWEEN ... AND ...` | Bereichstest, inklusiv | Range-Check | | `NOT BETWEEN ...`| Außerhalb eines Bereichs | (negativer Bereich) | | `LIKE 'muster'` | Mustervergleich (`%` und `_`) | Pattern matching| | `NOT LIKE` | Nicht-Übereinstimmung mit Muster | - | | `IS NULL` | Prüft NULL | Null-Test | | `IS NOT NULL` | Prüft *nicht* NULL | - | | `EXISTS (Subq.)` | TRUE, wenn Subquery ≥1 Zeile liefert | Subquery-Test | | `NOT EXISTS` | Kein Ergebnis aus Subquery | (neg.) | | `AND`, `OR`, `NOT` | Logische Verknüpfung / Negation | - | *(In SQL sind auch `<>` und `!=` gültig für „ungleich“. Die Liste basiert u.a. auf offiziellen MySQL-Dokumentationstabellen.)* ## 12. String-Funktionen und -Operatoren - **`CONCAT(str1, str2, ...)`** – Verknüpft Strings zu einem (falls alle nicht-binär): ```sql SELECT CONCAT('My', 'SQL') AS full; -- Ergebnis: 'MySQL' ``` [MySQL: *Returns the string that results from concatenating the arguments*.] - **`CHAR_LENGTH(str)`** – Anzahl Zeichen im String (Unicode-Codepunkte). Beispiel: ```sql SELECT CHAR_LENGTH('Hello'), LENGTH('Hello'); ``` gibt 5 bzw. 5 (bei Mehrbyte-Zeichenabweichungen möglich, CHAR_LENGTH zählt Zeichen, LENGTH Bytes). - **`TRIM(str)`** – Entfernt Leerzeichen am Anfang und Ende: ```sql SELECT TRIM(' text '); -- 'text' ``` - **`REPLACE(str, from, to)`** – Ersetzt alle Vorkommen: ```sql SELECT REPLACE('Hello World', 'l', '*'); -- 'He**o Wor*d' ``` (MySQL: *Returns the string with all occurrences of from_str replaced by to_str*.) - **`UPPER(str)` bzw. `LOWER(str)`** – Alle Zeichen groß/klein: ```sql SELECT UPPER('Hallo'), LOWER('WORLD'); -- 'HALLO', 'world' ``` (Uppercase-Konvertierung nach aktuellem Zeichensatz.) - **Weitere nützliche:** `CONCAT_WS(sep, ...)` (Concat with separator), `SUBSTRING()`, `LEFT()/RIGHT()`, `INSTR()`, `REVERSE()`, `LENGTH()`, `LTRIM()/RTRIM()` (Leerzeichen entfernen), etc. (Siehe MySQL-Doku Kapitel String-Funktionen für Details.) ## 13. Best Practices beim Arbeiten mit SQL/MySQL - **Zeichensatz:** Immer UTF-8 (bzw. `utf8mb4`) verwenden, um Zeichensalat zu vermeiden. MySQL-Default ist oft `utf8mb4`. - **Datenvalidierung:** Nach Import oder Manipulation prüfen, z.B. `SELECT COUNT(*)`, oder `CHECK`-Constraints. - **Indexe:** Für JOINs und WHERE-Bedingungen Indizes setzen. Beim großen Datenimport Indexe vorübergehend deaktivieren (`ALTER TABLE ... DISABLE KEYS`) und danach wieder aktivieren (schneller). - **SQL-Injektion vermeiden:** In Anwendungscode nie Parameter direkt einfügen, sondern parameterisierte Abfragen/Prepared Statements nutzen (in MySQL z.B. über Programmierschnittstellen). - **Kommentare:** SQL unterstützt `-- Kommentar` oder `/* Kommentar */`. ## 14. Entität-Beziehungs-Modell (ERM) und Normalisierung (Prüfungsaufgaben) - **ERM (Chen-Notation):** Visualisiert Entitäten (Rechtecke), Beziehungen (Rauten), Attribute (Ellipsen). Beispiel: (Student) – (LEGT ab) – (Prüfung). Beziehungen können Kardinalitäten haben (1:1, 1:n, n:m). - **Crow’s Foot (Peter Chen vs. Crow):** Merkmals- und Beziehungen ähnlich, aber oft Tabellendiagramm mit „Krähenfuß“-Beinchen. (Mermaid oben zeigt Crow’s Foot.) - **1. Normalform (1NF):** Alle Spaltenwerte atomar (einzelner Wert). Keine mehrfach belegten Felder oder Listen in einer Zelle. Beispiel: Tabelle mit „Pj# = 11,12“ ist *nicht* 1NF. In 1NF würde man solche Auflistungen in separate Zeilen/Tabellen aufteilen. - **2. Normalform (2NF):** Voraussetzung: 1NF. Zusätzlich darf kein Nicht-Schlüsselattribut von einem Teil eines zusammengesetzten Schlüssels abhängen (keine teilweisen Abhängigkeiten). Nicht-Schlüssel-Attribute müssen vom ganzen Schlüssel abhängen. *Beispiel:* Ein Schlüssel (P#, Abt#) bestimmt Mitarbeiterdaten und Projektdaten. Falls Mitarbeiterdaten nur von P# abhängen, müssen diese in einer eigenen Tabelle (mit P# als Schlüssel) stehen. - **3. Normalform (3NF):** Voraussetzung: 2NF. Außerdem darf kein Nicht-Schlüsselattribut transitiv abhängen (d.h. von einem anderen Nicht-Schlüsselattribut). Jedes Nicht-Schlüsselattribut muss nur direkt vom Schlüssel abhängen. *Beispiel:* Tabelle hat (Personalnummer, Abteilungsname). Abteilungsname hängt transitiv von Personalnummer über Abteilungsnummer. Daher muss Abteilungsname in separate Tabelle (Tabelle Abteilungen) ausgelagert werden. - **Zusammenfassung Normalisierung:** - **Original:** Nicht-atomare oder redundante Felder (z.B. mehrere Projekt-IDs in einer Zelle, oder Abteilungsname in Mitarbeiter-Tabelle). - **1NF:** Atome erzwingen – Mehrfachwerte aufteilen (s. Grafik weiter unten). - **2NF:** Alle Teilschlüssel-Abhängigkeiten entfernen (Partielle Abhängigkeiten auslagern). - **3NF:** Transitive Abhängigkeiten entfernen (abhängige Attribute in neue Tabelle). *Mermaid-Prozessdiagramm: Normalisierungsschritte:* ```mermaid flowchart LR A["Unnormalisierte Tabelle"] --> B["1. NF: Atomare Werte (Mehrfachspalten auflösen)"] B --> C["2. NF: Keine partiellen Abhängigkeiten (Tabellen nach Teilschlüssel splitten)"] C --> D["3. NF: Keine transitiven Abhängigkeiten (Tabellen nach Sachgebieten aufteilen)"] ``` - **Beispiel ERD:** ```mermaid erDiagram STUDENT ||--o{ KURS : besucht KURS ||--|{ DOZENT : wird_geleitet_von ``` Chen-Notation würde `STUDENT (PK StudentID, Name, ...)`, `KURS (PK KursID, Thema, ...)`, mit einer n:m-Beziehung *besucht* (hängt von Join-Tabelle mit Zusatzattributen ab). ## 15. Spickzettel-A5 erstellen (Kernaussagen) - **Fokus-Themen:** Aus den obigen Bereichen die Schlüsselbegriffe und Befehle notieren. Zum Beispiel: **Datentypen Tabelle** (Typ vs Bytes), **Primär- vs Fremdschlüssel**, **Normalformen kurz**, **Wichtigste SQL-Befehle in Stichworten** (`CREATE TABLE`, `SELECT ... WHERE`, `JOIN`, `GROUP BY` etc.). - **Kürzel:** Nutze verständliche Kürzel (z.B. `PK = Primärschlüssel`, `1NF: atomar` usw.). - **Tabellen und Code:** Kleinformatige Beispiele oder Syntaxschemata notieren. - **Legende:** Bei Abkürzungen Legende kurz dazuschreiben (oder im Kopf, falls Platz). - **Übersichtlichkeit:** Setze Farben oder Rahmen, um Abschnitte visuell zu trennen (falls erlaubt). - **Merke:** Keine Fließtexte – nur komprimierte Stichpunkte oder Keywords. ## 16. Übungsaufgaben (mit Lösungen) ### Übung 1: Tabellen & einfache Abfrage **Aufgabe:** Erstelle in MySQL eine Datenbank `schule`, lege eine Tabelle `studenten` an mit Spalten `id (PK, AUTO_INCREMENT)`, `name (VARCHAR)`, `age (INT)`, `city (VARCHAR)`. Füge drei Datensätze ein (z.B. Alice/19, Bob/20, Carola/18) und führe folgende Abfrage aus: Zeige alle Studenten über 18 aus der Stadt «Zürich» an. **Lösung:** ```sql CREATE DATABASE schule; USE schule; CREATE TABLE studenten ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT, city VARCHAR(50) ); INSERT INTO studenten (name, age, city) VALUES ('Alice', 19, 'Zürich'), ('Bob', 20, 'Bern'), ('Carola',18, 'Zürich'); -- Abfrage: SELECT * FROM studenten WHERE age > 18 AND city = 'Z\u00fcrich'; ``` Ergebnis: Zeile mit Alice (19, Zürich) und evtl. Carola nur, wenn 18 als „nicht über 18“ gewertet wird (hier *nicht*, da >18). ### Übung 2: JOIN und Aggregation **Aufgabe:** Gegeben sind zwei Tabellen: `kunden(id, name)` und `bestellungen(id, kunde_id, betrag)`. Fülle je zwei Kunden und drei Bestellungen ein (Bestellungen referenzieren `kunde_id`). Schreibe eine Abfrage, die für jeden Kunden den Gesamtbetrag seiner Bestellungen ermittelt (Summe) und nur Kunden mit Summe > 100 anzeigt. **Lösung:** ```sql CREATE TABLE kunden ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) ); CREATE TABLE bestellungen ( id INT AUTO_INCREMENT PRIMARY KEY, kunde_id INT, betrag DECIMAL(10,2), FOREIGN KEY (kunde_id) REFERENCES kunden(id) ); INSERT INTO kunden (name) VALUES ('M\u00e4ller'),('Schmidt'); INSERT INTO bestellungen (kunde_id, betrag) VALUES (1, 80.00), (1, 50.00), (2, 120.00); -- Abfrage mit JOIN und GROUP BY: SELECT k.name AS Kunde, SUM(b.betrag) AS Gesamt FROM kunden k JOIN bestellungen b ON k.id = b.kunde_id GROUP BY k.id HAVING SUM(b.betrag) > 100; ``` Ergebnis: Kunde „Müller“ (80+50=130), „Schmidt“ (120) – beide über 100 werden angezeigt. ### Übung 3: Datenimport per CSV (phpMyAdmin) **Aufgabe:** Erstelle eine Tabelle `produkte(id, name, preis DECIMAL(6,2))`. Speichere eine Excel-Tabelle mit 3 Zeilen (Name und Preis) als CSV (UTF-8) und importiere sie via phpMyAdmin. Zeige alle Produkte an. **Lösung:** 1. Tabelle in phpMyAdmin erstellen oder via SQL: ```sql CREATE TABLE produkte ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100), preis DECIMAL(6,2) ); ``` 2. Excel-Datei mit Spaltenüberschrift `name,preis` (z.B. Apfel/1.20, Brot/2.50, Milch/1.10) speichern. 3. In phpMyAdmin: Datenbank auswählen, Reiter „Importieren“, CSV-Datei hochladen, Trennzeichen `,`, Zeichensatz UTF-8, „Erste Zeile als Spaltenkopf“ aktivieren, Import starten. 4. Kontrolle: ```sql SELECT * FROM produkte; ``` Ausgabe: Drei Zeilen mit den Produkten. Bei Fehlern: Prüfe Zeichensatz und Trennzeichen. ### Übung 4: Normalisierung (Erkennen) **Aufgabe:** Gegeben ist folgende Tabelle (jede Zeile ist ein Datensatz). Prüfe, welche Normalformen verletzt sind, und normalisiere die Daten: | MitarbeiterID | Name | AbtID | AbtName | PjID | PjName | Stunden | |--------------|-------|-------|----------|--------|--------|--------| | 1 | Müller| 10 | Verkauf | 100 | A | 50 | | 1 | Müller| 10 | Verkauf | 101 | B | 30 | | 2 | Meier | 10 | Verkauf | 101 | B | 20 | **Lösung:** - **Verletzungen:** Die Tabelle ist *nicht in 1NF*, da Name und Abteilung mehrfach (Atomarität ok hier) – das Problem ist hier transitive und partielle Abhängigkeiten. Auch *2NF* ist verletzt, weil Abteilungsname nur von AbtID abhängt (Teil vom Schlüssel). *3NF*: Da AbtName transitiv über AbtID von MA abhängt. - **Normalisierung:** - Tabelle **Mitarbeiter**(MA_ID PK, Name, AbtID) - Tabelle **Abteilung**(AbtID PK, AbtName) - Tabelle **Projekt**(PjID PK, PjName) - Tabelle **Arbeitsstunden**(MA_ID, PjID, Stunden) mit zusammengesetztem PK (MA_ID,PjID). Beispiel-SQL: ```sql CREATE TABLE Abteilung ( AbtID INT PRIMARY KEY, AbtName VARCHAR(50) ); INSERT INTO Abteilung VALUES (10,'Verkauf'); CREATE TABLE Mitarbeiter ( MA_ID INT PRIMARY KEY, Name VARCHAR(50), AbtID INT, FOREIGN KEY (AbtID) REFERENCES Abteilung(AbtID) ); INSERT INTO Mitarbeiter VALUES (1,'M\u00fcller',10),(2,'Meier',10); CREATE TABLE Projekt ( PjID INT PRIMARY KEY, PjName VARCHAR(50) ); INSERT INTO Projekt VALUES (100,'A'),(101,'B'); CREATE TABLE Stunden ( MA_ID INT, PjID INT, Stunden INT, PRIMARY KEY (MA_ID,PjID), FOREIGN KEY (MA_ID) REFERENCES Mitarbeiter(MA_ID), FOREIGN KEY (PjID) REFERENCES Projekt(PjID) ); INSERT INTO Stunden VALUES (1,100,50),(1,101,30),(2,101,20); ``` Damit sind 1NF–3NF erfüllt: keine mehrfachen Werte in einer Spalte, keine partiellen oder transitiven Abhängigkeiten mehr. ## Quellen - MySQL 8.0/9.x Referenz-Handbuch (insbesondere Kapitel **SQL Statements**, **Data Type Storage Requirements**, **String Functions**, **Comparison Operators**, **Foreign Keys**). - PHPMyAdmin-Dokumentation (CSV-Import). - Dozentenvorlesungen und Musterlösungen (Modulunterlagen).