-- Skriv din SQL-kod här
CREATE TABLE Departments(
DepartmentID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
DepartmentName varchar(50),
Location varchar(50)
);
-- Skriv din SQL-kod här
INSERT INTO Departments(DepartmentName, Location)
VALUES
('IT', 'Stockholm'),
('HR', 'Göteborg'),
('Finance', 'Malmö');
-- Skriv din SQL-kod här
SELECT * FROM Customers
-- Skriv din SQL-kod här
SELECT * FROM Products
WHERE Price > 200;
-- Skriv din SQL-kod här
SELECT FirstName, LastName, BirthDate
FROM Employees
-- Skriv din SQL-kod här
SELECT * FROM Orders
WHERE OrderDate > '1997-01-01';
-- Skriv din SQL-kod här
SELECT CustomerName, City
FROM Customers
WHERE City='Rio de Janeiro';
-- Skriv din SQL-kod här
SELECT ProductName, Unit
FROM Products
WHERE Unit Like "%pies%";
-- Skriv din SQL-kod här
SELECT * FROM Orders
ORDER BY OrderDate DESC
LIMIT 5;
-- Skriv din SQL-kod här
UPDATE Shippers
SET Phone ='1234567890'
WHERE ShipperName = 'Speedy Express';
-- Skriv din SQL-kod här
UPDATE Customers
SET Address ='Main Street 456'
WHERE CustomerId=10;
-- Skriv din SQL-kod här
DELETE FROM Products WHERE ProductId=60;
-- Skriv din SQL-kod här
DELETE FROM Customers WHERE Country='Germany';
-- Skriv din SQL-kod här
SELECT MAX(Price) FROM Products;
-- Skriv din SQL-kod här
SELECT AVG(Price) FROM Products;
-- Skriv din SQL-kod här
SELECT *
FROM Customers
JOIN Orders
ON Customers.CustomerId=Orders.CustomerId;
-- Skriv din SQL-kod här
SELECT Products.ProductName,Categories.CategoryName
FROM Products
JOIN Categories
ON Products.CategoryId = Categories.CategoryId;
-- Skriv din SQL-kod här
SELECT Employees.LastName, Employees.FirstName, Departments.DepartmentName, Departments.Location
FROM Employees
JOIN Departments
ON Employees.DepartmentID=Departments.DepartmentID;
(Använd Orders, Customers, Employees och Departments.)
-- Skriv din SQL-kod här
SELECT Customers.CustomerName, Orders.OrderDate, Departments.DepartmentName
FROM Orders
JOIN Customers
ON Orders.CustomerId=Customers.CustomerId
JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
JOIN Departments
ON Employees.DepartmentID=Departments.DepartmentID;
-- Skriv din SQL-kod här
SELECT * FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);
-- Skriv din SQL-kod här
SELECT Employees.FirstName, Employees.LastName
FROM Employees
JOIN Orders ON Employees.EmployeeID=Orders.EmployeeID
GROUP BY Employees.EmployeeID, Employees.FirstName, Employees.LastName
HAVING COUNT(Orders.OrderID >2);
-- Skriv din SQL-kod här
-- Skriv din SQL-kod här
-- Skriv din SQL-kod här
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
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, Customers.CustomerName;
-- Skriv din SQL-kod här
SELECT Products.ProductName,
SUM(OrderDetails.Quantity) AS TotalSold
FROM Products
JOIN OrderDetails ON Products.ProductId = OrderDetails.ProductId
GROUP BY Products.ProductId, Products.ProductName
ORDER BY TotalSold DESC
LIMIT 5;
-- Skriv din SQL-kod här