Corso MariaDB / Esercizio: vendite mensili, acquisti dei clienti e prodotti invenduti

Vendite mensili, acquisti dei clienti e prodotti invenduti rappresentano unità diverse anche quando leggono gli stessi dati. Definire stato pagato, importi, periodo e significato di invenduto, confrontando i totali soltanto quando i dati inclusi hanno lo stesso ambito.

Definire misura e periodo di ogni rapporto

In questo esercizio le vendite sono quantity × unit_price al momento dell'acquisto per le righe di ordini pagati. Spedizioni e ordini annullati sono esclusi. È una prova di rapporti sulle merci, non una politica completa di contabilizzazione dei ricavi, rimborsi o imposte.

Un prodotto invenduto è un prodotto registrato che non è mai comparso in un ordine pagato. Il rapporto mensile si limita al 2026; le query su clienti e invenduti leggono invece tutta la storia disponibile. Nei dati iniziali tutti gli ordini pagati risalgono al 2026 e i totali sono quindi confrontabili. L'aggiunta di dati può eliminare questa coincidenza dei periodi.

Rapporto Significato di una riga Ambito
Vendite mensili Anno e mese Dettagli pagati nel 2026
Acquisti dei clienti Un cliente Tutta la storia pagata; zero per chi non ha acquistato
Prodotti invenduti Un prodotto Nessun dettaglio pagato in tutta la storia

La guida a GROUP BY e HAVING descrive l'unità dei gruppi; la guida alle duplicazioni nei JOIN aiuta a riconoscere importi ripetuti. Il riferimento MariaDB di GROUP BY e quello di EXISTS nelle sottoquery descrivono la sintassi usata.

Preparare dati piccoli da riconciliare a mano

Collegarsi al server di esercitazione come course_user e selezionare shopcourse. La guida al client e ai file SQL UTF-8 spiega salvataggio ed esecuzione con SOURCE C:/mariadb-lab/lesson.sql;. Sostituire il percorso con quello reale ed eseguire SQL nel client MariaDB.

Prima di creare c42_customers, c42_products, c42_orders e c42_items, controllare SELECT VERSION(), DATABASE(); e SHOW TABLES;. Se gli oggetti esistono già, esaminarne definizioni e righe. Usare soltanto oggetti di prova eliminabili e conservare le tabelle applicative. Sono fornite tutte le tabelle, evitando che esercizi precedenti cambino i risultati iniziali.

USE shopcourse;
CREATE TABLE c42_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c42_order_customer FOREIGN KEY (customer_id)
  REFERENCES c42_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c42_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c42_item_order FOREIGN KEY (order_id)
  REFERENCES c42_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c42_item_product FOREIGN KEY (product_id)
  REFERENCES c42_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c42_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c42_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c42_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c42_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Il codice conserva nomi e città coreani della fonte. I prodotti 1–4 sono quaderno, penna, tazza e lampada. I clienti 1 e 3 appartengono a Seoul, il cliente 2 a Busan e il cliente 4 a Daejeon. Salvare i testi in UTF-8 e usare gli ID nel confronto dei risultati.

Le merci pagate valgono 11500 nell'ordine 101, 8000 nel 102 e 19500 nel 103: totale 39000. L'ordine annullato 104, pari a 1500, è escluso. Riconciliare prima gli ordini individuali per evitare che un totale apparentemente plausibile nasconda un ambito sbagliato.

Raggruppare anno e mese e contare gli ordini corretti

SELECT DATE_FORMAT(o.ordered_at,'%Y-%m') AS month_label,
 COUNT(DISTINCT o.order_id) AS paid_orders,
 SUM(i.quantity*i.unit_price) AS goods_sales
FROM c42_orders o JOIN c42_items i ON i.order_id=o.order_id
WHERE o.status='paid' AND o.ordered_at>='2026-01-01' AND o.ordered_at<'2027-01-01'
GROUP BY DATE_FORMAT(o.ordered_at,'%Y-%m') ORDER BY month_label;
Mese Ordini pagati Importo delle merci
2026-01 2 19500.00
2026-02 1 19500.00

Il JOIN dei dettagli contiene cinque righe di merci pagate, ma soltanto tre ordini. La misura degli ordini usa quindi COUNT(DISTINCT order_id). COUNT(*) a gennaio conterebbe tre dettagli anziché due ordini.

