La base de données Northwind⚓︎

Questions non classées

Cet exercice contient des questions de niveaux variés. Certaines sont (très) simples, d'autres sont (très) compliquées !

Elles ne sont pas classées par ordre de difficulté.

La base de données Northwind contient des informations décrivant un magasin par correspondances (employés, clients, produits, commandes, fournisseurs...).

Le fichier northwind_creation.sql contient les requêtes saisies pour créer la base de données. On y trouvera donc les noms des tables ainsi que leurs attributs.

northwind_creation.sql

CREATE TABLE IF NOT EXISTS "Categories" (
    "CategoryID"    INTEGER,
    "CategoryName"  TEXT,
    "Description"   TEXT,
    "Picture"   BLOB,
    PRIMARY KEY("CategoryID")
);
CREATE TABLE IF NOT EXISTS "Customers" (
    "CustomerID"    TEXT,
    "CompanyName"   TEXT,
    "ContactName"   TEXT,
    "ContactTitle"  TEXT,
    "Address"   TEXT,
    "City"  TEXT,
    "Region"    TEXT,
    "PostalCode"    TEXT,
    "Country"   TEXT,
    "Phone" TEXT,
    "Fax"   TEXT,
    PRIMARY KEY("CustomerID")
);
CREATE TABLE IF NOT EXISTS "EmployeeTerritories" (
    "EmployeeID"    INTEGER NOT NULL,
    "TerritoryID"   TEXT NOT NULL,
    PRIMARY KEY("EmployeeID","TerritoryID"),
    FOREIGN KEY("EmployeeID") REFERENCES "Employees"("EmployeeID") ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY("TerritoryID") REFERENCES "Territories"("TerritoryID") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "Employees" (
    "EmployeeID"    INTEGER,
    "LastName"  TEXT,
    "FirstName" TEXT,
    "Title" TEXT,
    "TitleOfCourtesy"   TEXT,
    "BirthDate" DATE,
    "HireDate"  DATE,
    "Address"   TEXT,
    "City"  TEXT,
    "Region"    TEXT,
    "PostalCode"    TEXT,
    "Country"   TEXT,
    "HomePhone" TEXT,
    "Extension" TEXT,
    "Photo" BLOB,
    "Notes" TEXT,
    "ReportsTo" INTEGER,
    "PhotoPath" TEXT,
    PRIMARY KEY("EmployeeID"),
    FOREIGN KEY("ReportsTo") REFERENCES "Employees"("EmployeeID") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "OrderDetails" (
    "OrderID"   INTEGER NOT NULL,
    "ProductID" INTEGER NOT NULL,
    "UnitPrice" NUMERIC NOT NULL DEFAULT 0,
    "Quantity"  INTEGER NOT NULL DEFAULT 1,
    "Discount"  REAL NOT NULL DEFAULT 0,
    PRIMARY KEY("OrderID","ProductID"),
    FOREIGN KEY("OrderID") REFERENCES "Orders"("OrderID") ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY("ProductID") REFERENCES "Products"("ProductID") ON DELETE CASCADE ON UPDATE CASCADE,
    CHECK("Discount" >= (0) AND "Discount" <= (1)),
    CHECK("Quantity" > (0)),
    CHECK("UnitPrice" >= (0))
);
CREATE TABLE IF NOT EXISTS "Orders" (
    "OrderID"   INTEGER NOT NULL,
    "CustomerID"    TEXT,
    "EmployeeID"    INTEGER,
    "OrderDate" DATETIME,
    "RequiredDate"  DATETIME,
    "ShippedDate"   DATETIME,
    "ShipVia"   INTEGER,
    "Freight"   NUMERIC DEFAULT 0,
    "ShipName"  TEXT,
    "ShipAddress"   TEXT,
    "ShipCity"  TEXT,
    "ShipRegion"    TEXT,
    "ShipPostalCode"    TEXT,
    "ShipCountry"   TEXT,
    PRIMARY KEY("OrderID"),
    FOREIGN KEY("CustomerID") REFERENCES "Customers"("CustomerID") ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY("EmployeeID") REFERENCES "Employees"("EmployeeID") ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY("ShipVia") REFERENCES "Shippers"("ShipperID") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS "Products" (
    "ProductID" INTEGER NOT NULL,
    "ProductName"   TEXT NOT NULL,
    "SupplierID"    INTEGER,
    "CategoryID"    INTEGER,
    "QuantityPerUnit"   TEXT,
    "UnitPrice" NUMERIC DEFAULT 0,
    "UnitsInStock"  INTEGER DEFAULT 0,
    "UnitsOnOrder"  INTEGER DEFAULT 0,
    "ReorderLevel"  INTEGER DEFAULT 0,
    "Discontinued"  TEXT NOT NULL DEFAULT '0',
    PRIMARY KEY("ProductID"),
    FOREIGN KEY("CategoryID") REFERENCES "Categories"("CategoryID") ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY("SupplierID") REFERENCES "Suppliers"("SupplierID") ON DELETE CASCADE ON UPDATE CASCADE,
    CHECK("UnitPrice" >= (0)),
    CHECK("ReorderLevel" >= (0)),
    CHECK("UnitsInStock" >= (0)),
    CHECK("UnitsOnOrder" >= (0))
);
CREATE TABLE IF NOT EXISTS "Regions" (
    "RegionID"  INTEGER NOT NULL,
    "RegionDescription" TEXT NOT NULL,
    PRIMARY KEY("RegionID")
);
CREATE TABLE IF NOT EXISTS "Shippers" (
    "ShipperID" INTEGER NOT NULL,
    "CompanyName"   TEXT NOT NULL,
    "Phone" TEXT,
    PRIMARY KEY("ShipperID")
);
CREATE TABLE IF NOT EXISTS "Suppliers" (
    "SupplierID"    INTEGER NOT NULL,
    "CompanyName"   TEXT NOT NULL,
    "ContactName"   TEXT,
    "ContactTitle"  TEXT,
    "Address"   TEXT,
    "City"  TEXT,
    "Region"    TEXT,
    "PostalCode"    TEXT,
    "Country"   TEXT,
    "Phone" TEXT,
    "Fax"   TEXT,
    "HomePage"  TEXT,
    PRIMARY KEY("SupplierID")
);
CREATE TABLE IF NOT EXISTS "Territories" (
    "TerritoryID"   TEXT NOT NULL,
    "TerritoryDescription"  TEXT NOT NULL,
    "RegionID"  INTEGER NOT NULL,
    PRIMARY KEY("TerritoryID"),
    FOREIGN KEY("RegionID") REFERENCES "Regions"("RegionID") ON DELETE CASCADE ON UPDATE CASCADE
);

Une représentation visuelle de la base est fournie ci-dessous :

Cette base est téléchargeable au format sqlite ici. La source est sur ce site.

Combien de clients sont enregistrés dans la base ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT COUNT(*)
FROM Customers;
```
Quel est le nom du contact du client « France restauration » ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT ContactName
FROM Customers
WHERE CompanyName = 'France restauration'        
```
Quel est le prix unitaire du produit « Longlife Tofu » ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT UnitPrice
FROM Products
WHERE ProductName = 'Longlife Tofu'
```
Quelle entreprise fournit la « Tarte au sucre » ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT Suppliers.CompanyName
FROM Suppliers
JOIN Products ON Products.SupplierID = Suppliers.SupplierID
WHERE Products.ProductName = 'Tarte au sucre'
```
Le magasin vend des fromages (dont l'identifiant de catégorie est le 4). Combien de produits de ce type sont référencés ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT COUNT(*)
FROM Products
WHERE Products.CategoryID = 4
```
Combien de produits de chaque catégorie sont référencés ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT Products.CategoryID, Categories.CategoryName, COUNT(*)
FROM Products
JOIN Categories ON Categories.CategoryID = Products.CategoryID
GROUP BY Products.CategoryID
```
Combien de clients habitent en France ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT COUNT(*)
FROM Customers
WHERE Country = 'France'
```
Combien de clients habitent en Amérique (nord, centrale ou sud confondues) ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT COUNT(*)
FROM Customers
WHERE Region LIKE '%America'
```
Combien de clients habitent en Europe de l'Ouest mais pas en France ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT COUNT(*)
FROM Customers
WHERE Region = 'Western Europe' AND Country != 'France'
```
Un client ne se souvient plus du nom de l'employé avec qui il s'est entretenu. Il se souvient simplement que celui-ci vit à Londres et parle japonais... Quels sont le nom et le prénom de cet employé ?
Votre réponse
SQL
Solution

La mention de la langue japonaise apparaît dans les Employees.Notes.
🗂️ Requête SQL
```
SELECT FirstName, LastName
FROM Employees
WHERE Employees.City = 'London' AND Notes LIKE '%Japan%'
```

Le « Richter Supermarkt » a effectué une commande livrée à Genève en 2017. Quels produits ont été livrés ?

Votre réponse

SQL

Solution

🗂️ Requête SQL

SELECT ProductName
FROM Products
JOIN OrderDetails   ON OrderDetails.ProductID = Products.ProductID
JOIN Orders         ON Orders.OrderID = OrderDetails.OrderID
JOIN Customers      ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShippedDate LIKE '%2017-09%'
      AND Orders.ShipCity = 'Genève'
      AND Customers.CompanyName = 'Richter Supermarkt'

Il est possible calculer le montant d'une commande en calculant la somme du prix unitaire multiplié par la quantité commandée. Quel est la montant de la commande n° 10789 ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT SUM(UnitPrice * Quantity)
FROM OrderDetails
WHERE OrderID = 10789
```
Quel est le nom de l'entreprise de livraison la plus utilisée ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT ShipVia, Shippers.CompanyName, COUNT(*) as total
FROM Orders
JOIN Shippers ON Shippers.ShipperID = Orders.ShipVia
GROUP BY ShipVia
ORDER BY total DESC
LIMIT 1;
```
Qui est le directeur du magasin ? Il ne rend compte à personne.
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT FirstName, LastName
FROM Employees
WHERE ReportsTo IS NULL
```
Combien de livraisons ont été faite à l'entreprise « Rancho grande » ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT COUNT(*)
FROM Orders
JOIN Customers ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CompanyName = 'Rancho grande';
```
Dans quel pays est situé la ville de « Walla Walla » ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT Country
FROM Customers
WHERE City = 'Walla Walla';
```
Combien y-a-t-il de produits vendus en bouteille ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT COUNT(*)
FROM Products
WHERE QuantityPerUnit LIKE '%bottle%';
```
Quel nombre moyens de produits de chaque catégorie est en stock ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT Products.CategoryID, CategoryName, ROUND(AVG( UnitsInStock ), 2) AS moyenne
FROM Products
JOIN Categories ON Categories.CategoryID = Products.CategoryID
GROUP BY Products.CategoryID
ORDER BY moyenne ASC;
```
Quelles sont les commandes qui n'ont pas encore été expédiées ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT COUNT(*)
FROM Orders
WHERE ShippedDate IS NULL
```
Quels sont les nom et prénom de l'employé qui a réalisé le plus de commandes ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT FirstName, LastName, Orders.EmployeeID, COUNT(*) AS total
FROM Orders
JOIN Employees ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Orders.EmployeeID
ORDER BY total DESC
LIMIT 1;
```
Insérer votre lycée dans la table des clients. On pourra laisser certaines informations vides.
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
INSERT INTO Customers (CompanyName, ContactName, ContactTitle, City, Country)
VALUES ('Lycée Jules Ferry', 'Michelle Langle', 'Directrice', 'Vannes', 'FRANCE');
```
La commande numéro 11008 a été expédiée le 7 mai 2018. Mettre à jour la ligne correspondante.
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
UPDATE Orders
SET ShippedDate = '2018-05-07'
WHERE OrderID = 11008;
```
Dans quelles régions se trouvent les fournisseurs ?
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
SELECT DISTINCT Region
FROM Suppliers;
```
Le fournisseur « PB Knäckebröd AB » situé en Suède n'a pas de région renseigné. Mettre à jour ses informations.
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
UPDATE Suppliers
SET Region = 'Scandinavia'
WHERE CompanyName = 'PB Knäckebröd AB';
```
L'employée « Nancy Davolio », dont l'identifiant est le 1, a passé par mégarde une commande le 5 mai 2018. Effacer cette commande de la base de données.
Votre réponse
SQL
Solution

En utilisant le EmployeeID on peut simplement faire :
🗂️ Requête SQL
```
DELETE FROM Orders
WHERE EmployeeID = 1 AND Orders.OrderDate = '20150-05-05';
```
La requête utilisant le nom et le prénom de l'employé est plus compliquée en sqlite :
🗂️ Requête SQL
```
DELETE FROM Orders
WHERE OrderID IN (
    SELECT OrderID
    FROM Orders
    JOIN Employees ON Employees.EmployeeID = Orders.EmployeeID
    WHERE Employees.Firstname = 'Nancy'
          AND Employees.Lastname = 'Davolio'
          AND Orders.OrderDate = '20150-05-05'
);
```
De nouvelles taxes sont appliquées aux produits vendus par le fournisseur d'identifiant le n° 8. Ajoutez 5 % au prix unitaire de tous ses produits.
Votre réponse
SQL
Solution
🗂️ Requête SQL
```
UPDATE Products
SET UnitPrice = 1.05 * UnitPrice
WHERE SupplierID = 8;
```

Quel est le nom du fournisseur vendant le plus de produit de la catégorie « Seafood » ?

Votre réponse

SQL

Solution

🗂️ Requête SQL

SELECT Suppliers.CompanyName, COUNT(*) as total
FROM Products
JOIN Suppliers ON Suppliers.SupplierID = Products.SupplierID
JOIN Categories ON Categories.CategoryID = Products.CategoryID
WHERE Categories.CategoryName = 'Seafood'
GROUP BY Products.SupplierID
ORDER BY total DESC
LIMIT 1;