P0v0f6SS

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