· 9 years ago · May 22, 2017, 04:42 PM
1DROP DATABASE IF EXISTS event_database;
2CREATE DATABASE event_database;
3USE event_database;
4
5CREATE TABLE events(
6 id INT AUTO_INCREMENT PRIMARY KEY,
7 address varchar(255) NOT NULL,
8 event_data datetime NOT NULL,
9 event_time time NOT NULL,
10 event_subject varchar(255) NOT NULL,
11 duratio INT NOT NULL
12);
13
14CREATE TABLE roles(
15 id INT AUTO_INCREMENT PRIMARY KEY,
16 role varchar(255)
17);
18
19CREATE TABLE users (
20 id INT AUTO_INCREMENT PRIMARY KEY,
21 names varchar(255) NOT NULL,
22 address varchar(255) NOT NULL,
23 telephone varchar(15) NOT NULL,
24 email varchar(255) NOT NULL,
25 roleId INT NOT NULL,
26 CONSTRAINT FOREIGN KEY (roleId) REFERENCES roles(id)
27);
28
29CREATE TABLE users_events(
30 id INT AUTO_INCREMENT PRIMARY KEY,
31 userId INT NOT NULL,
32 eventId INT NOT NULL,
33 CONSTRAINT FOREIGN KEY (userId) REFERENCES users(id),
34 CONSTRAINT FOREIGN KEY (eventId) REFERENCES events(id)
35);
36
37INSERT INTO events(address, event_data, event_time, event_subject, duratio)
38VALUES("Sofia, Bulgaria", DATE '2017-05-21', '10:30', "Flight To America", 15),
39 ("Ocen City, Maryland", DATE '2017-07-17', '20:00', "Lubo's Birthday", 12),
40 ("Miami, Florida", DATE '2017-09-25', '10:30', "Flight To Bulgaria", 15),
41 ("Sofia, Bulgaria", DATE '2017-05-21', '10:00', "Bulgarian Airport", 2),
42 ("Earth", DATE '9999-05-21', '10:00', "Hell Event", 20);
43
44INSERT INTO roles(role)
45VALUES("creator"), ("participant");
46
47INSERT INTO users(names, address, telephone, email, roleId)
48VALUES("Luboslav Ivanov Ivanov", "Sofia, Bulgaria", "0899994453", "lubo.ivanov96@gmail.com", 1),
49 ("Martin Georgiev Turnev", "Sofia, Bulgaria", "0873425735", "martin4oo97@gmail.com", 2),
50 ("Dimitur Georgiev Andreev", "Sofia, Bulgaria", "0899994453", "d.andreev@gmail.com", 2),
51 ("Simona Mitkova Grigorova", "Sofia, Bulgaria", "0895747313", "simonka97@gmail.com", 2),
52 ("Magdalena Grigorova Spasova ", "Sofia, Bulgaria", "0842743413", "magiToo97@gmail.com", 2),
53 ("Kamlia Jikova Doncheva ", "Sofia, Bulgaria", "0894237313", "kamitoo97@gmail.com", 2),
54 ("Emilia Trifonova Ivanova", "Sofia, Bulgaria", "0899994450", "emi_t@gmail.com", 2),
55 ("Ivan Bojidarov Ivanov", "Sofia, Bulgaria", "0898591498", "farma@gmail.com", 2);
56
57INSERT INTO users_events(userId, eventId)
58VALUES(1,1), (2,1), (3,1),
59 (1,2), (2,2), (3,2), (4,2), (5,2), (6,2),
60 (1, 3), (2, 3), (3, 3), (4, 3),
61 (1,4), (2,4), (3,4), (7,4), (8,4);
62
63
64###SelectExamples###
65
66##List all people with family name Ivanov## N:2
67
68SELECT names AS Names, address AS Address, telephone as Phone, email AS Email
69FROM users
70where names LIKE "% Ivanov%";
71
72##List how many participant were registered to every event## N:3
73
74SELECT event_subject AS Event_Subject, address AS Address, event_data AS Data, event_time as Time, COUNT(*) as Participants
75FROM users_events
76INNER JOIN events ON users_events.eventId = events.id
77GROUP BY eventId
78ORDER BY event_data, event_time;
79
80##List N:4
81
82SELECT event_subject as Event_Subject, names AS Participants, event_data as Data
83FROM users_events
84INNER JOIN users ON users_events.userId = users.id
85RIGHT OUTER JOIN events ON users_events.eventId = events.id
86ORDER BY COUNT(*);
87
88
89
90
91##List the people how will be on the airport when its time to go to USA.