01. Esempi di Query

Schema logico del database orders

Gli esempi inclusi in questa pagina fanno riferimento al database di default predisposto dal sito w3schools.com:

Customers(CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country)

Categories(CategoryID, CategoryName, Description)

Employees(EmployeeID, LastName, FirstName, BirthDate, Photo, Notes)

OrderDetails(OrderDetailID, OrderID↑, ProductID↑, Quantity)

OrderID FK (Orders.OrderID)

ProductID FK(Products.ProductID)

Orders(OrderID, CustomerID↑, EmployeeID↑, OrderDate, ShipperID↑)

CustomerID FK (Customers.CustomerID)

EmployeeID FK(Employees.EmployeeID)

ShipperID FK (Shippers.ShipperID)

Products(ProductID, ProductName, SupplierID↑, CategoryID↑, Unit, Price)

SupplierID FK (Suppliers.SupplierID)

CategoryID FK (Categories.CategoryID)

Shippers(ShipperID, ShipperName, Phone)

Suppliers(SupplierID, SupplierName, ContactName, Address, City, PostalCode, Country, Phone)

Interrogazioni su tabelle singole

SELECT * FROM Customers;

SELECT CustomerName, ContactName FROM Customers;

SELECT * FROM Customers ORDER BY CustomerName ASC;

SELECT CustomerName as Società, ContactName as Nome FROM Customers ORDER BY Nome DESC;

SELECT * FROM Employees WHERE BirthDate > "1950-12-08";

SELECT * FROM Employees WHERE FirstName = "Laura";

SELECT * FROM Products WHERE Price > 30;

SELECT * FROM Employees WHERE FirstName IN ("Laura", "Steven", "Robert");

SELECT DISTINCT Country FROM Customers;

SELECT DISTINCT Country FROM Customers LIMIT 3;

Interrogazioni su tabelle in join

SELECT * FROM Orders, Customers WHERE Orders.CustomerID = Customers.CustomerID;

SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SELECT * FROM Orders, Customers, Shippers WHERE Orders.CustomerID = Customers.CustomerID AND Orders.ShipperID=Shippers.ShipperID;

SELECT * FROM (Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID) INNER JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID;

SELECT annidate

SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);

SELECT * FROM Customers WHERE PostalCode IN (SELECT PostalCode FROM Suppliers);


Funzioni

SELECT MAX(Price) FROM Products;

SELECT AVG(Price) as MEDIA FROM Products;

SELECT COUNT(*) FROM Customers WHERE Country="Italy";

SELECT ProductName FROM Products WHERE Price=(SELECT MIN(Price) FROM Products);


Costruzione di una query per selezionare tutti i CustomerName che non hanno mai acquistato un certo prodotto

/* JOIN tra le tabelle Customer e Orders */

SELECT * FROM Customers, Orders WHERE Customers.CustomerId=Orders.CustomerId;

/* al precedente risultato aggiungiamo la JOIN con OrderDetails */

SELECT * FROM Customers, Orders, OrderDetails WHERE Customers.CustomerId=Orders.CustomerId AND Orders.OrderId = OrderDetails.OrderId;

/* al precedente risultato aggiungiamo la JOIN con Products */

SELECT * FROM Customers, Orders, OrderDetails, Products WHERE Customers.CustomerId=Orders.CustomerId AND Orders.OrderId = OrderDetails.OrderId AND OrderDetails.ProductId=Products.ProductId;

/* aggiungiamo una clausola per selezionare solo il prodotto con ProductName="Queso Cabrales" */

SELECT * FROM Customers, Orders, OrderDetails, Products WHERE Customers.CustomerId=Orders.CustomerId AND Orders.OrderId = OrderDetails.OrderId AND OrderDetails.ProductId=Products.ProductId AND ProductName="Queso Cabrales";

/* proiettiamo il risultato sulla sola colonna de CustomerName */

/* questa query (che chiamiamo UNO) elenca tutti i Customers che hanno aquistato il prodotto */

SELECT CustomerName FROM Customers, Orders, OrderDetails, Products WHERE Customers.CustomerId=Orders.CustomerId AND Orders.OrderId = OrderDetails.OrderId AND OrderDetails.ProductId=Products.ProductId AND ProductName="Queso Cabrales";

/* costruiamo ora una query (che chiamiamo DUE) che elenca tutti i clienti */

SELECT CustomerName FROM Customers;

/* la query finale si otterrà escludendo dalla query DUE i Customers presenti nella query UNO */

SELECT CustomerName FROM Customers where CustomerName NOT IN (SELECT CustomerName FROM Customers, Orders, OrderDetails, Products WHERE Customers.CustomerId=Orders.CustomerId AND Orders.OrderId = OrderDetails.OrderId AND OrderDetails.ProductId=Products.ProductId AND ProductName="Queso Cabrales");