· 9 years ago · Oct 05, 2016, 06:14 PM
1CREATE TABLE IF NOT EXISTS city (
2 city_id SERIAL NOT NULL PRIMARY KEY,
3 name VARCHAR(50) NOT NULL
4);
5
6CREATE TYPE clan_type AS ENUM ('КраÑные', 'Синие', 'Желтые');
7
8CREATE TABLE IF NOT EXISTS hanter (
9 hanter_id SERIAL NOT NULL PRIMARY KEY,
10 firstname VARCHAR(50) NOT NULL,
11 surname VARCHAR(50),
12 age INT,
13CONSTRAINT positive_age CHECK (age >= 0),
14 city INT,
15 CONSTRAINT hanter_city_id_fk FOREIGN KEY (city)
16 REFERENCES city (city_id),
17 create_date TIMESTAMP DEFAULT now(),
18 clan clan_type NOT NULL,
19 clan_change_date DATE DEFAULT CURRENT_DATE
20);
21
22CREATE TABLE IF NOT EXISTS main_type(
23 type_id SERIAL NOT NULL PRIMARY KEY,
24 name VARCHAR(50) NOT NULL
25);
26
27CREATE TABLE IF NOT EXISTS additional_type(
28 type_id SERIAL NOT NULL PRIMARY KEY,
29 name VARCHAR(50) NOT NULL
30);
31
32CREATE TABLE IF NOT EXISTS pokemon (
33 pokemon_id SERIAL NOT NULL PRIMARY KEY,
34 description TEXT,
35 type_1_id INT,
36 type_2_id INT,
37 CONSTRAINT pokemon_type_1_id_fk FOREIGN KEY (type_1_id)
38 REFERENCES main_type (type_id),
39 CONSTRAINT pokemon_type_2_id_fk FOREIGN KEY (type_2_id)
40 REFERENCES additional_type (type_id),
41 pokemon_evolution_id INT
42);
43
44ALTER TABLE pokemon ADD CONSTRAINT pokemon_evalution_id_pk
45 FOREIGN KEY (pokemon_evolution_id)
46 REFERENCES pokemon (pokemon_id);
47
48CREATE TABLE IF NOT EXISTS hanter_pokemon (
49 hp_id SERIAL NOT NULL PRIMARY KEY,
50 hanter_id INT NOT NULL,
51 pokemon_id INT NOT NULL,
52 CONSTRAINT hanter_id_fk FOREIGN KEY (hanter_id)
53 REFERENCES hanter (hanter_id),
54 CONSTRAINT pokemon_id_fk FOREIGN KEY (pokemon_id)
55 REFERENCES pokemon (pokemon_id),
56 catch_date TIMESTAMP DEFAULT now(),
57 catch_point POINT
58);
59
60CREATE TABLE IF NOT EXISTS item (
61 item_id SERIAL NOT NULL PRIMARY KEY,
62 name VARCHAR(50) NOT NULL,
63 description TEXT,
64 is_for_pokemon BOOLEAN NOT NULL,
65 duration_time DOUBLE PRECISION default 0,
66 cost INT NOT NULL
67);
68
69CREATE TABLE IF NOT EXISTS hanter_item (
70 hi_id SERIAL NOT NULL PRIMARY KEY,
71 hanter_id INT,
72 item_id INT,
73 CONSTRAINT hanter_id_fk FOREIGN KEY (hanter_id)
74 REFERENCES hanter (hanter_id),
75 CONSTRAINT item_id_fk FOREIGN KEY (item_id)
76 REFERENCES item (item_id),
77 count INT,
78 CONSTRAINT count_positive CHECK (count > 0)
79);
80
81-- TASK 2
82
83ALTER TABLE hanter_pokemon ADD COLUMN pokeboll_count INT NOT NULL;
84
85ALTER TABLE hanter_pokemon ADD CONSTRAINT pokeboll_count_non_negative CHECK (pokeboll_count >= 0);
86
87
88ALTER TABLE hanter_pokemon ADD CONSTRAINT hander_pokemon_unique UNIQUE (hanter_id, pokemon_id);
89
90ALTER TABLE item ALTER COLUMN "cost" TYPE Decimal(11, 2),
91 ALTER COLUMN "cost" SET NOT NULL;
92
93
94--Taks 3
95
96--3.1
97
98UPDATE dept_manager
99 SET to_date = '1994-06-01'
100 WHERE dept_no = (SELECT dept_no
101 FROM departments
102 WHERE dept_name = 'Research'
103 )
104 AND emp_no = (SELECT emp_no
105 FROM employees
106 WHERE first_name = 'Hilary'
107 AND last_name = 'Kambil')
108 AND to_date = '9999-01-01';
109
110INSERT INTO dept_manager (dept_no, emp_no, from_date, to_date)
111 VALUES ((SELECT dept_no
112 FROM departments
113 WHERE dept_name = 'Research'
114 ),
115 (SELECT emp_no
116 FROM employees
117 WHERE first_name = 'Lucien'
118 AND last_name = 'Rosenbaum'),
119 '1994-06-01',
120 '9999-01-01');
121
122
123UPDATE titles
124 SET to_date = '1994-06-01'
125 WHERE emp_no = (SELECT emp_no
126 FROM employees
127 WHERE first_name = 'Lucien'
128 AND last_name = 'Rosenbaum')
129 AND to_date = '9999-01-01';
130
131INSERT INTO titles (emp_no, title, from_date, to_date)
132 VALUES ((SELECT emp_no
133 FROM employees
134 WHERE first_name = 'Lucien'
135 AND last_name = 'Rosenbaum'),
136 'Manager',
137 '1994-06-01',
138 '9999-01-01');
139
140
141-- 3.2
142
143SELECT e.emp_no as number,
144 e.first_name || ' ' || e.last_name as name,
145 age(max(de.to_date), e.birth_date) as layouff_age,
146 EXTRACT(YEAR FROM age(max(de.to_date), e.hire_date)) as years_of_work
147 FROM employees as e
148 JOIN dept_emp as de ON de.emp_no = e.emp_no
149 WHERE de.to_date != '9999-01-01'
150 AND EXISTS (SELECT 1
151 FROM dept_emp as sub_de
152 JOIN departments sub_d
153 ON sub_de.dept_no = sub_d.dept_no
154 AND sub_de.emp_no = e.emp_no
155 WHERE sub_d.dept_name = 'Development'
156 AND 1990 <= EXTRACT(YEAR FROM sub_de.from_date)
157 AND EXTRACT(YEAR FROM sub_de.to_date) <= 1995
158 )
159 AND EXISTS (SELECT 1 FROM salaries as sub_s
160 WHERE sub_s.emp_no = e.emp_no
161 AND 40000 <= sub_s.salary
162 AND sub_s.salary <= 50000)
163 AND EXTRACT(YEAR FROM age(e.hire_date, birth_date)) <= 30
164 GROUP BY e.emp_no
165 ORDER BY years_of_work, name
166 LIMIT 50;
167
168
169
170---3.3
171
172SELECT d.dept_name as department,
173 count(*) as employees_count,
174 max(s.salary) as maximum_salary,
175 avg(s.salary) as average_salary,
176 count(case when EXTRACT(YEAR FROM age(timestamp '1995-01-01', e.birth_date)) < 30 then e.emp_no end)
177 as before_30,
178 count(case when EXTRACT(YEAR FROM age(timestamp '1995-01-01', e.birth_date)) BETWEEN 30 AND 40
179 then e.emp_no end)
180 as between_30_40,
181 count(case when EXTRACT(YEAR FROM age(timestamp '1995-01-01', e.birth_date)) > 40 then e.emp_no end)
182 as after_40
183
184 FROM departments as d
185 JOIN dept_emp as de ON d.dept_no = de.dept_no
186 AND (TIMESTAMP '1995-01-01' BETWEEN de.from_date AND de.to_date)
187 AND de.to_date = '9999-01-01'
188 JOIN employees as e ON de.emp_no = e.emp_no
189 JOIN salaries as s ON s.emp_no = e.emp_no
190 AND s.to_date = '9999-01-01'
191 GROUP BY d.dept_no