LxbSzx7x

1use kadry;
2drop table if exists regions;
3CREATE TABLE regions (
4    region_id INT(11),
5    region_name VARCHAR(25)
6);
7drop table if exists countries;
8CREATE TABLE countries (
9    country_id CHAR(2),
10    country_name VARCHAR(40),
11    region_id INT(11) NOT NULL
12);
13drop table if exists locations;
14CREATE TABLE locations (
15    location_id INT(11),
16    street_address VARCHAR(40),
17    postal_code VARCHAR(12),
18    city VARCHAR(30) NOT NULL,
19    state_provine VARCHAR(25),
20    country_id CHAR(2) NOT NULL
21);
22drop table if exists departments;
23CREATE TABLE departments (
24    department_id INT(11),
25    department_name VARCHAR(30) NOT NULL,
26    manager_id INT(11),
27    location_id INT(11)
28);
29drop table if exists employees;
30CREATE TABLE employees (
31    employee_id INT(11),
32    first_name VARCHAR(20),
33    last_name VARCHAR(25) NOT NULL,
34    email VARCHAR(25) NOT NULL,
35    phone_number VARCHAR(20),
36    hire_date DATE NOT NULL,
37    job_id VARCHAR(10) NOT NULL,
38    salary DECIMAL(8 , 2 ) NOT NULL,
39    commission_pct DECIMAL(2 , 2 ),
40    manager_id INT(11),
41    department_id INT(11)
42);
43drop table if exists job_history;
44CREATE TABLE job_history (
45    employee_id INT(11) NOT NULL,
46    start_date DATE NOT NULL,
47    end_dane DATE NOT NULL,
48    job_id VARCHAR(10) NOT NULL,
49    department_id INT(11) NOT NULL
50);
51drop table if exists jobs;
52CREATE TABLE jobs (
53    job_id VARCHAR(10),
54    job_title VARCHAR(35) NOT NULL,
55    min_salary DECIMAL(8 , 0 ),
56    max_salary DECIMAL(8 , 0 )
57)
58
59ALTER TABLE regions ADD PRIMARY KEY (region_id);
60ALTER TABLE countries ADD PRIMARY KEY (country_id);
61ALTER TABLE locations ADD PRIMARY KEY (location_id);
62ALTER TABLE departments ADD PRIMARY KEY (department_id);
63ALTER TABLE employees ADD PRIMARY KEY (employee_id);
64ALTER TABLE jobs ADD PRIMARY KEY (job_id);
65
66
67ALTER TABLE countries ADD FOREIGN KEY (region_id) REFERENCES regions(region_id); 
68ALTER TABLE locations ADD FOREIGN KEY (country_id) REFERENCES countries(country_id); 
69ALTER TABLE departments ADD FOREIGN KEY (location_id) REFERENCES locations(location_id); 
70ALTER TABLE job_history ADD FOREIGN KEY (employee_id) REFERENCES employees(employee_id); 
71ALTER TABLE job_history ADD FOREIGN KEY (department_id) REFERENCES departments(department_id); 
72ALTER TABLE job_history ADD FOREIGN KEY (job_id) REFERENCES jobs(job_id); 
73ALTER TABLE employees ADD FOREIGN KEY (job_id) REFERENCES jobs(job_id); 
74ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES departments(department_id); 
75ALTER TABLE employees ADD FOREIGN KEY (manager_id) REFERENCES employees(employee_id); 
76
77INSERT INTO regions VALUES (1,'Ameryka PÅ‚n i PÅ‚d');
78INSERT INTO countries VALUES(1,'USA',1);
79INSERT INTO locations VALUES(1,'2004 Charade Rd','98199','Seattle','Washington',1);
80INSERT INTO jobs VALUES ('1','Prezes',20000,40000);
81INSERT INTO employees VALUES(100,'Steven','King','SKING','515.123.4567','1987-06-17','1',24000,NULL,NULL,NULL);
82INSERT INTO departments VALUES(1,'Kierownictwo',100,1);
83INSERT INTO job_history VALUES(100,'1987-06-17','','1',1);
84UPDATE employees 
85SET 
86    department_id = 1
87WHERE
88    employee_id = 100;