sql_kreditrisikoanalyse.sql

CREATE DATABASE kreditfinanzierung2;

USE kreditfinanzierung2;


ALTER TABLE credit_risk_dataset
ADD COLUMN kredit_id INT AUTO_INCREMENT PRIMARY KEY FIRST;


-- Spalten auf Deutsch umbenennen
ALTER TABLE credit_risk_dataset
CHANGE COLUMN person_age P_Alter INT,
CHANGE COLUMN person_income P_Einkommen DECIMAL(10,0),
CHANGE COLUMN person_home_ownership P_Hausbesitz VARCHAR(50),
CHANGE COLUMN person_emp_length P_Arbeitsjahre DECIMAL(10,0),
CHANGE COLUMN loan_intent P_Kreditabsicht VARCHAR(50),
CHANGE COLUMN loan_grade P_Kreditwürdigkeit VARCHAR(10),
CHANGE COLUMN loan_amnt P_Kreditsumme DECIMAL(10,0),
CHANGE COLUMN loan_int_rate P_Zinssatz DECIMAL(5,2),
CHANGE COLUMN loan_status P_Kreditstatus TINYINT(1),
CHANGE COLUMN loan_percent_income P_Kreditanteil_am_Einkommen DECIMAL(10,0),
CHANGE COLUMN cb_person_default_on_file P_Historischer_Ausfall CHAR(1),
CHANGE COLUMN cb_person_cred_hist_length P_Kreditvergangenheit INT;


-- Struktur der Tabelle anzeigen
DESC credit_risk_dataset;


SELECT * FROM credit_risk_dataset;


SELECT COUNT(*) AS gesamt_zeilen FROM credit_risk_dataset;


-- Duplikate finden
SELECT P_Alter, P_Einkommen, P_Hausbesitz, P_Arbeitsjahre, P_Kreditabsicht, 
       P_Kreditwürdigkeit, P_Kreditsumme, P_Zinssatz, P_Kreditstatus, 
       P_Kreditanteil_am_Einkommen, P_Historischer_Ausfall, P_Kreditvergangenheit, 
       COUNT(*) AS anzahl
FROM credit_risk_dataset
GROUP BY P_Alter, P_Einkommen, P_Hausbesitz, P_Arbeitsjahre, P_Kreditabsicht, 
         P_Kreditwürdigkeit, P_Kreditsumme, P_Zinssatz, P_Kreditstatus, 
         P_Kreditanteil_am_Einkommen, P_Historischer_Ausfall, P_Kreditvergangenheit
HAVING COUNT(*) > 1;



       
-- Duplikate löschen
DELETE FROM credit_risk_dataset 
WHERE kredit_id NOT IN (
    SELECT MIN(kredit_id) 
    FROM (SELECT kredit_id, P_Alter, P_Einkommen, P_Hausbesitz, P_Arbeitsjahre, P_Kreditabsicht, 
                 P_Kreditwürdigkeit, P_Kreditsumme, P_Zinssatz, P_Kreditstatus, 
                 P_Kreditanteil_am_Einkommen, P_Historischer_Ausfall, P_Kreditvergangenheit
          FROM credit_risk_dataset
         ) AS subquery
    GROUP BY P_Alter, P_Einkommen, P_Hausbesitz, P_Arbeitsjahre, P_Kreditabsicht, 
             P_Kreditwürdigkeit, P_Kreditsumme, P_Zinssatz, P_Kreditstatus, 
             P_Kreditanteil_am_Einkommen, P_Historischer_Ausfall, P_Kreditvergangenheit
);





-- 5 Personen zum löschen ab 80 Jahren gelöscht
-- 1 Person über 80J. und 4 Personen über 100J.
SELECT * FROM credit_risk_dataset WHERE P_Alter > 80;

DELETE FROM credit_risk_dataset WHERE P_Alter > 80;



-- Die Arbeitsjahre angepasst 2 waren zu hoch
SELECT * FROM credit_risk_dataset WHERE P_Arbeitsjahre > P_Alter - 14;

UPDATE credit_risk_dataset SET P_Arbeitsjahre = 0 
WHERE P_Arbeitsjahre > P_Alter - 14;



