-- Fireblaze SQL Interview Practice Dataset
-- Works on MySQL 8+, PostgreSQL 13+, SQLite 3.25+ and SQL Server (change DATE literals if needed)
-- 4 tables: departments, employees, customers, orders

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL UNIQUE,
  city      VARCHAR(30)
);

CREATE TABLE employees (
  emp_id     INT PRIMARY KEY,
  emp_name   VARCHAR(50) NOT NULL,
  dept_id    INT REFERENCES departments(dept_id),
  manager_id INT REFERENCES employees(emp_id),
  salary     INT,              -- monthly salary in INR
  hire_date  DATE NOT NULL,
  city       VARCHAR(30)
);

CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL,
  city          VARCHAR(30),
  signup_date   DATE
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  order_date  DATE NOT NULL,
  amount      INT NOT NULL,     -- order value in INR
  status      VARCHAR(20)
);

INSERT INTO departments VALUES
(10, 'Analytics',   'Nagpur'),
(20, 'Engineering', 'Pune'),
(30, 'Sales',       'Nagpur'),
(40, 'HR',          'Mumbai'),
(50, 'Research',    'Nagpur');   -- no employees yet

INSERT INTO employees VALUES
(1,  'Anita Deshmukh', 20, NULL, 210000, '2017-04-01', 'Pune'),
(2,  'Rahul Patil',    10, 1,    120000, '2018-06-15', 'Nagpur'),
(3,  'Sneha Kulkarni', 10, 2,     65000, '2021-01-10', 'Nagpur'),
(4,  'Vikram Joshi',   10, 2,     65000, '2022-03-05', 'Nagpur'),
(5,  'Pooja Wankhede', 10, 2,     48000, '2024-07-01', 'Nagpur'),
(6,  'Amit Shinde',    20, 1,     95000, '2019-09-20', 'Pune'),
(7,  'Neha Bhosale',   20, 6,    105000, '2020-11-11', 'Pune'),
(8,  'Karan Mehta',    30, 1,     55000, '2023-02-14', 'Nagpur'),
(9,  'Riya Gawande',   30, 8,     42000, '2025-05-19', 'Nagpur'),
(10, 'Sagar Thakre',   40, 1,     NULL,  '2026-08-01', 'Mumbai'),
(11, 'Mayur Raut',     NULL, 2,   38000, '2026-09-15', 'Nagpur');  -- not yet assigned to a department

INSERT INTO customers VALUES
(101, 'Orange City Traders', 'Nagpur', '2025-01-05'),
(102, 'Deccan Retail',       'Pune',   '2025-02-11'),
(103, 'Vidarbha Agro',       'Nagpur', '2025-03-20'),
(104, 'Konkan Foods',        'Mumbai', '2025-06-02'),
(105, 'Sitabuldi Mobiles',   'Nagpur', '2025-09-30'),
(106, 'Wardha Textiles',     'Wardha', '2026-01-15');  -- never ordered

INSERT INTO orders VALUES
(1001, 101, '2026-01-04', 12000, 'Delivered'),
(1002, 102, '2026-01-09',  8500, 'Delivered'),
(1003, 101, '2026-01-22',  4300, 'Cancelled'),
(1004, 103, '2026-02-02', 15600, 'Delivered'),
(1005, 101, '2026-02-17',  9900, 'Delivered'),
(1006, 104, '2026-02-25',  7200, 'Delivered'),
(1007, 102, '2026-03-03', 11000, 'Delivered'),
(1008, 101, '2026-03-12',  6100, 'Delivered'),
(1009, 105, '2026-03-28',  3200, 'Returned'),
(1010, 103, '2026-04-06', 18400, 'Delivered'),
(1011, 102, '2026-04-15',  5600, 'Delivered'),
(1012, 102, '2026-04-15',  5600, 'Delivered'),   -- duplicate of 1011 (same customer, date, amount)
(1013, 104, '2026-05-01',  9800, 'Delivered'),
(1014, 101, '2026-05-09', 14200, 'Delivered');