Tabellen und Datentypen

CREATE TABLE produkte (
    id      INTEGER PRIMARY KEY,
    name    TEXT    NOT NULL,
    preis   REAL    NOT NULL CHECK (preis >= 0),
    lager   INTEGER DEFAULT 0,
    aktiv   INTEGER DEFAULT 1
);

INSERT INTO produkte (name, preis) VALUES ('Stift', 1.5);
SELECT * FROM produkte;

SELECT typeof(42) AS a, typeof(3.14) AS b, typeof('x') AS c, typeof(NULL) AS d, typeof(x'ff') AS e;
SELECT 0.1 + 0.2 AS summe, 0.1 + 0.2 = 0.3 AS gleich;

CREATE TABLE konto (
    iban  TEXT PRIMARY KEY,
    inhaber TEXT NOT NULL,
    stand REAL NOT NULL DEFAULT 0 CHECK (stand >= -500),
    email TEXT UNIQUE
);
INSERT INTO konto (iban, inhaber, email) VALUES ('DE01', 'Mia', 'mia@x.de');
SELECT * FROM konto;

CREATE TABLE konto (iban TEXT PRIMARY KEY, stand REAL NOT NULL CHECK (stand >= 0));
INSERT INTO konto VALUES ('DE01', 10);
INSERT INTO konto VALUES ('DE01', 20);

CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT);
ALTER TABLE t ADD COLUMN notiz TEXT;
ALTER TABLE t RENAME COLUMN notiz TO bemerkung;
ALTER TABLE t RENAME TO tabelle;
INSERT INTO tabelle (name) VALUES ('x');
SELECT * FROM tabelle;
DROP TABLE tabelle;
DROP TABLE IF EXISTS gibt_es_nicht;
SELECT count(*) AS tabellen FROM sqlite_master WHERE type = 'table';

CREATE TABLE gaeste (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO gaeste (name) VALUES ('A'), ('B'), ('C');
SELECT * FROM gaeste;
SELECT last_insert_rowid() AS letzte_id;