... neuere Einträge
Vorbereitung auf meine praktische Prüfung
Dienstag, 29. Januar 2008, 10:31
....Alle folgende Beiträge zu SQL & Co habe ich für meine Vorbereitung auf meine praktische Prüfung erstellt. Da dieser Weblog mich bei meiner Lehrveranstaltung unterstützen soll, wollte ich ihn auch für meine Vorbereitung auf die Prüfung nutzen.
SQL in EXCEL importieren
-Abfrage erstellen und durchführen
-Datei - Frame speichern (Format: Textdatei - als .txt speichern)
-Excel - Daten - Daten importieren
> 'Getrennt' auswählen, Import beginnt in der Zeile ... hier die erste Zeile in der meine Überschriften stehen - weiter
> 'Tabstopp' auswählen - weiter
> Die Spalte der Preise markieren und 'Text' auswählen - Fertig stellen
> vorhandenes Datenblatt - ok
- Punkt durch Beistrich ersetzten (Bearbeiten - Suchen nach . Ersetzen durch , )
PIVOT-Table
-Gesamtes Excelsheet markieren
-Daten - Pivot Table
SQL in EXCEL importieren
-Abfrage erstellen und durchführen
-Datei - Frame speichern (Format: Textdatei - als .txt speichern)
-Excel - Daten - Daten importieren
> 'Getrennt' auswählen, Import beginnt in der Zeile ... hier die erste Zeile in der meine Überschriften stehen - weiter
> 'Tabstopp' auswählen - weiter
> Die Spalte der Preise markieren und 'Text' auswählen - Fertig stellen
> vorhandenes Datenblatt - ok
- Punkt durch Beistrich ersetzten (Bearbeiten - Suchen nach . Ersetzen durch , )
PIVOT-Table
-Gesamtes Excelsheet markieren
-Daten - Pivot Table
Permalink (0 Kommentare) Kommentieren
SQL allgemein, links,...
Dienstag, 29. Januar 2008, 10:28
Distict
SELECT DISTINCT Vorname FROM Kunde
Operatoren in WHERE
1) ( )
2) * /
3) + -
4) = <> > <
5) NOT AND OR
Between
SELECT Vorname, Nachname, Plz FROM Kunde WHERE Plz BETWEEN 1010 AND 4030
Group by, wenn...
COUNT()
SUM()
AVG()
MAX()
MIN()
Beispiele
1.Es sollen alle bisher erfassten Aufträge angezeigt werden, und zwar mit Auftragsnummer, Kundenname (Nachname) und Auftragssumme. Die
Anzeige soll absteigend nach der Auftragssumme sortiert werden.
SELECT aa.Auftragsnummer, k.Nachname, SUM(b.Preis) AS Auftragssumme
FROM Auftrag a, Auftragspos aa, Buch b, Kunde k
WHERE aa.Auftragsnummer=a.Nr
AND a.Kundennummer=k.Nr
AND aa.Buchnummer=b.Nr
GROUP BY k.Nachname
ORDER BY 3 DESC
2. Anzahl der Chemiebücher je Verlag, absteigend gereiht nach Vorkommen
SELECT COUNT(*) AS Vorkommen, b.Verlag
FROM Buch b
WHERE b.Titel LIKE "%chemie%"
GROUP BY b.Verlag
ORDER BY 1
3. Es soll der Verlagsname und die Anzahl der Bücher, die dieser Verlag
veröffentlicht hat, ausgegeben werden.
SELECT v.Name, COUNT(b.Titel)
FROM Verlag v, Buch b
WHERE v.Kurzbezeichnung=b.Verlag
GROUP BY v.Name
4. Wie viele Kunden wohnen in welcher Region (gruppiert nach Region;
Anzeige: Region und Anzahl der Kunden)?
SELECT p.Region, COUNT(k.Nr) AS Anzahl_der_Kunden
FROM Kunde k, PLZ p
WHERE p.Plz=k.Plz
GROUP BY p.Region
5. Gesamtwert aller Bücher in denen das Wort „bio“ vorkommt? Anzeige: Summe
SELECT SUM(b.Preis) AS Summe
FROM Buch b
WHERE b.Titel LIKE "%bio%"
6. Zeigen Sie den Nachnamen und das Datum der Aufträge an die im
Zeitraum 26.01.2000 bis 28.01.2000 getätigt wurden. Ordnen sie die
Anzeige absteigend nach Nachnamen.
SELECT k.Nachname, a.Datum
FROM Auftrag a, Kunde k
WHERE k.Nr=a.Kundennummer AND a.Datum BETWEEN "2000-01-26"
AND "2000-01-28"
ORDER BY k.Nachname DESC
Hilfreiche Beispiele von Kolleg/innen
ABC-Analyse
/0355143/stories/18734/
Umsätze & co zu 2006
/0256479/stories/17909/
Auftragszahlen und distinct
/0256479/stories/17660/
Bester Kunde, Ladenhüter,...
/0256479/stories/17121/
SELECT DISTINCT Vorname FROM Kunde
Operatoren in WHERE
1) ( )
2) * /
3) + -
4) = <> > <
5) NOT AND OR
Between
SELECT Vorname, Nachname, Plz FROM Kunde WHERE Plz BETWEEN 1010 AND 4030
Group by, wenn...
COUNT()
SUM()
AVG()
MAX()
MIN()
Beispiele
1.Es sollen alle bisher erfassten Aufträge angezeigt werden, und zwar mit Auftragsnummer, Kundenname (Nachname) und Auftragssumme. Die
Anzeige soll absteigend nach der Auftragssumme sortiert werden.
SELECT aa.Auftragsnummer, k.Nachname, SUM(b.Preis) AS Auftragssumme
FROM Auftrag a, Auftragspos aa, Buch b, Kunde k
WHERE aa.Auftragsnummer=a.Nr
AND a.Kundennummer=k.Nr
AND aa.Buchnummer=b.Nr
GROUP BY k.Nachname
ORDER BY 3 DESC
2. Anzahl der Chemiebücher je Verlag, absteigend gereiht nach Vorkommen
SELECT COUNT(*) AS Vorkommen, b.Verlag
FROM Buch b
WHERE b.Titel LIKE "%chemie%"
GROUP BY b.Verlag
ORDER BY 1
3. Es soll der Verlagsname und die Anzahl der Bücher, die dieser Verlag
veröffentlicht hat, ausgegeben werden.
SELECT v.Name, COUNT(b.Titel)
FROM Verlag v, Buch b
WHERE v.Kurzbezeichnung=b.Verlag
GROUP BY v.Name
4. Wie viele Kunden wohnen in welcher Region (gruppiert nach Region;
Anzeige: Region und Anzahl der Kunden)?
SELECT p.Region, COUNT(k.Nr) AS Anzahl_der_Kunden
FROM Kunde k, PLZ p
WHERE p.Plz=k.Plz
GROUP BY p.Region
5. Gesamtwert aller Bücher in denen das Wort „bio“ vorkommt? Anzeige: Summe
SELECT SUM(b.Preis) AS Summe
FROM Buch b
WHERE b.Titel LIKE "%bio%"
6. Zeigen Sie den Nachnamen und das Datum der Aufträge an die im
Zeitraum 26.01.2000 bis 28.01.2000 getätigt wurden. Ordnen sie die
Anzeige absteigend nach Nachnamen.
SELECT k.Nachname, a.Datum
FROM Auftrag a, Kunde k
WHERE k.Nr=a.Kundennummer AND a.Datum BETWEEN "2000-01-26"
AND "2000-01-28"
ORDER BY k.Nachname DESC
Hilfreiche Beispiele von Kolleg/innen
ABC-Analyse
/0355143/stories/18734/
Umsätze & co zu 2006
/0256479/stories/17909/
Auftragszahlen und distinct
/0256479/stories/17660/
Bester Kunde, Ladenhüter,...
/0256479/stories/17121/
Permalink (0 Kommentare) Kommentieren
SQL Beispiele
Dienstag, 29. Januar 2008, 10:28
Abfrage
SELECT
FROM
WHERE
Einfache Abfrage
SELECT Vorname
FROM Kunde
Mehr Abfragen
SELECT Vorname, Nachname, Plz
FROM Kunde
Alles aus einer Tabelle abfragen
SELECT*
FROM Kunde
Jeden Vornamen nur einmal
SELECT DISTINCT Vorname
FROM Kunde
Auswahl auf „Michael“ eingrenzen
SELECT Vorname, Nachname
FROM Kunde
WHERE Vorname='Michael'
...und PLZ kleiner 4030
SELECT Vorname, Nachname, Plz
FROM Kunde
WHERE Vorname='Michael' AND Plz<4030
PLZ zwischen 1010 und 4030
SELECT Vorname, Nachname, Plz
FROM Kunde
WHERE Plz BETWEEN 1010 AND 4030
Schlüsselwörter
SELECT Vorname, Nachname
FROM Kunde
WHERE Nachname LIKE '%Muster%'
Datensätze ordnen
SELECT Vorname, Nachname
FROM Kunde
ORDER BY Nachname, Vorname ASC
Gruppieren
SELECT Vorname, Nachname
FROM Kunde
GROUP BY Nachname, Vorname ASC
Beispiel 1 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
Beispiel 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(Buch.Preis), COUNT(Buch.Nr)
FROM Buch
WHERE Buch.Verlag='mc'
Beispiel 3 Wie viel kostet im Durchschnitt ein Buch des Autors Arge?
SELECT AVG(Buch.Preis), COUNT(Buch.Nr)
FROM Buch
WHERE Buch.Autor='Arge'
Beispiel 4 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
Beispiel 5 Es sollen alle Orte in Oberösterreich in einer Liste mit ihrer Postleitzahl ausgegeben werden. Es sollen nur jene Orte aufgelistet wer-den deren Postleitzahl nicht mit einer 4 beginnt.
SELECT *
FROM PLZ
WHERE (PLZ.Region='O' AND PLZ.Plz<4000) OR (PLZ.Region='O' AND PLZ.Plz>4999)
SELECT
FROM
WHERE
Einfache Abfrage
SELECT Vorname
FROM Kunde
Mehr Abfragen
SELECT Vorname, Nachname, Plz
FROM Kunde
Alles aus einer Tabelle abfragen
SELECT*
FROM Kunde
Jeden Vornamen nur einmal
SELECT DISTINCT Vorname
FROM Kunde
Auswahl auf „Michael“ eingrenzen
SELECT Vorname, Nachname
FROM Kunde
WHERE Vorname='Michael'
...und PLZ kleiner 4030
SELECT Vorname, Nachname, Plz
FROM Kunde
WHERE Vorname='Michael' AND Plz<4030
PLZ zwischen 1010 und 4030
SELECT Vorname, Nachname, Plz
FROM Kunde
WHERE Plz BETWEEN 1010 AND 4030
Schlüsselwörter
SELECT Vorname, Nachname
FROM Kunde
WHERE Nachname LIKE '%Muster%'
Datensätze ordnen
SELECT Vorname, Nachname
FROM Kunde
ORDER BY Nachname, Vorname ASC
Gruppieren
SELECT Vorname, Nachname
FROM Kunde
GROUP BY Nachname, Vorname ASC
Beispiel 1 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
Beispiel 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(Buch.Preis), COUNT(Buch.Nr)
FROM Buch
WHERE Buch.Verlag='mc'
Beispiel 3 Wie viel kostet im Durchschnitt ein Buch des Autors Arge?
SELECT AVG(Buch.Preis), COUNT(Buch.Nr)
FROM Buch
WHERE Buch.Autor='Arge'
Beispiel 4 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
Beispiel 5 Es sollen alle Orte in Oberösterreich in einer Liste mit ihrer Postleitzahl ausgegeben werden. Es sollen nur jene Orte aufgelistet wer-den deren Postleitzahl nicht mit einer 4 beginnt.
SELECT *
FROM PLZ
WHERE (PLZ.Region='O' AND PLZ.Plz<4000) OR (PLZ.Region='O' AND PLZ.Plz>4999)
Permalink (0 Kommentare) Kommentieren
SQL + HAVING
Dienstag, 29. Januar 2008, 10:21
+ Einführung des Befehls "having", z.B.: Abfrage von Kundennummer, Kundenname, Gesamteinkaufssumme mit Beschriftung „Summe“; das Ganze gruppiert nach Kundennummer und nur mit jenen Datensätzen mit einer Gesamteinkaufssumme > 100.000 € und sortiert nach Gesamteinkaufssumme beginnend mit höchster Einkaufssumme --> umsatzstärkste Kunden mit Mindestumsatz 100.000 €
Lösung:
SELECT Kunde.Nr AS KNr, Kunde.Vorname, Kunde.Nachname, SUM(Buch.Preis*Auftragspos.Menge) AS Summe
FROM Auftragspos, Auftrag, Kunde, Buch
WHERE Auftragspos.Auftragsnummer=Auftrag.Nr
AND Kunde.Nr=Auftrag.Kundennummer
AND Buch.Nr=Auftragspos.Buchnummer
GROUP BY Kunde.Nr
HAVING SUM(Buch.Preis*Auftragspos.Menge)>100000
ORDER BY Summe DESC
Lösung:
SELECT Kunde.Nr AS KNr, Kunde.Vorname, Kunde.Nachname, SUM(Buch.Preis*Auftragspos.Menge) AS Summe
FROM Auftragspos, Auftrag, Kunde, Buch
WHERE Auftragspos.Auftragsnummer=Auftrag.Nr
AND Kunde.Nr=Auftrag.Kundennummer
AND Buch.Nr=Auftragspos.Buchnummer
GROUP BY Kunde.Nr
HAVING SUM(Buch.Preis*Auftragspos.Menge)>100000
ORDER BY Summe DESC
Permalink (0 Kommentare) Kommentieren
SQL + COUNT()
Dienstag, 29. Januar 2008, 10:21
Wie viele Kunden wohnen in welcher Region (gruppiert nach Region)?
Lösung:
SELECT PLZ.Region, COUNT(Kunde.Nr) AS "Anzahl der Kunden"
FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz
GROUP BY PLZ.Region
Lösung:
SELECT PLZ.Region, COUNT(Kunde.Nr) AS "Anzahl der Kunden"
FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz
GROUP BY PLZ.Region
Permalink (0 Kommentare) Kommentieren
Drop-Down-Button, Eingabefeld
Dienstag, 29. Januar 2008, 10:15
+ Auswahl Button
+ Drop-Down Button
Auswahlbutton |
Permalink (0 Kommentare) Kommentieren
... ältere Einträge