CREATE TABLE medium (
    id        INTEGER NOT NULL AUTO_INCREMENT,
    name      VARCHAR(100) NOT NULL,
    jahr      SMALLINT NOT NULL,
    typ       CHAR(1) NOT NULL,
    isbn      INTEGER UNSIGNED,
    label     VARCHAR(100),
    regisseur VARCHAR(100),
    PRIMARY KEY(id));
    
ALTER TABLE medium ADD anschaffungspreis DECIMAL(8,2) DEFAULT 33.20;
	
CREATE TABLE exemplar (
	id        INTEGER NOT NULL AUTO_INCREMENT,
	medium_id INTEGER NOT NULL,
	signatur  CHAR(5) NOT NULL,
	PRIMARY KEY(id));

ALTER TABLE exemplar ADD UNIQUE (signatur);
ALTER TABLE exemplar ADD FOREIGN KEY (medium_id) REFERENCES medium(id);

DELIMITER //
DROP PROCEDURE IF EXISTS empty_checker //
CREATE PROCEDURE empty_checker (field_name VARCHAR(50), data TEXT)
BEGIN
DECLARE mt VARCHAR(59);
DECLARE CUSTOM_EXCEPTION CONDITION FOR SQLSTATE '45000';
IF data = '' THEN
  SELECT CONCAT(field_name,' must not be empty') INTO mt;
--  SIGNAL CUSTOM_EXCEPTION SET MESSAGE_TEXT = CONCAT(field_name, ' must not be empty');
  SIGNAL CUSTOM_EXCEPTION SET MESSAGE_TEXT = mt;
END IF;
END //

DROP TRIGGER IF EXISTS expl_sign_ins //
CREATE TRIGGER expl_sign_ins BEFORE INSERT ON exemplar FOR EACH ROW BEGIN
CALL empty_checker('signatur', NEW.signatur);
END //

DROP TRIGGER IF EXISTS expl_sign_upd //
CREATE TRIGGER expl_sign_upd BEFORE UPDATE ON exemplar FOR EACH ROW BEGIN
CALL empty_checker('signatur', NEW.signatur);
END //
DELIMITER ;

CREATE TABLE nutzer (
	id         INTEGER NOT NULL AUTO_INCREMENT,
	vorname    VARCHAR(100) NOT NULL,
	nachname   VARCHAR(100) NOT NULL,
	plz        SMALLINT UNSIGNED NOT NULL,
	ort        VARCHAR(100) NOT NULL,
	geburtstag DATE NOT NULL,
	PRIMARY KEY(id));

ALTER TABLE nutzer ADD UNIQUE (vorname,nachname,geburtstag);

CREATE TABLE ausleihe (
	nutzer_id   INTEGER NOT NULL,
	exemplar_id INTEGER NOT NULL,
	ruckgabe    BOOLEAN NOT NULL,
	PRIMARY KEY(nutzer_id,exemplar_id));
ALTER TABLE ausleihe ADD FOREIGN KEY (nutzer_id) REFERENCES nutzer(id);
ALTER TABLE ausleihe ADD FOREIGN KEY (exemplar_id) REFERENCES exemplar(id);

DELIMITER //
DROP TRIGGER IF EXISTS ausl_ins //
CREATE TRIGGER ausl_ins AFTER INSERT ON ausleihe FOR EACH ROW BEGIN
DECLARE anz INTEGER;
DECLARE mid INTEGER;

SELECT medium_id FROM exemplar WHERE id = NEW.exemplar_id INTO mid;
SELECT anzahl FROM ausleihestat WHERE medium_id = mid INTO anz;
IF anz IS NULL
THEN
  INSERT INTO ausleihestat VALUES (mid, 0, 1);
ELSE
  UPDATE ausleihestat SET anzahl = anz + 1 WHERE medium_id = mid;
END IF;
END //
DELILITER ;

CREATE TABLE ausleihestat (
    medium_id INTEGER NOT NULL PRIMARY KEY,
    modif     INTEGER NOT NULL,
    anzahl    INTEGER NOT NULL);

	
INSERT INTO medium VALUES 
	(NULL, 'CName', 1999, 'B', 123345589, NULL, NULL),
	(NULL, 'XName', 2000, 'B', 123345546, NULL, NULL);
	(NULL, 'MName', 2000, 'B', 123345546, NULL, NULL,33.44);
	(NULL, 'CName', 1999, 'C', NULL, 'X-Records', NULL);
	(NULL, 'DName', 2000, 'C', NULL, 'X-Records', NULL);
	(NULL, 'FName', 1999, 'D', NULL, NULL, 'Bully');
	(NULL, 'FName', 2000, 'D', NULL, NULL, 'Bully');
