· 6 years ago · May 15, 2019, 08:24 PM
1DROP DATABASE IF EXISTS Institution;
2CREATE DATABASE Institution;
3USE Institution;
4
5CREATE TABLE people(
6id INT AUTO_INCREMENT PRIMARY KEY,
7Name VARCHAR(100) NOT NULL,
8Egn VARCHAR(10) NOT NULL,
9Address VARCHAR(255) NOT NULL,
10Email VARCHAR(30) NOT NULL,
11Phone VARCHAR(20) NOT NULL
12);
13
14CREATE TABLE employees(
15id INT AUTO_INCREMENT PRIMARY KEY,
16Position VARCHAR(20) NOT NULL,
17Salary DOUBLE NOT NULL,
18PracticeLength INT NOT NULL,
19people_id INT NOT NULL,
20CONSTRAINT FOREIGN KEY (people_id) REFERENCES people(id)
21);
22
23CREATE TABLE financiers(
24id INT AUTO_INCREMENT PRIMARY KEY,
25CompanyName VARCHAR(100) NOT NULL,
26Address VARCHAR(255) NOT NULL,
27Phone VARCHAR(20) NOT NULL
28);
29
30CREATE TABLE projects(
31id INT AUTO_INCREMENT PRIMARY KEY,
32Type VARCHAR(30) NOT NULL,
33Description TEXT NOT NULL,
34financier_id INT NOT NULL,
35CONSTRAINT FOREIGN KEY (financier_id) REFERENCES financiers(id)
36);
37
38CREATE TABLE project_employee(
39employee_id int not null,
40CONSTRAINT FOREIGN KEY (employee_id) REFERENCES employees(id),
41project_id int not null,
42CONSTRAINT FOREIGN KEY (project_id) REFERENCES projects(id),
43PRIMARY KEY(employee_id,project_id)
44);
45
46INSERT INTO people VALUES
47(NULL, 'Preslava Dimitrova', '9804053998', '"Prof. Boyan Kamenov 4", Sofia', 'preslava981@abv.bg', '0885516938'),
48(NULL, 'Kostadin Krushkov', '9805067855', '"Doctor Ivan Stranski 59", Sofia', 'kostadin01998@gmail.com', '0892356889'),
49(NULL, 'Iva Yonkova', '9808082399', '"Storgozia 43", Pleven', 'khaleesiD@mail.bg', '0874242987'),
50(NULL, 'Ana Dosheva', '9607256545', '"Kensington Plymouth", London', 'ani25@yahoo.com', '0894784554'),
51(NULL, 'Maria Petrova', '7606206545', '"Mladost 1", Sofia', 'maria76-petrova@abv.bg', '0887622119'),
52(NULL, 'Dimitar Ivanov', '8709153987', '"Langestraat 82", Amsterdam', 'mitko-iv87@mail.bg', '0879904123');
53
54INSERT INTO employees VALUES
55(NULL, 'Architect', 2100, 3, 4),
56(NULL, 'Pastry Cook', 1300, 2, 3),
57(NULL, 'Photographer', 1900, 1, 1),
58(NULL, 'Architect', 1850, 1, 5),
59(NULL, 'Programmer', 2800, 4, 6),
60(NULL, 'Programmer', 3000, 2, 2);
61
62INSERT INTO financiers VALUES
63(NULL, 'ARK Design', 'London', '0873456129'),
64(NULL, 'Telco Systems', 'Sofia', '0893456556'),
65(NULL, 'Lephemere Pastries', 'Pleven', '0883422178'),
66(NULL, 'Photosynthesis', 'Burgas', '0873456129');
67
68INSERT INTO projects VALUES
69(NULL, 'Engineering', 'Construction of a robot with different objectives. It consists of plans for building, coding, debugging and demonstration.', 2),
70(NULL, 'Construction & Design', 'Building a small building with apartments and then applying different design options.', 1),
71(NULL, 'Research', 'Looking for suitable wild nature locations to shoot new Hollywod production in Bulgaria.', 4),
72(NULL, 'Research', 'Conveying a survey about preferred living locations for building houses.', 1),
73(NULL, 'Management', 'Hiring people for various positions to manage a small pastry themed shop.', 3);
74
75INSERT INTO project_employee VALUES
76(1,2),
77(2,5),
78(3,3),
79(4,4),
80(5,1),
81(6,1);
82
83-- 1.
84SELECT * FROM people
85WHERE address LIKE '%Sofia%';
86
87SELECT projects.Type, projects.Description, financiers.CompanyName
88FROM projects
89JOIN financiers
90ON projects.financier_id=financiers.id
91WHERE type='Research';
92
93-- 2.
94SELECT employees.Position, AVG(employees.salary) as AverageSalary
95FROM employees
96GROUP BY position;
97
98-- 3.
99SELECT people.Name, people.Email, people.Phone, employees.Position, employees.Salary
100FROM people
101INNER JOIN employees
102ON people.id = employees.people_id;
103
104SELECT projects.Type, projects.Description, financiers.CompanyName, financiers.Address, financiers.Phone
105FROM projects
106LEFT OUTER JOIN financiers
107ON projects.financier_id=financiers.id;
108
109-- 4.
110SELECT employees.Position,MAX(employees.practiceLength) as 'Longest Years of Practice', projects.Type
111from employees INNER JOIN projects
112ON employees.id IN(
113SELECT employee_id
114FROM project_employee
115WHERE project_employee.project_id = projects.id);
116
117
118USE Institution;
119DROP PROCEDURE IF EXISTS email_procedure;
120
121DELIMITER |
122
123CREATE PROCEDURE email_procedure (INOUT email_list varchar(500))
124BEGIN
125
126 DECLARE finished INT;
127 DECLARE tempEmail varchar(30);
128
129 DEClARE email_cursor CURSOR FOR
130 SELECT email FROM people;
131
132 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
133
134 OPEN email_cursor;
135
136 get_email: LOOP
137 FETCH email_cursor INTO tempEmail;
138 IF finished = 1 THEN
139 LEAVE get_email;
140 END IF;
141 SET email_list = CONCAT(tempEmail,"; ",email_list);
142 END LOOP get_email;
143
144 CLOSE email_cursor;
145 SELECT 'Finished!';
146
147END
148|
149DELIMITER |
150
151SET @email_list = "";
152CALL email_procedure(@email_list);
153SELECT @email_list;