Tamer Jamal - Frågor

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 > '1997-01-01';




        

SELECT City, CustomerName
FROM Customers
WHERE City = 'Rio de Janeiro';



        

SELECT ProductName, UNIT FROM Products
WHERE unit LIKE '%pies%';



        

SELECT * FROM Orders
ORDER BY OrderDate ASC
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, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;



        

SELECT Products.ProductName, Categories.CategoryId, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryId = Categories.CategoryId;


        

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentID, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentId=Departments.DepartmentID;



        

SELECT 
    o.OrderId,
    c.CustomerName,
    o.OrderDate,
    d.DepartmentName
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.CustomerId
INNER JOIN Employees e ON o.EmployeeId = e.EmployeeId
INNER JOIN Departments d ON e.DepartmentId = d.DepartmentID;



        

SELECT *
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);




        

SELECT 
    Orders.EmployeeID,
    Employees.FirstName,
    COUNT(Orders.OrderID) AS AntalBeställningar
FROM Orders
INNER JOIN Employees 
    ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY Orders.EmployeeID, Employees.FirstName
HAVING COUNT(Orders.OrderID) > 2;




        

SELECT 
    c.CustomerName,
    o.OrderId,
    SUM(od.Quantity * p.Price) AS TotalOrderValue
FROM Customers AS c
INNER JOIN Orders AS o 
    ON c.CustomerId = o.CustomerId
INNER JOIN OrderDetails AS od 
    ON o.OrderId = od.OrderId
INNER JOIN Products AS p 
    ON od.ProductId = p.ProductId
WHERE c.Country = 'USA'
GROUP BY c.CustomerName, o.OrderId
ORDER BY TotalOrderValue DESC
LIMIT 20;




        

SELECT *
FROM Employees
WHERE Employees.BirthDate BETWEEN '1950-01-01' AND '1960-12-31';




        

SELECT 
    d.DepartmentID,
    d.DepartmentName,
    COUNT(e.EmployeeID) AS employee_count
FROM Departments AS d
LEFT JOIN Employees AS e
    ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentID, d.DepartmentName
ORDER BY employee_count DESC;





        

CREATE VIEW CustomerSpending AS
SELECT 
    c.CustomerName,
    SUM(od.Quantity * p.Price) AS TotalSpent
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.CustomerID = o.CustomerID
INNER JOIN OrderDetails AS od
    ON o.OrderID = od.OrderID
INNER JOIN Products AS p
    ON od.ProductID = p.ProductID
GROUP BY c.CustomerName;





        

SELECT 
    p.ProductID,
    p.ProductName,
    SUM(od.Quantity) AS TotalUnitsSold
FROM Products AS p
INNER JOIN OrderDetails AS od
    ON p.ProductID = od.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY TotalUnitsSold DESC
LIMIT 5;