/* 
 * Im ersten Teil geht es um einfache Abfragen, insbesondere um 
 * ° WHERE-Klauseln mit Vergleichen (Kap. 7.1.3)
 * ° Vergleiche von NULL-Werten (Abschnitt 5)
 * ° das Mischen logischer Operatoren
 *   (Operatorpräzedenz – Abschnitt 3)
 * ° Sortieren von Ausgabedaten (Kap. 7.1.6)
 * ° Verwendung spezieller Operatoren wie 
 *   BETWEEN oder IN (7.1.3.6)
 */
 
use AdventureWorksLT2012 ;
-- bzw. der Name der kleinen Übungsdatenbank, den Sie ihr bei
-- der Installation gegeben haben.

-----------------------------------------------------------------
-- WHERE-Klausel
-- 7.10) Aufträge, deren netto-Rechnungssumme mindestens 1000
--       beträgt

-- (24 Zeilen)

-----------------------------------------------------------------
-- 7.11) Leere Felder: NULL (Kap. 7.1.3.5)
--       Produkte ohne Gewichtsangabe

-- (97 Zeilen)

-----------------------------------------------------------------
-- 7.12) Mehrere Kriterien; AND und OR mischen; Sortieren
-- Rote und schwarze Produkte der Kategorie 6, Listenpreis über
-- 600, Größe mindestens 50, sortiert nach ProductNumber.

-- (18 Zeilen)

-----------------------------------------------------------------
-- 7.13) Projektion von Spalten; Sortieren; NULL-Behandlung;
--       Unterdrückung von Doubletten
-- Eine alphabetische Telefonliste (Zuname, Vorname,
-- Telefonnummer) der Kunden, die nach Zunamen und bei gleichen
-- Zunamen nach Vornamen sortiert ist. Es sollen keine doppelten
-- Zeilen auftreten

-- (440 Zeilen)	

-----------------------------------------------------------------
-- 7.14) Rechnungen mit einem Gesamtbetrag (TotalDue) von 
--       100 … 10000
-- Tipp: BETWEEN (statt 2 Kriterien mit AND) – siehe Kap. 7.1.3.6

-- (15 Zeilen)	

-----------------------------------------------------------------
-- 7.15) Rote, grüne und schwarze Produkte der Kategorie 6
-- Tipp: IN (statt 3 Kriterien mit OR) – siehe Buch Kap. 7.1.3.3

--(34 Zeilen)

--===============================================================
/*
 * Im zweiten Teil fragen wir ›Wildcards‹ ab, also einfache
 * Muster.Dabei können eine beliebige Zeichenfolge durch ein %,
 * genau ein beliebiges Zeichen durch _ dargestellt werden.
 * Allerdings darf man nicht mit = vergleichen, sondern muss
 * LIKE benützen!
 *
 * Details in Kapitel 7.1.3.4
 */

-----------------------------------------------------------------
-- 7.16) Alle Beschreibungen ("description"), in denen 'chrom'
--       vorkommt

-- (2 Zeilen)	

-----------------------------------------------------------------
-- 7.17) Alle "ProductModels", deren Name die mit 'ML" beginnt

-- (23 Zeilen)

-----------------------------------------------------------------
-- 7.18) Alle grauen Produkte. Im Englischen heißt grau 'gray',
--       im Amerikanischen 'grey' und wir wissen nicht, wie die
--       Datenbank befüllt ist.

-- (1 Zeile)	

-----------------------------------------------------------------
-- 7.19) Alle Städte mit genau 5 Buchstaben

-- (13 Zeilen)

--===============================================================
/*
 * Der dritte und letzte Teil beschäftigt sich mit JOINS
 * (Kap. 7.1.5)
 * Außerdem werden Sie Doubletten aus Ergebnissen entfernen
 * müssen (distinct, 7.1.7)
 */


-- Join 2er Tabellen:

-----------------------------------------------------------------
-- 7.20) Basierend auf dem LISTENpreis: geben Sie die Produkte
--       und deren Umsätze im jeweiligen Auftrag (NICHT
--       aggregiert!) sortiert nach fallenden Umsätzen an. Die
--       Produkte müssen aktuell noch im Angebot sein.

-- Tipp: Sie werden den Umsatz *berechnen* müssen.

-- Tipp: Ein Produkt ist dann nicht mehr im Angebot, wenn
--       DiscontinuedDate in der Vergangenheit liegt.

-- Tipp: Das aktuelle Datum erhalten Sie mit der Funktion
--       getdate(), die sie einfach in einem Vergleich verwenden
--       können.

-- (542 Datensätze)

-----------------------------------------------------------------
-- 7.21) Zeigen Sie jene Kunden, die niemals etwas gekauft haben.
--       Benützen Sie dazu KEIN Subselect!

-- Tipp: ein OUTER JOIN liefert Ihnen *alle* Datensätze einer
-- Tabelle, auch, wenn aus der anderen keine passenden da sind.
-- Den Rest können Sie mit einer WHERE-Klausel erledigen.

-- (815 Datensätze)

-- Gegenprobe (ganz ohne Join): welchen Kundennummern wurde
-- jemals etwas verkauft?

-- (32 Datensätze) Macht in Summe 847.

-----------------------------------------------------------------
-- 7.22) Die Namen jener Produkte, für die jemals mehr als 10%
--       Rabatt gewährt wurde.

-- Tipp: teilen Sie so etwas in zwei Schritte

-- 1. Die Liste der ProductIDs (geht ganz ohne join):

-- 2. Das kann man jetzt fast unverändert in einem Join
--    verwenden:

-- (5 Datensätze)
	

-- Joins über 3 Tabellen

-----------------------------------------------------------------
-- 7.23) ProductModel steht mit ProductDescription in m:n-
--       Beziehung. Projizieren Sie ProductModelID, Name,
--       Katalogbeschreibung und englische
--       Produktbeschreibung für die Produkte, die eine
--       Katalogbeschreibung haben, sortiert nach Produktnamen

-- Tipp: denken Sie daran, dass eine m:n-Beziehung immer über
--       eine Zwischentaqbelle aufgelöst ist!

-- Tipp: die Sprache der Beschreibung findet sich in "Culture"
--       der Zwischentablle

-- ( 6 Datensätze )
		
-----------------------------------------------------------------
-- 7.24) Eine Liste aller Vor- und Zunamen jener Personen, die in
--       Montreal leben.

-- (12 Datensätze)

-----------------------------------------------------------------
-- 7.25) Ein Join einer Tabelle mit sich selbst!
--       Wie heißen die Produktkategorien, die 'Components' als
--       Parentkategorie haben?

-- Tipp: WENN wir eine Tabelle comp mit dem Komponenten und eine
--       parent mit den Parentkategorien HÄTTEN, wäre es ein
--       simpler join! Durch Tabellenaliase können Sie die
--       Situation genau so behandeln!

--(14 Datensätze)		

-----------------------------------------------------------------
-- 7.26) Ein Join-Monster über 7 Tabellen (kommt oft vor)

-- Vor- und Zunamen von Kunden und ENGLISCHE
-- Produktbeschreibungen von jenen Kunden, deren Datensätze
-- früher geändert wurden als die Produktbeschreibung.

--(289 Datensätze)