z8ghT5KS

1CREATE TABLE HISTORICO_EXECUCAO(
2	ID INT NOT NULL PRIMARY KEY IDENTITY,
3	USUARIO_ID INT NOT NULL,
4	MUSICA_ID INT NOT NULL
5);
6
7ALTER TABLE HISTORICO_EXECUCAO 
8ADD CONSTRAINT FK_HISTORICO_EXECUCAO_USUARIO
9FOREIGN KEY(USUARIO_ID)
10REFERENCES USUARIO(ID);
11
12ALTER TABLE HISTORICO_EXECUCAO 
13ADD CONSTRAINT FK_HISTORICO_EXECUCAO_MUSICA
14FOREIGN KEY(MUSICA_ID)
15REFERENCES MUSICA(ID);
16
17CREATE TABLE FAIXAS_PAGAMENTO(
18	ID INT NOT NULL PRIMARY KEY IDENTITY,
19	NOME VARCHAR(20) NOT NULL,
20	INICIO INT NOT NULL,
21	FIM INT,
22	VALOR DECIMAL(14,4) NOT NULL
23);
24
25INSERT INTO FAIXAS_PAGAMENTO(NOME, INICIO, FIM, VALOR)
26VALUES('Faixa 1', 1,  10,   0.0099), 
27	  ('Faixa 2', 11, 20,   0.0121), 
28	  ('Faixa 3', 21, 30,   0.0154), 
29	  ('Faixa 4', 31, 40,   0.0293), 
30	  ('Faixa 5', 41, NULL, 0.05367);
31
32CREATE OR ALTER PROCEDURE EXECUTAR_MUSICA(@MUSICA_ID INT, @USUARIO_ID INT)
33AS
34BEGIN
35INSERT INTO HISTORICO_EXECUCAO(MUSICA_ID, USUARIO_ID) VALUES (@MUSICA_ID, @USUARIO_ID);
36END;
37
38EXEC EXECUTAR_MUSICA 1,1;
39EXEC EXECUTAR_MUSICA 1,1;
40EXEC EXECUTAR_MUSICA 1,1;
41EXEC EXECUTAR_MUSICA 1,1;
42EXEC EXECUTAR_MUSICA 1,1;
43EXEC EXECUTAR_MUSICA 1,1;
44
45EXEC EXECUTAR_MUSICA 1,2;
46EXEC EXECUTAR_MUSICA 1,3;
47EXEC EXECUTAR_MUSICA 1,5;
48EXEC EXECUTAR_MUSICA 2,1;
49EXEC EXECUTAR_MUSICA 3,1;
50EXEC EXECUTAR_MUSICA 5,1;
51
52DROP TABLE IF EXISTS  #EXECUCOES_AGRUPADAS
53SELECT
54	MUSICA.ID AS 'MUSICA_ID',
55	CANTOR.ID AS 'CANTOR_ID',
56	COUNT(*) AS 'QTD_EXECUCOES'
57INTO #EXECUCOES_AGRUPADAS
58FROM
59	HISTORICO_EXECUCAO
60INNER JOIN MUSICA ON HISTORICO_EXECUCAO.MUSICA_ID =  MUSICA.ID
61INNER JOIN USUARIO ON HISTORICO_EXECUCAO.USUARIO_ID = USUARIO.ID
62INNER JOIN MUSICA_CANTOR ON MUSICA.ID = MUSICA_CANTOR.MUSICA_ID
63INNER JOIN CANTOR ON MUSICA_CANTOR.CANTOR_ID = CANTOR.ID
64
65GROUP BY MUSICA.ID,
66		MUSICA.NOME,
67		CANTOR.ID,
68		CANTOR.NOME
69
70SELECT * FROM  #EXECUCOES_AGRUPADAS