Online Shopping Database Project

About this Project

Database Schema

CREATE TABLE customers (cust_id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(30));
CREATE TABLE products (prod_id INT PRIMARY KEY, name VARCHAR(50), price DECIMAL(8,2), stock INT);
CREATE TABLE orders (order_id INT PRIMARY KEY, cust_id INT, prod_id INT, qty INT, order_date DATE,
  FOREIGN KEY(cust_id) REFERENCES customers(cust_id),
  FOREIGN KEY(prod_id) REFERENCES products(prod_id));

Useful Queries

-- total spent by each customer
SELECT c.name, SUM(p.price * o.qty) AS total_spent
FROM orders o JOIN customers c ON o.cust_id=c.cust_id
              JOIN products p ON o.prod_id=p.prod_id
GROUP BY c.name;

-- best-selling products
SELECT p.name, SUM(o.qty) AS units_sold
FROM orders o JOIN products p ON o.prod_id=p.prod_id
GROUP BY p.name ORDER BY units_sold DESC;

Summary

• This project shows a real, exam-ready database design with working queries.
• Create the tables, insert sample data, and run each query to learn by doing.

इस Project के बारे में

Database Schema

CREATE TABLE customers (cust_id INT PRIMARY KEY, name VARCHAR(50), city VARCHAR(30));
CREATE TABLE products (prod_id INT PRIMARY KEY, name VARCHAR(50), price DECIMAL(8,2), stock INT);
CREATE TABLE orders (order_id INT PRIMARY KEY, cust_id INT, prod_id INT, qty INT, order_date DATE,
  FOREIGN KEY(cust_id) REFERENCES customers(cust_id),
  FOREIGN KEY(prod_id) REFERENCES products(prod_id));

उपयोगी Queries

-- हर customer ने कुल कितना खर्च किया
SELECT c.name, SUM(p.price * o.qty) AS total_spent
FROM orders o JOIN customers c ON o.cust_id=c.cust_id
              JOIN products p ON o.prod_id=p.prod_id
GROUP BY c.name;

-- सबसे ज़्यादा बिकने वाले products
SELECT p.name, SUM(o.qty) AS units_sold
FROM orders o JOIN products p ON o.prod_id=p.prod_id
GROUP BY p.name ORDER BY units_sold DESC;

सारांश

• यह project एक real, exam-ready database design working queries के साथ दिखाता है।
• Tables बनाएं, sample data डालें, और हर query चलाकर करके सीखें।