SQL-Manipulacao-Basica-de-Dados-Aula-Completa

Banco de Dados Azriel Majdenbaum Aula 04 Aula 04 Dividida em cinco partes Apresentação dos principais conceitos relacionados a SQL Manipulação Básica de Dados Data Definition Language DDL Data Manipulation Language DML 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 4 Parte 1 Manipulação Básica de Dados Valores nulos NULL Operadores Like e IN Alteração da estrutura de uma tabela ALTER TABLE Aula 4 Parte 2 Manipulação Básica de Dados Manipulando Datas Aula 4 Parte 3 Manipulação Básica de Dados Parte Restrições de Integridade Aula 4 Parte 4 Manipulação Básica de Dados Parte Exercícios de Revisão Aula 4 Parte 5 Manipulação Básica de Dados Parte Junções de tabelas 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 Manipulação Básica de Dados NULL Valores Nulos Quando criamos uma tabela podemos especificar para cada campo se ele é obrigatório ou não Para especificar que um campo é obrigatório indicamos NOT NULL e para especificar que um campo é de conteúdo opcional indicamos NULL O padrão da linguagem SQL é NULL campo opcional Por exemplo CREATE TABLE PESSOAS cpf VARCHAR20 NOT NULL nome VARCHAR150 NOT NULL idade NUMBER3 NULL endereco VARCHAR150 o campo endereço é NULL implicitamente Manipulação Básica de Dados NULL Valores Nulos Depois para inserir registros podemos especificar cada um dos campos da tabela e inclusive a sua ordem Por exemplo ordem normal das colunas INSERT INTO PESSOAS cpf nome idade endereco VALUES 32809 Maria 25 Rua A 20 outra ordem qualquer das colunas INSERT INTO PESSOAS idade endereco cpf nome VALUES 25 Rua A 20 30599 Pedro valores nulos INSERT INTO PESSOAS cpf nome idade endereco VALUES 29385 Carlos NULL NULL INSERT INTO PESSOAS cpf nome idade endereco VALUES 39582 Alice 80 NULL INSERT INTO PESSOAS cpf nome idade endereco VALUES 78838 Antonio NULL Rua B 80 Manipulação Básica de Dados NULL Valores Nulos ou podemos omitir alguns dos campos Neste caso o valor NULL é implícito Mas para isso temos que omitir os nomes dos campos também no comando de inserção INSERT INTO PESSOAS cpf nome VALUES 90038 Ana Paula INSERT INTO PESSOAS cpf nome idade VALUES 23487 Patricia 18 INSERT INTO PESSOAS cpf nome endereco VALUES 23363 Jose Rua C 50 Manipulação Básica de Dados NULL Valores Nulos Para fazer busca por valores nulos não devemos usar o operador de igualdade convencional É necessário usar os operadores especiais IS NULL e IS NOT NULL Pessoas sem especificação de idade SELECT FROM PESSOAS WHERE idade IS NULL Pessoas que forneceram algum endereço SELECT FROM PESSOAS WHERE endereco IS NOT NULL Manipulação Básica de Dados LIKE e IN Operadores LIKE e IN O operador LIKE é usado para localizar textos O símbolo substitui zero ou mais caracteres Pessoas com nomes iniciando com a letra A SELECT FROM PESSOAS WHERE nome LIKE A Pessoas com nomes iniciando com Ana SELECT FROM PESSOAS WHERE nome LIKE Ana Pessoas com nomes que terminam com Silva SELECT FROM PESSOAS WHERE nome LIKE Silva Pessoas com nomes que contenham Carlos SELECT FROM PESSOAS WHERE nome LIKE Carlos Manipulação Básica de Dados LIKE e IN Operadores LIKE e IN O símbolo substitui exatamente um caractere Pode corresponder a Maria ou Mario SELECT FROM PESSOAS WHERE nome LIKE Mari da Silva O operador IN determina se um valor corresponde a qualquer um dos valores de uma lista Pessoas que tenham 25 30 ou 40 anos de idade SELECT FROM PESSOAS WHERE idade IN 25 30 40 Manipulação Básica de Dados ALTER TABLE Removendo e adicionando novas colunas em tabelas Remover e adicionar colunas em uma tabela significa alterar a sua estrutura Para isso usamos o comando ALTER TABLE Para remover uma coluna de uma tabela usamos o comando ALTER TABLE com a cláusula DROP COLUMN Para adicionar uma nova coluna em uma tabela usamos o comando ALTER TABLE com a cláusula ADD COLUMN ALTER TABLE PESSOAS DROP COLUMN idade ALTER TABLE PESSOAS ADD sexo CHAR1 Manipulação Básica de Dados ALTER TABLE Alterando colunas em tabelas Alterar colunas em uma tabela significa alterar a sua estrutura Para isso usamos o comando ALTER TABLE Para alterar uma coluna de uma tabela usamos o comando ALTER TABLE com a cláusula MODIFY ALTER TABLE PESSOAS MODIFY endereço VARCHAR200 atenção ao nome da coluna Adicionando uma nova coluna chamada salario com valor DEFAULT ALTER TABLE PESSOAS ADD salario NUMBER82 DEFAULT 130556 NOT NULL Resumo do que vimos até agora Manipulação Básica de Dados Valores nulos NULL Operadores Like e IN Alteração da estrutura de uma tabela ALTER TABLE Relembrando o conteúdo do vídeo anterior Valores nulos NULL Operadores Like e IN Alter Table Add Column Alter Table Drop Column Alter Table Modify O que você vai aprender nessa aula Parte 1 Manipulação Básica de Dados Parte 2 Manipulação Básica de Dados Manipulando Datas 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 DATE Introduction 14 Manipulando datas Nossa tabela PESSOAS armazena a idade de cada pessoa mas essa não foi uma boa escolha de projeto porque precisaríamos atualizar as idades ano após ano Uma escolha melhor teria sido CREATE TABLE PESSOAS cpf VARCHAR20 NOT NULL nome VARCHAR150 NOT NULL datanasc DATE NULL alterado endereco VARCHAR150 NULL Usando os comandos para alteração da tabela vamos modificar a sua estrutura para substituir idade por data de nascimento Observação A manipulação de datas é muito dependente do produto de banco de dados que se está usando As informações que constam aqui se referem ao SGBD da Oracle O tipo DATE armazena o século todos os quatro dígitos de um ano o mês o dia a hora formato 24h os minutos e os segundos ALTER TABLE PESSOAS DROP COLUMN idade ALTER TABLE PESSOAS ADD datanasc DATE NULL Manipulação Básica de Dados DATE Introduction 15 Inserindo datas em um campo DATE O formato padrão varia conforme a instalação do SGBD Supondo que a sua instalação apresenta o formato DDMONYYYY temos um dia com 2 dígitos as três primeiras letras do mês inglês e um ano com 4 dígitos Exemplo INSERT INTO PESSOAS cpf nome datanasc endereco VALUES 29048 Roberto 03FEB1980 Rua D 80 Formato alternativo padrão ANSI YYYYMMDD Devese acrescentar a palavra DATE antes da data INSERT INTO PESSOAS cpf nome datanasc endereco VALUES 29048 Roberto DATE 19800203 Rua D 80 Manipulação Básica de Dados DATE Funções de conversão de datas As funções TOCHARe TODATE convertem uma datahorário em uma string e vice versa A sintaxe geral é TOCHARx formato e TODATEx formato O elemento SYSDATE captura data e horas atuais SELECT TOCHARSYSDATE MONTH DD YYYY HH24MISS FROM DUAL SELECT TOCHARSYSDATE YYYY ANO FROM DUAL SELECT TOCHARSYSDATE MONTH MÊS FROM DUAL SELECT TOCHARSYSDATE HH24MISS HORÁRIO FROM DUAL SELECT nome TOCHARdatanasc MONTH DD YYYY FROM PESSOAS Manipulação Básica de Dados DATE Funções de conversão de datas INSERT INTO PESSOAS cpf nome datanasc endereco VALUES 29920 Beto TODATE25FEB1979 213628 DDMONYYYY HH24MISS Rua E 80 SELECT NOME TOCHARDATANASC DDMONYYYY HH24MISS DATADENASCIMENTO FROM PESSOAS Manipulação Básica de Dados DATE Aritmética de datas Em SQL é possível realizar as seguintes operações sobre datas DATE NUMBER DATE DATE NUMBER DATE DATE DATE número de dias entre as datas Por exemplo SELECT SYSDATE 1 FROM DUAL Observação A tabela DUAL é uma tabela dummy no SGBD Oracle Ela contém somente uma coluna chamada dummy e apenas uma linha que contém o valor X Ela é utilizada sempre que se deseja retornar uma única linha em uma consulta e também porque todo comando SELECT deve possuir uma cláusula FROM Os especificadores de formato podem ser obtidos nos links a seguir httpwwworadevcomoracledateformatjsp Manipulação Básica de Dados DATE Parâmetros de sessão Cada sessão pode ter um formato padrão diferente Uma sessão é iniciada quando abrimos uma conexão com o banco de dados e é terminada quando a conexão é fechada Podemos consultar os parâmetros da sessão com o comando SELECT FROM NLSSESSIONPARAMETERS É possível alterar o formato de data padrão da sessão atual com o comando ALTER SESSION SET NLSDATEFORMAT MONTHDDYYYY Manipulação Básica de Dados DATE Outras Funções de datahorário EXTRACT YEAR MONTH DAY HOUR MINUTE SECOND TIMEZONEHOUR TIMEZONEMINUTE TIMEZONEREGION TIMEZONEABBR FROM datevalue intervalvalue Por exemplo SELECT EXTRACTYEAR FROM DATE 20030822 FROM DUAL retorna 2003 SELECT EXTRACTMONTH FROM DATE 20030822 FROM DUAL retorna 8 SELECT EXTRACTDAY FROM DATE 20030822 FROM DUAL retorna 22 Uma função bastante útil no Oracle é EXTRACT A sintaxe geral é Manipulação Básica de Dados DATE Outras Funções de datahorário ADDMONTHSx y Adiciona y meses a uma data x SELECT ADDMONTHSsysdate 2 FROM DUAL LASTDAYx Retorna o último dia do mês de uma determinada data SELECT LASTDAYsysdate FROM DUAL MONTHSBETWEENx y Retorna o número de meses entre duas datas SELECT MONTHSBETWEENsysdate datanasc FROM PESSOAS Dinâmica 1 Calcule e apresente sua idade 2 Apresente a data atual sysdate explorando outros formatos com base na documentação do Oracle Os especificadores de formato podem ser obtidos nos links a seguir httpwwworadevcomoracledateformatjsp Resumo do que vimos até agora Manipulando Datas Inserindo datas em um campo DATE Funções de conversão de datas Aritmética de datas Parâmetros de sessão Outras funções de datas Relembrando o conteúdo do vídeo anterior Manipulando Datas Inserindo datas em um campo DATE Funções de conversão de datas Aritmética de datas Parâmetros de sessão Outras funções de datas 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 Restrições de Integridade 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 INTEGRIDADE Introduction 14 Restrição de Integridade de Entidade Para manter bancos de dados com qualidade é necessário garantir a integridade dos dados armazenados A integridade de entidade define uma linha como entidade exclusiva de uma determinada tabela Por exemplo não podemos permitir que exista mais de uma pessoa com o mesmo CPF ou mais de um veículo com uma mesma placa Para isso devemos escolher para cada tabela o conjunto mínimo de colunas atributos que unicamente identifica cada registro Na tabela PESSOAS a coluna cpf deve ter valores únicos Na tabela VEICULOS a coluna placa deve ter valores únicos Em alguns casos será necessário usar duas ou mais colunas para garantir unicidade Chamamos esses conjuntos de atributos de chave Às vezes uma tabela pode apresentar mais de uma alternativa para essa escolha Considere por exemplo uma tabela de ALUNOS contendo os campos nroMatricula e cpf Nesse caso os dois campos individualmente devem ser únicos Nos bancos de dados chamamos cada um desses casos de chaves candidatas Depois que definimos todas as chaves candidatas para uma determinada tabela escolhemos apenas uma delas como sendo a principal Por exemplo se uma tabela apresenta três chaves candidatas escolhemos uma delas como sendo a principal arbitrariamente já que qualquer uma delas serviria para esse propósito Chamamos a chave principal de chave primária ou em inglês PRIMARY KEY PK As Manipulação Básica de Dados INTEGRIDADE Introduction 15 Restrição de Integridade de Entidade Em SQL usamos as restrições PRIMARY KEY para a principal e UNIQUE para as demais AKs Naturalmente toda PK é também NOT NULL por quê Por exemplo CREATE TABLE ALUNOS nroMatricula VARCHAR10 PRIMARY KEY cpf VARCHAR20 UNIQUE email VARCHAR100 UNIQUE nome VARCHAR150 NOT NULL anoIngresso NUMBER4 NOT NULL endereco VARCHAR150 NULL sexo CHAR1 NOT NULL Essas declarações de restrições PRIMARY KEY e UNIQUE estão na forma inline ou seja são especificadas junto com a criação de cada campo no comando CREATE TABLE Manipulação Básica de Dados INTEGRIDADE Introduction 16 Restrição de Integridade de Entidade CREATE TABLE ALUNOS nroMatricula VARCHAR10 NOT NULL cpf VARCHAR20 NOT NULL email VARCHAR100 NOT NULL nome VARCHAR150 NOT NULL anoIngresso NUMBER4 NOT NULL endereco VARCHAR150 NULL sexo CHAR1 NOT NULL CONSTRAINT PKALUNOS PRIMARY KEY nroMatricula CONSTRAINT AK1ALUNOS UNIQUE cpf CONSTRAINT AK2ALUNOS UNIQUE email A forma alternativa que usa a palavra CONSTRAINT restrição é preferível por ser mais flexível Experimente agora tentar inserir registros diferentes mas com o mesmo número de matrícula ou com um mesmo número de CPF para ver como o SGBD impede a operação e relata um erro de duplicidade de chave Manipulação Básica de Dados INTEGRIDADE Introduction 17 Restrição de Integridade de Domínio A integridade de domínio visa a garantir que os dados armazenados respeitem determinados valores permitidos Podemos restringir o intervalo de dados permitido para um campo Alguns exemplos de restrição de domínio são garantir que o preço de um produto não pode ser zero ou ter um valor negativo garantir que o campo status de um pedido tenha somente um dos seguintes valores ABERTO PENDENTE FECHADO garantir que o campo sexo somente aceite os valores M ou F Esses casos podem ser garantidos usando a restrição CHECK Por exemplo vamos garantir que o campo anoIngresso possua sempre um valor superior a 2000 e que o campo sexo permita apenas os valores M ou F em maiúsculas ALTER TABLE ALUNOS ADD CONSTRAINT CKAnoIngr CHECK anoIngresso 2000 ALTER TABLE ALUNOS ADD CONSTRAINT CKsexo CHECK sexo IN M F Manipulação Básica de Dados INTEGRIDADE Introduction 18 Restrição de Integridade Referencial A integridade referencial é usada entre duas tabelas para garantir que os dados de uma coluna da primeira tabela se referem aos dados registrados em uma coluna da segunda tabela CREATE TABLE ESTADOS uf CHAR2 NOT NULL nome VARCHAR240 NOT NULL regiao CHAR2 NOT NULL CONSTRAINT PKESTADOS PRIMARY KEY uf CREATE TABLE CIDADES codcidade NUMBER4 NOT NULL nome VARCHAR260 NOT NULL uf CHAR2 NOT NULL CONSTRAINT PKCIDADES PRIMARY KEY codcidade Por exemplo suponha uma tabela que registra os estados unidades federativas do país Suponha agora outra tabela que registra as cidades vinculandoas aos estados Como não há verificação de consistência integridade entre os dados das duas tabelas seria perfeitamente possível cadastrar uma cidade especificando sua uf como XX ou ainda especificando sua uf com uma sigla de estado ainda não cadastrada na tabela de estados Manipulação Básica de Dados INTEGRIDADE Introduction 19 Restrição de Integridade Referencial Então como podemos garantir que ao incluir uma nova cidade o seu campo uf se refere a um estado que realmente existe ou seja já tenha sido anteriormente cadastrado Experimente agora inserir dados nas duas tabelas depois de enviar o comando ALTER TABLE para o banco de dados Tente também forçar uma inconsistência de dados como por exemplo inserir uma cidade para um estado que ainda não esteja cadastrado A resposta é com uma chave estrangeira em inglês FOREIGN KEY FK O comando a seguir cria um vínculo entre as duas tabelas definindo uma regra de integridade relacional do campo uf da tabela de cidades para o campo uf da tabela de estados ou seja para a chave primária da tabela pai ALTER TABLE CIDADES ADD CONSTRAINT FKESTCID FOREIGN KEY uf REFERENCES ESTADOS uf Observação se houver algum registro que não atenda à restrição que estamos tentando adicionar o SGBD retorna um erro por quê Manipulação Básica de Dados INTEGRIDADE Introduction 110 Relacionamentos do tipo muitosparamuitos Dizemos que o relacionamento existente entre as tabelas ESTADOS e CIDADES é do tipo um paramuitos ou 1N Em outras palavras estamos dizendo que cada estado pode ter várias cidades mas que cada cidade pertence a apenas um estado No entanto em alguns casos precisamos representar relacionamentos do tipo muitos paramuitos ou NN Esse é o caso dos relacionamentos entre MEDICOS e PACIENTES ou então entre PESSOAS e PROJETOS por exemplo Infelizmente não há como representar relacionamentos NN entre usando apenas duas tabelas em um banco de dados relacional No entanto podemos resolver esse problema criando uma terceira tabela ou seja uma tabela intermediária entre as duas tabelas originais e criando dois relacionamentos 1N entre as tabelas Resumo do que vimos até agora Restrições de Integridade Entidade Domínio Referencial Relembrando o conteúdo do vídeo anterior Restrições de Integridade Entidade Domínio Referencial 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 Dinâmica Filmes Dinâmica Estados da Federação Parte 5 Manipulação Básica de Dados Dinâmica Execute o script abaixo no OracleLive e após faça o que se pede CRIAÇÃO DO BANCO Drop table filmes CREATE TABLE Filmes codfilme NUMBER5 titulo VARCHAR250 ano NUMBER4 diretor VARCHAR100 genero VARCHAR80 atoresPrincipais VARCHAR1000 duracao NUMBER4 em minutos valorIngresso NUMBER52 BD INICIAL insert into filmes values1Cosmopolis2012David CronenbergDramaRobert Pattinson Juliette Binoche Sarah Gadon Mathieu Amalric1082299 insert into filmes values2The Awakening2012Nick Murphy HorrorRebecca Hall Dominic West Imelda Staunton Lucy Cohu1072999 insert into filmes values3The Shawshank Redemption1994Frank DarabontDramaCrimeTim Robbins Morgan Freeman and Bob Gunton1422599 insert into filmes values4Pulp Fiction1994Quentin TarantinoCrimeThrillerJohn Travolta Uma Thurman and Samuel L Jackson1542999 insert into filmes values5One Flew Over the Cuckoos Nest1975Milos FormanDramaJack Nicholson Louise Fletcher and Michael Berryman1335599 insert into filmes values6Inception2010Christopher NolanActionLeonardo DiCaprio Joseph GordonLevitt and Ellen Page1487999 insert into filmes values7Fight Club1999David FincherDramaBrad Pitt Edward Norton and Helena Bonham Carter1397599 insert into filmes values8Casablanca1942Michael CurtizDramaHumphrey Bogart Ingrid Bergman and Paul Henreid1026299 insert into filmes values9The Matrix1999Andy Wachowski Lana WachowskiActionKeanu Reeves Laurence Fishburne and CarrieAnne1362899 insert into filmes values10Se7en1995David FincherCrimeMorgan Freeman Brad Pitt and Kevin Spacey1274299 FIM Escreva os comandos SELECT para os itens abaixo a o título o ano e o diretor de todos os filmes b os filmes de horror de 2012 c o título e o ano dos filmes com duração maior do que 2 horas d o título e a duração dos dramas lançados na década de 1990 com pelo menos 1 hora e 20 minutos de duração dos diretores cujos nomes começam pela letra D e o título o gênero e o valor do ingresso dos filmes a partir de 2006 mostrando os valores inflacionados em 863 f a quantidade de filmes de ação com ingressos que custam mais do que R 2000 g os nomes de todos os diretores cadastrados sem repetir e em ordem alfabética Dinâmica Escreva os comandos UPDATE para os itens abaixo a aumentar em 10 minutos a duração dos filmes em que participa o ator Morgan Freeman b dar um desconto de 10 para os filmes de drama do ano 2012 c acrescentar um asterisco no final dos títulos dos filmes com duração menor ou igual a 120 minutos Use o operador para concatenar strings Escreva os comandos DELETE para os itens abaixo a excluir os filmes com valor de ingresso superior a R 6000 b excluir os filmes em cujo título aparece a palavra assombrado ou cujo sobrenome do diretor é David Fincher Resumo do que vimos até agora Exercícios Dinâmica Filmes Dinâmica Estados Relembrando o conteúdo do vídeo anterior Dinâmica com tabela Filmes 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 Junção de tabelas Manipulação Básica de Dados Join Até o momento consultamos dados a partir de uma única tabela Esta parte do material introduz o conceito de JUNÇÕES que permitem a consulta de dados de mais de uma tabela pela relação entre chaves estrangeiras e chaves primárias SELECT ESTuf ESTnome CIDuf CIDnome FROM ESTADOS EST INNER JOIN CIDADES CID ON ESTuf CIDuf SELECT ESTuf ESTnome CIDuf CIDnome FROM ESTADOS EST LEFT OUTER JOIN CIDADES CID ON ESTuf CIDuf Manipulação Básica de Dados Cross Join O produto cartesiano é uma operação da teoria de conjuntos Executar um produto cartesiano entre duas tabelas resulta na combinação de todas as linhas registros da primeira tabela com todas as linhas da segunda tabela Observação Para consultar dados de uma ou mais tabelas relacionadas devemos utilizar operações denominadas junções JOINs A junção de duas ou mais tabelas é equivalente em termos de resultado final à realização do produto cartesiano comparando o valor de certos atributos e aplicando uma projeção e uma seleção ao resultado Manipulação Básica de Dados Outer Join Junções externas Em um INNERJOIN somente compõem o resultado as linhas da tabelapai e da tabela filha que tenham a condição atendida No entanto podem existir linhas não relacionadas por possuírem valor NULL na coluna FK Em uma junção externa ou OUTER JOIN é possível definir que todas as linhas de determinada tabela pai ou filha farão parte do resultado inclusive as que forem NULL nas colunas da condição SELECT ESTuf ESTnome CIDuf CIDnome FROM ESTADOS EST LEFT OUTER JOIN CIDADES CID ON ESTuf CIDuf SELECT ESTuf ESTnome CIDuf CIDnome FROM ESTADOS EST RIGHT OUTER JOIN CIDADES CID ON ESTuf CIDuf SELECT ESTuf ESTnome CIDuf CIDnome FROM ESTADOS EST FULL OUTER JOIN CIDADES CID ON ESTuf CIDuf Resumo do que vimos até agora Junção de tabelas