Webbserverprogrammering 1 - Abdualrhman Mardini - 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:
-- Skriv din SQL-kod här

   CREATE TABLE Departments(
   DepartmentID INT AUTO_INCREMENT PRIMARY KEY,
   DepartmentName VARCHAR(50) NOT NULL,
   Location VARCHAR(50)
   );
2. Lägg till tre avdelningar i tabellen Departments:
-- Skriv din SQL-kod här

INSERT INTO Departments (DepartamentName, Location)
VALUES 
('IT', 'Stockholm'),
('HR', 'Göteborg'),
('Finance', 'Malmö');

Del 2: Grundläggande urval

3. Visa alla kunder från tabellen Customers.
-- Skriv din SQL-kod här

SELECT * FROM Customers;
4. Visa alla produkter som kostar mer än 200 kr från tabellen Products.
-- Skriv din SQL-kod här

SELECT * FROM Products;
WHERE Price > 200;
5. Visa alla anställdas förnamn, efternamn och födelsedatum från tabellen Employees.
-- Skriv din SQL-kod här

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.
-- Skriv din SQL-kod här

SELECT * FROM Orders;
WHERE OrderDate > '1997-01-01';
7. Visa kundnamn och stad för kunder som bor i "Rio de Janeiro" (Customers).
-- Skriv din SQL-kod här

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).
-- Skriv din SQL-kod här

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).
-- Skriv din SQL-kod här

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.
-- Skriv din SQL-kod här

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.
-- Skriv din SQL-kod här

UPDATE Customers
SET Address = 'Main Street 456'
WHERE CustomerId = 10;
12. Ta bort produkten med ID 60 från tabellen Products.
-- Skriv din SQL-kod här

DELETE FROM Products WHERE ProductId = 60;
13. Ta bort alla kunder från "Germany" i tabellen Customers.
-- Skriv din SQL-kod här

DELETE FROM Customers WHERE Country = 'Germany';

Del 5: Aggregering och funktioner

14. Visa det högsta priset bland alla produkter i Products.
-- Skriv din SQL-kod här

SELECT MAX(Price)
FROM Products;
15. Visa det genomsnittliga priset för alla produkter i Products.
-- Skriv din SQL-kod här

SELECT AVG(Price)
FROM Products;

Del 6: Joins – koppla tabeller

16. Visa alla kunder och deras beställningar genom att koppla Customers och Orders.
-- Skriv din SQL-kod här

SELECT Customers.CustomerId, Customers.CustomerName, Orders.OrderId, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerId = Orders.CustomerId;
17. Visa alla produkter tillsammans med deras kategorinamn (Products + Categories).
-- Skriv din SQL-kod här

SELECT Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryId = Categories.CategoryId;
18. Visa alla anställda tillsammans med deras avdelningsnamn och plats (Employees + Departments).
-- Skriv din SQL-kod här

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Departments.DepartmentName, Departments.Location
FROM Employees
INNER 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.)

-- Skriv din SQL-kod här

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

Del 7: Avancerade frågor

20. Visa alla produkter som kostar mer än det genomsnittliga priset.
-- Skriv din SQL-kod här

SELECT * FROM Products
Where Price > (SELECT AVG(Price) FROM Products);
21. Visa alla anställda som har hanterat fler än 2 beställningar.
-- Skriv din SQL-kod här

SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName,
       COUNT(Orders.OrderID) AS AntalOrders
FROM Employees
INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.LastName
HAVING COUNT(Orders.OrderID) > 2;
22. Visa alla kunder från "USA" och deras 20 högsta beställningar (räknat som Quantity × Price).
-- Skriv din SQL-kod här

SELECT Customers.CustomerName, Orders.OrderID,
       SUM(OrderDetails.Quantity * Products.Price) AS OrderTotal
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE Customers.Country = 'USA'
GROUP BY Customers.CustomerName, Orders.OrderID
ORDER BY OrderTotal DESC
LIMIT 20;
23. Visa alla anställda födda mellan 1950 och 1960.
-- Skriv din SQL-kod här

SELECT * FROM Employees
WHERE YEAR(BirthDate) BETWEEN 1950 AND 1960;
24. Visa hur många anställda som finns i varje avdelning (Departments + Employees).
-- Skriv din SQL-kod här

SELECT Departments.DepartmentName, COUNT(Employees.EmployeeID) AS AntalAnstallda
FROM Departments
LEFT JOIN Employees ON Departments.DepartmentID = Employees.DepartmentID
GROUP BY Departments.DepartmentName;

Del 8: VIEW och sammanfattning

25. Skapa en VIEW som heter CustomerSpending och visar:

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

-- Skriv din SQL-kod här

CREATE VIEW CustomerSpending AS
SELECT Customers.CustomerName,
       SUM(OrderDetails.Quantity * Products.Price) AS TotalSpent
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY Customers.CustomerID, Customers.CustomerName;
26. Visa de fem mest sålda produkterna (efter total antal sålda enheter).
-- Skriv din SQL-kod här

SELECT Products.ProductName, SUM(OrderDetails.Quantity) AS TotaltSalda
FROM Products
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
GROUP BY Products.ProductID, Products.ProductName
ORDER BY TotaltSalda DESC
LIMIT 5;
27. Visa alla kunder från "USA" som har gjort en beställning under de senaste 6 månaderna.
-- Skriv din SQL-kod här

SELECT DISTINCT Customers.CustomerName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.Country = 'USA'
AND Orders.OrderDate >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);