3-KEYS_Explained.sql

-- Explanation of keys:
-- PRIMARY KEY: Ensures each record in a table is unique and not NULL. Used to uniquely identify rows (e.g., CustomerID, OrderID, ProductID).
-- FOREIGN KEY: Establishes a relationship between two tables by referencing the PRIMARY KEY in another table. Helps maintain referential integrity (e.g., CustomerID in orders references CustomerID in customers).
-- IDENTITY: Automatically generates unique values for a column, often used for PRIMARY KEY fields.
-- NOT NULL: Ensures a column cannot contain NULL values, enforcing data presence.

-- Benefits of keys:
-- 1. Ensures data integrity by avoiding duplicate or NULL values in key columns.
-- 2. Establishes relationships between tables, enabling meaningful JOINs and queries.
-- 3. Facilitates indexing, which improves query performance.
-- 4. Enforces referential integrity, ensuring consistency between related tables.

-- Create a new database
CREATE DATABASE Sales;

-- Use the newly created database
USE Sales;

-- Create the customers table
-- PRIMARY KEY uniquely identifies each customer in the table
CREATE TABLE customers (
    CustomerID INT NOT NULL PRIMARY KEY, 
    FName NCHAR(100), 
    SName NCHAR(100), 
    Address NCHAR(100) 
);
-- Insert data into customers table
INSERT INTO customers (CustomerID, FName, SName, Address)
VALUES
(1, 'Ahmed', 'Salem', 'Cairo'),
(2, 'Alaa', 'Nabil', 'Cairo'),
(3, 'Mona', 'Samy', 'Alex'),
(4, 'Noha', 'Mohamed', 'Ast'),
(5, 'Mahmoud', 'Ahmed', 'Giza');

-- Create the orders table
-- PRIMARY KEY uniquely identifies each order in the table
-- FOREIGN KEY establishes a relationship between tables (CustomerID in orders references CustomerID in customers)
CREATE TABLE orders (
    OrderID INT NOT NULL PRIMARY KEY, 
    OrderDate DATETIME, 
    CustomerID INT, 
    FOREIGN KEY (CustomerID) REFERENCES customers(CustomerID) 
);

-- Insert data into orders table
INSERT INTO orders (OrderID, OrderDate, CustomerID) VALUES
(1, '2023-02-03', 1),
(2, '2023-04-05', 1),
(3, '2023-01-01', 2),
(4, '2023-08-12', 3),
(5, '2023-03-09', 4),
(6, '2023-04-05', 4),
(7, '2023-09-01', 5),
(8, '2023-10-02', 3),
(9, '2023-03-02', 1);

-- Create the productt table
-- IDENTITY automatically generates unique values starting from 5, incrementing by 1
CREATE TABLE productt (
    ProductID INT NOT NULL PRIMARY KEY IDENTITY(5,1), 
    ProductName NCHAR(100), 
    OrderID INT, 
    FOREIGN KEY (OrderID) REFERENCES orders(OrderID) 
);

-- Insert data into productt table
-- ProductID is not included because it is auto-generated by the IDENTITY property
INSERT INTO productt (ProductName, OrderID) VALUES
('Product1', 1),
('Product2', 1),
('Product3', 2),
('Product4', 2),
('Product5', 3),
('Product6', 3),
('Product7', 3),
('Product8', 4),
('Product9', 4),
('Product10', 5),
('Product11', 6),
('Product12', 7),
('Product13', 8),
('Product14', 8),
('Product15', 9),
('Product16', 7),
('Product17', 6),
('Product18', 5),
('Product19', 5);


-- Select data from related tables using JOINs
SELECT *
FROM customers
JOIN orders
ON customers.CustomerID = orders.CustomerID
JOIN productt
ON productt.OrderID = orders.OrderID;


-- When using JOIN between different tables, all columns from the involved tables will be included in the result.
-- This causes columns like OrderID, CustomerID, and ProductID to appear multiple times because they exist in more than one table.
-- To resolve this issue, we can specify only the necessary columns from each table using qualified names (table_name.column_name).
-- This avoids column duplication in the result.
SELECT
    customers.CustomerID,
    customers.FName,
    customers.SName,
    customers.Address,
    orders.OrderID,
    orders.OrderDate,
    productt.ProductID,
    productt.ProductName
FROM customers
JOIN orders ON customers.CustomerID = orders.CustomerID
JOIN productt ON productt.OrderID = orders.OrderID;