/*exercicio1*/

create table categorias
(codCategoria integer auto_increment not null,
nome varchar(45) not null,
descricao varchar(45) not null,
PRIMARY KEY(codCategoria));

create table Produtos
(codProduto integer auto_increment not null,
codCategoria integer not null,
nome varchar(45) not null,
preco double(4,4) not null,
PRIMARY KEY(codProduto),
FOREIGN KEY (codCategoria) references categorias(codCategoria));

create table Clientes
(codCliente integer auto_increment not null,
nome varchar(45) not null,
telefone varchar(45) not null,
limiteCredito double (4,0) not null,
PRIMARY KEY(codCliente));

create table Pedidos
(numPedido integer auto_increment not null,
codCliente integer not null,
data date not null,
PRIMARY KEY(numPedido),
foreign key (codCliente) references clientes(codCliente));

create table ProdutoPedido
(numPedido integer not null,
codCliente integer not null,
dataa date not null,
quantidade integer not null,
PRIMARY KEY(codCliente, numPedido),
foreign key(codCliente) references clientes(codCliente),
foreign key(numPedido) references pedidos(codCliente));

/*bd_publicacoes*/

CREATE DATABASE publicacoes;

USE publicacoes;

create table Autores
(codAutor integer AUTO_INCREMENT NOT NULL,
nome varchar(45) not null,
dataNascimento date,
PRIMARY KEY(codAutor));

DESCRIBE livros;

create tablE editoras
(codEditora integer AUTO_INCREMENT NOT NULL,
razaoSocial varchar(45) not null,
CNPJ varchar(14) not null,
endereco varchar(80),
PRIMARY KEY (codEditora),
UNIQUE KEY (CNPJ));

CREATE TABLE livros
(titulo varchar(45) not null,
codAutor integer not null,
codEditora integer not null,
valor float not null,
edicao integer,
PRIMARY KEY (titulo, codAutor),
foreign key (codAutor) references autores(codAutor),
foreign key (codEditora) references editoras(codEditora));

alter table autores
add nacionalidade varchar(45);

alter table autores
modify column nome varchar(60);

describe autores;

INSERT INTO autores(nome,dataNascimento)
values ('Autora do harry pote', '1839-06-21');

select * from autores;

insert into editoras(razaoSocial,CNPJ, endereco)
values('Livraria Garnier', '11111111111', 'Rua Brasil, 180');

insert into livros (titulo,codAutor, codEditora, valor, edicao)
values('Dom Casmuro', '1','1','30.0',1);

UPDATE autores
SET nome = 'joaquim maria machado de assis'
WHERE codAutor = 2;

select * from autores order by nome DESC;

select * FROM autores where nome in ('Mauricio de Souza');

select * from autores where nome like 'a%';

select * from editoras where endereco is null;

select codAutor AS ID FROM autores;

select distinct valor from livros;

/*CREATE DATABASE livraria_prof;*/

USE livraria_prof;

CREATE TABLE IF NOT EXISTS editoras (
    codEditora int AUTO_INCREMENT PRIMARY KEY,
    nome varchar(80)
);

