Requêtes SQL de base

-- Sélectionner toutes les colonnes
SELECT * FROM Customers;

-- Sélectionner des colonnes spécifiques
SELECT CustomerName, Email, City 
FROM Customers;

-- Sélectionner avec alias
SELECT 
    CustomerName AS Nom,
    Email AS Courriel,
    City AS Ville
FROM Customers;

-- Obtenir les villes uniques
SELECT DISTINCT City 
FROM Customers;

-- Combinaisons uniques de plusieurs colonnes
SELECT DISTINCT City, Country 
FROM Customers;

-- Condition simple
SELECT * FROM Customers 
WHERE Country = 'Suisse';

-- Plusieurs conditions (AND)
SELECT * FROM Customers 
WHERE Country = 'Suisse' AND City = 'Lausanne';

-- Conditions alternatives (OR)
SELECT * FROM Customers 
WHERE City = 'Lausanne' OR City = 'Genève';

-- Comparaisons numériques
SELECT * FROM Products 
WHERE Price > 100 AND Price <= 500;

-- Vérifier NULL
SELECT * FROM Customers 
WHERE Phone IS NULL;

-- Vérifier NOT NULL
SELECT * FROM Customers 
WHERE Phone IS NOT NULL;

-- Commence par 'A'
SELECT * FROM Customers WHERE Name LIKE 'A%';

-- Se termine par 'son'
SELECT * FROM Customers WHERE Name LIKE '%son';

-- Contient 'tech'
SELECT * FROM Companies WHERE Name LIKE '%tech%';

-- Deuxième lettre est 'a'
SELECT * FROM Customers WHERE Name LIKE '_a%';

-- Exactement 5 caractères
SELECT * FROM Codes WHERE Code LIKE '_____';

-- Tri ascendant (par défaut)
SELECT * FROM Customers 
ORDER BY CustomerName;

-- Tri descendant
SELECT * FROM Products 
ORDER BY Price DESC;

-- Tri sur plusieurs colonnes
SELECT * FROM Customers 
ORDER BY Country ASC, City ASC, CustomerName ASC;

-- Tri avec alias
SELECT 
    FirstName + ' ' + LastName AS FullName,
    Salary
FROM Employees 
ORDER BY FullName;

-- SQL Server : TOP
SELECT TOP 10 * 
FROM Products 
ORDER BY Price DESC;

-- MySQL / PostgreSQL : LIMIT
SELECT * FROM Products 
ORDER BY Price DESC 
LIMIT 10;

-- Avec OFFSET (pagination)
SELECT * FROM Products 
ORDER BY ProductID 
OFFSET 20 ROWS 
FETCH NEXT 10 ROWS ONLY;

-- Compter le nombre de clients
SELECT COUNT(*) AS TotalCustomers 
FROM Customers;

-- Compter les valeurs non-NULL
SELECT COUNT(Phone) AS CustomersWithPhone 
FROM Customers;

-- Somme, moyenne, min, max
SELECT 
    COUNT(*) AS NbProduits,
    SUM(Price) AS Total,
    AVG(Price) AS PrixMoyen,
    MIN(Price) AS PrixMin,
    MAX(Price) AS PrixMax
FROM Products;

-- Compter les clients par pays
SELECT 
    Country,
    COUNT(*) AS NbClients
FROM Customers 
GROUP BY Country 
ORDER BY NbClients DESC;

-- Statistiques par catégorie
SELECT 
    CategoryID,
    COUNT(*) AS NbProduits,
    AVG(Price) AS PrixMoyen,
    MAX(Price) AS PrixMax
FROM Products 
GROUP BY CategoryID 
HAVING COUNT(*) > 5;

-- Pays avec plus de 5 clients
SELECT 
    Country,
    COUNT(*) AS NbClients
FROM Customers 
GROUP BY Country 
HAVING COUNT(*) > 5;

-- Catégories avec prix moyen > 100
SELECT 
    CategoryID,
    AVG(Price) AS PrixMoyen
FROM Products 
GROUP BY CategoryID 
HAVING AVG(Price) > 100;

-- Combinaison WHERE et HAVING
SELECT 
    Country,
    COUNT(*) AS NbClients
FROM Customers 
WHERE Active = 1  -- Filtre AVANT regroupement
GROUP BY Country 
HAVING COUNT(*) > 10;  -- Filtre APRÈS regroupement

-- Insérer une ligne
INSERT INTO Customers (CustomerName, Email, City, Country)
VALUES ('John Doe', 'john@example.com', 'Lausanne', 'Suisse');

-- Insérer plusieurs lignes
INSERT INTO Customers (CustomerName, Email, City, Country)
VALUES 
    ('Alice Smith', 'alice@example.com', 'Genève', 'Suisse'),
    ('Bob Martin', 'bob@example.com', 'Zurich', 'Suisse');

-- Insérer depuis une autre table
INSERT INTO CustomersArchive (CustomerName, Email, City)
SELECT CustomerName, Email, City 
FROM Customers 
WHERE LastOrderDate < '2020-01-01';

-- Modifier une ligne spécifique
UPDATE Customers 
SET Email = 'newemail@example.com', 
    City = 'Berne'
WHERE CustomerID = 123;

-- Modifier plusieurs lignes
UPDATE Products 
SET Price = Price * 1.10  -- Augmentation de 10%
WHERE CategoryID = 5;

-- Modifier avec calcul
UPDATE Employees 
SET Salary = Salary * 1.05
WHERE Department = 'IT' AND Salary < 80000;

-- Supprimer une ligne spécifique
DELETE FROM Customers 
WHERE CustomerID = 123;

-- Supprimer plusieurs lignes
DELETE FROM Orders 
WHERE OrderDate < '2020-01-01';

-- Supprimer toutes les lignes (dangereux!)
DELETE FROM TempTable;

-- Alternative plus rapide pour vider une table
TRUNCATE TABLE TempTable;