Wirtschaftsinformatik SQL/QBE1 Abfragen mit QBE und SQL Wirtschaftsinformatik II

SQL/QBE 1

Wirtschaftsinformatik

Abfragen mit QBE und SQLAbfragen mit QBE und SQL

Wirtschaftsinformatik IIWirtschaftsinformatik II

SQL/QBE 2

WirtschaftsinformatikProgrammiersprachen

1. Generation: • Maschinensprachen (prozessorabhängig!): nur 0 und 1

2. Generation: • Assembler (prozessorabhängig!): ADD, SUB, MUL, DIV, ...

3. Generation: • Problemorientierte Sprachen:

• WIE soll ein Problem gelöst werden? (Unterteilung in betriebswirtschaftliche und technisch-wissenschaftliche Sprachen): Cobol, Pascal, Fortran, Ada, Basic, C, PL/1, RPG, ...

4. Generation: • Deskriptive Sprachen:

• WAS wird als Ergebnis erwartet? Beispiel: SQL (Structured Query Language)

weitere Unterscheidung: • wissensbasierte (Prolog und Lisp) und

objektorientierte Sprachen (SmallTalk, C++, Java, Delphi)

SQL/QBE 3

WirtschaftsinformatikProgrammiersprachen

Prozeduraler Algorithmus (3. Generation):

1) Nimm nächstes Buch

2) Prüfe, ob Titel = „Wirtschaftsinformatik“

3) Falls JA, notiere Autor

4) Prüfe, ob letztes Buch

5) Falls nein, zurück zu 1)

6) Falls ja, Ende

SQL/QBE 4

WirtschaftsinformatikProgrammiersprachen

Nicht-prozedurale, deskriptive Formulierung (4. Generation):

Suche alle Bücher aus der Tabelle „Bibliothek“ für die gilt: Titel = „Wirtschaftsinformatik“

Beispiel für eine entsprechende SQL-Abfrage:

SELECT Autor, Titel, Erscheinungsjahr

FROM Bibliothek

WHERE Titel = „Wirtschaftsinformatik“;

SQL/QBE 5

WirtschaftsinformatikGrundlagen von SQL

• SQL = Structured Query Language

• Standardsprache für relationalen Datenbanken – (z.B. Oracle, IBM DB2, MS Access, MS SQL-Server, etc.)

• Dient dem Benutzer zur Kommunikation mit dem DBMS

• Es existieren proprietäre Erweiterungen zum „reinen“ Standard-SQL

• Sprache der 4.Generation– Nicht prozedural, sondern datenorientiert

• Elemente von SQL:– Datendefinition– Datenmanipulation– Datenabfrage– Datenschutz

SQL/QBE 6

WirtschaftsinformatikDatendefinition

• Tabellen erzeugen

• Tabellen ändern:– Neue Attribute einfügen

• Auch wenn bereits Daten in der Tabelle eingegeben sind

– Tabellenattribute indizieren• z.B. wenn nur eindeutige Werte erlaubt sind oder wenn die

Suche nach Attributwerten beschleunigt werden soll

– Beziehungen verwalten• Festlegen von Primär- und Fremdschlüsseln Einhaltung der

referentiellen Integrität

• Tabellen löschen

SQL/QBE 7

WirtschaftsinformatikDatenmanipulation

• Datensätze einfügen

• Datensätze nachführen

• Datensätze löschen

SQL/QBE 8

WirtschaftsinformatikDatenabfrage (Query)

• Angegeben wird:– Mit welchen Daten gearbeitet werden soll und– Welche Bedingungen diese Daten erfüllen müssen

• Eine Abfrage beginnt immer mit SELECT

SELECT [DISTINCT] {*|Attributliste|mathematische Ausdrücke} AS Bezeichner

FROM Tabelle1 AS Bezeichner1, Tabelle2 AS Bezeichner2, ...

[WHERE Bedingungen]

[GROUP BY Attributliste] [HAVING Bedingungen]

[ORDER BY Attributliste] [ASC|DESC];

SQL/QBE 9

WirtschaftsinformatikDatenabfrage (Query)

• Typische Fragen, die durch Auswahlabfragen beantwortet werden sollen:– „Welches Projekt hat die höchsten Projektkosten?“– „Liste der Mitarbeiter absteigend sortiert nach

