[DB] select_pres.sql

CONNECT TO dbs;

-- a) Welche Praesidenten wurden wann im Bundesstaat "Texas" geboren?
SELECT pres_name, birth_year
FROM president
WHERE state_born = 'Texas';

-- b) Welche Praesidenten sind noch nicht gestorben?
SELECT p.pres_name, p.birth_year, p.years_serv, p.party, p.state_born
FROM president p
WHERE death_age IS NULL;

-- c) Was sind die verschiedenen (DISTINCT) Parteien aller bisherigen Praesidenten?
SELECT DISTINCT party
FROM president;

-- d) Geben Sie alle(!!) in der Datenbank verfuegbaren Informationen ueber Bush aus!
SELECT *
FROM president p
    LEFT JOIN state s ON p.state_born = s.state_name
    LEFT OUTER JOIN pres_hobby h ON p.pres_name = h.pres_name
    LEFT OUTER JOIN pres_marriage m ON p.pres_name = m.pres_name
    LEFT OUTER JOIN election e ON p.pres_name = e.candidate
WHERE p.pres_name LIKE '%Bush%';

-- e) Welche Praesidenten sind Republikaner oder in Texas geboren?
SELECT *
FROM president
WHERE party = 'Republican' OR state_born = 'Texas';

-- f) Welche Praesidenten waren genau 4, 8 oder 12 Jahre im Amt?
SELECT *
FROM president
WHERE years_serv IN (4, 8, 12);

-- g) Geben Sie die Bundesstaaten sortiert nach dem Eintrittsjahr aus.
SELECT *
FROM state
ORDER BY year_entered;

-- h) Wieviele Wahlkandidaten haben verloren und wieviele gewonnen? (Dabei sollen dieselben Wahlkandidaten in unterschiedlichen Wahlen mehrfach zaehlen, d.h. Duplikatelimination soll nicht angewendet werden!)
SELECT COUNT(*) AS COUNT
FROM election
WHERE winner_loser_indic = 'L'
UNION
SELECT COUNT(*)
FROM election
WHERE winner_loser_indic = 'W';

-- i) Wer hat die meisten Stimmen in einer Wahl erhalten?
SELECT *
FROM president p
    INNER JOIN election e ON p.pres_name = e.candidate
WHERE e.votes = (
    SELECT MAX(votes)
    FROM election
);

-- j) Welcher Präsident hat nicht zweimal gewonnen?
SELECT *
FROM president
WHERE pres_name IN (
    SELECT candidate
    FROM election
    WHERE winner_loser_indic = 'W'
    GROUP BY candidate
    HAVING COUNT(candidate) <> 2
);