Projekt: die Bibliotheksdatenbank

-- Create the table members (id, name, email, city, joined)
-- and add these four members in one INSERT:
--   1  Ada       ada@example.com       Berlin  2025-01-15
--   2  Grace     grace@example.com     Munich  2025-03-02
--   3  Linus     (no email)            Berlin  2026-02-10
--   4  Margaret  margaret@example.com  (no city)  2026-05-20
-- Then list them, ordered by id.

-- test: members hat die Spalten id (integer), name, email, city (text) und joined (date)
SELECT count(*) = 5 FROM information_schema.columns
WHERE table_name = 'members'
  AND ((column_name = 'id' AND data_type = 'integer')
    OR (column_name IN ('name', 'email', 'city') AND data_type = 'text')
    OR (column_name = 'joined' AND data_type = 'date'));

-- test: Die Tabelle enthält vier Mitglieder
SELECT count(*) = 4 FROM members;

-- test: Linus hat keine E-Mail (NULL, nicht der Text 'NULL')
SELECT EXISTS (SELECT 1 FROM members WHERE id = 3 AND name = 'Linus' AND email IS NULL AND city = 'Berlin' AND joined = '2026-02-10');

-- test: Margaret hat keine Stadt (NULL)
SELECT EXISTS (SELECT 1 FROM members WHERE id = 4 AND name = 'Margaret' AND email = 'margaret@example.com' AND city IS NULL AND joined = '2026-05-20');

-- test: Das Skript listet die vier Mitglieder auf, nach id sortiert
-- output:
--  id |   name   |        email         |  city  |   joined
-- ----+----------+----------------------+--------+------------
--   1 | Ada      | ada@example.com      | Berlin | 2025-01-15
--   2 | Grace    | grace@example.com    | Munich | 2025-03-02
--   3 | Linus    |                      | Berlin | 2026-02-10
--   4 | Margaret | margaret@example.com |        | 2026-05-20
-- (4 rows)

-- setup.sql has created books and members.
-- Create loans (id, book_id, member_id, loaned_on, returned_on)
-- and add these five loans in one INSERT:
--   1  book 1  member 1  2026-09-01  2026-09-10
--   2  book 3  member 1  2026-09-12  (open)
--   3  book 2  member 2  2026-09-05  2026-09-20
--   4  book 1  member 3  2026-09-15  (open)
--   5  book 6  member 2  2026-09-18  (open)
-- Then count the rows of books, members and loans.

-- test: loans hat integer-ids und zwei date-Spalten
SELECT count(*) = 5 FROM information_schema.columns
WHERE table_name = 'loans'
  AND ((column_name IN ('id', 'book_id', 'member_id') AND data_type = 'integer')
    OR (column_name IN ('loaned_on', 'returned_on') AND data_type = 'date'));

-- test: Die Tabelle enthält fünf Ausleihen
SELECT count(*) = 5 FROM loans;

-- test: Die Ausleihen 2, 4 und 5 sind offen (returned_on ist NULL)
SELECT (SELECT count(*) FROM loans WHERE returned_on IS NULL AND id IN (2, 4, 5)) = 3
   AND (SELECT count(*) FROM loans WHERE returned_on IS NULL) = 3;

-- test: Ausleihe 4 ist Buch 1, am 2026-09-15 an Mitglied 3 verliehen
SELECT EXISTS (SELECT 1 FROM loans WHERE id = 4 AND book_id = 1 AND member_id = 3 AND loaned_on = '2026-09-15');

-- test: Das Skript gibt die drei Anzahlen aus: 6, 4 und 5
-- output:
--  books
-- -------
--      6
-- (1 row)
--
--  members
-- ---------
--        4
-- (1 row)
--
--  loans
-- -------
--      5
-- (1 row)

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');