· 5 years ago · Oct 25, 2020, 01:04 PM
1CREATE DATABASE softuni_stores_system;
2USE softuni_stores_system;
3
4CREATE TABLE `pictures`(
5 `id` INT AUTO_INCREMENT,
6 `url` VARCHAR(100) NOT NULL,
7 `added_on` DATETIME NOT NULL,
8 CONSTRAINT `pk_pictures` PRIMARY KEY(`id`)
9);
10
11CREATE TABLE `categories`(
12 `id` INT AUTO_INCREMENT,
13 `name` VARCHAR(40) UNIQUE NOT NULL,
14 CONSTRAINT `pk_categories` PRIMARY KEY(`id`)
15);
16
17CREATE TABLE `products`(
18 `id` INT AUTO_INCREMENT,
19 `name` VARCHAR(40) UNIQUE NOT NULL,
20 `best_before` DATE,
21 `price` DECIMAL(10, 2) NOT NULL,
22 `description` TEXT,
23 `category_id` INT NOT NULL,
24 `picture_id` INT NOT NULL,
25 CONSTRAINT `pk_products` PRIMARY KEY(`id`),
26 CONSTRAINT `fk_products_category` FOREIGN KEY(`category_id`) REFERENCES `categories`(`id`),
27 CONSTRAINT `fk_products_pictures` FOREIGN KEY(`picture_id`) REFERENCES `pictures`(`id`)
28);
29
30CREATE TABLE `towns`(
31 `id` INT AUTO_INCREMENT,
32 `name` VARCHAR(20) UNIQUE NOT NULL,
33 CONSTRAINT `pk_towns` PRIMARY KEY(`id`)
34);
35
36CREATE TABLE `addresses`(
37 `id` INT AUTO_INCREMENT,
38 `name` VARCHAR(50) UNIQUE NOT NULL,
39 `town_id` INT NOT NULL,
40 CONSTRAINT `pk_addresses` PRIMARY KEY(`id`),
41 CONSTRAINT `fk_addresses_towns` FOREIGN KEY(`town_id`) REFERENCES `towns`(`id`)
42);
43
44CREATE TABLE `stores`(
45 `id` INT AUTO_INCREMENT,
46 `name` VARCHAR(20) UNIQUE NOT NULL,
47 `rating` FLOAT NOT NULL,
48 `has_parking` BOOLEAN DEFAULT FALSE,
49 `address_id` INT NOT NULL,
50 CONSTRAINT `pk_stores` PRIMARY KEY(`id`),
51 CONSTRAINT `fk_stores_addresses` FOREIGN KEY(`address_id`) REFERENCES `addresses`(`id`)
52);
53
54CREATE TABLE `products_stores`(
55 `product_id` INT NOT NULL,
56 `store_id` INT NOT NULL,
57 CONSTRAINT `pk_products_stores` PRIMARY KEY(`product_id`, `store_id`),
58 CONSTRAINT `fk_products` FOREIGN KEY(`product_id`) REFERENCES `products`(`id`),
59 CONSTRAINT `fk_stores` FOREIGN KEY(`store_id`) REFERENCES `stores`(`id`)
60);
61
62CREATE TABLE `employees`(
63 `id` INT AUTO_INCREMENT,
64 `first_name` VARCHAR(15) NOT NULL,
65 `middle_name` CHAR(1),
66 `last_name` VARCHAR(20) NOT NULL,
67 `salary` DECIMAL(19, 2) DEFAULT 0,
68 `hire_date` DATE NOT NULL,
69 `manager_id` INT,
70 `store_id` INT NOT NULL,
71 CONSTRAINT `pk_employees` PRIMARY KEY(`id`),
72 CONSTRAINT `fk_managers_employees` FOREIGN KEY(`manager_id`) REFERENCES `employees`(`id`),
73 CONSTRAINT `fk_stores_employees` FOREIGN KEY(`store_id`) REFERENCES `stores`(`id`)
74);
75
76
77-- Data Manipulation Language (DML)
78
79INSERT INTO `products_stores`(`product_id`, `store_id`)
80SELECT p.`id`, 1 FROM `products` AS p
81LEFT JOIN `products_stores` AS ps ON p.`id` = ps.`product_id`
82WHERE ps.`store_id` IS NULL;
83
84SELECT store_id, s.name, p.name, product_id FROM products_stores
85JOIN products p ON p.id = products_stores.product_id
86JOIN stores s ON products_stores.store_id = s.id
87ORDER BY product_id, store_id;
88
89UPDATE `employees`
90SET `manager_id` = 3, `salary` = `salary` - 500
91WHERE `hire_date` >= '2003-01-01' AND `store_id` NOT IN (5, 14);
92
93DELETE FROM `employees` WHERE `manager_id` IS NOT NULL AND `salary` >= 6000;
94
95-- Querying
96
97SELECT `first_name`, `middle_name`, `last_name`, `salary`, `hire_date` FROM `employees`
98ORDER BY `hire_date` DESC;
99
100SELECT pr.`name` AS 'product_name', pr.`price`, pr.`best_before`,
101CONCAT(LEFT(pr.`description`, 10), '...') AS 'short_description', p.`url` FROM `products` AS pr
102JOIN `pictures` AS p ON pr.`picture_id` = p.`id`
103WHERE LENGTH(pr.`description`) > 100 AND p.`added_on` <= '2018-12-31'
104AND pr.`price` > 20
105ORDER BY pr.`price` DESC;
106
107SELECT s.`name`, COUNT(ps.`product_id`) AS 'product_count', ROUND(AVG(p.`price`), 2) AS 'avg'
108FROM `stores` AS s
109LEFT JOIN `products_stores` AS ps ON s.`id` = ps.`store_id`
110LEFT JOIN `products` AS p ON ps.`product_id` = p.`id`
111GROUP BY s.`name`
112ORDER BY `product_count` DESC, `avg` DESC, s.`id`;
113
114SELECT CONCAT(e.`first_name`, ' ', e.`last_name`) AS 'Full_name', s.`name` AS 'Store_name',
115a.`name` AS 'address', e.`salary` FROM `employees` AS e
116JOIN `stores` AS s ON e.`store_id` = s.`id`
117JOIN `addresses` AS a ON s.`address_id` = a.`id`
118WHERE e.`salary` < 7000 AND LOCATE('a', a.`name`) AND LENGTH(s.`name`) > 5;
119
120SELECT REVERSE(s.`name`) AS 'reversed_name', CONCAT(UPPER(t.`name`), '-', a.`name`) AS 'full_address',
121COUNT(e.`id`) AS 'employees_count', MIN(p.`price`) AS 'min_price',
122COUNT(p.`id`) AS 'products_count', MAX(DATE_FORMAT(pic.`added_on`, '%D-%b-%Y')) AS 'newest_pic'
123FROM `stores` AS s
124LEFT JOIN `addresses` AS a ON s.`address_id` = a.`id`
125LEFT JOIN `towns` AS t ON a.`town_id` = t.`id`
126LEFT JOIN `employees` AS e ON s.`id` = e.`store_id`
127LEFT JOIN `products_stores` AS ps ON s.`id` = ps.`store_id`
128LEFT JOIN `products` AS p ON ps.`product_id` = p.`id`
129LEFT JOIN `pictures` AS pic ON p.`picture_id` = pic.`id`
130GROUP BY `reversed_name`
131HAVING `min_price` > 10
132ORDER BY `reversed_name`, `min_price`;
133
134-- Section Programmability
135
136DROP FUNCTION IF EXISTS udf_top_paid_employee_by_store;
137DELIMITER $$
138CREATE FUNCTION udf_top_paid_employee_by_store(`store_name` VARCHAR(50))
139RETURNS VARCHAR(255)
140DETERMINISTIC
141BEGIN
142 RETURN (SELECT CONCAT(e.`first_name`, ' ', e.`middle_name`, '. ', e.`last_name`, ' ',
143 'works in store for', ' ', TIMESTAMPDIFF(YEAR, e.`hire_date`, "2020-10-18"), ' ', 'years') AS 'full_info'
144 FROM `employees` AS e
145 JOIN `stores` AS s ON e.`store_id` = s.`id`
146 WHERE s.`name` = `store_name`
147 ORDER BY e.`salary` DESC LIMIT 1);
148END $$
149DELIMITER ;
150SELECT udf_top_paid_employee_by_store('Stronghold') as 'full_info';
151
152
153DROP PROCEDURE IF EXISTS udp_update_product_price;
154DELIMITER $$
155CREATE PROCEDURE udp_update_product_price(`address_name` VARCHAR(50))
156BEGIN
157 DECLARE a_name VARCHAR(50);
158 SET `a_name` := (SELECT a.`name` FROM `addresses` AS a
159 WHERE a.`name` = `address_name`);
160
161 UPDATE `products` AS p
162 JOIN `products_stores` AS ps ON p.`id` = ps.`product_id`
163 JOIN `stores` as s ON ps.`store_id` = s.`id`
164 JOIN `addresses` AS a ON s.`address_id` = a.`id`
165 SET `price` = CASE
166 WHEN LEFT(`a_name`, 1) LIKE '0' THEN `price` + 100
167 ELSE `price` + 200
168 END
169 WHERE a.`name` = `address_name`;
170END $$
171
172CALL udp_update_product_price('07 Armistice Parkway');
173SELECT name, price FROM products WHERE id = 15;
174
175
176CALL udp_update_product_price('1 Cody Pass');
177SELECT name, price FROM products WHERE id = 17;