PMG2426 1/DB Beleg/Klara
Aus ProgrammingWiki
Wiki-Quelltext dieser Seite.
<language>SQL</language>
<code id="69830ae6bd747">CREATE TABLE IF NOT EXISTS AUTOR (
Autor_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Vorname VARCHAR(50) NOT NULL,
Nachname VARCHAR(50) NOT NULL,
Geburtsjahr INT,
Nationalitaet VARCHAR(50)
);
CREATE TABLE IF NOT EXISTS BUCH (
Buch_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Titel VARCHAR(200) NOT NULL,
ISBN VARCHAR(17),
Erscheinungsjahr INT,
Seitenzahl INT,
Kategorie VARCHAR(50),
Autor_ID INTEGER NOT NULL,
FOREIGN KEY (Autor_ID) REFERENCES AUTOR(Autor_ID)
);
CREATE TABLE IF NOT EXISTS SCHUELER (
Schueler_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Vorname VARCHAR(50) NOT NULL,
Nachname VARCHAR(50) NOT NULL,
Geburtsdatum DATE NOT NULL,
Klasse VARCHAR(10),
Email VARCHAR(100),
Telefon VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS AUSLEIHE (
Ausleihe_ID INTEGER PRIMARY KEY AUTOINCREMENT,
Buch_ID INTEGER NOT NULL,
Schueler_ID INTEGER NOT NULL,
Ausleihedatum DATE NOT NULL,
Geplante_Rueckgabe DATE NOT NULL,
Tatsaechliche_Rueckgabe DATE,
Status VARCHAR(20) NOT NULL DEFAULT 'ausgeliehen',
FOREIGN KEY (Buch_ID) REFERENCES BUCH(Buch_ID),
FOREIGN KEY (Schueler_ID) REFERENCES SCHUELER(Schueler_ID)
);
CREATE INDEX IF NOT EXISTS idx_buch_titel ON BUCH(Titel);
CREATE INDEX IF NOT EXISTS idx_schueler_nachname ON SCHUELER(Nachname);
CREATE INDEX IF NOT EXISTS idx_ausleihe_status ON AUSLEIHE(Status);
DELETE FROM AUSLEIHE;
DELETE FROM SCHUELER;
DELETE FROM BUCH;
DELETE FROM AUTOR;
INSERT INTO AUTOR (Vorname, Nachname, Geburtsjahr, Nationalitaet) VALUES
('Johann Wolfgang', 'Goethe', 1749, 'Deutsch'),
('Friedrich', 'Schiller', 1759, 'Deutsch'),
('Hermann', 'Hesse', 1877, 'Deutsch-Schweizer'),
('Franz', 'Kafka', 1883, 'Tschechisch'),
('Thomas', 'Mann', 1875, 'Deutsch'),
('Erich Maria', 'Remarque', 1898, 'Deutsch'),
('J.K.', 'Rowling', 1965, 'Britisch'),
('George', 'Orwell', 1903, 'Britisch'),
('Agatha', 'Christie', 1890, 'Britisch'),
('Ernest', 'Hemingway', 1899, 'Amerikanisch');
INSERT INTO BUCH (Titel, ISBN, Erscheinungsjahr, Seitenzahl, Kategorie, Autor_ID) VALUES
('Die Leiden des jungen Werther', '978-3-15-000067-0', 1774, 148, 'Roman', 1),
('Faust - Der Tragödie erster Teil', '978-3-15-000001-4', 1808, 176, 'Drama', 1),
('Wilhelm Tell', '978-3-15-000012-0', 1804, 144, 'Drama', 2),
('Die Räuber', '978-3-15-000015-1', 1781, 160, 'Drama', 2),
('Der Steppenwolf', '978-3-518-36675-9', 1927, 288, 'Roman', 3),
('Siddhartha', '978-3-518-36627-8', 1922, 136, 'Roman', 3),
('Die Verwandlung', '978-3-15-009900-6', 1915, 80, 'Erzählung', 4),
('Der Prozess', '978-3-596-90120-5', 1925, 312, 'Roman', 4),
('Buddenbrooks', '978-3-10-048001-5', 1901, 736, 'Roman', 5),
('Der Zauberberg', '978-3-10-048002-2', 1924, 1008, 'Roman', 5),
('Im Westen nichts Neues', '978-3-462-00001-0', 1929, 288, 'Roman', 6),
('Harry Potter und der Stein der Weisen', '978-3-551-55167-2', 1997, 336, 'Fantasy', 7),
('Harry Potter und die Kammer des Schreckens', '978-3-551-55168-9', 1998, 352, 'Fantasy', 7),
('1984', '978-3-548-23415-9', 1949, 352, 'Science-Fiction', 8),
('Farm der Tiere', '978-3-257-20118-5', 1945, 144, 'Fabel', 8),
('Mord im Orient-Express', '978-3-596-17277-7', 1934, 272, 'Krimi', 9),
('Der alte Mann und das Meer', '978-3-499-23652-4', 1952, 128, 'Roman', 10);
INSERT INTO SCHUELER (Vorname, Nachname, Geburtsdatum, Klasse, Email, Telefon) VALUES
('Max', 'Müller', '2008-03-15', '10a', 'max.mueller@schule.de', '0123-456789'),
('Anna', 'Schmidt', '2007-07-22', '11b', 'anna.schmidt@schule.de', '0123-456790'),
('Leon', 'Weber', '2009-11-08', '9c', 'leon.weber@schule.de', '0123-456791'),
('Sophie', 'Fischer', '2008-05-30', '10b', 'sophie.fischer@schule.de', '0123-456792'),
('Paul', 'Wagner', '2007-01-18', '11a', 'paul.wagner@schule.de', '0123-456793'),
('Emma', 'Becker', '2009-09-25', '9a', 'emma.becker@schule.de', '0123-456794'),
('Felix', 'Koch', '2008-12-03', '10a', 'felix.koch@schule.de', '0123-456795'),
('Lena', 'Hoffmann', '2007-04-14', '11c', 'lena.hoffmann@schule.de', '0123-456796'),
('Jonas', 'Schulz', '2009-06-21', '9b', 'jonas.schulz@schule.de', '0123-456797'),
('Mia', 'Zimmermann', '2008-08-09', '10c', 'mia.zimmermann@schule.de', '0123-456798');
INSERT INTO AUSLEIHE (Buch_ID, Schueler_ID, Ausleihedatum, Geplante_Rueckgabe, Tatsaechliche_Rueckgabe, Status) VALUES
(1, 1, '2026-01-10', '2026-01-24', '2026-01-23', 'zurueckgegeben'),
(2, 2, '2026-01-12', '2026-01-26', '2026-01-25', 'zurueckgegeben'),
(5, 3, '2026-01-15', '2026-01-29', '2026-01-28', 'zurueckgegeben'),
(7, 4, '2026-01-18', '2026-02-01', '2026-02-01', 'zurueckgegeben'),
(12, 1, '2026-01-25', '2026-02-08', NULL, 'ausgeliehen'),
(13, 2, '2026-01-28', '2026-02-11', NULL, 'ausgeliehen'),
(14, 5, '2026-02-01', '2026-02-15', NULL, 'ausgeliehen'),
(15, 6, '2026-02-02', '2026-02-16', NULL, 'ausgeliehen'),
(16, 7, '2026-02-03', '2026-02-17', NULL, 'ausgeliehen'),
(3, 3, '2026-01-05', '2026-01-19', NULL, 'ueberfaellig'),
(6, 4, '2026-01-08', '2026-01-22', NULL, 'ueberfaellig'),
(9, 8, '2026-01-10', '2026-01-24', NULL, 'ueberfaellig');
CREATE VIEW IF NOT EXISTS verfuegbare_buecher AS
SELECT b.Buch_ID, b.Titel, b.ISBN, a.Vorname AS Autor_Vorname, a.Nachname AS Autor_Nachname,
b.Kategorie, b.Erscheinungsjahr
FROM BUCH b
JOIN AUTOR a ON b.Autor_ID = a.Autor_ID
WHERE b.Buch_ID NOT IN (
SELECT Buch_ID FROM AUSLEIHE WHERE Status IN ('ausgeliehen','ueberfaellig')
);
CREATE VIEW IF NOT EXISTS aktuelle_ausleihen AS
SELECT au.Ausleihe_ID, b.Titel, b.ISBN, a.Nachname AS Autor,
s.Vorname AS Schueler_Vorname, s.Nachname AS Schueler_Nachname, s.Klasse,
au.Ausleihedatum, au.Geplante_Rueckgabe, au.Status,
julianday('now') - julianday(au.Geplante_Rueckgabe) AS Tage_ueberfaellig
FROM AUSLEIHE au
JOIN BUCH b ON au.Buch_ID = b.Buch_ID
JOIN AUTOR a ON b.Autor_ID = a.Autor_ID
JOIN SCHUELER s ON au.Schueler_ID = s.Schueler_ID
WHERE au.Status IN ('ausgeliehen','ueberfaellig');
</code>
<run id="69830b411bc1f">SELECT * FROM verfuegbare_buecher;
SELECT Kategorie, COUNT(*) as Anzahl_Buecher
FROM BUCH
GROUP BY Kategorie
ORDER BY Anzahl_Buecher DESC;
SELECT s.Nachname, b.Titel,
ROUND((julianday('now') - julianday(au.Geplante_Rueckgabe)) * 0.50, 2) AS Mahngebuehr_EUR
FROM AUSLEIHE au
JOIN SCHUELER s ON au.Schueler_ID = s.Schueler_ID
JOIN BUCH b ON au.Buch_ID = b.Buch_ID
WHERE au.Status = 'ueberfaellig';
SELECT
s.Vorname, s.Nachname, s.Klasse,
COUNT(au.Ausleihe_ID) as Gesamt_Ausleihen,
SUM(CASE WHEN au.Status = 'ueberfaellig'
THEN CAST((julianday('now') - julianday(au.Geplante_Rueckgabe)) AS INTEGER)
ELSE 0 END) as Ueberfaellige_Tage
FROM SCHUELER s
LEFT JOIN AUSLEIHE au ON s.Schueler_ID = au.Schueler_ID
WHERE au.Status IN ('ausgeliehen', 'ueberfaellig') OR au.Schueler_ID IS NULL
GROUP BY s.Schueler_ID
ORDER BY Gesamt_Ausleihen DESC, Ueberfaellige_Tage DESC;
</run>