8

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255),
    City VARCHAR(255),
    Country VARCHAR(255)
);

-- Inserting sample data into Customers table
INSERT INTO Customers VALUES(1, 'John Doe', 'New York', 'USA');
INSERT INTO Customers VALUES(2, 'Jane Smith', 'Los Angeles', 'USA');
INSERT INTO Customers VALUES(3, 'Alice Johnson', 'London', 'UK');
INSERT INTO Customers VALUES(4, 'Bob Brown', 'Sydney', 'Australia');
INSERT INTO Customers VALUES(5, 'Emma Lee', 'Toronto', 'Canada');
SELECT *FROM CUSTOMERS;

-- Inserting sample data into Orders table
INSERT INTO Orders VALUES(101, 1,DATE '2024-01-01', 100.50);
INSERT INTO Orders VALUES(102, 1,DATE  '2024-01-05', 200.75);
INSERT INTO Orders VALUES(103, 2,DATE  '2024-01-10', 150.00);
INSERT INTO Orders VALUES(104, 3,DATE  '2024-01-15', 300.25);
INSERT INTO Orders VALUES(105, 4,DATE '2024-01-20', 50.50);
SELECT *FROM ORDERS;

--------------------------------------------------------------------------------------------------------------------------------------

Cross Join

SELECT *
FROM Orders
CROSS JOIN Customers;

Self Join

SELECT c1.CustomerName AS Customer, c2.CustomerName AS ReferredBy
FROM Customers c1
INNER JOIN Customers c2 ON c1.CustomerID <> c2.CustomerID;

Inner Join

SELECT o.OrderID, c.CustomerName, o.TotalAmount
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

Natural Join

SELECT OrderID, CustomerName, TotalAmount
FROM Orders
NATURAL JOIN Customers;

Left Outer Join

SELECT o.OrderID, c.CustomerName, o.TotalAmount
FROM Orders o
LEFT OUTER JOIN Customers c ON o.CustomerID = c.CustomerID;

Right Outer Join

SELECT o.OrderID, c.CustomerName, o.TotalAmount
FROM Orders o
RIGHT OUTER JOIN Customers c ON o.CustomerID = c.CustomerID;

Full Outer join

SELECT o.OrderID, c.CustomerName, o.TotalAmount
FROM Orders o
FULL OUTER JOIN Customers c ON o.CustomerID = c.CustomerID;