Untitled
unknown
mysql
13 days ago
14 kB
2
Indexable
Never
-- Show databases
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sakila |
| sys |
| we9 |
| weekdays_2pm |
| weekend9am |
| world |
+--------------------+
10 rows in set (0.00 sec)
-- I. Create Database xschema
mysql> create database xschema;
Query OK, 1 row affected (0.00 sec)
-- use Database xschema
mysql> use xschema;
Database changed
mysql> show tables;
Empty set (0.01 sec)
-- Tables
-- 1. Customers Table
mysql> CREATE TABLE Customers (
-> customer_id INT PRIMARY KEY AUTO_INCREMENT,
-> first_name VARCHAR(50),
-> last_name VARCHAR(50),
-> email VARCHAR(100) UNIQUE,
-> phone VARCHAR(15),
-> address VARCHAR(255)
-> );
Query OK, 0 rows affected (0.04 sec)
-- 2. Products Table
mysql> CREATE TABLE Products (
-> product_id INT PRIMARY KEY AUTO_INCREMENT,
-> product_name VARCHAR(100),
-> description TEXT,
-> price DECIMAL(10, 2),
-> stock_quantity INT
-> );
Query OK, 0 rows affected (0.02 sec)
-- 3. Orders Table
mysql> CREATE TABLE Orders (
-> order_id INT PRIMARY KEY AUTO_INCREMENT,
-> customer_id INT,
-> order_date DATE,
-> status VARCHAR(50),
-> FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
-> );
Query OK, 0 rows affected (0.05 sec)
-- 4. OrderDetails Table
mysql> CREATE TABLE OrderDetails (
-> order_detail_id INT PRIMARY KEY AUTO_INCREMENT,
-> order_id INT,
-> product_id INT,
-> quantity INT,
-> total_price DECIMAL(10, 2),
-> FOREIGN KEY (order_id) REFERENCES Orders(order_id),
-> FOREIGN KEY (product_id) REFERENCES Products(product_id)
-> );
Query OK, 0 rows affected (0.04 sec)
-- II. Relationships
-- Customers to Orders: One-to-Many (each customer can place multiple orders)
-- Orders to OrderDetails: One-to-Many (each order can contain multiple products)
-- Products to OrderDetails: One-to-Many (each product can appear in multiple order details)
-- ER Diagram
-- Draw an ER diagram showing:
-- Customers table connected to Orders table with a one-to-many line.
-- Orders table connected to OrderDetails with a one-to-many line.
-- Products table connected to OrderDetails with a one-to-many line.
-- ER Diagram Structure
+----------------+ +-------------+ +-------------------+
| Customers | | Orders | | OrderDetails |
|----------------| |-------------| |-------------------|
| customer_id PK |<-------+ | order_id PK | | order_detail_id PK |
| first_name | | | customer_id FK|<-----+ | order_id FK |
| last_name | | | order_date | | product_id FK |
| email | | | status | | quantity |
| phone | | +-------------+ | total_price |
| address | | +-------------------+
+----------------+ |
+----------------+
| Products |
|----------------|
| product_id PK |
| product_name |
| description |
| price |
| stock_quantity |
+----------------+
-- III. Operations
-- CRUD Operations
-- 1. Insert New Records
mysql> INSERT INTO Customers (first_name, last_name, email, phone, address) VALUES ('John', 'Doe', 'john@example.com', '1234567890', '123 Main St');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Products (product_name, description, price, stock_quantity) VALUES ('Widget', 'A useful widget.', 19.99, 100);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Orders (customer_id, order_date, status) VALUES (1, '2024-09-21', 'Shipped');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO OrderDetails (order_id, product_id, quantity, total_price) VALUES (1, 1, 2, 39.98);
Query OK, 1 row affected (0.00 sec)
-- 2. Read/Display Records
mysql> SELECT * FROM Customers;
+-------------+------------+-----------+------------------+------------+-------------+
| customer_id | first_name | last_name | email | phone | address |
+-------------+------------+-----------+------------------+------------+-------------+
| 1 | John | Doe | john@example.com | 1234567890 | 123 Main St |
+-------------+------------+-----------+------------------+------------+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Products;
+------------+--------------+------------------+-------+----------------+
| product_id | product_name | description | price | stock_quantity |
+------------+--------------+------------------+-------+----------------+
| 1 | Widget | A useful widget. | 19.99 | 100 |
+------------+--------------+------------------+-------+----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Orders;
+----------+-------------+------------+---------+
| order_id | customer_id | order_date | status |
+----------+-------------+------------+---------+
| 1 | 1 | 2024-09-21 | Shipped |
+----------+-------------+------------+---------+
1 row in set (0.00 sec)
mysql> SELECT * FROM OrderDetails;
+-----------------+----------+------------+----------+-------------+
| order_detail_id | order_id | product_id | quantity | total_price |
+-----------------+----------+------------+----------+-------------+
| 1 | 1 | 1 | 2 | 39.98 |
+-----------------+----------+------------+----------+-------------+
1 row in set (0.00 sec)
-- 3. Update Existing Records
mysql> UPDATE Customers SET phone = '0987654321' WHERE customer_id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Products SET price = 24.99 WHERE product_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE Orders SET status = 'Delivered' WHERE order_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE OrderDetails SET quantity = 3 WHERE order_detail_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 4. Delete Records
mysql> DELETE FROM OrderDetails WHERE order_detail_id = 1;
Query OK, 1 row affected (0.01 sec)
mysql> DELETE FROM Orders WHERE order_id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM Products WHERE product_id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM Customers WHERE customer_id = 1;
Query OK, 1 row affected (0.00 sec)
-- IV. SQL Queries
-- 1. Retrieve all orders with customer details
mysql> SELECT Orders.*, Customers.first_name, Customers.last_name FROM Orders
-> JOIN Customers ON Orders.customer_id = Customers.customer_id;
Empty set (0.00 sec)
-- 2. Find products ordered by a specific customer
mysql> SELECT Products.* FROM Products
-> JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
-> JOIN Orders ON OrderDetails.order_id = Orders.order_id
-> WHERE Orders.customer_id = 1; -- Replace with the specific customer ID
Empty set (0.00 sec)
-- 3. Retrieve total sales for each product
mysql> SELECT Products.product_name, SUM(OrderDetails.total_price) AS total_sales
-> FROM Products
-> JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
-> GROUP BY Products.product_name;
Empty set (0.01 sec)
-- 4. Find customers who placed at least one order
mysql> SELECT DISTINCT Customers.* FROM Customers
-> JOIN Orders ON Customers.customer_id = Orders.customer_id;
Empty set (0.00 sec)
-- 5. Retrieve total quantity of products ordered by each customer
mysql> SELECT Customers.first_name, Customers.last_name, SUM(OrderDetails.quantity) AS total_quantity
-> FROM Customers
-> JOIN Orders ON Customers.customer_id = Orders.customer_id
-> JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
-> GROUP BY Customers.customer_id;
Empty set (0.00 sec)
-- 6. Find all orders and details for a specific customer
mysql> SELECT Orders.*, OrderDetails.* FROM Orders
-> JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
-> WHERE Orders.customer_id = 1; -- Replace with specific customer ID
Empty set (0.00 sec)
-- 7. Retrieve all products with total quantity ordered
mysql> SELECT Products.product_name, SUM(OrderDetails.quantity) AS total_quantity
-> FROM Products
-> LEFT JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
-> GROUP BY Products.product_name;
Empty set (0.00 sec)
-- 8. Total revenue generated from orders in a date range
mysql> SELECT SUM(OrderDetails.total_price) AS total_revenue FROM Orders
-> JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
-> WHERE Orders.order_date BETWEEN '2024-01-01' AND '2024-12-31'; -- Replace with date range
+---------------+
| total_revenue |
+---------------+
| NULL |
+---------------+
1 row in set (0.00 sec)
-- 9. Retrieve customers who ordered a specific product
mysql> SELECT DISTINCT Customers.* FROM Customers
-> JOIN Orders ON Customers.customer_id = Orders.customer_id
-> JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
-> WHERE OrderDetails.product_id = 1; -- Replace with specific product ID
Empty set (0.00 sec)
-- 10. Find most frequently ordered product
mysql> SELECT Products.product_name, SUM(OrderDetails.quantity) AS total_ordered
-> FROM Products
-> JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
-> GROUP BY Products.product_name
-> ORDER BY total_ordered DESC
-> LIMIT 1;
Empty set (0.00 sec)
-- 11. Retrieve average order value for each customer
mysql> SELECT Customers.first_name, Customers.last_name, AVG(OrderDetails.total_price) AS average_order_value
-> FROM Customers
-> JOIN Orders ON Customers.customer_id = Orders.customer_id
-> JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
-> GROUP BY Customers.customer_id;
Empty set (0.00 sec)
-- 12. Find products that have never been ordered
mysql> SELECT * FROM Products
-> WHERE product_id NOT IN (SELECT product_id FROM OrderDetails);
Empty set (0.01 sec)
-- 13. Total number of orders placed each month
mysql> SELECT FORMAT(order_date, 'yyyy-mm') AS month, COUNT(*) AS total_orders
-> FROM Orders
-> GROUP BY FORMAT(order_date, 'yyyy-mm');
Empty set (0.00 sec)
-- 14. Total number of products ordered in each order
mysql> SELECT Orders.order_id, SUM(OrderDetails.quantity) AS total_products_ordered
-> FROM Orders
-> JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
-> GROUP BY Orders.order_id;
Empty set (0.00 sec)
-- 15.Top 5 customers based on total spending
mysql> SELECT Customers.first_name, Customers.last_name, SUM(OrderDetails.total_price) AS total_spending
-> FROM Customers
-> JOIN Orders ON Customers.customer_id = Orders.customer_id
-> JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
-> GROUP BY Customers.customer_id
-> ORDER BY total_spending DESC
-> LIMIT 5;
Empty set (0.00 sec)
-- 16.Retrieve all orders placed on a specific date
mysql> SELECT * FROM Orders WHERE order_date = '2024-09-21'; -- Replace with specific date
Empty set (0.00 sec)
-- 17.Total unique products ordered by each customer
mysql> SELECT Customers.first_name, Customers.last_name, COUNT(DISTINCT OrderDetails.product_id) AS unique_products_ordered
-> FROM Customers
-> JOIN Orders ON Customers.customer_id = Orders.customer_id
-> JOIN OrderDetails ON Orders.order_id = OrderDetails.order_id
-> GROUP BY Customers.customer_id;
Empty set (0.00 sec)
-- 18. Order details for the order with the highest total price
mysql> SELECT OrderDetails.*
-> FROM OrderDetails
-> JOIN Orders ON OrderDetails.order_id = Orders.order_id
-> WHERE Orders.order_id = (
-> SELECT order_id
-> FROM Orders
-> ORDER BY total_price DESC
-> LIMIT 1
-> );
Empty set (0.00 sec)
-- 19. Top 3 products based on total quantity ordered
mysql> SELECT Products.product_name, SUM(OrderDetails.quantity) AS total_quantity
-> FROM Products
-> JOIN OrderDetails ON Products.product_id = OrderDetails.product_id
-> GROUP BY Products.product_name
-> ORDER BY total_quantity DESC
-> LIMIT 3;
Empty set (0.00 sec)
-- 20. Total sales for each month
mysql> SELECT AVG(total_quantity) AS average_quantity_per_order
-> FROM (
-> SELECT SUM(quantity) AS total_quantity
-> FROM OrderDetails
-> GROUP BY order_id
-> ) AS order_totals;
+----------------------------+
| average_quantity_per_order |
+----------------------------+
| NULL |
+----------------------------+
1 row in set (0.00 sec)
mysql>Leave a Comment