Dieses Tutorium war dazu da uns zu zeigen wie man Ergebnisse einer SQL Abfrage ins Excel importieren kann, sprich wie man es dort dann weiter verarbeiten kann.

Die genaue Anleitung wie dieses funktioniert findet ihr im Tutoriumsblog.

Hier ein Bsp. einer Abfrage, eingebunden im Excel, das im Tutorium durchgemacht wurde:

SELECT Kunde.Nr, Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.PLZ, 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 AND Kunde.Vorname LIKE "M%"
GROUP BY Kunde.Nr

sql_bsp_in_excel (xls, 46 KB)

Greifen Sie aus der, in der Lehrveranstaltung zum Thema "eBusiness" behandelten Literatur (30. April bis 21. Mai) Aussagen heraus, die Ihrer Meinung nach Empfehlungscharakter für die Umgestaltung des Modell-Lehrbuchhandels in Richtung eBusiness haben. Dokumentieren und zitieren Sie diese Aussagen in Ihrem Lernblog und beschreiben Sie, wie die ausgewählten Aussagen (Empfehlungen) auf den Modell-Lehrbuchhandel umgesetzt werden sollten!

Bevor ich mich mit den Umstrukturierungen auseinandersetze bin ich zur folgender Grundüberlegung gekommen:

Brick and Mortar Modell vs. Click and Mortar Modell:

Das derzeitige Geschäftsmodell stellt ein Brick and Mortar-Modell dar. Unser Schulbuchunternehmen kauft bei den Verlagen Bücher ein und zwischenlagern diese bei uns ein im Lager. Kunden wie Schulen kommen zu uns ins Unternehmen und machen ihre Bestellung. Die Auswahl werden wir dann zusammenstellen und lieferbereit machen. Der Kunde bekommt die Ware durch Zustellung oder durch eigene Abholung. Eine solche Bestellung ist natürlich auch über andere Funktionen möglich: z.B. Fax, Telefon,...

->Anwendung des neuen click and mortar Modells könnte folgende Verbesserung für unser Geschäftsmodell bedeuten:

Um eine solche Umwandlung zu schaffen müssen eine Menge von Umstrukturierungen vorgenommen werden. Die Bestellungen von Kunden und Lieferanten soll durch E-Business-Werkzeuge vereinfacht werden. Just in Time System soll Lieferzeiten vom Lieferanten verkürzen und den Lagerbestand so klein wie möglich halten (da ein höherer Lagerbestand automatisch höhere Kosten bedeuten würde). Bestellungen sollen überwiegend über unsere Website laufen. Nur mehr in Ausnahmefällen soll auf Telefon und Fax zurückgegriffen werden. Dennoch soll dem Kunden weiterhin auch die Möglichkeit angeboten werden in unser Unternehmen persönlich kommen zu können. Dies dient auch dem Vertrauenscharakter. Manche Kunden vertrauen halt doch lieber noch einen alten brick and mortar Modell als der neuen Generation click and mortar.

Nun hätte ich folgende Vorschläge für unseren Schulbuchhandel:


**Gestaltung einer Website im Internet:

Mit der Gestaltung einer entsprechenden Website für den Kunden tun sich ganz andere Dimensionen von Marketing auf. Natürlich ist aber nicht nur die Erstellung einer Homepage von Nöten, es müssen vor allem die verschiedensten Datenbanken (unsere, die der Lieferanten, und die der Kunden) miteinander verbunden werden.

->Anwendung auf unser Geschäftsmodell:

Die Website soll so gestaltet werden das der Kunde Infos über Angebot, Preise, Beständen, Lieferzeit usw. durch einfaches anklicken erhalten kann. Unsere Sql Datenbank sollte also unbedingt so eingebracht werden das es ohne Probleme aktuelle Daten "auswerfen" kann. Um den Kunden ein problemfreies Bestellen per Internet zu ermöglichen ist solch eine Umsetzung unabkömmlich.

**Bezahlung
Damit das ganze auch funktioniert und Anklang findet wäre es wichtig eine richtige Zahlungsweise zu wählen und diese auch problemfrei durchführen zu können. Bei Problemen würde der Kunde schnell abgeschreckt werden und somit vl. nicht mehr gewillt sein online zu bestellen bzw. zu bezahlen.

