-- Fireblaze SQL practice dataset (4 tables, 36 rows)
-- Runs on MySQL 8, PostgreSQL 13+ and SQLite 3.39+
-- Used in: SQL Joins Explained and SQL Interview Questions (fireblazeaischool.in)

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(50)
);

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,
  hire_date  DATE
);

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

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT REFERENCES customers(customer_id),
  order_date  DATE,
  amount      INT,
  status      VARCHAR(20)
);

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

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

INSERT INTO customers VALUES
(101, 'Orange City Traders', 'Nagpur'),
(102, 'Deccan Retail',       'Pune'),
(103, 'Vidarbha Agro',       'Nagpur'),
(104, 'Konkan Foods',        'Mumbai'),
(105, 'Sitabuldi Mobiles',   'Nagpur'),
(106, 'Wardha Textiles',     'Wardha');

INSERT INTO orders VALUES
(1001, 101, '2026-01-04', 12000, 'Delivered'),
(1002, 102, '2026-01-09', 8500,  'Delivered'),
(1003, 101, '2026-01-20', 4300,  'Cancelled'),
(1004, 103, '2026-02-02', 15600, 'Delivered'),
(1005, 101, '2026-02-14', 9900,  'Delivered'),
(1006, 104, '2026-02-25', 7200,  'Delivered'),
(1007, 102, '2026-03-05', 11000, 'Delivered'),
(1008, 101, '2026-03-18', 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'),
(1013, 104, '2026-05-08', 9800,  'Delivered'),
(1014, 101, '2026-05-20', 14200, 'Delivered');
