Versicherung - Die Datenbank

Beschreibung der Datenbank

Mitarbeiter sind eindeutig identifiziert durch ihre Mitarbeiternummer (MNR). Es wird auch noch der Vorname (VNAME), der Nachname (NNAME), die Sozialversicherungsnummer (SVNR) und das Geschlecht (GESCHLECHT) gespeichert. Optional kann für gekündigte Mitarbeiter ein Kündigungsdatum (DATUM) gespeichert werden. Es gibt zwei unterschiedliche Typen von Mitarbeitern: Vertreter und Kundenbetreuer. Bei Vertretern ist bekannt, welchen Dienstwagen (DIENSTWAGEN) sie benutzen. Bei Kundenbetreuern sind die Telefonnummer (TELEFON) und E-Mail-Adresse (EMAIL) bekannt. Alle Mitarbeiter besitzen einen anderen Mitarbeiter als Vorgesetzten, ausgenommen jene, die sich in der obersten Hierarchieebene befinden. Die Produkte der Versicherung sind eindeutig identifiziert durch die Produkt-ID (PID). Zusätzlich werden der Titel (TITEL), der Preis (PREIS) und die maximal gedeckte Schadenshöhe (MAXSCHADEN) eines jeden Produkts in der Datenbank abgelegt. Ein Produkt gehört jeweils zu einer Kategorie, wobei einer Kategorie beliebig viele Produkte zugeordnet werden können. Bei einer Kategorie sind folgende Eigenschaften bekannt: Eine global einzigartige Kategorie-ID (KATID) sowie der Titel der Kategorie (TITEL). Ein Vertreter kann nun beliebig viele Produkte vertreiben und erhält dazu jeweils die Vorgabe (VORGABE), welche Menge er von einem beliebigen Produkt verkaufen sollte. Kunden sind eindeutig identifiziert durch ihre Kundennummer (KNR). Es werden bei Kunden weiters der Vorname (VNAME), der Nachname (NNAME) und das Geburtsdatum (GEBDAT) hinterlegt. Kunden können nun im Zuge von Aufträgen Produkte von Vertretern erwerben. Ein Auftrag ist identifiziert durch den jeweiligen Kunden und das gewählte Produkt, sowie das Kaufdatum (DATUM). Zusätzlich wird noch gespeichert, in welcher Höhe an Prozenten der Kunde einen Rabatt (RABATT) auf den Auftrag bekommt. Ein Kunde kann mehrere Bankverbindungen haben, die eindeutig durch die Bankleitzahl (BLZ) als auch durch die Kontonummer (KNR) identifiziert sind. Bei Bankverbindungen wird zusätzlich der Name der Bank (BANK) abgespeichert und es ist zu bemerken, dass eine Bankverbindung immer zu exakt einem Kunden gehört. Ein Kundenbetreuer berät und unterstützt jeweils eine beliebige Anzahl von Kunden im Umfeld einer ebenfalls beliebigen Anzahl an Themen. Bei Themen sind jeweils der eindeutige Titel (TITEL) sowie auch eine Beschreibung (BESCHREIBUNG) bekannt. Bei auftretenden Schadensfällen können Kunden eine Schadensmeldung einreichen, die identifiziert ist durch eine eindeutige ID. Weiters werden eine Detailinformation (INFO), das Datum (DATUM) und der vermutliche Betrag des Schadens (BETRAG) bei der Schadensmeldung gespeichert. Zur Abwicklung eines Schadensfalles wird noch dessen Bearbeitung in der Datenbank gespeichert. Eine Bearbeitung ist hierbei eindeutig identifiziert durch den Schadensfall, dem sie zugeordnet ist. Als Eigenschaften einer Bearbeitung werden sowohl das Datum (DATUM), eine Anmerkung (ANMERKUNG) als auch der tatsächlich anerkannte Schadensbetrag (BETRAG) gesichert. Für die Nachverfolgung der Bearbeitung muss auch die verwendete Bankverbindung und der für die jeweilige Bearbeitung zuständige Mitarbeiter persistiert werden.

1)

