FFWgugK9

1DROP DATABASE IF EXISTS streaming;
2CREATE DATABASE streaming;
3USE streaming;
4
5CREATE TABLE Utente (
6Nickname varchar(25),
7Nome varchar(15) NOT NULL,
8Cognome varchar(15) NOT NULL,
9DnD DATE,
10Via varchar(30) NOT NULL,
11Citta varchar(20) NOT NULL,
12CAP int NOT NULL,
13Tipo ENUM('STREAMER','UTENTE') NOT NULL,
14PRIMARY KEY (Nickname),
15UNIQUE (Via,Citta,CAP)
16);
17
18CREATE TABLE Video (
19IDVideo smallint NOT NULL,
20Titolo varchar (40) NOT NULL,
21Genere varchar (20) NOT NULL,
22Punteggio bigint DEFAULT 0,
23Views bigint DEFAULT 0,
24Lingua varchar (10) NOT NULL,
25PRIMARY KEY (IDVideo)
26);
27
28CREATE TABLE Visualizzazione (
29Nickname varchar(25) NOT NULL,
30IDVideo smallint NOT NULL,
31Gradimento ENUM('DISLIKE','NO_ACTION','LIKE') DEFAULT 'NO_ACTION',
32current_data DATE,
33FOREIGN KEY (Nickname) REFERENCES Utente(Nickname),
34FOREIGN KEY (IDVideo) REFERENCES Video(IDVideo),
35PRIMARY KEY (Nickname,IDVideo,current_data)
36);
37
38CREATE TABLE Premium (
39Nickname varchar(25),
40abbonamento_inizio DATE NOT NULL,
41abbonamento_fine DATE NOT NULL,
42FOREIGN KEY (Nickname) REFERENCES Utente(Nickname),
43PRIMARY KEY (Nickname)
44);
45
46CREATE TABLE Commento (
47Nickname varchar (25),
48IDVideo smallint NOT NULL,
49current_data DATE,
50Testo varchar (255) NOT NULL,
51FOREIGN KEY (Nickname) REFERENCES Utente(Nickname),
52FOREIGN KEY (IDVideo) REFERENCES Video(IDVideo),
53PRIMARY KEY (Nickname,IDVideo,current_data)
54);
55
56/*Inserimento dati*/
57INSERT INTO Utente(Nickname,Nome,Cognome,DnD,Via,Citta,CAP,Tipo) VALUES
58('Arthur', 'Arturo', 'Pezza', '1998-02-18', 'Viale Paolino 12', 'Torino', 82112, 'UTENTE'),
59('Maykol404', 'Maykol', 'Faschion', '1984-08-21', 'Via Scorrimento 78', 'Milano', 51243, 'UTENTE'),
60('Mike', 'Mike', 'Tyson', '1980-02-24', 'Via Limonti 74', 'Napoli', 21453, 'UTENTE'),
61('Reby01', 'Rebecca', 'Ultimo', '2002-02-18', 'Viale Traiano 54', 'Napoli', 82115, 'UTENTE'),
62('Luke', 'Luca', 'Aldroandi', '1999-06-21', 'Via Napoli 21', 'Roma', 12342, 'STREAMER'),
63('Giorgino01', 'Giorgio', 'Armani', '1969-01-01', 'Via Asus 17', 'Calabria', 41245, 'STREAMER'),
64('Fiddy99', 'Giulio', 'Tulliver', '1999-06-21', 'Via Unisa 21', 'Puglia', 85144, 'STREAMER');
65
66INSERT INTO Video(IDVideo,Titolo,Genere,Lingua) VALUES
67(101, '2Scratch - Reminder', 'Musica', 'Inglese'),
68(102, 'OGM - Hold Me', 'Musica', 'Inglese'),
69(103, '2Scratch - Sober', 'Musica', 'Inglese'),
70(104, 'Fortnite', 'Videogame', 'Inglese'),
71(105, 'Call Of Duty', 'Videogame', 'Inglese'),
72(106, 'Frozen', 'Film', 'Italiano'),
73(107, 'DoS', 'Hacking', 'Francese');
74
75INSERT INTO Visualizzazione(Nickname, IDVideo, Gradimento, current_data) VALUES
76('Arthur', 101, 'LIKE', '2005-02-04'),
77('Arthur', 102, 'NO_ACTION','2005-02-06'),
78('Mike', 105, 'DISLIKE', '2006-01-12'),
79('Fiddy99', 103, 'DISLIKE', '2010-08-27'),
80('Luke', 104, 'DISLIKE', '2018-10-22'),
81('Luke', 101, 'LIKE', '2007-09-11'),
82('Reby01', 103, 'LIKE', '2018-11-19');
83
84
85INSERT INTO Premium (Nickname,abbonamento_inizio,abbonamento_fine) VALUES
86('Reby01', '2021-01-01', '2021-04-01'),
87('Luke', '2021-01-12', '2021-04-12');
88
89INSERT  Commento (Nickname, IDVideo,current_data, Testo) VALUES
90('Reby01', '103', '2018-11-19', 'Bellissima canzone!'),
91('Luke', '104', '2018-10-22', 'Non mi è piaciuto!'),
92('Luke', '101', '2007-09-11', 'Da scaricare :P');
93
94/*Selezione ordinata su un attributo di una tabella con condizioni AND e OR*/
95SELECT Nickname, COUNT(*) AS TotVisualizzazione
96FROM Visualizzazione v
97WHERE (v.Nickname = 'Luke' OR v.Nickname = 'Reby01') AND (v.Gradimento = 'LIKE' OR v.Gradimento = 'DISLIKE')
98GROUP BY Nickname
99ORDER BY TotVisualizzazione DESC;
100
101/*Una selezione su due o più tabelle con condizioni*/
102SELECT p.Nickname, c.testo 
103FROM Premium p
104INNER JOIN Commento c ON p.Nickname = c.Nickname
105INNER JOIN Video v ON v.IDVideo = c.IDVideo
106WHERE v.Genere = 'Musica';
107
108/*Una selezione aggregata su tutti i valori (es. somma di tutti gli stipendi)*/
109SELECT Nickname, COUNT(IDVideo) AS count
110FROM Visualizzazione
111GROUP BY Nickname
112UNION ALL
113SELECT 'Total views:' Nickname, COUNT(IDVideo)
114FROM Visualizzazione;
115
116/*Una selezione aggregata su raggruppamenti (es. somma stipendi per dipartimenti)*/
117SELECT v.Nickname, COUNT(v.IDVideo) AS count
118FROM Visualizzazione v
119INNER JOIN Premium p ON p.Nickname = v.Nickname
120GROUP BY v.Nickname;
121
122/*Una selezione aggregata su raggruppamenti con condizioni (es. dipartimenti la cui somma degli stipendi dei dipendendi è > 100k)*/
123SELECT v.Nickname, COUNT(v.IDVideo) AS count
124FROM Visualizzazione v
125INNER JOIN Premium p ON p.Nickname = v.Nickname
126GROUP BY v.Nickname
127HAVING count >= 2;
128
129/*Una selezione aggregata su raggruppamenti con condizioni che includano un'altra funzione di raggruppamento (es. dipartimenti la cui somma degli stipendi è la più alta)*/
130CREATE VIEW Visualizzazioni (Titolo, Gradimento) AS
131SELECT vd.Titolo,sum(v.Gradimento) as Tot_Gradimento
132FROM Visualizzazione v
133INNER JOIN Video vd ON vd.IDVideo = v.IDVideo
134GROUP BY vd.Titolo;
135SELECT Titolo, Gradimento
136FROM Visualizzazioni
137WHERE Gradimento = (SELECT max(Gradimento) FROM Visualizzazioni);
138
139/*Una selezione con operatori sugli insiemi (IN oppure NOT IN) (elencare i video che non sono stati visualizzati) */
140SELECT *
141FROM Video vd
142WHERE vd.IDVideo not in(SELECT v.IDVideo FROM Visualizzazione v);
143
144/*L'uso del doppio NOT EXISTS*/
145SELECT *
146FROM Visualizzazione vd
147WHERE not exists (SELECT * 
148					FROM Video v
149                    WHERE not exists (SELECT *
150										FROM Commento c
151                                        WHERE vd.IDVideo = c.IDVideo AND vd.Nickname = c.Nickname));
152						
153