-- Kreditnehmer und Ausfallquoten nach Kreditwürdigkeit
SELECT P_Kreditwürdigkeit, COUNT(*) AS Anzahl_Kredite,
       SUM(P_Kreditstatus) AS Anzahl_Ausfälle,
       ROUND((SUM(P_Kreditstatus) / COUNT(*)) * 100, 2) AS Ausfallrate
FROM credit_risk_dataset
GROUP BY P_Kreditwürdigkeit
ORDER BY Ausfallrate DESC;

-- Risiko-Score Berechnung 
SELECT P_Alter, P_Einkommen, P_Kreditsumme, P_Zinssatz, P_Historischer_Ausfall, P_Kreditstatus,
       ROUND((P_Zinssatz * P_Kreditsumme / P_Einkommen), 2) AS P_Risiko_Score
FROM credit_risk_dataset;

-- Kredite mit mehr als 50% des Einkommens und schlechter Bonität
SELECT kredit_id, P_Kreditwürdigkeit, P_Einkommen, P_Kreditsumme, P_Kreditanteil_am_Einkommen, P_Zinssatz, P_Historischer_Ausfall, P_Kreditstatus
FROM credit_risk_dataset
WHERE P_Kreditsumme / P_Einkommen > 0.5 AND P_Kreditwürdigkeit IN ('D', 'E') AND P_Historischer_Ausfall = 'Y'
ORDER BY P_Kreditanteil_am_Einkommen DESC;

-- Durchschnittlicher Zinssatz nach Kreditwürdigkeit
SELECT P_Kreditwürdigkeit, ROUND(AVG(P_Zinssatz), 2) AS Durchschnittlicher_Zinssatz
FROM credit_risk_dataset
GROUP BY P_Kreditwürdigkeit
ORDER BY Durchschnittlicher_Zinssatz DESC;

-- Kreditausfälle nach Wohnsituation
SELECT P_Hausbesitz, COUNT(*) AS Anzahl_Kredite,
       SUM(P_Kreditstatus) AS Anzahl_Ausfälle,
       ROUND((SUM(P_Kreditstatus) / COUNT(*)) * 100, 2) AS Ausfallrate
FROM credit_risk_dataset
GROUP BY P_Hausbesitz
ORDER BY Ausfallrate DESC;

-- Zusammenhang zwischen Zinssatz und Kreditausfall
SELECT CASE 
           WHEN P_Zinssatz <= 10 THEN '0-10%'
           WHEN P_Zinssatz <= 15 THEN '10-15%'
           WHEN P_Zinssatz <= 20 THEN '15-20%'
           ELSE '20%+'
       END AS Zinssatz_Kategorie,
       COUNT(*) AS Anzahl_Kredite,
       SUM(P_Kreditstatus) AS Anzahl_Ausfälle,
       ROUND((SUM(P_Kreditstatus) / COUNT(*)) * 100, 2) AS Ausfallrate
FROM credit_risk_dataset
GROUP BY Zinssatz_Kategorie
ORDER BY Ausfallrate DESC;



