Funktionen für Text, Zahlen, Datum und NULL

SELECT upper('Hallo') AS gross, lower('Hallo') AS klein, length('Hallo') AS laenge,
       substr('Datenbank', 1, 5) AS teil, replace('a-b-c', '-', '+') AS ersetzt,
       trim('  x  ') AS getrimmt, 'Mia' || ' ' || 'Müller' AS verkettet,
       instr('Datenbank', 'ban') AS position;

SELECT abs(-5) AS betrag, round(3.14159, 2) AS gerundet, round(2.5) AS r,
       max(3, 9, 5) AS groesstes, min(3, 9, 5) AS kleinstes,
       7 / 2 AS ganzzahl, 7 / 2.0 AS komma, 7 % 3 AS rest, 2 * 3 + 4 AS rechnung;

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT name, punkte,
       CASE
           WHEN punkte IS NULL THEN 'unbekannt'
           WHEN punkte >= 150 THEN 'Gold'
           WHEN punkte >= 100 THEN 'Silber'
           ELSE 'Bronze'
       END AS stufe
FROM kunden
ORDER BY punkte DESC;

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT name, punkte, COALESCE(punkte, 0) AS ohne_null, IFNULL(ort, '?') AS ort,
       NULLIF(ort, 'Berlin') AS nicht_berlin
FROM kunden;

SELECT date('2025-03-15', '+1 month') AS naechster_monat,
       date('2025-03-15', '-10 days') AS vor_10_tagen,
       strftime('%Y', '2025-03-15') AS jahr,
       strftime('%m', '2025-03-15') AS monat,
       strftime('%w', '2025-03-15') AS wochentag_0_so,
       julianday('2025-12-24') - julianday('2025-03-15') AS tage_bis_weihnachten,
       date('2025-01-31', 'start of month', '+1 month', '-1 day') AS monatsende;

SELECT CAST('42' AS INTEGER) + 1 AS a, CAST(3.99 AS INTEGER) AS b,
       CAST(42 AS TEXT) || ' Stück' AS c, typeof(CAST('7' AS REAL)) AS d;