INSERT INTO nutzer VALUES
	(NULL, 'Hans', 'Maier', 12344, 'Neustadt', '2000-12-12');
	(NULL, 'Hans-A', 'Maier', 12344, 'Neustadt', '2001-12-12'),
	(NULL, 'Hans-B', 'Maier', 12344, 'Neustadt', '2002-12-12'),
	(NULL, 'Hans-C', 'Maier', 12344, 'Neustadt', '2003-12-12');

INSERT INTO exemplar VALUES
	(NULL, 1, 'A1'),
	(NULL, 2, 'A2'),
	(NULL, 3, 'A3'),
	(NULL, 4, 'A4'),
	(NULL, 5, 'A5'),
	(NULL, 6, 'A6'),
	(NULL, 7, 'A7'),
	(NULL, 8, 'A8'),
	(NULL, 8, 'A8-1'),
	(NULL, 8, 'A8-2'),
	(NULL, 9, 'A9');

INSERT INTO ausleihe VALUES
	(1, 2, FALSE),
	(1, 3, FALSE),
	(2, 3, TRUE),
	(2, 4, FALSE),
	(2, 5, FALSE),
	(3, 5, TRUE),
	(4, 5, FALSE),
	(4, 6, TRUE),
	(3, 7, FALSE);

% Aufgaben für eine Tabelle
SELECT * FROM medium WHERE jahr = 1999;
SELECT name, anschaffungspreis FROM medium WHERE typ = 'C' AND anschaffungspreis < 15;
SELECT * FROM medium WHERE typ = 'D' AND jahr BETWEEN 2000 AND 2020;
SELECT * FROM medium WHERE typ IN ('C','D') ORDER BY jahr;
SELECT * FROM medium WHERE typ = 'B' AND name LIKE 'M%';
SELECT * FROM medium WHERE typ = 'B' AND isbn IS NULL;

SELECT vorname, nachname, exemplar_id FROM nutzer,ausleihe WHERE nutzer.id = nutzer_id;

SELECT vorname, nachname, exemplar_id, COALESCE(isbn,'kein Buch') as ISBN FROM nutzer 
	JOIN ausleihe ON nutzer.id = nutzer_id
	JOIN exemplar ON exemplar_id = exemplar.id
	JOIN medium ON medium_id = medium.id
	WHERE vorname LIKE '%A';

CREATE TABLE buch (
    id        INTEGER NOT NULL AUTO_INCREMENT,
    name      VARCHAR(100) NOT NULL,
    jahr      SMALLINT NOT NULL,
    isbn      INTEGER UNSIGNED,
    PRIMARY KEY(id));

INSERT INTO buch SELECT (id, name, jahr, isbn) from medium WHERE typ = 'B';

SELECT vorname, nachname, exemplar_id, COALESCE(isbn,'kein Buch') as ISBN FROM nutzer 
	JOIN ausleihe ON nutzer.id = nutzer_id
	JOIN exemplar ON exemplar_id = exemplar.id
	LEFT JOIN buch ON medium_id = buch.id;

SELECT ausleihe.* FROM ausleihe
	JOIN exemplar ON exemplar.id = exemplar_id
	WHERE signatur = 'A1';
SELECT * FROM ausleihe
	WHERE EXISTS 
	( SELECT * FROM exemplar 
	  WHERE exemplar.id = exemplar_id
	  AND signatur = 'A1');
SELECT * FROM ausleihe
	WHERE exemplar_id =
	( SELECT id FROM exemplar 
	  WHERE signatur = 'A1');
	  
SELECT * FROM exemplar
	WHERE NOT EXISTS 
	( SELECT * FROM ausleihe 
	  WHERE exemplar.id = exemplar_id);
SELECT exemplar.* FROM exemplar
	LEFT JOIN ausleihe ON exemplar.id = exemplar_id
	WHERE ruckgabe IS NULL;
	
SELECT COUNT(*) FROM medium;
SELECT typ, COUNT(*) FROM medium GROUP BY typ;
SELECT jahr, COUNT(*) FROM medium GROUP BY jahr;
SELECT typ, COUNT(*) FROM medium WHERE jahr < 2000 GROUP BY typ;
SELECT jahr, COUNT(*) FROM medium GROUP BY jahr HAVING COUNT(*) > 4;

SELECT COUNT(*) FROM exemplar;
SELECT typ, COUNT(*) FROM exemplar
	JOIN medium ON medium_id = medium.id GROUP BY typ;
SELECT jahr, COUNT(*) FROM exemplar
	JOIN medium ON medium_id = medium.id GROUP BY jahr;
SELECT typ, COUNT(*) FROM exemplar
	JOIN medium ON medium_id = medium.id WHERE jahr < 2000 GROUP BY typ;
SELECT jahr, COUNT(*) FROM exemplar
	JOIN medium ON medium_id = medium.id GROUP BY jahr HAVING COUNT(*) > 4;

CREATE TABLE alte_ausleihen (
  vorname    VARCHAR(100) NOT NULL,
	nachname   VARCHAR(100) NOT NULL,
	signatur  CHAR(5) NOT NULL );