Zusätzliche Angebote:

**Auszug aus einem Buch/Leseprobe für die Kunden:

Durch die Möglichkeit sich kurze Auszüge aus den Büchern herunterzuladen, ist der Kunde in der Lage sich schon mal einen Überblick über das Buch zu verschaffen. Somit erspart man sich wiederum immense Kosten und Zeit als wenn man sich das Buch erst zum testen schicken lassen muss.

**Foren auf dieser Website:

->Anwendung:

Diese Foren sollten so einfach wie möglich zu bedienen sein damit die Kunden positive sowohl auch negative Erfahrungen über Schulbücher schnell austauschen können. Somit wäre es nicht mehr notwendig von Geschäft zu Geschäft zu laufen, jedoch liegt auch die Konkurrenz jetzt viel näher als zuvor. Der Kunde ist mit ein paar Klicks bei der Konkurrenz. Früher hat man vielleicht weitere Wege mit dem Auto zurücklegen müssen. Somit war die Konkurrenz früher viel weiter entfernt. Heute ist das jedoch durch das click and mortar Modell anders.

Diese Foren würde ich aber verschlüsseln damit die Infos nicht für die Konkurrenten sichtbar sind.

Eine zusätzliche Form um die Kunden über neue Bücher zu informieren wäre ein so genannter Newsletter:

Ein Newsletter hätte den Sinn via E-Mail Neuheiten oder besondere Angebote an die Schulen senden zu können. Somit ist man immer auf dem aktuellsten Stand.

-------------------------------------------
Soweit meine Gedanken und Empfehlungen für die Umgestaltung des Modell-Lehrbuchhandels.

java script:

Auftragssuche nach Kundennummer

Im 2.ten Tutorium am 15.04.08 konnte ich aufgrund von Krankheit nicht anwesend sein...deshalb verlinke ich hier auf den Tutoriums Blog um sich einen Überblick über den Inhalt zu verschaffen.

Diese Aufgabe beschäftigt sich mit der Zusammenführung von SQL/XLM und DTD.
Da meine Matrikelnummer auf 5 endet muss ich folgende Aufgabe erledigen:

Einen Kunden (beliebig wählen) und seine Aufträge auflisten:

Den folgenden Arbeitsverlauf teile ich in 4 Teilschritte ein:

Teilschritt 1)
Ich wähle einen beliebigen Kunden aus:mit folgender Abfrage bekomme ich jene Kunden inkl. relevanter Daten deren Nachname Huber ist.

Select Kunde.Nr, Kunde.Nachname, Kunde.Vorname, Kunde.Plz, Kunde.Strasse, Auftrag.Nr, Auftrag.Datum, Auftrag.Kundennummer From Kunde, Auftrag Where Kunde.Nr=Auftrag.Kundennummer And Kunde.Nachname="huber"

--------------------------
Teilschritt 2)
Ich wähle Olaf Huber aus.
Mit folgenden SQL-Befehl komme ich auf diese XML- Stuktur

<?xml version="1.0" encoding="iso-8859-1"?>
<kunde>
	<name>
		<vorname>olaf</vorname>
		<nachname>huber</nachname>
	</name>
	<adresse>
		<strasse>puchham 17</strasse>
		<plz>4040</plz>
	</adresse>
	<auftrag>
		<auftragsnummer>528</auftragsnummer>
		<auftragsdatum>05.07.2000</auftragsdatum>
		<kundennummer>20</kundennummer>
	</auftrag>
	<auftrag>
		<auftragsnummer>837</auftragsnummer>
		<auftragsdatum>08.11.2000</auftragsdatum>
		<kundennummer>20</kundennummer>
	</auftrag>
</kunde>

für diese Struktur habe ich den Dreamweaver verwendet...

hier noch mal extra abrufbar:
kunde.info (info, 0 KB)

--------------------------
Teilschritt 3)
Aus der oben dargestellten XML Struktur ergibt sich folgende DTD Struktur:

