i0xJx6x7

1DROP DATABASE IF EXISTS People_Tracking;
2CREATE DATABASE People_Tracking;
3USE People_Tracking;
4#Задача 1 - Създаване на базата + ER диаграма
5CREATE TABLE people(
6	id INT AUTO_INCREMENT PRIMARY KEY ,
7	name VARCHAR(255) NOT NULL ,
8	egn VARCHAR(10) NOT NULL UNIQUE ,
9	address VARCHAR(255) NOT NULL ,
10	phone VARCHAR(20) NULL DEFAULT NULL ,
11	email VARCHAR(50) NULL DEFAULT NULL ,
12    status ENUM('In a relationship','Single', 'Married') NULL DEFAULT NULL,
13    carModel VARCHAR(50) NULL DEFAULT NULL,
14    shoeNumber INT NULL DEFAULT NULL
15);
16CREATE TABLE places(
17	id INT AUTO_INCREMENT PRIMARY KEY ,
18	country VARCHAR(255) NOT NULL ,
19	city VARCHAR(255) NOT NULL ,
20	address VARCHAR(255) NOT NULL ,
21	gpsCoordinates VARCHAR(100) NULL DEFAULT NULL ,
22	type ENUM('Restaurant', 'Bar', 'Library', 'Pub', 'Unspecified') NOT NULL DEFAULT 'Unspecified'
23);
24CREATE TABLE activities(
25	person_id INT NOT NULL,
26    place_id INT NOT NULL,
27    PRIMARY KEY(person_id,place_id),
28    CONSTRAINT FOREIGN KEY (person_id) REFERENCES people(id),
29    CONSTRAINT FOREIGN KEY (place_id) REFERENCES places(id),
30    time DATETIME NOT NULL,
31    isAlone BOOLEAN NOT NULL DEFAULT 1,
32    lengthOfStayMinutes INT NOT NULL,
33    wasWith INT NULL DEFAULT NULL,
34    CONSTRAINT FOREIGN KEY (wasWith) REFERENCES people(id)
35);
36
37INSERT INTO people(name,egn,address,phone,email,status,carModel,shoeNumber)
38VALUES('Иван Иванов Иванов', '9812187300','град София, Младост 3', '0887855432', 'ivan@abv.bg', 'Single', 'Audi R8', '42');
39
40INSERT INTO people(name,egn,address,phone,email,status,carModel,shoeNumber)
41VALUES('Петър Иванов Попов', '9811047033','град София, Младост 2', '0887424242', 'petur@abv.bg', 'Married', 'Mercedes', '45');
42
43INSERT INTO people(name,egn,address,phone,email,status,carModel,shoeNumber)
44VALUES('Мария Иванова Петрова', '9211047033','град София, Дружба 1', '0888332298', 'maria@abv.bg', 'Single', 'Mini Cooper', '39');
45
46INSERT INTO people(name,egn,address,phone,email,status,carModel,shoeNumber)
47VALUES('Петя Иванова Иванова', '9210047033','град София, Дружба 2', '0888375298', NULL, 'Single', 'Opel', '40');
48
49INSERT INTO places(country, city, address, gpsCoordinates, type)
50VALUES('България', 'София', 'ул. Димитър Списаревски 24', '42.69751 23.32415', 'Bar');
51
52INSERT INTO places(country, city, address, gpsCoordinates, type)
53VALUES('България', 'София', 'ул. Константин Величков 31', '42.69751 23.32415', 'Unspecified');
54
55INSERT INTO places(country, city, address, gpsCoordinates, type)
56VALUES('България', 'София', 'бул. Мария Луиза', '42.69751 23.32415', 'Restaurant');
57
58INSERT INTO activities(person_id,place_id,time,isAlone,lengthOfStayMinutes,wasWith)
59VALUES('1','1','2019-02-05 08:00:00','0','50','4');
60
61INSERT INTO activities(person_id,place_id,time,isAlone,lengthOfStayMinutes,wasWith)
62VALUES('2','2','2019-02-05 08:00:00','1','30',NULL);
63
64INSERT INTO activities(person_id,place_id,time,isAlone,lengthOfStayMinutes,wasWith)
65VALUES('3','3','2019-02-06 14:00:00','0','25','2');
66
67INSERT INTO activities(person_id,place_id,time,isAlone,lengthOfStayMinutes,wasWith)
68VALUES('1','3','2019-12-04 16:00:00','0','15','3');
69
70INSERT INTO people(name,egn,address,phone,email,status,carModel,shoeNumber)
71VALUES('Александър Иванов Георгиев', '9812177200','град София, Младост 3', '0887852842', 'alexander@abv.bg', 'Married', 'Ford GT', '45');
72
73
74
75#Задача 2: Напишете заявка, в която демонстрирале SELECT с ограничаващо условие по избор.
76SELECT *
77FROM activities
78WHERE person_id = '1';
79
80SELECT name AS PeopleWhoAreSingle
81FROM people
82WHERE status = 'Single';
83
84#Задача 3: Напишете заявка, в която използвате агрегатна функция и GROUP BY по ваш избор.
85SELECT people.name AS Name, COUNT(activities.person_id) AS NumberOfActivities
86FROM activities JOIN people
87ON activities.person_id = people.id
88GROUP BY people.id;
89
90#Задача 4: Напишете заявка, в която демонстрирате INNER и OUTER JOIN по ваш избор.
91#Изкарваме типовете и времето на всички места, които е посетил човек с име Иван Иванов Иванов;
92SELECT people.name AS Name, places.type AS PlaceType, activities.time
93FROM people JOIN activities
94ON activities.person_id = people.id
95JOIN places
96ON activities.place_id = places.id
97WHERE people.name = 'Иван Иванов Иванов';
98
99
100#Виждаме, че имаме двама човека, дето не участват в никакви събития.
101SELECT people.name AS Name, activities.*
102FROM people LEFT OUTER JOIN activities
103ON people.id = activities.person_id;
104
105
106#Задача 5 - Да напишем процедура, в която използваме курсор по избор.
107USE people_tracking;
108drop procedure if exists  peopleCursor;
109DELIMITER |
110CREATE PROCEDURE peopleCursor(person_status VARCHAR(20))
111begin
112declare id INT;
113declare name VARCHAR(50);
114declare egn VARCHAR(20);
115declare phone VARCHAR(20);
116declare status VARCHAR(20);
117declare people_cursor cursor for
118  SELECT people.id, people.name, people.egn, people.phone, people.status
119  FROM people
120  WHERE people.status = person_status;
121  
122  OPEN people_cursor;
123  LOOP
124     FETCH people_cursor INTO id,name,egn,phone,status;
125     SELECT concat('Id is: ', id) AS Id, 
126     concat('Name is: ', name) AS Name, 
127     concat('EGN is: ', egn) AS EGN,
128     concat('Phone is: ', phone) AS Phone, 
129     concat('Status is: ', status) AS Status;
130     #Може и да обработим данните ред по ред, точно за това се ползва курсора
131     end loop;
132     close people_cursor;
133end
134|
135DELIMITER ;
136
137call peopleCursor('Single');