/* ============================================================
DATABASE: company_db
PURPOSE : Full SQL setup
TABLES : employees, departments, projects, customers, orders
ROWS : 10 per table
============================================================ */
-- 1️⃣ Create schema
DROP DATABASE IF EXISTS company_db;
CREATE DATABASE company_db;
USE company_db;
-- ============================================================
-- 2️⃣ Table: employees
-- ============================================================
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE,
status VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO employees (first_name, last_name, email, phone, department, salary, hire_date, status) VALUES
('John','Doe','john@ex.com','111-111','IT',70000,'2020-01-01','ACTIVE'),
('Jane','Smith','jane@ex.com','111-112','HR',65000,'2020-02-01','ACTIVE'),
('Bob','Lee','bob@ex.com','111-113','IT',72000,'2020-03-01','ACTIVE'),
('Alice','Kim','alice@ex.com','111-114','FIN',80000,'2020-04-01','ACTIVE'),
('Tom','Fox','tom@ex.com','111-115','OPS',60000,'2020-05-01','ACTIVE'),
('Eva','Ray','eva@ex.com','111-116','HR',64000,'2020-06-01','ACTIVE'),
('Max','Hill','max@ex.com','111-117','IT',75000,'2020-07-01','ACTIVE'),
('Nina','Park','nina@ex.com','111-118','FIN',82000,'2020-08-01','ACTIVE'),
('Sam','Cole','sam@ex.com','111-119','OPS',59000,'2020-09-01','ACTIVE'),
('Leo','Wang','leo@ex.com','111-120','IT',78000,'2020-10-01','ACTIVE');
-- ============================================================
-- 3️⃣ Table: departments
-- ============================================================
CREATE TABLE departments (
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50),
manager VARCHAR(100),
location VARCHAR(50),
budget DECIMAL(12,2),
phone VARCHAR(20),
email VARCHAR(100),
floor INT,
active BOOLEAN,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO departments (dept_name, manager, location, budget, phone, email, floor, active) VALUES
('IT','John Doe','NY',500000,'222-111','it@ex.com',5,1),
('HR','Jane Smith','NY',200000,'222-112','hr@ex.com',3,1),
('FIN','Alice Kim','NY',400000,'222-113','fin@ex.com',6,1),
('OPS','Tom Fox','TX',300000,'222-114','ops@ex.com',2,1),
('MKT','Sara Lane','CA',250000,'222-115','mkt@ex.com',4,1),
('QA','Paul Ray','TX',150000,'222-116','qa@ex.com',1,1),
('ENG','Max Hill','CA',600000,'222-117','eng@ex.com',7,1),
('SUP','Nina Park','NY',180000,'222-118','sup@ex.com',2,1),
('ADM','Sam Cole','TX',120000,'222-119','adm@ex.com',1,1),
('RND','Leo Wang','CA',700000,'222-120','rnd@ex.com',8,1);
-- ============================================================
-- 4️⃣ Table: projects
-- ============================================================
CREATE TABLE projects (
project_id INT PRIMARY KEY AUTO_INCREMENT,
project_name VARCHAR(100),
owner VARCHAR(100),
start_date DATE,
end_date DATE,
budget DECIMAL(12,2),
status VARCHAR(20),
priority INT,
department VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO projects (project_name, owner, start_date, end_date, budget, status, priority, department) VALUES
('Website','John','2021-01-01','2021-06-01',100000,'DONE',1,'IT'),
('HR Tool','Jane','2021-02-01','2021-07-01',80000,'DONE',2,'HR'),
('Finance App','Alice','2021-03-01','2021-09-01',120000,'ACTIVE',1,'FIN'),
('Ops Dash','Tom','2021-04-01','2021-10-01',90000,'ACTIVE',2,'OPS'),
('Marketing','Sara','2021-05-01','2021-11-01',70000,'ACTIVE',3,'MKT'),
('QA Auto','Paul','2021-06-01','2021-12-01',60000,'ACTIVE',2,'QA'),
('Engine','Max','2021-07-01','2022-01-01',150000,'ACTIVE',1,'ENG'),
('Support','Nina','2021-08-01','2022-02-01',50000,'ACTIVE',3,'SUP'),
('AdminSys','Sam','2021-09-01','2022-03-01',40000,'ACTIVE',3,'ADM'),
('Research','Leo','2021-10-01','2022-04-01',200000,'ACTIVE',1,'RND');
-- ============================================================
-- 5️⃣ Table: customers
-- ============================================================
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
phone VARCHAR(20),
city VARCHAR(50),
country VARCHAR(50),
status VARCHAR(20),
signup_date DATE,
loyalty_points INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO customers (name, email, phone, city, country, status, signup_date, loyalty_points) VALUES
('Acme','a@ex.com','333-111','NY','USA','ACTIVE','2022-01-01',100),
('Beta','b@ex.com','333-112','LA','USA','ACTIVE','2022-01-02',200),
('Core','c@ex.com','333-113','TX','USA','ACTIVE','2022-01-03',150),
('Delta','d@ex.com','333-114','SF','USA','ACTIVE','2022-01-04',90),
('Echo','e@ex.com','333-115','NY','USA','ACTIVE','2022-01-05',120),
('Fox','f@ex.com','333-116','LA','USA','ACTIVE','2022-01-06',300),
('Gamma','g@ex.com','333-117','TX','USA','ACTIVE','2022-01-07',250),
('Helix','h@ex.com','333-118','SF','USA','ACTIVE','2022-01-08',80),
('Ion','i@ex.com','333-119','NY','USA','ACTIVE','2022-01-09',60),
('Jolt','j@ex.com','333-120','LA','USA','ACTIVE','2022-01-10',500);
-- ============================================================
-- 6️⃣ Table: orders
-- ============================================================
CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_name VARCHAR(100),
product VARCHAR(100),
quantity INT,
price DECIMAL(10,2),
total DECIMAL(10,2),
order_date DATE,
status VARCHAR(20),
payment_method VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO orders (customer_name, product, quantity, price, total, order_date, status, payment_method) VALUES
('Acme','Laptop',1,1200,1200,'2023-01-01','PAID','CARD'),
('Beta','Mouse',2,25,50,'2023-01-02','PAID','CARD'),
('Core','Keyboard',1,80,80,'2023-01-03','PAID','PAYPAL'),
('Delta','Monitor',2,300,600,'2023-01-04','PAID','CARD'),
('Echo','Chair',1,150,150,'2023-01-05','PAID','CASH'),
('Fox','Desk',1,400,400,'2023-01-06','PAID','CARD'),
('Gamma','Dock',3,90,270,'2023-01-07','PAID','PAYPAL'),
('Helix','Headset',2,60,120,'2023-01-08','PAID','CARD'),
('Ion','Webcam',1,110,110,'2023-01-09','PAID','CARD'),
('Jolt','Tablet',1,500,500,'2023-01-10','PAID','CARD');
