SQL

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. Cliente–Auto-Assicurazione 05.jpg

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;

Cliente–Auto-Assicurazione 06.jpg

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.

Database Customers-Cars-Insurances using SQL in Access

SQL is a standard language for storing, manipulating and retrieving data in databases. I want create a new database in two different way: in this article I use Microsoft Access, in another I explain how I program it in SQLlite.

To create a new SQL database, I would use the CREATE DATABASE statement, but creating a database in MS Access is done by creating a new file.
Either way, the clause would be:

CREATE DATABASE Customers-Cars-Insurances

To program in SQL in MS Access you have to Create a new Query. To do this, go to the Create menu and click on Query Structure.

Cliente–Auto-Assicurazione 01.jpg

Then click on SQL View at the top left of the window. A work environment will open.

Cliente–Auto-Assicurazione 02.jpg

Enter the SQL commands into the editor, as in the following figure, and click! Run to test and execute them.

Cliente–Auto-Assicurazione 03.jpg

Write 3 queries to create 3 tables and their constraints relationships: Customers (PK CodiceFiscale, ...), Cars (PK ChassisNumber, FK CodiceFiscale, ...) and Insurances (PK ChassisNumber, ...). Codice fiscale is an alphanumeric code of 16 characters and serves to unambiguously identify individuals residing in Italy. The chassis number is a unique code for each car.
Customers and Cars have a 1: N (one to many) relationship, Insurance and Cars have a 1: 1 (one to one) relationship.

CREATE TABLE Customers (
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 Customers (CodiceFiscale),
PRIMARY KEY (ChassisNumber)
);

Cliente–Auto-Assicurazione 04.jpg

Now you can populate the tables by entering data into Access or by using new SQL queries. In this last case You can see my article in SQLlite: https://www.filipposfactory.com/index.php/21-sql/40-database-customers-cars-insurances-using-sqlite

Database Customers-Cars-Insurances using SQLite

Database Customers-Cars-Insurances using SQL in Access

Advertising

Advertising

Advertising