Geben Sie jene aktiven (d.h. nicht gekündigten) Vertreter aus, deren Nachname mit 'Bau' beginnt. Ordnen Sie die Ergebnisse absteigend nach der Sozialversicherungsnummer.

		
			SELECT * FROM Mitarbeiter
			WHERE mnr IN (SELECT mnr FROM Vertreter)
			AND mnr NOT IN (SELECT mnr FROM gekuendigt)
			AND nname LIKE 'Bau%'
			ORDER BY svnr DESC;	
	
		

		
			SELECT Mitarbeiter.*
			FROM Mitarbeiter JOIN Vertreter ON Vertreter.mnr = Mitarbeiter.mnr
			WHERE Mitarbeiter.mnr NOT IN (SELECT mnr
			                              FROM gekuendigt)
			AND nname LIKE 'Bau%'
			ORDER BY svnr DESC;	
	
		

		
			SELECT Mitarbeiter.* 
			FROM Mitarbeiter JOIN Vertreter ON Vertreter.mnr = Mitarbeiter.mnr
			WHERE NOT EXISTS (SELECT mnr
			                  FROM gekuendigt
			                  WHERE gekuendigt.mnr = Mitarbeiter.mnr)
			AND nname LIKE 'Bau%'
			ORDER BY svnr DESC;	
	
		

2)

Geben Sie Kundennummer, Vor- und Nachname aller Kunden aus, die vor dem 01-01-1965 geboren wurden und noch nie eine Schadensmeldung eingereicht haben. Verwenden Sie dazu die Funktion TO_DATE und folgendes Datumsformat: DD-MM-YYYY. Ordnen Sie die Ergebnisse aufsteigend nach dem Geburtsdatum.

		
			SELECT knr, vname, nname
			FROM Kunde WHERE gebdat < TO_DATE('01-01-1965', 'DD-MM-YYYY')
			AND knr NOT IN (SELECT kunde
			                FROM Schadensmeldung)
			ORDER BY GebDat ASC;	
	
		

		
			SELECT knr, vname, nname
			FROM Kunde
			WHERE gebdat < TO_DATE('01-01-1965', 'DD-MM-YYYY')
			AND NOT EXISTS (SELECT kunde
			                FROM Schadensmeldung
			                WHERE kunde = knr)
			ORDER BY GebDat ASC;	
	
		

3.1)

Geben Sie die Anzahl aller (unterschiedlichen) Themen aus.

		
			SELECT COUNT(*) as anzahl
			FROM Thema;	
	
		

3.2)

Geben Sie die Mitarbeiternummer jener Kundenbetreuer aus, die Themen betreuen, sowie die Anzahl aller unterschiedlichen Themen, die sie betreuen.

		
			SELECT mnr, COUNT(DISTINCT thema) as anzahl
			FROM Kundenbetreuer JOIN betreut ON betreut.betreuer = Kundenbetreuer.mnr
			GROUP BY Kundenbetreuer.mnr;	
	
		

3.3)

Geben Sie die Mitarbeiternummer, Vor- und Nachnamen jener Kundenbetreuer aus, die Themen betreuen, sowie die Anzahl aller unterschiedlichen Themen, die sie betreuen.

		
			SELECT Mitarbeiter.mnr, vname, nname, COUNT(DISTINCT thema) as anzahl
			FROM Kundenbetreuer JOIN Mitarbeiter ON Mitarbeiter.mnr = Kundenbetreuer.mnr JOIN betreut ON betreut.betreuer = Kundenbetreuer.mnr
			GROUP BY Mitarbeiter.mnr, vname, nname;	
	
		

		
			SELECT mnr, vname, nname, COUNT(DISTINCT thema) as anzahl
			FROM Kundenbetreuer NATURAL JOIN Mitarbeiter JOIN betreut ON betreut.betreuer = mnr
			GROUP BY mnr, vname, nname;	
	
		

3.4)

Geben Sie die Mitarbeiternummer, Vor- und Nachnamen der Mitarbeiter aus, die am meisten unterschiedliche Themen betreuen.

		
			SELECT mnr, vname, nname
			FROM Kundenbetreuer NATURAL JOIN Mitarbeiter JOIN betreut ON betreut.betreuer = mnr
			GROUP BY mnr, vname, nname
			HAVING COUNT(DISTINCT thema) = (SELECT MAX(themen)
			                                FROM (SELECT COUNT(DISTINCT thema) themen
			                                      FROM Kundenbetreuer JOIN betreut ON betreut.betreuer = Kundenbetreuer.mnr
			                                      GROUP BY Kundenbetreuer.mnr) as tmp);	
	
		

		
			SELECT mnr, vname, nname
			FROM Kundenbetreuer NATURAL JOIN Mitarbeiter JOIN betreut ON betreut.betreuer = mnr
			GROUP BY mnr, vname, nname
			HAVING COUNT(DISTINCT thema) >= ALL(SELECT COUNT(DISTINCT thema)
			                                    FROM Kundenbetreuer JOIN betreut ON betreut.betreuer = Kundenbetreuer.mnr
			                                    GROUP BY Kundenbetreuer.mnr);	
	
		