Betriebszugehörigkeit.“– „Welche Mitarbeiter arbeiten in einem Datenbank-Projekt?“– „Welche Mitarbeiter wohnen in München?“– „Wie hoch ist der Durchschnittsgewinn der Projekte?“

• Hierzu greifen Auswahlabfragen auf ein oder mehrere Tabellen einer DB zu.

• Sie liefern wiederum eine Tabelle als Ergebnis.

SQL/QBE 10

Wirtschaftsinformatik

SQL/QBE 11

WirtschaftsinformatikProjektion

• Auswahl einzelner Attribute (Felder, Spalten). Bsp. in QBE (Query by Example):

SQL/QBE 12

WirtschaftsinformatikProjektion

• Dasselbe Beispiel in SQL (Structured Query Language):

SELECT Nachname, Vorname, Straße_HausNr, PLZ, Ort, Telefon_PrivatFROM Mitarbeiter;

SQL/QBE 13

WirtschaftsinformatikProjektion

• Das Ergebnis:

SQL/QBE 14

WirtschaftsinformatikSelektion

• Auswahl bestimmter Datensätze. Bsp.: „Welche Kunden befinden sich in München und gehören zu den Branchen Bank oder Versicherung?“

SQL/QBE 15

WirtschaftsinformatikSelektion

• „Welche Kunden befinden sich in München und gehören zu den Branchen Bank oder Versicherung?“

• In SQL:

SELECT Firmenname, Ort, BrancheFROM KundeWHERE

(Ort="München" AND Branche="Bank") OR (Ort="München" AND Branche="Versicherung");

SQL/QBE 16

WirtschaftsinformatikSelektion

• Ergebnis:

SQL/QBE 17

WirtschaftsinformatikSelektion

• Selektionskriterien finden Sie im Access-Handout auf S.178. • Was findet die folgende

Auswahlabfrage heraus?

• Hier der zugehörige SQL:

SELECT Kurzname, Projektkosten

FROM ProjektWHERE Projektkosten>2000000;

SQL/QBE 18

WirtschaftsinformatikSortierung

SELECT Nachname, Vorname, Straße_HausNr, PLZ, OrtFROM MitarbeiterORDER BY Ort, Nachname;

SQL/QBE 19

WirtschaftsinformatikSortierung

• Ergebnis:

SQL/QBE 20

Wirtschaftsinformatik

Join:Verknüpfung mehrerer Tabellen

• Bsp: „Welche Kunden haben welche Projekte vergeben?“

SQL/QBE 21

Wirtschaftsinformatik

• In SQL:

SELECT Kunde.Firmenname, Kunde.Kundennummer, Projekt.KundenNr, Projekt.Kurzname

FROM Kunde INNER JOIN Projekt ON Kunde.Kundennummer = Projekt.KundenNr;

Join:Verknüpfung mehrerer Tabellen

SQL/QBE 22

WirtschaftsinformatikJoin mit Kriterien

• „Welche Kunden aus München haben Projekte mit einem Volumen von über 1‘000‘000.- vergeben?“

SQL/QBE 23

WirtschaftsinformatikJoin mit Kriterien• „Welche Kunden aus München haben Projekte mit einem Volumen von über 1‘000‘000.- vergeben?“ In SQL:

SELECT Kunde.Firmenname, Kunde.Ort, Projekt.Kurzname, Projekt.VergütungFROM Kunde INNER JOIN Projekt ON

Kunde.Kundennummer = Projekt.KundenNrWHERE (Kunde.Ort="München" AND Projekt.Vergütung>1000000);

SQL/QBE 24

WirtschaftsinformatikKomplexer Join

• „Welche Mitarbeiter haben schon auf vom Kunden ‚Bodidas‘ vergebenen Projekten gearbeitet?“

SQL/QBE 25

WirtschaftsinformatikKomplexer Join

• „Welche Mitarbeiter haben schon auf vom Kunden ‚Bodidas‘ vergebenen Projekten gearbeitet?“• In SQL:

SELECT Kunde.Firmenname, Mitarbeiter.NachnameFROM (Kunde INNER JOIN Projekt ON Kunde.Kundennummer = Projekt.KundenNr) INNER JOIN (Mitarbeiter INNER JOIN Projektbearbeitung ON

Mitarbeiter.MitarbeiterNr = Projektbearbeitung.MitarbeiterNr) ON Projekt.Projektnummer = Projektbearbeitung.ProjektnummerWHERE (Kunde.Firmenname Like "Bodidas*");

