Scribd
Upload
15 views7 pages

April Assignment

The document outlines a MySQL assignment for creating an Online Shop database, detailing the creation of six tables with specified columns and relationships, along with CRUD operations, JOINs, subqueries, and stored procedures. It includes SQL code examples for table creation, data insertion, and various queries to manage and retrieve data. Additionally, it presents a challenge task that combines multiple concepts to find customers who purchased products from a specific category.

Uploaded by

syedfaakhirshah
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
15 views7 pages

April Assignment

The document outlines a MySQL assignment for creating an Online Shop database, detailing the creation of six tables with specified columns and relationships, along with CRUD operations, JOINs, subqueries, and stored procedures. It includes SQL code examples for table creation, data insertion, and various queries to manage and retrieve data. Additionally, it presents a challenge task that combines multiple concepts to find customers who purchased products from a specific category.

Uploaded by

syedfaakhirshah
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

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', '[email protected]', '1234567890', '123 Main St'),
('Bob Johnson', '[email protected]', '2345678901', '456 Oak Ave'),
('Charlie Brown', '[email protected]', '3456789012', '789 Pine Rd'),
('Daisy Lee', '[email protected]', '4567890123', '321 Cedar Blvd'),
('Ethan Hunt', '[email protected]', '5678901234', '654 Spruce Ln'),
('Fiona Gill', '[email protected]', '6789012345', '987 Birch Ct'),
('George Bush', '[email protected]', '7890123456', '741 Willow Dr'),
('Hannah Wells', '[email protected]', '8901234567', '852 Fir Loop'),
('Ian Somerhalder', '[email protected]', '9012345678', '963 Elm Cir'),
('Jill Turner', '[email protected]', '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';

You might also like