-- Creating the Online Grocery Shop Database
CREATE DATABASE GroceryShop;
USE GroceryShop;
-- Creating the Customers table
CREATE TABLE Customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(15),
address TEXT
);
-- Creating the Products table
CREATE TABLE Products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(50),
price DECIMAL(10,2) CHECK (price >= 0),
stock_quantity INT CHECK (stock_quantity >= 0)
);
-- Creating the Orders table
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
-- Creating the OrderDetails table
CREATE TABLE OrderDetails (
order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT CHECK (quantity > 0),
subtotal DECIMAL(10,2),
FOREIGN KEY (order_id) REFERENCES Orders(order_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
-- Creating the Payments table
CREATE TABLE Payments (
payment_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
amount DECIMAL(10,2),
payment_method ENUM('Credit Card', 'Debit Card', 'UPI', 'Cash'),
FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);
-- Sample Data for Customers
INSERT INTO Customers (customer_name, email, phone, address) VALUES
('Arjun Kumar', '
[email protected]', '9876543210', 'Chennai, India'),
('Neha Sharma', '
[email protected]', '9123456789', 'Delhi, India');
-- Sample Data for Products
INSERT INTO Products (product_name, category, price, stock_quantity) VALUES
('Apple', 'Fruits', 120.00, 50),
('Rice', 'Grains', 60.00, 100),
('Milk', 'Dairy', 45.00, 30);
-- Aggregate Functions
SELECT COUNT(order_id) AS total_orders, AVG(total_amount) AS average_order_value
FROM Orders;
SELECT MIN(price) AS cheapest_product, MAX(price) AS most_expensive_product FROM
Products;
-- Date & Time Functions
SELECT NOW() AS current_time, CURDATE() AS today_date;
SELECT order_date, DATE_ADD(order_date, INTERVAL 5 DAY) AS estimated_delivery FROM
Orders;
SELECT order_date, DATEDIFF(NOW(), order_date) AS days_since_order FROM Orders;
SELECT CONVERT_TZ(NOW(), 'UTC', '+05:30') AS IST_time;
SELECT TZ_OFFSET('+05:30') AS timezone_offset;
SELECT STR_TO_DATE('2025-03-01', '%Y-%m-%d') AS formatted_date;
-- Subqueries & EXISTS
SELECT customer_name FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE
Orders.customer_id = Customers.customer_id);
SELECT product_name FROM Products WHERE price > ANY (SELECT price FROM Products
WHERE category = 'Dairy');
-- Regular Expressions
SELECT product_name FROM Products WHERE REGEXP_LIKE(product_name, '^A');
SELECT REGEXP_COUNT(product_name, 'a') AS count_a FROM Products;
SELECT REGEXP_SUBSTR(product_name, '[A-Za-z]+') AS extracted_text FROM Products;
SELECT REGEXP_REPLACE(product_name, 'Apple', 'Mango') AS updated_name FROM
Products;
-- FULL OUTER JOIN (Emulated with LEFT and RIGHT JOIN)
SELECT c.customer_name, o.order_id, p.payment_method FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
RIGHT JOIN Payments p ON o.order_id = p.order_id;
-- Stored Procedure for Order Summary
DELIMITER //
CREATE PROCEDURE GetOrderSummary(IN o_id INT)
BEGIN
SELECT o.order_id, c.customer_name, o.total_amount, p.payment_method
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
LEFT JOIN Payments p ON o.order_id = p.order_id
WHERE o.order_id = o_id;
END //
DELIMITER ;
-- Transaction with ROLLBACK TO SAVEPOINT
START TRANSACTION;
SAVEPOINT before_update;
UPDATE Products SET stock_quantity = stock_quantity - 5 WHERE product_id = 1;
ROLLBACK TO before_update;
COMMIT;
-- User Management
GRANT SELECT, INSERT, UPDATE ON GroceryShop.* TO 'grocery_user'@'localhost' WITH
GRANT OPTION;
REVOKE ROLE 'analyst' FROM 'grocery_user'@'localhost';
-- Backup & Restore
-- Use mysqldump to backup: mysqldump -u root -p GroceryShop >
C:/backup/grocery_backup.sql
-- Restore using: mysql -u root -p GroceryShop < C:/backup/grocery_backup.sql
