--
-- Aufgabe 1
--

CREATE DATABASE `ap09_quiz` DEFAULT CHARACTER SET utf8;


CREATE TABLE `tblTeilnehmer` (
`TeilnehmerID` INT( 4 ) NOT NULL ,
`Nachname` VARCHAR( 50 ) NOT NULL ,
`Vorname` VARCHAR( 50 ) NULL ,
`PLZ` VARCHAR( 5 ) NOT NULL ,
`Ort` VARCHAR( 50 ) NOT NULL ,
`Strasse` VARCHAR( 50 ) NOT NULL ,
`Email` VARCHAR( 100 ) NULL ,
PRIMARY KEY ( `TeilnehmerID` )
) ENGINE = innodb CHARACTER SET=utf8;

CREATE TABLE `tblFrage` (
`FrageNr` INT( 2 ) NOT NULL ,
`Frage` VARCHAR( 200 ) NOT NULL ,
PRIMARY KEY ( `FrageNr` )
) ENGINE = innodb CHARACTER SET=utf8;

CREATE TABLE `tblAntwort` (
  `FrageNr` INT( 2 ) NOT NULL ,
  `AntwortNr` VARCHAR( 1 ) NOT NULL ,
  `Antwort` VARCHAR( 100 ) NOT NULL ,
  `Loesung` INT( 2 ) NOT NULL ,
  FOREIGN KEY (`FrageNr`) REFERENCES tblFrage (`FrageNr`),
  PRIMARY KEY  (`FrageNr`,`AntwortNr`)
) ENGINE = innodb CHARACTER SET=utf8;


CREATE TABLE `tblTeilnehmerAntwort` (
  `TeilnehmerID` INT( 4 ) NOT NULL ,
  `FrageNr` INT( 2 ) NOT NULL ,
  `AntwortNr` VARCHAR( 1 ) NOT NULL ,
  FOREIGN KEY (`TeilnehmerID`) REFERENCES tblTeilnehmer (`TeilnehmerID`),
  FOREIGN KEY (`FrageNr`, `AntwortNr`) REFERENCES tblAntwort (`FrageNr`, `AntwortNr`),
  PRIMARY KEY  (`TeilnehmerID`,`FrageNr`,`AntwortNr`)
) ENGINE = innodb CHARACTER SET=utf8;


--
-- Aufgabe 2
--
--
-- Importieren Frage.cvs/Antwort.cvs mit UTF8 Codierung oder einfache inserts
--

INSERT INTO `ap09_quiz`.`tblFrage` (`FrageNr`, `Frage`) 
VALUES
(1, 'Welche Energie ist unerschöpflich?'),
(2, 'Die größte Quelle der Erneuerbaren Energien ist:'),
(3, 'Wie lange braucht eine Windenergie-Anlage um so viel Energie zu produzieren, wie bei ihrem Bau benötigt wurde?'),
(4, 'Was produziert eine Photovoltaik-Anlage?'),
(5, 'Was ist Energie aus Biomasse?');


INSERT INTO `ap09_quiz`.`tblAntwort` ( `FrageNr` , `AntwortNr` , `Antwort` , `Loesung` )
VALUES 
( 1, 'A', 'Atomenergie', 0 ) , 
( 1, 'B', 'Erdöl', 0 ) , 
( 1, 'C', 'Wind, Sonne, Wasser & Co', 1 ) , 
( 2, 'A', 'Sonne', 1 ) , 
( 2, 'B', 'Wasser', 0 ) , 
( 2, 'C', 'Mond', 0 ) , 
( 3, 'A', '6 - 12 Monate', 0 ) , 
( 3, 'B', '1 - 2 Jahre', 0 ) , 
( 3, 'C', 'länger', 1 ) , 
( 4, 'A', 'Wärme', 0 ) , 
( 4, 'B', 'Strom', 1 ) , 
( 4, 'C', 'Biogas', 0 ) , 
( 5, 'A', 'Strom und Wärme aus nachwachsenden Rohstoffen', 1 ) , 
( 5, 'B', 'Strom aus Wasserkraft', 0 ) , 
( 5, 'C', 'Strom aus Windkraft', 0 ) ;


--
-- Aufgabe 3
--

ALTER TABLE `ap09_quiz`.`tblTeilnehmer` CHANGE `TeilnehmerID` `TeilnehmerID` INT( 4 ) NOT NULL AUTO_INCREMENT;


INSERT INTO `ap09_quiz`.`tblTeilnehmer` 
(`TeilnehmerID` ,`Nachname` ,`Vorname` ,`PLZ` ,`Ort` ,`Strasse` ,`Email`)
VALUES
(1 , 'Mustermann', 'Max', '12345', 'Musterhausen', 'musterstr', 'muster@gmx.de'),
(2 , 'Testus', 'Henriette', '12354', 'Tessin', 'Teststr.', NULL),
(3 , 'Demo', 'Dorrothea', '21345', 'Demohausen', 'Demostr.', 'demo@gmx.de'),
(4 , 'Meier', 'Klaus-Heinrich', '12879', 'Idar Oberstein', 'Hochstr.', NULL), 
(5 , 'Zilgid', 'Isidor', '74935', 'Zwergenhausen', 'Waldgasse', 'frodo@elfenland.de'), 
(6 , 'Erpel', 'Emil', '54736', 'Entenhausen', 'Tuempelallee', NULL), 
(7 , 'Schwammkopf', 'Spongebob', '56784', 'Bikini Bottom', 'In der Ananas 3', 'quallenfischenjuchey@ichbinbereit.de');

-- die teilnehmerID kann durch das auto_increment auch leer gelassen werden

INSERT INTO `ap09_quiz`.`tblTeilnehmerAntwort` (`TeilnehmerID` ,`FrageNr` ,`AntwortNr`)
VALUES 
('1', '1', 'A'), 
('1', '2', 'B'),
('1', '3', 'C'),
('1', '4', 'A'),
('1', '5', 'B'),
('2', '1', 'B'),
('2', '2', 'A'),
('2', '3', 'C'),
('2', '4', 'B'),
('2', '5', 'A'),
('3', '1', 'C'),
('3', '2', 'B'),
('3', '3', 'A'),
('3', '4', 'B'),
('3', '5', 'A'),
('4', '1', 'A'), 
('4', '2', 'B'),
('4', '3', 'C'),
('4', '4', 'A'),
('4', '5', 'B'),
('5', '1', 'C'),
('5', '2', 'A'),
('5', '3', 'C'),
('5', '4', 'B'),
('5', '5', 'A'),
('6', '1', 'C'),
('6', '2', 'B'),
('6', '3', 'A'),
('6', '4', 'B'),
('6', '5', 'A'),
('7', '1', 'C'),
('7', '2', 'A'),
('7', '3', 'C'),
('7', '4', 'B'),
('7', '5', 'A');


--
-- Aufgabe 4
--

SELECT *
FROM tblTeilnehmer
WHERE TeilnehmerID NOT
IN (
 SELECT TeilnehmerID
 FROM tblTeilnehmerAntwort
 INNER JOIN tblAntwort ON tblTeilnehmerAntwort.FrageNr = tblAntwort.FrageNr
 AND tblTeilnehmerAntwort.AntwortNr = tblAntwort.AntwortNr
 WHERE tblAntwort.Loesung =0
);