Aufgaben zu SQL
Aufgabe 1
Beantworte folgende Fragen:
- Wofür steht SQL?
- In welche 4 Bereiche lassen sich die Befehle einordnen?
- Und was ist die "referentielle Integrität"?
Lösung
SQL steht für "structured query language" und wird in:
- (DQL) zur Abfrage und Aufbereitung der gesuchten Informationen
- (DML) zur Datenmanipulation und lesendem Zugriff
- (DDL) zur Definition des Datenbankschemas
- (DCL) für die Rechteverwaltung und Transaktionskontrolle
Bereich eingeteilt. Die referentielle Integrität beschreibt die Bedingung, dass Informationen miteinander verbunden sind. So muss es für einen Fremdschlüssel den jeweiligen Primärschlüssel geben.
Aufgabe 2
Erstelle die Datenbank "JS-buildings" mit folgenden Tabellen.
- "Employees": Jeder Angestellte hat eine fortlaufende ID, Vor- und Nachnamen mit jeweils maximal 20 Zeichen, bekommt einen ganzzahligen Lohn, der wenn nicht angegeben 450 beträgt und hat maximal ein optionales Hobby
- "sold_buildings": Hier sind alle Gebäude verzeichnet, die von den Mitarbeitern gemanagt werden. Jeder Mitarbeiter kümmert sich um ein Gebäude. Die Gebäude haben eine Bezeichnung und verursachen Kosten im 4 stelligen Bereich
Ändere den ganzzahligen Lohn zu einem mit 2 Nachkommastellen im 4 stelligen Bereich und entferne die Spalte Hobby. Die Gebäude erwirtschaften auch Erlöse im 5 stelligen Bereich.
Lösung
CREATE DATABASE `JS-buildings` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `employees` (
id int(11) PRIMARY KEY AUTO_INCREMENT,
forename varchar(20) NOT NULL,
sirname varchar(20) NOT NULL,
salary int(5) NOT NULL DEFAULT 450,
hobby varchar(50) NULL
);
CREATE TABLE `sold_buildings` (
id int(11) PRIMARY KEY AUTO_INCREMENT,
b_name varchar(30) NOT NULL,
costs decimal(6,2) NOT NULL
);
ALTER TABLE employees
MODIFY salary decimal(6,2),
DROP hobby;
ALTER TABLE sold_buildings
ADD revenues decimal(7,2);
Aufgabe 3
Übertrage die Werte in die Tabellen.
Angestellte
| 1 | Max | Peters | 450 |
| 2 | Mila | Neumeier | 2048 |
| 3 | Alexa | Finke | 900,24 |
| 4 | Julian | Schönling | 450 |
| 5 | Fino | Neumeier | 7030,50 |
| 6 | Heino | Schmidt | 1040,20 |
| 7 | Lisa | Neumeier | 675 |
| 8 | Adrian | Peters | 3090 |
Gebäude
| 1 | STRG+C-Schule | 8250,90 | 11300 |
| 2 | Gib-aus-Bank | 1760,45 | 20500 |
| 3 | Sammelkiosk | 3140 | 13050 |
| 4 | Luxuswohnung | 9990,01 | 35900 |
| 5 | STRG+V-Kirche | 560 | 09000 |
| 6 | Obama-Tower | 6500 | 30001,50 |
| 7 | Kammer des Fürchtens | 2000,60 | 02500 |
| 8 | Museum der Toten | 900 | 15670,50 |
| 9 | Lisa Feller | 450 | 0 |
Lösung
INSERT INTO employees VALUES
(1, "Max", "Peters", 450),
(2, "Mila", "Neumeier", 2048),
(3, "Alexa", "Finke", 900.24),
(4, "Julian", "Schönling", 450),
(5, "Fino", "Neumeier", 7030.5),
(6, "Heino", "Schmidt", 1040.20),
(7, "Lisa", "Neumeier", 675),
(8, "Adrian", "Peters", 3090);
INSERT INTO sold_buildings (b_name, costs, revenues) VALUES
("STRG+C-Schule", 8250.9, 11300),
("Gib-aus-Bank", 1760.45, 20500),
("Sammelkiosk", 3140, 13050),
("Luxuswohnung", 9990.1, 35900),
("STRG+V-Kirche", 560, 9000),
("Obama-Tower", 6500, 39991.5),
("Kammer des Fürchtens", 2000.6, 2500),
("Museum der Toten", 900, 15670.5),
("Lisa Feller", 450, 0);
Aufgabe 4
Ändere die Werte:
- "" um die Tastenkombinationen
- Erlöse um 5% verringern
- lösche Lisa Feller
Lösung
UPDATE sold_buildings
SET b_name='"STRG+C-Schule"'
WHERE id=1;
UPDATE sold_buildings
SET b_name='"STRG+V-Kirche"'
WHERE id=5;
UPDATE sold_buildings
SET revenues=revenues*.95;
DELETE FROM sold_buildings
WHERE b_name="Lisa Feller";
Aufgabe 5
einfache Abfragen
- Angestellte (forename, id) aus der Familie Neumeier und Peters
- Mitarbeiter, die weniger als 3090 und nicht 675 oder 900,24 verdienen
- Alle Familien (Spalte als "Families") die bei JS-buildings angestellt sind
- Löhne, die zwischen 450 und 2000 liegen
komplexere Abfragen
- Welche Gebäude betreuen Adrian und Julian?
- Welches Gebäude erwirtschaftet (auch nach Abzug der Lohnkosten) am meisten und wie viel?
- Was verdienen die Angestellten im Durchschnitt
Lösung
SELECT id, forename
FROM employees
WHERE sirname IN ('Neumeier', 'Peters'); -- a)
SELECT *
FROM employees
WHERE salary < 3090 AND salary NOT IN (675, 900.24); -- b)
SELECT DISTINCT sirname as famalies
FROM employees; -- c)
SELECT salary
FROM employees
WHERE salary BETWEEN 450 AND 2000; -- d)