CREATE TABLE IF NOT EXISTS assuntos (
    sigla char PRIMARY KEY,
    descricao varchar(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS autores (
    matricula int PRIMARY KEY,
    nome varchar(80) NOT NULL,
    CPF character(11) NOT NULL,
    endereco varchar(100) NOT NULL,
    dataNascimento date NOT NULL,
    nacionalidade varchar(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS livros (
    codLivro int AUTO_INCREMENT PRIMARY KEY,
    titulo varchar(80) NOT NULL,
    preco float,
    lancamento date,
    assunto char NOT NULL,
    editora int NOT NULL,
    FOREIGN KEY (assunto) REFERENCES assuntos(sigla),
    FOREIGN KEY (editora) REFERENCES editoras(codEditora)
);

CREATE TABLE IF NOT EXISTS autores_livros (
    codLivro int NOT NULL,
    matriculaAutor int NOT NULL,
    PRIMARY KEY (codLivro, matriculaAutor),
    FOREIGN KEY (codLivro) REFERENCES livros(codLivro),
    FOREIGN KEY (matriculaAutor) REFERENCES autores(matricula)
);


INSERT INTO editoras(nome) VALUES
('Mirandela Editora'),
('Editora Via-Norte'),
('Editora Ilhas Tijucas'),
('MJ Editora');

INSERT INTO assuntos(sigla, descricao) VALUES
('B', 'Banco de Dados'),
('P', 'Programação'),
('R', 'Redes'),
('S', 'Sistemas Operacionais');

INSERT INTO livros(titulo, preco, lancamento, assunto, editora) VALUES
('Banco de Dados para a Web', 131.2, '2015-01-10', 'B', 1),
('Programando em Linguagem C', 130, '2007-10-01', 'P', 1),
('Programando em Linguagem C++', 110.5, '2002-11-01', 'P', 3),
('Bancos de Dados na Bioinformática', 90, 'null', 'B', 2),
('Redes de Computadores', 72, '2011-03-07', 'R', 2);


INSERT INTO autores(matricula, nome, CPF, endereco, dataNascimento, nacionalidade) VALUES
(123, 'John Smith', '11111111111', 'Rua Brasil', '1963-04-03', 'Canadense'),
(456, 'Adam Silva', '22222222222', 'Rua Canadá', '1983-06-13', 'Brasileira'),
(789, 'Matias Rodriguez', '33333333333', 'Rua Argentina', '1976-08-06', 'Argentina');

INSERT INTO autores_livros(codLivro, matriculaAutor) VALUES
(1, 456),
(1, 789),
(2, 123),
(3, 123),
(4, 456),
(5, 789);

select * from editoras;
select * from assuntos;
select * from autores;

select titulo, descricao  /*inner join, não aparece o nulo */
from livros
inner join assuntos
on assunto = sigla
where lancamento is not null;

select titulo, nome, descricao
from livros
inner join editoras E
on editora = E.codEditora
inner join assuntos
on assunto = sigla;

/*select pnome,unome from funcionarios SILVA
WHERE .pnome = 'joao' and F.unome = 'silva';


SELECT pnr from funcionarios F,TRABALHA_EM T
WHERE F.pnome = 'joao' and F.unome = 'silva' and T.fcpf = F.cpf ;*/

select nome, titulo
from editoras  E
inner join livros
on editora = E.codEditora
where lancamento is not null
order by nome, titulo;

/*junção externa*/
select nome, titulo
from editoras E
left outer join livros
on editora = E.codEditora
order by nome, titulo;

select descricao, titulo
from assuntos
left outer join livros
on sigla = assunto
order by descricao;

/*junção externa a direita*/
select descricao, titulo
from livros
right outer join assuntos
on sigla = assunto
order by descricao;

/*junção externa completa*/
select titulo, nome
from livros
full join editoras
on editoras.codEditora = editora
order by titulo;

/*Letra A*/
select titulo, preco ,nome
from livros inner join editoras E
on E.codEditora = livros.editora
where livros.preco < 100 ;

/*Letra B*/
select nome as NomeAutor, titulo as Título
from autores A inner join autores_livros L on
A.matricula = L.matriculaAutor
inner join livros Li on
Li.codLivro = L.codLivro
order by NomeAutor;

/*Letra C*/
select E.nome AS Nome_Editora from editoras E
inner join autores_livros AL
inner join livros L
inner join autores A
on AL.codLivro = L.codLivro and AL.matriculaAutor = A.matricula and L.editora = E.codEditora
where A.nome = 'Adam Silva';

/*Letra D*/
select count(A.nome) as quantidade_smith from livros L
INNER JOIN autores_livros AL on AL.codLivro = L.codLivro
inner join autores A on AL.matriculaAutor = A.matricula
where A.nome like '%Smith%';

/*Letra E*/
select max(L.preco) as livro_mais_caro_mirandela from livros L
inner join editoras E on E.codEditora = L.editora
inner join assuntos A on L.assunto = A.sigla
where E.nome = 'Mirandela Editora' and A.sigla = 'P';

/*Letra F*/
select nome NOME from editoras
left join livros L on codEditora = L.editora where L.editora is null;