SQL Joins - INNER JOIN, LEFT JOIN, RIGHT JOIN - Day19
@LearnWithRD0529
-------scripts-------------
-- Create the 'accounts' table
CREATE TABLE accounts (
account_id INT PRIMARY KEY,
customer_id INT,
order_id INT,
balance INT,
company VARCHAR(50)
);
-- Create the 'customers' table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
-- Create the 'orders' table
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 sample data into the 'customers' table
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES(1, 'John', 'Doe', '[email protected]');
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES(2, 'Jane', 'Smith', '[email protected]');
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES(3, 'Bob', 'Johnson', '[email protected]');
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES(4, 'Bob', 'Johnson', '[email protected]');
-- Insert sample data into the 'orders' table
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES(101, 1, '15-JAN-2023', 100.00);
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES(102, 1, '20-FEB-2023', 75.50);
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES(103, 2, '10-JAN-2023', 200.25);
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES(104, 3, '05-MAR-2023', 50.75);
-- Insert sample data into the 'accounts' table
INSERT INTO accounts (account_id, customer_id, order_id, balance, company)
VALUES (201, 1, 101, 5000, 'ABC Company');
INSERT INTO accounts (account_id, customer_id, order_id, balance, company)
VALUES (202, 2, 102, 7500, 'XYZ Corporation');
INSERT INTO accounts (account_id, customer_id, order_id, balance, company)
VALUES (203, 3, 103, 3000, 'LMN Ltd.');
INSERT INTO accounts (account_id, customer_id, order_id, balance, company)
VALUES (204, 4, 104, 6000, 'PQR Industries');
INSERT INTO accounts (account_id, customer_id, order_id, balance, company)
VALUES (205, 5, 105, 4500, 'XYZ Corporation');
INSERT INTO accounts (account_id, customer_id, order_id, balance, company)
VALUES (206, 6, 106, 8000, 'ABC Company');
INSERT INTO accounts (account_id, customer_id, order_id, balance, company)
VALUES (207, 7, 107, 3500, 'JKL Enterprises');