TLBD II - Tcnicas de Linguagem de Banco de Dados II

TLBD 3 Tcnicas de linguagem de banco de dados 3Professor EduardoProfessor Enildo

Adio de ComentriosComentrios so informaes que adicionamos ao cdigo que desenvolvemos para documentar os scripts, permitindo que outros desenvolvedores compreendam melhor o que escrevemos.No script operadores aritmticos, acrescente o seguinte texto:

Comentrios com mltiplas linhasOuta forma de adicionar comentrios atravs de barras com asterisco, que permitem comentrios com mltiplas linhas. Exemplo:

Teste operacional bsicoSELECT CURRENT_TIMESTAMP;SELECT SUSER_SNAME();

Para executar a instruo, use o comando de menu Consultar/executar ou simplesmente acione a tecla de funo . Detalhes de PainelMenu File:File/Open/FilePermite ao usurio abrir salvar arquivos e script, que tem a terminao QSL

Object Explorer:Painel a esquerda da tela, mostra estrutura hierrquica as instncias conectadas, seus bancos de dados e suas estruturas de tabelas, procedures e functions.

possvel conectar diversas instncias ao mesmo tempo e escrever scripts, acessar objetos, desde que voc tenha as permisses.Editos de Querys:Com o boto New Query possvel abrir novas janelas de query e tambm usar a combinao de atalhos Ctrl+N.

Database Selection:Um Listbox localizado na barra de botes que lista todos os bancos de dados criados na instncia, de sistema ou criados pelo usurio.

Na janela Object Explorer encontraremos os bancos de dados do sistema, criados durante o processo de instalao do produto.

