SQL-Manipulação-Basica-de-Dados-Juncao-de-Tabelas-Indices-e-Auto-Incremento

Banco de Dados Azriel Majdenbaum Aula 05 Aula 05 Dividida em cinco partes Apresentação dos principais conceitos relacionados a SQL Manipulação Básica de Dados Junção de Tabelas Índices Auto Incremento Script de criação do modelo físico Script de inserção de dados O que você vai aprender nessa aula Parte 1 Manipulação Básica de Dados Parte 2 Manipulação Básica de Dados Parte 3 Manipulação Básica de Dados Parte 4 Manipulação Básica de Dados Parte 5 Manipulação Básica de Dados Aula 5 Parte 1 Manipulação Básica de Dados Junção de tabelas dinâmica Junção de tabelas joins encadeados Aula 5 Parte 2 Manipulação Básica de Dados Joins encadeados dinâmica Aula 5 Parte 3 Manipulação Básica de Dados Parte Índices Aula 5 Parte 4 Manipulação Básica de Dados Auto incremento Criação do script do modelo físico Aula 5 Parte 5 Manipulação Básica de Dados Criação do script do modelo físico Criação do script de inserção de dados O que você vai precisar para acompanhar essa aula livesqloraclecom brModelo Web httpswwwbrmodelowebcomlangptbrindexhtml OU Download brModelo 32 httpssourceforgenetprojectsbrmodelo O que você vai aprender nessa aula Parte 1 Manipulação Básica de Dados Parte 2 Manipulação Básica de Dados Parte 3 Manipulação Básica de Dados Parte 4 Manipulação Básica de Dados Parte 5 Manipulação Básica de Dados Dinâmica Execute os scripts abaixo no OracleLive e após faça o que se pede CRIAÇÃO DO BANCO DROP TABLE cidades CASCADE CONSTRAINTS DROP TABLE estados CASCADE CONSTRAINTS CREATE TABLE estados uf CHAR 2 NOT NULL nome VARCHAR2 40 NOT NULL regiao CHAR 2 NOT NULL CONSTRAINT PKESTADOS PRIMARY KEY uf CREATE TABLE cidades codcidade NUMBER 4 NOT NULL nome VARCHAR2 60 NOT NULL uf CHAR 2 CONSTRAINT PKCIDADES PRIMARY KEY codcidade ALTER TABLE cidades ADD CONSTRAINT FKESTCID FOREIGN KEY uf REFERENCES estados uf Cidades insert into cidades values 1Abadia de GoiásGO insert into cidades values 2Abadia dos DouradosMG insert into cidades values 3AbadiâniaGO insert into cidades values 4AbaetéMG insert into cidades values 27AcrelândiaAC insert into cidades values 186AmapáAP insert into cidades values 295AracajuSE insert into cidades values 451BagéRS insert into cidades values 581BelémPA insert into cidades values 647Boa VistaRR insert into cidades values 749BrasíliaDF insert into cidades values 902Caldas NovasGO insert into cidades values 947CampinasSP insert into cidades values 968Campo GrandeMS insert into cidades values 977Campo Novo de RondôniaRO insert into cidades values 1031CanoasRS insert into cidades values 1486CuiabáMT insert into cidades values 1504CuritibaPR insert into cidades values 1807FlorianópolisSC insert into cidades values 1826FortalezaCE insert into cidades values 1922GoiâniaGO insert into cidades values 2588João PessoaPB insert into cidades values 2823MacapáAP insert into cidades values 2831MaceióAL insert into cidades values 2877ManausAM insert into cidades values 7777Nova null Estados insert into estados values ACAcreN insert into estados values ALAlagoasNE insert into estados values APAmapáN insert into estados values AMAmazonasN insert into estados values BABahiaNE insert into estados values CECearáNE insert into estados values DFDistrito FederalCO insert into estados values ESEspírito SantoSE insert into estados values GOGoiásCO insert into estados values MAMaranhãoNE insert into estados values MTMato GrossoCO insert into estados values MSMato Grosso do SulCO insert into estados values MGMinas GeraisSE insert into estados values PAParáN insert into estados values PBParaíbaNE insert into estados values PRParanáS insert into estados values PEPernambucoNE insert into estados values PIPiauíNE insert into estados values RJRio de JaneiroSE insert into estados values RNRio Grande do NorteNE insert into estados values RSRio Grande do SulS insert into estados values RORondôniaN insert into estados values RRRoraimaN insert into estados values SCSanta CatarinaS insert into estados values SPSão PauloSE insert into estados values SESergipeNE insert into estados values TOTocantinsN Questões Liste o que se pede 1 O nome e a região das cidades cadastradas no estado de Goiás 2 O código o nome e a região de cada cidade dos estados do Rio Grande do Sul São Paulo e Paraná cujo nome inicia por C Ordenar pelo nome da cidade de forma decrescente 3 O nome do estado e de suas respectivas cidades incluindo as cidades que não pertencem a nenhum estado Manipulação Básica de Dados Join JOINS encadeados Há ocasiões em que precisamos buscar dados de mais de duas tabelas Em outros casos os dados de que precisamos encontramse em tabelas mais distantes no esquema do banco de dados Para poder obter esses dados precisamos usar JOINs encadeados SELECT AUnome PRODtitulo FROM AUTORES AU JOIN AUTORESPRODUTOS AP ON AUcodautor APcodautor JOIN PRODUTOS PROD ON APcodproduto PRODcodproduto Manipulação Básica de Dados Join Criação do esquema Drop table produtos Cascade Constraints Drop table autores Cascade Constraints Drop table autoresprodutos Cascade Constraints CREATE TABLE produtos codproduto NUMBER 5 NOT NULL titulo VARCHAR2 200 NOT NULL anolancamento DATE NOT NULL importado CHAR 1 DEFAULT N NOT NULL preco NUMBER 10 2 NOT NULL prazoentrega NUMBER 3 NOT NULL CONSTRAINT PKPRODUTOS PRIMARY KEY codproduto CONSTRAINT CHKPRODIMPORTADO CHECK importado in SN CREATE TABLE autores codautor NUMBER 4 NOT NULL nome VARCHAR2 100 NOT NULL descricao VARCHAR2 1024 CONSTRAINT PKAUTORES PRIMARY KEY codautor CREATE TABLE autoresprodutos codautor NUMBER 4 NOT NULL codproduto NUMBER 5 NOT NULL CONSTRAINT PKAUTORESPRODUTOS PRIMARY KEY codautor codproduto ALTER TABLE autoresprodutos ADD CONSTRAINT FKAUTAUTPROD FOREIGN KEY codautor REFERENCES autores codautor ALTER TABLE autoresprodutos ADD CONSTRAINT FKPRDAUTPROD FOREIGN KEY codproduto REFERENCES produtos codproduto Manipulação Básica de Dados Join Autores insert into autores values 1Sophia Angelidesnull insert into autores values 2Mário Pedrosanull insert into autores values 1002Otília Arantesnull insert into autores values 3Emílio F Morannull insert into autores values 4Jack R Greenenull insert into autores values 5Antônio Chavesnull insert into autores values 6José Pedro de Oliveira Costanull insert into autores values 7Ana Mae Barbosanull insert into autores values 8Piero CominChiaramontinull insert into autores values 1008Celso Gomesnull insert into autores values 9Barão de Itararénull insert into autores values 10Barão de Itararénull insert into autores values 11Aziz Nacib AbSabernull insert into autores values 12Maria L Aragãonull insert into autores values 1012José Carlos S Meihy null insert into autores values 13Eliane Garcindo Dayrellnull insert into autores values 1013Zilda Márcia Iokoinull insert into autores values 14Maria Lígia Coelho Pradonull insert into autores values 15Tânia Maria Bessonenull insert into autores values 1015Tereza Aline Queiroznull insert into autores values 16Dante Moreira Leitenull insert into autores values 17Nilza Nunes da Silvanull insert into autores values 18Ricardo Palmanull insert into autores values 19José Luiz Goldfarbnull insert into autores values 1019Márcia Ferraznull insert into autores values 20Júlio César Rodrigues Pereiranull Produtos insert into produtos values 1A P TCHEKHOV Cartas para uma Poéticatodate29121995ddmmyyyyS29643 insert into produtos values 2ACADÊMICOS E MODERNOS Textos Escolhidos 3todate22092002ddmmyyyyN3147 insert into produtos values 3ADAPTABILIDADE HUMANA Uma Introdução à Antropologiatodate17011998ddmmyyyyN40042 insert into produtos values 4ADMINISTRAÇÃO DO TRABALHO POLICIALtodate25082000ddmmyyyyN26526 insert into produtos values 5ADOÇÃO INTERNACIONAL e o Tráfico de Criançastodate16111998ddmmyyyyN4951 insert into produtos values 6AIURUOCA MATUTU E PEDRA DO PAPAGAIOtodate19081999ddmmyyyyN32641 insert into produtos values 7ALEX FLEMMINGtodate23092001ddmmyyyyS2443 insert into produtos values 8ALKALINE MAGMATISM IN CENTRALEASTERN PARAGUAYtodate20081997ddmmyyyyN14345 insert into produtos values 9ALMANHAQUE PARA 1955 1º SEMESTREtodate16051998ddmmyyyyS17927 insert into produtos values 10ALMANHAQUE PARA 1949todate20062000ddmmyyyyN8621 insert into produtos values 11ALMANHAQUE PARA xyztodate20062000ddmmyyyyN8621 AutoresProdutos insert into autoresprodutos values 11 insert into autoresprodutos values 22 insert into autoresprodutos values 10022 insert into autoresprodutos values 33 insert into autoresprodutos values 44 insert into autoresprodutos values 55 insert into autoresprodutos values 66 insert into autoresprodutos values 77 insert into autoresprodutos values 88 insert into autoresprodutos values 10088 insert into autoresprodutos values 99 insert into autoresprodutos values 1010 insert into autoresprodutos values 111 insert into autoresprodutos values 122 insert into autoresprodutos values 10122 insert into autoresprodutos values 133 insert into autoresprodutos values 10133 insert into autoresprodutos values 144 insert into autoresprodutos values 155 insert into autoresprodutos values 10155 insert into autoresprodutos values 166 insert into autoresprodutos values 177 insert into autoresprodutos values 188 insert into autoresprodutos values 91 Inserção de dados Resumo do que vimos até agora Junção de Tabelas Joins encadeados Relembrando o conteúdo do vídeo anterior Junção de tabelas joins O que você vai aprender nessa aula Parte 1 Manipulação Básica de Dados Parte 2 Manipulação Básica de Dados Joins encadeados Parte 3 Manipulação Básica de Dados Parte 4 Manipulação Básica de Dados Parte 5 Manipulação Básica de Dados O que você vai precisar para acompanhar essa aula Acessar o Oracle Live SQL em livesqloraclecom Manipulação Básica de Dados Join JOINS encadeados Exemplo selecionar o nome de todos os clientes e quando tiverem seus números de telefone SELECT usunome DECODEtelnumero null SEM TELEFONE telnumero FROM usuarios usu JOIN clientes cli ON usucodusuario clicodcliente LEFT OUTER JOIN telefones tel ON clicodcliente telcodcliente Manipulação Básica de Dados Join Criação do esquema Drop table administradores Cascade Constraints Drop table telefones Cascade Constraints Drop table clientes Cascade Constraints Drop table usuarios Cascade Constraints CREATE TABLE administradores codadministrador NUMBER 6 NOT NULL nivelprivilegio NUMBER 1 NOT NULL CONSTRAINT PKADMINISTRADORES PRIMARY KEY codadministrador CREATE TABLE telefones codcliente NUMBER 6 NOT NULL codtelefone NUMBER 2 NOT NULL codtipotelefone NUMBER 2 NOT NULL ddd NUMBER 3 numero VARCHAR2 10 NOT NULL CONSTRAINT PKTELEFONES PRIMARY KEY codcliente codtelefone CREATE TABLE clientes codcliente NUMBER 6 NOT NULL datanascimento DATE datacadastro DATE NOT NULL CONSTRAINT PKCLIENTES PRIMARY KEY codcliente CREATE TABLE usuarios codusuario NUMBER 6 NOT NULL nome VARCHAR2 100 NOT NULL cpf CHAR 11 NOT NULL email VARCHAR2 40 NOT NULL username VARCHAR2 20 NOT NULL password VARCHAR2 20 NOT NULL CONSTRAINT PKUSUARIOS PRIMARY KEY codusuario CONSTRAINT AKUSUCPF UNIQUE cpf CONSTRAINT AKUSUUSERNAME UNIQUE username ALTER TABLE administradores ADD CONSTRAINT FKUSUADM FOREIGN KEY codadministrador REFERENCES usuarios codusuario ALTER TABLE telefones ADD CONSTRAINT FKCLITEL FOREIGN KEY codcliente REFERENCES clientes codcliente ALTER TABLE clientes ADD CONSTRAINT FKUSUCLI FOREIGN KEY codcliente REFERENCES usuarios codusuario Manipulação Básica de Dados Join Inserção de dados insert into usuarios values 1 A Emygdio35370282516agdiotestecombragdio 64668998 insert into usuarios values 2 A Saabor92677298888aabortestecombraabor 96705441 insert into usuarios values 3 Adauto Pereira04885082373aeiratestecombraeira 65274537 insert into usuarios values 4 Adelina Alcântara97526685616ataratestecombratara 82364180 insert into usuarios values 5 Ailde Ribeiro30712138787aeirotestecombraeiro 19747183 insert into usuarios values 6 Ailema De Bem Bianchetti45047736266aettitestecombraetti 58221306 insert into usuarios values 7 Aldmeriza Riker34950572373aikertestecombraiker 12580599 insert into usuarios values 8 Alvimar70703977554aimartestecombraimar 75203761 insert into usuarios values 9 Ambrosina Coradi04896260189araditestecombraradi 97278274 insert into usuarios values 10 Ana Catarina07304019543arinatestecombrarina 91069637 insert into usuarios values 11 Ana Guimarães53908635986arãestestecombrarães 16044890 insert into usuarios values 20 Angélica Bittencourt75989278641aourttestecombraourt 44786047 insert into usuarios values 45 Cláudia Vinhote19783421199chotetestecombrchote 8812908 insert into usuarios values 74 Eugênio Fernandes47274314171endestestecombrendes 13774634 insert into usuarios values 89 Gabriel Di Castro42953819298gstrotestecombrgstro 15590031 insert into usuarios values 104 Hermínia Metzler79048120305hzlertestecombrhzler 38436352 insert into usuarios values 105 Huet Azevedo52722224483hvedotestecombrhvedo 4555441 insert into usuarios values 260 Tônia Oberlaender83787166240tndertestecombrtnder 88173385 insert into usuarios values 277 Wanda Rosa23200128949wrosatestecombrwrosa 81554640 insert into usuarios values 281 José Artur Grossi52855328846jossitestecombrjossi 38359176 insert into usuarios values 282 Zerbina Ventura13317301021zturatestecombrztura 46608648 insert into administradores values 13 insert into administradores values 203 insert into administradores values 453 insert into administradores values 749 insert into administradores values 896 insert into administradores values 1048 insert into administradores values 2605 insert into administradores values 2772 insert into administradores values 2817 insert into administradores values 2822 insert into administradores values 22 insert into clientes values 2todate27091993ddmmyyyytodate03021999ddmmyyyy insert into clientes values 3todate16041991ddmmyyyytodate23021997ddmmyyyy insert into clientes values 4todate22101984ddmmyyyytodate30051999ddmmyyyy insert into clientes values 5todate30081978ddmmyyyytodate27071998ddmmyyyy insert into clientes values 6todate19121993ddmmyyyytodate10092000ddmmyyyy insert into clientes values 7todate29091979ddmmyyyytodate12022003ddmmyyyy insert into clientes values 8todate21011972ddmmyyyytodate14032001ddmmyyyy insert into clientes values 9todate23021976ddmmyyyytodate04082001ddmmyyyy insert into clientes values 10todate25071970ddmmyyyytodate11122001ddmmyyyy insert into clientes values 11todate15041973ddmmyyyytodate06031996ddmmyyyy insert into telefones values 21331115453008 insert into telefones values 222NULL31947514 insert into telefones values 3134322279433 insert into telefones values 41530530243262 insert into telefones values 42527863952847 insert into telefones values 511NULL85371262 insert into telefones values 52316695639007 insert into telefones values 61230751866295 insert into telefones values 624292705128 insert into telefones values 634NULL69073865 insert into telefones values 71328311329535 insert into telefones values 101516031788478 insert into telefones values 102315036641809 insert into telefones values 10325081961878 insert into telefones values 104213887714754 insert into telefones values 1052NULL21421524 insert into telefones values 111514294677343 insert into telefones values 112224490735574 Dinâmica Baseado no banco de dados de usuários administradores clientes e seus telefones faça o que se pede Liste os usuários cujo nome começa por Ana e seus respectivos telefones Liste o nome o ddd e o número de telefone dos usuários que são clientes ordenado por nome do usuário de forma descendente e ddd Liste o nome o nível de privilégio o ddd e o número do telefone dos usuários que são administradores e ao mesmo tempo são clientes Conte o número de usuários cujo nome começa por Ana que não preencheram o ddd Liste o nome o nível de privilégio e o tamanho da password dos administradores cuja password seja menor que 8 caracteres Conte o número de clientes cujo email pertença ao domínio testecombr que tenham telefone Resumo do que vimos até agora Junção de tabelas Joins encadeados Relembrando o conteúdo do vídeo anterior Junção de tabelas Joins encadeados O que você vai aprender nessa aula Parte 1 Manipulação Básica de Dados Parte 2 Manipulação Básica de Dados Parte 3 Manipulação Básica de Dados Índices Parte 4 Manipulação Básica de Dados Parte 5 Manipulação Básica de Dados Manipulação Básica de Dados Indexação Exemplo com a tabela Cidades Por que o Oracle está me mostrando os nomes das cidades ordenados SELECT nome uf FROM CIDADES Porque casualmente ao rodar o script de INSERÇÂO estava já em ordem Então o SGBD foi preenchendo cada DATABLOCK na ordem que o script foi sendo executado Então quando consulto ele lista nessa ordem E os dados estão ordenados porque casualmente os dados foram inseridos de forma ordenada É apenas uma coincidência sobre a forma como eu inseri os dados aqui Manipulação Básica de Dados Indexação Mas o fato é que os SGBD não garantem ordenação nas consultas SELECT nome FROM AUTORES Uma busca sequencial Tem um custo muito alto Mas pode dar sorte se estiver no início No pior caso será o último da lista ou nem estar no Banco de dados Por exemplo Autores não estão ordenados Como posso procurar um autor específico Que tipo de algoritmo o SGBD poderia usar Manipulação Básica de Dados Indexação O SGBD precisa usar um algoritmo otimizado para agilizar as pesquisas SELECT Count FROM AUTORES Como acharíamos o autor Dante Moreira Leite usando pesquisa binária Por exemplo Pesquisa Binária Mas para isso os dados precisam estar ordenados Quantos registros tem a tabela Autores SELECT nome FROM AUTORES ORDER BY nome Manipulação Básica de Dados Indexação Então não seria mais fácil guardar o arquivo ordenado Não porque para pesquisar pela coluna ordenada seria fácil mas para pesquisar pelas outras colunas não E o SGBD foi feito para que façamos as consultas que quisermos Então como o SGBD não pode guardar de forma ordenada por uma coluna porque restringiria a capacidade dele de fazer consultas por outras colunas QUAL FOI A ESTRATÉGIA ADOTADA PELOS SGBDS Criar um outro arquivo chamado de arquivo de índice que vai conter a Chave de Busca e um ponteiro físico para aquela linha Manipulação Básica de Dados Indexação O rowid é uma pseudo coluna que me dá o endereço físico de uma coluna Dentro dessa estrutura está o endereço completo para o SGBD encontrar o registro lá no disco Ele faz um acesso direto ao DATABLOCK no disco É a forma mais eficiente de trazer do disco a informação Então o que a gente precisaria ter para indexar uma tabela Um outro arquivo com a Chave de Busca ordenada mais o rowid SELECT codautor nome rowid FROM Autores Manipulação Básica de Dados Indexação Então se eu quiser um índice por NOME Vai ser um arquivo separado com todos os NOMES ordenados e mais o rowid para poder localizar a linha nessa tabela Se quiser pesquisar por outra coluna como o CODAUTOR então cria outro índice 1 O SGBD vai lá no índice de código de autor porque está ordenado faz a busca eficiente nesse índice vai encontrar o rowid 2 Se não encontrar nada nem vai na tabela porque ele sabe que não existe aquele dado na tabela 3 Se encontrar ele tem o rowid então ele consegue recuperar aquela informação com um acesso direto ao DATABLOCK Resumindo O índice é um arquivo separado Tem duas informações a Chave de Busca ordenada e o rowid Manipulação Básica de Dados Indexação O que precisamos observar no nosso modelo físico O Oracle cria automaticamente o índice da chave primaria PK Por que ele faz isso Para garantir que não haja repetição de dados Quando vou inserir um novo registro ele tem que procurar se já não existe e a busca precisa ser eficiente Traz vantagem também nas nossas consultas Desvantagem Cada vez que insiro um novo registro ele precisa atualizar os índices Então equilibrar a quantidade de índices X inserções Então vamos criar só os índices extremamente necessários O Oracle não cria índice para Chave Estrangeira Foreign Key Manipulação Básica de Dados Indexação Sintaxe de criação de índices Tabela de AUTORES CREATE INDEX nome do índice ON nome da tabela nome da coluna CREATE INDEX idxautoresnome ON AUTORES nome Resumo do que vimos até agora Índices Relembrando o conteúdo do vídeo anterior Índices O que você vai aprender nessa aula Parte 1 Manipulação Básica de Dados Parte 2 Manipulação Básica de Dados Parte 3 Manipulação Básica de Dados Parte 4 Manipulação Básica de Dados Auto incremento Script do modelo físico DDL Parte 5 Manipulação Básica de Dados Manipulação Básica de Dados Sequence Auto incremento CREATE SEQUENCE seqtitulacoes START WITH 6 Existe uma propriedade chamada Nextval SELECT seqtitulaçõesnextval FROM DUAL rodar várias vezes para observar a variação Para inserir um novo registro com Nextval INSERT INTO AUTORES codautor nome VALUES seqtitulaçõesnextval Dias Gomes Para saber o número atual ou corrente SELECT seqtitulaçõescurrval FROM DUAL Manipulação Básica de Dados Sequence Colocando a Sequence na criação da tabela CREATE TABLE TITULACOES codtitulacao NUMBER4 DEFAULT seqtitulacoesnextval NOT NULL titulo VARCHAR 20 NOT NULL CONSTRAINT pktitulacoes PRIMARY KEY codtitulacao Agora como tenho o DEFAULT com a Sequence para fazer INSERT posso fazer apenas assim INSERT INTO TITULACOES titulo VALUES Bacharel INSERT INTO TITULACOES titulo VALUES Especialista INSERT INTO TITULACOES titulo VALUES Mestre Dinâmica Criação do script do modelo físico Modelo Conceitual Dinâmica Criação do script do modelo físico Modelo Lógico Relacional Resumo do que vimos até agora Auto incremento sequence Criação do script do modelo físico DDL Relembrando o conteúdo do vídeo anterior Auto incremento Script do modelo físico O que você vai aprender nessa aula Parte 1 Manipulação Básica de Dados Parte 2 Manipulação Básica de Dados Parte 3 Manipulação Básica de Dados Parte 4 Manipulação Básica de Dados Parte 5 Manipulação Básica de Dados Script de inserção de dados DML Dinâmica Script de inserção de dados Modelo Conceitual Dinâmica Modelo Lógico Relacional Script de inserção de dados Resumo do que vimos até agora Script de inserção de dados