<!DOCTYPE Kunde [ 
<!ELEMENT Kunde (Nachname, Vorname, Strasse, Plz)>
<!ELEMENT Nachname (#PCDATA)>
<!ELEMENT Vorname (#PCDATA)>
<!ELEMENT Strasse (#PCDATA)>
<!ELEMENT Plz (#PCDATA)>
<!ELEMENT Auftrag (Auftragsnummer, Auftragsdatum, Kundennummer)>
<!ELEMENT Auftragsnummer (#PCDATA)>
<!ELEMENT Auftragsdatum (#PCDATA)>
<!ELEMENT Kundennummer (#PCDATA)>

]>

--------------------------
Teilschritt 4)
Schließlich führe ich XML und DTD zusammen:

<?xml version="1.0" encoding="iso-8859-1"?>


	<!DOCTYPE Kunde [ 
	<!ELEMENT Kunde (Nachname, Vorname, Strasse, Plz)>
	<!ELEMENT Nachname (#PCDATA)>
	<!ELEMENT Vorname (#PCDATA)>
	<!ELEMENT Strasse (#PCDATA)>
	<!ELEMENT Plz (#PCDATA)>
	<!ELEMENT Auftrag (Auftragsnummer, Auftragsdatum, Kundennummer)>
	<!ELEMENT Auftragsnummer (#PCDATA)>
	<!ELEMENT Auftragsdatum (#PCDATA)>
	<!ELEMENT Kundennummer (#PCDATA)>

]>

<kunde>
	<name>
		<vorname>olaf</vorname>
		<nachname>huber</nachname>
	</name>
	<adresse>
		<strasse>puchham 17</strasse>
		<plz>4040</plz>
	</adresse>
	<auftrag>
		<auftragsnummer>528</auftragsnummer>
		<auftragsdatum>05.07.2000</auftragsdatum>
		<kundennummer>20</kundennummer>
	</auftrag>
	<auftrag>
		<auftragsnummer>837</auftragsnummer>
		<auftragsdatum>08.11.2000</auftragsdatum>
		<kundennummer>20</kundennummer>
	</auftrag>
</kunde>

Das ganze ist hier abrufbar: kunde.info mit dtd (info mit dtd, 1 KB)
---------------------------------------------------
Da ich nicht wirklich eine Ahnung hatte wie ich XML und DTD zusammenführen sollte war ich ein wenig ratlos bin aber dann auf folgenden Link gestoßen:
XML/DTD

Hiermit stelle ich meine Lösung der Tutoriums Hausübung zur Verfügung: tutorium_excel_hue_1 (xls, 138 KB)

Ausgehend von folgender 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

habe ich schrittweise eine Auswertung entwickelt, die hoffentlich betriebswirtschaftlich wertvolle Aussagen produziert.

Meine Lösungen:

Teilschritt 1)
Ergänzen sie obenstehende Abfrage um den Umsatz (Preis * Menge) je Kunde. Achten Sie darauf, dass die Anzahl der getätigten Aufträge "COUNT(Auftrag.Nr)" korrekt ist, bzw. im Vergleich zur Ausgangssituation unverändert:

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

---> durch diesen Befehl erhalte ich also den Umsatz jedes Kunden. Durch die Erweiterung DISTINCT erreiche ich das die Anzahl der Aufträge unverändert bleibt.
Wenn ich nun wissen will welcher Kunde den stärksten Umsatz hat setzte ich anstelle von ORDER BY Kunde.Nr ---> ORDER BY Umsatz DESC ein.
Somit komme ich zu dem Schluss das der Kunde ERDL mit der Kundennr. 14 und 4 Aufträgen den stärksten Umsatz von 118018.9 aufweist.

Teilschritt 2)
Ermitteln Sie in einem Weiteren Schritt die Verteilung der Aufträge über die Zeit (Jahre), OHNE dass dabei Auswertungen (Anzahl der Aufträge bzw. Auftragssummen) ermittelt werden:

SELECT Kunde.Nr, Kunde.Nachname, Auftrag.Datum
FROM Kunde, Auftrag
WHERE Kunde.Nr = Auftrag.Kundennummer
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Man kann hier natürlich auch nach anderen Kriterien ordnen (z.b: Auftragsdatum: ORDER BY Auftrag.Datum)

Teilschritt 3)
Grenzen Sie die Abfrage 1 in sinnvolle Zeitabschnitte ein:

Die Aufträge reichen vom 1. Jänner 2000 bis zum 17. Jänner 2001.
Meine Berechnung führe ich im Jahr 2000 durch indem ich das Jahr in 2 Jahreshälften einteile:

Mein Befehl ändert sich dahin gehend das ich bei SELECT den Schritt Auftrag.Datum einfüge und bei der Bedingung WHERE die Zeitspanne erweitere (1.Halbjahr: 1.1.00-31.6.00):

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-06-31"
GROUP BY Kunde.Nr
ORDER BY Auftrag.Datum

Somit bekomme ich ein Ergebnis das sich über alle Kunden im Zeitraum zw. 1.1.2000-31.6.2000 erstreckt.

Das 2.te Halbjahr 2000 wird folgendermaßen berechnet:


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-07-01" AND Auftrag.Datum <= "2000-12-31"
GROUP BY Kunde.Nr
ORDER BY Auftrag.Datum

Somit erhalte ich alle Kunden im Zeitraum 1.7.2000-31.12.2000.


Teilschritt 4)
Ergänzen Sie die zeitlich abgegrenzten Auswertungen um einen weiteren Filter, der sich an der durchschnittlichen Anzahl der Aufträge orientiert:

Ich erweitere die Abfrage um den Filter AVG(Auftragspos.Nr) AS "Durchschnittl. Anzahl der Aufträge" und wähle als Datum das 1.Halbjahr(1.1.2001-31.6.2001) aus:

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.Nr) 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 >= "2001-01-01" AND Auftrag.Datum <= "2001-06-31"
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr
-------------------------------------------------
Nach einigen zahlreichen Überlegungen aufgrund von anderen Kommentaren und Meinungen meiner Kollegen hab ich nun den Teilschritt 4 noch mal überarbeitet und bin zu folgender Lösung gekommen:

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-06-31"
GROUP BY Kunde.Nr
ORDER BY Kunde.Nr

Um die durchschnittliche Anzahl an Aufträgen für das 1.Halbjahr(1.1.2000-31.6.2000) zu berechnen habe ich nun den Filter AVG(Auftragspos.Menge) AS "Durchschnittl. Anzahl der Aufträge" eingefügt und nicht wie zuvor AVG(Auftragspos.Nr)AS "Durchschnittl. Anzahl der Aufträge".

Im ersten IV 2 Tutorium ging es um das Thema SQL sprich es wurden auf Anfragen der Studentinnen nochmal alle grundlegenden Kenntnisse durchgearbeitet.

Um richtige Ergebnisse zu erzielen braucht man diese Begriffe bei denen auch die Reihenfolge eingehalten werden muss:

SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING(in Verbindung mit GROUP BY)

zur Ergänzung sind noch folgende Begriffe relevant:
COUNT, AND, OR, BETWEEN, LIKE, SUM, MAX, MIN, AVG, AS, DISTINCT, ASC, DESC

Eine genaue Erklärung zur Anwendung findet ihr auf dem Weblog von Patrick Führer.

Einige Beispiele wurden im Tutorium bearbeitet:Hier nur ein Auszug:

1)
Welche Kunden kommen aus Wien?

SELECT Kunde.Vorname, Kunde.Nachname, PLZ.Ort FROM Kunde, PLZ WHERE Kunde.Plz=PLZ.Plz AND PLZ.Ort LIKE "Wien" GROUP BY 1, 2

2)
Wie viele Bücher vertreibt der Morawa Verlag und wie hoch ist die Bestellsumme, wenn jedes Buch dieses Verlages einmal bestellt wird?

SELECT SUM(b.Preis), COUNT(b.Nr)
FROM Buch b
WHERE b.Verlag="mc"

3)
Wieviel kostet im Durchschnitt ein
Buch des Autors Arge?

SELECT AVG(b.Preis), COUNT(b.Nr)
FROM Buch b
WHERE b.Autor="Arge"

5)
Maximaler Buchpreis, minimaler Buchpreis, durchschnittlicher Buchpreis, Summe
aller Buchpreise, Anzahl aller Bücher?

SELECT MAX(Preis), MIN(Preis), AVG(Preis), COUNT(Bestand), SUM(Preis)
FROM Buch

Verknüpfte Abfragen:

1)
Titel, Verlag, Name (=> aus Tabelle Verlag) aller Bücher?

SELECT b.Titel, b.Verlag, v.Name
FROM Buch b, Verlag v
WHERE b.Verlag = v.Kurzbezeichnung

3)
Nr, Auftragsnummer, Buchnummer, Menge, Preis, Preis * Menge (also Umsatz pro
Buch) nur von der Auftragsnummer 1.

SELECT a.Nr, a.Auftragsnummer, a.Buchnummer, a.Menge, b.Preis, a.Menge*b.Preis
FROM Auftragspos a, Buch b
WHERE (a.Auftragsnummer = 1) AND (a.Buchnummer=b.Nr)

5)
Auftragsnummer, Kundennummer, Gesamteinkaufssumme (also SUM[Preis*Menge])
mit Beschriftung „Summe“; das Ganze gruppiert nach Auftragsnummer und nur mit
jenen Datensätzen mit einer Gesamteinkaufssumme > 20.000

SELECT a.Auftragsnummer, aa.Kundennummer, SUM(b.Preis*a.Menge) AS Summe
FROM Auftragspos a, Auftrag aa, Buch b
WHERE (a.Auftragsnummer=aa.Nr) AND (b.Nr=a.Buchnummer)
GROUP BY a.Auftragsnummer
HAVING Summe>20000

Mit diese Aufgabe habe ich meine SQL-Kenntnisse nocheinmal wiederholt. Es waren mindestens fünf Abfragen zu bearbeiten, mit steigendem Schwierigkeitsgrad.

1. Abfrage:
Es sollen alle Kunden gezeigt werden die in unserer Datenbank vorhanden sind, geordnet nach Plz.

SELECT * FROM Kunde ORDER BY Plz ASC

2. Abfrage:
Es soll gezeigt werden wie viel Kunden sich in der Datenbank in jedem Bundesland befinden; aufsteigend geordnet;


SELECT Region, COUNT(Kunde.Nr) as "Kundenanzahl" FROM PLZ, Kunde WHERE PLZ.Plz = Kunde.Plz GROUP by Region ORDER by 2 ASC

die wenigsten Kunden befinden sich in Voralberg(49) und die meisten in Salzburg(160).


3. Abfrage:
Nun werden alle Daten der Kunden mit Vorname Michael (sprich KundenNr., Vorname, Nachname, Strasse und Plz) dem Nachnamen nach alphabetisch aufgelistet.

SELECT * FROM Kunde WHERE Kunde.Vorname="Michael" ORDER BY "Nachname" ASC

4. Abfrage:
Weiters sollen alle Kunden mit Vornamen Michael, Nachname und Plz(innerhalb Salzburg) aus dem Bundesland Salzburg, sortiert dargestellt werden.

SELECT Kunde.Vorname="Michael",Kunde.Nachname,Kunde.Plz, PLZ.Region FROM Kunde, PLZ WHERE Kunde.Plz=PLZ.Plz and Region='Sa' ORDER BY Kunde.Vorname="Michael" DESC

Dieses Ergebnis liefert uns die Information das nur 3 Kunden mit dem Namen Michael Hafner (Plz 5511), Michael Pielsticker (Plz 5010), und Michael Leupold (Plz 5010) in Salzburg zu finden sind.

5. Abfrage:
Welche Kunden haben den schwächsten Umsatz?

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 ASC

Unter den 3 schwächsten Kunden befinden sich Alexandra Leupold(56,78), Susanne Laenger(113,56) und Luisa Kinner(140,28).

Mein Name ist Karin Strasser und ich studiere Wirtschaftswissenschaften an der Johannes Kepler Universität in Linz.

Seit dem SS08 belege ich unter anderem das Ergänzungsfach Informationsverarbeitung 2 im 2. Abschnitt meines Studiums.
Mit der Belegung dieser Lehrveranstaltungen erhoffe ich mir vor allem das ich den Inhalt von IV1 im ersten Abschnitt wieder auffrischen und vor allem noch mehr an neuen Inhalten lernen und anwenden kann.

Das Ziel dieser Lehrveranstaltung ist Kooperatives Lernen zu vermitteln und mit Hilfe eines Weblogs dieses umzusetzen.

Ich wünsche euch allen ein erfolgreiches SS 08 und viel Vergnügen mit meinem Weblog:)