CREATE TABLE region (
region_ID int NOT NULL,
region_name varchar(25),
PRIMARY KEY (region_ID)
);
CREATE TABLE countries (
country_ID int NOT NULL,
country_name varchar(25),
region_ID int,
PRIMARY KEY (country_ID),
FOREIGN KEY (region_ID) REFERENCES region(region_ID)
);
CREATE TABLE location (
location_ID int NOT NULL,
street_address varchar(25),
postal_code varchar(12),
city varchar(30),
state_province varchar(12),
country_ID int,
PRIMARY KEY (location_ID),
FOREIGN KEY (country_ID) REFERENCES countries(country_ID)
);
CREATE TABLE departments (
DEPARTMENT_ID int NOT NULL,
DEPARTMENT_NAME varchar(30),
MANAGER_ID int,
LOCATION_ID int,
PRIMARY KEY (DEPARTMENT_ID),
FOREIGN KEY (LOCATION_ID) REFERENCES location(LOCATION_ID)
);
CREATE TABLE jobs (
JOB_ID varchar(10) NOT NULL,
JOB_TITLE varchar(35),
MIN_SALARY int,
MAX_SALARY int,
PRIMARY KEY (JOB_ID)
);
CREATE TABLE employees (
EMPLOYEE_ID int NOT NULL,
FIRST_NAME varchar(20),
LAST_NAME varchar(25),
EMAIL varchar(25) UNIQUE,
PHONE_NUMBER varchar(20),
HIRE_DATE date,
JOB_ID varchar(10),
SALARY int,
COMMISSION_PCT int,
MANAGER_ID int,
DEPARTMENT_ID int,
PRIMARY KEY (EMPLOYEE_ID),
FOREIGN KEY (JOB_ID) REFERENCES jobs(JOB_ID),
FOREIGN KEY (DEPARTMENT_ID) REFERENCES departments(DEPARTMENT_ID)
);
CREATE TABLE job_history (
EMPLOYEE_ID int NOT NULL,
START_DATE date NOT NULL,
END_DATE date,
JOB_ID varchar(10),
DEPARTMENT_ID int,
PRIMARY KEY (EMPLOYEE_ID,START_DATE),
FOREIGN KEY (EMPLOYEE_ID) REFERENCES employees(EMPLOYEE_ID),
FOREIGN KEY (DEPARTMENT_ID) REFERENCES departments(DEPARTMENT_ID),
FOREIGN KEY (JOB_ID) REFERENCES jobs(JOB_ID)
);
CREATE TABLE job_grade (
grade_level varchar(2) NOT NULL,
lowest_sal int,
highest_sal int,
PRIMARY KEY (grade_level)
);
INSERT INTO region (region_ID, region_name) VALUES(3, 'Africa');
INSERT INTO countries (country_id, country_name, region_id) Values(1,'Egypt',3);
INSERT INTO location (location_id, street_address,postal_code, city,country_id) VALUES (155,
'22 x street', '12705 ', 'Cairo',1);
INSERT INTO location (location_id, street_address,postal_code, city,country_id) VALUES (145,
'4 y street', '11806 ', 'Cairo',1);
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (1, 'IT', 123, 155);
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (2, 'sales', 124, 145);
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (3, 'marketing', 125, 135);
INSERT INTO jobs (job_id,job_title,min_salary,max_salary) VALUES ('3', 'Quality Engineer',
4000, 10000);
INSERT INTO jobs (job_id,job_title,min_salary,max_salary) VALUES ('2', 'Telesales', 2500,
15000);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number,
hiring_date, job_id, salary, commission_pct, manager_id, department_id) VALUES (12, 'Ahmed',
'Salah', 'a.salah@company.com', '01105090740', '12/3/2018', '3', 6000, 200, 123, 1);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, job_id,
salary, commission_pct, manager_id, department_id) values (13, 'Sara', 'Mostafa',
's.mostafa@company.com', '01203045760', '2', 3500, 100, 124, 2);
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, job_id,
salary, commission_pct, manager_id, department_id) values (14, 'Nada', 'Mahmoud',
'n.mahmod@company.com', '01214045760', '2', 3500, 100, 125, 3);
UPDATE jobs
Set min_salary = 5000
WHERE job_title = 'Quality Engineer';
UPDATE jobs
Set max_salary = 15000;
UPDATE employees
Set LAST_NAME = 'mohamed'
WHERE EMPLOYEE_ID = 14;
DELETE FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE
department_name = 'sales');
DELETE FROM employees
WHERE employee_id = 13;
DELETE FROM countries
WHERE country_id = 1;
DELETE FROM employees
WHERE last_name = 'Mostafa';
SELECT * FROM employees;
SELECT * FROM employees
ORDER BY salary;
SELECT * FROM employees
WHERE employee_id <= 105;
