/*
 * Aggregatfunktionen und Gruppen
 * Abfragen kombinieren (union [all], except, intersect)
 * Funktionen
 */
 
use AdventureWorksLT2012 ;
-- bzw. der Name der kleinen Übungsdatenbank, den Sie ihr bei
-- der Installation gegeben haben.

--===============================================================
-- Aggegate.
-- Sehr oft treten Aggregatfunktionen zusammen mit Subselects
-- auf. Denken Sie and die ›Standardsituationen‹ für Subselects.

-----------------------------------------------------------------
-- 7.27) Wie ist die geographische Verteilung unserer Kunden: 
--       Geben Sie pro Postleitzahl (PostalCode) an, wie viele
--       Kunden jeweils dort wohnen

-- Tipp: das geht mit einer einfachen Gruppierung + Aggregat.

-- (303 Datensätze)

-----------------------------------------------------------------
-- 7.28.) Zeigen Sie den kleinsten, den größten und den
--        durchschnittlich gewährten Rabatt (discount) auf
-- Produkte, bei denen ein Rabatt gewährt wurde. 
-- (Der Rabatt wird dargestellt als Zahl zwischen 0 und 1)

-- Tipp: Hier brauchen Sie nur die entsprechenden
--       Aggregatfunktionen

-- (1 Datensatz)

-----------------------------------------------------------------
-- 7.29) Wie 7.28. Zeigen Sie in einer zusäzlichen Spalte "totAvg"
--       den Durchschnitt des gewährten Rabatts über *alle*
--       Verkäufe (auch über die, in denen der Rabatt 0 war)

-- Tipp: Das Statement aus 7.28 ist ja schon sehr in der Nähe! Blöd
--       ist, dass die where-Klausel Datensätze wegblendet, die
--       wir brauchen. Mit einem Subselect in der 
--       Auswahlliste können Sie das umgehen!

-- (1 Datensatz)

-----------------------------------------------------------------
-- 7.30) Liefern Sie dieselbe Information wie für 7.29, aber für
--       jedes Produkt getrennt. Der durchschnittliche Rabatt
-- soll dabei für jedes Produkt gebildet werden (also für
-- jedes Produkt eine andere Zahl liefern).

-- Tipp: "Für jedes Produkt getrennt" bedeutet, dass Sie
--       eineGruppierung brauchen. Beachten Sie weiters, dass Sie
--       das Subselect aus 7.29 korrelieren müssen, weil Sie
--       sonst den Gesamtdurchschnitt erhalten, also in allen
--       Zeilen dieselbe Zahl stehen würde.

-- Anm.: In diesen Daten wurden für die gleichen Produkte immer
--       wieder die gleichen Rabatte gewährt. Die Spalten min,
-- max und avg werden daher in allen Zeilen dieselben Zahlen
-- enthalten. Die Spalte aus dem Subselect zeigt eine etwas
-- niedrigere Zahl, weil im äußeren Select nur die Zeilen zu
-- berücksichtigen sind, wo überhaupt ein Rabatt gewährt wurde
-- (siehe Bsp 3), ins Subselect aber /alle/ Verkäufe des Produkts
-- eingehen, egal, ob es einen Rabatt gab oder nicht.

-- (20 Datensätze)

-----------------------------------------------------------------
-- 7.31) Wie oft haben die einzelnen Kunden bei uns eingekauft?
--       Zeigen Sie Vor- und Nachname und benützen Sie zur Lösung
--       ein korreliertes Subselect in der Feldliste des select

-- (847 Datensätze)
-- Anm.: die meisten gar nicht, also 0 mal.

-----------------------------------------------------------------
-- 7.32) Stammkunden: Welche Kunden haben mindestens 3x
--       eingekauft?

-- Tipp: wie 7.31. Sie können allerdings den Aliasnamen des
--       Subselect *nicht* in der where-Klausel benützen, sondern
--       müssen das ganze Subselect wiederholen. (Der MS SQL
--       Server kann in der Where-Klausel keine Aliases auflösen)

-- (0 Datensätze)

-----------------------------------------------------------------
-- 7.33) Nochmal wie 7.31.: Wie oft haben die einzelnen Kunden
--       bei uns eingekauft? Zeigen Sie nur Kunden, die
-- eingekauft haben. Benützen Sie zu Lösung ein NICHT
-- korreliertes Subselect in der from-Klausel