Raggruppare soltanto per numero del mese può riunire lo stesso mese di anni diversi. La query non genera automaticamente righe zero per i mesi senza vendite: per mostrarli tutti occorre un insieme calendario definito. L'INNER JOIN limita inoltre il rapporto agli ordini che hanno dettagli.

Conservare i clienti senza acquisti nel rapporto

SELECT c.customer_id,c.customer_name,
 COALESCE(SUM(CASE WHEN o.status='paid' THEN i.quantity*i.unit_price ELSE 0 END),0) AS paid_goods
FROM c42_customers c LEFT JOIN c42_orders o ON o.customer_id=c.customer_id
LEFT JOIN c42_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name ORDER BY c.customer_id;

I totali dei clienti 1–4 sono 19500.00, 19500.00, 0.00 e 0.00. LEFT JOIN conserva i clienti e CASE include soltanto i dettagli pagati. Spostare paid nella WHERE può eliminare clienti senza ordini o con soli ordini annullati.

Questa query non ha limiti di data e comprende tutta la storia pagata. Per un rapporto clienti del 2026 definire gli stessi confini temporali mantenendo i clienti a zero. Non aggiungere un filtro della tabella destra alla WHERE senza controllarne l'effetto sul LEFT JOIN.

Non sommare shipping_fee direttamente attraverso le righe di dettaglio: il costo per ordine può ripetersi. Questo rapporto somma soltanto le merci. Importi al livello dell'ordine richiedono un calcolo separato o una preventiva aggregazione dei dettagli.

Trovare i prodotti senza alcuna vendita pagata

SELECT p.product_id,p.product_name FROM c42_products p
WHERE NOT EXISTS(SELECT 1 FROM c42_items i JOIN c42_orders o ON o.order_id=i.order_id
 WHERE i.product_id=p.product_id AND o.status='paid') ORDER BY p.product_id;

Nei dati iniziali soltanto il prodotto 4, la lampada, soddisfa la condizione. NOT EXISTS controlla l'assenza di un dettaglio collegato a un ordine pagato. Un prodotto presente soltanto in ordini annullati rimane invenduto secondo questa definizione. Considerare tutti gli stati richiede una condizione diversa.

L'assenza riguarda tutta la storia, non soltanto il 2026. Un prodotto venduto l'anno precedente è escluso anche se non è stato venduto quest'anno. Distinguere «mai venduto» da «non venduto nel periodo del rapporto» prima di scegliere il filtro.

Riconciliare totali, righe mancanti e confini temporali

Nei dati iniziali i due importi mensili sommano a 39000 e anche i quattro importi dei clienti sommano a 39000. Aggiungendo un ordine pagato del 2025 da 25000, il rapporto mensile 2026 rimane a 39000 mentre il totale clienti su tutta la storia arriva a 64000. La differenza è corretta per i diversi ambiti delle query e non dimostra un errore di somma.

Se rapporti con lo stesso ambito differiscono, confrontare chiavi, periodo, stato e definizione del prezzo unitario; poi cercare moltiplicazioni nei JOIN e filtri successivi a LEFT JOIN. Un totale complessivo plausibile non dimostra che ogni rapporto sia corretto.

I rapporti di produzione richiedono anche un momento di osservazione coerente, regole sui fusi orari e trattamento di annullamenti e rimborsi. I DATETIME fissi dell'esempio mantengono stabili i confini indipendentemente dalla data di esecuzione. Usare parametri per i periodi richiesti dall'applicazione anziché concatenare l'input nelle stringhe SQL.

Prima di ripetere, controllare database selezionato, SHOW CREATE TABLE e contenuto delle quattro tabelle, salvando ciò che va conservato. Eliminare soltanto gli oggetti c42_ di questo articolo, partendo dalle tabelle figlie. DROP TABLE elimina struttura e righe e non viene annullato da un normale ROLLBACK: separare la pulizia dalle prove sulle transazioni.

DROP TABLE shopcourse.c42_items;
DROP TABLE shopcourse.c42_orders;
DROP TABLE shopcourse.c42_products;
DROP TABLE shopcourse.c42_customers;