SQL Hands-On Workshop 2010 EB Zürich Ergänzende

Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen und Übungen
1. Abfragen mit einer Tabelle (Unterlagen Herdt; Kap. 7)
Serie A
Anzahl
Datensätze
A1.
Erstellen Sie eine Abfrage Q01_Namensliste, die Vorname, Nachname und
Ort jeder Adresse anzeigt (in dieser Reihenfolge, aber nach Nachname und
Vorname sortiert).
191
A2.
Erstellen Sie eine Abfrage Q02_AusserhalbZuerich, die alle Adressen
auswählt, die nicht in Zürich liegen.
184
A4.
Erstellen Sie eine Abfrage Q04_Ortschaften, die eine Liste sämtlicher
Ortschaften ausgibt, aus denen Kursteilnehmer oder Kursleiter kommen.
68
A5.
Erstellen Sie eine Abfrage Q05_PLZZwischen8000Und8999, die alle
Adressen auswählt, deren Postleitzahl zwischen 8000 und 8999 liegt.
54
A6.
Erstellen Sie eine Abfrage Q06_Meierei, die alle Adressen auswählt, deren
Nachname Meier, Maier, Meyer oder Mayer lautet.
14
A7.
Erstellen Sie eine Abfrage Q07_NachnameMitM, die alle Adressen auswählt,
deren Nachname mit einem M anfängt.
30
A8.
Erstellen Sie eine Abfrage Q08_NachnameMitVokal, die alle Adressen
auswählt, deren Nachname mit einem Vokal beginnt.
29
A9.
Sie haben den Namen einer Kursteilnehmerin vergessen – Sie wissen nur noch,
dass der südländisch klingende Nachname einen Apostroph (') enthält.
Erstellen Sie eine Abfrage Q09_Apostroph, mit deren Hilfe Sie den Namen
der Kursteilnehmerin herausfinden können.
A10. Sie haben den Namen eines Kursteilnehmers vergessen – Sie wissen nur noch,
dass der Nachname mit ammann aufhört. Erstellen Sie eine Abfrage Q10_ammann, mit deren Hilfe Sie den gesuchten Kursteilnehmer finden können.
A11. Und Sie sind so vergesslich, dass Sie noch einen dritten Namen vergessen
haben, irgend etwas mit hau oder hauen oder so... – metallisch klingend.
Finden Sie den Namen mit Hilfe einer Abfrage Q11_Hauruck heraus.
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
3, aber nur eine
Frau
7, aber nur ein
"Nicht Ammann"
3, aber nur 2
eindeutig metallisch
Seite 1 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
2. Nützliche Auswahl von MS SQL Server Funktionen
Aggregatsfunktionen
Funktion und Parameter
Beispiel
-- Anzahl:
SELECT COUNT(*) FROM T02_Kurs
-- Oder:
SELECT COUNT(KNr) FROM T02_Kurs
-- Summierung:
SELECT SUM(KKost) FROM T02_Kurs
-- Minimum:
SELECT MIN(KKost) FROM T02_Kurs
-- Maximum:
SELECT MAX(KKost) FROM T02_Kurs
-- Durchschnitt:
SELECT AVG(KKost) FROM T02_Kurs
-- Standardabweichung:
SELECT STDEV(KKost) FROM T02_Kurs
Systemfunktionen
Funktion und Parameter
Beispiel
-- Nullwert umwandeln:
SELECT ISNULL(Vorname, 'KEIN VORNAME') FROM
T01_Person
-- Datentyp konvertieren:
SELECT CONVERT(datetime, '2007-05-11', 102)
-- Test ob Datum:
SELECT ISDATE('2007-05-11')
-- oder:
SELECT ISDATE('2007-02-31')
-- Test ob Zahl:
SELECT ISNUMERIC('123.456')
-- oder:
SELECT ISNUMERIC('123.456 kg')
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
Seite 2 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
Funktion und Parameter
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
Beispiel
-- OS Login:
SELECT SYSTEM_USER
-- DB User:
SELECT CURRENT_USER
-- Maschinenname:
SELECT HOST_NAME()
-- Session ID:
SELECT HOST_ID()
Zeichenfolgefunktionen
Funktion und Parameter
Beispiel
-- Anzahl Zeichen:
SELECT LEN('123')
-- Erster Fundort:
SELECT CHARINDEX('XX', 'asXXdf', 1)
-- Erster Fundort (Muster):
SELECT PATINDEX('%Xd%', 'aXsXdf')
-- Linker Teil:
SELECT LEFT('Otto', 3)
-- Rechter Teil:
SELECT Right('Otto', 3)
-- Teil einer Zeichenfolge:
SELECT SUBSTRING('Otto', 2, 2)
-- Leerzeichen links entfernen:
SELECT LTRIM(' Otto')
-- Leerzeichen rechts entfernen:
SELECT LTRIM('Otto ')
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
Seite 3 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
Funktion und Parameter
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
Beispiel
-- Zahl in Text mit fixer Länge umwandeln:
SELECT STR(1.2345, 10, 2)
-- In Grossbuchstaben:
SELECT UPPER('otto')
-- In Kleinbuchstaben:
SELECT LOWER('OTTO')
-- ASCII Code:
SELECT ASCII('A')
-- Zeichen aus ASCII Code:
SELECT CHAR(65)
Datums- und Uhrzeitfunktionen
Funktion und Parameter
Beispiel
-- Systemdatum:
SELECT GETDATE()
-- Nur Jahr:
SELECT YEAR(GETDATE())
-- Nur Monat:
SELECT MONTH(GETDATE())
-- Nur Tag:
SELECT DAY(GETDATE())
-- Irgendein Datumsteil (Zahl):
SELECT DATEPART(hh, GETDATE())
-- Irgendein Datumsteil (Text):
SELECT DATENAME(mm, GETDATE())
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
Seite 4 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
Funktion und Parameter
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
Beispiel
-- Einheit zu Datum addieren:
SELECT DATEADD(dd, 7, GETDATE())
-- Anzahl Einheiten zwischen Datum:
SELECT DATEDIFF(dd, '2007-01-01', GETDATE())
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
Seite 5 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
3. Abfragen mit mehreren Tabellen (Herdt: Kap. 10)
Verknüpfungen bei Abfragen mit mehreren Tabellen
Bestellungen
BestNr
Bestellpositionen
BestDat
1
2
3
4
5
6
BestNr
01.05.01
02.06.01
02.06.01
04.06.01
05.06.01
07.06.01
ArtNr
1
1
2
2
5
6
6
Artikel
Anzahl
1
4
1
3
2
1
2
ArtNr ArtBez
1
2
1
3
1
5
2
1
2
3
4
Festplatte 11 GB, SCSI
Festplatte 18 GB, IDE
Monitor EIZO T563
Pentium III, 800 MHz
Aufgabe:
Ziehen Sie mit einem Stift die entsprechenden Verbindungen zwischen einander zugeordneten Zeilen in
den Tabellen auf der vorherigen Seite.
Was "gehört zusammen"?
BestNr
BestDat
Bestellung 1:
1
01.05.01
1
01.05.01
Bestellung 3:
3
02.06.01
...
Bestellung 6:
6
07.06.01
6
07.06.01
BestNr
ArtNr
Anzahl
ArtNr ArtBez
1
1
1
4
1
2
1 Festplatte 11 GB, SCSI
4 Pentium III, 800 MHz
2
1
1
1 Festplatte 11 GB, SCSI
6
6
1
2
5
2
1 Festplatte 11 GB, SCSI
2 Festplatte 18 GB, IDE
Wieso das?
Weil man über die Spalte 'BestNr' (Fremdschlüssel) in der Tabelle Bestellpositionen herausfindet, welches
die dazugehörige Bestellung ist (die mit der gleichen BestNr in der Tabelle Bestellungen).
Und weil man über die Spalte 'ArtNr' (Fremdschlüssel) in der Tabelle Bestellpositionen herausfindet,
welches der dazugehörige Artikel ist (der mit der gleichen ArtNr in der Tabelle Artikel).
Diese Verknüpfung von den drei Tabellen muss einer Abfrage zuerst 'mitgeteilt' werden:
...
FROM
...
Bestellungen AS b
INNER JOIN Bestellpositionen AS p ON b.BestNr=p.BestNr
INNER JOIN Artikel AS a ON a.ArtNr=b.ArtNr
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
Seite 6 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
Aufgaben:
Versuchen Sie mittels Abfragen die folgenden Fragen zu beantworten. Beachten Sie, dass rechts neben
jeder Aufgabenstellung die Anzahl Datensätze (Zeilen) für die richtige Lösung angegeben ist.
Serie B
Anzahl
Datensätze
B1. Wie heissen die KursteilnehmerInnen, die sich am 8.8.2002 angemeldet haben?
__________________________________________________________
4 Datensätze
B2. Wie lauten die Kursnummern der Kurse, die von TeilnehmerInnen aus Amriswil
besucht werden?
__________________________________________________________
4 Datensätze
B3. Wer leitet den Kurs mit Kursnummer 5?
__________________________________________________________
1 Datensatz
B4. Wieviel kostet der Kurs Datenbank: Aufbau?
__________________________________________________________
4 Datensätze
B5. Wer aus Kreuzlingen besucht den Kurs Datenbank: Einführung?
__________________________________________________________
B6. Bei wem besucht Frau Erika Müller Kurse?
__________________________________________________________
B7. Welches ist die Nummer des meist besuchten Kurstyps? Wie viele Teilnehmer
besuchen ihn? Wie lautet seine Bezeichnung?
__________________________________________________________
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
1 Datensatz
2 Datensätze
1 Datensatz
(Verwenden
von
Gruppierungen)
(5 Datensätze
ohne Top 1)
Seite 7 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
Serie C
Anzahl
Datensätze
C1.
Erstellen Sie eine Abfrage "Q01_KursteilnehmerEinesKurses", die Vor- und Nachname
aller KursteilnehmerInnen von Kursnummer 8 ausgibt.
C2.
Erstellen Sie eine Abfrage "Q02_KurseVonFrauIseli", die alle Kurse (KNr) ausgibt, die von
Frau Jaqueline Iseli-Widmer besucht werden.
2
C3.
Erstellen Sie eine Abfrage "Q03_KurseOhneTeilnehmerInnen ", die alle Kurse (KNr)
ausgibt, die von niemandem besucht werden.
1
C4.
Erstellen Sie eine Abfrage "Q04_RolfsOhneKurse", die alle Personen mit Vorname "Rolf"
ausgibt, die keinen Kurs besuchen.
5
C5.
Erstellen Sie eine Abfrage "Q05_PersonenMitMehrfachteilnahmen", die alle Personen
ausgibt, die mehr als einen Kurs belegen.
C6.
Wie heissen die Personen, die genau 3 Kurse belegen?
C7.
Erstellen Sie eine Abfrage "Q07_AnzTeilnehmerInnenProKurs", die für alle Kurse angibt,
wie viele Personen daran teilnehmen.
C8.
Erstellen Sie eine Abfrage "Q08_unbezahlteAnmeldungen", die angibt, wie viele
Kursteilnahmen noch unbezahlt sind.
C9.
Erstellen Sie eine Abfrage "Q09_ausstehendeZahlungenTotal", die den Gesamtbetrag der
ausstehenden Zahlungen ausgibt.
11
19
1
13
124
1
C10. Erstellen Sie eine Abfrage "Q10_TeamTeachingKurse", die alle Kurse ausgibt, die von
mehr als einem Kursleiter oder einer Kursleiterin geleitet werden.
1
C11. Erstellen Sie eine Abfrage "Q11_lernendeLehrerInnen", die alle Personen ausgibt, die
sowohl Kurse leiten als auch Kurse besuchen.
2
C12. Wie lauten die AHV Nummern aller KursleiterInnen, die den Kurs "Datenbank: Aufbau"
unterrichten?
3
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
Seite 8 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
Serie D: MiniPars
D1. Über welche Felder in T30 werden T01 und T02 miteinander 'verbunden'? Welches sind die
'Brückenköpfe'?
_________________________________________________________________________
D3.
Wie viele verschiedene Airlines gibt es?
_______________________________________________________________________
D4.
Welche Airlines fliegen nach New York Kennedy ?
_______________________________________________________________________
D5.
Wieviel kostet die teuerste Buchung?
_______________________________________________________________________
D6.
Wieviel kosten alle Buchungen zusammen?
_______________________________________________________________________
D7.
Wieviel kosten alle Buchungen im Durchschnitt (Tip: wie Aufgabe 6. Was müssen Sie verwenden
an Stelle von Summe)?
_______________________________________________________________________
D8.
Welcher Passagier fliegt am häufigsten?
_______________________________________________________________________
D9.
Summieren Sie die Flugpreise pro Passagier. Welche drei Passagiere zahlen am meisten?
_______________________________________________________________________
D10. Erstellen Sie eine Abfrage, aus der Sie die Anzahl freier Sitze für jeden Flug und die prozentuale
Auslastung entnehmen können.
_______________________________________________________________________
D11. Welches ist der Durchschnitt der prozentualen Auslastung?
_______________________________________________________________________
D12. Wer fliegt mit Air France nach Frankreich oder nach Belgien?
_______________________________________________________________________
D13. Welche Personen zahlen mehr als 3'000.- Fr.?
_______________________________________________________________________
D14. Wieviel kosten deren Tickets insgesamt?
_______________________________________________________________________
D15. Aktualisierungsabfrage: Erhöhen Sie alle Preise um 6.4% in der Reservationstabelle (Erstellen Sie
zuerst eine Sicherungskopie der Tabelle).
_______________________________________________________________________
D16. Löschabfrage: Löschen Sie alle Buchungen zwischen 1. bis 20. Febr. 2001 (Erstellen Sie zuerst eine
Sicherungskopie der Tabelle).
_______________________________________________________________________
D17. Tabellenerstellungsabfrage: Erstellen Sie eine neue Tabelle von allen Passagieren (Adressen), die
mit dem Flug AZ 143 am 10. Juni 2001 nach Rom geflogen sind.
_______________________________________________________________________
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
Seite 9 / 10
Inforis AG
Beckenhofstrasse 16
8006 Zürich
www.inforis.ch
SQL Hands-On Workshop 2010
EB Zürich
Ergänzende Unterlagen
4. Datenbank Diagramme
Datenbank KUTAdmin:
Datenbank Bestell
Datenbank MiniPars
M:\11_Schul\EB_Zuerich\2010_Q3_SQL_HandsOnWorkshop\20100720_Funktionen_und_SQLAufgaben.doc
Werner Müntener, Inforis AG
Seite 10 / 10