Master (registra informaes de instncia, login, servidores conectados e configuraes do sistema.

Model ( utilizado como um template para a criao de outros banco de dados.

Msdb (armazena informaes sobre agendamento de jobs e configuraes de algumas funcionalidades.

Tempdb (armazena todos os bjetos que so criados temporariamente.Banco de dados do SistemaPrimeiros Scripts em T-SQLA linguagem Transact-SQL uma extenso proprietria do SQL comum desenvolvido pela Microsoft.

Abra uma janela de query e digite o treco de cdigo seguinte:

Clique no boto execute ou F5.O SQL Server as strings so indicadas com aspas simples.

Como salvar o Script:

Clique no boto salvar, localizado no painel superior, a tela para escolha do nome e da localizao do arquivo deve se abrir. Informe o nome Script1, selecione uma pasta para salvar o arquivo e clique em OK.Variveis no T-SQL e Tipos de DadosCom o T-SQL possvel criar variveis que recebem e retornam valores, utilizando os tipos de dados disponveis no SQL Server. Os valores atribudos s variveis so armazenados em memria, assim que a sesso encerrada, os dados a essas variveis so perdidos.

New query:

Execute.

Tipos de variveisToda declarao de variveis no T-SQL deve ser tipada, ou seja, deve utilizar um dos tipos de dados disponveis no SQL Server, que so os seguintes:

Tipos numricos exatosBigintbitdecimalintmoneynumericsmallintsmallmoneytinyintTipos numricos aproximadosfloatrealData e horadatedatetime2datetimedatetimeoffsetsmalldatetimetimeOutros tipos de dadosCursor hierarchyidSQL_varianttabletimestampuniqueidentifierxmlBinriosbinaryimagevarbinaryCadeia de caractereschartextvarcharCadeia de caracteres unicodencharntextnvarcharConcatenao de StringsUtilizando o operador de adio +, podemos concatenar strings de todo tipo, como variveis declaradas com tipos de caracteres.

New query

Execute.

Vale observar que a atribuio de calor de variveis pode ser feita com SET ou SELECT, levando ao mesmo resultado. A atribuio com SET est dentro dos padres ANSI, mas com SELECT permite atribuio mltipla.

New query

Execute.

Operadores AritmticosOs operadores aritmticos em T-SQT so:

operaooperadoradio+subtrao-multiplicao*diviso/mod%New query:

ExecuteObs: AS para definir um nome para a coluna de resultado desta operao.

ltima aula 04/02/2014 - prxima aula 11/02/2014

16Comparadores Lgicos e Controle de Fluxo com T-SQL.No contexto da programao de sistemas informatizados, controle de fluxo a ordem em que os comandos e instrues so executados.

Um comando de controle de fluxo, com base em uma condio lgica esttica ou dinmica, permite determinar o prosseguimento de dois ou mais caminhos possveis do cdigo.Comparadores LgicosOperadorDescrio>Maior que=Maior ou igual a 3;Exerccio 7Realize uma consulta onde mostre o nome do cliente com cdigo de numero 4, e uma data limite de 30 dias a partir da data de hoje.

SoluoRealize uma consulta onde o mostre o nome do cliente com cdigo de numero 4, e uma data limite de 30 dias a partir da data de hoje.

Operador ANDPodemos fazer comparaes mais completas, envolvendo outros campos, usando o operador AND. Por exemplo, se quisermos ver os clientes com o cdigo maior que 3 e com data de nascimento posterior ao ano de 1950:

SELECT Codigocli, Nomecli, Enderecocli, Telefonecli, Emailcli,DataNascimentocli FROM TBclientes WHERE Codigocli > 3 AND DataNascimentocli >'19500101';Operador ORO comando OR permite retornar valores que satisfazem uma ou outra condio.Exemplo:

SELECT Codigocli, Nomecli, Enderecocli, Telefonecli, Emailcli,DataNascimentocli FROM TBclientes WHERE Codigocli > 3 OR DataNascimentocli >'19500101';Operador BetweenPodemos fazer comparaes de faixas de dados com comando BEWEEEN utilizando um valor inicial e um final de comparao. Exemplo:

SELECT * FROM Tbclientes WHERE Codigocli > 1 AND DataNascimentocli BETWEEN '19000101' AND '19850101';Operador LIKE Podemos usar o operador LIKE nas consultas para realizar pesquisas aproximadas em campos de caracteres. Por exemplo, para consultar todos os registros com o primeiro caractere do campo nomecli igual a B:

SELECT * FROM TBclientesWHERE Nomecli LIKE 'B%';

Caractere %No script anterior foi utilizado o caractere %, que permite realizar a consulta aproximada. Podemos usar o % para pesquisar no apenas no comeo, mas tambm no final da string. Exemplo:

SELECT * FROM TBclientes WHERE Nomecli LIKE '%n';

Exemplo no meio da string:

SELECT * FROM TBclientes WHERE Nomecli LIKE '%m%';Exerccio 8Insira os dois registros abaixo no BDProjetoTSQL na tabela TBclientes;

NomeEndereoTelefoneE-mailData NascMarcelo SilvaRua da Feia, 222222-3333 marcelosil@hotmail.com 28/10/2000

Marcela Teixeira Rua S, 142222-4334 marcelatei@hotmail.com 17/01/1999

Soluo

Uso do LIKE com colchetes []O comando LIKE pode ser complementado com os colchetes para pesquisas mais complexas. Por exemplo, para consultar todos os registros de clientes que tm como primeiro nome , sem importar o sobrenome. Exemplo:

SELECT * FROM TBclientes WHERE Nomecli LIKE 'Marcel[ao]%';

O Comando TOPO comando TOP permite limitar o nmero de registro que sero mostrados pela instruo SELECT, a qual recebe como argumento nico o nmero de registros a serem mostrados pela query. Veja exemplo do script mostrando os dois primeiros registros da tabela:

-----------------------------------------------------------------

Exemplo com *

Ordenao dos Registros com o ORDER BYPara dispor os registros em uma determinada ordem, use o comando ORDER BY. O argumento ORDER BY espera apenas a indicao das colunas em que voc deseja ordenar a seleo desejada. Exemplo:

Exemplo em ordem decrescente:

Atualizao dos Registros com o Comando UPDATEO comando UPDATE permite atualizar registros em tabelas do SGBD, alterando o valor de uma ou mais colunas. Exemplo alterando os dados do cliente com o cdigo igual a 1, que mudou de endereo.

PRXIMA AULA 11/03/201476Exerccio 9 Adicione um campo nomeado como Cep do tipo VARCHAR(10) na tabela Tbclientes na base de dados BDProjetoTSQL.

Soluo

As Funes ISNULL e COALESCE A funo ISNULL utilizada para tratar campos com valores nulos, seja em consultas ou em trechos de cdigos SQL. Veja uma consulta completa de todos as colunas da tabela TBclientes;

Observe que a coluna Cep est com indicador NULL em todos os registros da tabela, pois no contm nenhum dado.

Para fins de relatrios e de apresentaes de dados mais finalizados, possvel trocar o indicador NULL por outra mais descritivo. Exemplo podemos trocar o indicador NULL por Sem Cep :

Analise as consultas abaixo

Exerccio 10Faa uma atualizao no registros do cliente com o cdigo 1, inserindo no campo cep o dado 99909-303.Soluo

Ao executar a consulta realizada anteriormente, observe que para o cliente com cdigo 1, em que alteramos o valor da coluna Cep, o valor mostrado o da coluna.

A funo COALESCE tem o mesmo propsito da funo ISNULL, porm a funo COALESCE faz parte do padro ANSI, ou seja, est presente em outros bancos de dados, diferentes do ISNULL que uma extenso do SQL Server.A funo COALESCE tem a mesma sintaxe do ISNULL, porm com uma diferena bsica: pode receber mais de dois parmetros, diferente da funo ISNULL que pode receber apenas dois.Exemplo:

Excluso de Registros com o comando DELETEPara excluir um registro que foi inserido em uma tabela do SQL Server, devemos usar o comando DELETE que, assim como os comandos SELECT e UPDATE, pode se utilizar da clusula WHERE para filtrar os dados a serem excludos.

Exemplo excluindo o cliente com o cdigo igual a sete:

Uma questo importante quando exclumos registros de tabelas com um campo com propriedade Identity que, depende dos registros que esto sendo excludos, podemos ter gaps na sequncia do campo Identity.

Exerccio 11 1. Insira um novo registro com os seguintes dados:

Nome ( Joselito Manga)Endereo ( Rua das Minas, 33)Telefone (9191-3343)Email (manga@etec.com)Data de Nascimento ( 22/04/1990)

2. Aps inserir o registro faa uma consulta que mostre todos os dados de todos os clientes. SoluoObserve que na lista de resultados temos uma lacuna na sequncia dos valores Identity.

Excluso de Todos os Registros com o Comando TRUNCATE TABLEPara excluir todos os registros de uma tabela, podemos usar o comando DELETE sem nenhuma restrio na clusula WHERE. Se a tabela no possui relacionamentos e desejamos excluir todos os registros sem nenhuma descriminao, mais recomendado utilizar o comando TRUNCATE TABLE, por questes de desempenho, mas tenha muito cuidado ao utilizar esse comando.

Exemplo da sintaxe:

Observao: O comando TRUNCATE TABLE exclui os registros da tabela, porm d um RESET na coluna Identity.

Tabelas Temporrias Tabelas temporrias so comuns, mas existem apenas no escopo da sesso em que foram criadas. No esto armazenadas fisicamente no banco de dados de maneira definitiva. Para criar uma tabela temporria, basta acrescentar o prefixo # ao nome da tabela na sua criao.

Observe que podemos criar essa tabela no mesmo Database em que criamos a tabela de clientes, pois na verdade tabelas temporrias so armazenadas no Tempdb.

Opcionalmente, podemos criar uma tabela temporria de outra maneira atravs do comando SELECT, usando a instruo INTO. Essa tcnica cria uma tabela temporria implicitamente j com os dados da tabela preexistente, diferente do comando CRATE TABLE que executa essa operao de maneira explcita e vazia.

Exemplo sintaxe:

O SELECT INTO permite a criao de tabelas fsicas tambm. Basta remover o caractere #.Tabelas temporrias se dividem em dois tipos:

Globais;Locais.

Tabelas locais so criadas com o caractere Sharp (#).Tabelas globais so criadas com dois caractere Sharp (##).

Exemplo:

SELECT * INTO # TBClientes FROM TBClientes;

A criao de tabelas temporrias muito til para validao de dados e realizao de testes em scripts mais complexos.Relacionamento e Integridade ReferencialIntegridade referencial uma propriedade relacionada aos valores de uma coluna de uma tabela de banco de dados. Todos os valores dessa coluna tm uma correspondncia em outra coluna de outra tabela. Se esta condio estiver satisfeita, podemos dizer que h uma integridade referencial entre duas tabelas.PRXIMO 15/03/201494Chave EstrangeiraUma coluna declarada como chave estrangeira quando faz referncia a dados de uma coluna que declarada chave Primria em outra tabela. A chave Estrangeira pode conter valores nulos (ausncia de dados), porm os registros que to preenchidos devem ter correspondncia na outra tabela.No caso de tentativa de excluso de registro que so referenciados por uma chave estrangeira, um banco de dados relacional pode impor a integridade referencial, impedindo a excluso, excluindo todos os registros relacionados ou at mesmo tornando nulas as colunas de registros que faziam referncia chave que foi excluda. Verificando tabelas existente

No SQL Server, para cada banco de dados criado em uma instncia, um novo registro na view de sistema sys.databases.

Alm de vrias outras ferramentas que substituem o MS em forma grfica (Exemplo: SQL DBX), podemos fazer via prompt de comando ou em uma janela de consulta.

Exemplo de comando para verificar quais as tabelas que existem na base de dados em uso:

Exemplo de comando para verificar quais as tabelas existem em uma base especifica:

Assim, basta consultar esta tabela para realizar a tarefa desejada

Exerccio 12 A) Na base de dados BDProjetoTSQL. Realize uma consulta para verificar quais tabelas existem.

B)Faa uma consulta para verificar todos os registros da tabela existente.

C) Inclua os seguintes registros na tabela Existente;1. Nome (Bill Gates), endereo (Rua da Feia, 12), telefone (2222-3333), e-mail (bill@hotmail.com) e data de nascimento (28/10/1955); 2. Nome ( Benjamin Franklin), endereo (Rua S Ns Dois, 3 14) , telefone (2222-3334) e-mail (benjamin@hotmail.com) e data de nascimento (17/01/1906);

3. Nome ( John Lennon), endereo (Rua Deus-te-guarde, 123) , telefone (2222-3335) e-mail (john@hotmail.com) e data de nascimento (09/10/1940);

4. Nome ( Thomas Edison), endereo (Largo da Boa Morte, 32) , telefone (2222-3336) e-mail (thomas@hotmail.com) e data de nascimento (11/02/1847);

5. Nome ( Steve Jobs), endereo (Rua Capito da Meia-noite, 55) , telefone (2222-3337) e-mail (steve@hotmail.com) e data de nascimento (24/02/1955).

A) Na base de dados BDProjetoTSQL. Realize uma consulta para verificar quais tabelas existem.

B)Faa uma consulta para verificar todos os registros da tabela existente.