-- Tipp: lassen Sie sich ausgeben, welche Kunden-IDs wie oft
--       eingekauft haben und benutzen das dann als Subselect.

-- (32 Datensätze)

-----------------------------------------------------------------
-- 7.34) Die Namen (Vor- und Zuname) der Kunden mit mehr als 1
--       Adresse.
-- Tipp: wieder schrittweise aufbauen: welche KundenIDs haben
-- mehr als 1 Adresse? Das als Subselect benutzen.

-- (10 Datensätze)

-----------------------------------------------------------------
-- 7.35) Welche Produkte wurden unterdurchschnittlich oft
--       verkauft?
-- (Anm.: Schwierig! Man muss mehrere Subselects staffeln!)

-- Vorgangsweise: Frage in handhabbare Teile aufbrechen:
-- 1. Wie oft wurden Produkte verkauft?
	
-- 2. Was ist der Durchschnitt?

-- Lösung: 1. & 2. kombiniereen

-- (93 Datensätze)

-----------------------------------------------------------------
-- 7.36) Zu allen Listenpreisen: Wie lautet der jeweils
--       nächsthöhere in der Datenbank und die Differenz zwischen
--       beiden? Welche ist die höchste Differenz?

-- Anm.: Sehr schwierig! Die Aufgabe muss in mehreren Denk-
-- schritten gelöst werden es ist so gut wie unmöglich, das 
-- ›richtige‹ Statement einfach aufzuschreiben!

-- Vorgangsweise:
-- 1. Die Listenpreise sind einfach (unterdrücken Sie
--    Doubletten!):

-- 2. Den jeweils nächsthöheren kann man mit einem korrelierten
--    Subselect in der Feldliste dazu projizieren. (Versuchen Sie
--    dazu erst einmal den nächsthöheren Preis zu einer
--    beliebigen Zahl aus der Datenbank zu holen.)

-- 3. Jetzt wäre es verlockend, noch eine weitere Spalte
--    ›nextPrice – ListPrice‹ zu projizieren – aber leider geht
--    das nicht. Wiederholt man statt dessen das Subselect, so
--    klappt das (*kein* Performancenachteil. Das Subselect
--    wird nur 1x ausgeführt). Sortierung auf der Differenz (hier
--    *kann* man den Spaltenalias angeben!), bringt die größte
--    Differenz nach oben:

-- (99 Datensätze)

--===============================================================
-- Mehrere Abfragen (UNION / EXCEPT / INTERSECT )
-- Funktionen

-----------------------------------------------------------------
-- 7.37) ProduktIDs, für die niemals ein Rabatt gewährt wurde. 
--       Produkte, die gar nie verkauft wurden, sollen in der
-- Liste aufscheinen. Verwenden Sie ›except‹ statt eines
-- Subselect.

-- (275 Datensätze)

-----------------------------------------------------------------
-- 7.38) ProduktIDs, für die niemals ein Rabatt gewährt wurde. 
--      Produkte, die gar nie verkauft wurden, sollen in der
--      Liste NICHT aufscheinen. Verwenden Sie auch hier kein
--      Subselect.

-- (122 Datensätze)

-----------------------------------------------------------------
-- 7.39) Produkte mit mehr als 1000g, sofern sie jemals 3x oder
--       öfter in einem einzelnen Auftrag bestellt wurden.
--       Benutzen Sie INTERSECT.

-- (59 Datensätze)	

-----------------------------------------------------------------
-- 7.40) Adressen, die unbenützt sind

-- Tipp: Adressen können Kunden zugeordnet sein (wie
--       CustomerAddress) oder als Lieferadressen auftauchen
--       (SalesOrderHeader.ShipToAddressID, BillToAddressID)
--       Die vollständige Liste aller IDs erhält man also
--       ziemlich einfach als union, das diese drei einzeln
--       auflistet und vereinigt. Das kann man dann in einem 
--       Subselect benützen.

-- Tipp: Die Situation entspricht der Standardsituation für
--       Subselects: für die Nicht-Benützung von Adressen haben
--       wir keine Datensätzte, nur für die Benützung.

-- Union in einem Subselect

-- (33 Datensätze)

--===============================================================
-- Stringfunktionen

-----------------------------------------------------------------
-- 7.41) Wie lang sind die Produktnamen?

