SQL - Introduction to JOIN and UNION Script by HadiMaster

-- Create table for Employees
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

-- Populate Employees table with sample data
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
VALUES
    (1, 'John', 'Smith', 101),
    (2, 'Jane', 'Doe', 102),
    (3, 'Michael', 'Johnson', 101),
    (4, 'Emily', 'Brown', 103),
    (5, 'David', 'Davis', 102),
    (6, 'Sarah', 'Wilson', 103),
    (7, 'James', 'Jones', 101),
    (8, 'Jennifer', 'Lee', 103),
    (9, 'Christopher', 'Anderson', 102),
    (10, 'Amanda', 'Martinez', 101),
    (11, 'Matthew', 'Taylor', 103),
    (12, 'Ashley', 'White', 102),
    (13, 'Ryan', 'Jackson', 101),
    (14, 'Jessica', 'Harris', 103),
    (15, 'Daniel', 'Thompson', 102),
    (16, 'Melissa', 'Clark', 101),
    (17, 'Kevin', 'Thomas', 103),
    (18, 'Elizabeth', 'Lewis', 102),
    (19, 'Eric', 'Walker', 101),
    (20, 'Samantha', 'Hall', 103);

-- Create table for Departments
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Populate Departments table with sample data
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
    (101, 'HR'),
    (102, 'Finance'),
    (103, 'Marketing'),
    (104, 'IT'),
    (105, 'Operations'),
    (106, 'Sales'),
    (107, 'Research'),
    (108, 'Customer Service'),
    (109, 'Legal'),
    (110, 'Administration');

-- Create table for Orders
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);

-- Populate Orders table with sample data
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
    (1, 101, '2023-01-01', 1000),
    (2, 102, '2023-01-02', 1500),
    (3, 103, '2023-01-03', 2000),
    (4, 104, '2023-01-04', 1200),
    (5, 105, '2023-01-05', 1800),
    (6, 106, '2023-01-06', 900),
    (7, 107, '2023-01-07', 1300),
    (8, 108, '2023-01-08', 1750),
    (9, 109, '2023-01-09', 1100),
    (10, 110, '2023-01-10', 1600),
    (11, 101, '2023-01-11', 1400),
    (12, 102, '2023-01-12', 1900),
    (13, 103, '2023-01-13', 2200),
    (14, 104, '2023-01-14', 1350),
    (15, 105, '2023-01-15', 1950),
    (16, 106, '2023-01-16', 950),
    (17, 107, '2023-01-17', 1200),
    (18, 108, '2023-01-18', 1650),
    (19, 109, '2023-01-19', 1000),
    (20, 110, '2023-01-20', 1500);

-- INNER JOIN
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

-- LEFT JOIN
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

-- RIGHT JOIN
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

-- FULL JOIN
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

-- SEMI JOIN
SELECT Employees.FirstName, Employees.LastName
FROM Employees
WHERE EXISTS (
    SELECT 1
    FROM Departments
    WHERE Employees.DepartmentID = Departments.DepartmentID
);

-- ANTI JOIN
SELECT Employees.FirstName, Employees.LastName
FROM Employees
WHERE NOT EXISTS (
    SELECT 1
    FROM Departments
    WHERE Employees.DepartmentID = Departments.DepartmentID
);

-- UNION and UNION ALL

-- UNION
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE TotalAmount > 1500

UNION

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate > '2023-01-10';

-- UNION ALL
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE TotalAmount > 1500

UNION ALL

SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Orders
WHERE OrderDate > '2023-01-10';