Bewertete Übungsaufgabe

-11- Arbeitsunterlagen
DVT LK13.1 2014/2015
SQL Befehlsübersicht
Lehrkraft:
__________________
Kurs:
0
__________________
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
SQL – Abfragen
SELECT [DISTINCT] Spalten/*
FROM tabelle1 [,tabelle2, tabelle3,…]
[WHERE Bedingung]
[GROUP BY Spalten [HAVING Bedingung] ]
[ORDER BY Spalten [ASC/DESC]]
Angaben in eckigen Klammern [ ] sind optional.
Syntax
SELECT *
FROM Tabellenname
SELECT Attribut1, Attribut2, …
FROM Tabellenname
SELECT Attribut1, Attribut2,…
FROM Tabellenname
WHERE Bedingung
AND, OR, NOT
DISTINCT
BETWEEN Wert1 AND Wert2
Beschreibung / Beispiel
Gibt alle Datensätze einer Tabelle aus.
Bsp.: Gib alle Mitarbeiter aus.
SELECT * FROM Mitarbeiter
Gibt von den Datensätzen der Tabelle nur ausgewählte Attribute aus (Projektionsabfrage).
Bsp.: Gib Nachname und Gehalt aller Mitarbeiter aus.
SELECT Nachname, Gehalt FROM Mitarbeiter
Es werden nur die Datensätze selektiert, welche der WHEREBedingung genügen (Selektionsabfrage).
Bsp.: Gib Nachname und Gehalt von allen Mitarbeitern aus, die mehr
als 500 € verdienen.
SELECT Nachname, Gehalt FROM Mitarbeiter WHERE Gehalt > 500
Bedingungen können durch diese logischen Operatoren verknüpft werden.
Bsp.: Gib Nachname und Gehalt aller Mitarbeiter aus, die zwischen
500 € und 1000 € verdienen.
SELECT Nachname, Gehalt FROM Mitarbeiter
WHERE Gehalt > 500 AND Gehalt < 1000
Gibt an, dass identische Datensätze nur einmal ausgegeben
werden sollen.
Bsp.: Gib alle verschiedenen Nachnamen der Mitarbeiter aus.
SELECT DISTINCT Nachname FROM Mitarbeiter
Gibt an, dass ein Attributwert zwischen zwei Werten liegen darf.
Bsp.: Gib Nachname und Gehalt aller Mitarbeiter aus, die zwischen
500 € und 1000 € verdienen.
SELECT Nachname, Gehalt FROM Mitarbeiter
WHERE Gehalt BETWEEN 500 AND 1000
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
IN (Wert1, Wert2,…)
Gibt an, dass ein Attribut nur Werte aus einer Menge von Werten annehmen darf.
Bsp.: Gib Nachname und Gehalt von allen Mitarbeitern aus, deren Nachname
Müller, Meier oder Maier ist.
SELECT Nachname, Gehalt FROM Mitarbeiter
WHERE Nachname IN (‚Müller', 'Meier', 'Maier')
LIKE
% beliebige Anzahl
Zeichen
_ einzelnes
beliebiges Zeichen
Dient zum Vergleichen zweier Zeichenfolgen .
Bsp.: Gib die Namen aller Zulieferer aus, deren PLZ mit 7 beginnt.
SELECT name
FROM Zulieferer
WHERE plz LIKE „7%“
Legt in der Ausgabe für ein Attribut einen Alternativnamen fest.
Bsp.: Gib die Anzahl der Mitarbeiter aus.
SELECT COUNT(Vorname) AS Anzahl_Mitarbeiter
FROM Mitarbeiter
Sortiert die Daten nach einem oder mehreren Attributen. (ASC = aufsteigende Reihenfolge, DESC = absteigende Reihenfolge)
Bsp.: Gib Nachname und Gehalt aller Mitarbeiter aus. Die Datensätze sollen
absteigend nach Gehalt sortiert werden.
SELECT Nachname, Gehalt
FROM Mitarbeiter
ORDER BY Gehalt DESC
Begrenzt die Anzahl der auszugebenen Datensätze. Es kann die Anzahl der Datensätze angegeben werden (beginnend beim ersten Datensatz) oder es kann angegeben werden, ab welchem Datensatz gezählt werden soll.
Bsp.: Aus der Bestverdienerliste sollen die Plätze 5-10 ausgegeben werden.
SELECT Nachname, Gehalt
FROM Mitarbeiter
ORDER BY Gehalt DESC
LIMIT 4,6 (die Zählung der Datensätze beginnt bei 0 !!!)
Fasst Datensätze zusammen, die denselben Attributwert besitzen. Für
jeden Datensatz wir d ein Ergebniswert berechnet, wenn Sie eine SQLFunktion wie SUM oder COUNT in der SELECT-Anweisung angeben.
Bsp.: Gib den Gesamtumsatz der Zulieferer nach PLZ gruppiert an.
SELECT plz, SUM(umsatz)
FROM Zulieferer
GROUP BY plz
AS
ORDER BY Spalten
[ASC/DESC]
LIMIT Anzahl
LIMIT Start, Anzahl
GROUP BY Spalten
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
HAVING
Wählt aus den gruppierten Datensätzen die Gruppen aus, welche der
HAVING-Bedingung genügen.
Bsp.: Gib den Gesamtumsatz der Zulieferer nach PLZ gruppiert an. Es sollen
nur PLZ-Gruppen berücksichtig werden, deren Gesamtumsatz > 30000 € ist.
SELECT plz, SUM(umsatz)
FROM Zulieferer
GROUP BY plz HAVING SUM(umsatz) > 30000
JOIN - Arten
FROM Tabelle1
JOIN Tabelle2
ON Bedingung
FROM Tabelle1
LEFT JOIN Tabelle2
ON Bedingung
FROM Tabelle1
RIGHT JOIN Tabelle2
ON Bedingung
FROM Tabelle1
FULL JOIN Tabelle2
ON Bedingung
Gibt Datensätze aus zwei Tabellen aus. Dabei werden nur solche Datensätze ausgegeben, welche der ON-Bedingung genügen.
Bsp.: Gib alle Busse aus, die momentan auf Fahrten eingesetzt sind.
SELECT * FROM Fahrt JOIN Bus ON Fahrt.Kennzeichen = Bus.Kennzeichen
Gibt Datensätze aus zwei Tabellen aus. Dabei müssen nur die Datensätze aus der rechten Tabelle der JOIN-Bedingung genügen.
Bsp.: Gib alle Fahrten aus. Sind den Fahrten schon Busse zugewiesen, sollen
auch dessen Daten ausgegeben werden.
SELECT * FROM Fahrt LEFT JOIN Bus ON Fahrt.Kennzeichen = Bus.Kennzeichen
Gibt Datensätze aus zwei Tabellen aus. Dabei müssen nur die Datensätze aus der linken Tabelle der JOIN-Bedingung genügen.
Bsp.: Gib alle Busse aus. Sind die Busse einer Fahrt zugewiesen, sollen auch
die Fahrdaten ausgegeben werden.
SELECT * FROM Fahrt RIGHT JOIN Bus
ON Fahrt.Kennzeichen = Bus.Kennzeichen
Gibt Datensätze aus zwei Tabellen aus. Datensätze welche die JOINBedingung erfüllen, werden in einer Zeile angezeigt. Es werden aber
auch alle anderen Datensätze der Tabellen angezeigt.
Bsp.: Gib alle Fahrten und Busse aus. Sind Busse einer Fahrt zugewiesen, sollen diese Datensätze zusammen ausgegeben werden.
SELECT * FROM bestpos FULL JOIN teile ON bestpos.teileNr=teile.teileNr
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
SQL – Funktionen
Syntax
AVG(Ausdruck)
Beschreibung / Beispiel
Berechnet den arithmetischen Mittelwert einer Menge von Werten in einem
bestimmten Feld einer Abfrage.
Bsp.: Gib den Durchschnittsumsatz aller Zulieferer in Tausend € an.
SELECT AVG(umsatz/1000) AS Durchschnitt FROM zulieferer
COUNT(Ausdruck) Berechnet die Anzahl der von einer Abfrage zurückgegebenen Datensätze.
Bsp.: Gib die Anzahl aller Artikel aus, deren Einkaufspreis größer als 20 € ist.
SELECT COUNT(*) AS Anzahl FROM Artikel WHERE ekpreis > 20
Gibt den größten Wert aus einer Reihe von Werten zurück, die in einem
MAX(Ausdruck)
bestimmten Feld einer Abfrage enthalten sind.
Bsp.: Gib den höchsten Umsatz aller Zulieferer an.
SELECT MAX(umsatz) AS maxUmsatz FROM zulieferer
Gibt den kleinsten Wert aus einer Reihe von Werten zurück, die in einem
MIN(Ausdruck)
bestimmten Feld einer Abfrage enthalten sind.
Bsp.: Gib den kleinsten Umsatz aller Zulieferer an.
SELECT MIN(umsatz) AS maxUmsatz FROM zulieferer
Berechnet die Summe einer Menge von Werten in einem bestimmten Feld
SUM(Ausdruck)
einer Abfrage.
Bsp.: Gib den Gesamtumsatz aller Zulieferer an.
SELECT SUM(umsatz) AS Gesamtumsatz FROM zulieferer
Einfügen und Ändern von Datensätzen
Syntax
INSERT INTO Tabellenname
VALUES (Wert1, Wert2,…)
INSERT INTO Tabellenname
(Attribut1, Attribut2,…)
VALUES (Wert1,Wert2,…)
Beschreibung / Beispiel
Fügt einen kompletten Datensatz in eine Tabelle ein. Die Werte
werden in derselben Reihenfolge angegeben wie im Relationenschema angegeben ist.
Bsp.: Füge den Mitarbeiter Peter Müller (MNr. 5) mit Gehalt 5000 ein.
INSERT INTO Mitarbeiter VALUES (5, 'Peter', 'Müller', 5000)
Fügt einen Datensatz in eine Tabelle ein. Die Liste der Attribute
legt Reihenfolge und Anzahl der folgenden Werte fest. Ein nicht
angegebenes Attribut der Relation wird mit Wert null belegt.
Bsp.: Füge den Mitarbeiter Peter Müller (MNr. 5) ohne Gehalt ein.
INSERT INTO Mitarbeiter (MNr, Vorname, Nachname)
VALUES (5, 'Peter', 'Müller')
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
UPDATE Tabellenname
SET Attribut1=Wert1,…
[WHERE Bedingung]
DELETE FROM Tabellenname
[WHERE Bedingung]
Setzt die angegebenen Attribute der Tabelle auf einen neuen
Wert. Die optionale WHERE-Bedingung selektiert bestimmte
Datensätze, die aktualisiert werden sollen.
Bsp.: Alle Mitarbeiter mit einem Gehalt < 3000 bekommen eine Gehaltserhöhung von 5%.
UPDATE Mitarbeiter SET Gehalt=Gehalt*1.05 WHERE Gehalt < 3000
Löscht Datensätze aus seiner Tabelle. Die optionale WHEREBedingung selektiert bestimmte Datensätze, die gelöscht werden
sollen.
Bsp.: Der Mitarbeiter mit der MNr. 17 soll gelöscht werden.
DELETE FROM Mitarbeiter WHERE MNr=17
Erzeugen und löschen von Tabellen
Syntax
DROP Tabellenname
CREATE TABLE Tabellenname (
Attribut1 Datentyp1 [constraints],
Attribut2 Datentyp2 [constraints],
…)
UNIQUE
NOT NULL
PRIMARY KEY
Beschreibung / Beispiel
Löscht die Tabelle und alle Datensätze.
Bsp.: Lösche die Tabelle Mitarbeiter.
DROP Mitarbeiter
Erzeugt eine Tabelle mit den angegebenen Attributen.
Die möglichen Datentypen werden in der nächsten Tabelle angegeben. Die optionalen constraints werden in
dieser Tabelle erläutert.
Bsp.: Lege die Tabelle Mitarbeiter an.
CREATE TABLE Mitarbeiter(
MNr INT PRIMARY KEY,
Vorname VARCHAR(30) NOT NULL,
Nachname VARCHAR(30) NOT NULL,
Spitzname VARCHAR(30) UNIQUE,
Ort VARCHAR(30) DEFAULT 'Bensheim',
Geschlecht CHAR CHECK (Geschlecht='m' OR Geschlecht='w'),
AbtNr INT FOREIGN KEY REFERENCES Abteilung(AbtNr),
Gehalt DOUBLE CHECK (Gehalt>0)
)
Gibt an, dass die Werte dieses Attributes in allen Datensätzen unterschiedlich sein muss.
Gibt an, dass das Attribut einen Wert besitzen muss
(null-Wert ist nicht erlaubt).
Ist eine Kombination von UNIQUE und NOT NULL. Definiert einen Primärschlüssel.
Klasse / Kurs:
Thema / LS:
Datum:
DVT LK13.1 2014/2015
FOREIGN KEY
REFERENCES Tabellenname (Attribut)
CHECK Bedingung
DEFAULT Attributwert
Definiert einen Fremdschlüssel. Hinter REFERENCES
wird der Primärschlüssel einer Tabelle angegeben, mit
der der Fremdschlüssel verknüpft ist. Durch diese Angabe wird die referentielle Integrität der DB gewährleistet.
Prüft beim Einfügen oder Ändern eines Datensatzes, ob
der Attributwert der angegebenen Bedingung genügt.
Durch diese Angabe wird die semantische Integrität der
DB gewährleistet.
Definiert einen Standardwert für dieses Attribut, wenn
beim Einfügen eines Datensatzes kein anderer Wert angegeben wird.
SQL – Datentypen
Datentyp
CHAR(n)
Speicherplatz
-
INT
Beschreibung
Zeichenkette mit fester Länge. n gibt die
Anzahl der Zeichen an.
Zeichenkette mit variabler Länge. n gibt
die maximale Anzahl Zeichen an.
Speichert ganze Zahlen.
DOUBLE
Speichert Fließkommazahlen.
8 Byte
FLOAT
Speichert Fließkommazahlen.
4 Byte
BLOB
Speichert Binärdaten (z.B. Bilder).
-
TIMESTAMP
Enthält Datum und Uhrzeit.
-
DATE
Enthält ein Datum.
-
TIME
Enthält eine Uhrzeit.
-
VARCHAR(n)
4 Byte