===== 9.7.2) DQL - Data Query Language =====

===== 9.7.2.1) Grundkonstruktion einer SQL-Abfrage =====

Grundkonstruktion einer SQL-Abfrage ist von der Form
\\
\\
"**SELECT** //attributListe// **FROM** //relation//"

== Beispiel ==

<code sql>
SELECT name,adresse FROM restaurant;
</code>
entspricht \\
$ π_{name,adresse}(restaurant) $
\\
\\
<code sql>
SELECT * FROM restaurant;
/*liefert die gesamte Tabelle (d.h. mit allen Attributen)
</code>
\\
\\
**SQL-Abfrage mit Verknüpfung mehrerer Relationen:**
\\
\\
"**SELECT** //attributListe// **FROM** //relationen// **WHERE** //bedingung//"
\\
\\
Eine solche SQL-Abfrage wird ausgewertet, indem zunächst das **kartesische Produkt** der in der **FROM**-Klausel angegebenen Relationen gebildet wird. Daraus werden dann jene Tupel ausgewählt, die die in der **WHERE**-Klausel angegebenen Bedingungen erfüllen. Zudem wird auf die in der **SELECT**-Klausel angegebenen Attribute dieser Tupel projiziert. 

== Beispiel ==

Wir wollen wissen welche Speisen in welchem Restaurant angeboten werden.

<code sql>
SELECT * FROM Speise, Restaurant WHERE Restaurant.rnr = Speise.rnr;
</code>

Die Bedingunng in der WHERE-Klausel kann sich aus mehreren, durch die logischen Operatoren AND, OR und NOT verbundenen Teilbedingungen zusammensetzen.

== Beispiel ==
<code sql>
	SELECT rnr, name, haube, typ
	FROM restaurant
	WHERE haube > 1 AND NOT (typ='österreichisch' OR typ='international');
</code>

In der SELECT-Klausel können auch Ausdrücke, die aus Attributen berechnet werden, stehen. Durch das Schlüsselwort **AS** kann einem so berechneten Ausdruck ein Attributname zugewiesen werden.

==Beispiel==

Wir wollen alle Paare von Restaurants auswählen, die sich um mehr als zwei Hauben unterscheiden und die Differenz der Hauben ausgeben.

<code sql>
	SELECT r1.name, r2.name, (r2.haube-r1.haube) AS differenz
	FROM restaurant r1, restaurant r2
	WHERE r1.haube +2 < r2.haube;
</code> 

In SQL werden Duplikate, d.h. Ergebnistupel mit identischen Werten, nicht automatisch eliminiert. Um Duplikate zu eliminieren, muss der Liste von ausgewählten Attribute das Schlüsselwort **DISTINCT** vorangestellt werden.\\
Durch die Angabe einer ORDER BY-Klausel mit den Schlüsselworten ASC oder DESC kann die Ausgabe aufsteigend bzw. absteigend nach bestimmten Attributen sortiert werden. Der Defaultwert ist ASC.

== Beispiel ==
Wir wollen eine alphabetisch sortierte Liste aller Speisen von Restaurants mit mehr als einer Haube.

<code sql>
	SELECT DISTINCT s.name
	FROM Restaurant r, Speise r
	WHERE (r.rnr = s.rnr) AND (r.haube>1)
	ORDER BY s.name, s.preis;
</code>

==== Übungsbeispiele ====

Gegeben sind folgende Relationen
\\
\\
**Person** (__id__, name, plz) \\
**Orte** (__plz__, ort) \\
**Mitarbeiter** (__id__, gehalt, abtnr) \\ 
**Kunde** (__id__, rabatt)\\ 
**Abteilung** (__abtnr__, name)\\ 
**PKW** (marke, ps, id, __fahrgestellnr__)\\

== Aufgaben ==

Entwickeln Sie SQL-Anweisungen für folgende Abfragen

  - Geben Sie die Namen aller Personen aus Amstetten aus
  - Geben Sie die Namen aller Mitarbeiter aus Amstetten aus
  - Gesucht sind die Namen aller Mitarbeiter die auch Kunden sind
  - Namen aller Mitarbeiter die PKWs mit mehr als 1000 PS fahren
  - Gesucht sind die Namen jener Orte, in denen Mitarbeiter wohnen, die in einer Abteilung "Buchhaltung" arbeiten

== Lösungen ==

<code sql>
1)
SELECT name
FROM Person p, Orte o
WHERE o.ort="Amstetten" and p.plz=o=plz;


2)
SELECT name
FROM Person p, Orte o, Mitarbeiter m
WHERE m.id=p.id AND o.ort="Amstetten" AND p.plz=o.plz; 


3)
SELECT name
FROM Person p, Orte o, Mitarbeiter m
WHERE m.id=p.id AND m.id=k.id;

4)
SELECT name
FROM Person p, Mitarbeiter m, PKW
WHERE p.id=m.id AND PKW.id=p.id AND PKW.ps>100;

5)
SELECT ort
FROM Orte o, Mitarbeiter m, Person p, Abteilung a
WHERE o.plz=p.plz AND p.id=m.id AND m.abtnr=a.abtnr AND p.plz=o.plz;

</code>

===== 9.7.2.2) Die WHERE-Klausel =====

Neben den Boolschen Funktionen (AND,OR,NOT) sind folgende weitere Angaben zulässig:

==== IN-Operator ====

Anstatt eine Reihe von OR-Verknüpfungen durchzuführen, kann auch der IN-Operator verwendet werden.

Bsp: 
<code SQL>
SELECT * FROM ... WHERE typ IN ('österreichisch', 'international');
</code>

==== BETWEEN-Operator ====

Bsp.: 
<code SQL>
SELECT * FROM ... WHERE preis BETWEEN 100 and 150;

//ist dasselbe wie

SELECT * FROM ... WHERE preis >= 100 and preis<=150;

</code>

==== IS NULL-Operator ====
Bsp.: Es sollen alle Personen gesucht werden, die beim Attribut TelNr den NULL-Wert eingetragen haben.

<code sql>
SELECT * FROM ... WHERE TelNr IS NULL;
</code>

==== LIKE-Operator ====

Der Like-Operator ist ein Vergleichsoperator, der Datenwerte einer Spalte mit einem vorgegebenen Muster vergleicht.
\\
\\
Allgemeine Form:
\\
\\
AttributName [NOT] LIKE 'muster';
\\
\\
Dabei haben in 'muster' zwei Zeichen eine besondere Bedeutung: \\
  * % .... steht für 0 bis beliebig viele Zeichen
  * _ .... steht für genau ein beliebiges Zeichen
\\
\\
Bsp.: Alle Speisen deren Name mit 'M' beginnt

<code sql>
SELECT Name FROM Speise WHERE Name LIKE 'M%';
</code>
\\
Bsp.: Alle Restaurants, welche an 2. Stelle im Namen ein 'o' haben

<code sql>
SELECT Name FROM Restaurant WHERE Name LIKE '_o%';
</code>


===== 9.7.2.3) Mengenoperationen =====

SQL stellt die Mengenoperationen **UNION** (Vereinigung), **INTERSECT** (Durchschnitt) und **EXCEPT** (Mengendifferenz) für typkompatible Relationen (d.h. Relationen mit der gleichen Anzahl von jeweils typkompatiblen Attributen) zur Verfügung.
\\
\\
**Beispiel:** Wir wollen eine Liste der Namen aller Restaurants und der Namen aller Speisen

<code sql>
(SELECT Name FROM Restaurant)
UNION
(SELECT Name FROM Speise)
</code>

Mit typkompatiblen Relationen sind dabei nicht die Relationen **Restaurant** oder **Speise** gemeint, sondern die Ergebnisse der beiden **SELECT** Statements.
\\
\\
===== 9.7.2.4) Aggregatfunktionen und Gruppierung =====

==== Aggregatfunktionen ====

Es gibt folgende Aggregatfunktionen in SQL:

  * MIN
  * MAX
  * SUM
  * AVG
  * COUNT

Bsp: Gesucht ist der Preis des teuersten Burgers:

<code sql>
SELECT MAX(Preis)
FROM Speise
WHERE Name LIKE '%Burger%';
</code>

Bsp.: Wie viele Speisen werden im Brauhof angeboten?
<code sql>
SELECT COUNT (*)   //COUNT (*) zählt alle Tupel
FROM Restaurant r, Speise s,
WHERE r.rnr = s.rnr AND r.name="Brauhof";
</code>

==== Gruppierung ====

Die Select-Abfrage kann um eine Klause "**GROUP BY** //attributListe//" und eine Klausel "**HAVING** //bedingung//" ergänzt werden, um Gruppen von Tupeln zu bilden und auf diese Gruppen Aggregatfunktionen anzuwenden.
\\
Dabei werden diese Tupel nach gemeinsamen Werten in den in der **GROUP BY**-Klausel angeführten Attributen gruppiert. Von diesen Gruppen werden jene eliminiert, die die in der **HAVING**-Klausel angegebene Bedingung nicht erfüllen.
\\
\\
Anschließend werden etwaige in der **SELECT**-Klausel angegebenen Aggregatfunktionen auf jede der verbliebenen Gruppen angewendet. Diese Aggregatfunktionen (außer **COUNT**) können auch auf arithmetische Ausdrücke über Attributen angewandt werden.
\\
\\
===Beispiel===
^Relation Restaurant^^^^
^rnr^RName^Adr^Haube^
|1|McDonalds|Amstetten|0|
|2|McDonalds|Ybbs|0|
|3|Hollywood|Amstetten|1|
|4|Casa Venezia|Amstetten|2|
|5|grill.Bar|Amstetten|2|
|6|Schinakel|Grein|1|

Frage: Wie viele Restaurants haben die gleiche Haubenanzahl?
\\
\\
<code sql>
SELECT Haube, COUNT (*) AS AnzahlRestaurants
FROM Restaurant
GROUP BY Haube;
</code>
\\
**Ergebnis:**
\\
^Haube^AnzahlRestaurants^
|0|2|
|1|2|
|2|2|

Frage: Wie viele Restaurants in Amstetten haben die gleiche Haubenanzahl?
\\
\\
<code sql>
SELECT Haube, COUNT (*) AS AnzahlRestaurants
FROM Restaurant
WHERE Adr='Amstetten'
GROUP BY Haube;
</code>
\\
**Ergebnis:**
\\
^Haube^AnzahlRestaurants^
|0|1|
|1|1|
|2|2|


===Beispiel===
Gesucht ist eine SQL-Abfrage, die alle Restaurants (rnr und name) und die Anzahl der jeweils gebotenen Speisen ausgibt:
\\

^Relation Restaurant^^^^
^rnr^RName^Adr^Haube^
|1|McDonalds|Amstetten|0|
|2|McDonalds|Ybbs|0|
|3|Hollywood|Amstetten|1|
|4|Casa Venezia|Amstetten|2|
|5|grill.Bar|Amstetten|2|
|6|Schinakel|Grein|1|

^Relation Speise^^^
^rnr^SName^Preis^
|1|Hamburger|1|
|2|Hamburger|1|
|2|Cheeseburger|1,30|
|3|Hawaii|8,6|
|4|Hawaii|9|
|5|Salat|5|
|1|Salat|1,80|

<code sql>
SELECT r.rnr, r.name, COUNT(*) AS Anzahl
FROM Restaurant r, Speise s
WHERE r.rnr=s.rnr
GROUP BY r.rnr, r.name;
</code>

\\
**1. Schritt: JOIN **

|1|McDonalds|Amstetten|0|Hamburger|1|
|1|McDonalds|Amstetten|0|Salat|1,80|
|2|McDonalds|Ybbs|0|Hamburger|1|
|2|McDonalds|Ybbs|0|Cheeseburger|1,30|
|3|Hollywood|Amstetten|1|Hawaii|8,6|
|4|Casa Venezia|Amstetten|2|Hawaii|9|
|5|grill.Bar|Amstetten|2|Salat|5|

**2. Schritt: GROUP BY**

|1|McDonalds|Amstetten|0|Hamburger|1|
|1|McDonalds|Amstetten|0|Salat|1,80|

|2|McDonalds|Ybbs|0|Hamburger|1|
|2|McDonalds|Ybbs|0|Cheeseburger|1,30|

|3|Hollywood|Amstetten|1|Hawaii|8,6|

|4|Casa Venezia|Amstetten|2|Hawaii|9|

|5|grill.Bar|Amstetten|2|Salat|5|

**3. Schritt: COUNT (*) AS Anzahl (pro Gruppe) **
^rnr^RName^Anzahl^
|1|McDonalds|2|
|2|McDonalds|2|
|3|Hollywood|1|
|4|Casa Venezia|1|
|5|grill.Bar|1|


===Beispiel===

Gesucht sind alle Restaurants (rnr, name), welche mehr als 5 verschiedene (Tipp: Schlüsselwort DISTINCT) Speisen anbieten!

^Relation Restaurant^^^^
^rnr^RName^Adr^Haube^
|1|McDonalds|Amstetten|0|
|2|McDonalds|Ybbs|0|
|3|Hollywood|Amstetten|1|
|4|Casa Venezia|Amstetten|2|
|5|grill.Bar|Amstetten|2|
|6|Schinakel|Grein|1|

^Relation Speise^^^
^rnr^SName^Preis^
|1|Hamburger|1|
|2|Hamburger|1|
|2|Cheeseburger|1,30|
|3|Hawaii|8,6|
|4|Hawaii|9|
|5|Salat|5|
|1|Salat|1,80|


/*
<code sql>
SELECT r.rnr, r.name
FROM Restaurant r, Speise s
WHERE r.rnr=s.rnr
GROUP BY r.rnr, r.name
HAVING COUNT (DISTINCT s.name) > 5;
</code>


**1. Schritt: JOIN **

|1|McDonalds|Amstetten|0|Hamburger|1|
|1|McDonalds|Amstetten|0|Salat|1,80|
|2|McDonalds|Ybbs|0|Hamburger|1|
|2|McDonalds|Ybbs|0|Cheeseburger|1,30|
|3|Hollywood|Amstetten|1|Hawaii|8,6|
|4|Casa Venezia|Amstetten|2|Hawaii|9|
|5|grill.Bar|Amstetten|2|Salat|5|

**2. Schritt: GROUP BY**

|1|McDonalds|Amstetten|0|Hamburger|1|
|1|McDonalds|Amstetten|0|Salat|1,80|

|2|McDonalds|Ybbs|0|Hamburger|1|
|2|McDonalds|Ybbs|0|Cheeseburger|1,30|

|3|Hollywood|Amstetten|1|Hawaii|8,6|

|4|Casa Venezia|Amstetten|2|Hawaii|9|

|5|grill.Bar|Amstetten|2|Salat|5|

**3. Schritt: HAVING COUNT(DISTINCT s.name)>5**

^rnr^RName^

*/


