Kreditkarten sind eindeutig identifiziert durch ihre Kartennummer (KNR). Zusätzlich muss das Ablaufdatum (ABLAUFDATUM) und der Sicherheitscode (CVC) vermerkt sein. Ersetzt eine Karte eine ältere Karte, so wird gespeichert welche Karte ersetzt wird (jede Karte kann maximal eine andere Karte ersetzen und durch maximal eine andere Karte ersetzt werden), und ab wann die neue Karte gültig ist (AB). KundInnen haben eine eindeutige Kundennummer (KNR). Zusätzlich ist ihr Vor- und Nachname (VNAME, NNAME) und ihr Geburtsdatum (GEBDAT) bekannt, wobei die Kombination aus Vorname, Nachname und Geburtsdatum ebenfalls eindeutig ist. Außerdem muss für jede Kundin/jeden Kunden eine Kontonummer (IBAN) vorhanden sein. Jede Kreditkarte lautet auf genau eine Kundin/einen Kunden. Partner der Kreditkartenfirma sind sowohl durch ihren Markennamen (MNAME) eindeutig identifiziert, als auch durch ihre Partner-ID (PID). Bei Partnern wird zwischen Vertriebspartnern und Bezahlpartnern unterschieden. Vertriebspartner vergeben Kreditkarten, wobei jede Karte von genau einem Vertriebspartner vergeben werden muss. Für Vertriebspartner wird neben dem Preis (PREIS) einer Karte auch der Prozentsatz (ANTEIL) gespeichert, den der Vertriebspartner von jedem mit der Karte getätigten Umsatz erhält. Bezahlpartner sind Unternehmen welche Kreditkarten von ``Schuldenfroh'' akzeptieren. Für jeden Bezahlpartner wird der Anteil des Umsatzes gespeichert der als Gebühr abgeführt werden muss (GEBUEHR). Manche Vertriebspartner gewähren einen Rabatt bei Zahlungen mit bestimmten Bezahlpartnern. Es wird vermerkt, welcher Vertriebspartner bei welchem Bezahlpartner welchen Rabatt (PROZENT) gewährt. Jeder Bezahlpartner erhält mindestens einen Account. Ein Account ist eindeutig identifiziert durch den Bezahlpartner sowie durch den Benutzernamen (BENUTZER). Zusätzlich wird für jeden Account das Passwort (PW), sowie sämtliche Loginversuche gespeichert. Ein Login(versuch) wird eindeutig identifiziert durch den Account, sowie durch das Datum (DATUM) des Logins, gemeinsam mit einer id (ID). Darüber hinaus wird die IP-Adresse des Logins gespeichert (IP), und ob der Login erfolgreich war (SUCCESS). MitarbeiterInnen von Bezahlpartnern werden durch ihre Sozialversicherungsnummer (SNVR) eindeutig identifiziert. Darüber hinaus wird ihr Vorname (VNAME), Nachname (NNAME), ihr Geburtsdatum (GEBDAT), sowie ihr Gehalt (GEHALT) gespeichert. Es wird nicht gespeichert, welche MitarbeiterInnen bei welchem Partner arbeiten, sondern nur, welche MitarbeiterInnen Zugriff auf welchen Account haben, wobei es für jeden Account mindestens eineN MitarbeiterIn mit Zugriff geben muss. Eine Transaktion ist eindeutig identifiziert durch die Kreditkarte auf welche sie gebucht wurde, den Account mit dem die Buchung durchgeführt wurde, sowie durch eine Transaktionsid (TID). Zu jeder Transaktion wird außerdem der Betrag (TOTAL) sowie das Datum (DATUM) gespeichert. Jedes Security-Level ist eindeutig bezeichnet durch seinen Namen (NAME) in Kombination mit seiner Nummer (NR). Für jede Transaktion wird genau ein Security-Level verwendet. Die Kreditkartenfirma beschäftigt Security-Agents. Zu jedem Security-Agent wird ein Name (NAME), sein Trust-Level (TLEVEL), sowie eine eindeutige Agent-ID (AID) verwaltet. Zu Transaktionen, bei denen der Verdacht auf einen Betrugsfall besteht wird zusätzlich noch eine Bemerkung (BEM), die Art des Betrugs (TYP), das Datum an dem der Betrugsverdacht erkannt wurde (DATUM) gespeichert, sowie welcher Security-Agent den Verdacht gemeldet hat. Sobald einE KundeIn auf einen Verdachtsfall reagiert wird das Datum (DATUM) der Reaktion gespeichert, sowie ob es sich in der Tat um einen Betrug handelt oder nicht (OK). Es wird auch gespeichert, welcher Security-Agent welcheN MitarbeiterIn zu welchem Security-Level zugelassen hat, und wann das passiert ist (AM). Es gilt zu beachten, dass jedeR MitarbeiterIn zu mindestens einem Security-Level zugelassen sein muss.
1)
Geben Sie eine Liste aller verdächtiger Transaktionen (KNR, TID, DATUM, TOTAL) über 150 (TOTAL) aus. Sortieren Sie die Ausgabe aufsteigend nach der Kartennummer, innerhalb einer Kartennummer absteigend nach Datum (neueste zuerst), und innerhalb eines Datums aufsteigend nach dem Umsatz (TOTAL).
SELECT t.KNR, t.TID, t.DATUM, t.TOTAL FROM Transaktion t JOIN Verdacht v ON (t.KNR = v.KNR AND t.MNAME=v.MNAME AND t.BENUTZER=v.BENUTZER AND t.TID=v.TID) WHERE TOTAL > 150 ORDER BY KNR ASC, DATUM DESC, TOTAL ASC;
2)
Geben Sie jene Vertriebspartner (MNAME) aus, welche bei der größsten Zahl an Bezahl-Partnern einen Rabatt von mindestens 0.05 anbieten.
SELECT VMNAME FROM gibt_Rabatt WHERE prozent >= 0.05 GROUP BY VMNAME HAVING count(BMNAME) >= all (SELECT count(BMNAME) FROM gibt_Rabatt WHERE prozent >= 0.05 GROUP BY VMNAME);
SELECT VMNAME FROM gibt_Rabatt WHERE prozent >= 0.05 GROUP BY VMNAME HAVING count(BMNAME) >= (SELECT max(anz) FROM (SELECT count(BMNAME) as anz FROM gibt_Rabatt WHERE prozent >= 0.05 GROUP BY VMNAME) as temp);
3.1)
Geben Sie alle SecurityAgents (NAME, TLEVEL) an, welche bislang noch keinen bestätigten Verdacht gemeldet haben. Achten Sie darauf, dass für jeden SecurityAgent welcher die Eigenschaft erfüllt genau ein Eintrag ausgegeben wird.
SELECT NAME, TLEVEL FROM SecurityAgent NATURAL JOIN ( SELECT AID FROM SecurityAgent EXCEPT (SELECT AID FROM Verdacht v JOIN UserFeedback u ON (v.KNR=u.KNR AND v.MNAME=u.MNAME AND v.BENUTZER=u.BENUTZER AND v.TID=u.TID) WHERE OK=false)) as sa;
SELECT NAME, TLEVEL FROM SecurityAgent WHERE AID NOT IN (SELECT AID FROM Verdacht v JOIN UserFeedback u ON (v.KNR=u.KNR AND v.MNAME=u.MNAME AND v.BENUTZER=u.BENUTZER AND v.TID=u.TID) WHERE OK=false);
SELECT NAME, TLEVEL FROM SecurityAgent sa WHERE NOT EXISTS (SELECT * FROM Verdacht v JOIN UserFeedback u ON (v.KNR=u.KNR AND v.MNAME=u.MNAME AND v.BENUTZER=u.BENUTZER AND v.TID=u.TID) WHERE OK=false AND v.AID=sa.AID);
3.2)
Geben Sie eine Liste ALLER Transaktionen (KNR, TID, DATUM, TOTAL) an. In einer zusätzlichen Spalte (STATUS) soll folgendes ausgegeben werden: "SAUBER" für unverdächtige Transaktionen, "OFFEN" für verdächtige Transkationen für die bislang noch keine Rückmeldung eines Kunden vorliegt (UserFeedback), "OK" falls das UserFeedback positiv ist (= Wert des Attributs OK ist true), und "!!!" falls das UserFeedback negativ ist (=Wert des Atrributs OK ist false).
SELECT KNR, TID, DATUM, TOTAL, 'SAUBER' as STATUS FROM Transaktion WHERE (KNR, MNAME, BENUTZER, TID) NOT IN (SELECT KNR, MNAME, BENUTZER, TID FROM Verdacht) UNION (SELECT t.KNR, t.TID, t.DATUM, TOTAL, 'OFFEN' as STATUS FROM Transaktion t JOIN Verdacht v ON (t.KNR = v.KNR AND t.MNAME=v.MNAME AND t.BENUTZER=v.BENUTZER AND t.TID=v.TID) WHERE (t.KNR, t.MNAME, t.BENUTZER, t.TID) NOT IN (SELECT KNR, MNAME, BENUTZER, TID FROM UserFeedback)) UNION (SELECT t.KNR, t.TID, t.DATUM, t.TOTAL, CASE WHEN OK THEN 'OK' ELSE '!!!' END as STATUS FROM Transaktion t JOIN UserFeedback u ON (t.KNR = u.KNR AND t.MNAME=u.MNAME AND t.BENUTZER=u.BENUTZER AND t.TID=u.TID));
(SELECT KNR, TID, DATUM, TOTAL, 'SAUBER' as STATUS FROM Transaktion WHERE (KNR, MNAME, BENUTZER, TID) NOT IN (SELECT KNR, MNAME, BENUTZER, TID FROM Verdacht)) UNION (SELECT t.KNR, t.TID, t.DATUM, TOTAL, 'OFFEN' as STATUS FROM Transaktion t JOIN Verdacht v ON (t.KNR = v.KNR AND t.MNAME=v.MNAME AND t.BENUTZER=v.BENUTZER AND t.TID=v.TID) WHERE (t.KNR, t.MNAME, t.BENUTZER, t.TID) NOT IN (SELECT KNR, MNAME, BENUTZER, TID FROM UserFeedback)) UNION (SELECT t.KNR, t.TID, t.DATUM, t.TOTAL, CASE WHEN OK THEN 'OK' ELSE '!!!' END as STATUS FROM Transaktion t JOIN UserFeedback u ON (t.KNR = u.KNR AND t.MNAME=u.MNAME AND t.BENUTZER=u.BENUTZER AND t.TID=u.TID));
SELECT KNR, TID, DATUM, TOTAL, (case when (KNR, MNAME, BENUTZER, TID) NOT IN (SELECT KNR, MNAME, BENUTZER, TID FROM Verdacht) then 'SAUBER' when (KNR, MNAME, BENUTZER, TID) IN (SELECT KNR, MNAME, BENUTZER, TID FROM Verdacht) AND (KNR, MNAME, BENUTZER, TID) NOT IN (SELECT KNR, MNAME, BENUTZER, TID FROM UserFeedback) then 'OFFEN' when (KNR, MNAME, BENUTZER, TID, false) IN (SELECT KNR, MNAME, BENUTZER, TID, OK FROM UserFeedback) then '!!!' when (KNR, MNAME, BENUTZER, TID, true) IN (SELECT KNR, MNAME, BENUTZER, TID, OK FROM UserFeedback) then 'OK' end) as STATUS FROM Transaktion;
3.3)
Geben Sie für jeden Account (MNAME, BENUTZER) aus wie viele bestätigte (d.h. es existiert ein UserFeedback und OK=false) verdächtige Transaktionen über ihn gebucht wurden. Geben Sie dabei nur solche Accounts aus bei denen diese Anzahl mindestens 3 ist, und geben Sie ebenfalls die Anzahl aus.
SELECT MNAME, BENUTZER, count((KNR,TID)) FROM UserFeedback WHERE OK=false GROUP BY MNAME, BENUTZER HAVING count((KNR,TID)) >= 3;
4)
Geben Sie alle Transaktionen (KNR, TID, MNAME, BENUTZER) aus für welche ein Sicherheitslevel verwendet wurde, für das kein Mitarbeiter mit Zugriff auf den Account (mit welchem die Transaktion gebucht wurde) zugelassen ist. (Beachten Sie, dass einE MitarbeiterIn explizit zu einem Sicherheitslevel zugelassen sein muss. Ist einE MitarbeiterIn z.B. zu einem höheren Sicherheitslevel zugelassen, so ist er/sie deshalb nicht automatisch auch für alle niedrigeren Levels zugelassen.)
SELECT KNR, MNAME, BENUTZER, TID FROM Transaktion t WHERE NOT EXISTS ( SELECT * FROM hat_Zugriff hz NATURAL JOIN zugelassen z WHERE t.MNAME = hz.MNAME AND t.BENUTZER = hz.BENUTZER AND z.SLNAME = t.SLNAME AND z.SLNR = t.SLNR );
SELECT KNR, MNAME, BENUTZER, TID FROM Transaktion t EXCEPT SELECT KNR, MNAME, BENUTZER, TID FROM Transaktion t2 NATURAL JOIN hat_zugriff hz NATURAL JOIN zugelassen;
SELECT KNR, TID, t.MNAME, t.BENUTZER FROM Transaktion t LEFT OUTER JOIN hat_zugriff hz ON (t.MNAME=hz.mname AND t.benutzer=hz.benutzer) LEFT OUTER JOIN zugelassen z ON (hz.svnr=z.svnr AND t.slname=z.slname AND t.slnr = z.slnr) GROUP BY KNR, TID, t.MNAME, t.BENUTZER HAVING COUNT(z.svnr) = 0;
5.1)
Geben Sie für jeden Account (MNAME, BENUTZER) die Anzahl jener Kreditkarten an welche (auf irgendeinem Account) missbräuchliche verwendet wurden (Wert von OK im UserFeedback auf einen Verdacht ist "false") und für die eine Transaktion vor der ersten missbräuchlichen Verwendung auf diesem Account gebucht wurde. Geben Sie nur jene Accounts aus, auf denen mindestens 10% sämtlicher Kreditkarten welche irgendwann einmal Opfer eines Missbrauchs wurden jeweils vor dem ersten Betrug verwendet wurden (d.h., die zu berechnende Anzahl soll mindestens 10% der Gesamtanzahl an missbräuchlich verwendeten Kreditkarten ausmachen). Ordnen Sie die Ausgabe zuerst Absteigend nach der Anzahl der Kreditkarten, und Einträge mit gleicher Anzahl aufsteigend nach dem BENUTZER. Hinweis: Sie dürfen annehmen, dass es mindestens einen Betrugsfall gibt. D.h., wenn Sie wollen können Sie sich auf jene Accounts beschränken, auf denen eine missbräuchlich verwendete Kreditkarte auch einmal verwendet wurde.
SELECT MNAME, BENUTZER, count(DISTINCT KNR) as ANZ FROM Transaktion NATURAL JOIN ( SELECT t.KNR, min(t.DATUM) as FirstFraud FROM Transaktion t JOIN UserFeedback u ON (t.KNR = u.KNR AND t.MNAME=u.MNAME AND t.BENUTZER=u.BENUTZER AND t.TID=u.TID) WHERE OK=false GROUP BY t.KNR ) as FraudDate WHERE Transaktion.DATUM < FraudDate.FirstFraud GROUP BY MNAME, BENUTZER HAVING count(DISTINCT KNR) >= 0.1*( SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false ) ORDER BY ANZ DESC, BENUTZER ASC;
SELECT a.MNAME, a.BENUTZER, count(DISTINCT bc.KNR) as anz FROM Account a LEFT OUTER JOIN Transaktion ON (a.MNAME=Transaktion.MNAME AND a.BENUTZER=Transaktion.BENUTZER) LEFT OUTER JOIN (SELECT t.KNR, min(t.datum) as date FROM UserFeedback uf JOIN Transaktion t ON (uf.knr=t.knr AND uf.mname=t.mname AND uf.benutzer=t.BENUTZER AND uf.TID=t.TID) WHERE OK=false GROUP BY t.KNR) as bc ON (Transaktion.KNR=bc.KNR AND Transaktion.DATUM < bc.DATE) GROUP BY a.MNAME, a.BENUTZER HAVING count(DISTINCT bc.KNR) >= 0.1*(SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false) ORDER BY ANZ DESC, a.BENUTZER ASC;
SELECT MNAME, BENUTZER, count(DISTINCT KNR) as ANZ FROM Transaktion NATURAL JOIN ( SELECT t.KNR, min(t.DATUM) as FirstFraud FROM Transaktion t JOIN UserFeedback u ON (t.KNR = u.KNR AND t.MNAME=u.MNAME AND t.BENUTZER=u.BENUTZER AND t.TID=u.TID) WHERE OK=false GROUP BY t.KNR ) as FraudDate WHERE Transaktion.DATUM < FraudDate.FirstFraud GROUP BY MNAME, BENUTZER HAVING count(DISTINCT KNR)*10 >= ( SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false ) ORDER BY ANZ DESC, BENUTZER ASC;
SELECT MNAME, BENUTZER, count(DISTINCT KNR) as ANZ FROM Transaktion NATURAL JOIN ( SELECT t.KNR, min(t.DATUM) as FirstFraud FROM Transaktion t JOIN UserFeedback u ON (t.KNR = u.KNR AND t.MNAME=u.MNAME AND t.BENUTZER=u.BENUTZER AND t.TID=u.TID) WHERE OK=false GROUP BY t.KNR ) as FraudDate WHERE Transaktion.DATUM < FraudDate.FirstFraud GROUP BY MNAME, BENUTZER HAVING count(DISTINCT KNR) >= ( SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false )/10.0 ORDER BY ANZ DESC, BENUTZER ASC;
SELECT a.MNAME, a.BENUTZER, count(DISTINCT bc.KNR) as anz FROM Account a LEFT OUTER JOIN Transaktion ON (a.MNAME=Transaktion.MNAME AND a.BENUTZER=Transaktion.BENUTZER) LEFT OUTER JOIN (SELECT t.KNR, min(t.datum) as date FROM UserFeedback uf JOIN Transaktion t ON (uf.knr=t.knr AND uf.mname=t.mname AND uf.benutzer=t.BENUTZER AND uf.TID=t.TID) WHERE OK=false GROUP BY t.KNR) as bc ON (Transaktion.KNR=bc.KNR AND Transaktion.DATUM < bc.DATE) GROUP BY a.MNAME, a.BENUTZER HAVING count(DISTINCT bc.KNR)*10 >= (SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false) ORDER BY ANZ DESC, a.BENUTZER ASC;
SELECT a.MNAME, a.BENUTZER, count(DISTINCT bc.KNR) as anz FROM Account a LEFT OUTER JOIN Transaktion ON (a.MNAME=Transaktion.MNAME AND a.BENUTZER=Transaktion.BENUTZER) LEFT OUTER JOIN (SELECT t.KNR, min(t.datum) as date FROM UserFeedback uf JOIN Transaktion t ON (uf.knr=t.knr AND uf.mname=t.mname AND uf.benutzer=t.BENUTZER AND uf.TID=t.TID) WHERE OK=false GROUP BY t.KNR) as bc ON (Transaktion.KNR=bc.KNR AND Transaktion.DATUM < bc.DATE) GROUP BY a.MNAME, a.BENUTZER HAVING count(DISTINCT bc.KNR) >= (SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false)/10.0 ORDER BY ANZ DESC, a.BENUTZER ASC;
SELECT MNAME, BENUTZER, count(DISTINCT KNR) AS anz FROM Transaktion t WHERE EXISTS (SELECT * FROM UserFeedback uf WHERE OK=false AND uf.KNR=t.KNR) AND NOT EXISTS (SELECT * FROM UserFeedback uf1 JOIN Transaktion t1 ON (uf1.KNR=t1.KNR AND uf1.MNAME=t1.MNAME AND uf1.BENUTZER=t1.BENUTZER AND uf1.TID=t1.TID) WHERE uf1.OK=false AND t1.DATUM<=t.DATUM AND t1.KNR=t.KNR) GROUP BY MNAME, BENUTZER HAVING COUNT(DISTINCT KNR) >= 0.1*( SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false ) ORDER BY ANZ DESC, BENUTZER ASC;
SELECT MNAME, BENUTZER, count(DISTINCT KNR) AS anz FROM Transaktion t WHERE EXISTS (SELECT * FROM UserFeedback uf WHERE OK=false AND uf.KNR=t.KNR) AND NOT EXISTS (SELECT * FROM UserFeedback uf1 JOIN Transaktion t1 ON (uf1.KNR=t1.KNR AND uf1.MNAME=t1.MNAME AND uf1.BENUTZER=t1.BENUTZER AND uf1.TID=t1.TID) WHERE uf1.OK=false AND t1.DATUM<=t.DATUM AND t1.KNR=t.KNR) GROUP BY MNAME, BENUTZER HAVING COUNT(DISTINCT KNR)*10 >= ( SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false ) ORDER BY ANZ DESC, BENUTZER ASC;
SELECT MNAME, BENUTZER, count(DISTINCT KNR) AS anz FROM Transaktion t WHERE EXISTS (SELECT * FROM UserFeedback uf WHERE OK=false AND uf.KNR=t.KNR) AND NOT EXISTS (SELECT * FROM UserFeedback uf1 JOIN Transaktion t1 ON (uf1.KNR=t1.KNR AND uf1.MNAME=t1.MNAME AND uf1.BENUTZER=t1.BENUTZER AND uf1.TID=t1.TID) WHERE uf1.OK=false AND t1.DATUM<=t.DATUM AND t1.KNR=t.KNR) GROUP BY MNAME, BENUTZER HAVING COUNT(DISTINCT KNR) >= ( SELECT count(DISTINCT KNR) FROM UserFeedback WHERE OK=false )/10.0 ORDER BY ANZ DESC, BENUTZER ASC;
5.2)
Geben Sie jene Bezahl-Partner (MNAME) aus, bei denen jede missbräuchlich verwendete Kreditkarte (= Kreditkarte für die es mindestens eine verdächtige Transaktion mit UserFeedback OK=false gibt) mindestens einmal für einen Betrugsfall (= verdächtige Transaktion mit UserFeedback OK=false) verwendet wurde.
SELECT MNAME FROM bpartner bp WHERE NOT EXISTS ( SELECT * FROM userfeedback uf WHERE OK=false AND NOT EXISTS ( SELECT * FROM userfeedback uf1 WHERE uf1.knr=uf.knr AND uf1.OK=false AND uf1.mname=bp.mname ))
SELECT MNAME FROM UserFeedback uf WHERE OK=false GROUP BY MNAME HAVING COUNT(DISTINCT KNR) = (SELECT COUNT(DISTINCT KNR) FROM UserFeedback uf1 WHERE OK=false)
SELECT MNAME FROM bpartner EXCEPT (SELECT MNAME FROM ((SELECT bp.MNAME, uf.KNR FROM bpartner bp, userfeedback uf WHERE uf.OK=false) EXCEPT (SELECT MNAME, KNR FROM userfeedback WHERE OK=false)) as miss)
5.3)
Geben Sie eine Liste jener Mitarbeiter (VNAME, NNAME) aus, welche Zugriff auf alle missbräuchlich verwendeten Kreditkarten, jeweils vor dem ersten missbräuchlichen Zugriff, hatten (d.h. jene Mitarbeiter, so dass es für jede missbräuchlich verwendete Kreditkarte eine Buchung vor dem ersten Missbrauch über einen Account gibt, auf den die/der MiterbeiterIn Zugriff hat).
SELECT VNAME, NNAME FROM Mitarbeiter m WHERE NOT EXISTS (SELECT KNR FROM ( SELECT t1.KNR, min(t1.DATUM) as FirstFraud FROM Transaktion t1 JOIN UserFeedback uf ON (t1.KNR=uf.KNR AND t1.MNAME=uf.MNAME AND t1.BENUTZER=uf.BENUTZER AND t1.TID=uf.TID) WHERE OK=false GROUP BY t1.KNR ) as FraudCards WHERE NOT EXISTS ( SELECT * FROM Transaktion t2 NATURAL JOIN hat_Zugriff hz WHERE hz.svnr = m.svnr AND t2.knr = FraudCards.knr AND t2.DATUM < FraudCards.FirstFraud ) );
SELECT VNAME, NNAME FROM Mitarbeiter m WHERE NOT EXISTS (SELECT * FROM UserFeedback uf WHERE OK=false AND NOT EXISTS ( SELECT * FROM Transaktion t2 NATURAL JOIN hat_Zugriff hz WHERE hz.svnr = m.svnr AND t2.knr = t1.knr AND t2.DATUM < (SELECT min(t3.DATUM) FROM Transaktion t3 JOIN UserFeedback uf2 ON (t3.KNR=uf2.KNR AND t3.MNAME=uf2.MNAME AND t3.BENUTZER=uf2.BENUTZER AND t3.TID=uf2.TID) WHERE OK=false AND t2.knr = t3.knr) ) );
SELECT VNAME, NNAME FROM Mitarbeiter m NATURAL JOIN hat_zugriff hz JOIN Transaktion t ON (hz.MNAME=t.MNAME AND hz.BENUTZER=t.BENUTZER) JOIN (SELECT t1.KNR, min(t1.DATUM) as firstfraud FROM userFeedback uf1 JOIN Transaktion t1 ON (t1.KNR=uf1.KNR AND t1.MNAME=uf1.MNAME AND t1.BENUTZER=uf1.BENUTZER AND t1.TID=uf1.TID) WHERE OK=false GROUP BY t1.knr) as d ON (t.knr=d.knr) WHERE t.DATUM<d.firstfraud GROUP BY m.svnr, m.vname, m.nname HAVING COUNT(DISTINCT t.KNR) = (SELECT COUNT(DISTINCT(KNR)) FROM userfeedback WHERE OK=false);
6.1)
Geben Sie für ALLE Kunden (VNAME, NNAME) die Anzahl der auf den Kunden lautenden Kreditkarten aus, die bis inklusive 29.01.2016 ablaufen. (HINWEIS: Benützen Sie die Funktion TO_DATE und das Datumsformat 'DD-MM-YYYY'.) Geben Sie für jede Kundin/jeden Kunden genau einen Eintrag aus.
SELECT VNAME, NNAME, count(Karte.knr) as Karten
FROM Kunde LEFT OUTER JOIN Karte ON
(Karte.Kunde = Kunde.knr AND Karte.ablaufdatum <= TO_DATE('29-01-2016','DD-MM-YYYY'))
GROUP BY Kunde.knr;
SELECT VNAME, NNAME, count(k.knr)
FROM kunde left outer join (SELECT knr, kunde from karte where ablaufdatum <= TO_DATE('29-01-2016','DD-MM-YYYY')) as k ON (kunde.knr=k.kunde)
GROUP BY kunde.knr, kunde.vname, kunde.nname
6.2)
Geben Sie eine Liste aller Kunden (KNR, NNAME, GEBDAT) aus, von denen alle Karten die bis zum 05.05.2016 ablaufen bereits ersetzt wurden. (HINWEIS: Benützen Sie die Funktion TO_DATE und das Datumsformat 'DD-MM-YYYY'.)
SELECT KNR, NNAME, GEBDAT
FROM Kunde ku1
WHERE NOT EXISTS ( SELECT *
FROM Karte ka1
WHERE ablaufdatum <= TO_DATE('05-05-2016','DD-MM-YYYY')
AND ka1.kunde = ku1.knr AND
NOT EXISTS ( SELECT *
FROM ersetzt
WHERE VGKNR = ka1.knr
) );
SELECT ku1.KNR, NNAME, GEBDAT
FROM Kunde ku1 LEFT OUTER JOIN Karte ka1 ON(ku1.knr=ka1.kunde AND ka1.ablaufdatum <= TO_DATE('05-05-2016','DD-MM-YYYY'))
GROUP BY ku1.KNR, ku1.NNAME, ku1.GEBDAT
HAVING count(ka1.knr) =
(SELECT count(e.nfknr)
FROM Karte ka2 LEFT OUTER JOIN ersetzt e ON (ka2.knr=e.vgknr)
WHERE ka2.ablaufdatum <= TO_DATE('05-05-2016','DD-MM-YYYY') AND
ka2.kunde=ku1.knr);
7)
Geben Sie für jeden erfolgreichen Login (d.h., SUCCESS=true) die die Werte der Attribute MNAME, BENUTZER, DATUM, ID sowie die Anzahl der nötigen Loginversuche (= 1 + Anzahl der Logins mit SUCCESS=false seit dem letzten erfolgreichen Login auf dem jeweiligen Account) an. Sie können dabei annehmen, dass es keine gleichzeitigen Login-Versuche für den selben Account gibt.
SELECT l1.MNAME, l1.BENUTZER, l1.DATUM, l1.ID, count(l2.ID)+1 AS Versuche FROM Login l1 LEFT OUTER JOIN Login l2 ON (l1.MNAME=l2.MNAME AND l1.BENUTZER=l2.BENUTZER AND l1.DATUM > l2.DATUM AND l2.success=false AND NOT EXISTS (SELECT * FROM Login l3 WHERE l3.MNAME=l1.MNAME AND l3.BENUTZER=l1.BENUTZER AND l3.success=true AND l3.DATUM < l1.DATUM AND l3.DATUM > l2.DATUM)) WHERE l1.success=true GROUP BY l1.MNAME, l1.BENUTZER, l1.DATUM, l1.ID;
SELECT mname, benutzer, datum, id, ( SELECT count(*)+1 FROM login l WHERE l.mname=login.mname AND l.benutzer=login.benutzer AND l.datum < login.datum AND l.success=false AND NOT EXISTS (SELECT * FROM login l2 where l2.MNAME=l.MNAME AND l2.BENUTZER=l.BENUTZER AND l2.datum<login.datum and l2.datum>l.datum and l2.success=true)) as versuche FROM login WHERE success=true
8.1)
Geben Sie den/die lukrativsten Bezahl-Partner (MNAME, PID, GEBUEHR) im Jahr 2016 aus, d.h. jene Bezahl-Partner welche im Jahr 2016 die meisten Gebühren abgeführt haben (Diese Gebühren setzen sich wie folgt zusammen: Das Attribut GEBUEHR enthält für jeden Bezahl-Partner den Prozentsatz welcher von jeder Zahlung an die Kreditkartenfirma abgeführt werden muss. Die Summe an Gebühren für einen Bezahl-Partner ergibt sich also aus der Summe an Umsätzen die über einen Account dieses Partners gebucht wurden, multipliziert mit dem Wert von GEBUEHR). Geben Sie zusätzlich die abgeführten Gebühren aus. (HINWEIS: Benützen Sie die Funktion TO_DATE und das Datumsformat 'DD-MM-YYYY'.)
SELECT MNAME, PID, GEBUEHR, sum(TOTAL)*GEBUEHR AS Abgabe
FROM Partner NATURAL JOIN BPartner NATURAL JOIN Transaktion
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
GROUP BY MNAME, PID, GEBUEHR
HAVING sum(TOTAL)*GEBUEHR >= all (
SELECT sum(TOTAL)*GEBUEHR
FROM BPartner NATURAL JOIN Transaktion
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
GROUP BY MNAME
);
SELECT MNAME, PID, GEBUEHR, sum(TOTAL)*GEBUEHR AS Abgabe
FROM Partner NATURAL JOIN BPartner NATURAL JOIN Transaktion
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
GROUP BY MNAME, PID, GEBUEHR
HAVING sum(TOTAL)*GEBUEHR = (
SELECT max(abgabe) FROM (
SELECT sum(TOTAL)*GEBUEHR as abgabe
FROM BPartner NATURAL JOIN Transaktion
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
GROUP BY MNAME) as tmp
);
8.2)
Geben Sie für jeden Bezahl-Partner (MNAME) jene/jenen Vertriebs-Partner (MNAME) an, über dessen Karten 2016 die meisten Transaktionen gelaufen sind welche mehr als die durchschnittliche Höhe eines Umsatz des Bezahl-Partners 2016 ausgemacht haben. Falls es für einen Bezahl-Partner keinen solchen Vertriebs-Partner gibt so soll statt dessen 'kein TOP Partner' ausgegeben werden. Zusätzlich soll auch die Anzahl der Transaktionen ausgegeben werden.
SELECT bp.MNAME as BPMNAME, COALESCE(goodTrans.vmname,'kein TOP Partner') as VPMNAME , count(goodTrans.tid)
FROM BPartner bp LEFT OUTER JOIN (
SELECT t.mname as bmname, k.mname as vmname, t.tid
FROM Transaktion t JOIN Karte k ON (t.knr = k.knr) JOIN
(SELECT MNAME, avg(TOTAL) as av
FROM Transaktion
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
GROUP BY MNAME) as au
ON (t.mname = au.mname AND t.TOTAL > au.av)
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY') ) as goodTrans
ON (bp.mname = goodTrans.bmname)
GROUP BY bp.MNAME, goodTrans.vmname
HAVING count(goodTrans.tid) >= all (
SELECT count(goodTrans1.tid)
FROM BPartner bp1 LEFT OUTER JOIN (
SELECT t1.mname as bmname, k1.mname as vmname, t1.tid
FROM Transaktion t1 JOIN Karte k1 ON (t1.knr = k1.knr) JOIN
(SELECT MNAME, avg(TOTAL) as av1
FROM Transaktion
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
GROUP BY MNAME) as au1
ON (t1.mname = au1.mname AND t1.TOTAL > au1.av1)
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')) as goodTrans1
ON (bp1.mname = goodTrans1.bmname)
WHERE bp1.mname = bp.mname
GROUP BY bp1.MNAME, goodTrans1.vmname
);
SELECT bp.mname as bpmname, COALESCE(mp.vname, 'kein TOP Partner') as vpartner, COALESCE(mp.count,0) as anzahl
FROM bpartner bp LEFT OUTER JOIN
(
SELECT t.mname as mname, k.mname as vname, count(TID)
FROM Transaktion t JOIN Karte k ON (t.knr = k.knr)
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY') AND
TOTAL > ( SELECT avg(TOTAL) FROM Transaktion WHERE Transaktion.MNAME = t.mname
AND
DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
)
GROUP BY t.mname, k.mname
HAVING count(TID) >= all (
SELECT count(TID)
FROM Transaktion t1 JOIN Karte k1 ON (t1.knr = k1.knr)
WHERE t1.MNAME=t.MNAME AND
DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY') AND
TOTAL > ( SELECT avg(TOTAL) FROM Transaktion WHERE Transaktion.MNAME = t1.mname
AND
DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
)
GROUP BY k1.mname
)
) as mp ON (bp.mname=mp.mname) ;
SELECT bp.mname as mname, mp.vname as vpartner, mp.count as anzahl
FROM bpartner bp JOIN
(
SELECT t.mname as mname, k.mname as vname, count(TID)
FROM Transaktion t JOIN Karte k ON (t.knr = k.knr)
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY') AND
TOTAL > ( SELECT avg(TOTAL) FROM Transaktion WHERE Transaktion.MNAME = t.mname
AND
DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
)
GROUP BY t.mname, k.mname
HAVING count(TID) >= all (
SELECT count(TID)
FROM Transaktion t1 JOIN Karte k1 ON (t1.knr = k1.knr)
WHERE t1.MNAME=t.MNAME AND
DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY') AND
TOTAL > ( SELECT avg(TOTAL) FROM Transaktion WHERE Transaktion.MNAME = t1.mname
AND
DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
)
GROUP BY k1.mname
)
) as mp ON (bp.mname=mp.mname)
UNION
(
SELECT bp.mname as mname, 'kein TOP Partner' as vpartner, 0 as anzahl
FROM bpartner bp
WHERE NOT EXISTS (
SELECT * FROM Transaktion t WHERE
t.mname=bp.mname AND
DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY') AND
TOTAL > ( SELECT avg(TOTAL) FROM Transaktion WHERE Transaktion.MNAME = t.mname
AND
DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
)
));
9)
Geben Sie eine Liste aller Paare von (verschiedenen) Kreditkarten (KNR, ABLAUFDATUM, KNR, ABLAUFDATUM) aus, welche bei exakt den selben Bezahl-Partnern benutzt wurden. Geben Sie für ein Paar (x,y) nicht beide Darstellungen (x,y) und (y,x) aus, sondern nur jene bei der die erste Stelle die kleinere Kartennummer enthält (also (x,y) falls x < y, und (y,x) andernfalls).
SELECT k1.knr, k1.ablaufdatum, k2.knr, k2.ablaufdatum FROM karte k1, karte k2 WHERE NOT EXISTS ( SELECT * FROM Transaktion t1 WHERE t1.knr = k1.knr AND NOT EXISTS ( SELECT * FROM Transaktion t2 WHERE t2.knr = k2.knr AND t1.mname = t2.mname ) ) AND NOT EXISTS ( SELECT * FROM Transaktion t1 WHERE t1.knr = k2.knr AND NOT EXISTS ( SELECT * FROM Transaktion t2 WHERE t2.knr = k1.knr AND t1.mname = t2.mname ) ) AND k1.knr < k2.knr;
SELECT k1.knr, k1.ablaufdatum, k2.knr, k2.ablaufdatum FROM Karte k1, Karte k2 WHERE k1.knr < k2.knr AND NOT EXISTS (((SELECT MNAME FROM Transaktion WHERE Transaktion.knr=k1.knr) EXCEPT (SELECT MNAME FROM Transaktion WHERE Transaktion.knr=k2.knr) )UNION( (SELECT MNAME FROM Transaktion WHERE Transaktion.knr=k2.knr) EXCEPT (SELECT MNAME FROM Transaktion WHERE Transaktion.knr=k1.knr) ));
SELECT k1.knr, k1.ablaufdatum, k2.knr, k2.ablaufdatum FROM Karte k1, Karte k2 WHERE k1.knr < k2.knr EXCEPT ( SELECT knr1, abl1, knr2, abl2 FROM ( SELECT k1.knr as knr1, k1.ablaufdatum as abl1, k2.knr as knr2, k2.ablaufdatum as abl2, t.mname FROM Karte k1 JOIN Transaktion t ON (t.KNR=k1.KNR), Karte k2 WHERE k1.knr < k2.knr EXCEPT (SELECT k1.knr as knr1, k1.ablaufdatum as abl1, k2.knr as knr2, k2.ablaufdatum as abl2, t.mname FROM Karte k1, (Karte k2 JOIN Transaktion t ON (t.KNR=k2.KNR)) WHERE k1.knr < k2.knr ) UNION( SELECT k1.knr as knr1, k1.ablaufdatum as abl1, k2.knr as knr2, k2.ablaufdatum as abl2, t.mname FROM Karte k1, (Karte k2 JOIN Transaktion t ON (t.KNR=k2.KNR)) WHERE k1.knr < k2.knr EXCEPT (SELECT k1.knr as knr1, k1.ablaufdatum as abl1, k2.knr as knr2, k2.ablaufdatum as abl2, t.mname FROM Karte k1 JOIN Transaktion t ON (t.KNR=k1.KNR), Karte k2 WHERE k1.knr < k2.knr) ))as negative);
10)
Geben Sie für jede Kunden/jede Kundin (VNMANE, NNAME) das Lieblingsgeschäft/die Lieblingsgeschäfte (MNAME) im Jahr 2016 an (= jener/jene Bezahlpartner bei dem/denen, über alle auf die Kundin/den Kunden lautenden Kreditkarten, in diesem Jahr am meisten Transkationen gebucht wurden). (HINWEIS: Benützen Sie die Funktion TO_DATE und das Datumsformat 'DD-MM-YYYY'.)
SELECT VNAME, NNAME, Transaktion.MNAME
FROM Kunde k JOIN Karte ON (k.KNR = Karte.Kunde) JOIN Transaktion ON (Transaktion.KNR = Karte.KNR)
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
GROUP BY k.KNR, VNAME, NNAME, Transaktion.MNAME
HAVING count(TID) >= all ( SELECT count(TID)
FROM Kunde k2 JOIN Karte ON (k2.KNR = Karte.Kunde) JOIN Transaktion ON (Transaktion.KNR = Karte.KNR)
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY') AND
k2.knr = k.knr
GROUP BY Transaktion.MNAME);
SELECT VNAME, NNAME, Transaktion.MNAME
FROM Kunde k JOIN Karte ON (k.KNR = Karte.Kunde) JOIN Transaktion ON (Transaktion.KNR = Karte.KNR)
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY')
GROUP BY k.KNR, VNAME, NNAME, Transaktion.MNAME
HAVING count(TID) >= ( SELECT max(c) FROM
(SELECT count(TID) as c
FROM Kunde k2 JOIN Karte ON (k2.KNR = Karte.Kunde) JOIN Transaktion ON (Transaktion.KNR = Karte.KNR)
WHERE DATUM <= TO_DATE('31-12-2016','DD-MM-YYYY') AND
DATUM >= TO_DATE('01-01-2016','DD-MM-YYYY') AND
k2.knr = k.knr
GROUP BY Transaktion.MNAME) as tmp );