CREATE DATABASE cd;
USE cd;

CREATE TABLE MUSICA(
    cod_musica INT NOT NULL PRIMARY KEY,
    nome_musica VARCHAR(255) NOT NULL,
    duracao_musica DECIMAL(4, 2)
);

CREATE TABLE CD_CATEGORIA(
    cod_categoria INT NOT NULL PRIMARY KEY,
    menor_preco DECIMAL(8,2) NOT NULL,
    maior_preco DECIMAL(8,2) NOT NULL
);

CREATE TABLE GRAVADORA(
    cod_gravadora INT NOT NULL PRIMARY KEY,
    nome_gravadora VARCHAR(255) NOT NULL,
    endereco_gravadora VARCHAR(255) NOT NULL,
    tel_gravadora VARCHAR(20) NOT NULL,
    contato_gravadora VARCHAR(50) NOT NULL,
    url_gravadora VARCHAR(50) NOT NULL
);

CREATE TABLE CD(
    cod_cd INT NOT NULL PRIMARY KEY,
    cod_gravadora INT NOT NULL FOREIGN KEY REFERENCES gravadora(cod_gravadora),
    nome_cd VARCHAR(255) NOT NULL,
    preco_venda DECIMAL(8,2) NOT NULL,
    data_lancamento DATETIME NOT NULL,
    cd_indicado INT NOT NULL -- FK
);

CREATE TABLE FAIXA(
    cod_cd INT NOT NULL FOREIGN KEY REFERENCES CD(cod_cd),
    num_faixa INT NOT NULL PRIMARY KEY,
    cod_musica INT NOT NULL FOREIGN KEY REFERENCES MUSICA(cod_musica),
);

CREATE TABLE AUTOR(
    cod_autor INT NOT NULL PRIMARY KEY,
    nome_autor VARCHAR(255) NOT NULL
);

CREATE TABLE MUSICA_AUTOR(
    cod_musica INT NOT NULL FOREIGN KEY REFERENCES MUSICA(cod_musica),
    cod_autor INT NOT NULL FOREIGN KEY REFERENCES AUTOR(cod_autor)
);

CREATE TABLE ITEM_CD(
    num_faixa INT NOT NULL FOREIGN KEY REFERENCES FAIXA(num_faixa),
    cod_musica INT NOT NULL FOREIGN KEY REFERENCES MUSICA(cod_musica),
    cod_cd INT NOT NULL FOREIGN KEY REFERENCES CD(cod_cd)
);

ALTER TABLE MUSICA_AUTOR add FOREIGN KEY(cod_musica) references MUSICA(cod_musica);
ALTER TABLE MUSICA_AUTOR add FOREIGN KEY(cod_autor) references AUTOR(cod_autor);
ALTER TABLE CD add FOREIGN KEY(cd_indicado) references CD(cod_cd);
ALTER TABLE FAIXA add FOREIGN KEY(cod_musica) references MUSICA(cod_musica);
ALTER TABLE item_cd ADD FOREIGN KEY(cod_musica) REFERENCES musica(cod_musica);

INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (1, 'Será', 02.28);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (2, 'Ainda é cedo', 03.55);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (3, 'Geração Coca-Cola', 02.20);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (4, 'Eduardo e Mônica', 04.32);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (5, 'Tempo Perdido', 05.00);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (6, 'Indios', 04.23);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (7, 'Que país é esse', 03.04);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (8, 'Faroeste Caboclo', 09.03);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (9, 'Há Tempos', 03.16);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (10, 'Pais e Filho', 03.22);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (11, 'Meninos e Meninas', 03.22);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (12, 'Vento no Litoral', 06.05);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (13, 'Perfeição', 04.35);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (14, 'Giz', 03.20);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (15, 'Dezesseis', 05.28);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (16, 'Antes das Seis', 03.09);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (17, 'Meninos, Eu vi', 03.25);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (18, 'Eu te amo', 03.06);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (19, 'Piano na mangueira', 02.23);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (20, 'A Violeira', 02.54);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (21, 'Anos Dourados', 02.56);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (22, 'Olha, Maria', 03.55);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (23, 'Biscate', 03.20);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (24, 'Retrato em Preto e Branco', 03.03);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (25, 'Falando de Amor', 03.20);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (26, 'Pois é', 02.48);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (27, 'Noite dos mascarados', 02.42);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (28, 'Sabiá', 03.20);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (29, 'Imagina', 02.52);
INSERT INTO MUSICA (cod_musica, nome_musica, duracao_musica) VALUES (30, 'Bate-Boca', 04.41);

