Comandi SQL utili. Molto utili :-)

Esempio con Clienti e Fornitori

-- riepilogo della sintassi più importante in SQL

-- SQL inizia SEMPRE con ...
CREATE DATABASE nome_database; 

CREATE TABLE Customers (
id INT AUTO_INCREMENT PRIMARY KEY,
CustomerName VARCHAR(60) NOT NULL,
ContactName VARCHAR(70) NOT NULL,
Address VARCHAR(100) NOT NULL,
City VARCHAR(45) NOT NULL,
PostalCode VARCHAR(30) NOT NULL,
Country VARCHAR(30) NOT NULL
) 

-- inserire un dato nella tabella creata
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

-- seleziona nome cliente, città dalla tabella Customers
SELECT CustomerName, City 
FROM Customers;

-- lista/elenco dei paesi presenti nella tabella customers (CON DUPLICATI, visto che Country NON è chiave primaria)
SELECT Country 
FROM Customers;

-- lista/elenco dei paesi presenti nella tabella customers (SENZA DUPLICATI, visto che Country NON è chiave primaria)
SELECT DISTINCT Country 
FROM Customers;

-- Selezionare soltanto i clienti italiani, andiamo a filtrare sul campo Country
SELECT * FROM Customers
WHERE Country="Italy";

-- Seleziona i clienti della città di Berlin in "via 123" (filtro con condizione logica AND)
SELECT * FROM Customers
WHERE City="Berlin" and Address="via 123";

-- seleziona tutti i clienti ordinati per paese di appartenenza
SELECT * 
FROM Customers
ORDER BY Country;

-- seleziona il prezzo più basso dei prodotti
SELECT MIN(Price) AS Prezzo_Minimo
FROM Products;

-- seleziona il prezzo più alto dei prodotti
SELECT MAX(Price) AS Prezzo_Massimo
FROM Products; 

-- indicare il numero di prodotti
SELECT COUNT(ProductID) AS Numero_Prodotti
FROM Products;

-- clienti il cui nome inizia per "B"
SELECT * FROM Customers
WHERE CustomerName LIKE "B%"; 

-- clienti dello stesso paese dei fornitori
SELECT * 
FROM Customers
WHERE Country IN (
SELECT Country 
FROM Suppliers
);

-- JOIN: lista "combinata" dei clienti con dei rispettivi ordini
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders, Customers 
WHERE Orders.CustomerID=Customers.CustomerID;

-- raggruppamento: numero di clienti e paese corrispondente, raggruppato per singolo paese
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

-- raggruppamento base: numero di clienti e paese corrispondente, raggruppato per singolo paese
-- limitato alle righe con almeno 5 clienti
-- attenzione: qui è SBAGLIATO usare WHERE al posto di HAVING!

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) >= 5;

-- come creare una primary key, opzione 1
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
); 

-- oppure, se non lo mettiamo prima, opzione 2
ALTER TABLE Persons ADD PRIMARY KEY (ID);           -- PK singola 

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); -- PK doppia

-- foreign key, chiave esterna
-- qui sto assumendo di avere una tabella Ordini (Orders) e di:
--- 1) creare un vincolo nomeChiave (nome puramente formale, richiesto dalla sintassi)
--- 2) creare una chiave esterna X, che può valere sintatticamente uno qualsiasi dei campi della tabella
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT nomeChiave 
        FOREIGN KEY (nomeCampoTabellaAttuale) 
            REFERENCES
                nomeTabellaEsterna(nomeCampoTabellaEsterna) 
                ON DELETE CASCADE ON UPDATE CASCADE
); 

-- oppure, se non lo mettiamo prima:
ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

-- da qui in poi, esempi più avanzati
-- imparare BENE tutto quello che 
-- c'è in precedenza, prima di addentrarsi qui
-- ... BENE :-)

-- AEROPORTO (Città, Nazione, NumPiste)
-- VOLO (IdVolo, GiornoSett, CittàPart, OraPart,CittàArr, OraArr, TipoAereo)
-- AEREO (TipoAereo, NumPasseggeri, QtaMerci)

-- città da cui partono voli diretti a Roma, ordinate alfabeticamente

SELECT DISTINCT CittàPar
FROM Volo
WHERE CittàArr= "Roma"
ORDER BY CittàPar

-- Trovare le città con un aeroporto di cui non è noto il numero di piste
SELECT Città
FROM Aeroporto
WHERE NumPiste IS NULL

--  Di ogni volo misto (merci e passeggeri) estrarre il codice e i dati relativi al trasporto

SELECT IdVolo, NumPasseggeri, QtaMerci
FROM VOLO AS V, AEREO AS A
WHERE V.TipoAereo = A.TipoAereo AND NumPasseggeri > 0 and QtaMerci > 0

-- Aereo col massimo numero di passeggeri (query scalare)

SELECT TipoAereo, MAX( NumPasseggeri )
FROM AEREO
[LIMIT 1]

-- Aeroporti col maggior numero di piste - NON scalare, caso più complicato!

SELECT Città, NumPiste
FROM AEROPORTO
WHERE Nazione=‘Italia’ and
NumPiste = ( SELECT max(numPiste) FROM AEROPORTO WHERE Nazione=‘Italia’ );

Schema generale query.sql

-- (A) = query semplici
-- (B) = query con raggruppamento / aggregazione + (A)
-- (C) = query con join + (A) 
-- (D) = (B) + (C) + (A)
-- (E) = (B) + (C) + (A) + innesto query

-- CREATE / ALTER (ADD COLUMN) / DROP 
-- (A): SELECT, DISTINCT, FROM, WHERE
-- (B): COUNT, MIN, MAX, AVG, ORDER BY, GROUP BY
-- (C): JOIN
-- Altri: HAVING (SOLO per COUNT, MIN, MAX, AVG), IN, NOT IN, BETWEEN, LIKE, LIMIT