===Beispiel===

Gesucht ist eine SQL-Abfrage, die die rnr und die Anzahl der Haube jener Restaurants angibt, deren teuerste Speise mehr als 8€ kostet. Weiters werden jeweils die Anzahl der Speisen als auch deren Durchschnittspreis ausgegeben.

^Relation Restaurant^^^^
^rnr^RName^Adr^Haube^
|1|McDonalds|Amstetten|0|
|2|McDonalds|Ybbs|0|
|3|Hollywood|Amstetten|1|
|4|Casa Venezia|Amstetten|2|
|5|grill.Bar|Amstetten|2|
|6|Schinakel|Grein|1|

^Relation Speise^^^
^rnr^SName^Preis^
|1|Hamburger|1|
|2|Hamburger|1|
|2|Cheeseburger|1,30|
|3|Hawaii|8,6|
|4|Hawaii|9|
|5|Salat|5|
|1|Salat|1,80|
|4|Hühnerstreifensalat|8,20|

/*

<code sql>
SELECT r.rnr, r.haube, COUNT (DISTINCT s.name) AS Anzahl, AVG(s.preis) AS AVGPreis
FROM Restaurant r, Speise s
WHERE r.rnr=s.rnr
GROUP BY r.rnr, haube
HAVING MAX (s.preis)>8;
</code>

**1. Schritt: JOIN **

|1|McDonalds|Amstetten|0|Hamburger|1|
|1|McDonalds|Amstetten|0|Salat|1,80|
|2|McDonalds|Ybbs|0|Hamburger|1|
|2|McDonalds|Ybbs|0|Cheeseburger|1,30|
|3|Hollywood|Amstetten|1|Hawaii|8,6|
|4|Casa Venezia|Amstetten|2|Hawaii|9|
|4|Casa Venezia|Amstetten|2|Hühnerstreifensalat|7,20|
|5|grill.Bar|Amstetten|2|Salat|5|

**2. Schritt: GROUP BY**

|1|McDonalds|Amstetten|0|Hamburger|1|
|1|McDonalds|Amstetten|0|Salat|1,80|

|2|McDonalds|Ybbs|0|Hamburger|1|
|2|McDonalds|Ybbs|0|Cheeseburger|1,30|

|3|Hollywood|Amstetten|1|Hawaii|8,6|

|4|Casa Venezia|Amstetten|2|Hawaii|9|
|4|Casa Venezia|Amstetten|2|Hühnerstreifensalat|7,20|

|5|grill.Bar|Amstetten|2|Salat|5|


**3. Schritt: HAVING MAX(s.preis)>8**

|3|Hollywood|Amstetten|1|Hawaii|8,6|

|4|Casa Venezia|Amstetten|2|Hawaii|9|
|4|Casa Venezia|Amstetten|2|Hühnerstreifensalat|7,20|

**4. Schritt: SELECT r.rnr, r.name, COUNT (DISTINCT s.name) AS Anzahl, AVG(s.preis) AS AVGPreis **

^rnr^RName^Anzahl^Preis^
|3|Hollywood|1|8,6|
|4|Casa Venezia|2|8,1|

*/