GAMBAS BOOK 3.19.1

So that you can follow all the examples presented, you should create a database `test` with a DB table `test` for your DBMS SQLite3.

SQL commands and control commands

SQLite distinguishes between SQL commands and SQLite control commands. The control commands such as .databases or .quit always start with a dot. SQL commands do not have a dot as the first character, but are always ended with a semicolon (;) as in SELECT * FROM test; . Keywords such as SELECT are always capitalised in the chapter. This is not a rule, but increases readability for longer SQL statements such as

CREATE TABLE IF NOT EXISTS 
  tutors 
    (
      tutor_id    INTEGER PRIMARY KEY AUTOINCREMENT, 
      lastname    TEXT NOT NULL 
    )
;
.schema tutor

The last statement within the round brackets is not terminated with a comma!

Creating a database is quick and easy. As the system user, start the DB monitor sqlite3 in a console to establish a connection to the DBMS. If you specify the name of a database as an option, the database is connected to immediately or, if the database does not exist, it is created and connected to the new database immediately. You can freely assign the extension - the default 'sqlite' was used here:

hans@mint20:~$ sqlite3 test.sqlite
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite>

The .databases command displays an overview of all databases with the file paths:

sqlite> .databases
main: /home/hans/test.sqlite

The output of the .tables command is empty - which will not surprise you:

sqlite> .tables
sqlite>

The rest of the work begins with the fact that you, as the system user

• first create a new DB table `test`,
• then insert data into this table and
• then read data from the DB table from certain DB fields.

To create a table, use the following SQL statement:

CREATE TABLE 
  test 
    (
      id        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      vorname   TEXT NOT NULL, 
      nachname  TEXT NOT NULL, 
      wohnort   TEXT NOT NULL, 
      plz       TEXT, 
      strasse   TEXT, 
      gebdatum  DATE, -- Geburtstag. Das DB-Speicherformat ist:  yyyy-mm-dd.
      tfestnetz TEXT, 
      tmobil    TEXT, 
      email     TEXT
    )
;

This always gives you a description of the DB fields of a DB table:

sqlite> .schema test
CREATE TABLE test 
    (
      id        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      vorname   TEXT NOT NULL, 
      nachname  TEXT NOT NULL, 
      wohnort   TEXT NOT NULL, 
      plz       TEXT, 
      strasse   TEXT, 
      gebdatum  DATE, -- Geburtstag. Das DB-Speicherformat ist:  yyyy-mm-dd.
      tfestnetz TEXT, 
      tmobil    TEXT, 
      email     TEXT
    );
sqlite> 

Enter 45 data records in the DB table `test`. Select the complete SQL statement below, switch to the console with the DB monitor and enter the SQL statement with <CTRL>+<SHIFT>+V:

