Montag, 9. Juni 2008
Übungsbeispiele
Topic: 'Tutorien (IV2)'
Anbei findet ihr Lösungen zu einigen Übungsbeispielen:
1. Anzahl der Bücher je Verlag:
SELECT Verlag, COUNT(*) AS Buchanzahl
FROM Buch
GROUP BY Verlag
2. Maximaler, Minimaler, Durchschnittl. Buchpreis, Summe aller Buchpreise, Anzahl aller Bücher:
SELECT MAX(Buch.Preis), MIN(Buch.Preis), AVG(Buch.Preis), SUM(Buch.Preis), COUNT(Buch.Bestand)
FROM Buch
3. Titel, Verlag, Name (aus Tabelle Verlag) aller Bücher:
SELECT Buch.Titel, Buch.Verlag, Verlag.Name
FROM Verlag, Buch
WHERE Verlag.Kurzbezeichnung=Buch.Verlag
4. Nr, Auftrags-, Buchnummer, Menge, Preis, Preis*Menge (=Umsatz pro Buch), nur von der Auftragsnummer 1:
SELECT Auftragspos.Nr, Auftragspos.Auftragsnummer, Auftragspos.Buchnummer, Auftragspos.Menge, Buch.Preis, Buch.Preis*Auftragspos.Menge AS Umsatz_pro_Buch
FROM Buch, Auftragspos
WHERE Buch.Nr=Auftragspos.Buchnummer AND Auftragspos.Auftragsnummer=1
5. Auftragsnummer und Gesamteinkaufssumme der Auftragsnummer 1:
SELECT Auftragspos.Auftragsnummer, SUM(Buch.Preis*Auftragspos.Menge)
FROM Buch, Auftragspos
WHERE Buch.Nr=Auftragspos.Buchnummer AND Auftragspos.Auftragsnummer=1
GROUP BY Auftragspos.Auftragsnummer
6. Auftrags-, Kundennummer, Gesamteinkaufssumme, gruppieren nach Auftragsnummer, jene Datensätze mit Gesamteinkaufssumme > 20.000:
SELECT Auftragspos.Auftragsnummer, Auftrag.Kundennummer, SUM(Buch.Preis*Auftragspos.Menge) AS Summe
FROM Auftrag, Auftragspos, Buch
WHERE Auftrag.Nr=Auftragspos.Auftragsnummer AND Buch.Nr=Auftragspos.Buchnummer
GROUP BY Auftragspos.Auftragsnummer
HAVING Summe>20000
7. Alle bisher erfassten Aufträge sollen angezeigt werden, und zwar mit Auftragsnummer, Kundenname (Nachname) und Auftragssumme. Anzeige soll nach der Auftragssumme absteigend sortiert werden:
SELECT Auftragspos.Auftragsnummer, Kunde.Nachname, SUM(Buch.Preis) AS Auftragssumme
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr
GROUP BY Kunde.Nachname
ORDER BY Auftragssumme DESC
8. Es soll der Verlagsname und die Anzahl der Bücher die dieser Verlag veröffentlicht hat ausgegeben werden:
SELECT Verlag.Name, COUNT(Buch.Titel) AS Anzahl_der_Buecher
FROM Verlag, Buch
WHERE Verlag.Kurzbezeichnung=Buch.Verlag
GROUP BY Verlag.Name
9. Wie viele Kunden wohnen in welcher Region (gruppiert nach Region):
SELECT PLZ.Region, COUNT(Kunde.Nr) AS Anzahl_der_Kunden
FROM PLZ, Kunde
WHERE PLZ.Plz=Kunde.Plz
GROUP BY PLZ.Region
10. Gesamtwert aller Bücher in denen das Wort "bio" vorkomt? Anzeige: Summe:
SELECT SUM(Buch.Preis) AS Summe
FROM Buch
WHERE Buch.Titel LIKE "%bio%"
11. Anzahl der Chemiebücher je Verlag, absteigend gereiht nach Vorkommen:
SELECT COUNT(*) AS Vorkommen, Buch.Verlag
FROM Buch
WHERE Buch.Titel LIKE "%chemie%"
GROUP BY Buch.Verlag
ORDER BY Vorkommen
12. Zeigen Sie den Nachnamen und das Datum der Aufträge an die im Zeitraum 26.01.2000 bis 28.01.2000 getätigt wurden. Geordnet soll nach dem Nachnamen werden:
SELECT Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Datum BETWEEN "2000-01-26" AND "2000-01-28"
ORDER BY Kunde.Nachname
13. Alle Kunden auswählen die mit Vornamen Michael heißen und deren PLZ kleiner als 4030 ist:
SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Plz
FROM Kunde
WHERE Kunde.Vorname LIKE "Michael" AND Kunde.Plz<4030
ORDER BY Kunde.Nachname
14. Welche Kunden kommen aus Wien?
SELECT Kunde.Vorname, Kunde.Nachname, PLZ.Ort
FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz AND PLZ.Ort="Wien"
ORDER BY Kunde.Nachname
15. Wie viele Bücher vertreibt der Morawa Verlag und wie hoch ist die Bestellsumme, wenn jedes Buch dieses Verlages einmal bestellt wird?
SELECT SUM(Buch.Preis), COUNT(Buch.Nr)
FROM Buch
WHERE Buch.Verlag="mc"
16. Wie viel kostet im Durchschnitt ein Buch des Autors Arge und um wie viele handelt es sich hierbei?
SELECT AVG(Buch.Preis), COUNT(Buch.Nr)
FROM Buch
WHERE Buch.Autor="Arge"
17. Welcher Autor hat wie viele Bücher geschrieben? Sortiere absteigend nach der Bücheranzahl.
SELECT Buch.Autor, COUNT(*)
FROM Buch
GROUP BY Buch.Autor
ORDER BY 2 DESC
18. Es sollen alle Orte in OÖ in einer Liste mit ihrer PLZ ausgegeben werden. Es sollen nur jene Orte aufgelistet werden, deren PLZ nicht mit einer 4 beginnt.
SELECT *
FROM PLZ
WHERE (PLZ.Region="O" AND PLZ.Plz<4000) OR (PLZ.Region="O" AND PLZ.Plz>4999)
19. Anzahl der Aufträge je Kunde, wobei nur jene mit eine Auftragsanzahl von größer als 3 angezeigt werden sollen:
SELECT Auftrag.Kundennummer, Kunde.Vorname, Kunde.Nachname, COUNT(*) AS Auftragsanzahl
FROM Auftrag, Kunde
WHERE Kunde.Nr=Auftrag.Kundennummer
GROUP BY Auftrag.Kundennummer, Kunde.Vorname, Kunde.Nachname
HAVING Auftragsanzahl>3
ORDER BY Auftragsanzahl DESC
20. Auswahl treffen:
21. Javascript:
1. Anzahl der Bücher je Verlag:
SELECT Verlag, COUNT(*) AS Buchanzahl
FROM Buch
GROUP BY Verlag
2. Maximaler, Minimaler, Durchschnittl. Buchpreis, Summe aller Buchpreise, Anzahl aller Bücher:
SELECT MAX(Buch.Preis), MIN(Buch.Preis), AVG(Buch.Preis), SUM(Buch.Preis), COUNT(Buch.Bestand)
FROM Buch
3. Titel, Verlag, Name (aus Tabelle Verlag) aller Bücher:
SELECT Buch.Titel, Buch.Verlag, Verlag.Name
FROM Verlag, Buch
WHERE Verlag.Kurzbezeichnung=Buch.Verlag
4. Nr, Auftrags-, Buchnummer, Menge, Preis, Preis*Menge (=Umsatz pro Buch), nur von der Auftragsnummer 1:
SELECT Auftragspos.Nr, Auftragspos.Auftragsnummer, Auftragspos.Buchnummer, Auftragspos.Menge, Buch.Preis, Buch.Preis*Auftragspos.Menge AS Umsatz_pro_Buch
FROM Buch, Auftragspos
WHERE Buch.Nr=Auftragspos.Buchnummer AND Auftragspos.Auftragsnummer=1
5. Auftragsnummer und Gesamteinkaufssumme der Auftragsnummer 1:
SELECT Auftragspos.Auftragsnummer, SUM(Buch.Preis*Auftragspos.Menge)
FROM Buch, Auftragspos
WHERE Buch.Nr=Auftragspos.Buchnummer AND Auftragspos.Auftragsnummer=1
GROUP BY Auftragspos.Auftragsnummer
6. Auftrags-, Kundennummer, Gesamteinkaufssumme, gruppieren nach Auftragsnummer, jene Datensätze mit Gesamteinkaufssumme > 20.000:
SELECT Auftragspos.Auftragsnummer, Auftrag.Kundennummer, SUM(Buch.Preis*Auftragspos.Menge) AS Summe
FROM Auftrag, Auftragspos, Buch
WHERE Auftrag.Nr=Auftragspos.Auftragsnummer AND Buch.Nr=Auftragspos.Buchnummer
GROUP BY Auftragspos.Auftragsnummer
HAVING Summe>20000
7. Alle bisher erfassten Aufträge sollen angezeigt werden, und zwar mit Auftragsnummer, Kundenname (Nachname) und Auftragssumme. Anzeige soll nach der Auftragssumme absteigend sortiert werden:
SELECT Auftragspos.Auftragsnummer, Kunde.Nachname, SUM(Buch.Preis) AS Auftragssumme
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr
GROUP BY Kunde.Nachname
ORDER BY Auftragssumme DESC
8. Es soll der Verlagsname und die Anzahl der Bücher die dieser Verlag veröffentlicht hat ausgegeben werden:
SELECT Verlag.Name, COUNT(Buch.Titel) AS Anzahl_der_Buecher
FROM Verlag, Buch
WHERE Verlag.Kurzbezeichnung=Buch.Verlag
GROUP BY Verlag.Name
9. Wie viele Kunden wohnen in welcher Region (gruppiert nach Region):
SELECT PLZ.Region, COUNT(Kunde.Nr) AS Anzahl_der_Kunden
FROM PLZ, Kunde
WHERE PLZ.Plz=Kunde.Plz
GROUP BY PLZ.Region
10. Gesamtwert aller Bücher in denen das Wort "bio" vorkomt? Anzeige: Summe:
SELECT SUM(Buch.Preis) AS Summe
FROM Buch
WHERE Buch.Titel LIKE "%bio%"
11. Anzahl der Chemiebücher je Verlag, absteigend gereiht nach Vorkommen:
SELECT COUNT(*) AS Vorkommen, Buch.Verlag
FROM Buch
WHERE Buch.Titel LIKE "%chemie%"
GROUP BY Buch.Verlag
ORDER BY Vorkommen
12. Zeigen Sie den Nachnamen und das Datum der Aufträge an die im Zeitraum 26.01.2000 bis 28.01.2000 getätigt wurden. Geordnet soll nach dem Nachnamen werden:
SELECT Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Datum BETWEEN "2000-01-26" AND "2000-01-28"
ORDER BY Kunde.Nachname
13. Alle Kunden auswählen die mit Vornamen Michael heißen und deren PLZ kleiner als 4030 ist:
SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Plz
FROM Kunde
WHERE Kunde.Vorname LIKE "Michael" AND Kunde.Plz<4030
ORDER BY Kunde.Nachname
14. Welche Kunden kommen aus Wien?
SELECT Kunde.Vorname, Kunde.Nachname, PLZ.Ort
FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz AND PLZ.Ort="Wien"
ORDER BY Kunde.Nachname
15. Wie viele Bücher vertreibt der Morawa Verlag und wie hoch ist die Bestellsumme, wenn jedes Buch dieses Verlages einmal bestellt wird?
SELECT SUM(Buch.Preis), COUNT(Buch.Nr)
FROM Buch
WHERE Buch.Verlag="mc"
16. Wie viel kostet im Durchschnitt ein Buch des Autors Arge und um wie viele handelt es sich hierbei?
SELECT AVG(Buch.Preis), COUNT(Buch.Nr)
FROM Buch
WHERE Buch.Autor="Arge"
17. Welcher Autor hat wie viele Bücher geschrieben? Sortiere absteigend nach der Bücheranzahl.
SELECT Buch.Autor, COUNT(*)
FROM Buch
GROUP BY Buch.Autor
ORDER BY 2 DESC
18. Es sollen alle Orte in OÖ in einer Liste mit ihrer PLZ ausgegeben werden. Es sollen nur jene Orte aufgelistet werden, deren PLZ nicht mit einer 4 beginnt.
SELECT *
FROM PLZ
WHERE (PLZ.Region="O" AND PLZ.Plz<4000) OR (PLZ.Region="O" AND PLZ.Plz>4999)
19. Anzahl der Aufträge je Kunde, wobei nur jene mit eine Auftragsanzahl von größer als 3 angezeigt werden sollen:
SELECT Auftrag.Kundennummer, Kunde.Vorname, Kunde.Nachname, COUNT(*) AS Auftragsanzahl
FROM Auftrag, Kunde
WHERE Kunde.Nr=Auftrag.Kundennummer
GROUP BY Auftrag.Kundennummer, Kunde.Vorname, Kunde.Nachname
HAVING Auftragsanzahl>3
ORDER BY Auftragsanzahl DESC
| Anzahl der Aufträge je Kunde |
20. Auswahl treffen:
| Bitte treffen Sie Ihre Auswahl: |
21. Javascript:
