3join.txt

CREATE DATABASE  ecommerce;

USE ecommerce;


CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    manager_id INT
);


CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers (customer_id, first_name, last_name, manager_id)
VALUES
    (1, 'John', 'Doe', NULL),
    (2, 'Jane', 'Smith', 1),
    (3, 'Bob', 'Johnson', 1);

INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES
    (101, 1, '2023-01-15', 1200.00),
    (102, 2, '2023-02-20', 900.00);



SELECT customers.customer_id, customers.first_name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;


SELECT customers.customer_id, customers.first_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;


SELECT customer_id, first_name,
       (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;




CREATE VIEW customer_order_totals AS
SELECT c.customer_id, c.first_name, SUM(o.total_amount) AS total_order_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name;


SELECT c.customer_id, c.first_name, o.order_id, o.total_amount
FROM customers c
JOIN (SELECT * FROM orders WHERE order_date >= '2023-01-01') o ON c.customer_id = o.customer_id;