INSERT INTO test (vorname,nachname,wohnort,plz,strasse,gebdatum,tfestnetz,tmobil,email) 
  VALUES
    ('Arno','Adler','Arneburg','39606','Am Hafen 3','1981-12-08','03937864322','1715749482','arno.adler@arneburg.de'),
    ('Bruno','Bär','Berlin','10404','Bode-Strasse 1','1986-12-06','03094157777','1716771528','bruno.baer@freenet.de'),
    ('Gerda','Geier','Gera','07997','Gartenweg 23','1980-09-12','03657788989','1714472473','gerda.geier@gera.de'),
    ('Lutz','Lama','Leipzig','04103','Lessing-Allee 5','1989-02-25','0641432222','1717346836','lutz.lama@wwleipzig.de'),
    ('Maria','Meise','München','80805','Malergasse 10','1980-07-20','0867554324','1716821096','maria.meise@mawa.com'),
    ('Emil','Elch','Erfurt','99033','Eggert-Strasse 3c','1989-04-18','0361334455','1714287196','emil.elch@erfurt.de'),
    ('Detlef','de Hirsch','Dresden','01067','Deichweg 8','1984-01-28','0351876544','1716271745','detlef.dehirsch@dresden.de'),
    ('Hans-Helmut','Huhn','Hamburg','22111','Hafengasse 90','1988-01-26','04067554008','1716360418','hhhuhn@arcor.com'),
    ('Friedrich','Fledermaus','Frankfurt a.M.','60308','Flusenweg 12','1983-01-01','06101666664','1715209075','fledermaus74@web.de'),
    ('Norbert','Natter','Nürnberg','90402','Nord-Strasse 6a','1980-04-05','091155224324','1713545289','norbert.natter@web.de'),
    ('Clara','Chamäleon','Celle','29229','Claus-Kurt-Weg 1','1990-05-28','05141554678','1717398273','clara.camae@leon.de'),
    ('Ingelore','Igel','Immenstadt i.A.','87509','Imm-Reute 3','1988-12-10','08328552233','1715500891','ingelore.igel@web.de'),
    ('Stephanie','Storch','Stendal','39596','Strohgasse 55b','1989-05-10','039312232366','1716249744','stephanie.storch@reiseland.de'),
    ('Kurt','Kater','Köln','29229','Kanalstrasse 107','1985-05-11','0221767878789','1716968271','kurt.kater@koeln-nord.de'),
    ('Susanne','Sperling','Stuttgart','70173','Strasse der Einheit 3','1988-11-28','05141554678','1713955401','susanne.sperling@aol.com'),
    ('Doreen','Delphin','Dessau-Roßlau','06842','Drosselweg 2','1983-08-01','03455662271','1714171247','ddelphin@arcor.net'),
    ('Andreas','Ameise','Aachen','52064','An der Mauer 4','1982-04-19','02419854332','1715571201','andreas.ameise@aachen.de'),
    ('Fred','Fliege','Fürstenfeldbruck','82276','Feldrain 2','1985-06-23','08148642278','1717564483','fred.fliege@web.de'),
    ('Brunhilde','Bussard','Berlin','10405','Berg-Strasse 44c','1987-11-12','03066422783','1714613147','brunhilde.bussard@web.de'),
    ('Karl-Heinz','Kranich','Kleve','82276','Kurze Strasse 9','1985-02-11','028216666774','1715671343','khkranich@kranich.net'),
    ('Ziri','Zebra','Zittau','02763','Zur Post 33e','1983-06-08','035853376223','1716739494','ziri.zebra@zittauerland.de'),
    ('Paul','Pferd','Pinneberg','25421','Paradegasse 5','1990-03-01','041015555577','1714546720','paul.pferd@rennbahn.de'),
    ('Quirina','Qualle','Querfurt','06268','Quartz-Steg 33','1985-04-28','03477123456','1713455231','quirina.qualle@arcor.de'),
    ('Rüdiger','Rüsselkäfer','Rüdesheim','65385','Radweg 23','1980-06-03','067220042278','1714576104','r.kaefer@web.de'),
    ('Victor','Vogelspinne','Vogelsberg','99610','Vor dem Tore 4-5','1980-11-12','03637290887','1714737048','vvspinne@herbarium.de'),
    ('Jens-Peter','Jaguar','Jena','82276','Janusweg 66','1984-09-14','036410815','1716538095','jp.jaguar@juga.de'),
    ('Otto','Otter','Oldenburg i.H.','23779','Oberer Steg 2','1981-06-22','046313423888','1716342612','otto.otter@freenet.de'),
    ('Thomas','Taube','Taubenheim','02689','Tannwald-Strasse 4','1984-10-18','03593650441','1714320997','tom.taube@taubeev.de'),
    ('Ullrich','Uhu','Ulm','89073','Ulmenweg 7','1988-02-07','073181862258','1713517261','ullrich.uhu@ulm.de'),
    ('Walter','Wisent','Wittenberge','19322','Wiesenweg 50','1989-05-14','038771111258','1715563424','walter.wisent@web.de'),
    ('Dorothea','Dorsch','Düsedau','39606','Dammer Weg 123','1982-07-03','03937345222','1715128617','dodo@dorsch.com'),
    ('Ernst','Esel','Erfurt','99033','Eckstrasse 6','1989-01-18','0361337612','1715533977','ernst.esel@aol.com'),
    ('Hannelore','Haifisch','Hamburg','22131','Hochbauamt 2-7','1980-05-18','04012384566','1714506260','halohai@freenet.de'),
    ('Hans','Hundt','Hameln','31785','Hoher Weg 34','1984-10-16','0515176395588','1716869476','hans.hundt@haba.com'),
    ('Nancy','Nasenbär','Naumburg','06618','Neue Strasse 44a','1984-10-15','0344567888','1714332937','nancy.nb@zoo-nb.de'),
    ('Wolfgang','Wolf','Werra','06766','Waldweg 6a','1984-10-14','036922123','1716355311','wolf.wolter@werra-berg.de'),
    ('Erwin','Eule','Erfurt','99099','Erzberg 6','1988-11-11','0361234333','1718885311','eeule@freenet.de'),
    ('Bernd','Bisam','Berlin','10315','Bad-Ring 1c','1990-09-09','0304409999','1527035312','bernd@bisam-park.de'),
    ('Hans','Hase','Hassel','39695','Hain 12','1988-03-05','0393217752','1727775311','hassel.hase@web.de'),
    ('Stefan','Spatz','Stendal','39576','Seeweg 6a','1989-11-11','03931844123','1715454561','spatz89@aol.com'),
    ('Karl','Keiler','Kassel','34117','Kleiner Weg 3','1988-12-12','05618967944','1527022212','karl.k@arcor.de'),
    ('Xenia','Xanter','Xanten','46509','Xyloner See 2','1989-04-03','0280134556','1612345799','x.y.jolle@freenet.de'),
    ('Luise','Laus','Löbau','02708','Langer Kolken 1','1988-06-23','03587622022','1622323239','familia.l@genua.it'),
    ('Zeno','Zander','Zingst','18374','Ziegenhof 4c','1987-04-04','03823299744','1530345888','zeno.zack@web.de'),
    ('Volker','von Viper','Verden','27283','Volksbad 5c','1987-09-03','04231667293','1724522277','von.viper@web.de');

You can use the following SQL statement to output the data from the fields “first name”, “last name”, “postcode”, “city” and the calculated current age, for example - but only from locations whose postcode begins with a 3:

SELECT vorname AS "Vorname", nachname AS "Nachname", plz AS "PLZ", wohnort AS "Ort", 
(strftime('%Y','now') - strftime('%Y',gebdatum)) - (strftime('%m%d','now') < strftime('%m%d',gebdatum)) AS "Alter" 
FROM test 
WHERE plz LIKE '3%' ORDER BY nachname DESC;

sqlite> SELECT vorname AS "Vorname", nachname AS "Nachname", plz AS "PLZ", wohnort AS "Ort", 
   ...> (strftime('%Y','now') - strftime('%Y',gebdatum)) - (strftime('%m%d','now') < strftime('%m%d',gebdatum)) AS "Alter" 
   ...> FROM test WHERE plz LIKE '3%' ORDER BY nachname DESC;
 
Vorname     Nachname    PLZ         Ort         Alter     
----------  ----------  ----------  ----------  ----------
Stephanie   Storch      39596       Stendal     32        
Stefan      Spatz       39576       Stendal     31        
Karl        Keiler      34117       Kassel      32        
Hans        Hundt       31785       Hameln      36        
Hans        Hase        39695       Hassel      33        
Dorothea    Dorsch      39606       Düsedau     38        
Arno        Adler       39606       Arneburg    39            
sqlite> .quit
$