C) Inclua os seguintes registros na tabela Existente;

Soluo

D) Na base de dados BDProjetoTSQL.Crie uma tabela com nome de TBprodutos, com os campos;

Codprod (Int Identity/PK)Descprod (Varchar(150))Valorprod (Numeric(18,2))Ativo (Bit)

Para atender ao requisito de valor padro no campo Ativo, informe a instruo default, em que ser informado o valor 1.

Exemplo de declarao default:Campo Bit Default(1);D) Na base de dados BDProjetoTSQL.Crie uma tabela com nome de TBprodutos, com os campos;

Para atender ao requisito de valor padro no campo Ativo, informe a instruo default, em que ser informado o valor 1.

Exemplo de declarao default:Campo Bit Default(1);Soluo

E) Na base de dados BDProjetoTSQL. Crie uma tabela com nome de TBvendas, deve apresentar a seguinte estrutura;

Exemplo de referncia de campo: REFERENCES ()ColunaTipo de dadosDescrioNotafiscalInt (Identity)Chave primria da tabelaCodigocliIntChave estrangeira da tabela TBclienteCodigoprodutoIntChave estrangeira da tabela TBprodutoDatavendaDateTimeData da venda, valor padro, data e hora atuaisQuantidadeIntQuantidades de itens vendidosValortotalNumeric (18,2)Valor total da venda, calculado pelo quantidade de itens, e valor de venda do produtoPRXIMO 18/03/2014102E) Na base de dados BDProjetoTSQL. Crie uma tabela com nome de TBvendas, deve apresentar a seguinte estrutura;