4.1)

Geben Sie Kundennummer und Nachname der ältesten Kunden aus.

		
			SELECT knr, nname
			FROM Kunde
			WHERE gebdat <= ALL(SELECT gebdat
			                    FROM Kunde);	
	
		

		
			SELECT knr, nname
			FROM Kunde
			WHERE gebdat = (SELECT MIN(gebdat)
			                FROM Kunde);	
	
		

4.2)

Geben Sie Kundennummer und Nachname der ältesten Kunden aus, die zu mindestens einem Thema betreut werden.

		
			SELECT DISTINCT knr, nname
			FROM Kunde JOIN betreut ON betreut.kunde = Kunde.knr
			WHERE gebdat <= ALL(SELECT gebdat
			                    FROM Kunde JOIN betreut ON betreut.kunde = Kunde.knr);	
	
		

		
			SELECT DISTINCT knr, nname
			FROM Kunde JOIN betreut ON betreut.kunde = Kunde.knr
			WHERE gebdat = (SELECT MIN(gebdat)
			                FROM Kunde JOIN betreut ON betreut.kunde = Kunde.knr);	
	
		

		
			SELECT DISTINCT knr, nname
			FROM Kunde
			WHERE knr in (SELECT kunde FROM betreut)
			AND gebdat <= ALL(SELECT gebdat
			                    FROM Kunde
			                    WHERE knr IN (SELECT kunde
			                                  FROM betreut));	
	
		

		
			SELECT DISTINCT knr, nname
			FROM Kunde
			WHERE knr IN (SELECT kunde FROM betreut)
			AND gebdat = (SELECT MIN(gebdat)
			              FROM Kunde
			              WHERE knr IN (SELECT kunde FROM betreut));	
	
		

5)

Geben Sie Mitarbeiternummer und Nachname aller Mitarbeiter aus. In einer zusätzlichen Spalte soll vermerkt werden, um welchen Typ von Mitarbeiter es sich handelt, also entweder 'Vertreter' oder 'Kundenbetreuer'. Sie können davon ausgehen, dass ein Mitarbeiter immer nur maximal einem Typ zugeordnet ist. Ist die betreffende Person weder Vertreter noch Kundenbetreuer, soll der Wert der Spalte 'Nicht zugeordnet' lauten.

		
			SELECT mnr, nname, 'Kundenbetreuer'
			FROM Kundenbetreuer NATURAL JOIN Mitarbeiter
			UNION
			SELECT mnr, nname, 'Vertreter'
			FROM Vertreter NATURAL JOIN Mitarbeiter
			UNION
			SELECT mnr, nname, 'Nicht zugeordnet'
			FROM Mitarbeiter
			WHERE mnr NOT IN (SELECT mnr FROM Kundenbetreuer UNION SELECT mnr FROM Vertreter);	
	
		

6)

Geben Sie alle Kunden aus, die Produkte aus jeder Kategorie gekauft haben.

		
			SELECT * FROM Kunde WHERE NOT EXISTS
			    (SELECT * FROM Kategorie WHERE katid NOT IN
			        (SELECT Produkt.katid FROM Auftrag JOIN Produkt ON Produkt.pid = Auftrag.pid WHERE Auftrag.knr = Kunde.knr));	
	
		

		
			SELECT * FROM Kunde
			WHERE (SELECT COUNT(DISTINCT katid)
			      FROM Auftrag JOIN Produkt ON Produkt.pid = Auftrag.pid
			      WHERE Auftrag.knr = Kunde.knr) = (SELECT COUNT(*)
			                                        FROM Kategorie);	
	
		

7.1)

Geben Sie Information, Datum und Betrag aller Schadensmeldungen aus, die der Kunde mit dem Nachnamen 'Steinreich' eingereicht hat.

		
			SELECT info, datum, betrag
			FROM Schadensmeldung JOIN Kunde ON Kunde.knr = Schadensmeldung.kunde
			WHERE nname = 'Steinreich';	
	
		

