# Bazy danych i zarządzanie informacją
# WPPT, Informatyka 2009
# 
# Tue, 03 Nov 2009 09:43:29 +0100
#
# Bartosz Chodorowski <bartosz.chodorowski.pwr.wroc.pl>
#
# Zadania zostały rozwiązane w języky MySQL. Pracowałem na bazie:
# http://www.geeksengine.com/lg.php?i=northwind-sql
#


# Lista 1, zadanie 01
#
SELECT products.ProductName, suppliers.CompanyName FROM products
	INNER JOIN suppliers
	ON products.SupplierID = suppliers.SupplierID
	WHERE products.ProductName REGEXP 't$'
		AND suppliers.CompanyName REGEXP '^P';


# Lista 1, zadanie 02
#
SELECT ProductName, UnitPrice FROM products
	WHERE (UnitPrice >= 10.0 AND UnitPrice <= 20.0)
		OR (UnitPrice >= 35.0 AND UnitPrice <= 50.0)
		OR UnitPrice IS NULL;


# Lista 1, zadanie 03
#
SELECT ProductName, CategoryID, UnitPrice from products
	ORDER BY CategoryID DESC, UnitPrice ASC;


# Lista 1, zadanie 04
#
SELECT DISTINCT Region FROM suppliers;


# Lista 1, zadanie 05
#
SELECT ProductID FROM order_details
	ORDER BY Quantity DESC LIMIT 3;


# Lista 1, zadanie 06
#
SELECT ProductID FROM order_details
	GROUP BY ProductID
	HAVING avg(Quantity) > 2;


# Lista 1, zadanie 07
#
SELECT CompanyName, OrderID FROM orders
	NATURAL JOIN customers
	WHERE OrderDate > date('1998-01-23');


# Lista 1, zadanie 08
#
SELECT LastName, FirstName, OrderDate FROM employees
	LEFT OUTER JOIN orders
	ON employees.EmployeeID = orders.EmployeeID;



# Lista 1, zadanie 09
#
SELECT shippers.CompanyName, suppliers.CompanyName FROM shippers
	CROSS JOIN suppliers
	WHERE shippers.CompanyName REGEXP '^[Ss]'
		AND suppliers.CompanyName REGEXP '^[Ss]';


# Lista 1, zadanie 10
#
SELECT ProductName, OrderDate FROM products
	NATURAL JOIN orders;


# Lista 1, zadanie 11
#
SELECT c1.CompanyName, c2.CompanyName FROM customers AS c1
	CROSS JOIN customers AS c2
	WHERE c1.Country = c2.Country AND c1.CustomerID < c2.CustomerID;


# Lista 1, zadanie 12
#
SELECT CustomerID, ProductName, sum(UnitPrice*Quantity*(1.0-Discount))
	FROM customers
	NATURAL JOIN orders
	NATURAL JOIN order_details
	NATURAL JOIN products
	GROUP BY CustomerID, ProductName;


# Lista 1, zadanie 13
#
SELECT ProductID, min(Quantity) FROM products
	NATURAL JOIN order_details
	GROUP BY ProductID;


# Lista 1, zadanie 14
#
SELECT LastName, FirstName FROM employees
	WHERE NOT EXISTS
	(
		SELECT EmployeeId FROM orders
		WHERE orders.EmployeeId=employees.EmployeeId
		AND OrderDate = date('1997-05-15')
	);


# Lista 1, zadanie 15
#
CREATE TEMPORARY TABLE tmptable AS SELECT * FROM customers;
SELECT * FROM tmptable;
DROP TABLE tmptable;


# Lista 1, zadanie 16
#
DELETE FROM order_details WHERE
	order_details.orderID IN
	(
	SELECT orders.OrderID FROM orders
		WHERE orders.OrderDate = '1998-04-14'
			OR orders.OrderDate = '1999-07-17'
	);


# Lista 1, zadanie 17
#
INSERT INTO order_details (OrderId, ProductID, UnitPrice, Quantity)
	SELECT OrderID, ProductID, 10, 8 FROM orders CROSS JOIN
	(SELECT * FROM products LIMIT 1) AS p
	WHERE OrderDate = '1998-04-14';


# Lista 1, zadanie 18
#
UPDATE order_details NATURAL JOIN orders
	SET UnitPrice=UnitPrice+2 
	WHERE ShipCountry = 'USA';


# Lista 1, zadanie 19
#
ALTER TABLE products ADD COLUMN TotalSales INTEGER;

UPDATE products NATURAL JOIN
	(
	SELECT ProductID, sum(UnitPrice*Quantity) AS foobar
		FROM order_details GROUP BY ProductID
	) AS fb
	SET TotalSales = foobar;


# Lista 1, zadanie 20
#
ALTER TABLE products DROP COLUMN TotalSales;