DROP TABLE fischer CASCADE CONSTRAINTS;
DROP TABLE fisch CASCADE CONSTRAINTS;
DROP TABLE fang CASCADE CONSTRAINTS;


CREATE TABLE fischer(
	fnr INT ,
	fname VARCHAR2(30),
	lizenznr INT,
	latein VARCHAR2(1),
	gebdatum DATE,
	boot VARCHAR2(30),
  CONSTRAINT pk_fischer PRIMARY KEY (fnr),
	CONSTRAINT ck_latein CHECK (latein IN ('j','n'))
);

CREATE TABLE fisch(
	finr INT,
	finame VARCHAR2(30),
  CONSTRAINT pk_fisch PRIMARY KEY (finr)
);

CREATE TABLE fang(
	fangnr INT,
	fnr INT,
	finr INT,
	gewicht INT,
  CONSTRAINT pk_fang PRIMARY KEY (fangnr),
	CONSTRAINT fk_fanr FOREIGN key (fnr) REFERENCES fischer(fnr),
	CONSTRAINT fk_finr FOREIGN key (finr) REFERENCES fisch(finr)
);

INSERT INTO fischer VALUES(1,'Fischer Emil',1000,'j',TO_DATE('14.02.1992'),'Opel Admiral');
INSERT INTO fischer VALUES(2,'Fischer Zeus',1001,'n',TO_DATE('11.02.1992'),'Opel Corsa');
INSERT INTO fischer VALUES(3,'Fischer Dorli',1002,'j',TO_DATE('06.02.1991'),'Opel Rekord');
INSERT INTO fischer VALUES(4,'Fischer Vreni',1003,'n',TO_DATE('06.02.1989'),'Opel Corsa');
INSERT INTO fischer VALUES(5,'Fischer Franz',1004,'j',TO_DATE('06.02.1989'),'Isetta');
INSERT INTO fischer VALUES(6,'Fischer Siegi',1005,'j',TO_DATE('06.02.1989'),'Opel Corsa');
INSERT INTO fischer VALUES(7,'Fischer Susi',1006,'j',TO_DATE('06.02.1989'),'NSU Prinz 1200');
INSERT INTO fischer VALUES(8,'Anton',1007,'n',TO_DATE('24.01.2006'),'Opel Corsa');
INSERT INTO fischer VALUES(9,'Susi',1008,'j',TO_DATE('06.02.1989'),'Isetta');
INSERT INTO fischer VALUES(10,'Amir',1009,'j',TO_DATE('06.02.1989'),'NSU Prinz 1200');
INSERT INTO fischer VALUES(11,'Ausi',1066,'j',TO_DATE('06.02.1969'),'Opel Kadett');
INSERT INTO fischer VALUES(12,'Fischer A',1007,'n',TO_DATE('20.09.2005'),'Isetta');
INSERT INTO fischer VALUES(13,'Fischer A',1007,'n',TO_DATE('19.09.2005'),'NSU Prinz 1200');
INSERT INTO fischer VALUES(14,'Fischer C',1007,'n',TO_DATE('21.09.2005'),'Opel Diplomat');
INSERT INTO fischer VALUES(15,'Amir',1019,'n',TO_DATE('06.02.1979'),'NSU Prinz 1200');

INSERT INTO fisch VALUES(1,'Aal');
INSERT INTO fisch VALUES(2,'Forelle');
INSERT INTO fisch VALUES(3,'Hai');
INSERT INTO fisch VALUES(4,'Wal');
INSERT INTO fisch VALUES(5,'Seepferd');
INSERT INTO fisch VALUES(6,'Tintenfisch');
INSERT INTO fisch VALUES(7,'Scampi');
INSERT INTO fisch VALUES(8,'Mur‰ne');

INSERT INTO fang VALUES(1,1,1,1000);
INSERT INTO fang VALUES(2,1,2,1001);
INSERT INTO fang VALUES(3,1,3,1002);
INSERT INTO fang VALUES(4,1,4,1003);
INSERT INTO fang VALUES(5,2,5,1);
INSERT INTO fang VALUES(6,2,6,2);
INSERT INTO fang VALUES(7,1,7,15);
INSERT INTO fang VALUES(8,1,1,15);
INSERT INTO fang VALUES(9,1,1,9);
INSERT INTO fang VALUES(10,2,1,10);
INSERT INTO fang VALUES(11,2,6,20);
INSERT INTO fang VALUES(13,10,1,11);

a)	Gib von allen Fischern, die Latein kˆnnen, den Namen aus, sofern dieser
		mindestens 5 Buchstaben hat. Hat er weniger Buchstaben, so darf der Fischer
		nur dann in Ausgabe, wenn der Name mit 'A' anf‰ngt und  mit 'R' aufhˆrt,
		Latein ist nach wie vor Voraussetzung.

SELECT * FROM fischer
WHERE latein = 'j' AND (LENGTH(fname) >= 5 OR UPPER(fname) LIKE 'A%R');

b) Gib jene Fischer aus, die bisher noch nichts gefangen haben und auch keinen Opel fahren.

SELECT * FROM fischer LEFT JOIN fang ON fischer.fnr = fang.fnr
 WHERE fang.fnr IS NULL AND boot NOT LIKE '%Opel%';

 oder

SELECT * FROM fischer
 WHERE fnr NOT IN (SELECT fnr FROM fang) AND boot NOT LIKE '%Opel%';

c) Welche Fischer sind an einem Dienstag geboren und haben das 12. Lebensjahr noch nicht vollendet?

SELECT fnr, fname, TO_CHAR(gebdatum,'DAY') AS "Wochentag", gebdatum FROM fischer
 WHERE TO_CHAR(gebdatum,'DAY') LIKE 'DIE%' AND gebdatum + INTERVAL '12' YEAR > SYSDATE;


d) Welche Fischer sind ¸berdurchschnittlich alt? Der ƒlteste soll jedoch nicht ausgegeben werden.
SELECT * FROM fischer
 WHERE (SYSDATE - gebdatum) > (SELECT AVG(SYSDATE - gebdatum) FROM fischer)
   AND gebdatum > (SELECT MIN(gebdatum) FROM fischer);

e) Welche Fischer haben zwar zumindest einen Aal aber noch nie einen Esel gefangen?
SELECT * FROM fischer INNER JOIN fang ON fischer.fnr = fang.fnr
                      INNER JOIN fisch ON fang.finr = fisch.finr
 WHERE finame LIKE 'Aal'
   AND fischer.fnr NOT IN ( SELECT fang.fnr FROM fang INNER JOIN fisch ON fang.finr = fisch.finr
                             WHERE finame LIKE 'Esel'
                          );



--Welche Bootsklassen haben die meisten Fischer und wie viele sind dasfl

SELECT COUNT(*), boot
    FROM fischer
  GROUP BY boot
  HAVING COUNT(*) >= ALL (
                            SELECT COUNT(*)
                            FROM fischer
                            GROUP BY boot
  );

--Welcher Fischer hat die meisten Fische gefangen und wieviele Fische sind das?

SELECT fischer COUNT(*) FROM  fang
  GROUP BY fischer
  HAVING COUNT(*) = (
                      SELECT MAX (COUNT(*)) FROM fang
                      GROUP BY fischer
  )