· 8 years ago · Dec 16, 2017, 05:36 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
73
74
75INSERT INTO employees (id, full_name, dob) VALUES (1, "maksim marcynkevich", date("1488-12-12"));
76INSERT INTO employees (id, full_name, dob) VALUES (2, "pavel belyakov1", date("1983-11-15"));
77INSERT INTO employees (id, full_name, dob) VALUES (3, "andrew horemyka", date());
78
79INSERT INTO position (id, name, employee_id) VALUES (1, "main engineer", 1);
80INSERT INTO position (id, name, employee_id) VALUES (2, "cleaner", 2);
81INSERT INTO position (id, name, employee_id) VALUES (3, "manager", 3);
82
83INSERT INTO speciality (id, name, field_of_activity, employee_id) VALUES (1, "project management", "software", 3);
84INSERT INTO speciality (id, name, field_of_activity, employee_id) VALUES (2, "workshop cleaner", "cleaning", 2);
85INSERT INTO speciality (id, name, field_of_activity, employee_id) VALUES (2, "cpu development", "digital electronics", 1);
86INSERT INTO speciality (id, name, field_of_activity, employee_id) VALUES (3, "cpu development", "digital electronics", 1);
87
88INSERT INTO workers (id, workshop_id, employee_id) VALUES (1, 1);
89INSERT INTO workers (id, workshop_id, employee_id) VALUES (1, 1, 1);
90INSERT INTO workers (id, workshop_id, employee_id) VALUES (2, 2, 1);
91INSERT INTO workers (id, workshop_id, employee_id) VALUES (3, 3, 2);
92INSERT INTO workers (id, workshop_id, employee_id) VALUES (4, 2, 3);
93
94
95INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
96INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 1, 1, 2);
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", 2, 2, 2);
105INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 3, 2, 2);
106INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 3, 3, 2);
107INSERT INTO produced (produced_at, price, workshop_id, product_id, speciality_id) VALUES (datetime(), "555", 3, 3, 3);
108
109SELECT employees.full_name, workshops.location, workshops.production_type from workers
110inner join employees on employees.id = workers.employee_id
111inner join workshops on workshops.id = workers.workshop_id
112where workshops.location like 'california' order by employees.full_name;
113
114
115SELECT distinct produced_at, products.name, workshops.location, price from produced
116inner join products on products.id = produced.product_id
117inner join workshops on workshops.id = produced.workshop_id
118order by produced_at;
119
120SELECT employees.full_name, workshops.production_type, speciality.name from produced
121inner join workshops on workshops.id = produced.workshop_id
122inner join speciality on speciality.id = produced.speciality_id
123inner join employees on employees.id = speciality.employee_id;