· 8 years ago · Apr 02, 2018, 03:22 AM
1DROP DATABASE IF EXISTS hypermarket;
2CREATE DATABASE hypermarket;
3USE hypermarket;
4
5CREATE TABLE addresses(
6 id INT AUTO_INCREMENT PRIMARY KEY,
7 country VARCHAR(40) NOT NULL,
8 city VARCHAR(40) NOT NULL,
9 zip_code VARCHAR(30),
10 neighborhood VARCHAR(30) NOT NULL,
11 street VARCHAR(30) NOT NULL,
12 `number` VARCHAR(10) NOT NULL
13);
14
15CREATE TABLE departments(
16 id INT AUTO_INCREMENT PRIMARY KEY,
17 `name` VARCHAR(30) NOT NULL,
18 manager_id INT
19);
20
21CREATE TABLE employees(
22 id INT AUTO_INCREMENT PRIMARY KEY,
23 first_name VARCHAR(30) NOT NULL,
24 midle_name VARCHAR(30) NOT NULL,
25 last_name VARCHAR(30) NOT NULL,
26 salary DECIMAL(7,2) NOT NULL,
27 department_id INT,
28 address_id INT,
29 CONSTRAINT FOREIGN KEY (department_id)
30 REFERENCES departments(id),
31 CONSTRAINT FOREIGN KEY (address_id)
32 REFERENCES addresses(id)
33);
34
35ALTER TABLE departments
36ADD CONSTRAINT FOREIGN KEY (manager_id)
37REFERENCES employees(id);
38
39CREATE TABLE product_types(
40 id INT AUTO_INCREMENT PRIMARY KEY,
41 product_type VARCHAR(30) NOT NULL
42);
43
44CREATE TABLE products(
45 id INT AUTO_INCREMENT PRIMARY KEY,
46 `name` VARCHAR(30) NOT NULL,
47 price DECIMAL(8,2) NOT NULL,
48 expiry_date DATE NOT NULL,
49 weight DECIMAL(8,2),
50 product_type_id INT,
51 department_id INT,
52 CONSTRAINT FOREIGN KEY (product_type_id)
53 REFERENCES product_types(id),
54 CONSTRAINT FOREIGN KEY (department_id)
55 REFERENCES departments(id)
56);
57
58CREATE TABLE manufacturers(
59 id INT AUTO_INCREMENT PRIMARY KEY,
60 `name` VARCHAR(30) NOT NULL,
61 address_id INT,
62 CONSTRAINT FOREIGN KEY (address_id)
63 REFERENCES addresses(id)
64);
65
66CREATE TABLE products_manufactures(
67 product_id INT NOT NULL,
68 manufacture_id INT NOT NULL,
69 PRIMARY KEY (product_id, manufacture_id),
70 CONSTRAINT FOREIGN KEY (product_id)
71 REFERENCES products(id),
72 CONSTRAINT FOREIGN KEY (manufacture_id)
73 REFERENCES manufacturers(id)
74);
75
76CREATE TABLE emails(
77 id INT AUTO_INCREMENT PRIMARY KEY,
78 email VARCHAR(100),
79 employee_id INT,
80 manufacture_id INT,
81 CONSTRAINT FOREIGN KEY (employee_id)
82 REFERENCES employees(id),
83 CONSTRAINT FOREIGN KEY (manufacture_id)
84 REFERENCES manufacturers(id)
85);
86
87CREATE TABLE telephones(
88 id INT AUTO_INCREMENT PRIMARY KEY,
89 telephone VARCHAR(40),
90 employee_id INT,
91 manufacture_id INT,
92 CONSTRAINT FOREIGN KEY (employee_id)
93 REFERENCES employees(id),
94 CONSTRAINT FOREIGN KEY (manufacture_id)
95 REFERENCES manufacturers(id)
96);
97
98INSERT INTO addresses (country, city, zip_code, neighborhood, street, `number`)
99VALUES
100 ('Bulgaria', 'Sofia', '1000', 'Banishora', 'Klokotnica', '34'),
101 ('Bulgaria', 'Sofia', '1000', 'Sveta troica', 'Varna', '21'),
102 ('Bulgaria', 'Sofia', '1000', 'Zapadern park', 'Naicho Canov', '10'),
103 ('Bulgaria', 'Sofia', '1000', 'Druzhba', '5019', '7'),
104 ('Bulgaria', 'Varna', '9000', 'Hristo Botev', 'Stara planina', '8');
105
106SET FOREIGN_KEY_CHECKS=0;
107
108INSERT INTO departments(`name`,manager_id)
109VALUES
110 ('meat food',2),
111 ('drinks',2),
112 ('bathrooms',2),
113 ('office products',1),
114 ('bread department',1);
115
116INSERT INTO employees(first_name, midle_name, last_name, salary, department_id,address_id)
117VALUES
118 ('Ivan', 'Mendeleev', 'Mendeleev', 50000, 1, 1),
119 ('Stamat', 'Vencislavov', 'Aleksandrov', 60000, 2, NULL),
120 ('Pencho', 'Petkov', 'Trifonov', 55000 , 3, NULL),
121 ('Lili', 'Bozhkova', 'Ivanova', 66000, 4, NULL),
122 ('Boiko', 'Borislavov', 'Petrov', 80000, 5, 2);
123
124SET FOREIGN_KEY_CHECKS=1;
125
126INSERT INTO product_types(product_type)
127VALUES
128 ('meat'),
129 ('drink'),
130 ('toilet'),
131 ('office'),
132 ('bread');
133
134INSERT INTO products(`name`, price, expiry_date, weight, product_type_id, department_id)
135VALUES
136 ('bacon', 12, '2019-01-02', 1.2, 1, 1),
137 ('water', 1, '2019-01-10', 1.5, 2, 2),
138 ('toilet paper', 1, '2025-01-02', 0.5, 2, 2),
139 ('pen', 0.5, '2024-01-02', 0.02, 2, 2),
140 ('dobruzhda bread', 2, '2019-01-03', 0.1, 2, 2);
141
142INSERT INTO manufacturers(`name`, address_id)
143VALUES
144 ('bacons OOD', 3),
145 ('food creaters', 4),
146 ('Office Eldorado', 5);
147
148INSERT INTO products_manufactures(product_id,manufacture_id)
149VALUES
150 (1,1),
151 (1,2),
152 (2,3),
153 (3,3),
154 (4,3),
155 (5,2);
156
157INSERT INTO emails(email, employee_id, manufacture_id)
158VALUES
159 ('pingvin@abv.bg', 1, NULL),
160 ('stamat@abv.bg', 2, NULL),
161 ('bobo.c@abv.bg', 3, NULL),
162 ('titan@abv.bg', 3, NULL),
163 ('darling@abv.bg', NULL, 2);
164
165INSERT INTO telephones(telephone, employee_id, manufacture_id)
166VALUES
167 ('0877322781', 1, NULL),
168 ('0877322782', 2, NULL),
169 ('0877322783', 3, NULL),
170 ('0877322784', 3, NULL),
171 ('0877322789', NULL, 2);
172
173SELECT pr.name AS product_name, pr.expiry_date, man.name AS manufacturer
174FROM products AS pr
175JOIN manufacturers AS man
176ON man.id IN (
177 SELECT pr_man.manufacture_id
178 FROM products_manufactures AS pr_man
179 WHERE pr_man.product_id = pr.id
180 );
181#2
182SELECT emp.first_name, emp.last_name
183FROM employees AS emp
184WHERE emp.address_id IN (
185 SELECT ad.id
186 FROM addresses AS ad
187 WHERE ad.city = 'Sofia'
188 );
189#3
190SELECT ad.city, COUNT(ad.city) AS addresses_from_city
191FROM addresses AS ad
192GROUP BY ad.city;
193
194#4
195#---> INNER JOIN EXAMPLE
196SELECT emp.first_name, ad.city
197FROM employees as emp
198JOIN addresses AS ad
199ON emp.address_id = ad.id;
200
201#----> OUTER JOIN EXAMPLE
202SELECT emp.first_name, ad.city
203FROM employees as emp
204LEFT JOIN addresses AS ad
205ON emp.address_id = ad.id;
206
207#5
208SELECT dep.name AS department, ROUND(AVG(emp.salary),2) AS avarege_salary
209FROM employees AS emp
210JOIN departments AS dep
211ON emp.department_id = dep.id
212GROUP BY dep.name;
213
214DROP PROCEDURE IF EXISTS p_cursor_demonstration;
215DELIMITER $$
216CREATE PROCEDURE p_cursor_demonstration()
217BEGIN
218DECLARE flag INT;
219DECLARE temp_employee_name VARCHAR(30);
220DECLARE temp_employee_salary DECIMAL;
221DECLARE employee_cursor CURSOR FOR
222SELECT emp.first_name, emp.salary
223FROM employees AS emp
224WHERE emp.salary IS NOT NULL;
225DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 0;
226SET flag = 1;
227OPEN employee_cursor;
228employee_loop: WHILE(flag = 1)
229DO
230FETCH employee_cursor INTO temp_employee_name, temp_employee_salary;
231 IF(flag = 0)
232 THEN
233 LEAVE employee_loop;
234 END IF;
235 SELECT temp_employee_name, temp_employee_salary;
236END WHILE;
237CLOSE employee_cursor;
238SET flag = 1;
239END;
240$$
241DELIMITER ;
242
243CALL p_cursor_demonstration();