Webbserverprogrammering 1 - Charlie Dahlén - SQL-frågorna

Del 1: Skapa och hantera tabeller

1. Skapa en tabell (Departments) som lagrar information om företagets avdelningar. Tabellen ska ha kolumnerna:
CREATE TABLE Departments (
DepartmentID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
DepartmentName VARCHAR(50),
Location VARCHAR(50)
); 
2. Lägg till tre avdelningar i tabellen Departments:
INSERT INTO Departments (DepartmentName, Location) VALUES 
('IT', 'Stockholm'),
('HR', 'Göteborg'),
('Finance', 'Malmö'); 

Del 2: Grundläggande urval

3. Visa alla kunder från tabellen Customers.
SELECT CustomerName FROM Customers; 
4. Visa alla produkter som kostar mer än 200 kr från tabellen Products.
SELECT * FROM Products WHERE Price > 200; 
5. Visa alla anställdas förnamn, efternamn och födelsedatum från tabellen Employees.
SELECT FirstName, LastName, BirthDate FROM Employees; 

Del 3: Filter och sortering

6. Visa alla beställningar från tabellen Orders som gjordes efter den 1 januari 1997.
SELECT * FROM Orders WHERE OrderDate > DATE('1997-01-01'); 
7. Visa kundnamn och stad för kunder som bor i "Rio de Janeiro" (Customers).
SELECT CustomerName, City FROM Customers WHERE City = 'Rio de Janeiro'; 
8. Visa produktnamn och enhet för produkter som har "pies" i enhetsfältet (Products).
SELECT ProductName, Unit FROM Products WHERE Unit LIKE '%pies%'; 
9. Visa de fem senaste beställningarna från Orders, sorterade efter datum (nyast först).
SELECT * FROM Orders ORDER BY OrderDate DESC LIMIT 5; 

Del 4: Uppdatera och radera data

10. Uppdatera telefonnumret till "1234567890" för fraktföretaget "Speedy Express" i tabellen Shippers.
UPDATE Shippers SET Phone = '1234567890' WHERE ShipperName = 'Speedy Express'; 
11. Uppdatera adressen för kund med ID 10 till "Main Street 456" i tabellen Customers.
UPDATE Customers SET Address = 'Main Street 456' WHERE CustomerId = 10; 
12. Ta bort produkten med ID 60 från tabellen Products.
DELETE FROM Products WHERE ProductId = 60; 
13. Ta bort alla kunder från "Germany" i tabellen Customers.
DELETE FROM Customers WHERE Country = 'Germany'; 

Del 5: Aggregering och funktioner

14. Visa det högsta priset bland alla produkter i Products.
SELECT MAX(Price) FROM Products; 
15. Visa det genomsnittliga priset för alla produkter i Products.
SELECT AVG(Price) FROM Products; 

Del 6: Joins – koppla tabeller

16. Visa alla kunder och deras beställningar genom att koppla Customers och Orders.
SELECT Orders.OrderId, Customers.CustomerName, Customers.CustomerId FROM Orders 
JOIN Customers ON Orders.CustomerId=Customers.CustomerId; 
17. Visa alla produkter tillsammans med deras kategorinamn (Products + Categories).
SELECT Products.ProductName, Categories.CategoryName FROM Products 
JOIN Categories ON Products.CategoryId=Categories.CategoryId; 
18. Visa alla anställda tillsammans med deras avdelningsnamn och plats (Employees + Departments).
SELECT LastName, FirstName, DepartmentName, Location FROM Employees 
JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID; 
19. Visa alla beställningar med kundnamn, orderdatum och avdelningsnamn för den ansvariga anställda.

(Använd Orders, Customers, Employees och Departments.)

SELECT OrderId, CustomerName, OrderDate, DepartmentName FROM Orders 
JOIN Customers ON Orders.CustomerId=Customers.CustomerId 
JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID 
JOIN Departments ON Employees.EmployeeID=Departments.DepartmentID; 

Del 7: Avancerade frågor

20. Visa alla produkter som kostar mer än det genomsnittliga priset.
SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products); 
21. Visa alla anställda som har hanterat fler än 2 beställningar.
SELECT Employees.FirstName, Employees.LastName, COUNT(OrderId) 
FROM Employees JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID 
GROUP BY Employees.EmployeeID 
HAVING COUNT(Orders.EmployeeID) > 2; 
22. Visa alla kunder från "USA" och deras 20 högsta beställningar (räknat som Quantity × Price).
SELECT Customers.CustomerName, (OrderDetails.Quantity * Products.Price) AS TotalPrice FROM Customers 
JOIN Orders ON Customers.CustomerId=Orders.CustomerId 
JOIN OrderDetails ON Orders.OrderId=OrderDetails.OrderId 
JOIN Products ON OrderDetails.ProductId=Products.ProductId 
WHERE Customers.Country='USA' 
ORDER BY TotalPrice DESC 
LIMIT 20;  
23. Visa alla anställda födda mellan 1950 och 1960.
SELECT * FROM Employees WHERE BirthDate BETWEEN DATE('1950-00-00') AND DATE('1960-00-00'); 
24. Visa hur många anställda som finns i varje avdelning (Departments + Employees).
SELECT Departments.DepartmentID, DepartmentName, COUNT(EmployeeID) as EmployeeCount FROM Employees 
JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID 
GROUP BY Departments.DepartmentID 

Del 8: VIEW och sammanfattning

25. Skapa en VIEW som heter CustomerSpending och visar:

Använd tabellerna Customers, Orders, OrderDetails och Products.

CREATE VIEW CustomerSpending AS
SELECT Customers.CustomerName, SUM(OrderDetails.Quantity * Products.Price) AS TotalPrice 
FROM Customers 
JOIN Orders ON Customers.CustomerId=Orders.CustomerId 
JOIN OrderDetails ON Orders.OrderId=OrderDetails.OrderId 
JOIN Products ON OrderDetails.ProductId=Products.ProductId 
GROUP BY Customers.CustomerId; 
26. Visa de fem mest sålda produkterna (efter total antal sålda enheter).
SELECT Products.ProductName, OrderDetails.Quantity FROM Orders 
JOIN OrderDetails ON Orders.OrderId=OrderDetails.OrderId 
JOIN Products ON OrderDetails.ProductId=Products.ProductId 
ORDER BY OrderDetails.Quantity DESC 
LIMIT 5; 
27. Visa alla kunder från "USA" som har gjort en beställning under de senaste 6 månaderna.
SELECT Customers.CustomerName, Orders.OrderDate FROM Orders 
JOIN Customers ON Orders.CustomerId=Customers.CustomerId 
WHERE Customers.Country = 'USA' 
AND Orders.OrderDate > DATE_SUB(NOW(), INTERVAL 6 MONTH);