7.2)

Geben Sie die Kundennummer, Nachname und Gesamtschadenssumme aller Schadensmeldungen jedes Kunden aus. Wenn kein Schadensfall vorliegt, soll die Schadenssumme 0 betragen. (Verwenden Sie dazu die Funktion COALESCE.)

		
			SELECT Kunde.knr, nname, SUM(COALESCE(betrag, 0)) as summe
			FROM Schadensmeldung RIGHT JOIN Kunde ON Kunde.knr = Schadensmeldung.kunde
			GROUP BY Kunde.knr, nname;	
	
		

		
			SELECT Kunde.knr, nname, COALESCE((SELECT SUM(betrag)
			                                    FROM Schadensmeldung
			                                    WHERE kunde = knr), 0) as summe
			FROM Kunde;	
	
		

7.3)

Geben Sie eine Liste ALLER Kunden aus zusammen mit der Schadenssumme, die für Autounfälle eingereicht wurden.

		
			SELECT Kunde.knr, nname, SUM(COALESCE(betrag, 0)) as summe
			FROM Schadensmeldung RIGHT JOIN Kunde ON Kunde.knr = Schadensmeldung.kunde AND info = 'Autounfall'
			GROUP BY Kunde.knr, nname;	
	
		

		
			SELECT Kunde.knr, nname, COALESCE((SELECT SUM(betrag)
			                                   FROM Schadensmeldung
			                                   WHERE kunde = knr
			                                   AND info = 'Autounfall'), 0) as summe
			FROM Kunde;	
	
		

8)

Geben Sie für Kunden die Kundennummer und die Gesamtkosten für alle gekauften Versicherungsprodukte aus. Es sollen nur die Kunden aufscheinen, die durchschnittlich weniger als 250 Geldeinheiten Umsatz mit ihren gekauften Produkten gemacht haben. Beachten Sie in diesem Beispiel den gewährten Rabatt. (Doppelkäufe werden hierbei ebenfalls gezählt. Wurden keine Produkte gekauft, sollen die Gesamtkosten 0 betragen.)

		
			SELECT Kunde.knr, SUM(COALESCE(preis * ((100 - rabatt) / 100), 0)) as kosten
			FROM Kunde LEFT JOIN Auftrag ON Auftrag.knr = Kunde.knr LEFT JOIN Produkt ON Produkt.pid = Auftrag.pid
			GROUP BY Kunde.knr
			HAVING AVG(COALESCE(preis * ((100 - rabatt) / 100), 0)) < 250;	
	
		

		
			SELECT knr, COALESCE((SELECT SUM(preis * ((100 - rabatt) / 100))
			                            FROM Auftrag NATURAL JOIN Produkt
			                            WHERE Auftrag.knr = Kunde.knr), 0) as kosten
			FROM Kunde
			WHERE COALESCE((SELECT AVG(preis * ((100 - rabatt) / 100))
			                    FROM Auftrag NATURAL JOIN Produkt
			                WHERE Auftrag.knr = Kunde.knr), 0) < 250;	
	
		

9)

Finden Sie die am häufigsten verwendeten Nachnamen aller Mitarbeiter aus.

		
			SELECT nname
			FROM Mitarbeiter
			GROUP BY nname
			HAVING COUNT(nname) >= ALL(SELECT COUNT(nname)
			                           FROM Mitarbeiter
			                           GROUP BY nname);	
	
		

		
			SELECT nname
			FROM Mitarbeiter
			GROUP BY nname
			HAVING COUNT(nname) = (SELECT MAX(anzahl)
			                       FROM (SELECT COUNT(nname) anzahl
			                             FROM Mitarbeiter
			                             GROUP BY nname) as tmp);	
	
		

10)

Geben Sie die Mitarbeiternummer aller nicht gekündigten Vertreter aus, die schon mehr als 20% aller Produkte vertrieben haben.

		
			SELECT mnr
			FROM Vertreter
			WHERE mnr NOT IN (SELECT mnr FROM gekuendigt)
			AND (SELECT COUNT(DISTINCT produkt)
			     FROM vertreibt
			     WHERE vertreter = mnr) > (SELECT COUNT(pid)/5
			                               FROM produkt);