Montag, 21. April 2008
Montag, 21. April 2008
4. Aufgabenstellung: SQL inkl. XML & DTD
Topic: 'Aufgabenstellungen IV 2'
Diese Aufgabenstellung beschäftigt sich wieder mit der Lehrdatenbank des Buchhandels. Bei dieser Aufgabenstellung geht es um die Verbindung von SQL mit XML und DTD.
Aufgrund meiner Matrikelnummer (0456032) und somit der Endziffer 2, beschäftige ich mich mit der 2. Aufgabe: Erfassung eines Verlages.
Zuerst werde ich zur Bewältigung der Aufgabe die SQL-Abfrage definieren bzw. den Ausgangspunkt um dann anschließend die XML-Datei und folglich die DTD-Erweiterung erstellen zu können. Hierbei sei anzumerken, dass ein Verlag in der Lehrdatenbank erfasst werden soll, aber kein bestehender.
1. SQL-Abfrage:
Für die folgenden Schritte gehe ich von der folgenden allgemeinen SQL-Abfrage aus bzw. ziehe ich für einen neuen Verlag den Linde Verlag in Wien aus. Bei dieser Abfrage ist ersichtlich, dass der Linde Verlag noch kein Bestandteil der Datenbank ist.
SELECT Verlag.Kurzbezeichnung, Verlag.Name, Verlag.PLZ, Verlag.Strasse, Verlag.Kundennummer
FROM Verlag
ORDER BY Verlag.Name
Anschrift:
LINDE VERLAG WIEN Ges.m.b.H.
Scheydgasse 24
1210 Wien
Kurzbezeichnung: lvw
Kundenummer: 10320
2. XML-Datei:
Bevor die XML-Datei bzw. die hierfür zu erstellende Sprache erwähnt wird, werden noch kurz die Voraussetzungen von XML, die es zu erfüllen gilt, erwähnt:
1. XML Deklaration muss immer vorhanden sein
2. Wurzelelement muss vorhanden sein
3. Richtige Schreibweise gilt es zu berücksichtigen
4. Attributwerte müssen im Gegensatz zu HTML unter Anführungszeichen gesetzt werden
5. Leere Elemente darf es nicht geben, d.h. ein schließender Tag muss immer vorhanden sein
6. Elementnamen gilt es zu berücksichtigen
7. Groß- und Kleinschreibung berücksichtigen
8. Kommentare sind möglich (Quelle: Vgl. XML-Einführung).
Folgender XML-Befehl wird für den Linde Verlag herangezogen:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Verlag>
Die Zahl 1.0 sagt aus, um welche XML-Version es sich handelt. Encoding wird verwendet um den Zeichensatz, mit dem das Dokument behandelt wird, zu setzen. In diesem Fall steht "ISO-8859-1" für den ISO-Zeichensatz für westeuropäische Sprachen.
Die damit verbundene XML-Datei kann folglich aufgerufen werden:
3. DTD-Erweiterung:
Auch wenn zahlreiche Regeln (siehe unter 2. XML-Datei: Aufzählungspunkte 1.-8.) zu berücksichtigen sind, so ist das Dokument noch nicht gültig. Ein Dokument gilt dann als gültig, wenn die verwendeten Elemente definiert sind. Weiters muss das XML auch wissen um welche Attribute bzw. welchen Inhalt es bei den Elementen geht. Hierfür verwendet man das oben bereits erwähnte DTD (Document Type Definition).
Grundsätzlich wird zwischen einer internen und einer externen (ausgelagerten) DTD unterschieden. Ausgehend von der Aufgabenstellung werde ich eine interne DTD verwenden, da diese bei kleinen Dokumenten angewandt wird sowie steht die interne DTD im selben XML-Dokument.
In Bezug auf das vorhergehende Beispiel ergibt sich folgender DTD-Befehl bzw. Schreibweise:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Verlag>
Die DTD reicht von <!DOCTYPE Verlag[ bis ]>. Dabei ist ersichtlich, dass der Name der DTD Verlag ist. Ein Element wird mit <!ELEMENT > beschrieben. Das Wurzelelement muss dabei immer als erstes definiert werden.
Weiters ist es wichtig, dass die richtige Reihenfolge der einzelnen Definitionen eingehalten wird. Die weiteren Elemente des Wurzelelementes werden von einer runde Klammer umschlossen und mittels Beistrichen getrennt. Mit #PCDATA erreicht man, dass der Inhalt des Elementes jeglicher Text oder Code sein darf, allerdings können bzw. dürfen hierbei keine weiteren Unterelemente eingefügt werden.
Die DTD-Erweiterung kann folglich aufgerufen werden:
Aufgrund meiner Matrikelnummer (0456032) und somit der Endziffer 2, beschäftige ich mich mit der 2. Aufgabe: Erfassung eines Verlages.
Zuerst werde ich zur Bewältigung der Aufgabe die SQL-Abfrage definieren bzw. den Ausgangspunkt um dann anschließend die XML-Datei und folglich die DTD-Erweiterung erstellen zu können. Hierbei sei anzumerken, dass ein Verlag in der Lehrdatenbank erfasst werden soll, aber kein bestehender.
1. SQL-Abfrage:
Für die folgenden Schritte gehe ich von der folgenden allgemeinen SQL-Abfrage aus bzw. ziehe ich für einen neuen Verlag den Linde Verlag in Wien aus. Bei dieser Abfrage ist ersichtlich, dass der Linde Verlag noch kein Bestandteil der Datenbank ist.
SELECT Verlag.Kurzbezeichnung, Verlag.Name, Verlag.PLZ, Verlag.Strasse, Verlag.Kundennummer
FROM Verlag
ORDER BY Verlag.Name
Anschrift:
LINDE VERLAG WIEN Ges.m.b.H.
Scheydgasse 24
1210 Wien
Kurzbezeichnung: lvw
Kundenummer: 10320
2. XML-Datei:
Bevor die XML-Datei bzw. die hierfür zu erstellende Sprache erwähnt wird, werden noch kurz die Voraussetzungen von XML, die es zu erfüllen gilt, erwähnt:
1. XML Deklaration muss immer vorhanden sein
2. Wurzelelement muss vorhanden sein
3. Richtige Schreibweise gilt es zu berücksichtigen
4. Attributwerte müssen im Gegensatz zu HTML unter Anführungszeichen gesetzt werden
5. Leere Elemente darf es nicht geben, d.h. ein schließender Tag muss immer vorhanden sein
6. Elementnamen gilt es zu berücksichtigen
7. Groß- und Kleinschreibung berücksichtigen
8. Kommentare sind möglich (Quelle: Vgl. XML-Einführung).
Folgender XML-Befehl wird für den Linde Verlag herangezogen:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<Verlag>
<Kurzbezeichnung>lvw</Kurzbezeichnung></Verlag>
<Name>LINDE VERLAG WIEN Ges.m.b.H.</Name>
<Kundennummer>10320</Kundennummer>
<Strasse>Scheydgasse 24</Strasse>
<Plz>1210</Plz>
Die Zahl 1.0 sagt aus, um welche XML-Version es sich handelt. Encoding wird verwendet um den Zeichensatz, mit dem das Dokument behandelt wird, zu setzen. In diesem Fall steht "ISO-8859-1" für den ISO-Zeichensatz für westeuropäische Sprachen.
Die damit verbundene XML-Datei kann folglich aufgerufen werden:
Neuer_Verlag
3. DTD-Erweiterung:
Auch wenn zahlreiche Regeln (siehe unter 2. XML-Datei: Aufzählungspunkte 1.-8.) zu berücksichtigen sind, so ist das Dokument noch nicht gültig. Ein Dokument gilt dann als gültig, wenn die verwendeten Elemente definiert sind. Weiters muss das XML auch wissen um welche Attribute bzw. welchen Inhalt es bei den Elementen geht. Hierfür verwendet man das oben bereits erwähnte DTD (Document Type Definition).
Grundsätzlich wird zwischen einer internen und einer externen (ausgelagerten) DTD unterschieden. Ausgehend von der Aufgabenstellung werde ich eine interne DTD verwenden, da diese bei kleinen Dokumenten angewandt wird sowie steht die interne DTD im selben XML-Dokument.
In Bezug auf das vorhergehende Beispiel ergibt sich folgender DTD-Befehl bzw. Schreibweise:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<!DOCTYPE Verlag []>
<!ELEMENT Verlag (Kurzbezeichnung, Name, Kundennummer, Strasse, Plz)>
<!ELEMENT Kurzbezeichnung (#PCDATA)>
<!ELEMENT Name (#PCDATA)>
<!ELEMENT Kundennummer (#PCDATA)>
<!ELEMENT Strasse (#PCDATA)>
<!ELEMENT Plz (#PCDATA)>
<!ELEMENT Kurzbezeichnung (#PCDATA)>
<Verlag>
<Kurzbezeichnung>lvw</Kurzbezeichnung></Verlag>
<Name>LINDE VERLAG WIEN Ges.m.b.H.</Name>
<Kundennummer>10320</Kundennummer>
<Strasse>Scheydgasse 24</Strasse>
<Plz>1210</Plz>
Die DTD reicht von <!DOCTYPE Verlag[ bis ]>. Dabei ist ersichtlich, dass der Name der DTD Verlag ist. Ein Element wird mit <!ELEMENT > beschrieben. Das Wurzelelement muss dabei immer als erstes definiert werden.
Weiters ist es wichtig, dass die richtige Reihenfolge der einzelnen Definitionen eingehalten wird. Die weiteren Elemente des Wurzelelementes werden von einer runde Klammer umschlossen und mittels Beistrichen getrennt. Mit #PCDATA erreicht man, dass der Inhalt des Elementes jeglicher Text oder Code sein darf, allerdings können bzw. dürfen hierbei keine weiteren Unterelemente eingefügt werden.
Die DTD-Erweiterung kann folglich aufgerufen werden:
Neuer_Verlag
Link
(0 Kommentare)
Kommentieren
Montag, 7. April 2008
Montag, 7. April 2008
3. Aufgabenstellung: Weitere SQL-Abfragen
Topic: 'Aufgabenstellungen IV 2'
Diese Aufgabenstellung beschäftigt sich wieder mit der Lehrdatenbank des Buchhandels. Ausgehend von dieser Abfrage
SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
soll schrittweise eine Auswertung erfolgen, die wertvolle betriebswirtschaftliche Aussagen produziert. Folgende Auswertungen wurden hierfür getätigt:
1. Abfrage: Umsatz je Kunde
Mit der folgenden Abfrage möchte man herausfinden wie hoch der Umsatz jedes Kunden ist.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Wie aus der Abfrage ersichtlich ist wurde der Befehl COUNT(DISTINCT Auftrag.Nr) eingeführt und nicht lediglich COUNT(Auftrag.Nr) zum Zählen der Aufträge verwendet. Der Grund hierfür ist, dass der Befehl COUNT(Auftrag.Nr) die Anzahl der Datensätze, in denen das Feld Auftrag.Nr einen Wert enthält, zurückgeben würde. Das bedeutet folglich, dass somit diese Zahl ohne den Befehl DISTINCT um einiges zunehmen muss. Führt man hingegen den Befehl COUNT(DISTINCT Auftrag.Nr) durch bzw. ein so entsteht das genannte Problem nicht und die Anzahl der Aufträge bleibt unverändert wie in der Ausgangssituation.
Ergebnis: Ersetzt man weiters den Befehl ORDER BY Kunde.Nr durch ORDER BY 4 DESC so wird ersichtlich, dass Kunde Erdl mit vier Aufträgen der umsatzstärkste Kunde (118.018,90) und Kunde Leupold mit einem Auftrag der umsatzschwächste Kunde (56,78) ist.
2. Abfrage: Verteilung der Aufträge über die Zeit
In einem weiteren Schritt soll die Verteilung der Aufträge über die Zeit angezeigt werden ohne die Anzahl der Aufträge bzw. die Auftragssummen zu ermitteln.
SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Die angeführte Abfrage zeigt die Verteilung der Aufträge über die Zeit hinweg geordnet nach der Kundennummer. Möchte man das Ganze nach dem Datum sortieren um zu sehen wie sich diese Verteilung chronologisch entwickelt hat, so muss man beim Befehl ORDER BY anstelle von Kunde.Nr die Zahl 3 hinschreiben um somit nach der dritten Spalte, nämlich dem Datum, zu sortieren. Wird dieser Befehl so stehen gelassen, so wird aufsteigend sortiert; mit dem Zusatzbefehl DESC kann zeitlich absteigend sortiert werden.
Ergebnis: Den aktuellsten Kunden stellt Hilgers dar (Datum: 2001-01-17) und der am wenigsten aktuellste Kunde lautet Steinmans (2000-01-03).
Möchte man hingegen bei der Verteilung über die Zeit lediglich die Jahre anzeigen, so kann man sich folgender SQL-Abfrage bedienen:
SELECT Kunde.Nr, Kunde.Nachname, Year(Auftrag.Datum)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
3. Abfrage: Eingrenzen der 1. Abfrage in Zeitabschnitte
Die Aufgabenstellung lautet, dass die 1. Abfrage in sinnvolle Zeitabschnitte eingegrenzt werden soll. Aus betriebswirtschaftlicher Sicht wäre es vielleicht interessant zu sehen, welche Entwicklung die Kunden im vierteljährlichen Gesichtspunkt gemacht haben. Da die Aufträge vom 1. Jänner 2000 bis zum 17. Jänner 2001 reichen werden im Jahr 2000 vier Abfragen und im Jahr 2001 eine Abfrage nötig sein.
Der Form halber soll hier nur eine Abfrage exemplarisch für die anderen vier Abfragen dargestellt werden, da diese aus Platzgründen zu umfangreich wären und sich immer nur ein Befehl ändert. Dieser sich vierteljährlich verändernde Befehl wird natürlich kurz dargestellt.
Die folgende Abfrage zeigt mir alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.1.2000 bis 31.3.2000 befinden.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, Auftrag.Datum
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr AND Auftrag.Datum >= "2000-01-01" AND Auftrag.Datum <= "2000-03-31"
GROUP BY Kunde.Nr
ORDER BY 5
Wird das Datum nun mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.4.2000 bis 30.6.2000 befinden.
Auftrag.Datum >= "2000-04-01" AND Auftrag.Datum <= "2000-06-30"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.7.2000 bis 30.9.2000 befinden.
Auftrag.Datum >= "2000-07-01" AND Auftrag.Datum <= "2000-09-30"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.10.2000 bis 31.12.2000 befinden.
Auftrag.Datum >= "2000-10-01" AND Auftrag.Datum <= "2000-12-31"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.1.2001 bis 31.3.2001 befinden.
Auftrag.Datum >= "2001-01-01" AND Auftrag.Datum <= "2001-03-31"
(Alle übrigen Paramater bleiben gleich)
4. Abfrage: Ergänzen der 3. Abfrage um einen Filter
In dieser Abfrage sollen nun die zeitlich abgegrenzten Auswertungen um einen weiteren Filter ergänzt werden, der sich an der durchschnittlichen Anzahl der Aufträge orientiert.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, Auftrag.Datum, AVG(Auftragspos.Menge) AS "Durchschnittl. Anzahl der Aufträge"
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr AND Auftrag.Datum >= "2000-01-01" AND Auftrag.Datum <= "2000-03-31"
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Mit dem eingeführten Filter AVG(Auftragspos.Menge) lässt sich somit die durchschnittliche Anzahl der Aufträge darstellen. Hierbei wurden in dieser Abfrage wiederrum jene Kunden des 1. Quartals 2000 ausgewählt. Die Kunden der übrigen Quartale können durch das Austauschen der Auftrag.Datum und den entsprechenden Datumseingrenzungen (siehe 3. Abfrage) geändert und abgefragt werden.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(Auftrag.Nr)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
soll schrittweise eine Auswertung erfolgen, die wertvolle betriebswirtschaftliche Aussagen produziert. Folgende Auswertungen wurden hierfür getätigt:
1. Abfrage: Umsatz je Kunde
Mit der folgenden Abfrage möchte man herausfinden wie hoch der Umsatz jedes Kunden ist.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Wie aus der Abfrage ersichtlich ist wurde der Befehl COUNT(DISTINCT Auftrag.Nr) eingeführt und nicht lediglich COUNT(Auftrag.Nr) zum Zählen der Aufträge verwendet. Der Grund hierfür ist, dass der Befehl COUNT(Auftrag.Nr) die Anzahl der Datensätze, in denen das Feld Auftrag.Nr einen Wert enthält, zurückgeben würde. Das bedeutet folglich, dass somit diese Zahl ohne den Befehl DISTINCT um einiges zunehmen muss. Führt man hingegen den Befehl COUNT(DISTINCT Auftrag.Nr) durch bzw. ein so entsteht das genannte Problem nicht und die Anzahl der Aufträge bleibt unverändert wie in der Ausgangssituation.
Ergebnis: Ersetzt man weiters den Befehl ORDER BY Kunde.Nr durch ORDER BY 4 DESC so wird ersichtlich, dass Kunde Erdl mit vier Aufträgen der umsatzstärkste Kunde (118.018,90) und Kunde Leupold mit einem Auftrag der umsatzschwächste Kunde (56,78) ist.
2. Abfrage: Verteilung der Aufträge über die Zeit
In einem weiteren Schritt soll die Verteilung der Aufträge über die Zeit angezeigt werden ohne die Anzahl der Aufträge bzw. die Auftragssummen zu ermitteln.
SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Die angeführte Abfrage zeigt die Verteilung der Aufträge über die Zeit hinweg geordnet nach der Kundennummer. Möchte man das Ganze nach dem Datum sortieren um zu sehen wie sich diese Verteilung chronologisch entwickelt hat, so muss man beim Befehl ORDER BY anstelle von Kunde.Nr die Zahl 3 hinschreiben um somit nach der dritten Spalte, nämlich dem Datum, zu sortieren. Wird dieser Befehl so stehen gelassen, so wird aufsteigend sortiert; mit dem Zusatzbefehl DESC kann zeitlich absteigend sortiert werden.
Ergebnis: Den aktuellsten Kunden stellt Hilgers dar (Datum: 2001-01-17) und der am wenigsten aktuellste Kunde lautet Steinmans (2000-01-03).
Möchte man hingegen bei der Verteilung über die Zeit lediglich die Jahre anzeigen, so kann man sich folgender SQL-Abfrage bedienen:
SELECT Kunde.Nr, Kunde.Nachname, Year(Auftrag.Datum)
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
3. Abfrage: Eingrenzen der 1. Abfrage in Zeitabschnitte
Die Aufgabenstellung lautet, dass die 1. Abfrage in sinnvolle Zeitabschnitte eingegrenzt werden soll. Aus betriebswirtschaftlicher Sicht wäre es vielleicht interessant zu sehen, welche Entwicklung die Kunden im vierteljährlichen Gesichtspunkt gemacht haben. Da die Aufträge vom 1. Jänner 2000 bis zum 17. Jänner 2001 reichen werden im Jahr 2000 vier Abfragen und im Jahr 2001 eine Abfrage nötig sein.
Der Form halber soll hier nur eine Abfrage exemplarisch für die anderen vier Abfragen dargestellt werden, da diese aus Platzgründen zu umfangreich wären und sich immer nur ein Befehl ändert. Dieser sich vierteljährlich verändernde Befehl wird natürlich kurz dargestellt.
Die folgende Abfrage zeigt mir alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.1.2000 bis 31.3.2000 befinden.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, Auftrag.Datum
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr AND Auftrag.Datum >= "2000-01-01" AND Auftrag.Datum <= "2000-03-31"
GROUP BY Kunde.Nr
ORDER BY 5
Wird das Datum nun mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.4.2000 bis 30.6.2000 befinden.
Auftrag.Datum >= "2000-04-01" AND Auftrag.Datum <= "2000-06-30"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.7.2000 bis 30.9.2000 befinden.
Auftrag.Datum >= "2000-07-01" AND Auftrag.Datum <= "2000-09-30"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.10.2000 bis 31.12.2000 befinden.
Auftrag.Datum >= "2000-10-01" AND Auftrag.Datum <= "2000-12-31"
(Alle übrigen Paramater bleiben gleich)
Wird das Datum mit dem nachstehenden Befehl geändert, so erhält man alle Kunden (chronologisch aufsteigend sortiert) die sich im Zeitraum 1.1.2001 bis 31.3.2001 befinden.
Auftrag.Datum >= "2001-01-01" AND Auftrag.Datum <= "2001-03-31"
(Alle übrigen Paramater bleiben gleich)
4. Abfrage: Ergänzen der 3. Abfrage um einen Filter
In dieser Abfrage sollen nun die zeitlich abgegrenzten Auswertungen um einen weiteren Filter ergänzt werden, der sich an der durchschnittlichen Anzahl der Aufträge orientiert.
SELECT Kunde.Nr, Kunde.Nachname, COUNT(DISTINCT Auftrag.Nr) AS "Anzahl der Aufträge", SUM(Buch.Preis*Auftragspos.Menge) AS Umsatz, Auftrag.Datum, AVG(Auftragspos.Menge) AS "Durchschnittl. Anzahl der Aufträge"
FROM Kunde, Auftrag, Auftragspos, Buch
WHERE Kunde.Nr = Auftrag.Kundennummer AND Auftrag.Nr = Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer = Buch.Nr AND Auftrag.Datum >= "2000-01-01" AND Auftrag.Datum <= "2000-03-31"
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
Mit dem eingeführten Filter AVG(Auftragspos.Menge) lässt sich somit die durchschnittliche Anzahl der Aufträge darstellen. Hierbei wurden in dieser Abfrage wiederrum jene Kunden des 1. Quartals 2000 ausgewählt. Die Kunden der übrigen Quartale können durch das Austauschen der Auftrag.Datum und den entsprechenden Datumseingrenzungen (siehe 3. Abfrage) geändert und abgefragt werden.
Montag, 31. März 2008
Montag, 31. März 2008
Aufgabenstellung: SQL-Kenntnisse
Topic: 'Aufgabenstellungen IV 2'
Zur Durchführung von mindestens fünf SQL-Abfragen beziehe ich mich auf die Lehrdatenbank Buchhandel, speziell auf den Kundenbereich.
1. Abfrage
Mittels folgender Abfrage sollen alle Kunden unserer Buchhandelsdatenbank angezeigt werden:
SELECT * FROM Kunde Die Abfrage liefert kein eindeutiges Ergebnis, da sich zwar 999 Kunden in der Datenbank befinden (siehe 4. Abfrage), aber aufgrund eines automatisch festgelegten Limits von 500 möglich anzuzeigenden Kunden, nicht alle Kunden abgebildet werden können.
2. Abfrage
In einer weiteren Abfrage werden mehrere Auswahlmöglichkeiten miteinander kombiniert. Mit den folgenden Befehlen ist es möglich sich entweder alle Kunden, alle Verlage oder alle Bücher der Buchhandelsdatenbank anzeigen zu lassen:
SELECT * FROM Kunde
SELECT * FROM Verlag
SELECT * FROM Buch
3. Abfrage
Hinsichtlich der ersten Abfrage (Auswahl aller Kunden) soll dieser Befehl eine Hilfestellung zur leichteren Kundensuche, z.B. bezogen auf den Vornamen, ermöglichen:
SELECT * FROM Kunde
WHERE Vorname like '%Hier Vornamen einfügen%'
4. Abfrage
Als weiteren Schritt bzw. zur Verfeinerung der Ergebnisse liefert die nächste Abfrage die Anzahl der Kunden der einzelnen Bundesländer:
SELECT Region, COUNT(Kunde.Nr) AS "Kundenanzahl"
FROM PLZ, Kunde WHERE PLZ.Plz = Kunde.Plz
GROUP BY Region ORDER BY 2 desc
Wie aus dem Ergebnis ersichtlich ist, liegt Salzburg mit 160 Kunden an der Spitze der Datenbank. Das Schlusslicht bildet Vorarlberg mit insgesamt 49 Kunden.
5. Abfrage
Mit folgendem Befehl werden alle Kunden inklusive deren Kundennummern sowie Nachnamen, z.B. aus dem Bundesland Oberösterreich, dargestellt:
SELECT Kunde.Nr, Kunde.Vorname, Kunde.Nachname,
PLZ.Region FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz AND Region='O'
Diese Abfrage liefert uns nun die genaue Aufstellung aller 137 Kunden aus Oberösterreich.
6. Abfrage
Zum Abschluss der Analyse des Buchhandels werden noch zwei Umsatzbezogene Abfragen wiedergegeben.
In der ersten dieser beiden Abfragen soll dargestellt werden, wer die Umsatzstärksten Kunden des Buchhandels sind:
SELECT Kundennummer, Vorname, Nachname, SUM
(Menge*Preis) AS Gesamtsumme FROM
Kunde,Auftrag,Auftragspos,Buch WHERE Kunde.Nr =
Auftrag.Kundennummer AND Auftrag.Nr =
Auftragsnummer AND Buchnummer = Buch.Nr
GROUP BY Kunde.Nr ORDER BY Gesamtsumme DESC
Wie aus dem Resultat ersichtlich ist, stellen Herr Kurt Erdl, Frau Vera Kremer sowie Frau Ruth Schmitz die Umsatzstärksten Kunden des Buchhandels dar.
7. Abfrage
Zu guter letzt soll dem Abfrager auch die Möglichkeit geboten werden, die Umastzstärksten Kunden, je nach Bundesland sortiert, z.B. für Burgenland, ermitteln zu können:
SELECT k.Nachname, k.Vorname, k.Nr, p.Region, SUM
(b.Preis*au.Menge)AS Gesamtumsatz FROM PLZ p,
Kunde k, Auftrag a, Auftragspos au, Buch b WHERE
p.Plz=k.Plz AND k.Nr=a.Kundennummer AND
a.Nr=au.Auftragsnummer AND au.Buchnummer=b.Nr
AND p.Region ="B" GROUP BY k.Nr ORDER BY 5 desc
1. Abfrage
Mittels folgender Abfrage sollen alle Kunden unserer Buchhandelsdatenbank angezeigt werden:
SELECT * FROM Kunde Die Abfrage liefert kein eindeutiges Ergebnis, da sich zwar 999 Kunden in der Datenbank befinden (siehe 4. Abfrage), aber aufgrund eines automatisch festgelegten Limits von 500 möglich anzuzeigenden Kunden, nicht alle Kunden abgebildet werden können.
2. Abfrage
In einer weiteren Abfrage werden mehrere Auswahlmöglichkeiten miteinander kombiniert. Mit den folgenden Befehlen ist es möglich sich entweder alle Kunden, alle Verlage oder alle Bücher der Buchhandelsdatenbank anzeigen zu lassen:
SELECT * FROM Kunde
SELECT * FROM Verlag
SELECT * FROM Buch
3. Abfrage
Hinsichtlich der ersten Abfrage (Auswahl aller Kunden) soll dieser Befehl eine Hilfestellung zur leichteren Kundensuche, z.B. bezogen auf den Vornamen, ermöglichen:
SELECT * FROM Kunde
WHERE Vorname like '%Hier Vornamen einfügen%'
4. Abfrage
Als weiteren Schritt bzw. zur Verfeinerung der Ergebnisse liefert die nächste Abfrage die Anzahl der Kunden der einzelnen Bundesländer:
SELECT Region, COUNT(Kunde.Nr) AS "Kundenanzahl"
FROM PLZ, Kunde WHERE PLZ.Plz = Kunde.Plz
GROUP BY Region ORDER BY 2 desc
Wie aus dem Ergebnis ersichtlich ist, liegt Salzburg mit 160 Kunden an der Spitze der Datenbank. Das Schlusslicht bildet Vorarlberg mit insgesamt 49 Kunden.
5. Abfrage
Mit folgendem Befehl werden alle Kunden inklusive deren Kundennummern sowie Nachnamen, z.B. aus dem Bundesland Oberösterreich, dargestellt:
SELECT Kunde.Nr, Kunde.Vorname, Kunde.Nachname,
PLZ.Region FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz AND Region='O'
Diese Abfrage liefert uns nun die genaue Aufstellung aller 137 Kunden aus Oberösterreich.
6. Abfrage
Zum Abschluss der Analyse des Buchhandels werden noch zwei Umsatzbezogene Abfragen wiedergegeben.
In der ersten dieser beiden Abfragen soll dargestellt werden, wer die Umsatzstärksten Kunden des Buchhandels sind:
SELECT Kundennummer, Vorname, Nachname, SUM
(Menge*Preis) AS Gesamtsumme FROM
Kunde,Auftrag,Auftragspos,Buch WHERE Kunde.Nr =
Auftrag.Kundennummer AND Auftrag.Nr =
Auftragsnummer AND Buchnummer = Buch.Nr
GROUP BY Kunde.Nr ORDER BY Gesamtsumme DESC
Wie aus dem Resultat ersichtlich ist, stellen Herr Kurt Erdl, Frau Vera Kremer sowie Frau Ruth Schmitz die Umsatzstärksten Kunden des Buchhandels dar.
7. Abfrage
Zu guter letzt soll dem Abfrager auch die Möglichkeit geboten werden, die Umastzstärksten Kunden, je nach Bundesland sortiert, z.B. für Burgenland, ermitteln zu können:
SELECT k.Nachname, k.Vorname, k.Nr, p.Region, SUM
(b.Preis*au.Menge)AS Gesamtumsatz FROM PLZ p,
Kunde k, Auftrag a, Auftragspos au, Buch b WHERE
p.Plz=k.Plz AND k.Nr=a.Kundennummer AND
a.Nr=au.Auftragsnummer AND au.Buchnummer=b.Nr
AND p.Region ="B" GROUP BY k.Nr ORDER BY 5 desc
