· 4 years ago · Jan 04, 2021, 04:26 PM
1-- ______ __ __
2-- /_ __/ ___ _ / / / / ___ ___
3-- / / / _ `/ / _ \ / / / -_) (_-<
4-- /_/ \_,_/ /_.__//_/ \__/ /___/
5--
6
7
8CREATE TABLE users
9(
10 user_id INTEGER NOT NULL,
11 first_name VARCHAR2(25) NOT NULL,
12 last_name VARCHAR2(25) NOT NULL,
13 gender CHAR(1) NOT NULL,
14 date_of_birth DATE NOT NULL,
15 date_of_death DATE,
16
17 CONSTRAINT users_pk PRIMARY KEY (user_id),
18 CONSTRAINT users_ck1 CHECK (date_of_death > date_of_birth),
19 CONSTRAINT users_ck2 CHECK (gender IN ('M', 'F'))
20);
21
22
23CREATE TABLE relationships
24(
25 relationship_id INTEGER NOT NULL,
26 type VARCHAR2(9) NOT NULL,
27
28 CONSTRAINT relationships_pk PRIMARY KEY (relationship_id),
29 CONSTRAINT relationships_ck CHECK (type IN ('family', 'work'))
30);
31
32
33CREATE TABLE membership
34(
35 relationship_id INTEGER NOT NULL,
36 user_id INTEGER NOT NULL,
37
38 CONSTRAINT membership_pk PRIMARY KEY (relationship_id, user_id)
39);
40
41
42CREATE TABLE contacts
43(
44 contact_id INTEGER NOT NULL,
45 location_id INTEGER NOT NULL,
46
47 CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
48);
49
50
51CREATE TABLE participants
52(
53 contact_id INTEGER NOT NULL,
54 user_id INTEGER NOT NULL,
55 date_received DATE NOT NULL,
56
57 CONSTRAINT participants_pk PRIMARY KEY (contact_id, user_id),
58 CONSTRAINT participants_uk UNIQUE (user_id, date_received)
59);
60
61
62CREATE TABLE locations
63(
64 location_id INTEGER NOT NULL,
65 name VARCHAR2(25) NOT NULL,
66 city VARCHAR2(25) NOT NULL,
67 category VARCHAR(14) NOT NULL,
68
69 CONSTRAINT locations_pk PRIMARY KEY (location_id),
70 CONSTRAINT locations_uk UNIQUE (name),
71 CONSTRAINT locations_ck CHECK (category IN
72 ('recreational', 'entertainment', 'religious', 'education', 'healthcare',
73 'transportation', 'business'))
74);
75
76/*
77 CATEGORIES / CONTEXT:
78 recreational: hotel, restaurants, bar, public meeting
79 entertainment: movie theatre, theatre, stadium, concert area, ski resort.
80 religious: churches, monasteries, sacred places
81 education: university, school, library
82 healthcare: hospital, private clinic
83 transportation: airport, train station, bus station, metro station
84 business: barber shop, private stores
85*/
86
87
88CREATE TABLE swabs
89(
90 swab_id INTEGER,
91 user_id INTEGER NOT NULL,
92 date_result DATE NOT NULL,
93 positivity CHAR(1) NOT NULL,
94
95 CONSTRAINT swabs_pk PRIMARY KEY (swab_id),
96 CONSTRAINT swabs_ck1 CHECK ( positivity IN ('y', 'n') ),
97 CONSTRAINT swabs_uk UNIQUE (user_id, date_result)
98);
99
100
101CREATE TABLE serologicals
102(
103 serological_id INTEGER,
104 user_id INTEGER NOT NULL,
105 date_result DATE NOT NULL,
106 igm CHAR(1) NOT NULL,
107 igg CHAR(1) NOT NULL,
108
109 CONSTRAINT serologicals_pk PRIMARY KEY (serological_id),
110 CONSTRAINT serologicals_uk UNIQUE (user_id, date_result),
111 CONSTRAINT serologicals_ck1 CHECK ( igm IN ('positive', 'negative') ),
112 CONSTRAINT serologicals_ck2 CHECK ( igg IN ('positive', 'negative') )
113);
114
115
116CREATE TABLE health_checks
117(
118 health_check_id INTEGER NOT NULL,
119 user_id INTEGER NOT NULL,
120 date_of_check DATE NOT NULL,
121 fever CHAR(1) NOT NULL,
122 respiratory_disorder CHAR(1) NOT NULL,
123 smell_taste_disorder CHAR(1) NOT NULL,
124
125 CONSTRAINT health_checks_ck1 CHECK (fever IN ('y', 'n')),
126 CONSTRAINT health_checks_ck2 CHECK (respiratory_disorder IN ('y', 'n')),
127 CONSTRAINT health_checks_ck3 CHECK (smell_taste_disorder IN ('y', 'n')),
128 CONSTRAINT health_checks_uk UNIQUE (user_id, date_of_check)
129);
130
131
132-- ___ __ __ ______ __ __
133-- / _ | / / / /_ ___ ____ /_ __/ ___ _ / / / / ___ ___
134-- / __ | / / / __// -_) / __/ / / / _ `/ / _ \ / / / -_) (_-<
135-- /_/ |_|/_/ \__/ \__/ /_/ /_/ \_,_/ /_.__//_/ \__/ /___/
136--
137
138
139ALTER TABLE membership
140 ADD
141 (
142 CONSTRAINT membership_relationships_fk FOREIGN KEY (relationship_id) REFERENCES relationships (relationship_id) ON DELETE CASCADE,
143 CONSTRAINT membership_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
144 );
145
146ALTER TABLE contacts
147 ADD
148 (
149 CONSTRAINT contacts_locations_fk FOREIGN KEY (location_id) REFERENCES locations (location_id) ON DELETE CASCADE
150 );
151
152ALTER TABLE participants
153 ADD
154 (
155 CONSTRAINT participants_contacts_fk FOREIGN KEY (contact_id) REFERENCES contacts (contact_id) ON DELETE CASCADE,
156 CONSTRAINT participants_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
157 );
158
159ALTER TABLE swabs
160 ADD (
161 CONSTRAINT swabs_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
162 );
163
164ALTER TABLE health_checks
165 ADD (
166 CONSTRAINT hc_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
167 );
168
169ALTER TABLE serologicals
170 ADD (
171 CONSTRAINT serological_users_fk FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
172 );
173
174
175-- ____
176-- / __/ ___ ___ _ __ __ ___ ___ ____ ___ ___
177-- _\ \ / -_)/ _ `// // // -_) / _ \/ __// -_) (_-<
178-- /___/ \__/ \_, / \_,_/ \__/ /_//_/\__/ \__/ /___/
179-- /_/
180
181
182CREATE SEQUENCE users_seq;
183CREATE SEQUENCE relationships_seq;
184CREATE SEQUENCE contacts_seq;
185CREATE SEQUENCE locations_seq;
186CREATE SEQUENCE swabs_seq;
187CREATE SEQUENCE serologicals_seq;
188CREATE SEQUENCE health_checks_seq;
189
190
191-- _ __ _
192-- | | / / (_) ___ _ __ ___
193-- | |/ / / / / -_)| |/|/ / (_-<
194-- |___/ /_/ \__/ |__,__/ /___/
195--
196
197
198-- all contacts between users (user_id1, user_id2, location_id, date_received) [used when inserting a new contact]
199CREATE OR REPLACE VIEW contacts_all_v AS
200SELECT p1.user_id AS user_id1,
201 p2.user_id AS user_id2,
202 c.location_id AS location_id,
203 p1.date_received AS date_received
204FROM contacts c
205 JOIN participants p1 ON c.contact_id = p1.contact_id
206 JOIN participants p2 ON p1.contact_id = p2.contact_id
207WHERE p1.user_id <> p2.user_id
208 AND p1.user_id < p2.user_id;
209
210
211-- all relationships (user_id1, user_id2, relationship type)
212CREATE OR REPLACE VIEW relationships_all_v AS
213SELECT m1.user_id AS user_id1, m2.user_id AS user_id2, r.type
214FROM relationships r
215 JOIN membership m1 ON r.relationship_id = m1.relationship_id
216 JOIN membership m2 ON m1.relationship_id = m2.relationship_id;
217
218
219-- ______ _
220-- /_ __/ ____ (_) ___ _ ___ _ ___ ____ ___
221-- / / / __/ / / / _ `/ / _ `// -_) / __/ (_-<
222-- /_/ /_/ /_/ \_, / \_, / \__/ /_/ /___/
223-- /___/ /___/
224
225
226CREATE OR REPLACE TRIGGER users_tr
227 BEFORE INSERT
228 ON users
229 FOR EACH ROW
230BEGIN
231 :new.user_id := users_seq.nextval;
232END;
233
234
235CREATE OR REPLACE TRIGGER locations_tr
236 BEFORE INSERT
237 ON locations
238 FOR EACH ROW
239BEGIN
240 :new.location_id := locations_seq.nextval;
241END;
242
243
244-- used when inserting a new contact between two users
245CREATE OR REPLACE TRIGGER contacts_all_v_tr
246 INSTEAD OF INSERT
247 ON contacts_all_v
248 FOR EACH ROW
249DECLARE
250 l_contact_pk INTEGER;
251BEGIN
252
253 FOR user_row IN (SELECT u.date_of_birth, u.date_of_death
254 FROM users u
255 WHERE u.user_id = :new.user_id1
256 OR u.user_id = :new.user_id2)
257 LOOP
258 -- check, for both users, if the date of contact is between the date_of_birth and date_of_death (excluding extremes)
259 IF (:new.date_received < user_row.date_of_birth) THEN
260 RAISE_APPLICATION_ERROR(-20001, 'date received < date of birth');
261 ELSIF (user_row.date_of_death IS NOT NULL) THEN
262 IF (:new.date_received > user_row.date_of_death) THEN
263 RAISE_APPLICATION_ERROR(-20001, 'date received > date of death');
264 END IF;
265 END IF;
266 END LOOP;
267
268 l_contact_pk := contacts_seq.nextval;
269 INSERT INTO contacts VALUES (l_contact_pk, :new.location_id);
270 INSERT INTO participants VALUES (l_contact_pk, :new.user_id1, :new.date_received);
271 INSERT INTO participants VALUES (l_contact_pk, :new.user_id2, :new.date_received);
272END;
273
274
275-- checks if user_id1 and user_id2 don't share already the same type of relationship
276CREATE OR REPLACE TRIGGER relationships_all_v_tr
277 INSTEAD OF INSERT
278 ON relationships_all_v
279 FOR EACH ROW
280DECLARE
281 l_relationship_pk INTEGER;
282 l_shared_relationship INTEGER;
283BEGIN
284 -- counts the number of relationships of the same type between user_id1 and user_id2
285 SELECT COUNT(*)
286 INTO l_shared_relationship
287 FROM relationships r
288 JOIN membership m1 ON r.relationship_id = m1.relationship_id
289 JOIN membership m2 ON m1.relationship_id = m2.relationship_id
290 WHERE m1.user_id = :new.user_id1
291 AND m2.user_id = :new.user_id2
292 AND r.type = :new.type;
293 IF (l_shared_relationship > 0) THEN
294 RAISE_APPLICATION_ERROR(-20001, 'a relationship of this type between these two users already exists');
295 ELSE
296 l_relationship_pk := relationships_seq.nextval;
297 INSERT INTO relationships VALUES (l_relationship_pk, :new.type);
298 INSERT INTO membership VALUES (l_relationship_pk, :new.user_id1);
299 INSERT INTO membership VALUES (l_relationship_pk, :new.user_id2);
300 END IF;
301END;
302
303
304CREATE OR REPLACE TRIGGER swabs_tr
305 BEFORE INSERT
306 ON swabs
307 FOR EACH ROW
308DECLARE
309 l_date_of_birth DATE;
310 l_date_of_death DATE;
311BEGIN
312 SELECT date_of_birth, date_of_death
313 INTO l_date_of_birth, l_date_of_death
314 FROM users
315 WHERE user_id = :new.user_id;
316 IF (:new.date_result < l_date_of_birth) THEN
317 RAISE_APPLICATION_ERROR(-20001, 'date result < date of birth');
318 ELSIF (l_date_of_death IS NOT NULL) THEN
319 IF (:new.date_result < l_date_of_death) THEN
320 RAISE_APPLICATION_ERROR(-20001, 'date result > date of death');
321 END IF;
322 END IF;
323 :new.swab_id := swabs_seq.nextval;
324END;
325
326
327CREATE OR REPLACE TRIGGER serologicals_tr
328 BEFORE INSERT
329 ON serologicals
330 FOR EACH ROW
331DECLARE
332 l_date_of_birth DATE;
333 l_date_of_death DATE;
334BEGIN
335 SELECT date_of_birth, date_of_death
336 INTO l_date_of_birth, l_date_of_death
337 FROM users
338 WHERE user_id = :new.user_id;
339 IF (:new.date_result < l_date_of_birth) THEN
340 RAISE_APPLICATION_ERROR(-20001, 'date result < date of birth');
341 ELSIF (l_date_of_death IS NOT NULL) THEN
342 IF (:new.date_result < l_date_of_death) THEN
343 RAISE_APPLICATION_ERROR(-20001, 'date result > date of death');
344 END IF;
345 END IF;
346 :new.serological_id := serologicals_seq.nextval;
347END;
348
349
350CREATE OR REPLACE TRIGGER health_checks_tr
351 BEFORE INSERT
352 ON health_checks
353 FOR EACH ROW
354DECLARE
355 l_date_of_birth DATE;
356 l_date_of_death DATE;
357BEGIN
358 SELECT date_of_birth, date_of_death
359 INTO l_date_of_birth, l_date_of_death
360 FROM users
361 WHERE user_id = :new.user_id;
362 IF (:new.date_of_check < l_date_of_birth) THEN
363 RAISE_APPLICATION_ERROR(-20001, 'date of check < date of birth');
364 ELSIF (l_date_of_death IS NOT NULL) THEN
365 IF (:new.date_of_check < l_date_of_death) THEN
366 RAISE_APPLICATION_ERROR(-20001, 'date of check > date of death');
367 END IF;
368 END IF;
369 :new.health_check_id := health_checks_seq.nextval;
370END;
371
372
373-- ____ __
374-- / _/ ___ ___ ___ ____ / /_ ___
375-- _/ / / _ \ (_-</ -_) / __// __/ (_-<
376-- /___/ /_//_//___/\__/ /_/ \__/ /___/
377--
378
379
380-- USERS
381INSERT INTO users (user_id, first_name, last_name, gender, date_of_birth, date_of_death)
382VALUES (0, 'Adriano', 'Peron', 'M', TO_DATE('14-05-2000', 'dd-mm-yyyy'), NULL); -- [1]
383INSERT INTO users
384VALUES (0, 'Massimo', 'Benerecetti', 'M', TO_DATE('15-05-1964', 'dd-mm-yyyy'), NULL); -- [2]
385INSERT INTO users
386VALUES (0, 'Piero', 'Bonatti', 'M', TO_DATE('20-07-1994', 'dd-mm-yyyy'), NULL); -- [3]
387INSERT INTO users
388VALUES (0, 'Alessandro', 'De Luca', 'F', TO_DATE('30-12-1975', 'dd-mm-yyyy'), NULL); -- [4]
389INSERT INTO users
390VALUES (0, 'Guido', 'Russo', 'M', TO_DATE('02-01-1980', 'dd-mm-yyyy'), NULL); -- [5]
391INSERT INTO users
392VALUES (0, 'Sergio', 'Di Martino', 'M', TO_DATE('19-09-1979', 'dd-mm-yyyy'), NULL); -- [6]
393INSERT INTO users
394VALUES (0, 'Guglielmo', 'Tamburrini', 'M', TO_DATE('06-12-1980', 'dd-mm-yyyy'), NULL); -- [7]
395INSERT INTO users
396VALUES (0, 'Daniel', 'Riccio', 'M', TO_DATE('29-11-1968', 'dd-mm-yyyy'), NULL); -- [8]
397INSERT INTO users
398VALUES (0, 'Alberto', 'Aloisio', 'M', TO_DATE('17-07-1992', 'dd-mm-yyyy'), NULL); -- [9]
399INSERT INTO users
400VALUES (0, 'Giovanni', 'Cutolo', 'M', TO_DATE('06-12-1957', 'dd-mm-yyyy'), NULL); -- [10]
401INSERT INTO users
402VALUES (0, 'Walter', 'Balzano', 'M', TO_DATE('22-10-2001', 'dd-mm-yyyy'), NULL); -- [11]
403INSERT INTO users
404VALUES (0, 'Aniello', 'Murano', 'M', TO_DATE('15-09-1952', 'dd-mm-yyyy'), NULL); -- [12]
405INSERT INTO users
406VALUES (0, 'Francesca', 'Cioffi', 'F', TO_DATE('13-06-1966', 'dd-mm-yyyy'), NULL); -- [13]
407INSERT INTO users
408VALUES (0, 'Paola', 'Festa', 'F', TO_DATE('27-10-1983', 'dd-mm-yyyy'), NULL); -- [14]
409INSERT INTO users
410VALUES (0, 'Francesco', 'Isgro', 'M', TO_DATE('07-08-1985', 'dd-mm-yyyy'), NULL); -- [15]
411INSERT INTO users
412VALUES (0, 'Roberto', 'Prevete', 'M', TO_DATE('20-05-1991', 'dd-mm-yyyy'), NULL); -- [16]
413INSERT INTO users
414VALUES (0, 'Luigia', 'Caputo', 'F', TO_DATE('02-09-1979', 'dd-mm-yyyy'), NULL); -- [17]
415INSERT INTO users
416VALUES (0, 'Francesco', 'Tramontano', 'M', TO_DATE('17-01-1969', 'dd-mm-yyyy'), NULL);
417-- [18]
418
419
420-- RELATIONSHIPS
421INSERT INTO relationships_all_v (user_id1, user_id2, type)
422VALUES (1, 2, 'work'); -- [1]
423INSERT INTO relationships_all_v (user_id1, user_id2, type)
424VALUES (4, 17, 'family'); -- [2]
425INSERT INTO relationships_all_v (user_id1, user_id2, type)
426VALUES (7, 18, 'work');
427-- [3]
428
429
430-- LOCATIONS
431INSERT INTO locations (location_id, name, city, category)
432VALUES (locations_seq.nextval, 'San Paolo', 'Napoli', 'entertainment'); -- [1]
433INSERT INTO locations
434VALUES (locations_seq.nextval, 'Monte S.Angelo', 'Napoli', 'education'); -- [2]
435INSERT INTO locations
436VALUES (locations_seq.nextval, 'Centro Direzionale', 'Napoli', 'business'); -- [3]
437INSERT INTO locations
438VALUES (locations_seq.nextval, 'Stazione Termini', 'Roma', 'transportation'); -- [4]
439INSERT INTO locations
440VALUES (locations_seq.nextval, 'Piazza Affari', 'Milano', 'business'); -- [5]
441
442
443-- CONTACTS
444INSERT INTO contacts_all_v (user_id1, user_id2, location_id, date_received)
445VALUES (1, 5, 1, TO_DATE('01-11-2020 17:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [1]
446INSERT INTO contacts_all_v
447VALUES (6, 2, 2, TO_DATE('02-11-2020 09:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [2]
448INSERT INTO contacts_all_v
449VALUES (3, 7, 4, TO_DATE('03-11-2020 20:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [3]
450INSERT INTO contacts_all_v
451VALUES (12, 14, 3, TO_DATE('09-11-2020 12:27:00', 'dd-mm-yyyy hh24:mi:ss')); -- [4]
452INSERT INTO contacts_all_v
453VALUES (13, 15, 1, TO_DATE('11-11-2020 10:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [5]
454INSERT INTO contacts_all_v
455VALUES (10, 13, 3, TO_DATE('12-11-2020 14:30:14', 'dd-mm-yyyy hh24:mi:ss')); -- [6]
456INSERT INTO contacts_all_v
457VALUES (11, 8, 2, TO_DATE('13-11-2020 14:50:14', 'dd-mm-yyyy hh24:mi:ss')); -- [7]
458INSERT INTO contacts_all_v
459VALUES (4, 9, 4, TO_DATE('15-11-2020 08:50:14', 'dd-mm-yyyy hh24:mi:ss')); -- [8]
460INSERT INTO contacts_all_v
461VALUES (14, 2, 3, TO_DATE('18-11-2020 19:50:14', 'dd-mm-yyyy hh24:mi:ss')); -- [9]
462INSERT INTO contacts_all_v
463VALUES (5, 2, 5, TO_DATE('18-11-2020 11:21:14', 'dd-mm-yyyy hh24:mi:ss')); -- [10]
464INSERT INTO contacts_all_v
465VALUES (4, 11, 3, TO_DATE('23-11-2020 10:11:14', 'dd-mm-yyyy hh24:mi:ss')); -- [11]
466INSERT INTO contacts_all_v
467VALUES (12, 10, 3, TO_DATE('24-11-2020 17:01:33', 'dd-mm-yyyy hh24:mi:ss')); -- [12]
468INSERT INTO contacts_all_v
469VALUES (9, 8, 2, TO_DATE('25-11-2020 21:10:00', 'dd-mm-yyyy hh24:mi:ss')); -- [13]
470INSERT INTO contacts_all_v
471VALUES (7, 1, 4, TO_DATE('28-11-2020 20:13:27', 'dd-mm-yyyy hh24:mi:ss')); -- [14]
472INSERT INTO contacts_all_v
473VALUES (3, 6, 1, TO_DATE('29-11-2020 15:31:07', 'dd-mm-yyyy hh24:mi:ss')); -- [15]
474INSERT INTO contacts_all_v
475VALUES (15, 13, 1, TO_DATE('29-11-2020 12:22:58', 'dd-mm-yyyy hh24:mi:ss')); -- [16]
476INSERT INTO contacts_all_v
477VALUES (16, 5, 3, TO_DATE('30-11-2020 16:02:38', 'dd-mm-yyyy hh24:mi:ss'));
478-- [17]
479
480
481-- ____ __ _
482-- / __/ __ __ ___ ____ / /_ (_) ___ ___ ___
483-- / _/ / // / / _ \/ __// __/ / / / _ \ / _ \ (_-<
484-- /_/ \_,_/ /_//_/\__/ \__/ /_/ \___//_//_//___/
485--
486
487
488-- ___ __
489-- / _ \ ____ ___ ____ ___ ___/ / __ __ ____ ___ ___
490-- / ___/ / __// _ \/ __// -_)/ _ / / // / / __// -_) (_-<
491-- /_/ /_/ \___/\__/ \__/ \_,_/ \_,_/ /_/ \__/ /___/
492--
493
494
495-- ___
496-- / _ \ ____ ___ ___ ___
497-- / // / / __// _ \ / _ \ (_-<
498-- /____/ /_/ \___/ / .__//___/
499-- /_/
500
501
502DROP SEQUENCE users_seq;
503DROP SEQUENCE relationships_seq;
504DROP SEQUENCE locations_seq;
505DROP SEQUENCE contacts_seq;
506DROP SEQUENCE swabs_seq;
507DROP SEQUENCE serologicals_seq;
508DROP SEQUENCE health_checks_seq;
509DROP TABLE membership CASCADE CONSTRAINTS;
510DROP TABLE relationships CASCADE CONSTRAINTS;
511DROP TABLE participants CASCADE CONSTRAINTS;
512DROP TABLE contacts CASCADE CONSTRAINTS;
513DROP TABLE locations CASCADE CONSTRAINTS;
514DROP TABLE swabs CASCADE CONSTRAINTS;
515DROP TABLE health_checks CASCADE CONSTRAINTS;
516DROP TABLE users CASCADE CONSTRAINTS;
517DROP TABLE serologicals CASCADE CONSTRAINTS;
518DROP VIEW contacts_all_v;
519DROP VIEW relationships_all_v;
520
521
522
523-- _ __ ____ ___
524-- | | /| / / / _/ / _ \
525-- | |/ |/ / _/ / / ___/
526-- |__/|__/ /___/ /_/
527--
528
529
530select *
531from locations;
532
533
534DELETE
535FROM users
536WHERE user_id = 3;
537
538
539DELETE
540FROM contacts;
541
542
543SELECT *
544FROM users;
545
546
547SELECT *
548FROM contacts_all_v;
549
550
551-- tutti i contatti (coppia di user, luogo, data)
552SELECT p1.user_id, p2.user_id, c.location_id, p1.date_received
553FROM contacts c
554 JOIN participants p1 ON c.contact_id = p1.contact_id
555 JOIN participants p2 ON p1.contact_id = p2.contact_id
556WHERE p1.user_id <> p2.user_id;
557
558
559-- calcola il numero di contatti di uno user nelle ultime 2 settimane (da controllare)
560SELECT COUNT(i.contact_id)
561FROM contacts i
562 JOIN participants p ON i.contact_id = p.contact_id
563WHERE p.user_id = 1
564 AND p.date_received BETWEEN TO_DATE('01-12-2020', 'dd-mm-yyyy') - 30 AND TO_DATE('01-12-2020', 'dd-mm-yyyy')
565ORDER BY p.date_received DESC;
566