-- ==========================================
-- SQL INTERVIEW PRACTICE DATABASE
-- Covers all 20 SQL interview questions
-- ==========================================

-- 1. EMPLOYEES TABLE
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT,
    email VARCHAR(100),
    hire_date DATE
);

INSERT INTO Employees VALUES
(1, 'Rahul', 'Engineering', 90000,  NULL, 'rahul@gmail.com', '2023-01-10'),
(2, 'Amit', 'Engineering', 75000,   1, 'amit@gmail.com', '2023-03-15'),
(3, 'Priya', 'Engineering', 75000,  1, 'priya@gmail.com', '2023-04-20'),
(4, 'Sneha', 'HR', 60000,           1, 'sneha@gmail.com', '2023-05-10'),
(5, 'Karan', 'HR', 50000,           4, 'karan@gmail.com', '2023-06-12'),
(6, 'Neha', 'Sales', 85000,         1, 'neha@gmail.com', '2023-07-18'),
(7, 'Vikram', 'Sales', 45000,       6, 'vikram@gmail.com', '2023-08-22'),
(8, 'Anjali', 'Engineering', 95000, 1, 'anjali@gmail.com', '2023-09-11'),
(9, 'Rohan', 'Finance', 70000,      NULL, NULL, '2023-10-01'),
(10, 'Pooja', 'Finance', 70000,     9, 'pooja@gmail.com', '2023-11-05');


-- 2. CUSTOMERS TABLE
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100),
    city VARCHAR(50)
);

INSERT INTO Customers VALUES
(101, 'Arjun', 'arjun@gmail.com', 'Pune'),
(102, 'Meera', 'meera@gmail.com', 'Mumbai'),
(103, 'Ravi', 'ravi@gmail.com', 'Pune'),
(104, 'Simran', 'simran@gmail.com', 'Delhi'),
(105, 'Dev', NULL, 'Bangalore'),
(106, 'Kavya', 'kavya@gmail.com', 'Hyderabad');


-- 3. ORDERS TABLE
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    status VARCHAR(30),

    FOREIGN KEY (customer_id)
    REFERENCES Customers(customer_id)
);

INSERT INTO Orders VALUES
(1001, 101, '2024-01-10', 5000, 'Delivered'),
(1002, 101, '2024-01-15', 3000, 'Delivered'),
(1003, 102, '2024-02-01', 7000, 'Pending'),
(1004, 102, '2024-02-10', 9000, 'Delivered'),
(1005, 103, '2024-03-05', 4000, 'Cancelled'),
(1006, 103, '2024-03-15', 6000, 'Delivered'),
(1007, 104, '2024-04-01', 12000, 'Delivered'),
(1008, 104, '2024-04-10', 2000, 'Pending');


-- 4. USER LOGINS TABLE
CREATE TABLE User_Logins (
    login_id INT PRIMARY KEY,
    user_id INT,
    login_date DATE
);

INSERT INTO User_Logins VALUES
(1, 101, '2024-01-01'),
(2, 101, '2024-01-05'),
(3, 101, '2024-01-10'),
(4, 102, '2024-01-02'),
(5, 102, '2024-01-08'),
(6, 103, '2024-02-01'),
(7, 103, '2024-02-15');


-- 5. USER EMAILS TABLE
-- Contains duplicate emails for Q20
CREATE TABLE User_Emails (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at DATE
);

INSERT INTO User_Emails VALUES
(1, 'Rahul', 'rahul@gmail.com', '2024-01-01'),
(2, 'Rahul Updated', 'rahul@gmail.com', '2024-02-01'),
(3, 'Amit', 'amit@gmail.com', '2024-01-10'),
(4, 'Amit Updated', 'amit@gmail.com', '2024-03-10'),
(5, 'Priya', 'priya@gmail.com', '2024-02-15'),
(6, 'Sneha', 'sneha@gmail.com', '2024-03-01'),
(7, 'Sneha Updated', 'sneha@gmail.com', '2024-04-01');


-- 6. NULL / COALESCE PRACTICE
CREATE TABLE Employee_Benefits (
    employee_id INT,
    bonus DECIMAL(10,2),
    allowance DECIMAL(10,2)
);

INSERT INTO Employee_Benefits VALUES
(1, 10000, 5000),
(2, NULL, 3000),
(3, 5000, NULL),
(4, NULL, NULL);


-- 7. DIVISION / NULLIF PRACTICE
CREATE TABLE Sales_Performance (
    employee_id INT,
    sales_amount DECIMAL(10,2),
    target_amount DECIMAL(10,2)
);

INSERT INTO Sales_Performance VALUES
(1, 100000, 80000),
(2, 75000, 0),
(3, 50000, 60000),
(4, 90000, 0);


-- ==========================================
-- SETUP COMPLETED
-- ==========================================

-- Verify tables
SELECT * FROM Employees;
SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM User_Logins;
SELECT * FROM User_Emails;
SELECT * FROM Employee_Benefits;
SELECT * FROM Sales_Performance;
select * from orders;