Tabellen anlegen: Datentypen, Namen und DROP TABLE

-- Every column is text. Give each one a type that fits.
CREATE TABLE books (
  id text,
  title text,
  price text,
  added text,
  on_loan text
);

INSERT INTO books VALUES (1, 'Dune', 12.5, '2026-09-01', true);
INSERT INTO books VALUES (2, 'Emma', 8, '2026-09-15', false);

SELECT * FROM books ORDER BY id;

-- test: id ist eine integer-Spalte
SELECT format_type(atttypid, atttypmod) = 'integer' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'id';

-- test: title ist varchar(80)
SELECT format_type(atttypid, atttypmod) = 'character varying(80)' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'title';

-- test: price ist numeric(6,2)
SELECT format_type(atttypid, atttypmod) = 'numeric(6,2)' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'price';

-- test: added ist eine date-Spalte
SELECT format_type(atttypid, atttypmod) = 'date' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'added';

-- test: on_loan ist eine boolean-Spalte
SELECT format_type(atttypid, atttypmod) = 'boolean' FROM pg_attribute WHERE attrelid = 'books'::regclass AND attname = 'on_loan';

-- test: Das Skript listet beide Bücher mit typisierten Werten auf
-- output:
--  id | title | price |   added    | on_loan
-- ----+-------+-------+------------+---------
--   1 | Dune  | 12.50 | 2026-09-01 | t
--   2 | Emma  |  8.00 | 2026-09-15 | f
-- (2 rows)

-- setup.sql has created an old members table (id, name) with Ada in it.
-- Replace it with the new table, then add the three members.

SELECT * FROM members ORDER BY id;

-- test: members hat die vier Spalten id, name, city und joined
SELECT count(*) = 4 FROM pg_attribute WHERE attrelid = 'members'::regclass AND attnum > 0 AND NOT attisdropped;

-- test: joined ist eine date-Spalte
SELECT format_type(atttypid, atttypmod) = 'date' FROM pg_attribute WHERE attrelid = 'members'::regclass AND attname = 'joined';

-- test: Die Tabelle enthält genau drei Mitglieder
SELECT count(*) = 3 FROM members;

-- test: Ada steht einmal in der Tabelle, jetzt mit ihrer Stadt
SELECT (SELECT count(*) FROM members WHERE name = 'Ada') = 1
   AND EXISTS (SELECT 1 FROM members WHERE id = 1 AND city = 'Berlin');

-- test: Das Skript listet die drei Mitglieder auf
-- output:
--  id | name  |  city  |   joined
-- ----+-------+--------+------------
--   1 | Ada   | Berlin | 2025-01-15
--   2 | Grace | Munich | 2025-03-02
--   3 | Linus | Berlin | 2026-02-10
-- (3 rows)

CREATE TABLE members (
  id integer,
  name text
);

INSERT INTO members VALUES (1, 'Ada');