· 8 years ago · Mar 27, 2018, 03:28 PM
1DROP DATABASE IF EXISTS internet;
2CREATE DATABASE internet;
3USE internet;
4
5CREATE TABLE users(
6id INT AUTO_INCREMENT PRIMARY KEY,
7name VARCHAR(255) NOT NULL,
8egn VARCHAR(10) NOT NULL UNIQUE,
9address VARCHAR(255) NOT NULL,
10phone VARCHAR(32) NOT NULL,
11mac VARCHAR(32) NOT NULL,
12ip VARCHAR(32) NOT NULL,
13plan VARCHAR(255) NOT NULL,
14cicleDate DATE NOT NULL,
15averageUsage DOUBLE
16);
17
18CREATE TABLE employees(
19id INT AUTO_INCREMENT PRIMARY KEY,
20name VARCHAR(255) NOT NULL,
21egn VARCHAR(10) NOT NULL UNIQUE
22);
23
24CREATE TABLE complaints(
25id INT AUTO_INCREMENT PRIMARY KEY,
26user_id INT NOT NULL,
27employee_id INT NOT NULL,
28fix VARCHAR(255) NOT NULL,
29charge DOUBLE,
30time DATETIME NOT NULL,
31CONSTRAINT FOREIGN KEY(user_id)
32 REFERENCES users(id) ,
33CONSTRAINT FOREIGN KEY (employee_id)
34 REFERENCES employees(id)
35);
36
37INSERT INTO employees
38VALUES (NULL, 'Ivan Todorov Petkov', '7509041245') ,
39 (NULL, 'georgi Ivanov Todorov', '8010091245') ,
40 (NULL, 'Ilian Todorov Georgiev', '8407106352') ,
41 (NULL, 'Petar Slavkov Yordanov', '7010102045') ,
42 (NULL, 'Todor Ivanov Ivanov', '8302160980') ,
43 (NULL, 'Slavi Petkov Petkov', '7106041278');
44
45INSERT INTO users
46VALUES (NULL,'Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', ' 01-23-45-67-89-ab-cd-ef','0.0.0.1', '50MB', '2018-01-01', 1.2) ,
47 (NULL,'Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11-23-45-67-89-ab-cd-ef','0.0.0.2', '150MB', '2018-01-15', 50.5) ,
48 (NULL,'Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '12-23-45-67-89-ab-cd-ef','0.0.0.3', '100MB', '2018-01-09', 55.5) ,
49 (NULL,'Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '14-23-45-67-89-ab-cd-ef','0.0.0.4', '50MB', '2018-01-01', 30) ,
50 (NULL,'Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '13-23-45-67-89-ab-cd-ef','0.0.0.5', '150MB', '2018-01-01', 20.6) ,
51 (NULL,'Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '15-23-45-67-89-ab-cd-ef','0.0.0.6', '100MB', '2018-01-09', 5.2);
52
53INSERT INTO complaints
54VALUES (NULL, 1, 3, 'smenen ruter', 9.9, now()),
55 (NULL, 2, 3, 'smeneni nastroyki na rutera', 0, now()),
56 (NULL, 3, 4, 'smenen ruter', 9.9, now()),
57 (NULL, 4, 2, 'smeneni nastroyki na kompyutara', 0, now()),
58 (NULL, 4, 2, 'smeneni nastroyki na rutera', 0, now()),
59 (NULL, 5, 5, 'smenen kabel', 0, now()),
60 (NULL, 5, 1, 'smenen ruter', 9.9, now()),
61 (NULL, 6, 5, 'smenen kabel', 0, now()),
62 (NULL, 6, 1, 'smeneni nastroyki na rutera', 0, now());
63
64SELECT name, egn, phone FROM users
65WHERE address = 'Sofia-Mladost 3';
66
67SELECT plan as Plan, COUNT(*) as NumberOfUsers FROM users
68GROUP BY plan;
69
70SELECT users.name as User, employees.name as EmployeeHandledComplaint
71FROM users JOIN complaints
72JOIN employees ON users.id
73IN( SELECT user_id FROM complaints WHERE complaints.employee_id = employees.id);