Exemplo de referncia de campo: REFERENCES ()

Anlise de instruesIDENTITY, controla a numerao automaticamente conforme nmeros pr-estabelecidos.PRIMARY KEY, indica um campo da tabela como chave primria, o mesmo no ir se repetir nos outros registros.REFERENCES, recebe como argumento a tabela e o campo em que a respectiva chave primria (PK) foi definida, portanto, todos os valores atribudos coluna deve ter uma correspondncia em outra tabela.GETDATE(), retorna a data atual do sistema operacional em que a instncia foi instalada.

CONSTRAINT"Constraint" so objetos no banco de dados que servem para definir sua integridade. Ou seja, so as chaves primrias, chaves estrangeiras, chaves secundrias e outros elementos que garantem segurana aos dados das tabelas, evitando que sejam excludos ou alterados indevidamente os dados que entram em sua base.Constraint FOREIGN KEY - FKSempre que criamos uma chave estrangeira, o SQL Server cria uma constraint para assegurar a integridade daquela chave estrangeira. Quando criamos a chave estrangeira de clientes na criao da tabela usando a instruo references, a criao da constraint foi implcita com um nome gerado automaticamente. Quando criamos com o comando ALTER TABLE, podemos atribuir um nome explcito a essa constraint.

Exemplo:

