-- Aufgabe 2
-- a
select s.MatrNr, s.Name, sum(v.SWS)
from Studenten s, prüfen p, Vorlesungen v
where s.MatrNr = p.MatrNr
 and p.VorlNr = v.VorlNr
group by s.MatrNr, s.Name;

----------------------------------------------------------
-- b unter DB2 und Oracle + durch || ersetzen!
select s.MatrNr, s.Name
from Studenten s, Professoren p
where s.Name like '%' + p.Name + '%';

----------------------------------------------------------
-- c (nicht unter SQL Server)
insert into hören 
(select s.MatrNr, v.VorlNr
 from Studenten s, Vorlesungen v, Professoren p
 where p.Name = 'Sokrates' and p.PersNr = v.gelesenVon
 and (s.MatrNr, v.VorlNr) not in (select * from hören));

-- Alternative mit "not exists"
insert into hören (MatrNr, VorlNr)
 select s.MatrNr, v.VorlNr
 from Studenten s, Vorlesungen v, Professoren p
 where p.Name = 'Sokrates' and p.PersNr = v.gelesenVon  
 and not exists (select * 
				 from hören h
				 where h.MatrNr = s.MatrNr
				  and h.VorlNr = v.VorlNr)

-- Alternative mit Subtraktion
insert into hören (MatrNr, VorlNr) 
((select s.MatrNr, v.VorlNr
	from Studenten s, Vorlesungen v, Professoren p
	where p.Name = 'Sokrates'
	and p.PersNr = v.gelesenVon)
	except 
	(select *
	from hören))

----------------------------------------------------------
-- d
create view kenntprof as
 select h.MatrNr, v.gelesenVon as PersNr
 from hören h, Vorlesungen v
 where v.VorlNr = h.VorlNr
 union
 select p.MatrNr, p.PersNr
 from prüfen p;

select k.PersNr, p.Name, count(*) as grad
from kenntprof k, Professoren p
where k.PersNr = p.PersNr
group by k.PersNr, p.Name
order by grad desc;



-- Aufgabe 3
----------------------------------------------------------
-- a
create view KandidatenstimmenBY as
select wk.Nr as Wahlkreis, k.Partei,
  sum(e.Stimmen) as AnzahlESWK
from Direktkandidaten k, Wahlbezirke wb, Wahlkreise wk,
  Erststimmen e
where wk.Bundesland = 'Bayern' and wb.Wahlkreis = wk.Nr
 and e.Wahlbezirk = wb.Nr and e.Jahr = 2005
group by wk.Nr, k.Partei;

create view DirektmandateBY as
select ks.Partei, ks.AnzahlESWK
from KandidatenstimmenBY ks where not exists
  (select * from KandidatenstimmenBY
   where AnzahlESWK > ks.AnzahlESWK
    and Wahlkreis = ks.Wahlkreis);

select Partei from DirektmandateBY where Partei != 'CSU';


----------------------------------------------------------
-- Aufgabe 3 b
create view StimmenProLand as
select zs.Partei, sum(zs.Stimmen) as AnzStimmen,
  wk.Bundesland
from Zweitstimmen zs, Wahlbezirke wb, Wahlkreise wk
where zs.Wahlbezirk = wb.nr and wb.Wahlkreis = wk.nr
group by zs.Partei, wk.Bundesland


select Partei, count(*) as gewonnen
from StimmenProLand
where not exists( select *
                  from StimmenProLand spl2
                  where Partei != spl2.Partei
                   and AnzStimmen < spl2.AnzStimmen
                   and Bundesland = spl2.Bundesland)
group by Partei;

-- alternative Formulierung mit "having"

select tmp.Partei, count(*) as gewonnen
from
	(select spl1.Partei, spl1.Bundesland, spl1.AnzStimmen
	from StimmenProLand spl1
	group by spl1.Partei, spl1.Bundesland, spl1.AnzStimmen
	having spl1.AnzStimmen = (select max(spl2.AnzStimmen)
						                from StimmenProLand spl2
						                where spl1.Bundesland = spl2.Bundesland)) tmp
group by tmp.Partei

----------------------------------------------------------