SQL/QBE 26

WirtschaftsinformatikKomplexer Join

• „Welche Mitarbeiter haben schon auf vom Kunden ‚Bodidas‘ vergebenen Projekten gearbeitet?“• In SQL:

SELECT Kunde.Firmenname, Mitarbeiter.Nachname

FROM Kunde, Projekt, Projektbearbeitung, Mitarbeiter

WHERE Kunde.Firmenname Like "Bodidas*"AND Kunde.Kundennummer = Projekt.KundenNrAND Projekt.Projektnummer =

Projektbearbeitung.ProjektnummerAND Mitarbeiter.MitarbeiterNr =

Projektbearbeitung.MitarbeiterNr;

SQL/QBE 27

WirtschaftsinformatikBerechnete Felder

• „Wie hoch ist der Projektgewinn (= Vergütung - Projektkosten) der Projekte?“

SQL/QBE 28

WirtschaftsinformatikBerechnete Felder

• „Wie hoch ist der Projektgewinn (= Vergütung - Projektkosten) der Projekte?“

• In SQL:

SELECT Projektnummer, Vergütung-Projektkosten AS Projektgewinn

FROM Projekt;

SQL/QBE 29

Wirtschaftsinformatik

Funktionen: Auswertungenüber alle Datensätze

• Berechnungen über mehrere Attributwerte eines Attributs• Bezieht sich entweder auf eine gesamte Tabelle oder auf eine Gruppe von

Datensätzen• Es gibt folgende Funktionen:

– Summe, Mittelwert, Minimum, Maximum, Anzahl• Bsp.: „Wieviele Projekte gibt es insgesamt?“• In SQL:

SELECT COUNT (Projektnummer) AS [Anzahl von Projektnummer]

FROM Projekt;

SQL/QBE 30

Wirtschaftsinformatik

Funktionen: Auswertungen über eine Gruppe von Datensätzen

„Anzahl der pro Fachgebiet durchgeführten Projekte.“

SELECT Fachgebiet, Count(Projektnummer) AS [Anzahl von Projektnummer]

FROM ProjektGROUP BY Fachgebiet;

ErgebnisFachgebiet Anzahl von Projektnummer

Datenbanken 3

Groupware 4

Internet 4

Intranet 5

SQL/QBE 31

Wirtschaftsinformatik

Suche in einer Abfrage mit variablem Suchwert

SELECT MitarbeiterNr, Nachname, Vorname

FROM MITARBEITER

WHERE Nachname LIKE [Suchen nach welchem Namen?];

SQL/QBE 32

WirtschaftsinformatikGrundstruktur der SQL-AbfrageSELECT [ DISTINCT ] { * | Attributliste | mathematische Ausdrücke} [ AS

Bezeichner ]

FROM Tabellenliste [ AS Bezeichner ]

[ WHERE Bedingungen ]

[ GROUP BY Attributliste ] [ HAVING Bedingungen ]

[ ORDER BY Attributliste ] [ ASC | DESC ] ;

• Mit SELECT kann angegeben werden, welche Attribute angezeigt werden sollen und wie diese allenfalls in Berechnungen und Funktionen einzusetzen sind. Falls DISTINCT verwendet wird, werden mehrfach auftretende, identische Datensätze nur einmal angezeigt.

• Mit FROM wird angegeben, aus welchen Tabellen Datensätze abgefragt bzw. zu neuen Datensätzen kombiniert werden.

• Mit WHERE wird angegeben, welche Bedingungen ein Datensatz erfüllen muss, damit er weiterverarbeitet wird.

• Mit GROUP BY können Datensätze zu Gruppen zusammengefasst und mit speziellen Gruppenfunktionen weiterverarbeitet werden.

• HAVING gibt an, welche Bedingungen eine Gruppe aus Datensätzen erfüllen muss, damit sie weiterverwendet wird.

• Mit ORDER BY können die resultierenden Datensätze vor der Ausgabe nach bestimmten Attributen aufsteigend (ASC für "ascending") bzw. absteigend (DESC für "descending") sortiert werden.

SQL/QBE 33

WirtschaftsinformatikQuellen

• Quelle der Beispiele: Jaros-Sturhahn, A.; Schachtner, K: Business Computing mit MS-Office und Internet, Springer 1999

• Steiner, René: Grundkurs Relationale Datenbanken, Vieweg, 2003