· 7 years ago · Jan 20, 2019, 05:10 PM
1CREATE DATABASE IF NOT EXISTS Multa;
2
3CREATE TABLE Agenti(
4 matricola VARCHAR(10) NOT NULL,
5 nome VARCHAR(10) NOT NULL,
6 cognome VARCHAR(10) NOT NULL,
7 CONSTRAINT ChiavePrimaria PRIMARY KEY(matricola)
8);
9
10CREATE TABLE Automobilisti(
11 cod_fiscale VARCHAR(10) NOT NULL,
12 nome VARCHAR(10) NOT NULL,
13 cognome VARCHAR(10) NOT NULL,
14 indirizzo VARCHAR(10) NOT NULL,
15 citta VARCHAR(10) NOT NULL,
16 CAP VARCHAR(10) NOT NULL,
17CONSTRAINT ChiavePrimaria PRIMARY KEY (cod_fiscale)
18);
19
20
21CREATE TABLE Auto(
22 targa VARCHAR(10) NOT NULL,
23 marca VARCHAR(10) NOT NULL,
24 modello VARCHAR(10) NOT NULL,
25 cod_fiscale VARCHAR(10) NOT NULL,
26CONSTRAINT ChiavePrimaria PRIMARY KEY(targa),
27CONSTRAINT AutomobilistiAuto FOREIGN KEY(cod_fiscale)
28 REFERENCES Automobilisti (cod_fiscale)
29);
30
31CREATE TABLE Infrazioni(
32 id_infrazioni VARCHAR(10) NOT NULL,
33 targa VARCHAR(10) NOT NULL,
34 matricola VARCHAR(10) NOT NULL,
35 data DATE NOT NULL,
36 tipo_infrazioni VARCHAR(10) NOT NULL,
37 importo VARCHAR(10) NOT NULL,
38CONSTRAINT ChiavePrimaria PRIMARY KEY(id_infrazioni),
39CONSTRAINT AgentiInfrazioni FOREIGN KEY(matricola)
40 REFERENCES Agenti(matricola),
41 CONSTRAINT AgentiAuto FOREIGN KEY(targa)
42 REFERENCES Auto(targa)
43);
44
45***************************QUERY***********************************
46SELECT *
47From Infrazioni
48WHERE data > '2000-01-01'
49------------------------------
50SELECT agenti.*
51From agenti,infrazioni
52WHERE infrazioni.matricola=agenti.matricola
53------------------------------
54SELECT automobilisti.*,auto.*
55From automobilisti,infrazioni,auto
56WHERE infrazioni.targa=auto.targa AND auto.cod_fiscale=automobilisti.cod_fiscale
57------------------------------
58SELECT infrazioni.data,agenti.nome,infrazioni.targa,auto.modello,auto.marca
59FROM infrazioni,agenti,auto
60WHERE infrazioni.matricola=agenti.matricola AND infrazioni.targa=auto.targa