Customers, orders and instalments¶

SQL exercises · Part I

Problem¶

Three relations given with their data: persons, their orders, and the instalments issued for each order (amount, due date, payment date, which is null when the instalment is still unpaid). Every order belongs to a person and every instalment to an order. Write six queries.

Schema and test data¶

schema.sqltest_data.sql

-- The exercise shows the three relations with their data (see test_data.sql).
CREATE TABLE persone (
    CF      TEXT PRIMARY KEY,
    cognome TEXT,
    nome    TEXT
);
CREATE TABLE ordini (
    codice      INTEGER PRIMARY KEY,
    cliente     TEXT REFERENCES persone(CF),
    descrizione TEXT
);
CREATE TABLE rateemesse (
    numero        INTEGER PRIMARY KEY,
    ordine        INTEGER REFERENCES ordini(codice),
    data          TEXT,
    importo       INTEGER,
    datapagamento TEXT
);

-- The data shown in the exercise. The third order appears as "03" in the text,
-- most likely 103 with a digit lost; the descriptions are elided there ("...").
INSERT INTO persone VALUES ('RSSMRA', 'Rossi', 'Mario'), ('BNCLGU', 'Bianco', 'Luigi'),
                           ('VRDLCU', 'Verdi', 'Luca'),  ('MROMRA', 'Mori', 'Mario');
INSERT INTO ordini VALUES (101, 'RSSMRA', '...'), (102, 'RSSMRA', '...'), (103, 'BNCLGU', '...');
INSERT INTO rateemesse VALUES
    (20010, 101, '2018-01-15', 250, '2018-02-10'),
    (20011, 101, '2018-04-15', 100, '2018-05-20'),
    (20012, 101, '2018-07-15', 120, NULL),
    (20021, 102, '2018-04-15', 130, '2018-05-02'),
    (20022, 102, '2018-07-15', 120, '2018-08-03');

Here the test data are the ones shown in the exercise.

Queries¶

Query 1 · Persons with no orders¶

Tax code, name and surname of the persons who have no order, ordered by tax code.

SELECT C1.CF, C1.cognome, C1.nome

FROM persone AS C1
LEFT JOIN ordini AS C2
ON C1.CF = C2.cliente

WHERE C2.codice IS NULL
ORDER BY C1.CF

CF	cognome	nome
MROMRA	Mori	Mario
VRDLCU	Verdi	Luca

Query 2 · Number of orders per person¶

For each person, the number of orders, showing 0 for those who have none. Two attributes: tax code and number of orders, ordered by tax code.

SELECT C1.CF, COUNT(C2.cliente) AS numeroOrdini

FROM persone AS C1
LEFT JOIN ordini AS C2
ON C1.CF = C2.cliente

GROUP BY C1.CF

ORDER BY C1.CF

CF	numeroOrdini
BNCLGU	1
MROMRA	0
RSSMRA	2
VRDLCU	0

Query 3 · Total of the instalments of each order¶

For each order with instalments, the total amount of its instalments. Two attributes, order and total, with increasing values of order.

SELECT C1.ordine, SUM(C1.importo) AS importoTotale

FROM rateemesse AS C1
JOIN ordini AS C2
ON C1.ordine = C2.codice

GROUP BY C1.ordine

ordine	importoTotale
101	470
102	250

Query 4 · Total, paid and debt of each order¶

For each order with instalments: the total issued, the total paid, and the debt (the difference). Assume every such order has at least one paid instalment. Four attributes, ordered by order.

CREATE VIEW importo_Totale AS
SELECT C1.ordine, SUM(C1.importo) AS importoTotale
FROM rateemesse AS C1
JOIN ordini AS C2
ON C1.ordine = C2.codice
GROUP BY C1.ordine;

CREATE VIEW importo_Totale_Pagato AS
SELECT C1.ordine, SUM(C1.importo) AS importoTotalePagato
FROM rateemesse AS C1
LEFT JOIN ordini AS C2
ON C1.ordine = C2.codice
WHERE datapagamento IS NOT NULL
GROUP BY C1.ordine;

SELECT importo_Totale.ordine,
importo_Totale.importoTotale,
importo_Totale_Pagato.importoTotalePagato,
importo_Totale.importoTotale - importo_Totale_Pagato.importoTotalePagato AS debito
FROM importo_Totale
JOIN importo_Totale_Pagato
ON importo_Totale.ordine = importo_Totale_Pagato.ordine

ordine	importoTotale	importoTotalePagato	debito
101	470	350	120
102	250	250	0

Query 5 · The order with the largest debt¶

Code, customer, description and debt of the order with the largest debt computed in Query 4.

CREATE VIEW importo_Totale AS
SELECT C1.ordine, SUM(C1.importo) AS importoTotale
FROM rateemesse AS C1
JOIN ordini AS C2
ON C1.ordine = C2.codice
GROUP BY C1.ordine;

CREATE VIEW importo_Totale_Pagato AS
SELECT C1.ordine, SUM(C1.importo) AS importoTotalePagato
FROM rateemesse AS C1
LEFT JOIN ordini AS C2
ON C1.ordine = C2.codice
WHERE datapagamento IS NOT NULL
GROUP BY C1.ordine;

CREATE VIEW debiti AS
SELECT importo_Totale.ordine,
importo_Totale.importoTotale,
importo_Totale_Pagato.importoTotalePagato,
importo_Totale.importoTotale - importo_Totale_Pagato.importoTotalePagato AS debito
FROM importo_Totale
JOIN importo_Totale_Pagato
ON importo_Totale.ordine = importo_Totale_Pagato.ordine;

SELECT ordini.codice, ordini.cliente, ordini.descrizione, debiti.debito
FROM debiti
JOIN  ordini
ON debiti.ordine = ordini.codice
WHERE debito = (SELECT MAX(debito) FROM debiti)

codice	cliente	descrizione	debito
101	RSSMRA	...	120

Query 6 · Persons with at least one order¶

Tax code, name and surname of the persons with at least one order, ordered by tax code.

SELECT C1.CF, C1.cognome, C1.nome
FROM persone AS C1
LEFT JOIN ordini AS C2
ON C1.CF = C2.cliente

GROUP BY C1.CF, C1.cognome, C1.nome
HAVING (COUNT(C2.codice) > 0)

CF	cognome	nome
BNCLGU	Bianco	Luigi
RSSMRA	Rossi	Mario

Review notes

All six queries return the right rows. Three of them miss the ordering the text asks for: Query 3 and Query 4 (ORDER BY C1.ordine) and Query 6 (ORDER BY C1.CF). On SQLite the result comes out sorted anyway, as a side effect of how GROUP BY is computed, but SQL does not guarantee any order without ORDER BY.

Two simplifications are possible: the LEFT JOIN ordini in the view of paid amounts adds nothing (only columns of rateemesse are used), and Query 6 is simply an inner join with DISTINCT, without GROUP BY and HAVING.

Takeaways¶

A null payment date marks an unpaid instalment: WHERE datapagamento IS NOT NULL before summing gives the amount paid.
Building the debt in steps (total, paid, difference) with views keeps each query small and checkable on its own.
Without ORDER BY there is no guaranteed order, whatever the result looks like.