-- (295 Datensätze)

-----------------------------------------------------------------
-- 7.42) An welcher Stelle haben Namen der ProduktModelle das
--       erste Leerzeichen, wenn es eines gibt

-- Tipp: die Stringfunktion charindex() fragt nach dem Vorkommen
--       eines BUchstaben in einem String: 
--       charindex ( 'a', 'Datenbank' ) = 2; 
--       charindex ( 'x', 'Datenbank' ) = 0.

-- (105 Datensätze)

-----------------------------------------------------------------
-- 7.43) Bei welchen Kunden ist der letzte Buchstabe des
--       Nachnamens gleich mit dem ersten des Vornamens?

-- Tipp: Die Funktionen left() und right() liefern die
--       Stringanfänge und -enden.
--       left ( 'Datenbank', 5) = 'Daten'
--       right ( 'Datenbank', 4) = 'bank'

-- (24 Datensätze)

-----------------------------------------------------------------
-- 7.44) Bei welchen Kunden ist der vorletzte Buchstabe des
--       Nachnamens gleich mit dem zweiten des Vornamens?

-- Tipp: die Funktion substring liefert Stringteile: 
--       substring ( 'Datenbank', 2, 3 ) = 'ate'

-- (86 Datensätze)

-----------------------------------------------------------------
-- 7.45) Das erste Wort der Produktnamen in einer eigenen Spalte,
--       den Rest des Produktnamens in einer weiteren.
-- (Schwierig!)

-- Tipp: Suchen Sie das Blank (charindex()), dann können sie mit
--       left() und right() den linken und rechten Teil heraus
--       lösen.

-- Tipp: Behandeln Sie die Produktnamen, die ein Blank enthalten
--       in einer Abfrage und die, die kein Bklank enthalten in
--        einer zweiten. Setzen Sie diese beiden Abfragen dann
--        mit einem UNION zum Ergebnis zusammen.

-- (295 Datensätze)

-----------------------------------------------------------------
-- 7.46) Projizieren Sie { AdressID, AddressLine1, PostalCode,
--       City } jener Adressen, die KEINE Kundenadressen sind.

-- Tipp: Das ist eine ›Standardsituation 1‹ für Subselects:
--       Information, für die es keine Datensätze gibt.

-- 1. Welches sind die Kundenadressen?

-- 2. Gib die anderen aus:

-- (33 Datensätze)

-----------------------------------------------------------------
-- 7.47) Listen Sie die Kundendaten plus einer zusätzlichen
--       Spalte mit der Anzahl der Rechnungen, die für den
--  jeweiligen Kunden ausgestellt wurde. Sortieren Sie nach
--  absteigender Anzahl der Rechnungen, Nach- und Vorname)
       
-- Tipps:
-- ° Das ist eine ›Standardsituation 2‹: mehrere Fragen in einer.
-- ° Ein "Korreliertes Subselect" (Kapitel 7.1.12) in der
--   Feldliste des Select bringt Sie weiter!

-- 1. Wieviele Rechnungen hat ein Kunde erhalten? 
--    (Nehmen Sie eine beliebige CustomerID)

-- 2. Als korreliertes Subselect ›verbauen‹:

-- (847 Datensätze)

-----------------------------------------------------------------
-- 7.48) (Sehr schwierig!) Welche Kunden haben Bestellungen mit
--       überdurchschnittlich vielen Positionen?
--
-- Hier das Rezept, das Sie am besten Schritt für Schritt
-- abarbeiten. Gehen Sie nicht zum nächsten Schritt,
-- bevor Sie den aktuellen nicht gelöst haben:

-- 1. Die Anzahl von Positionen in jedem Auftrag. (Da wir
--    vorhaben, das als Subselect zu verbauen, brauchen wir die
--    SalesOrderID gar nicht zu projizieren)

-- 2. Die durchschnittliche Anzahl von Positionen aus 1.
--    (bereits als ein Subselect):

-- 3. Welche Kunden haben Aufträge mit mehr Positionen platziert?
--    Beachten Sie die Aggregatfunktion bei der CustomerID, damit
-- das Feld projiziert werden kann! Innerhalb einer SalesOrderID
-- haben alle Datesätze dieselbe CustomerID, sodass min()
-- funktioniert.

-- (13 Datensätze)