#join operation is used to combine rows from two or more tables based on a related column between them\
# cROSS jOIN --EVERY ROW FROM ONE TABLE WITH COMBINE EVERY ROW FROM ANOTHER TABLE
# 
#
#

create table customers(
    cust_id Int auto_increment primary key,
    name varchar(50),
    email varchar(50)
    );
create table orders(
    ord_id int auto_increment primary key,
    date Date,
    amount decimal(10,2),
        cust_id INT,
        foreign key (cust_id) references customers (cust_id)
    );
-- Inserting data into the customers table
INSERT INTO customers (name, email) VALUES
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com'),
('Michael Johnson', 'michael@example.com'),
('Emily Davis', 'emily@example.com'),
('William Brown', 'william@example.com'),
('Robert Wilson', 'robert@example.com'),
('Olivia Garcia', 'olivia@example.com'),
('Sophia Martinez', 'sophia@example.com'),
('James Lee', 'james@example.com'),
('Linda Taylor', 'linda@example.com');


-- Inserting data into the orders table

INSERT INTO orders (date, amount, cust_id) VALUES
('2024-10-01', 150.75, 1),
('2024-10-03', 99.99, 2),
('2024-10-05', 250.00, 3),
('2024-10-07', 175.50, 4),
('2024-10-09', 120.00, 2),
('2024-10-10', 80.00, 4),
('2024-10-12', 300.75, 7),
('2024-10-14', 199.99, 5),
('2024-10-15', 500.00, 6),
('2024-10-16', 220.50, 10);

#CROSS JOIN 
#select * from customers, orders;

# INNER JOIN
#RETURN ONLY THE ROWS WHERE THERE IS A MATCH BETWEEN THE SPECIFIED COLUMNS IN OTH THE LEFT(OR FIRST ) AND RIGHT (OR SECOND) TABLES. 
#SELECT * FROM customers INNER join orders On orders.cust_id = customers.cust_id;

#select name, SUM(amount) from customers Inner Join orders On orders.cust_id = customers.cust_id Group by name;


##left join 
#select * from customers left join orders ON orders.cust_id = customers.cust_id;
#select name,Sum(amount) from customers left join orders on orders.cust_id = customers.cust_id group by name;
#use case
#select name,IfNULL(Sum(amount),0)  as Total from customers left join orders on orders.cust_id = customers.cust_id group by name;

#right join  
#returns all rows from the right (or second) table and the matching from the left (or first table).
select * from customers Right Join orders ON orders.cust_id=customers.cust_id;