Verificando a estrutura da TBvendas

Verificando as tabelas Existente na base BDProjetoTSQLComando para verificar quais as tabelas existem na base de dados em uso

SELECT * FROM information_schema.tables;

Criando um DiagramaClique com o boto direito do mouse na pasta Database Diagrams e clique na opo New Database Diagram,

Selecione as tabelas e clique no boto Adicionar.

Diagrama integridade referencial criado.

Para melhor visualizao, podemos posicionar as ligaes das tabelas.

Realize uma consulta nas tabelas para verificar os registros.

Exerccio 13 Cdigo do ProdutoDescrioValor6Tecnologia da informao23.000,005Sistemas da informao25.000,009Segurana da informao22.000,0010Processamentos de dados21.000,001Cincia da computao24.000,007Fsica computacional23.500,003Engenharia da computao25.000,004Engenharia de software24.000,008Engenharia de sistemas23.000,002Engenharia mecatrnica25.000,00Insira os registros abaixo na TBprodutos da base de dados BDProjetoTSQL. Os registros so fictcios.Soluo exerccio 13

Observe o resultado

Exerccio 14 Cdigo ClienteCdigo ProdutoData VendaQuant.Valor da venda6201/01/2014125.000,002601/01/2014123.000,006101/01/2014124.000,004401/01/2014224.000,004301/06/2014125.000,003701/06/2014223.500,002501/06/2014125.000,0031001/06/2014221.000,002201/06/2014125.000,004501/06/2014125.000,002701/06/2014123.500,003401/06/2014224.000,004701/06/2014123.500,00Insira os registros abaixo na TBvendas da base de dados BDProjetoTSQL. Os registros so fictcios.Analisando soluo

A instruo INSERT em conflito com a restrio FOREIGN KEY "FK__TBvendas__Codigo__15502E78". O conflito ocorreu no banco de dados "BDProjetoTSQL", tabela "dbo.TBclientes", a coluna 'Codigocli'. A instruo foi encerrada.

Verificando TBclientesErro de referncia, no pode ser efetuada uma venda para um cliente que no est cadastrado!

Ao tentar executar o passo quatro, inserir os registros na TBvendas, ocorre o mesmo erro. Por que?Se voc analisou o passa trs percebeu que quando executou o comando SELECT * FROM TBclientes o cdigo 6 no existe, ou seja, ele no existe! Por tanto o erro continua.

Possvel SoluoTentando solucionar o problema do cdigo do cliente, poderamos pensar em truncar a tabela, mas por conta da constraint isso no permitido.

Obs: em um banco de produo isso nunca ser uma possvel soluo!

Para fins de didtico vamos tentar truncar a tabela Tbcliente para solucionar o nosso problema:

Como indica a mensagem de erro Msg 4712, o comando TRUNCATE no pode ser executado pois existe uma constraint restrio na tabela.

Como solucionar? Vamos aproveitar para treinarmos o comando Drop.

Comando DROPPrimeiro preciso saber qual o nome da constrant, para isso verificamos a estrutura da Tbvendas:SP_HELP TBvendas;

Sintaxe DROP constraintALTER TABLE DROP CONSTRAINT < nome_constraint>[;]

Aps retirada da restrio o comando TRUNCATE pode ser executado:

Agora podemos inserir os dados novamente.

Analisando a TBclientesObserve o campo Codigocli. Agora os cdigos 1 at 10 existem, portanto podemos retornar ao passo quatro do exerccio 14? No, antes devemos adicionar a restrio que foi excluda.

ADD Constraint

Sinntaxe:

ALTER TABLE ADD CONSTRAINT FOREIGN KEY REFERENCES (