CREATE TABLE Departments ( DepartmentID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, DepartmentName VARCHAR(50), Location VARCHAR(50) );
INSERT INTO Departments (DepartmentName, Location) VALUES
('IT', 'Stockholm'),
('HR', 'Göteborg'),
('Finance', 'Malmö');
SELECT CustomerName FROM Customers;
SELECT * FROM Products WHERE Price > 200;
SELECT FirstName, LastName, BirthDate FROM Employees;
SELECT * FROM Orders WHERE OrderDate > DATE('1997-01-01');
SELECT CustomerName, City FROM Customers WHERE City = 'Rio de Janeiro';
SELECT ProductName, Unit FROM Products WHERE Unit LIKE '%pies%';
SELECT * FROM Orders ORDER BY OrderDate DESC LIMIT 5;
UPDATE Shippers SET Phone = '1234567890' WHERE ShipperName = 'Speedy Express';
UPDATE Customers SET Address = 'Main Street 456' WHERE CustomerId = 10;
DELETE FROM Products WHERE ProductId = 60;
DELETE FROM Customers WHERE Country = 'Germany';
SELECT MAX(Price) FROM Products;
SELECT AVG(Price) FROM Products;
SELECT Orders.OrderId, Customers.CustomerName, Customers.CustomerId FROM Orders JOIN Customers ON Orders.CustomerId=Customers.CustomerId;
SELECT Products.ProductName, Categories.CategoryName FROM Products JOIN Categories ON Products.CategoryId=Categories.CategoryId;
SELECT LastName, FirstName, DepartmentName, Location FROM Employees JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID;
(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;
SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);
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;
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;
SELECT * FROM Employees WHERE BirthDate BETWEEN DATE('1950-00-00') AND DATE('1960-00-00');
SELECT Departments.DepartmentID, DepartmentName, COUNT(EmployeeID) as EmployeeCount FROM Employees JOIN Departments ON Employees.DepartmentID=Departments.DepartmentID GROUP BY Departments.DepartmentID
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;
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;
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);