CREATE TABLE shows (
    id_show INT PRIMARY KEY,
    cidade VARCHAR(100),
    pais VARCHAR(100),
    data_show DATE,
    id_turne INT
);

CREATE TABLE musicas (
    id_musica INT PRIMARY KEY,
    nome_musica VARCHAR(100),
    duracao TIME,
    id_album INT
);

CREATE TABLE albuns (
    id_album INT PRIMARY KEY,
    nome_album VARCHAR(100),
    ano_lancamento INT
);

CREATE TABLE turnes (
    id_turne INT PRIMARY KEY,
    nome_turne VARCHAR(100)
);

CREATE TABLE show_musica (
    id_show INT,
    id_musica INT
);

INSERT INTO shows VALUES
(1, 'São Paulo', 'Brasil', '2025-03-10', 1),
(2, 'Londres', 'Inglaterra', '2025-04-15', 1),
(3, 'Berlim', 'Alemanha', '2025-05-20', 2),
(4, 'Paris', 'França', '2025-06-05', 2),
(5, 'Tóquio', 'Japão', '2025-07-12', 3);

INSERT INTO musicas VALUES
(10, 'Creep', '00:03:58', 1),
(20, 'Karma Police', '00:04:24', 2),
(30, 'No Surprises', '00:03:49', 2),
(40, 'Paranoid Android', '00:06:23', 2),
(50, 'High and Dry', '00:04:17', 1);

INSERT INTO albuns VALUES
(1, 'The Bends', 1995),
(2, 'OK Computer', 1997),
(3, 'Kid A', 2000);

INSERT INTO turnes VALUES
(1, 'World Tour 2025'),
(2, 'Europe Live'),
(3, 'Asia Experience');

INSERT INTO show_musica VALUES
(1,10),
(1,20),
(1,40),
(2,20),
(2,30),
(3,40),
(3,30),
(4,10),
(4,50),
(5,20),
(5,30);

SELECT s.cidade, s.pais
FROM shows s
ORDER BY s.cidade;

SELECT s.cidade AS Cidade, s.pais AS Pais, s.data_show AS Data
FROM shows s
ORDER BY s.pais;

SELECT m.nome_musica, a.nome_album, a.ano_lancamento
FROM musicas m
INNER JOIN albuns a 
ON m.id_album = a.id_album
ORDER BY m.nome_musica;

CREATE VIEW vw_shows AS 
SELECT cidade, pais, data_show
FROM shows
ORDER BY data_show;

SELECT *
FROM vw_shows;

CREATE VIEW vw_show_musicas AS
SELECT s.cidade, m.id_musica
FROM shows s
INNER JOIN show_musica sm 
ON s.id_show = sm.id_show 
    INNER JOIN musicas m 
    ON sm.id_musica = m.id_musica 
ORDER BY sm.id_musica;

SELECT * FROM vw_show_musicas;

CREATE VIEW vw_musicas_albuns AS 
SELECT s.nome_musica, a.nome_album, a.ano_lancamento
FROM musicas s 
INNER JOIN albuns a 
ON s.id_album = a.id_album
ORDER BY a.ano_lancamento;

SELECT * FROM vw_musicas_albuns;

CREATE VIEW vw_turnes_shows AS
SELECT t.nome_turne, s.cidade, s.pais, s.data_show
FROM shows s 
INNER JOIN turnes t 
ON s.id_turne = t.id_turne
ORDER BY s.data_show;

SELECT * FROM vw_turnes_shows;