SQL Queries
1. Insert Data into the Tables
-- Insert into Customer table
INSERT INTO Customer (CustomerID, FirstName, LastName, Email, Phone) VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '123-456-7890'),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '234-567-8901'),
(3, 'Emily', 'Jones', 'emily.jones@example.com', '345-678-9012'),
(4, 'Michael', 'Brown', 'michael.brown@example.com', '456-789-0123'),
(5, 'Sarah', 'Johnson', 'sarah.johnson@example.com', '567-890-1234');
-- Insert into Product table
INSERT INTO Product (ProductID, ProductName, Category, Price) VALUES
(1, 'Laptop', 'Electronics', 1000),
(2, 'Smartphone', 'Electronics', 500),
(3, 'Tablet', 'Electronics', 300),
(4, 'Headphones', 'Accessories', 100),
(5, 'Charger', 'Accessories', 20);
-- Insert into Order table
INSERT INTO [Order] (OrderID, OrderDate, CustomerID, TotalAmount) VALUES
(1, '2024-04-15', 1, 1500),
(2, '2024-04-16', 2, 700),
(3, '2024-04-17', 3, 320),
(4, '2024-04-18', 4, 100),
(5, '2024-04-19', 5, 1020);
-- Insert into OrderDetail table
INSERT INTO OrderDetail (OrderDetailID, OrderID, ProductID, Quantity,
UnitPrice) VALUES
(1, 1, 1, 1, 1000),
(2, 1, 2, 1, 500),
(3, 2, 3, 2, 300),
(4, 3, 4, 1, 100),
(5, 4, 5, 5, 20);
2. Update Existing Data in the Tables
-- Update the price of a product
UPDATE Product
SET Price = 550
WHERE ProductID = 2;
3. Delete Records from the Tables
-- Delete a customer record
DELETE FROM Customer
WHERE CustomerID = 5;
4. SQL Queries to Perform Various Tasks
-- a. Retrieve all customers along with their orders
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName,
[Order].OrderID, [Order].OrderDate, [Order].TotalAmount
FROM Customer
LEFT JOIN [Order] ON Customer.CustomerID = [Order].CustomerID;
-- b. Retrieve products along with their categories ordered by price in
descending order
SELECT ProductName, Category, Price
FROM Product
ORDER BY Price DESC;
-- c. Retrieve the latest order date for each customer
SELECT CustomerID, MAX(OrderDate) AS LatestOrderDate
FROM [Order]
GROUP BY CustomerID;
-- d. Retrieve the number of orders placed by each customer
SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders
FROM [Order]
GROUP BY CustomerID;
-- e. Retrieve the top 5 best-selling products (based on total quantity sold)
SELECT ProductID, SUM(Quantity) AS TotalQuantitySold
FROM OrderDetail
GROUP BY ProductID
ORDER BY TotalQuantitySold DESC
LIMIT 5;
-- f. Retrieve customers who have not placed any orders
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName
FROM Customer
LEFT JOIN [Order] ON Customer.CustomerID = [Order].CustomerID
WHERE [Order].OrderID IS NULL;
-- g. Retrieve orders placed on a specific date (e.g., '2024-04-18')
SELECT *
FROM [Order]
WHERE OrderDate = '2024-04-18';
-- h. Retrieve orders with a total amount greater than $1000
SELECT *
FROM [Order]
WHERE TotalAmount > 1000;
-- i. Retrieve customers who have placed orders for more than one product
category
SELECT DISTINCT Customer.CustomerID, Customer.FirstName, Customer.LastName
FROM Customer
JOIN [Order] ON Customer.CustomerID = [Order].CustomerID
JOIN OrderDetail ON [Order].OrderID = OrderDetail.OrderID
JOIN Product ON OrderDetail.ProductID = Product.ProductID
GROUP BY Customer.CustomerID, Customer.FirstName, Customer.LastName
HAVING COUNT(DISTINCT Product.Category) > 1;
-- j. Calculate the average order value for each customer and rank them by
their average order value
SELECT CustomerID, AVG(TotalAmount) AS AverageOrderValue,
RANK() OVER (ORDER BY AVG(TotalAmount) DESC) AS Rank
FROM [Order]
GROUP BY CustomerID;
-- k. Identify customers who have not made any purchases in the last 3 months
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName
FROM Customer
LEFT JOIN [Order] ON Customer.CustomerID = [Order].CustomerID
WHERE [Order].OrderDate IS NULL OR [Order].OrderDate < DATEADD(MONTH, -3,
GETDATE());
-- l. Retrieve the top 3 customers who have spent the most amount of money in
total
SELECT CustomerID, SUM(TotalAmount) AS TotalSpent
FROM [Order]
GROUP BY CustomerID
ORDER BY TotalSpent DESC
LIMIT 3;
-- m. Calculate the cumulative total amount spent by customers over time,
ordered by customer, and order date
SELECT CustomerID, OrderDate, TotalAmount,
SUM(TotalAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS
CumulativeTotal
FROM [Order]
ORDER BY CustomerID, OrderDate;
