6.
Source Code Examples
This section provides concise, runnable examples for three storage options SQLite. MySQL
(mysql-connector) and CSV These snippets are illustrative and focus on core operations
(connect, CRUD)
#create database and table
CREATE DATABASE ecommerce_db;
USE ecommerce_db;
#users table
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(50)
);
#products table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(50),
price INT,
stock INT
);
#orders table
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
product_id INT,
quantity INT,
total_price INT,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
#insert users
INSERT INTO users VALUES
(1, 'Rahul Sharma', 'rahul@[Link]'),
(2, 'Priya Singh', 'priya@[Link]');
#insert products
INSERT INTO products VALUES
(101, 'Mobile Phone', 15000, 10),
(102, 'Laptop', 55000, 5),
(103, 'Headphones', 2000, 20);
#insert orders
INSERT INTO orders VALUES
(1, 1, 101, 1, 15000);
#output from users
SELECT * FROM users;
+---------+---------------+-------------------+
| user_id | name | email |
+---------+---------------+-------------------+
|1 | Rahul Sharma | rahul@[Link] |
|2 | Priya Singh | priya@[Link] |
+---------+---------------+-------------------+
#ourtput from products
SELECT * FROM products;
+------------+--------------+-------+-------+
| product_id | product_name | price | stock |
+------------+--------------+-------+-------+
| 101 | Mobile Phone | 15000 | 10 |
| 102 | Laptop | 55000 | 5 |
| 103 | Headphones | 2000 | 20 |
+------------+--------------+-------+-------+
#output to display orders
SELECT * FROM orders;
+----------+---------+------------+----------+-------------+
| order_id | user_id | product_id | quantity | total_price |
+----------+---------+------------+----------+-------------+
|1 |1 | 101 |1 | 15000 |
+----------+---------+------------+----------+-------------+
#output for coustomer bill
SELECT [Link], p.product_name, [Link], o.total_price
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN products p ON o.product_id = p.product_id;
+---------------+--------------+----------+-------------+
| name | product_name | quantity | total_price |
+---------------+--------------+----------+-------------+
| Rahul Sharma | Mobile Phone | 1 | 15000 |
+---------------+--------------+----------+-------------+