Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
Curso SQL/Anota??es.docx · Banco de Dados · Tabelas · Campos (pedaços) · Codigo integer · Nome text · Nascimento date · Salario number · Registro · 200 produtos = 200 registros · Chave Primária – ÚNICO – Código – primeiro campo · Sqlite3 · .help, .exit, .tables, .schema · SQL · DDL – Data Definition Language · DML – Data Manipulation Language · Create Table · Insert into · Select · Drop table fornecedores; · Alter table agenda rename to amigos; · Alter table estudantes add estado text; · Inserir Informações · Insert · Visualizar · Select – Mais badalado · QUAL É A PERGUNTA? · Like · Ordenação – · Order by · Order by XXXXX desc · faixa · Excluir · Delete · Atualizar REGRAS PARA TABELAS: · Nomes no singular · O campo código se chamará id · A chave estrangeira será tabela_id COMO FAZER JOIN? 1) Escreva o Select com o FROM de todas as tabelas que precisa 2) Adicione um apelido para cada tabela 3) Faça as ligações com cuidado 4) Adicione outras restrições se necessário. VIEW = VISUALIZAÇÃO É um atalho para um select! INDEX = ÍNDICE Serve para acelerar buscas em uma tabela ÍNDICE ACELERA = ÍNDICE PRA TABELA TODA? Distinct – valores únicos LIMIT – Limitar a quantidade de registros retornados em um SELECT Subselect () Funções!! Trabalhar o resultado de um select! Diretamente! Consultar DEZENAS!! Data texto matemáticas 14 delas exemplificar! TRANSAÇÕES DO BANCO Tudo que acontece dentro de uma transação pode ser aceito ou revertido. Ou todas operações são completadas com sucesso ou nenhuma o é!! TUDO OU NADA · Exemplo – Automação Comercial · Clientes · Estoque · Fornecedores · Vendas · Faz uma venda com entrada + 3 parcelas – crediário · INÍCIO TRANSAÇÃO · Lança no caixa a entrada R$100 · Gera as parcelas e x R$50,00 · Baixa o estoque -1 · Histórico do Cliente · FIM TRANSAÇÃO – ok ou cancelar · Começa por begin; · Termina com Commit; ou Rollback; · · Base de 1.000.000 de linhas (registros · Padrão 1 commit por linha · 1 insert e depois 1 commit · Transações a cada 200.000 · Begin; · Insert · Commit; · .read dados.sql JOIN = ligação entre as tabelas Notação diferente = MAIS SIMPLES INNER JOIN OUTER JOIN = LEFT OUTER JOIN · Triggers = gatilhos · Podem ser disparados por: (EVENTOS) · Delete - OLD · Insert - NEW · Update – OLD e NEW · Executam quando? · AFTER · BEFORE · Como afetar os campos? · OLD.campo · NEW.campo · Qual um uso comum? · Tabelas de Auditoria! · SEGURANÇA · RASTREAR MUDANÇAS · Anotar a data de todas as inserções · Rastreabilidade!!! · Senha de Administrador!! Curso SQL/Arquivos usados nas aulas/funcoes.txt -- 14 Funções -- ifnull select titulo, estilo_id from livro; select titulo, ifnull(estilo_id,"Falta Código do Estilo") from livro; -- length Select length("Tiago"); select titulo, length(titulo) as "Tamanho do Título" from livro where id=1; -- lower select lower(titulo) from livro where id=1; -- upper() select upper(titulo) from livro where id=1; -- substr(x,y,z) select substr("Curso de SQL",3,3); select substr("Curso de SQL",8,2); -- random() select random(); select titulo, substr(random(),3,3) as codigo_inventado from livro; -- The replace(X,Y,Z) select replace("Curso de SQL"," ","---"); -- round(x) select titulo, precovenda from livro; select titulo, round(precovenda,1) from livro; -- trim() _remove espçaos do início e fim select trim(" Curso _ de _ S Q L "); -- rtrim() select rtrim(" Curso _ de _ S Q L "); -- ltrim() select ltrim(" Curso _ de _ S Q L "); -- typeof() select typeof(1.1); select typeof(" "); -- date('now'); select date('now'); -- sqlite_version select sqlite_version(); Curso SQL/Arquivos usados nas aulas/join.txt -- Joins -- Vendo as tabelas select * from livro; select * from estilo; -------------------------------------------------- -- Tradicional INNER JOIN select l.titulo, e.nome from livro l, estilo e where e.id = l.estilo_id; -- Outra forma de INNER JOIN select titulo, nome from estilo INNER JOIN livro ON estilo.id = livro.estilo_id; -- INNER JOIN = CONDIÇÃO SATISFEITA de ambos os lados! -------------------------------------------------- -- OUTER JOIN select titulo, nome from estilo LEFT OUTER JOIN livro ON estilo.id = livro.estilo_id; select titulo, nome from livro LEFT OUTER JOIN estilo ON estilo.id = livro.estilo_id; -- LEFT OUTER JOIN = MOSTRA MESMO QUE SEJA NULL!!!!!!!!!!!!!! Curso SQL/Arquivos usados nas aulas/transacoes.txt -- Transações -- Teste 1 begin transaction; insert into autor(id,nome) values (7,"Tiago"); rollback; -- Teste 2 begin transaction; insert into autor(id,nome) values (8,"Tiago"); commit; begin transaction; delete from autor where id=5; commit; commit; select * from autor; Curso SQL/Arquivos usados nas aulas/trigger.txt -- Nosso problema é o seguinte: -- REGISTRE POR FAVOR NA TABELA AUDITORIA -- Sempre que um novo autor for cadastrado! -- guarde a data por favor! -- Vamos criar nossa tabela de auditoria! create table auditoria (autor_id int, data date, acao text); drop table auditoria; -- Vamos criar nossa trigger de auditoria drop trigger auditoria; CREATE TRIGGER auditoria AFTER INSERT ON autor BEGIN INSERT INTO auditoria(autor_id,data, acao) VALUES (new.id, datetime('now'), "Autor inserido" ); END; -- Inserindo para testar! insert into autor(id,nome) values (10,"Ana"); -- Conferindo na auditoria select * from auditoria; -- Criando uma trigger para quando o cara for excluído! CREATE TRIGGER auditoria2 AFTER DELETE ON autor BEGIN INSERT INTO auditoria(autor_id,data, acao) VALUES (old.id, datetime('now'), "Autor excluído" ); END; -- Testando delete from autor where id=10; -- Conferindo na auditoria select * from auditoria; Curso SQL/Atividades/Lista 1 - Create Table - Drop Table - Alter Table - Copia.pdf Aprenda SQL Lista 1 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questõsqlitees. Não pare antes disso. 1) Execute o sqlite criando um banco de dados de nome lista1.sqlite. 2) Crie uma tabela com o nome de alunos. Deverá conter o campo código (inteiro), nome, telefone e cidade (texto). Vou te ajudar nessa: CREATE TABLE alunos (codigo int, nome text, telefone text, cidade text); 3) Use o comando .tables para verificar se a tabela foi criada 4) Crie uma tabela com o nome de alunos2. Deverá conter o campo código (inteiro), nome (varchar de tamanho 200), telefone (varchar de tamanho 50)e cidade (varchar de tamanho 100). Vou te ajudar nessa também. O comando ficará assim: CREATE TABLE alunos2 (codigo int, nome varchar(200), telefone varchar(50), cidade varchar(100) ); 5) Crie a tabela funcionários contendo os campos nome, endereço, telefone, cidade, estado, cep, rg, cpf e salário. Coloque os tipos de dados necessários. 6) Saia do sqlite com o comando .exit. 7) Abra novamente no sqlite o banco lista1.sqlite. 8) Verifique se as tabelas ainda existem com o comando .tables 9) Crie a tabela fornecedores contendo os campos nome, endereço, telefone, cidade, estado, cep, cnpj e email. Coloque os tipos de dados necessários. 10) Crie a tabela livros contendo o campo código, nome, categoria, resumo, precocusto, precovenda. 11) Existe uma maneira de verificar o ESQUEMA da tabela, ou seja, sua estrutura. É o comando .SCHEMA. 12) Crie a tabela estoque contendo o campo código, nomedoproduto, categoria, quantidade e fornecedor 13) Crie a tabela notas contendo os campos código, nomedoaluno, 1bim, 2bim, 3bim e 4bim 14) Crie a tabela caixa contendo os campos código, data, descrição, debito e credito. Aprenda SQL Lista 1 Prof. Tiago Baciotti Moreira 15) Crie a tabela contasAPagar contendo os campos código, data_conta, descrição, valor e data_pagamento. 16) Crie a tabela contasAReceber contendo os campos código, data_conta, descrição, valor e data_recebimento. 17) Crie a tabela filmes contendo os campos código, nome, sinopse, categoria e diretor 18) Crie a tabela CDs contendo os campos código, nome, cantor, ano e quantidademusicas. 19) Agora iremos aprender a excluir tabelas. É muito fácil. Basta usar o comando DROP TABLE. Se quero excluir a tabela alunos o comando fica assim: Drop table alunos;. Faça isso então, exclua a tabela alunos. 20) Use o comando .tables e veja se a tabela realmente foi excluída 21) Exclua a tabela livros. 22) Exclua a tabela contasAPagar. 23) Exclua também a tabela contasAReceber. 24) Agora apague a tabela filmes. 25) Liste as tabelas e veja se a tabela alunos2 ainda existe. 26) Agora iremos aprender como MUDAR O NOME das tabelas. É fácil, basta usar o comando ALTER TABLE. Por exemplo se quisermos mudar o nome da tabela NOMEFEIO para NOMEBONITO o comando ficará assim: ALTER TABLE NOMEFEIO RENAME TO NOMEBONITO; - Agora que você sabe disse renomeie a tabela alunos para super_alunos 27) Use o comando .tables e veja se foi alterado o nome. 28) Altere o nome da tabela estoque para produtos. 29) Altere o nome da tabela notas para aprovados. 30) Altere o nome da tabela aprovados para notas. Curso SQL/Atividades/Lista 2 - Create Table - Drop Table - Alter Table.pdf Aprenda SQL Lista 2 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questões. Não pare antes disso. 1) Altere o nome da tabela caixa para dinheiro. 2) Exclua a tabela dinheiro. 3) Exclua a tabela notas. 4) Altere o nome da tabela super_alunos para alunos. 5) Não gostei desse nome. Mude de alunos para estudantes. 6) Ficou feio mesmo assim. Altere o nome de estudantes para super_estudantes. 7) Veja se o nome foi alterado usando o comando .tables. 8) Exclua a tabela super_estudantes. 9) Agora crie novamente a tabela alunos usando o mesmo comando que usou no exercício 1. 10) Nós esquecemos que a tabela alunos precisa do campo estado! Precisamos alterar a estrutura da tabela incluindo o campo estado. Para isso iremos usar o comando alter table de novo. Veja como é fácil: ALTER TABLE ALUNOS ADD ESTADO TEXT; - Ou seja, nós informamos o nome da tabela e o novo campo junto com seu tipo! Faça isso agora! 11) Crie novamente a tabela caixa. 12) Adicione o campo observação do tipo text na tabela caixa. 13) Adicione o campo cpf na tabela alunos. 14) Veja a estrutura da tabela caixa 15) Adicione o campo saldo na tabela caixa. Aprenda SQL Lista 2 Prof. Tiago Baciotti Moreira 16) Adicione o campo rg na tabela alunos. 17) Veja a estrutura da tabela alunos. 18) Altere o nome da tabela caixa para muito_dinheiro 19) Acrescente o campo cliente na tabela muito_dinheiro. 20) Adicione o campo fornecedor na tabela muito_dinheiro 21) Mude o nome da tabela muito_dinheiro para caixa 22) Saia do sqlite. 23) Abra novamente o banco de dados lista1 no sqlite. 24) Veja a lista das tabelas existentes. 25) Exclua a tabela caixa 26) Exclua a tabela alunos 27) Insira o campo gravadora do tipo TEXT na tabela CDs 28) Mude o nome da tabela CDs para MeusCDs 29) Mude o nome da tabela MeusCDs para NossosCDs 30) Veja a estrutura da tabela NossosCDs Curso SQL/Atividades/Lista 3 - Insert - Select - Delete.pdf Aprenda SQL Lista 3 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questões. Não pare antes disso. 1) Leia do primeiro ao último exercício antes de começar a resolver. 2) Execute o sqlite criando um banco de dados de nome lista3.sqlite. 3) Crie uma tabela com o nome de alunos. Deverá conter o campo código (inteiro), nome, telefone e cidade (texto). Vou te ajudar nessa: CREATE TABLE alunos (codigo int, nome text, telefone text, cidade text); 4) Use o comando .tables para verificar se a tabela foi criada 5) Crie a tabela funcionários contendo os campos código, nome, endereço, telefone, cidade, estado, cep, rg, cpf e salário. Coloque os tipos de dados necessários. 6) Saia do sqlite com o comando .exit. 7) Abra novamente no sqlite o banco lista3.sqlite. 8) Verifique se as tabelas ainda existem com o comando .tables 9) Agora iremos trabalhar com o comando insert para inserir um novo registro ao banco de dados. Apenas para você lembrar o funcionamento dele iremos inserir um registro na tabela alunos: insert into alunos (código, nome, telefone, cidade) values (1,’Ana’,’9999-9999’,’Ituiutaba’); - Faça esse comando agora. 10) Precisamos agora verificar se o registro foi inserido corretamente. Então precisamos selecionar todos os dados da tabela alunos. Use o comando select desse jeito: select * from alunos; (lembre-se que o * aqui nesse caso significa todos os campos, ou seja, irá mostrar nome, endereço, código, etc). 11) Insira um novo registro na tabela alunos com os seus dados. 12) Selecione os registros da tabela alunos e veja se o registro foi inserido. 13) Ligue os cabeçalhos usando o comando .headers on 14) Selecione novamente para verificar se o cabeçalho foi mostrado corretamente. Aprenda SQL Lista 3 Prof. Tiago Baciotti Moreira 15) Insira na tabela alunos o aluno José Buscapé. 16) Selecione o conteúdo da tabela e veja se foi inserido corretamente. 17) Agora você vai aprender um novo recurso do comando select. Você pode escolher os CAMPOS que deseja que sejam exibidos. Por exemplo, se eu quiser exibir somente o código e o nome devo usar o comando assim: select codigo,nome from alunos; - Faça isso agora! 18) Selecione somente o nome e telefone dos alunos. 19) Selecione o nome e a cidade dos alunos 20) Selecione somente o código e o telefone dos alunos 21) Insira 4 novos alunos; 22) Selecione todos os campos da tabela alunos 23) Selecione da tabela alunos os seguintes campos (nessa ordem): cidade, código, nome. Veja que você pode exibir os dados na ordem que quiser. 24) Insira mais um alunos na tabela alunos. 25) Saia do sqlite, feche o terminal e abra novamente. 26) Selecione os dados da tabela a alunos e veja se ainda existem. 27) Adicione 1 novo funcionário. Lembre-se que é necessário usar aspas para campos TEXTO. Campos numéricos não podem ter aspas. Se o salário tiver centavos, lembre-se que deve separar os centavos com um (.) (ponto) pois a vírgula é usada para separar os valores a serem inseridos. 28) Selecione os dados da tabela funcionários e veja se foi inserido corretamente. 29) Cadastre 3 funcionários. Use código na sequência. (1,2,3,4,5 etc). 30) Selecione somente o código e nome dos funcionários. Curso SQL/Atividades/Lista 4 - Insert - Select - Delete.pdf Aprenda SQL Lista 4 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questões. Não pare antes disso. 1) Selecione somente o nome e salário dos funcionários. 2) Selecione somente o nome e telefone dos funcionários. 3) Selecione somente o nome, rg e cpf dos funcionários. 4) Selecione somente o nome e a cidade dos funcionários. 5) Insira mais um funcionário. 6) Exiba todos os dados dos funcionários. 7) Crie a tabela fornecedores contendo os campos nome, endereço, telefone, cidade, estado, cep, cnpj e email. Coloque os tipos de dados necessários. 8) Insira 2 fornecedores. Código 1 e Código 2 9) Selecione o nome e o telefone dos fornecedores. 10) Agora iremos aprender uma opção do comando select. Nós podemos restringir o que vai ser exibido na tela. É moleza. Por exemplo se eu quiser listar somente o aluno de código 2 o comando fica assim: select * from alunos where codigo = 2; - Nós apenas adicionamos o WHERE e a CONDIÇÃO. Veja que mantivemos o * para exibir todos os campos, mas poderíamos também exibir somente o nome do aluno 2 assim: select nome from alunos where codigo = 2; - EXPERIMENTE AGORA ESSES 2 COMANDOS. 11) Selecione o funcionário de código 3. 12) Selecione o fornecedor de código 1. 13) Selecione o aluno de código 2. 14) Selecione o funcionário de código 1. Aprenda SQL Lista 4 Prof. Tiago Baciotti Moreira 15) Selecione somente o nome e salário do funcionário de código 2. 16) Selecione somente o nome a cidade do aluno de código 1. 17) Selecione todos os funcionários de MG. É assim: select * from funcionários where estado=’MG’; - Como estado é texto eu usei aspas! – Faça isso agora! 18) Selecione todos os funcionários de GO. 19) Selecione todos os funcionários de SP. 20) Insira um funcionário para SP. 21) Selecione todos os funcionários de SP. 22) Crie a tabela livros contendo o campo código, nome, categoria, resumo, precocusto, precovenda. 23) Verifique o esquema .schema da tabela livros. 24) Liste as tabelas existentes. 25) Insira 1 livro. 26) Selecione o nome e a categoria do livro de código 1. 27) Selecione o nome e a categoria do livro de código 3. 28) Exclua a tabela livros. 29) Altere o nome da tabela aluno para estudantes. 30) Altere a tabela alunos inserindo o campo estado. Se estiver com dúvidas consulte a primeira lista de exercícios. Curso SQL/Atividades/Lista 5 - Select - Insert - Delete - TUDO.pdf Aprenda SQL Lista 5 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questões. Não pare antes disso. 1) Crie no SqliteStudio um banco de dados chamado lista3.sqlite e execute os comandos abaixo. 2) Crie uma tabela com o nome de livros contendo os campos codigo, titulo, codigo do autor, código da editora, código do estilo, sinopse e isbn. Sugestão de nome de campo: autor_codigo, editora_codigo 3) Crie uma tabela com o nome de editoras contendo o codigo, nome, cidade, estado, telefone e e-mail. 4) Crie uma tabela com o nome de estilos contendo o código e o nome do estilo. 5) Crie uma tabela com o nome de autores contendo o codigo, nome, cidade, estado, telefone do autor. 6) Insira um registro na tabela livros (todos os campos) 7) Insira um registro na tabela editoras (todos os campos). 8) Insira um registro na tabela estilos (todos os campos). 9) Insira um registro na tabela autores (todos os campos). 10) Altere o nome da tabela autores para autor. 11) Insira na tabela livros um novo registro adicionando somente os campos codigo e nome 12) Insira 5 estilos de livros (comédia, drama, ficção, suspense, romance). 13) Selecionar todos os livros do banco de dados. 14) Insira 2 novos livros. 15) Altere o nome da tabela livros autores para livro. 16) DESAFIO: Selecione o nome de todos os estilos em ordem alfabética 17) DESAFIO: Selecione o nome de todos os autores em ordem alfabética inversa Aprenda SQL Lista 5 Prof. Tiago Baciotti Moreira 18) Selecione o nome e o telefone de todas as editoras. 19) Selecione o nome de todas as editoras 20) Selecione o nome de todas as editoras de MG 21) Selecione os estilos de livros em ordem alfabética. 22) Selecione agora em ordem alfabética inversa. 23) Selecione o nome de todos os autores de SP. 24) Selecione o estilo de código 13 25) Selecione o autor de código 8 26) Selecione a editora de código 10 27) Selecione o nome, a cidade e o estado de todas as editoras. 28) Adicione 3 editoras. 29) Selecione o nome de todas as editoras 30) Exclua a editora de código 1 Curso SQL/Atividades/Lista 6 - Joins com o controle de livros.pdf Aprenda SQL Lista 6 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questões. Não pare antes disso. 1) Abra no SQLite Studio o banco de dados com o controle de livros que desenvolvemos. 2) Selecione o nome e o estilo de todos os livros 3) Selecione o nome e a editora de todos os livros 4) Selecione o nome e o autor de todos os livros 5) Selecione o nome e o estilo de todos os livros que começam por “A” 6) Selecione o nome e o estilo de todos os livros que cujo estilo comece por “R” 7) Selecione o nome do autor, da editora, do estilo e do livro de todos os livros de autores cujo nome comece por D. 8) Selecione o nome do autor, da editora, do estilo e do livro de todos os livros de editoras paulistas. 9) Atualize o autor do livro de id 1 para autor_id 2. 10) Atualize o telefone da editora de id 3 para 44 6666-6666 11) Atualize o nome do autor 8 para “Graciliano Ramos” 12) Atualize o estilo 5 para Fantasia. 13) Selecione o nome do livro e do estilo de todas as editoras de SP. 14) Selecione o nome do livro e da Editora de todas as editoras de SP. 15) Selecione o nome do livro e do autor de em ordem alfabética por autor. Curso SQL/Atividades/Lista 7 - Agrega??o.pdf Aprenda SQL Lista 7 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questões. Não pare antes disso. 1) Conte o número de editoras cadastradas 2) Calcule a média do preço de venda dos livros 3) Atualize o preço de venda do livro 1 aumentando seu valor em 7% 4) Conte a quantidade de editoras de MG. 5) Conte o número de editoras agrupando por estado. 6) Diminua o preço de todos os livros da editora 1 em 19%. 7) Qual o maior código de autor cadastrado? 8) Qual o menor código de autor cadastrado? 9) Conte o número de editoras, agrupando por estado e somente para estados que tenham 40 ou mais editoras. 10) Aumente o preço de todos os livros em 7,5% 11) Exclua todas as editoras do estado DF. 12) Os livros podem ser vendidos em 3 parcelas sendo a primeira parcela 30% do valor do livro e a segunda e terceira sendo 35% cada. Faça um select que mostre o nome do livro juntamente com o preço dele e os valor das parcelas. 13) Todas as editoras do DF mudaram para GO. Atualize no banco de dados por favor! 14) Quantas editoras temos em cidades que começam pela letra M? 15) Altere o preço de todos os livros dando um desconto de 6% Curso SQL/Atividades/Lista 8 - Distinct - Limit - View - Index.pdf Aprenda SQL Lista 8 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questões. Não pare antes disso. 1) Faça um select somente de 10 editoras de GO 2) Exiba o nome das editoras em ordem inversa e retorne as 3 primeiras 3) Exiba todos os estados que temos editoras cadastradas 4) Crie uma view para o select que você fez no exercício 1 com o nome de GOIAS. 5) Crie uma view para o select que você fez no exercício 3 com o nome de ESTADOS. 6) Crie um índice para o estado na tabela Editora 7) Crie um índice para o nome do autor. 8) Utilize subselect e exclua todos os livros da editora XPTO 9) Utilize subselect e exclua todos os livros do autor José Buscapé 10) Exclua a view GOIAS 11) Exclua o índice da tabela Editora 12) Exclua a view Estados 13) Exiba em ordem alfabética as editoras e mostre as 7 primeiras (somente o nome). 14) Exclua o índice da tabela autor 15) Crie um índice para o nome do livro Curso SQL/Atividades/Lista 9 - Fun??es - Transa??es - Outer Join - Trigger.pdf Aprenda SQL Lista 9 Prof. Tiago Baciotti Moreira Observações: • Para realização dos exercícios é necessário que você baixe o sqlite e coloque-o em sua área de trabalho. Depois você precisará abrir o cmd para executar com o nome correto do banco de dados conforme pedido. • Pode pesquisar a vontade e conversar com seus colegas. O Google é seu amigo. • Não reclame que não sabe. Pesquise, tente, teste e refaça! Mude sua postura de “Não sei fazer” para “Vou descobrir como se faz”. Anote a resposta de TODOS os exercícios. Irá te ajudar lá na frente! • Não pule nenhum exercício achando que “já sabe”. • Nomes de tabelas, campos e banco de dados NÃO devem possuir acentos e cedilha. • Lembre-se que o “;” finaliza os comandos. • Os tipos de dados basicamente são: int para inteiros, text para texto, real para números com decimais, date para datas e boolean para booleanos. Você também pode usar para campos texto o tipo varchar especificando o tamanho. Para valores monetários você pode usar o tipo real. • Sua missão é resolver todas as questões. Não pare antes disso. 1) Crie uma tabela de auditoria contendo os campos editora_id, data e observação 2) Crie uma trigger para que toda vez que seja inserido um novo registro na tabela editora seja gravado um registro na tabela auditoria com a informação “Registro inserido” 3) Crie uma trigger para que toda vez que seja excluído um registro na tabela editora seja gravado um registro na tabela auditoria com a informação “Registro excluído” 4) Faça um join na tabela de livros com autores mostrando o título do livro e do autor. 5) Faça um join na tabela de livros com autores mostrando o título do livro e do autor usando agora um LEFT OUTER JOIN para mostrar inclusive os livros que não tenham autor definido. 6) Escreva um bloco de transação que exclua o livro de código 1 e insira um novo livro de código 50. Mas dê um rollback na transação. 7) Escreva um bloco de transação que exclua o livro de código 3 e insira um novo livro de código 3. Feche a transação com Commit. 8) Fazer um select no Banco e retornar os seguintes campos: • Nome do autor em letras maiúsculas • Nome do estilo em letras minúsculas • As 10 primeiras letras do nome do livro • Valor do Livro • Valor do Livro com desconto de 7% Atenção: Trata-se de UM ÚNICO COMANDO SELECT para retornar todos os campos acima. 9) Faça um select trazendo o nome do livro, código do estilo, código do autor e código da editora. Caso algum campo desse esteja NULL mostre a mensagem SEM CADASTRO. 10) Mostre o nome das 6 primeiras editoras em ordem alfabética e em letras maiúsculas. 11) Faça um select mostrando o nome do livro e o tamanho em caracteres do nome Aprenda SQL Lista 9 Prof. Tiago Baciotti Moreira 12) Exiba a data atual 13) Exiba a versão do SQLite 14) Exiba o nome das editoras substituindo a letra a por X 15) Grite bem alto “TERMINEI TODOS OS EXERCÍCIOS!!!!!!!” ;-) Curso SQL/Bancos/aula.sqlite Curso SQL/Bancos/lista1.sqlite Curso SQL/Bancos/lista3.sqlite Curso SQL/Bancos/lista5.sqlite Curso SQL/Bancos/massa_um_milhao.zip massa_um_milhao Curso SQL/Respostas/lista3.txt --1)Leia do primeiro ao último exercício antes de começar a resolver. --2)Execute o sqlite criando um banco de dados de nome lista2.sqlite. --3)Crie uma tabela com o nome de alunos. Deverá conter o campo --código (inteiro), nome, telefone e cidade (texto). Vou te --ajudar nessa: CREATE TABLE alunos ( codigo int, nome text, telefone text, cidade text ); --4)Use o comando .tables para verificar se a tabela foi criada --5)Crie a tabela funcionários contendo os campos código, nome, --endereço, telefone, cidade, estado, cep, rg, cpf e salário. --Coloque os tipos de dados necessários. create table funcionarios ( codigo int, nome text, endereco text, telefone text, cidade text, estado text, cep text, rg text, cpf text, salario number); --6)Saia do sqlite com o comando .exit. --7)Abra novamente no sqlite o banco lista3.sqlite. --8)Verifique se as tabelas ainda existem com o comando .tables --9)Agora iremos trabalhar com o comando insert para inserir um --novo registro ao banco de dados. Apenas para você lembrar o --funcionamento dele iremos inserir um registro na tabela alunos: insert into alunos (codigo, nome, telefone, cidade) values (1,"Ana","9999-9999","Ituiutaba"); --10)Precisamos agora verificar se o registro foi inserido --corretamente. Então precisamos selecionar todos os dados da --tabela alunos. Use o comando select desse jeito: --select * from alunos; (lembre-se que o * aqui nesse caso --significa todos os campos, ou seja, irá mostrar nome, endereço, código, etc). select * from alunos; --11)Insira um novo registro na tabela alunos com os seus dados. insert into alunos (codigo, nome, telefone, cidade) values (1,"Tiago","8888-9999","Ituiutaba"); --12)Selecione os registros da tabela alunos e veja se o registro foi inserido. --13)Ligue os cabeçalhos usando o comando .headers on --14)Selecione novamente para verificar se o cabeçalho foi mostrado corretamente. --15)Insira na tabela alunos o aluno José Buscapé. insert into alunos (codigo, nome, telefone, cidade) values (3,"José Buscapé","9999-9999","São Paulo"); --16)Selecione o conteúdo da tabela e veja se foi inserido corretamente. --17)Agora você vai aprender um novo recurso do comando select. --Você pode escolher os CAMPOS que deseja que sejam exibidos. --Por exemplo, se eu quiser exibir somente o código e o nome devo --usar o comando assim: select codigo,nome from alunos; - Faça isso agora! select codigo,nome from alunos; --18)Selecione somente o nome e telefone dos alunos. select nome,telefone from alunos; --19)Selecione o nome e a cidade dos alunos select nome, cidade from alunos; --20)Selecione somente o código e o telefone dos alunos select codigo,telefone from alunos; --21)Insira 4 novos alunos; insert into alunos (codigo, nome, telefone, cidade) values (4,"Ana Maria","9999-9999","São Paulo"); insert into alunos (codigo, nome, telefone, cidade) values (5,"Marisa","7777-9999","Pernambuco"); insert into alunos (codigo, nome, telefone, cidade) values (6,"Pedro Malazarte","9999-9999","Rio de Janeiro"); insert into alunos (codigo, nome, telefone, cidade) values (7,"José Lucas","5555-9999","São Paulo"); --22)Selecione todos os campos da tabela alunos select * from alunos; --23)Selecione da tabela alunos os seguintes campos --(nessa ordem): cidade, código, nome. Veja --que você pode exibir os dados na ordem que quiser. select cidade, codigo, nome from alunos; --24)Insira mais um alunos na tabela alunos. insert into alunos (codigo, nome, telefone, cidade) values (3,"Lívia","9999-9999","Uberlândia"); --25)Saia do sqlite, feche o terminal e abra novamente. --26)Selecione os dados da tabela a alunos e veja se ainda existem. select * from alunos; --27)Adicione 1 novo funcionário. Lembre-se que é --necessário usar aspas para campos TEXTO. Campos numéricos --não podem ter aspas. Se o salário tiver centavos, lembre-se --que deve separar os centavos com um (.) (ponto) pois a vírgula -- é usada para separar os valores a serem inseridos. insert into funcionarios (nome, salario) values ("José",1000); --28)Selecione os dados da tabela funcionários e veja se --foi inserido corretamente. select * from funcionarios; --29)Cadastre 3 funcionários. Use código na sequência. --(1,2,3,4,5 etc). insert into funcionarios (codigo, nome, salario) values (50, "José",1000); --30)Selecione somente o código e nome dos funcionários. select codigo,nome from funcionarios; Curso SQL/Respostas/lista4.txt -- 1)Selecione somente o nome e salário dos funcionários. select nome,salario from funcionarios; -- 2)Selecione somente o nome e telefone dos funcionários. select nome,telefone from funcionarios; -- 3)Selecione somente o nome, rg e cpf dos funcionários. select nome,rg,cpf from funcionarios; -- 4)Selecione somente o nome e a cidade dos funcionários. select nome,cidade from funcionarios; -- 5)Insira mais um funcionário. insert into funcionarios (nome, cidade, cpf, telefone) values ("Pedro","Itú","123.456.789-00","13 9878-9878"); -- 6)Exiba todos os dados dos funcionários. select * from funcionarios; -- 7)Crie a tabela fornecedores contendo os campos nome, endereço, -- telefone, cidade, estado, cep, cnpj e email. Coloque os tipos de dados necessários. create table fornecedores(codigo int, nome text, endereco text, telefone text, cidade text, estado text, cep text, cnpj text, email text); -- 8)Insira 2 fornecedores. Código 1 e Código 2 insert into fornecedores(codigo,nome, telefone,cidade) values (1,"ABC Comércio","33 3333-3333","São Roque"); insert into fornecedores(codigo,nome, telefone,cidade) values (2,"XYZ Comércio","33 4444-3333","São Roque"); -- 9)Selecione o nome e o telefone dos fornecedores. select nome,telefone from fornecedores; -- 10)Agora iremos aprender uma opção do comando select. --Nós podemos restringir o que vai ser exibido na tela. É moleza. --Por exemplo se eu quiser listar somente o aluno de código 2 o --comando fica assim: select * from alunos where codigo = 2; - --Nós apenas adicionamos o WHERE e a CONDIÇÃO. Veja que mantivemos o * --para exibir todos os campos, mas poderíamos também exibir somente o --nome do aluno 2 assim: select nome from alunos where codigo = 2; - --EXPERIMENTE AGORA ESSES 2 COMANDOS. select * from alunos where codigo = 3; select nome from alunos where codigo = 3; -- 11)Selecione o funcionário de código 3. select * from alunos where codigo = 3; -- 12)Selecione o fornecedor de código 1. select * from fornecedores where codigo=1; -- 13)Selecione o aluno de código 2. select * from alunos where codigo=2; -- 14)Selecione o funcionário de código 1. select * from funcionarios where codigo=2; -- 15)Selecione somente o nome e salário do funcionário de código 2. select nome,salario from funcionarios where codigo=2; -- 16)Selecione somente o nome a cidade do aluno de código 1. select nome,cidade from alunos where codigo=1; -- 17)Selecione todos os funcionários de MG. É assim: select --* from funcionários where estado=’MG’; - --Como estado é texto eu usei aspas! – Faça isso agora! select * from funcionarios where estado="MG"; -- 18)Selecione todos os funcionários de GO. select * from funcionarios where estado="GO"; -- 19)Selecione todos os funcionários de SP. select * from funcionarios where estado="SP"; -- 20)Insira um funcionário para SP. insert into funcionarios (nome, cidade, cpf, telefone, estado) values ("Pedro","Itú","123.456.789-00","13 9878-9878","SP"); -- 21)Selecione todos os funcionários de SP. select * from funcionarios where estado="SP"; -- 22)Crie a tabela livros contendo o campo código, nome, --categoria, resumo, precocusto, precovenda. create table livros (codigo int, nome text, categoria text, resumo text, precocusto number, precovenda number); -- 23)Verifique o esquema .schema da tabela livros. -- 24)Liste as tabelas existentes. -- 25)Insira 1 livro. insert into livros(codigo,nome,precocusto) values (1,"A Arte da Guerra",6); -- 26)Selecione o nome e a categoria do livro de código 1. select nome, categoria from livros where codigo=1; -- 27)Selecione o nome e a categoria do livro de código 3. select nome, categoria from livros where codigo=3; -- 28)Exclua a tabela livros. drop table livros; -- 29)Altere o nome da tabela aluno para estudantes. alter table alunos rename to estudantes; -- 30)Altere a tabela alunos inserindo o campo estado. Se --estiver com dúvidas consulte a primeira lista de exercícios. alter table estudantes add estado text; Curso SQL/Respostas/lista5.txt -- 1)Crie no SqliteStudio um banco de dados chamado --lista5.sqlite e execute os comandos abaixo. -- 2)Crie uma tabela com o nome de livros contendo os campos -- codigo, titulo, codigo do autor, código da editora, código do estilo, --sinopse e isbn. Sugestão de nome de campo: autor_codigo, editora_codigo create table livro( id int, titulo text, autor_id int, editora_id int, estilo_id int, sinopse text, isbn text ); -- 3)Crie uma tabela com o nome de editoras contendo o --codigo, nome, cidade, estado, telefone e e-mail. create table editora( id int, nome text, cidade text, estado text, telefone text, email text); -- 4)Crie uma tabela com o nome de estilos contendo o --código e o nome do estilo. create table estilo( id int, nome text ); -- 5)Crie uma tabela com o nome de autores contendo o --codigo, nome, cidade, estado, telefone do autor. create table autor( id int, nome text, cidade text, estado text, telefone text ); -- 6)Insira um registro na tabela livros (todos os campos) insert into livro(id,titulo,autor_id,editora_id, estilo_id, sinopse, isbn) values (1,"Anjos e Demônios",1,1,1,"Livro excelente com uma historia legal","7878787878"); -- 7)Insira um registro na tabela editoras (todos os campos). insert into editora(id,nome, cidade, estado, telefone) values (1,"XYZ","São Paulo","SP","11 99999-7777"); -- 8)Insira um registro na tabela estilos (todos os campos). insert into estilo(id,nome) values (1,"Policial"); -- 9)Insira um registro na tabela autores (todos os campos). insert into autor(id, nome, cidade, estado, telefone) values (1,"Dan Brown","Los Angeles","CA","+1 00 9999-8877"); -- 10)Altere o nome da tabela autores para autor. -- 11)Insira na tabela livros um novo registro adicionando --somente os campos codigo e nome insert into livro(id,titulo) values (2,"A Arte da Guerra"); -- 12)Insira 5 estilos de livros (comédia, drama, ficção, --suspense, romance). insert into estilo (id,nome) values (2,"Comédia"); insert into estilo (id,nome) values (3,"Drama"); insert into estilo (id,nome) values (4,"Ficção"); insert into estilo (id,nome) values (5,"Suspense"); insert into estilo (id,nome) values (6,"Romance"); -- 13)Selecionar todos os livros do banco de dados. select * from livro; -- 14)Insira 2 novos livros. insert into livro(id,titulo,autor_id,editora_id, estilo_id, sinopse, isbn) values (2,"Código da Vinci",1,1,1,"Outro livro excelente com uma historia legal","555544448888"); insert into livro(id,titulo,autor_id,editora_id, estilo_id, sinopse, isbn) values (3,"Mais um código da Vinci",1,1,1,"Livro excelente com uma historia legal","333344448888"); -- 15)Altere o nome da tabela livros autores para livro. -- 16)DESAFIO: Selecione o nome de todos os estilos em --ordem alfabética select nome from estilo order by nome; -- 17)DESAFIO: Selecione o nome de todos os autores em --ordem alfabética inversa select nome from autor order by nome DESC; select nome from estilo order by nome DESC; -- 18)Selecione o nome e o telefone de todas as editoras. select nome,telefone from editora; -- 19)Selecione o nome de todas as editoras select nome from editora; -- 20)Selecione o nome de todas as editoras de MG select nome from editora where estado="MG" ; -- 21)Selecione os estilos de livros em ordem alfabética. select nome from estilo order by nome ; -- 22)Selecione agora em ordem alfabética inversa. select nome from estilo order by nome DESC; -- 23)Selecione o nome de todos os autores de SP. select nome from autor where estado="CA"; -- 24)Selecione o estilo de código 13 select * from estilo where id=13; -- 25)Selecione o autor de código 8 select * from autor where id=8; -- 26)Selecione a editora de código 10 select * from editora where id=1; -- 27)Selecione o nome, a cidade e o estado de todas as editoras. select nome,cidade, estado from editora; -- 28)Adicione 3 editoras. insert into editora(id,nome, cidade, estado, telefone) values (2,"ABC","Curitiba","PR","11 99999-7777"); insert into editora(id,nome, cidade, estado, telefone) values (3,"ZZZ","Rio Claro","SP","11 99999-7777"); insert into editora(id,nome, cidade, estado, telefone) values (4,"Saci-Pererê","Ribeirão Preto","SP","11 99999-7777"); -- 29)Selecione o nome de todas as editoras select nome from editora; -- 30)Exclua a editora de código 1 delete from editora where id=1; Curso SQL/Respostas/lista7.txt -- 1)Conte o número de editoras cadastradas select * from editora; select count(*) from editora; -- 2)Calcule a média do preço de venda dos livros select avg(precovenda) from livro; -- 3)Atualize o preço de venda do livro 1 aumentando seu valor em 7% update livro set precovenda = precovenda + (precovenda * 0.07) where id = 1; -- 4)Conte a quantidade de editoras de MG. select count(*) from editora where estado="MG" ; -- 5)Conte o número de editoras agrupando por estado. select estado, count(*) from editora group by estado; -- 6)Diminua o preço de todos os livros da editora 1 em 19%. select * from livro; update livro set precovenda = precovenda - (precovenda * 0.19) where editora_id = 1; -- 7)Qual o maior código de autor cadastrado? select max(id) from autor; -- 8)Qual o menor código de autor cadastrado? select min(id) from autor; -- 9)Conte o número de editoras, agrupando por estado e somente -- para estados que tenham 40 ou mais editoras. select estado, count(*) from editora group by estado having count(*) > 40 ; -- 10)Aumente o preço de todos os livros em 7,5% update livro set precovenda = precovenda + (precovenda * 0.075); select * from livro; -- 11)Exclua todas as editoras do estado DF. delete from editora where estado="DF"; -- 12)Os livros podem ser vendidos em 3 parcelas sendo a primeira parcela 30% do valor do livro -- e a segunda e terceira sendo 35% cada. Faça um select que mostre o nome -- do livro juntamente com o preço dele e os valor das parcelas. select titulo, precovenda, precovenda * 0.3 as "Parcela 1", precovenda * 0.35 as "Parcela 2", precovenda * 0.35 as "Parcela 3" from livro; -- 13)Todas as editoras do PR mudaram para GO. Atualize no banco de dados por favor! select estado, count(*) from editora group by estado; update editora set estado="GO" where estado="PR"; -- 14)Quantas editoras temos em cidades que começam pela letra M? select count(*) from editora where nome like "M%"; -- 15)Altere o preço de todos os livros dando um desconto de 6% update livro set precovenda = precovenda - (precovenda * 0.06) ; update livro set precovenda = precovenda * 0.94 where id = 3; select * from livro; Curso SQL/Respostas/lista8.txt -- 1)Faça um select somente de 10 editoras de GO select * from editora where estado="GO" LIMIT 10 ; -- 2)Exiba o nome das editoras em ordem inversa e retorne as 3 primeiras select nome from editora order by nome DESC limit 3 ; -- 3)Exiba todos os estados que temos editoras cadastradas select distinct estado from editora; -- 4)Crie uma view para o select que você fez no exercício 1 com o nome de GOIAS. create view GOIAS as select * from editora where estado="GO"; select * from goias; -- 5)Crie uma view para o select que você fez no exercício 3 com o nome de ESTADOS. create view ESTADOS as select distinct estado from editora; select * from estados; -- 6)Crie um índice para o estado na tabela Editora CREATE INDEX indice_estado ON editora ( estado ); -- 7)Crie um índice para o nome do autor. create index indice_autor on autor (nome); -- 8)Utilize subselect e exclua todos os livros da editora XPTO delete from livro where editora_id = ( select id from editora where nome="XPTO" ); -- 9)Utilize subselect e exclua todos os livros do autor José Buscapé delete from livro where autor_id = ( select id from autor where nome="José Buscapé" ); -- 10)Exclua a view GOIAS drop view goias; -- 11)Exclua o índice da tabela Editora drop index indice_estado; -- 12)Exclua a view Estados drop view estados; -- 13)Exiba em ordem alfabética as editoras e mostre as 7 primeiras (somente o nome). select nome from editora order by nome limit 7; -- 14)Exclua o índice da tabela autor drop index indice_autor; -- 15)Crie um índice para o nome do livro create index indice_titulo on livro (titulo); Curso SQL/Respostas/lista9.txt -- 1)Crie uma tabela de auditoria contendo os campos -- editora_id, data e observação create table auditoria (editora_id int, data date, obs text); -- drop table auditoria; -- 2)Crie uma trigger para que toda vez que seja inserido um -- novo registro na tabela editora seja gravado um registro -- na tabela auditoria com a informação “Registro inserido” -- drop trigger auditoria; -- Criando a Trigger CREATE TRIGGER auditoria AFTER INSERT ON editora BEGIN INSERT INTO auditoria(editora_id,data, obs) VALUES (new.id, datetime('now'), "Registro inserido" ); END; -- drop trigger auditoria -- Inserindo para testar! insert into editora(id,nome) values (10,"Editora Brasil Brasileiro"); -- Conferindo na auditoria select * from auditoria; -- 3)Crie uma trigger para que toda vez que seja excluído um -- registro na tabela editora seja gravado um registro na tabela -- auditoria com a informação “Registro excluído” drop trigger auditoria2; CREATE TRIGGER auditoria2 AFTER DELETE ON editora BEGIN INSERT INTO auditoria(editora_id,data, obs) VALUES (old.id, datetime('now'), "Registro Excluído" ); END; -- Excluindo para testar delete from editora where id = 999; -- Verificando select * from auditoria; select * from editora; -- 4)Faça um join na tabela de livros com autores mostrando o -- título do livro e do autor. -- Tradicional INNER JOIN select l.titulo, a.nome from livro l, autor a where a.id = l.autor_id; -- Outra forma de INNER JOIN select titulo, nome from autor INNER JOIN livro ON autor.id = livro.autor_id; -- 5)Faça um join na tabela de livros com autores mostrando o título -- do livro e do autor usando agora um LEFT OUTER JOIN para mostrar -- inclusive os livros que não tenham autor definido. select titulo, nome from livro LEFT OUTER JOIN autor ON autor.id = livro.estilo_id; -- 6)Escreva um bloco de transação que exclua o livro de código 1 e -- insira um novo livro de código 50. Mas dê um rollback na transação. /* begin transaction; delete from livro where id = 1; insert into livro(id,titulo) values (50,"Teste"); rollback; */ -- Verificando select * from livro; -- 7)Escreva um bloco de transação que exclua o livro de código 3 e insira -- um novo livro de código 3. Feche a transação com Commit. /* begin transaction; delete from livro where id = 3; insert into livro(id,titulo) values (3,"SQL de A a Z"); commit; */ -- Verificando select * from livro; /* 8) Fazer um select no Banco e retornar os seguintes campos: • Nome do autor em letras maiúsculas • Nome da categoria em letras minúsculas • As 10 primeiras letras do nome do livro • Valor do Livro • Valor do Livro com desconto de 7% Atenção: Trata-se de UM ÚNICO COMANDO SELECT para retornar todos os campos acima. */ select upper(a.nome), lower(e.nome), substr(l.titulo,1,10), l.precovenda, l.precovenda * 0.93 from livro l, autor a, estilo e where a.id = l.autor_id and e.id = l.estilo_id ; -- 9) Faça um select trazendo o nome do livro, código do estilo, código -- do autor e código da editora. Caso algum campo desse esteja -- NULL mostre a mensagem SEM CADASTRO. select titulo, ifnull(estilo_id,"SEM CADASTRO"), ifnull(autor_id,"SEM CADASTRO"), ifnull(editora_id,"SEM CADASTRO") from livro; -- 10)Mostre o nome das 6 primeiras editoras em ordem -- alfabética e em letras maiúsculas. select upper(nome ) from editora order by nome limit 6; -- 11)Faça um select mostrando o nome do livro e o -- tamanho em caracteres do nome select titulo, length(titulo) from livro; -- 12)Exiba a data atual select date('now'); -- 13)Exiba a versão do SQLite select sqlite_version(); -- 14)Exiba o nome das editoras substituindo a letra a por X select replace(nome,"a","X") from editora; -- 15)Grite bem alto “TERMINEI TODOS OS EXERCÍCIOS!!!!!!!” ;-) Curso SQL/SQL.xlsx Planilha1 -- -- -- -- -- 1) Crie uma tabela de auditoria contendo os campos editora_id, data e observação -- 1)Crie uma tabela de auditoria contendo os campos editora_id, data e observação -- -- 2) Crie uma trigger para que toda vez que seja inserido um novo registro na tabela editora seja gravado um registro na tabela auditoria com a informação “Registro inserido” -- 2)Crie uma trigger para que toda vez que seja inserido um novo registro na tabela editora seja gravado um registro na tabela auditoria com a informação “Registro inserido” -- -- 3) Crie uma trigger para que toda vez que seja excluído um registro na tabela editora seja gravado um registro na tabela auditoria com a informação “Registro excluído” -- 3)Crie uma trigger para que toda vez que seja excluído um registro na tabela editora seja gravado um registro na tabela auditoria com a informação “Registro excluído” -- -- 4) Faça um join na tabela de livros com autores mostrando o título do livro e do autor. -- 4)Faça um join na tabela de livros com autores mostrando o título do livro e do autor. -- -- 5) Faça um join na tabela de livros com autores mostrando o título do livro e do autor usando agora um LEFT OUTER JOIN para mostrar inclusive os livros que não tenham autor definido. -- 5)Faça um join na tabela de livros com autores mostrando o título do livro e do autor usando agora um LEFT OUTER JOIN para mostrar inclusive os livros que não tenham autor definido. -- -- 6) Escreva um bloco de transação que exclua o livro de código 1 e insira um novo livro de código 50. Mas dê um rollback na transação. -- 6)Escreva um bloco de transação que exclua o livro de código 1 e insira um novo livro de código 50. Mas dê um rollback na transação. -- -- 7) Escreva um bloco de transação que exclua o livro de código 3 e insira um novo livro de código 3. Feche a transação com Commit. -- 7)Escreva um bloco de transação que exclua o livro de código 3 e insira um novo livro de código 3. Feche a transação com Commit. -- -- 9) Faça um select trazendo o nome do livro, da categoria, do autor e do estilo. Caso algum campo desse esteja NULL mostre a mensagem SEM CADASTRO. -- 9)Faça um select trazendo o nome do livro, da categoria, do autor e do estilo. Caso algum campo desse esteja NULL mostre a mensagem SEM CADASTRO. -- -- 10) Mostre o nome das 6 primeiras editoras em ordem alfabética e em letras maiúsculas. -- 10)Mostre o nome das 6 primeiras editoras em ordem alfabética e em letras maiúsculas. -- -- 11) Faça um select mostrand o nome do livro e o tamanho em caracteres do nome -- 11)Faça um select mostrand o nome do livro e o tamanho em caracteres do nome -- -- 12) Exiba a data atual -- 12)Exiba a data atual -- -- 13) Exiba a versão do SQLite -- 13)Exiba a versão do SQLite -- -- 14) Exiba o nome das editoras substituindo a letra a por X -- 14)Exiba o nome das editoras substituindo a letra a por X -- -- 15) Grite bem alto “TERMINEI TODOS OS EXERCÍCIOS!!!!!!!” ;-) -- 15)Grite bem alto “TERMINEI TODOS OS EXERCÍCIOS!!!!!!!” ;-) -- -- 15) Crie um índice para o nome do livro -- 15)Crie um índice para o nome do livro -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Livros Controle de Livros livro editora estilo autor id id id id titulo nome nome nome autor_id cidade cidade editora_id estado estado estilo_id telefone telefone sinopse e-mail isbn Se concentre na PERGUNTA!!!! Onde estão as respostas? Como elas se ligam? Porcentagem 1) Encontrando um % R$ 200.00 2) Desconto 3) Acréscimo '=200 + (200*13%) =200 - (200*7%) Listagem CÓDIGO nome autor descricao editora Código da Vinci Dan Brown Leya Anjos e Demônios Dan Brawn Leia Inferno Dam Brown Léia redundancia
Compartilhar