-- SQL-Task5 -- SQL JOINS --

-- 1. Create Customers Table -- CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, CustomerName NVARCHAR(100) NOT NULL, Country NVARCHAR(50) );

-- 2. Create Orders Table -- CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE, CustomerID INT, Amount DECIMAL(10,2), FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );

-- 3. Insert Sample Data -- INSERT INTO Customers (CustomerID, CustomerName, Country) VALUES (1, 'Alice', 'USA'), (2, 'Bob', 'Canada'), (3, 'Charlie', 'UK'), (4, 'David', 'Australia');

INSERT INTO Orders (OrderID, OrderDate, CustomerID, Amount) VALUES (101, '2025-08-01', 1, 250.00), (102, '2025-08-03', 1, 450.00), (103, '2025-08-05', 2, 300.00), (104, '2025-08-07', 5, 500.00); -- CustomerID 5 doesn't exist in Customers

-- 4. INNER JOIN - Customers with Orders -- SELECT c.CustomerID, c.CustomerName, o.OrderID, o.Amount FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 5. LEFT JOIN - All Customers, with orders if available -- SELECT c.CustomerID, c.CustomerName, o.OrderID, o.Amount FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 6. RIGHT JOIN - All Orders, with customer info if available -- SELECT c.CustomerID, c.CustomerName, o.OrderID, o.Amount FROM Customers c RIGHT JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 7. FULL OUTER JOIN - All Customers and Orders -- SELECT c.CustomerID, c.CustomerName, o.OrderID, o.Amount FROM Customers c FULL OUTER JOIN Orders o ON c.CustomerID = o.CustomerID;