Database Customers-Cars-Insurances using SQLite

This is the second part about programming a database Customers-Cars-Insurances. In the first part I create a DB in MS Access: https://www.filipposfactory.com/index.php/21-sql/39-database-customer-car-insurance-using-sql. In this article I program it in SQLite, using some of the many websites that offer this possibility.

The first possibility is to use W3schools.com site in the Learn SQL section. Here it is not possible to create a database but you can add tables to the one already created on site. Either way, the clause would be:

CREATE DATABASE Customers-Cars-Insurances

To create tables and populate them with data within this site you need to use Chrome, Safari, Opera as a web browser. It is not possible to use Firefox because it cannot act on the database with the CREATE or UPDATE statement but only solves queries with SELECT. The tables that you create remain active as long as your browser session on the W3schools site remains open.

To program in SQLlite I open a W3schools SQL test window by clicking on any of the "Try it Yourself" buttons that I find on the site and send one SQL command at a time. I cannot create a table with Customers name because it already exists in database, so I use CarCustomers:

CREATE TABLE CarsCustomers (
   CodiceFiscale CHAR (16) NOT NULL,
  Lastname CHAR (20),
  Name CHAR (20),
  BirthdayDate DATE,
  Address CHAR (80),
  PRIMARY KEY (CodiceFiscale)
); 

CREATE TABLE Cars (
   ChassisNumber CHAR (20) NOT NULL,
   Brand CHAR (20),
   Model CHAR (20),
   RegistrationDate DATE,
   CodiceFiscale CHAR (16) CONSTRAINT CustomerCar REFERENCES CarCustomers (CodiceFiscale),
   PRIMARY KEY (ChassisNumber)
);

CREATE TABLE Insurances (
   ChassisNumber CHAR (20) NOT NULL UNIQUE CONSTRAINT CarsInsurances REFERENCES Cars (ChassisNumber),
   InsurancesDate DATE,
   InsurancesNumber INT,
   AnnualCost MONEY,
   PRIMARY KEY (ChassisNumber)
);

 Here there are some data entry queries in the CarsCustomers table (you must enter them one at a time):

INSERT INTO CarsCustomers VALUES("RSSMRA80A01F205X","Rossi","Mario", "01-01-1980", "Via dei Girasoli 20, Torino");

INSERT INTO CarsCustomers VALUES ("BNCRRT80A47L219L","Bianchi","Roberta", "07-01-1980", "Via Roma 100, Torino");

INSERT INTO CarsCustomers VALUES ("VRDPLA70R07I726H","Verdi","Paolo", "07-10-1970", "Via dei Ciclamini 128, Siena");

If the data entry was successful, I will see the three databases with their indexes on the side column and by SELECT statement on CarCustomers, I will have the result as shown in the following figure:

 SELECT * FROM CarsCustomers; 

Some data entry queries in the Cars table:

INSERT INTO Cars VALUES ("FIAT50034567890ABCDF","FIAT", "500","31-01-2020", "RSSMRA80A01F205X");

INSERT INTO Cars VALUES ("FIAT01234567890PUNTO","FIAT", "Grande Punto","31-01-2018", "RSSMRA80A01F205X");

INSERT INTO Cars VALUES ("FORD500345678ESCORT1","Ford", "Escort","22-07-2018", "VRDPLA70R07I726H");

INSERT INTO Cars VALUES ("OPEL111345678DICORSA","Opel", "Corsa","30-08-2019", "BNCRRT80A47L219L");

An update query in the Cars table:

UPDATE Cars SET RegistrationDate = "23-08-2018" WHERE ChassisNumber="FORD500345678ESCORT1";

Some data entry queries in the Insurances table:

INSERT INTO Insurances VALUES ("FIAT50034567890ABCDF","31-01-2020", 303021, 1010.99);

INSERT INTO Insurances VALUES ("FIAT01234567890PUNTO","31-12-2019", 303022, 1310.00);

INSERT INTO Insurances VALUES ("FORD500345678ESCORT1","15-02-2019", 303023, 801.50);

INSERT INTO Insurances VALUES ("OPEL111345678DICORSA","30-08-2020", 303024, 320.80);

Another possibility to program SQLite is for example the site ideone.com. You can program the database and share it with others, like in this link: https://ideone.com/2Jgx0u.