-
Notifications
You must be signed in to change notification settings - Fork 0
/
desafio1.sql
139 lines (125 loc) · 4.51 KB
/
desafio1.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
DROP DATABASE IF EXISTS SpotifyClone;
CREATE DATABASE SpotifyClone;
CREATE TABLE SpotifyClone.artists (
artista_id INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(45) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE SpotifyClone.plans (
plano_id INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(45) NOT NULL,
valor DECIMAL(3, 2) NOT NULL
) ENGINE = InnoDB;
CREATE TABLE SpotifyClone.Albuns (
album_id INT AUTO_INCREMENT PRIMARY KEY,
titulo VARCHAR(45) NOT NULL,
artista_id INT NOT NULL,
ano_lancamento YEAR NOT NULL,
FOREIGN KEY (artista_id) REFERENCES SpotifyClone.artists (artista_id)
) ENGINE = InnoDB;
CREATE TABLE SpotifyClone.Users (
usuario_id INT AUTO_INCREMENT PRIMARY KEY,
nome VARCHAR(45) NOT NULL,
idade TINYINT NOT NULL,
plano_id INT NOT NULL,
data_assinatura DATE NOT NULL DEFAULT(CURRENT_DATE),
FOREIGN KEY (plano_id) REFERENCES SpotifyClone.plans (plano_id)
) ENGINE = InnoDB;
CREATE TABLE SpotifyClone.musics (
data_reproduction INT AUTO_INCREMENT PRIMARY KEY,
titulo VARCHAR(45) NOT NULL,
duracao_seg INT NOT NULL,
album_id INT NOT NULL,
artista_id INT NOT NULL,
FOREIGN KEY (album_id) REFERENCES SpotifyClone.Albuns (album_id),
FOREIGN KEY (artista_id) REFERENCES SpotifyClone.artists (artista_id)
) ENGINE = InnoDB;
CREATE TABLE SpotifyClone.reproductions (
usuario_id INT NOT NULL,
data_reproduction INT NOT NULL,
data_reproducao DATETIME NOT NULL DEFAULT(CURRENT_TIMESTAMP),
CONSTRAINT PRIMARY KEY (usuario_id, data_reproduction),
FOREIGN KEY (usuario_id) REFERENCES SpotifyClone.Users (usuario_id),
FOREIGN KEY (data_reproduction) REFERENCES SpotifyClone.musics (data_reproduction)
) ENGINE = InnoDB;
CREATE TABLE SpotifyClone.followings (
usuario_id INT NOT NULL,
artista_id INT NOT NULL,
CONSTRAINT PRIMARY KEY (usuario_id, artista_id),
FOREIGN KEY (usuario_id) REFERENCES SpotifyClone.Users (usuario_id),
FOREIGN KEY (artista_id) REFERENCES SpotifyClone.artists (artista_id)
) ENGINE = InnoDB;
INSERT INTO SpotifyClone.artists (nome)
VALUES ('Beyoncé'),
('Queen'),
('Elis Regina'),
('Baco Exu do Blues'),
('Blind Guardian'),
('Nina Simone');
INSERT INTO SpotifyClone.plans (nome, valor)
VALUES ('gratuito', 0.00),
('pessoal', 6.99),
('familiar', 7.99),
('universitario', 5.99);
INSERT INTO SpotifyClone.Albuns (titulo, artista_id, ano_lancamento)
VALUES ('Renaissance', 1, 2022),
('Jazz', 2, 1978),
('Hot Space', 2, 1982),
('Falso Brilhante', 3, 1998),
('Vento de Maio', 3, 2001),
('QVVJFA?', 4, 2003),
('Somewhere Far Beyond', 5, 2007),
('I Put A Spell On You', 6, 2012);
INSERT INTO SpotifyClone.Users (nome, idade, plano_id, data_assinatura)
VALUES ('Barbara Liskov', 82, 1, '2019-10-20'),
('Robert Cecil Martin', 58, 1, '2017-01-06'),
('Ada Lovelace', 37, 3, '2017-12-30'),
('Martin Fowler', 46, 3, '2017-01-17'),
('Sandi Metz', 58, 3, '2018-04-29'),
('Paulo Freire', 19, 4, '2018-02-14'),
('Bell Hooks', 26, 4, '2018-01-05'),
('Christopher Alexander', 85, 2, '2019-06-05'),
('Judith Butler', 45, 2, '2020-05-13'),
('Jorge Amado', 58, 2, '2017-02-17');
INSERT INTO SpotifyClone.musics (titulo, duracao_seg, album_id, artista_id)
VALUES ('BREAK MY SOUL', 279, 1, 1),
('VIRGO’S GROOVE', 369, 1, 1),
('ALIEN SUPERSTAR', 116, 1, 1),
('Don’t Stop Me Now', 203, 2, 2),
('Under Pressure', 152, 3, 2),
('Como Nossos Pais', 105, 4, 3),
('O Medo de Amar é o Medo de Ser Livre', 207, 5, 3),
('Samba em Paris', 267, 6, 5),
('The Bard’s Song', 244, 7, 5),
('Feeling Good', 100, 8, 6);
INSERT INTO SpotifyClone.reproductions (usuario_id, data_reproduction, data_reproducao)
VALUES (1, 8, '2022-02-28 10:45:55'),
(1, 2, '2020-05-02 05:30:35'),
(1, 10, '2020-03-06 11:22:33'),
(2, 10, '2022-08-05 08:05:17'),
(2, 7, '2020-01-02 07:40:33'),
(3, 10, '2020-11-13 16:55:13'),
(3, 2, '2020-12-05 18:38:30'),
(4, 8, '2021-08-15 17:10:10'),
(5, 8, '2022-01-09 01:44:33'),
(5, 5, '2020-08-06 15:23:43'),
(6, 7, '2017-01-24 00:31:17'),
(6, 1, '2017-10-12 12:35:20'),
(7, 4, '2011-12-15 22:30:49'),
(8, 4, '2012-03-17 14:56:41'),
(9, 9, '2022-02-24 21:14:22'),
(10, 3, '2015-12-13 08:30:22');
INSERT INTO SpotifyClone.followings (usuario_id, artista_id)
VALUES (1, 1),
(1, 2),
(1, 3),
(2, 1),
(2, 3),
(3, 2),
(4, 4),
(5, 5),
(5, 6),
(6, 6),
(6, 1),
(7, 6),
(9, 3),
(10, 2);