-- Tabelle erstellen
CREATE TABLE kredit_entscheidungen (
    kredit_id INT PRIMARY KEY, 
    kreditstatus VARCHAR(100) NOT NULL,  
    ablehnungsgrund VARCHAR(255),              
    entscheidung_datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


DELIMITER //
CREATE TRIGGER tr_kreditentscheidung
AFTER INSERT ON credit_risk_dataset
FOR EACH ROW
BEGIN
    DECLARE entscheidung VARCHAR(50);
    DECLARE tempid int;
CASE 
WHEN NEW. P_Alter < 25 AND NEW.P_Einkommen < 30000 THEN SET entscheidung = 'Abgelehnt: Jünger als 25 mit geringem Einkommen';
WHEN NEW.P_Hausbesitz = 'RENT' AND NEW. P_Kreditsumme > NEW.P_Einkommen * 0.5 THEN SET entscheidung = 'Abgelehnt: Kreditsumme zu hoch für Mieter';
WHEN NEW. P_Hausbesitz = 'RENT' AND NEW. P_Kreditwürdigkeit IN ('C', 'D') THEN SET entscheidung = 'Abgelehnt: Kein Kredit für Mieter mit schlechter Bonität';
WHEN NEW. P_Kreditvergangenheit < 2 THEN SET entscheidung = 'Abgelehnt: Zu wenig Kreditvergangenheit';
WHEN NEW. P_Kreditvergangenheit > 5 AND NEW. P_Historischer_Ausfall = 'N' THEN SET entscheidung = 'Genehmigt: Bonus für lange Kreditvergangenheit';
WHEN NEW. P_Zinssatz > 15 THEN SET entscheidung = 'Abgelehnt: Zinssatz zu hoch';
WHEN NEW.P_Historischer_Ausfall = 'Y' AND NEW. P_Kreditvergangenheit < 7 THEN SET entscheidung = 'Abgelehnt: Historischer Ausfall zu riskant';
WHEN NEW. P_Kreditwürdigkeit IN ('C', 'D') AND NEW. P_Kreditsumme > 20000 THEN SET entscheidung = 'Abgelehnt: Zu hoher Kredit für Risiko-Kreditwürdigkeit';
WHEN NEW. P_Arbeitsjahre < 2 AND NEW. P_Kreditsumme > 8000 THEN SET entscheidung = 'Abgelehnt: Zu hoher Kredit für kurze Berufserfahrung';
WHEN NEW.P_Einkommen < 20000 THEN SET entscheidung = 'Abgelehnt: Einkommen zu niedrig';
WHEN NEW. P_Kreditanteil_am_Einkommen > 0.35 THEN SET entscheidung = 'Abgelehnt: Monatliche Belastung zu hoch';
WHEN NEW.P_Kreditsumme > NEW.P_Einkommen * 0.3 THEN SET entscheidung = 'Abgelehnt: Kreditsumme zu hoch im Verhältnis zum Einkommen';
WHEN NEW. P_Kreditwürdigkeit = 'A' AND NEW. P_Kreditsumme > 50000 THEN SET entscheidung = 'Abgelehnt: Zu hoher Kredit für A-Rating';
WHEN NEW. P_Kreditwürdigkeit = 'B' AND NEW. P_Kreditsumme > 30000 THEN SET entscheidung = 'Abgelehnt: Zu hoher Kredit für B-Rating';
WHEN NEW. P_Kreditwürdigkeit = 'C' AND NEW. P_Kreditsumme > 12000 THEN SET entscheidung = 'Abgelehnt: Zu hoher Kredit für C-Rating';
WHEN NEW. P_Kreditwürdigkeit = 'D' AND NEW. P_Kreditsumme > 5000 THEN SET entscheidung = 'Abgelehnt: Zu hoher Kredit für D-Rating';
WHEN NEW. P_Kreditwürdigkeit = 'A' AND NEW. P_Zinssatz > 10 THEN SET entscheidung = 'Abgelehnt: Zinssatz zu hoch für A-Rating';
WHEN NEW. P_Kreditwürdigkeit = 'B' AND NEW. P_Zinssatz > 12 THEN SET entscheidung = 'Abgelehnt: Zinssatz zu hoch für B-Rating';
WHEN NEW. P_Kreditwürdigkeit = 'C' AND NEW. P_Zinssatz > 12 THEN SET entscheidung = 'Abgelehnt: Zinssatz zu hoch für C-Rating';
WHEN NEW. P_Kreditwürdigkeit = 'D' AND NEW. P_Zinssatz > 14 THEN SET entscheidung = 'Abgelehnt: Zinssatz zu hoch für D-Rating';
ELSE SET entscheidung = 'Genehmigt';
END CASE;
INSERT INTO kredit_entscheidungen (kredit_id, kreditstatus)
VALUES (NEW.kredit_id, entscheidung );
END;
//
DELIMITER ;


DELIMITER //
CREATE TRIGGER tr_no_revert_ablehnung
BEFORE UPDATE ON kredit_entscheidungen
FOR EACH ROW
BEGIN
    IF OLD.kreditstatus = 'Abgelehnt' AND NEW.kreditstatus = 'Genehmigt' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Änderung von "Abgelehnt" auf "Genehmigt" ist nicht erlaubt!';
    END IF;
END;
//
DELIMITER ;


DELIMITER //

CREATE TRIGGER tr_no_update_kreditstatus
BEFORE UPDATE ON kredit_entscheidungen
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Änderung des Kreditstatus ist nicht erlaubt!';
END;

//
DELIMITER ;