Untitled
unknown
mysql
2 months ago
8.8 kB
3
Indexable
-- Drop existing tables if they exist
DROP TABLE IF EXISTS Payments;
DROP TABLE IF EXISTS OrderDetails;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Categories;
DROP TABLE IF EXISTS Customers;
-- Create Customers table
CREATE TABLE Customers (
Customer_ID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE,
Address VARCHAR(255),
Phone VARCHAR(15),
Date_Joined DATE
);
-- Create Categories table
CREATE TABLE Categories (
Category_ID INT PRIMARY KEY,
Category_Name VARCHAR(100) NOT NULL
);
-- Create Products table
CREATE TABLE Products (
Product_ID INT PRIMARY KEY,
Product_Name VARCHAR(100) NOT NULL,
Category_ID INT,
Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0),
Stock_Quantity INT NOT NULL CHECK (Stock_Quantity >= 0),
FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create Orders table
CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Customer_ID INT,
Order_Date DATE NOT NULL,
Total_Amount DECIMAL(10, 2) NOT NULL CHECK (Total_Amount >= 0),
Shipping_Address VARCHAR(255),
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create OrderDetails table
CREATE TABLE OrderDetails (
OrderDetail_ID INT PRIMARY KEY,
Order_ID INT,
Product_ID INT,
Quantity INT NOT NULL CHECK (Quantity > 0),
Unit_Price DECIMAL(10, 2) NOT NULL CHECK (Unit_Price >= 0),
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create Payments table
CREATE TABLE Payments (
Payment_ID INT PRIMARY KEY,
Order_ID INT,
Payment_Method VARCHAR(50) NOT NULL,
Payment_Amount DECIMAL(10, 2) NOT NULL CHECK (Payment_Amount >= 0),
Payment_Date DATE NOT NULL,
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Insert sample data into Categories
INSERT INTO Categories (Category_ID, Category_Name)
VALUES
(1, 'Electronics'),
(2, 'Home Appliances');
-- Insert sample data into Customers
INSERT INTO Customers (Customer_ID, Name, Email, Address, Phone, Date_Joined)
VALUES
(1, 'John Doe', 'john.doe@example.com', '123 Main St, City', '123-456-7890', '2025-01-06'),
(2, 'Jane Smith', 'jane.smith@example.com', '456 Elm St, City', '987-654-3210', '2025-01-07');
-- Insert sample data into Products
INSERT INTO Products (Product_ID, Product_Name, Category_ID, Price, Stock_Quantity)
VALUES
(1, 'Smartphone', 1, 699.99, 50),
(2, 'Laptop', 1, 1200.00, 30),
(3, 'Washing Machine', 2, 499.99, 20),
(4, 'Microwave Oven', 2, 300.00, 15);
-- Insert sample data into Orders
INSERT INTO Orders (Order_ID, Customer_ID, Order_Date, Total_Amount, Shipping_Address)
VALUES
(1, 1, '2025-01-06', 699.99, '123 Main St, City'),
(2, 2, '2025-01-07', 1300.00, '456 Elm St, City');
-- Insert sample data into OrderDetails
INSERT INTO OrderDetails (OrderDetail_ID, Order_ID, Product_ID, Quantity, Unit_Price)
VALUES
(1, 1, 1, 1, 699.99),
(2, 2, 2, 1, 1200.00);
-- Insert sample data into Payments
INSERT INTO Payments (Payment_ID, Order_ID, Payment_Method, Payment_Amount, Payment_Date)
VALUES
(1, 1, 'Credit Card', 699.99,'2025-01-06'),
(2, 2,'PayPal',1300.00,'2025-01-07');
-- Apply discounts to Products
-- Apply a 10% discount on Electronics category products
UPDATE Products
SET Price = Price * 0.9
WHERE Category_ID IN (SELECT Category_ID FROM Categories WHERE Category_Name = 'Electronics');
-- Apply a 15% discount on products priced above $500
UPDATE Products
SET Price = Price * 0.85
WHERE Price > 500;
-- Update Orders to apply a $50 discount on orders over $1000
UPDATE Orders
SET Total_Amount = Total_Amount - 50
WHERE Total_Amount > 1000;
-- Optionally: Check updated values in Products and Orders tables
SELECT * FROM Products;
SELECT * FROM Orders;
-- Drop existing tables if they exist
DROP TABLE IF EXISTS Payments;
DROP TABLE IF EXISTS OrderDetails;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Categories;
DROP TABLE IF EXISTS Customers;
-- Create Customers table
CREATE TABLE Customers (
Customer_ID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE,
Address VARCHAR(255),
Phone VARCHAR(15),
Date_Joined DATE
);
-- Create Categories table
CREATE TABLE Categories (
Category_ID INT PRIMARY KEY,
Category_Name VARCHAR(100) NOT NULL
);
-- Create Products table
CREATE TABLE Products (
Product_ID INT PRIMARY KEY,
Product_Name VARCHAR(100) NOT NULL,
Category_ID INT,
Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0),
Stock_Quantity INT NOT NULL CHECK (Stock_Quantity >= 0),
FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create Orders table
CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Customer_ID INT,
Order_Date DATE NOT NULL,
Total_Amount DECIMAL(10, 2) NOT NULL CHECK (Total_Amount >= 0),
Shipping_Address VARCHAR(255),
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create OrderDetails table
CREATE TABLE OrderDetails (
OrderDetail_ID INT PRIMARY KEY,
Order_ID INT,
Product_ID INT,
Quantity INT NOT NULL CHECK (Quantity > 0),
Unit_Price DECIMAL(10, 2) NOT NULL CHECK (Unit_Price >= 0),
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Create Payments table
CREATE TABLE Payments (
Payment_ID INT PRIMARY KEY,
Order_ID INT,
Payment_Method VARCHAR(50) NOT NULL,
Payment_Amount DECIMAL(10, 2) NOT NULL CHECK (Payment_Amount >= 0),
Payment_Date DATE NOT NULL,
FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Insert sample data into Categories
INSERT INTO Categories (Category_ID, Category_Name)
VALUES
(1, 'Electronics'),
(2, 'Home Appliances');
-- Insert sample data into Customers
INSERT INTO Customers (Customer_ID, Name, Email, Address, Phone, Date_Joined)
VALUES
(1, 'John Doe', 'john.doe@example.com', '123 Main St, City', '123-456-7890', '2025-01-06'),
(2, 'Jane Smith', 'jane.smith@example.com', '456 Elm St, City', '987-654-3210', '2025-01-07');
-- Insert sample data into Products
INSERT INTO Products (Product_ID, Product_Name, Category_ID, Price, Stock_Quantity)
VALUES
(1, 'Smartphone', 1, 699.99, 50),
(2, 'Laptop', 1, 1200.00, 30),
(3, 'Washing Machine', 2, 499.99, 20),
(4, 'Microwave Oven', 2, 300.00, 15);
-- Insert sample data into Orders
INSERT INTO Orders (Order_ID, Customer_ID, Order_Date, Total_Amount, Shipping_Address)
VALUES
(1, 1, '2025-01-06', 699.99, '123 Main St, City'),
(2, 2, '2025-01-07', 1300.00, '456 Elm St, City');
-- Insert sample data into OrderDetails
INSERT INTO OrderDetails (OrderDetail_ID, Order_ID, Product_ID, Quantity, Unit_Price)
VALUES
(1, 1, 1, 1, 699.99),
(2, 2, 2, 1, 1200.00);
-- Insert sample data into Payments
INSERT INTO Payments (Payment_ID, Order_ID, Payment_Method, Payment_Amount, Payment_Date)
VALUES
(1, 1,'Credit Card',699.99,'2025-01-06'),
(2, 2,'PayPal',1300.00,'2025-01-07');
-- Apply discounts to Products
-- Apply a 10% discount on Electronics category products
UPDATE Products
SET Price = Price * 0.9
WHERE Category_ID IN (SELECT Category_ID FROM Categories WHERE Category_Name = 'Electronics');
-- Apply a 15% discount on products priced above $500
UPDATE Products
SET Price = Price * 0.85
WHERE Price > 500;
-- Update Orders to apply a $50 discount on orders over $1000
UPDATE Orders
SET Total_Amount = Total_Amount - 50
WHERE Total_Amount > 1000;
-- Generate report of top 5 customers who spent the most in the last 6 months
SELECT TOP 5
c.Name AS Customer_Name,
SUM(o.Total_Amount) AS Total_Amount_Spent,
COUNT(o.Order_ID) AS Number_of_Orders,
(SELECT TOP 1 od.Product_ID
FROM OrderDetails od
JOIN Orders o2 ON od.Order_ID = o2.Order_ID
WHERE o2.Customer_ID = c.Customer_ID
GROUP BY od.Product_ID
ORDER BY COUNT(od.Product_ID) DESC) AS Most_Purchased_Product
FROM
Customers c
JOIN
Orders o ON c.Customer_ID = o.Customer_ID
WHERE
o.Order_Date >= DATEADD(MONTH, -6, GETDATE())
GROUP BY
c.Customer_ID,c.Name
ORDER BY
Total_Amount_Spent DESC;
Editor is loading...
Leave a Comment