INSERT INTO AUTOR (cod_autor, nome_autor) VALUES (1, 'Renato Russo');
INSERT INTO AUTOR (cod_autor, nome_autor) VALUES (2, 'Tom Jobim');
INSERT INTO AUTOR (cod_autor, nome_autor) VALUES (3, 'Chico Buarque');
INSERT INTO AUTOR (cod_autor, nome_autor) VALUES (4, 'Dado Villa-Lobos');
INSERT INTO AUTOR (cod_autor, nome_autor) VALUES (5, 'Marcelo Bonfá');
INSERT INTO AUTOR (cod_autor, nome_autor) VALUES (6, 'Ico Ouro Preto');
INSERT INTO AUTOR (cod_autor, nome_autor) VALUES (7, 'Vinícius de Moraes');

INSERT INTO GRAVADORA
    (cod_gravadora, nome_gravadora, endereco_gravadora, tel_gravadora, contato_gravadora, url_gravadora) VALUES
    (1, 'EMI', 'ROD. DUTRA, KM 29,8', '55659856', '8965-7894', 'emi@emimusic.com.br');
INSERT INTO GRAVADORA
    (cod_gravadora, nome_gravadora, endereco_gravadora, tel_gravadora, contato_gravadora, url_gravadora) VALUES
    (2, 'POLYGRAM', 'ROD. IMIGRANTES, KM 29,8', '99659856', '7595-7894', 'polygram@music.com.br');
INSERT INTO GRAVADORA
    (cod_gravadora, nome_gravadora, endereco_gravadora, tel_gravadora, contato_gravadora, url_gravadora) VALUES
    (3, 'BMG', 'ROD. ANCHIETA, KM 289,8', '99654456', '7595-7004', 'bmg@music.com.br');
INSERT INTO GRAVADORA
    (cod_gravadora, nome_gravadora, endereco_gravadora, tel_gravadora, contato_gravadora, url_gravadora) VALUES
    (4, 'SOM LIVRE', 'AV SAO JOSE, KM 289,8', '99657806', '7895-7004', 'somlivre@music.com.br');
INSERT INTO GRAVADORA
    (cod_gravadora, nome_gravadora, endereco_gravadora, tel_gravadora, contato_gravadora, url_gravadora) VALUES
    (5, 'EPIC', 'AV ALDA, N 89', '58793321', '8965-4790', 'epic@music.com.br');

INSERT INTO CD (cod_cd, cod_gravadora, nome_cd, preco_venda, data_lancamento, cd_indicado) VALUES
    (1, 1, 'Mais do Mesmo', -5.00, '1985-02-03', 1);
INSERT INTO CD (cod_cd, cod_gravadora, nome_cd, preco_venda, data_lancamento, cd_indicado) VALUES
    (2, 3, 'Bate Boca', -15.00, '1999-08-07', 2);
INSERT INTO CD (cod_cd, cod_gravadora, nome_cd, preco_venda, data_lancamento, cd_indicado) VALUES
    (3, 4, 'Elis Regina - Essa Mulher', -18.00, '1986-05-25', 3);
INSERT INTO CD (cod_cd, cod_gravadora, nome_cd, preco_venda, data_lancamento, cd_indicado) VALUES
    (4, 3, 'A força que nunca seca', 5.25, '1981-07-15', 4);
INSERT INTO CD (cod_cd, cod_gravadora, nome_cd, preco_venda, data_lancamento, cd_indicado) VALUES
    (5, 4, 'Perfil', 20.99, '1987-01-02', 5);
INSERT INTO CD (cod_cd, cod_gravadora, nome_cd, preco_venda, data_lancamento, cd_indicado) VALUES
    (6, 2, 'Barry Manilow Greastest Hits', 25.00, '1958-12-18', 1);
