Assignment
Month of April
MY SQL
DISM
Marks /100
My -based task for an Online Shop database that covers:
• Table creation with at least 6 columns
• Insertion of 10 rows each
• Primary and foreign key relationships
• CRUD operations
• JOINs (all types)
• Subqueries
• Stored procedure
• Usage of WHERE, LIKE, LIMIT, HAVING, GROUP BY
🛒 Task Title: Online Shop Database Project Using My
🧱 Step 1: Create Tables
1. Customers Table
CREATE TABLE Customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
phone VARCHAR(20),
address TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
2. Products Table
CREATE TABLE Products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
description TEXT,
price DECIMAL(10,2),
stock INT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
3. Categories Table
CREATE TABLE Categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(100),
description TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
status ENUM('Active', 'Inactive'),
featured BOOLEAN
);
4. Orders Table
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10,2),
status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled'),
payment_method VARCHAR(50),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
5. OrderDetails Table
CREATE TABLE OrderDetails (
order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
price DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES Orders(order_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
6. ProductCategories Table (Many-to-Many)
CREATE TABLE ProductCategories (
product_id INT,
category_id INT,
PRIMARY KEY (product_id, category_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id),
FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);
📝 Step 2: Insert 10 Rows Into Each Table
(Add your own test data as needed — names, descriptions, etc.)
-- Example for Customers
INSERT INTO Customers (name, email, phone, address) VALUES
('Alice Smith', 'alice@example.com', '1234567890', '123 Main St'),
('Bob Johnson', 'bob@example.com', '2345678901', '456 Oak Ave'),
('Charlie Brown', 'charlie@example.com', '3456789012', '789 Pine Rd'),
('Daisy Lee', 'daisy@example.com', '4567890123', '321 Cedar Blvd'),
('Ethan Hunt', 'ethan@example.com', '5678901234', '654 Spruce Ln'),
('Fiona Gill', 'fiona@example.com', '6789012345', '987 Birch Ct'),
('George Bush', 'george@example.com', '7890123456', '741 Willow Dr'),
('Hannah Wells', 'hannah@example.com', '8901234567', '852 Fir Loop'),
('Ian Somerhalder', 'ian@example.com', '9012345678', '963 Elm Cir'),
('Jill Turner', 'jill@example.com', '0123456789', '147 Aspen Way');
Repeat similar INSERT INTO for other tables like Products, Orders, etc.
🛠 Step 3: CRUD Operations
Create
INSERT INTO Products (name, description, price, stock)
VALUES ('Wireless Mouse', 'Ergonomic mouse', 25.99, 100);
Read
SELECT * FROM Products WHERE price < 50;
Update
UPDATE Customers SET address = '999 New St' WHERE customer_id = 1;
Delete
DELETE FROM Orders WHERE order_id = 5;
🔗 Step 4: JOINS
Inner Join
SELECT c.name, o.order_id, o.total_amount
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id;
Left Join
SELECT c.name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
Right Join
SELECT o.order_id, c.name
FROM Orders o
RIGHT JOIN Customers c ON o.customer_id = c.customer_id;
Full Outer Join (emulated using UNION)
SELECT c.name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
UNION
SELECT c.name, o.order_id
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;
🔍 Step 5: Subqueries
SELECT name, price
FROM Products
WHERE price > (SELECT AVG(price) FROM Products);
🧠 Step 6: Stored Procedure
DELIMITER //
CREATE PROCEDURE GetCustomerOrders(IN cust_id INT)
BEGIN
SELECT o.order_id, o.total_amount, o.order_date
FROM Orders o
WHERE o.customer_id = cust_id;
END //
DELIMITER ;
-- Call it like this:
CALL GetCustomerOrders(2);
📑 Step 7: Use of Clauses
WHERE & LIKE
SELECT * FROM Customers WHERE name LIKE 'A%';
LIMIT
SELECT * FROM Products ORDER BY price DESC LIMIT 5;
GROUP BY & HAVING
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;
✅ Challenge Task: Combine Concepts
Write a query to find customers who bought products from the
"Electronics" category, showing their name, product, and order date.
SELECT c.name, p.name AS product_name, o.order_date
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN OrderDetails od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
JOIN ProductCategories pc ON p.product_id = pc.product_id
JOIN Categories cat ON pc.category_id = cat.category_id
WHERE cat.category_name = 'Electronics';
