/*
Table to hold Department details
Master table for employees
*/

CREATE TABLE Departments(
   department_id VARCHAR(5) PRIMARY KEY,
   department_name VARCHAR(20) NOT NULL,
   hq_location VARCHAR(100)
   );

INSERT INTO 'Departments' ('department_id','department_name','hq_location') VALUES ('D01', 'Accounts', 'Hyderabad');
INSERT INTO 'Departments' ('department_id','department_name','hq_location') VALUES ('D02', 'Sales', 'Delhi');
INSERT INTO 'Departments' ('department_id','department_name','hq_location') VALUES ('D03', 'Front-office', 'Mumbai');
INSERT INTO 'Departments' ('department_id','department_name','hq_location') VALUES ('D04', 'Technical', 'Mumbai');
INSERT INTO 'Departments' ('department_id','department_name','hq_location') VALUES ('D05', 'Billing', 'Delhi');

/*
Table to hold Designation details
Master table for employees
*/

CREATE TABLE Designation(
   designation_id INT PRIMARY KEY,
   designation_title VARCHAR(30) NOT NULL
   );

INSERT INTO 'Designation' ('designation_id','designation_title') VALUES (100, 'Accounts Manager');
INSERT INTO 'Designation' ('designation_id','designation_title') VALUES (110, 'Accountant');
INSERT INTO 'Designation' ('designation_id','designation_title') VALUES (101, 'Jr Accountant');
INSERT INTO 'Designation' ('designation_id','designation_title') VALUES (200, 'Sales Manager');
INSERT INTO 'Designation' ('designation_id','designation_title') VALUES (300, 'FO Manager');
INSERT INTO 'Designation' ('designation_id','designation_title') VALUES (400, 'Technical Manager');

/*
Table to hold Employees data
*/
CREATE TABLE Employees(
   employee_id INT PRIMARY KEY,
   first_name VARCHAR(200) NOT NULL,
   last_name VARCHAR(100),
   department_code VARCHAR(5),
   designation_code INT,
   salary INT NOT NULL,
   FOREIGN KEY(department_code) REFERENCES Departments(department_id),
   FOREIGN KEY(designation_code) REFERENCES Designation(designation_id)
   );

INSERT INTO 'Employees' ('employee_id','first_name','last_name', department_code,designation_code, salary) VALUES (1, 'Ram', 'K.', 'D01', 100, 50000);
INSERT INTO 'Employees' ('employee_id','first_name','last_name', department_code,designation_code, salary) VALUES (2, 'Shyam', 'P.', 'D01', 101, 500);
INSERT INTO 'Employees' ('employee_id','first_name','last_name', department_code,designation_code, salary) VALUES (3, 'Xabc', 'B.', 'D01', 110, 5000);