INSERT INTO CD (cod_cd, cod_gravadora, nome_cd, preco_venda, data_lancamento, cd_indicado) VALUES
    (7, 5, 'Listen Without Prejudice', 28.00, '1975-06-11', 7);

INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (1, 10, 20);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (2, 20, 40);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (3, 30, 60);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (4, 11, 22);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (5, 15, 30);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (6, 25, 50);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (7, 21, 42);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (8, 22, 44);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (9, 9, 18);
INSERT INTO CD_CATEGORIA (cod_categoria, menor_preco, maior_preco) VALUES (10, 5, 10);

INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 1, 1);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 2, 2);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 3, 3);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 4, 4);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 5, 5);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 6, 6);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 7, 7);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 8, 8);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 9, 9);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 10, 10);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 11, 11);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 12, 12);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 13, 13);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 14, 14);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 15, 15);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (1, 16, 16);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 1, 17);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 2, 18);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 3, 19);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 4, 20);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 5, 21);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 6, 22);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 7, 23);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 8, 24);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 9, 25);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 10, 26);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 11, 27);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 12, 28);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 13, 29);
INSERT INTO FAIXA (cod_cd, cod_musica, num_faixa) VALUES (2, 14, 30);

INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (1, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (1, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (1, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (2, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (2, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (2, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (2, 6);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (3, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (4, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (5, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (6, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (7, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (8, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (9, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (9, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (10, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (10, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (10, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (11, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (11, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (11, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (12, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (12, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (12, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (13, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (13, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (13, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (14, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (14, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (14, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (15, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (15, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (15, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (16, 1);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (16, 4);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (16, 5);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (17, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (17, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (18, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (18, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (19, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (19, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (20, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (20, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (21, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (21, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (22, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (22, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (22, 7);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (23, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (24, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (24, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (25, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (26, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (26, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (27, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (28, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (28, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (29, 2);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (29, 3);
INSERT INTO MUSICA_AUTOR (cod_musica, cod_autor) VALUES (30, 3);

ALTER TABLE GRAVADORA ALTER COLUMN endereco_gravadora VARCHAR(255) NULL

INSERT INTO GRAVADORA
    (cod_gravadora, nome_gravadora, endereco_gravadora, tel_gravadora, contato_gravadora, url_gravadora) VALUES
    (6, 'SONY', NULL, '78946512', '8796-1234', 'sony@sony.com.br');
INSERT INTO GRAVADORA
    (cod_gravadora, nome_gravadora, endereco_gravadora, tel_gravadora, contato_gravadora, url_gravadora) VALUES
    (7, 'Globo', NULL, '8797123', '1231-1234', 'globo@globo.com.br');
INSERT INTO GRAVADORA
    (cod_gravadora, nome_gravadora, endereco_gravadora, tel_gravadora, contato_gravadora, url_gravadora) VALUES
    (8, 'Play Music', NULL, '86433793', '7136-6395', 'playmusic@playmusic.com.br');

SELECT cod_gravadora, nome_gravadora, endereco_gravadora FROM GRAVADORA WHERE endereco_gravadora IS NULL;
SELECT * FROM GRAVADORA WHERE endereco_gravadora IS NOT NULL;
SELECT * FROM CD WHERE data_lancamento BETWEEN '1980-01-01' and '1989-12-31';

SELECT * FROM MUSICA WHERE cod_musica IN (1, 3, 11, 17, 30, 33);

SELECT * FROM MUSICA WHERE nome_musica LIKE 'R%';
SELECT * FROM MUSICA WHERE nome_musica LIKE 'A%';
SELECT * FROM MUSICA WHERE nome_musica LIKE '%S';
SELECT * FROM MUSICA WHERE nome_musica LIKE '%E%';
SELECT * FROM MUSICA WHERE nome_musica LIKE '_O%';
SELECT * FROM MUSICA WHERE nome_musica LIKE '%O_';
SELECT * FROM MUSICA WHERE nome_musica LIKE '_I_';
SELECT * FROM MUSICA WHERE nome_musica LIKE '___A%';
SELECT * FROM MUSICA WHERE nome_musica LIKE '_____E%';

SELECT * FROM MUSICA;
SELECT * FROM AUTOR;
SELECT * FROM GRAVADORA;
SELECT * FROM CD;
SELECT * FROM CD_CATEGORIA;
SELECT * FROM FAIXA;
SELECT * FROM MUSICA_AUTOR ;


SELECT AVG(preco_venda) FROM CD;

SELECT SUM(duracao_musica) FROM MUSICA;

SELECT MIN(num_faixa)FROM FAIXA;

SELECT MAX(cod_musica) AS musica, MAX(cod_autor) AS autor FROM MUSICA_AUTOR;

SELECT COUNT(*) FROM MUSICA;

SELECT VAR(preco_venda) FROM CD;

SELECT STDEV(duracao_musica) FROM MUSICA;

SELECT AVG(preco_venda), cod_gravadora
FROM CD
WHERE cod_gravadora > 3
GROUP BY cod_gravadora
HAVING AVG(preco_venda) >= 10
ORDER BY cod_gravadora;



SELECT *, LOWER(url_gravadora) FROM gravadora;
SELECT *, UPPER(nome_autor) FROM autor;
SELECT *, LEN(nome_musica) FROM musica;
SELECT *, LEFT(endereco_gravadora, 3) FROM gravadora;
SELECT *, RIGHT(nome_cd, 5) FROM cd;

SELECT UPPER(nome_cd) FROM cd WHERE LEN(nome_cd) > 10;
SELECT LEFT(nome_autor, 6) FROM AUTOR;
SELECT SUBSTRING(nome_musica, 11, 7) FROM MUSICA;
SELECT ASCII('Fatec');
SELECT ASCII('F');
SELECT ASCII('a');

SELECT cod_musica, SUBSTRING(nome_musica, 9, 4) FROM MUSICA;
SELECT cod_musica, SUBSTRING(nome_musica, 5, 4) FROM MUSICA;
SELECT cod_musica, SUBSTRING(nome_musica, 4, 5) FROM MUSICA;
SELECT cod_musica, SUBSTRING(nome_musica, 6, 4) FROM MUSICA;
SELECT cod_musica, SUBSTRING(nome_musica, 10, 2) FROM MUSICA;
SELECT cod_musica, SUBSTRING(nome_musica, 3, 3) FROM MUSICA;
SELECT cod_musica, SUBSTRING(nome_musica, 10, 6) FROM MUSICA;

SELECT ASCII('R') + ASCII('O') + ASCII('N') + ASCII('A') + ASCII('l') + ASCII('d');

DECLARE @input as varchar(100);
DECLARE @count as INT;
DECLARE @i INT
SET @input = 'RONAld'
SET @count = 0
SELECT @i = 0
WHILE @i < LEN(@input)
BEGIN
SELECT @i = @i + 1
SELECT @count = @count + ASCII(SUBSTRING(@input, @i, 1))
END
SELECT @count


SELECT UPPER(nome_gravadora), LOWER(endereco_gravadora), LOWER(url_gravadora)
FROM GRAVADORA
WHERE endereco_gravadora LIKE 'rod%';


SELECT DATEADD(MONTH, 2, data_lancamento) FROM cd;
SELECT DATEADD(QUARTER, 2, '2006-03-06');
SELECT GETDATE();
SELECT data_lancamento, GETDATE(), DATEDIFF(YEAR, data_lancamento, GETDATE()) FROM cd;
SELECT DATEDIFF(DAY, '2006-03-06', GETDATE());

SELECT MONTH(GETDATE());
SELECT YEAR(GETDATE());
SELECT DAY(GETDATE());

SELECT DATEPART(QUARTER, data_lancamento) FROM CD;


SELECT DATENAME(MONTH, GETDATE());



SELECT
DATENAME(DAY, '1993-02-02') as 'day',
DATENAME(MONTH, '1993-02-02') as 'month',
DATENAME(YEAR, '1993-02-02') as 'year',
DATENAME(HOUR, '1993-02-02 11:30:00') as 'hour';


SELECT
(DATEDIFF(DAY, '1993-02-02 11:30:00', GETDATE()) % 365) as 'days',
(DATEDIFF(MONTH, '1993-02-02 11:30:00', GETDATE()) % 12) as 'months',
(DATEDIFF(YEAR, '1993-02-02 11:30:00', GETDATE())) as 'years';


-- Subquery
-- Busca dentro de outra query
-- Sintaxe:
-- SELECT colunas FROM tabela WHERE col expresao operador (SELECT col FROM tabela WHERE ...)
-- onde:
-- col: a mesma coluna em ambos SELECT's
-- operador: linha(<, <=, >, >=, =, <>)
--             grupo(in, any, all)

-- Subquery linha

-- ex1
SELECT nome_cd, preco_venda FROM cd WHERE preco_venda > (SELECT AVG(preco_venda) FROM cd)

-- ex2
SELECT cod_gravadora, nome_cd, preco_venda FROM cd
WHERE cod_gravadora = (SELECT cod_gravadora FROM cd WHERE cod_CD = 2)
AND preco_venda > (SELECT preco_venda FROM cd WHERE cod_cd = 5)

-- ex3
SELECT cod_gravadora, MIN(preco_venda) FROM cd
GROUP BY cod_gravadora
HAVING MIN(preco_venda) > (SELECT preco_venda FROM cd WHERE cod_cd = 6)

-- Desafios
-- 1) Apresente as musicas (cd, nome) do cd 1
SELECT cod_musica, nome_musica FROM MUSICA
WHERE cod_musica IN (SELECT cod_musica FROM FAIXA WHERE cod_cd = 1)

-- 2) Apresente os autores das musicas pares
SELECT * FROM AUTOR
WHERE cod_autor IN (SELECT cod_autor FROM MUSICA_AUTOR WHERE (cod_musica % 2) = 0)

-- 3) Quais as musicas da gravadora 2 e 3
SELECT * FROM MUSICA
WHERE cod_musica IN (SELECT cod_musica FROM FAIXA WHERE cod_cd IN (SELECT cod_cd FROM CD WHERE CD.cod_gravadora IN (1, 2)))

SELECT * FROM musica WHERE cod_musica
IN (SELECT cod_musica FROM faixa WHERE cod_cd
    IN (SELECT cod_cd FROM cd WHERE cod_gravadora
        IN (SELECT cod_gravadora FROM CD WHERE cod_gravadora = 2 OR cod_gravadora = 3)))

-- 4) Quais os autores da gravadora 'EMI'
SELECT * FROM autor
WHERE cod_autor IN (SELECT cod_autor FROM MUSICA_AUTOR
    WHERE cod_musica IN (SELECT cod_musica FROM faixa
        WHERE cod_cd = (SELECT cod_cd FROM CD
            WHERE cod_gravadora = (SELECT cod_gravadora FROM GRAVADORA
                WHERE nome_gravadora LIKE 'EMI'))))


SELECT cod_autor cod, nome_autor nome
FROM autor
WHERE cod_autor < 10
UNION
SELECT cod_musica, nome_musica
FROM musica
WHERE cod_musica < 15;

SELECT cod_musica, nome_musica
FROM musica
WHERE cod_musica IN (SELECT cod_musica FROM faixa WHERE cod_cd = 1)
UNION ALL
SELECT cod_musica, nome_musica
FROM musica
WHERE cod_musica % 2 = 0;

SELECT cod_musica, nome_musica
FROM musica
WHERE cod_musica IN (SELECT cod_musica FROM faixa WHERE cod_cd = 1)
INTERSECT
SELECT cod_musica, nome_musica
FROM musica
WHERE cod_musica % 2 = 0;



-- Desafios
-- 1) Quais os autores e músicas do cd1?
SELECT cod_autor, nome_autor
FROM autor
WHERE cod_autor IN (
    SELECT cod_autor
    FROM musica_autor
    WHERE cod_musica IN (
        SELECT cod_musica
        FROM faixa
        WHERE cod_cd = 1))
UNION ALL
SELECT cod_musica, nome_musica
FROM musica
WHERE cod_musica IN (SELECT cod_musica FROM faixa WHERE cod_cd = 1);

-- 2) Quais os cd's e gravadoras que tem o preço maior que a média de preço das gravadoras 2 e 3?
SELECT cod_gravadora, nome_cd
FROM cd
WHERE cod_gravadora IN (SELECT cod_gravadora FROM cd WHERE cod_cd IN (2, 3))
UNION
SELECT cod_cd, nome_cd FROM cd WHERE preco_venda > (SELECT AVG(preco_venda) FROM cd)

-- TODO
-- 3) Apresente os cd's, musicas, autores e faixas das músicas pares com duração ímpar

SELECT cod_cd FROM cd
WHERE cod_cd IN (
    SELECT cod_cd
    FROM faixa
    WHERE cod_musica IN (
        SELECT cod_musica
        FROM musica
        WHERE cod_musica % 2 -0 and duracao % 2 = 1
    )
)
UNION ALL
SELECT cod_musica FROM musica
SELECT cod_autor FROM autor
SELECT cod_faixa FROM faixa


-- DESAFIOS
-- 1) Apresente os autores que não gravaram músicas no cd 2
SELECT cod_autor, nome_autor
FROM autor
WHERE cod_autor IN (
    SELECT cod_autor
    FROM musica_autor
    WHERE cod_musica IN (
        SELECT cod_cd, cod_musica
        FROM musica
        WHERE cod_musica IN (
            SELECT cod_musica
            FROM faixa
        )
    )
)
EXCEPT
SELECT cod_cd, nome_cd FROM cd WHERE cod_cd = 2
-- 2) Quais músicas que não tem autores impares
-- 3) Quais cd's não foram gravados?
-- 4) Quais gravadoras não tem cd?


-- 1) Quais os autores "cd, musica"do cd 1
SELECT autor.nome_autor, musica.nome_musica, cd.nome_cd
FROM autor LEFT OUTER JOIN musica_autor
ON autor.cod_autor = musica_autor.cod_autor RIGHT OUTER JOIN musica
ON musica_autor.cod_musica = musica.cod_musica LEFT OUTER JOIN faixa
ON faixa.cod_musica = musica.cod_musica RIGHT OUTER JOIN cd
ON faixa.cod_cd = cd.cod_cd
WHERE faixa.cod_cd = 1

-- 2) Quais as gravadoras "cod, musica" do cd 2?
SELECT cd.nome_cd, musica.nome_musica
FROM cd RIGHT OUTER JOIN faixa
ON cd.cod_faixa = faixa.cod_gravadora
WHERE cd.cod_cd = 2

-- 3) quais as musicas "autor" dos autores impares?
SELECT musica.nome_musica, autor.nome_autor
FROM musica LEFT OUTER JOIN musica_autor
ON musica.cod_musica = musica_autor.cod_musica RIGHT OUTER JOIN autor
ON musica_autor.cod_autor = autor.cod_autor
WHERE ((autor.cod_autor % 2) = 1)

-- 4) Acresentar 3 gravadoras, 3 musicas e 3 autores (itens isolados)

/*
Subrotinas
Stored Procedures

Realiza tarefas especificas:

CREATE PROCEDURE nome (parametro | entrada saida) AS
    sintaxe;

onde parametros de entrada INPUT
onde parametros de saida OUTPUT

para declarar: DECLARE @var tipo
para atribuir valor: SET @var = valor
para calculo: SET @var = @var + @num

operadores de decisão
IF/ELSE

para mais de 1 linha de comando

BEGIN
END

para loop WHILE

para saida PRINT()
*/

-- EX 01
CREATE PROCEDURE sp_saudacao AS
DECLARE @msg VARCHAR(50)
SET @msg = 'Hello World'
PRINT @msg

EXEC sp_saudacao;

-- EX 02
CREATE PROCEDURE proc_soma AS
DECLARE @num01 INT, @num02 INT, @res INT
SET @num01 = 5
SET @num02 = 6
SET @res = @num01 + @num02
PRINT @res

ALTER PROCEDURE proc_soma @num01 INT, @num02 INT AS
DECLARE @res INT
SET @res = @num01 + @num02
PRINT @res

ALTER PROCEDURE proc_soma @num01 INT, @num02 INT, @res INT OUTPUT AS
SET @res = @num01 + @num02;


DECLARE @saida INT;
EXEC proc_soma 5, 5, @saida OUTPUT;
PRINT @saida;


/*
Desafio
EXEC sp_calculation '+', 13, 28;
EXEC sp_calculation '-', 3, 14; => se não: Troca num para valor positivo
EXEC sp_calculation '*', 6, 3;
EXEC sp_calculation '/', 12, 1; => se não: Troca num para valor e R
EXEC sp_calculation 'p', 7, 2; => primo
EXEC sp_calculation 'i', 5, 4; => par / impar
*/

DROP PROCEDURE sp_calculation;

CREATE PROCEDURE sp_calculation @operation CHAR(1), @num01 INT, @num02 INT, @res INT OUTPUT AS
    IF (@operation = '+')
        SET @res = @num01 + @num02
    ELSE IF (@operation = '-')
        SET @res = @num01 - @num02
    ELSE IF (@operation = '*')
        SET @res = @num01 * @num02
    ELSE IF (@operation = '/')
    BEGIN
        IF (@num01 > @num02)
            SET @res = @num01 / @num02
        ELSE
            SET @res = @num02 / @num01
    END


DECLARE @saida INT
EXEC sp_calculation '+', 13, 28, @saida OUTPUT
PRINT @saida;

DECLARE @saida INT
EXEC sp_calculation '-', 3, 14, @saida OUTPUT
PRINT @saida;

DECLARE @saida INT
EXEC sp_calculation '*', 6, 3, @saida OUTPUT
PRINT @saida;

DECLARE @saida INT
EXEC sp_calculation '/', 1, 12, @saida OUTPUT
PRINT @saida;


DROP PROCEDURE sp_odd_even

CREATE PROCEDURE sp_odd_even @num INT, @res CHAR(5) OUTPUT AS
    IF (@num % 2 = 0)
        SET @res = 'True'
    ELSE
        SET @res = 'False'

DECLARE @saida CHAR(5)
EXEC sp_odd_even 1, @saida OUTPUT
PRINT @saida;

DECLARE @saida CHAR(5)
EXEC sp_odd_even 2, @saida OUTPUT
PRINT @saida;


DROP PROCEDURE sp_prime

CREATE PROCEDURE sp_prime @num INT, @res CHAR(5) OUTPUT AS
    DECLARE @cont INT
    DECLARE @totaldiv INT
    SET @cont = 1
    SET @totaldiv = 0

    WHILE (@num <= @cont)
    BEGIN
        BEGIN
            IF (@num % @cont = 0)
                SET @totaldiv = @totaldiv + 1
                PRINT (@num % @cont)
        END
        SET @cont = @cont + 1
    END

    IF (@totaldiv = 2)
        SET @res = 'True'
    ELSE
        SET @res = 'False'


DECLARE @saida CHAR(5)
EXEC sp_prime 4, @saida OUTPUT
PRINT @saida;


CREATE PROCEDURE sp_buscamusica @tempo DECIMAL(4, 2) AS
    SELECT * FROM musica WHERE duracao_musica >= @tempo;

EXEC sp_buscamusica 1.00

CREATE PROCEDURE sp_inseremusica @cod INT, @nome VARCHAR(255), @tempo DECIMAL(4, 2) AS
    INSERT INTO musica(cod_musica, nome_musica, duracao_musica) VALUES (@cod, @nome, @tempo);

EXEC sp_inseremusica 31, 'foo bar', 5.32

CREATE PROCEDURE sp_alteramusica @cod INT, @nome VARCHAR(255) AS
    UPDATE musica SET nome_musica = @nome WHERE cod_musica = @cod;

EXEC sp_alteramusica 31, 'Foo Bar'

CREATE PROCEDURE sp_excluimusica @cod INT AS
    DELETE FROM musica WHERE cod_musica = @cod;

EXEC sp_excluimusica 31