· 8 years ago · Dec 16, 2017, 06:50 PM
1CREATE TABLE IF NOT EXISTS workshops
2(
3 id integer PRIMARY KEY,
4 location text NOT NULL,
5 production_type text NOT NULL
6);
7
8CREATE TABLE machine
9(
10 id integer NOT NULL PRIMARY KEY,
11 machine_id text NOT NULL,
12 lifetime DATE NOT NULL,
13 power_source VARCHAR(64) NOT NULL,
14 workshop_id integer NOT NULL,
15 FOREIGN KEY (workshop_id) REFERENCES workshops (id)
16);
17
18
19
20CREATE TABLE workers
21(
22 id INT NOT NULL PRIMARY KEY,
23 workshop_id integer NOT NULL,
24 employee_id integer NOT NULL,
25 FOREIGN KEY (workshop_id) REFERENCES workshops(workshop_id) ON DELETE CASCADE ON UPDATE CASCADE,
26 FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE ON UPDATE CASCADE
27);
28
29CREATE TABLE employees
30(
31 id INT NOT NULL PRIMARY KEY,
32 full_name VARCHAR(64) NOT NULL,
33 dob DATE NOT NULL
34);
35
36CREATE TABLE position
37(
38 id INT NOT NULL PRIMARY KEY,
39 name VARCHAR(64) NOT NULL,
40 employee_id integer NOT NULL,
41 FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE ON UPDATE CASCADE
42);
43
44CREATE TABLE speciality
45(
46 id INT NOT NULL PRIMARY KEY,
47 name VARCHAR(64) NOT NULL,
48 field_of_activity VARCHAR(64) NOT NULL,
49 employee_id integer NOT NULL,
50 FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE ON UPDATE CASCADE
51);
52
53CREATE TABLE products
54(
55 id INT NOT NULL PRIMARY KEY,
56 name VARCHAR(64) NOT NULL,
57 target_audience VARCHAR(64) NOT NULL
58);
59
60CREATE TABLE produced
61(
62 produced_at DATE NOT NULL PRIMARY KEY,
63 price VARCHAR(64) NOT NULL,
64 speciality_id integer NOT NULL,
65 workshop_id integer NOT NULL,
66 product_id integer NOT NULL,
67 FOREIGN KEY (workshop_id) REFERENCES workshop(workshop_id) ON DELETE CASCADE ON UPDATE CASCADE,
68 FOREIGN KEY (speciality_id) REFERENCES speciality(speciality_id) ON DELETE CASCADE ON UPDATE CASCADE,
69 FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE ON UPDATE CASCADE
70);
71
72
73INSERT INTO employees (id, full_name, dob) VALUES (1, "maksim marcynkevich", date("1488-12-12"));
74INSERT INTO employees (id, full_name, dob) VALUES (2, "pavel belyakov1", date("1983-11-15"));
75INSERT INTO employees (id, full_name, dob) VALUES (3, "andrew horemyka", date());
76
77INSERT INTO position (id, name, employee_id) VALUES (1, "main engineer", 1);
78INSERT INTO position (id, name, employee_id) VALUES (2, "cleaner", 2);
79INSERT INTO position (id, name, employee_id) VALUES (3, "manager", 3);
80
81INSERT INTO speciality (id, name, field_of_activity, employee_id) VALUES (1, "project management", "software", 3);
82INSERT INTO speciality (id, name, field_of_activity, employee_id) VALUES (2, "workshop cleaner", "cleaning", 2);
83INSERT INTO speciality (id, name, field_of_activity, employee_id) VALUES (2, "cpu development", "digital electronics", 1);
84INSERT INTO speciality (id, name, field_of_activity, employee_id) VALUES (3, "cpu development", "digital electronics", 1);
85
86INSERT INTO workers (id, workshop_id, employee_id) VALUES (1, 1, 2);
87INSERT INTO workers (id, workshop_id, employee_id) VALUES (1, 1, 1);
88INSERT INTO workers (id, workshop_id, employee_id) VALUES (2, 2, 1);
89INSERT INTO workers (id, workshop_id, employee_id) VALUES (3, 3, 2);
90INSERT INTO workers (id, workshop_id, employee_id) VALUES (4, 2, 3);
91
92INSERT INTO workshops (id, location, production_type) VALUES (1, "california", "cpu");
93INSERT INTO workshops (id, location, production_type) VALUES (2, "honkong", "electrocars");
94INSERT INTO workshops (id, location, production_type) VALUES (3, "london", "ICs");
95
96
97INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
98INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
99INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
100INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
101INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
102INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
103INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
104INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
105INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
106INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 2, 2, 2);
107INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 3, 2, 2);
108INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 3, 3, 2);
109INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 3, 3, 3);
110
111-- SELECT employees.full_name, workshops.location, workshops.production_type from workers
112-- inner join employees on employees.id = workers.employee_id
113-- inner join workshops on workshops.id = workers.workshop_id
114-- wherpe workshops.location like 'california' order by employees.full_name;
115
116
117-- SELECT distinct produced_at, products.name, workshops.location, price from produced
118-- inner join products on products.id = produced.product_id
119-- inner join workshops on workshops.id = produced.workshop_id
120-- order by produced_at;
121
122-- SELECT employees.full_name, workshops.production_type, speciality.name from produced
123-- inner join workshops on workshops.id = produced.workshop_id
124-- inner join speciality on speciality.id = produced.speciality_id
125-- inner join employees on employees.id = speciality.employee_id;
126
127----------------------------------------------------------------------
128
129
130-- select employees.full_name, avg(price) from produced
131-- inner join speciality on produced.speciality_id = speciality.id
132-- inner join employees on speciality.employee_id = employees.id
133-- where employees.dob < '2000-07-01:00:00:00'
134-- group by employees.full_name;
135
136-- select workshops.location, workshops.production_type, count(workers.id) from workers
137-- inner join workshops on workers.workshop_id = workshops.id
138-- group by location, production_type
139-- order by count(workers.id) desc;
140
141select products.name, employees.full_name from produced
142inner join products on produced.product_id = products.id
143inner join speciality on produced.speciality_id = speciality.id
144inner join employees on speciality.employee_id = employees.id
145inner join position on employees.id = position.employee_id
146where position.name like 'cleaner';