ADS_2_SET_JOIN

ORACLE

-- Create tables
CREATE TABLE Cust_Info(
 CustomerID INT PRIMARY KEY,
 CustomerName VARCHAR2(50),
 LastName VARCHAR2(50),
 Country VARCHAR2(50),
 Age INT,
 Phone VARCHAR2(15)
);

drop table Cust_Info;

-- Insert data into tables
-- Insert data into Cust_Info
INSERT INTO Cust_Info (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1001, 'John', 'Doe', 'USA', 30, '1111111111');

INSERT INTO Cust_Info (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1002, 'Jane', 'Smith', 'Canada', 25, '2222222222');

INSERT INTO Cust_Info (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1003, 'Alice', 'Johnson', 'UK', 28, '3333333333');

INSERT INTO Cust_Info (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1004, 'Bob', 'Brown', 'USA', 35, '4444444444');

-- UNION
SELECT CustomerID, CustomerName, Country FROM Cust_Info
UNION
SELECT CustomerID, CustomerName, Country FROM Customer_Other;

-- UNION ALL
SELECT CustomerID, CustomerName, Country FROM Cust_Info
UNION ALL
SELECT CustomerID, CustomerName, Country FROM Customer_Other;

-- INTERSECT
SELECT CustomerID, CustomerName, Country FROM Cust_Info
INTERSECT
SELECT CustomerID, CustomerName, Country FROM Customer_Other;

-- MINUS
SELECT CustomerID, CustomerName, Country FROM Cust_Info
MINUS
SELECT CustomerID, CustomerName, Country FROM Customer_Other;

-- Create Orders table
CREATE TABLE Orders (
 OrderID INT PRIMARY KEY,
 CustomerID INT,
 Product VARCHAR2(100),
 FOREIGN KEY (CustomerID) REFERENCES Cust_Info(CustomerID)
);

-- Drop table Orders
DROP TABLE Orders;

-- Insert data into Orders table
INSERT INTO Orders (OrderID, CustomerID, Product)
VALUES (2003, 1003, 'Headphones');

INSERT INTO Orders (OrderID, CustomerID, Product)
VALUES (2001, 1001, 'Laptop');

INSERT INTO Orders (OrderID, CustomerID, Product)
VALUES (2002, 1002, 'Smartphone');


-- INNER JOIN
SELECT Cust_Info.CustomerID, Cust_Info.CustomerName, Orders.Product
FROM Cust_Info
INNER JOIN Orders ON Cust_Info.CustomerID = Orders.CustomerID;

-- LEFT JOIN
SELECT Cust_Info.CustomerID, Cust_Info.CustomerName, Orders.Product
FROM Cust_Info
LEFT JOIN Orders ON Cust_Info.CustomerID = Orders.CustomerID;

-- RIGHT JOIN
SELECT Cust_Info.CustomerID, Cust_Info.CustomerName, Orders.Product
FROM Cust_Info
RIGHT JOIN Orders ON Cust_Info.CustomerID = Orders.CustomerID;

-- FULL OUTER JOIN
SELECT Cust_Info.CustomerID, Cust_Info.CustomerName, Orders.OrderID
FROM Cust_Info
FULL OUTER JOIN Orders ON Cust_Info.CustomerID = Orders.CustomerID;

-- CROSS JOIN
SELECT Cust_Info.CustomerID, Cust_Info.CustomerName, Orders.OrderID
FROM Cust_Info
CROSS JOIN Orders;