-- ==========================================================
-- Sample Dataset for SQL Interview Practice (MySQL syntax)
-- Tables: Department, Employee, Project, Project_Assignment
-- ==========================================================

DROP TABLE IF EXISTS Project_Assignment;
DROP TABLE IF EXISTS Project;
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;

-- ---------------- Department ----------------
CREATE TABLE Department (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

INSERT INTO Department (id, department_name) VALUES
(1, 'Engineering'),
(2, 'Sales'),
(3, 'HR'),
(4, 'Marketing');
-- Note: no department with id 5 -> used to test employees with no matching department

-- ---------------- Employee ----------------
CREATE TABLE Employee (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    name VARCHAR(100),
    email VARCHAR(100),
    salary DECIMAL(10,2),
    department_id INT,
    manager_id INT,
    hire_date DATE
);

INSERT INTO Employee (id, first_name, last_name, name, email, salary, department_id, manager_id, hire_date) VALUES
(1,  'Amit',   'Sharma',   'Amit Sharma',   '[email protected]',   95000, 1, NULL, '2019-01-15'),
(2,  'Priya',  'Verma',    'Priya Verma',   '[email protected]',   72000, 1, 1,    '2020-03-10'),
(3,  'Rahul',  'Nair',     'Rahul Nair',    '[email protected]',    68000, 1, 1,    '2021-07-22'),
(4,  'Anjali', 'Rao',      'Anjali Rao',    '[email protected]',    68000, 1, 1,    '2022-05-01'),
(5,  'Suresh', 'Kumar',    'Suresh Kumar',  '[email protected]',  60000, 2, NULL, '2018-11-05'),
(6,  'Neha',   'Gupta',    'Neha Gupta',    '[email protected]',    55000, 2, 5,    '2021-02-18'),
(7,  'Vikram', 'Singh',    'Vikram Singh',  '[email protected]',  61000, 2, 5,    '2020-09-09'),
(8,  'Anita',  'Desai',    'Anita Desai',   '[email protected]',   50000, 3, NULL, '2019-06-30'),
(9,  'Karan',  'Malhotra', 'Karan Malhotra','[email protected]',48000, 3, 8,    '2022-01-12'),
(10, 'Sneha',  'Joshi',    'Sneha Joshi',   '[email protected]',   47000, 3, 8,    '2021-10-04'),
(11, 'Arjun',  'Mehta',    'Arjun Mehta',   '[email protected]',   58000, 4, NULL, '2018-04-17'),
(12, 'Divya',  'Iyer',     'Divya Iyer',    '[email protected]',    52000, 4, 11,   '2020-12-25'),
(13, 'Rohan',  'Kapoor',   'Rohan Kapoor',  '[email protected]',  52000, 4, 11,   '2023-03-14'),
(14, 'Pooja',  'Bhatt',    'Pooja Bhatt',   '[email protected]',   45000, NULL, NULL, '2022-08-20'),
(15, 'Manish', 'Chopra',   'Manish Chopra', '[email protected]', 99000, 1, NULL, '2017-05-09'),
(16, 'Ritu',   'Saxena',   'Ritu Saxena',   '[email protected]',   72000, 1, 15,   '2021-11-11'),
(17, 'Deepak', 'Yadav',    'Deepak Yadav',  '[email protected]',  67000, 2, 5,    '2020-06-06'),
(18, 'Kavita', 'Reddy',    'Kavita Reddy',  '[email protected]',  46000, 3, 8,    '2019-09-23'),
(19, 'Ajay',   'Pandey',   'Ajay Pandey',   '[email protected]',   53000, 4, 11,   '2022-02-28'),
(20, 'Meera',  'Nambiar',  'Meera Nambiar', '[email protected]', 41000, 5, NULL, '2023-01-01');
-- Note: employee 20 has department_id 5 which does NOT exist in Department table
--       -> useful for LEFT JOIN / orphan-record practice
-- Note: employees 3 & 4 share salary 68000, and 12 & 13 share salary 52000
--       -> useful for duplicate/"same salary" practice
-- Note: hire_date values are intentionally spread across years and some
--       recent dates; replace with real recent dates to test "last 30 days"

-- ---------------- Project ----------------
CREATE TABLE Project (
    id INT PRIMARY KEY,
    project_name VARCHAR(100),
    start_date DATE,
    end_date DATE
);

INSERT INTO Project (id, project_name, start_date, end_date) VALUES
(1, 'Billing System Revamp',    '2023-01-10', '2023-06-30'),
(2, 'Customer Portal',          '2023-02-01', '2023-08-15'),
(3, 'Internal HR Tool',         '2023-03-05', '2023-05-20'),
(4, 'Marketing Analytics Dashboard', '2023-04-01', '2023-09-01');

-- ---------------- Project_Assignment ----------------
CREATE TABLE Project_Assignment (
    emp_id INT,
    project_id INT
);

INSERT INTO Project_Assignment (emp_id, project_id) VALUES
(1, 1), (2, 1), (3, 1),
(5, 2), (6, 2),
(8, 3), (9, 3),
(11, 4), (12, 4);
-- Employees 4,7,10,13,14,15,16,17,18,19,20 are intentionally NOT assigned
-- to any project -> useful for "employees with no project" practice