Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
* * BANCO DE DADOS UTILIZANDO SQL SERVER * * SQL * * SQL É uma linguagem estruturada padrão para acessar dados num sistema gerenciador de banco de dados relacional. Foi criada nos inicio dos anos 80 pela IBM. Primeira versão padrão lançada pela ANSI/ISO foi em 1986 e 1987. Em 1992 foi lançado o SQL/92, com uma padronização mais completa. Outras versões foram lançadas para aperfeiçoar a linguagem e também incorporar a orientação a objetos. ANSI – Instituto Americano de Padrões ISO – Organização Internacional de Padrões * * SQL Esta dividido em: DDL - Data Definition Language DML - Data Manipulation Language DDL: criação e modificação da estrutura das tabelas. Create Table; Alter Table; Drop Table; .... DML: recuperam,modificam e excluem dados da tabela. Select, Insert, Delete, Update... * * COMANDOS DDL * * CREATE TABLE CREATE TABLE [nome da tabela] (campo1 tipo (tamanho) NOT NULL [PRIMARY KEY , campo2 tipo (tamanho) [NOT NULL],[...]) .... [PRIMARY KEY (primária1[, primária2 [, ...]]) | UNIQUE (exclusiva1[, exclusiva2 [, ...]]) | FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES tabela externa [(campoexterno1 [, campoexterno2 [, ...]])]) * * SINTAXE DO CREATE TABLE Nome da tabela: tabela a ser criada; Campo: atributo/campo/variável a ser criado; tipo: int,varchar, char, datetime( 1753...9999), smalldatetime( 1900...2079), numeric(n,p), tinyint(-255 a 255), text, image.......; Not Null: previne a entrada de nulos; Primary Key: declaração da chave primaria da tabela; Unique: o conteúdo é único dentro da tabela. Chaves primarias não precisam da declaração Unique. Em uma mesma tabela, pode-se ter uma chave primaria, e mais alguns atributos declarados como Unique. Sempre que essa clausula for usada é necessário usar Not Null. Foreign Key: chave estrangeira vinda de outra tabela. References (nome da tabela): tabela de onde a chave estrangeira é a chave primaria. * * EXEMPLO Create table Cidade ( Cod_cidade int not null, Nome_cidade varchar(50) not null, constraint pkcidade primary key(cod_cidade)) Create table Cinema ( Cod_cinema integer not null , Nome_cinema varchar(50) not null, Cod_cidade integer not null, constraint pkcinema primary key(Cod_Cinema), constraint fkcinemacidade foreign key (cod_cidade) references cidade(Cod_Cidade)) * * EXEMPLO Create table Cinema ( Cod_cinema integer not null, Nome_cinema varchar(50) not null, Capacidade integer, Cod_cidade integer not null, Constraint pkcinema primary key (cod_cinema), Constraint fkcinemacidade foreign key (cod_cidade) references cidade(cod_cidade)); * * ALTER TABLE Inserir/Eliminar atributos em uma tabela. Sintaxe: Alter table <nome da tabela> ADD/DROP (nome_atributo1 <tipo> [not null], nome_atributoN <tipo> [not null]); * * EXEMPLO: Alter table Cidade ADD estado varchar(2) not null; Alter table Cinema DROP column capacidade; Alter table Cinema ALTER COLUMN nome_cinema varchar(30); Alter table Cinema drop constraint fkcinemacidade Alter table Cinema add constraint fkcinemacidade foreign key (Cod_Cidade) references Cidade(Cod_Cidade) * * DROP Elimina a definição da tabela, seus dados e referencias. Sintaxe: Drop Table <nome_tabela>; Ex.: DROP Table Cidade; * * COMANDOS DML * * CLAUSULA INSERT. Inserir dados em uma tabela. Sintaxe: Insert into <nome da tabela> (atributo1,atributo2,...,atributoN) values (<valor1>, <valor2>,..., <valorN>) * * EXEMPLO: insert into cidade (cod_cidade, nome_cidade, estado) values (1, ‘Umuarama', 'PR'); insert into cidade (cod_cidade, nome_cidade, estado) values (2, 'Cianorte', 'PR'); insert into cidade (cod_cidade, nome_cidade, estado) values (3, 'Maringá', 'PR') insert into cidade values (5,'Sao Paulo','SP') * * EXEMPLO: insert into Cinema (cod_cinema, nome_cinema, cod_cidade, capacidade) values (010,'Cinema de Umuarama',1,200); insert into Cinema (cod_cinema, nome_cinema, cod_cidade, capacidade) values (020,'Cine Vip',2,400); insert into Cinema (cod_cinema, nome_cinema, cod_cidade, capacidade) values (030,'Cine Maringá',3,300) * * UPDATE É usado para alterar valores já armazenados em tabelas. Sintaxe: Update <nome_tabela> Set <nome_atributo> = <novo_valor> Where <condição>; * * EXEMPLO Update Cidade Set nome_cidade = “Cruzeiro do Oeste” Where cod_cidade = 1; Update Cinema Set nome_cinema = 'Cine do Oeste', capacidade =500 Where cod_cidade = 1 * * DELETE É usado para remover linhas de uma tabela. Usar sempre chave primaria na condição. Sintaxe: Delete from <nome_tabela> Where <condição>; * * EXEMPLO Delete from Cinema Where cod_cinema = 100; Delete from Cinema * * NOVAS CONDIÇÕES: Criação de tabelas com restrições para o usuário: create table Cliente ( cod_cliente integer not null primary key, nome_cliente varchar(40) not null, sexo_cliente char(1), constraint regra_sexo check (sexo_cliente in ('F','f','M','m'))) insert into Cliente values (1,’Izabel',‘F'); insert into Cliente values (2,'Pedro','M'); insert into Cliente values (3,'Maria','F') insert into Cliente values (4,'Joao','M') * * NOVAS CONDIÇÕES: Criação de tabelas com campos únicos: create table Cliente ( cod_cliente integer not null primary key, nome_cliente varchar(40) not null, cpf_cliente varchar(15 not null, constraint cpf_unico unique ( cpf_cliente)) insert into Cliente values (5,’Felipe', ‘xxx.xxx.xxx-xx’); insert into Cliente values (6,‘Joao', ‘yyy.yyy.yyyy-yy’); insert into Cliente values (7,‘Tiago', ‘zzz.zzz.zzz-zz’) insert into Cliente values (8,‘Mateus', ‘xxx.xxx.xxx-xx’) (O banco mandaria uma mensagem de violação de chave única!) * * Alter table Cliente add sexo_cliente char(1) Constraint regra_sexo check (sexo_cliente in (‘F’, ‘M’,’f’,’m’)) ALTER TABLE CIDADE ADD ESTADO VARCHAR(2) CONSTRAINT NOVA_RESTRICAO CHECK (ESTADO In ('SP‘, ‘RS’, ‘PR’)); ALTER TABLE CIDADE ADD constraint regra_estado check (ESTADO In ('SP', 'RS', 'PR')) ....SALARIO DECIMAL(9,2) CONSTRAINT VERIF_SAL CHECK (SALARIO >= 1000) .... alter table cliente drop constraint regra_sexo * * ALTER TABLE cliente ADD CONSTRAINT nome_unico UNIQUE (NOME_cliente); insert into cliente values (1,'Izabel','F') insert into cliente values (2,‘izabel','F') Solução: insert into cliente values (3, upper('izabel'),'F') ou insert into cliente values (2, lower('izabel'),'F') ou insert into cliente values (2, upper('maria'), upper ('f')) * * SELECT Recupera as informações armazenadas no banco de dados através de consultas. Uma instrução SELECT não muda os dados no banco de dados. Para executar uma operação SELECT o programa principal do BD procura a tabela ou as tabelas especificadas, extrai as colunas escolhidas, seleciona as linhas que satisfazem o critério e classifica ou agrupa as linhas resultantes na ordem especificada. * * SEQÜÊNCIA NO COMANDO SELECT Select coluna(s) From tabela(s) Where condição(ões) da(s) tupla(s) Group By condição(ões) do(s) grupo(s) de tupla(s) Having condição(ões) do(s) grupo(s) de tupla(s) Order By coluna(s); O SQL fará a seguinte avaliação: a) Where – para estabelecer tuplas individuais, não pode conter funções de grupo. b) Group By – para fixar grupos. c) Having – para selecionar grupos para exibição. * * SINTAXE MÍNIMA DO SELECT SELECT <campo1,campo2,...,campoN> FROM <nome_tabela>; SELECT * FROM <nome_tabela>; * * EXEMPLO SELECT * FROM Cidade SELECT Cod_Cidade, Nome_Cidade FROM Cidade; SELECT Cod_cinema, Nome_cinema FROM cinema; * * SELECT – CLAUSULA WHERE Contém a condição que as tuplas devem obedecer afim de serem listadas; Ela é utilizada para comparar valores em colunas, literais, expressões aritméticas ou funções; Operadores de comparação: = igual a > maior que >= maior que ou igual a < menor que <= menor que ou igual a !=, ^= e <> diferença * * EXEMPLO SELECT cod_cidade, nome_cidade FROM Cidade WHERE cod_cidade = 10; SELECT cod_cidade, nome_cidade FROM Cidade WHERE cod_ Cidade >= 10; SELECT cod_cidade, nome_cidade FROM Cidade WHERE cod_ Cidade <> 10; * * SELECT – CLAUSULA WHERE Operadores Lógicos: AND - a linha precisa satisfazer as duas condições comparadas. OR – a linha precisa satisfazer uma das condições comparadas. * * EXEMPLO select * from cinema where cod_cinema >= 10 and cod_cidade = 1 select * from cinema where cod_cinema >= 10 or cod_cidade = 1 select * from cinema where cine_fundacao>= ‘1980-01-01’ and cine_fundacao<= ‘2000-01-01’ * * CLAUSULA ORDER BY Exibe os dados na ordem classificada, ascendente ou descendente; Normalmente é o último item em uma instrução SQL. Sintaxe: Select <nome_atributo> From <nome_tabela> Order By <nome_atributo> ASC/DESC; * * CLAUSULA ORDER BY select * from cidade order by nome_cidade select * from cidade order by nome_cidade desc * * WHERE – OUTROS OPERADORES Between ... and ... – entre dois valores (inclusive) In (....) – lista de valores Like – com um padrão de caracteres Not Between ... and .... – não entre dois valores Not In (....) – não existe na lista de valores Not Like – diferente do padrão apresentado. * * EXEMPLO select * from cidade where cod_cidade between 1 and 3 select * from cinema where cine_fundacao between '1980-01-01' and cine_fundacao<= '2000-01-01' select * from cidade where cod_cidade in (1,4,5) * * EXEMPLO select * from cliente where nome_Cliente like 'I%‘ select * from cliente where nome_Cliente like '%I%‘ select * from cliente where nome_Cliente like '%A‘ select * from cliente where nome_Cliente like ‘__[RST]%A‘ * * FUNÇÕES DE MANIPULAÇÃO DE DADOS NUMÉRICOS ABS(NUM): valor absoluto. Ex.: SELECT ABS(30-70); =40 CEILING(e.n): arredonda um número para cima, para um inteiro mais próximo ou para um múltiplo mais próximo de significancia. Ex.: SELECT CEILING(-7.1) =-7 SELECT CEILING(7.1) = 8 FLOOR(e.n): arredonda um número para baixo, em direção a zero até a um múltiplo mais próximo de significancia. Ex.: SELECT FLOOR(-7.1) =-8 SELECT FLOOR(7.1) = 7 * * FUNÇÕES DE MANIPULAÇÃO DE DADOS NUMÉRICOS ROUND(E.N, TAMANHO): Arredonda um número para um número especificado de dígitos. Ex.: SELECT ROUND(75.5392,2); =75.54 SELECT ROUND(Pi(),2); = 3.14 SIGN(NUM): Determina o sinal de um número. Retorna 1 se o número for positivo, 0 se o número for zero e -1 se for negativo Ex.: SELECT SIGN(50) = 1 SELECT SIGN(0) = 0 SELECT SIGN(50-75) = -1 * * FUNÇÕES DE MANIPULAÇÃO DE DADOS LITERAIS LOWER(TEXTO): Retorna uma expressão de caracteres depois de converter para minúsculas os dados de caracteres. Ex.: SELECT LOWER(‘MAMAE’); = mamae SELECT LOWER(NOME) FROM EMPREGADO = retorna os nomes na tabela empregado em letras minúsculas. UPPER(TEXTO): Retorna uma expressão de caracteres depois de converter para maiúsculas os dados de caracteres. Ex.: SELECT UPPER(‘mamae’); = MAMAE SELECT UPPER(NOME) FROM EMPREGADO = retorna os nomes na tabela empregado em letras maiúsculas. * * FUNÇÕES DE MANIPULAÇÃO DE DADOS LITERAIS PATINDEX(‘%STRING%’,’TEXTO’): Retorna uma posição de um caractere dentro de uma determinada sequência. Ex.: SELECT PATINDEX(‘a%’,’abc’); = 1 SELECT PATINDEX(‘%a’,’cba’); = 3. SELECT PATINDEX('%b%','aeibc') = 4 REPLICATE(TEXTO,n): Repete um valor da cadeia de caracteres um número especificado de vezes. Ex.: SELECT REPLICATE(‘ABC’,3); = ABCABCABC REVERSE(TEXTO): Retorna a ordem inversa de um valor da cadeia de caracteres. Ex.: SELECT REVERSE(‘ABCD’); =DCBA * * FUNÇÕES DE MANIPULAÇÃO DE DADOS LITERAIS LEFT(exp,tamanho): Retorna a parte da esquerda de uma cadeia de caracteres, com um número de caracteres especificado. Ex.: SELECT LEFT(‘ABCDE’,3); = ABC RIGHT(exp,tamanho): Retorna a parte da esquerda de uma cadeia de caracteres, com um número de caracteres especificado. Ex.: SELECT RIGHT(‘ABCDE’,3); = CDE SUBSTRING(exp, inicio, tamanho): Retorna parte de uma cadeia de caracteres, conforme números especificados. Ex.: SELECT SUBSTRING(‘ABCDEFG’,3,2); =CD SELECT SUBSTRING(‘ABCDEFG’,2,3); =BCD * * FUNÇÕES DE MANIPULAÇÃO DE DATAS GETDATE(): Retorna a data e a hora atual. Ex.: SELECT GETDATE(); = ‘2013-09-02 15:18:36.160’ DATEADD(datepart,nro,data): Retorna uma data especificada com o intervalo nro adicionado a datepart especificada dessa data. Ex.: SELECT DATEADD(dd,3,getdate()); =Acrescenta 3 dias a data atual; SELECT DATEADD(dd,-3,getdate()); =Tira 3 dias a data atual; DATEDIFF(datepart, data1, data2): Retorna a diferença entre duas datas, conforme a datepart especificada. Ex.: SELECT DATEDIFF(dd,’2013-08-30’,getdate()); = Retorna a diferença entre a data atual e a data de 30/08/2013. * * FUNÇÕES DE MANIPULAÇÃO DE DATAS Datepart especificadas. DATENAME(datepart, data): Retorna uma sequência que representa o datepart da data especificada.. Ex.: select DATENAME (mm,getdate()). = Setembro select DATENAME (dw,getdate()). = Quarta-Feira * * OUTROS OPERADORES/FUNÇÕES Count(): retorna o numero de linhas. Ex.: SELECT Count(nome_cinema) From Cinema; SELECT Count(nome_cidade) From Cidade; Distinct: elimina duplicidades, isto é, somente relações distintas serão apresentadas como resultado de uma pesquisa. Ex.: SELECT DISTINCT cod_cidade FROM cinema; * * OUTROS OPERADORES/FUNÇÕES IS NULL - testar uma condição se ela é nula update cinema set endereco = ‘umuarama’ where endereco is null Select * from livro where l_editora is null Select * from contas_a_receber where cr_datapamento is null and cr_vencimento <= getdate() IS NOT NULL - testar uma condição se ela não é nula update cinema set endereco = ‘umuarama’ where endereco is not null Select * from livro where l_editora is not null Select * from contas_a_receber where cr_datapamento is not null * * OUTROS OPERADORES/FUNÇÕES AVG() – média do valor n, ignorando os nulos. Ex.: SELECT AVG(capacidade) From cinema; MIN() – menor valor da expressão. Ex.: SELECT MIN(capacidade) From Cinema; MAX() – maior valor da expressão. Ex.: SELECT MAX(capacidade) From Cinema; SUM() – soma dos valores de n, ignorando os nulos. Ex.: SELECT SUM(capacidade) From Cinema; * * GROUP BY As operações de grupo operam sobre grupos de linhas (tuplas), retornando os resultados baseado no grupo criado. É usada para dividir tuplas em grupos menores. Omitir a clausula Group By significa agrupar toda a tabela. Sintaxe: SELECT <coluna_agrupamento>,<função> FROM <nome_tabela> GROUP BY <coluna_agrupamento>; * * EXEMPLO: SELECT cod_produto, SUM(qtde_vendida) FROM itens_venda GROUP BY cod_produto SELECT cod_produto, AVG(qtde_vendida) FROM itens_venda GROUP BY cod_produto Select cod_cidade, count(cod_cidade) From cliente Group by cod_cidade * * EXEMPLO: SELECT cod_editora, nome_editora, SUM(PRECO_LIVRO) FROM LIVRO GROUP BY cod_editora, nome_editora OBSERVAÇÃO: AO USAR UMA FUNÇÃO DE AGREGAÇÃO ALIADA AO GROUP BY SEMPRE COLOCAR AGRUPANDO PELOS CAMPOS MENCIONADOS APÓS O SELECT! * * CLÁUSULA HAVING É usado quando se deseja especificar qual o grupo será exibido. A cláusula Group By deve vir antes da cláusula Having. Exemplo: SELECT cod_produto, count(qtde_vendida) FROM itens_venda GROUP BY cod_produto Having count(qtde_vendida) > 1 SELECT cod_produto, SUM(qtde_vendida) FROM itens_venda GROUP BY cod_produto having sum(qtde_vendida) > 2 * * EXEMPLO: SELECT cod_produto, preco, SUM(qtde_vendida) FROM itens_venda GROUP BY cod_produto,preco having sum(qtde_vendida) > 2 Select cod_cidade, count(cod_cidade) From cliente Group by cod_cidade having count(cod_cidade) > 10 * * CLÁUSULAS COMPUTE E COMPUTE BY Para uso com funções de agregação. O COMPUTE gera um total geral, enquanto que o COMPUTE BY gera totais dos grupos. Este resultado gerado produz uma nova linha no resultado da query. É possível obter Totais por grupo e o Total Geral, numa mesma query. Exemplos: SELECT cod_depto, nome, salario FROM empregado WHERE salario > 500 COMPUTE SUM(salario) SELECT cod_depto, nome, salario FROM empregado WHERE salario > 500 ORDER BY cod_depto COMPUTE SUM(salario) by cod_depto COMPUTE SUM(salario) * * CLÁUSULAS COMPUTE E COMPUTE BY RESTRIÇÕES Não é permitido utilizar distinct com agrupamentos. Deve se utilizar ORDER BY se o COMPUTE BY for utilizado. As colunas listadas no COMPUTE BY devem ser idênticos ou um sub-conjunto das colunas listadas no ORDER BY . SELECT * FROM livro order by l_editora COMPUTE SUM (l_preco) by l_editora * * SELECT COM MAIS DE UMA TABELA Exemplo : Selecionar da tabela Cliente: o código, o nome do cliente. Deverá ser mostrado o nome da cidade que o cliente pertence, sendo que na tabela Cliente só está gravado o código cidade que o cliente pertence. Logo, o nome da cidade será buscado na tabela cidade. Resolução 1: select a.cli_cod, a.cli_nome, b.cid_nome from cliente A, Cidade B where a.cid_cod=b.cid_cod * * SELECT COM MAIS DE UMA TABELA Resolução 2: select cliente.cli_cod, cliente.cli_nome, cidade.cid_nome from cliente , Cidade where cliente.cid_cod=cidade.cid_cod * * EQUI-JUNÇÃO (JUNÇÃO POR IGUALDADE) É definido como sendo o relacionamento existente entre duas ou mais tabelas, pois os valores de colunas das tabelas são iguais; A Equi-junção é possível apenas quando for definido de forma adequada a chave estrangeira de uma tabela e sua referencia a chave primaria da tabela precedente; * * EQUI-JUNÇÃO (JUNÇÃO POR IGUALDADE) Ex.: listar o nome do cinema e a sua respectiva cidade: Select cinema.nome_cinema, cidade.nome_cidade from cinema, cidade where cinema.cod_cidade = cidade.cod_cidade * * As junções SQL são utilizadas quando precisamos selecionar dados de duas ou mais tabelas. Exemplo: Selecionar o nome do vendedor que atendeu um cliente: Select Vendedor.nome AS "Nome do Vendedor", Cliente.nome_cliente AS "Nome do Cliente" from vendedor, cliente, venda where venda.cod_vendedor = Vendedor.cod_vendedor and venda.cod_cliente = cliente.cod_cliente * * USANDO JOIN As junções com JOIN podem ser de dois tipos: as INNER JOINS e as OUTER JOINS (LEFT JOIN, RIGHT JOIN ou FULL JOIN) INNER JOINS: seleciona apenas os registros em comum entre AS DUAS TABELAS. Select Vendedor.vend_nome AS 'Nome do Vendedor', cliente.cli_nome AS 'Nome do Cliente', venda.ven_cod, venda.ven_data From Vendedor INNER JOIN venda ON vendedor.vend_codigo = Venda.vend_codigo INNER JOIN cliente ON Venda.cli_cod = cliente.cli_cod * * USANDO INNER JOIN Exemplo: select Vendedor.vend_nome AS 'Nome do Vendedor', cli.cli_nome AS "Nome do Cliente" from venda INNER JOIN vendedor ON vendedor.vend_codigo = Venda.vend_codigo INNER JOIN cliente AS CLI ON Venda.cli_cod = cli.cid_cod ORDER BY cli.cli_nome Select cli.cli_nome as 'Nome do Cliente', pro.pro_nome as 'Nome do Produto' from cliente as cli inner join venda as ven on ven.cli_cod = cli.cid_cod inner join itensvenda as iv on iv.ven_cod = ven.ven_cod inner join produto as pro on iv.pro_cod = pro.pro_cod order by cli.cli_nome * * USANDO INNER JOIN EXEMPLO: Select cliente.cli_nome as 'Nome do Cliente', produto.pro_nome as 'Nome do Produto' from cliente inner join venda on venda.cli_cod = cliente.cid_cod inner join itensvenda on itensvenda.ven_cod = venda.ven_cod inner join produto on itensvenda.pro_cod = produto.pro_cod order by cli_nome Exemplo: select cliente.cli_nome as 'Nome do Cliente', produto.pro_nome as 'Nome do Produto', vendedor.vend_nome as "Nome do Vendedor",venda.ven_cod,venda.ven_data from cliente inner join venda on venda.cli_cod = cliente.cli_cod inner join vendedor on vendedor.vend_codigo = venda.ven_cod inner join itensvenda on itensvenda.ven_cod = venda.ven_cod inner join produto on itensvenda.pro_cod = produto.pro_cod * * USANDO OUTER JOINS LEFT JOIN Somente os registros da tabela da esquerda (left) serão retornados, tendo ou não registros relacionados na tabela da direita; A tabela à esquerda do operador de junção exibirá cada um dos seus registros, enquanto que a da direita exibirá somente seus registros que tenham correspondentes aos da tabela da esquerda. Para os registros da direita que não tenham correspondentes na esquerda serão colocados valores NULL. * * EXEMPLO: LEFT JOIN Vendedor e Venda: select vendedor.vend_nome AS 'Nome do Vendedor', venda.vend_codigo as 'Codigo da venda', venda.ven_data,venda.vend_codigo from vendedor left JOIN venda ON vendedor.vend_codigo = Venda.vend_codigo Vendedor e Cliente: select vendedor.vend_nome AS 'Nome do Vendedor', cliente.cli_nome as 'Nome do Cliente', venda.ven_cod as 'Codigo da venda', venda.ven_data as 'Data da Venda',venda.vend_codigo as 'Código do Vendedor' from vendedor left JOIN venda ON vendedor.vend_codigo = Venda.vend_codigo left join cliente ON venda.cli_cod= Cliente.cli_cod * * EXEMPLO: LEFT JOIN select a.vend_nome AS 'Nome do Vendedor', b.cli_nome as 'Nome do Cliente', c.ven_cod as 'Codigo da venda', c.ven_data as 'Data da Venda',c.vend_codigo as 'Código do Vendedor' from vendedor as a left JOIN venda as c ON a.vend_codigo = c.vend_codigo left join cliente as b ON c.cli_cod= b.cli_cod * * USANDO OUTER JOINS RIGHT JOIN Inverso do LEFT, este retorna todos os registros somente da tabela da direita (Right). Exemplo: Cliente e Vendedor select vd.vend_nome AS 'Nome do Vendedor', cliente.cli_nome as 'Nome do Cliente', venda.ven_cod as 'Codigo da venda', venda.ven_data as 'Data da Venda', venda.vend_codigo as 'Código do Vendedor' from vendedor as vd right JOIN venda ON vd.vend_codigo = Venda.vend_codigo right join cliente ON venda.cli_cod= Cliente.cli_cod * * USANDO OUTER JOINS FULL JOIN Seleciona todos os registros de todas as tabelas. Exemplo: Cliente e Vendedor select vd.vend_nome AS 'Nome do Vendedor', cliente.cli_nome as 'Nome do Cliente', venda.ven_cod as 'Codigo da venda', venda.ven_data as 'Data da Venda', venda.vend_codigo as 'Código do Vendedor' from vendedor as vd full JOIN venda ON vd.vend_codigo = Venda.vend_codigo full join cliente ON venda.cli_cod= Cliente.cli_cod * * EXERCÍCIOS Usar o exemplo feito na ultima aula e aplicar os conceitos vistos nas listas de Exercícios solicitadas pelo professor. * * AUTO JOIN( SELF JOIN) Realiza o join das linhas de uma tabela com outras linhas da mesma tabela. Por tratar-se da mesma tabela, deve-se definir aliases distintos. select a.l_titulo,b.l_titulo,a.l_preco from livro a, livro b where a.l_cod < b.l_cod and a.l_preco=b.l_preco * * São sentenças Select aninhadas. Muitas sentenças SQL incluem subqueries que podem ser formuladas de modo alternativo através de Joins. Outras são resolvidas somente com subquerys. A sentença Select de uma subquery é sempre colocada dentro do parenteses. Uma subquery pode estar inserida dentro de uma lista de Select, numa clausula WHERE ou HAVING numa query externa. Em aninhamentos normais de subqueries, as mais internas são executadas primeiro. SUBQUERYS * * SELECT cli_cod, cli_nome FROM cliente WHERE cli_cod IN ( SELECT DISTINCT cli_cod FROM venda) OU SELECT distinct a.cli_cod,a.cli_nome FROM cliente as a INNER JOIN venda as b on b.cli_cod=a.cli_cod SUBQUERYS-EXEMPLOS * * SELECT l_titulo,l_preco, l_preco/(select SUM(L_preco) from livro)*100 as 'Perc %', (select SUM(l_preco) from livro) as 'SOMATORIA' from livro SUBQUERYS-EXEMPLOS * * São aquelas subqueries que referenciam na cláusula WHERE, uma tabela da cláusula FROM da query externa. Neste caso, a subquery é executada para cada linha da tabela referenciada na query externa. SUBQUERIES CORRELATAS * * SELECT cidade.Nome_cidade,cinema.Nome_cinema,cinema.CINE_FUNDACAO FROM Cidade INNER JOIN Cinema ON cinema.Cod_cidade=cidade.Cod_cidade and cinema.CINE_FUNDACAO=(SELECT MIN(cine_fundacao) from Cinema where cinema.Cod_cidade= cidade.Cod_cidade) SUBQUERIES CORRELATAS * * Permite unir os resultados de duas ou mais queries em um único resultado. O UNION resulta num incremento de linhas. ( Um JOIN resulta num incremento de colunas). Na união, a estrutura das colunas tem quer ser as mesmas e os tipos de dados compatíveis. Por default, as linhas duplicadas são removidas do resultado final. Se desejar que elas permaneçam utilizar a opção ALL. As listas de colunas dos Selects devem ter o mesmo número de colunas, tipos de dados similares e aparecerem na mesma ordem. Os nomes das colunas são obtidos da primeira Sentença SELECT. OPERADOR UNION * * Exemplo: select c.cid_cod as 'codigo', c.cid_nome as 'nome' from cidade c union all select b.cli_cod as 'codigo', b.cli_nome as 'nome' from cliente b OPERADOR UNION * * SELECT INTO Cria uma nova tabela baseada no resultado de um select. Se a nova tabela for uma tabela temporária, o nome desta deve estar precedido de um # ou ##. As colunas nas listas de select devem ter apelidos (alias). Denota-se a nova tabela como sendo temporária, colocando um sinal de escopo (#) como o primeiro caracter do nome da tabela. * * SELECT INTO Exemplo: select c.cid_nome as 'Cidade',COUNT(d.cli_cod) as 'Quantidade' into #cidade_qtde from cidade c, cliente d where d.cid_cod = c.cid_cod group by c.cid_nome Selecionando os dados da tabela temporária #cidade_qtde: Select * from #cidade_qtde * * INSERINDO LINHAS COM O SELECT Insere os dados de uma query em uma tabela que já existe. Exemplo: insert into cidade_qtde select c.cid_nome,COUNT(d.cli_cod) from cidade c, cliente d where d.cid_cod = c.cid_cod group by c.cid_nome * * VISÕES É como uma janela quer permite visualizar ou modificar seletivamente informações armazenadas em tabelas. É utilizada para: Poder restringir o acesso a informações contidas em tabelas; Permitir que os usuários efetuem consultas simples sobre consultas complicadas; Consultar dados de diversas tabelas. Uma visão é considerada uma tabela virtual, isto é, funciona como uma tabela mas não contém dados. Os dados de uma visão são calculados a partir de outras tabelas. * * EXEMPLO: Exemplo 1 Create View v_cliente1 As Select cli_nome From cliente Where cli_cod is not null Ou Exemplo 2 Create View v_cliente2 As Select cli_nome From cliente Where cli_cod > 200 * * VISÕES Para visualizar as informações da View: select * from v_cliente2; Uma visão (View) é armazenada como sendo uma sentença Select. Para remover uma visão: Drop View (nome_da_visão); Drop view v_cliente1 CREATE VIEW comedias AS SELECT * FROM filmes WHERE tipo = 'Comédia'; * * EXEMPLO: Select cliente.cli_nome as 'Nome do Cliente', produto.pro_nome as 'Nome do Produto' from cliente inner join venda on venda.cli_cod = cliente.cid_cod inner join itensvenda on itensvenda.ven_cod = venda.ven_cod inner join produto on itensvenda.pro_cod = produto.pro_cod CREATE VIEW V_produtos AS Select cliente.cli_nome as 'Nome do Cliente', produto.pro_nome as 'Nome do Produto' from cliente inner join venda on venda.cli_cod = cliente.cid_cod inner join itensvenda on itensvenda.ven_cod = venda.ven_cod inner join produto on itensvenda.pro_cod = produto.pro_cod * * EXERCÍCIOS Usar o exemplo feito na ultima aula e aplicar os conceitos vistos. * * PROPRIEDADE IDENTITY Cria um auto-incremento de número inteiros e seqüenciais. É utilizado em conjunto com Create table e ou Alter table. Sintaxe: IDENTITY(VALOR INICIAL, INCREMENTO) Exemplo: CREATE TABLE TESTE( test_cod int identity(100,2), test_nome varchar(30), constraint pkteste primary key(test_cod)) ; Valor Inicial: O valor usado na primeira linha carregado na tabela. No exemplo, esse valor é 100. Incremento: O valor incremental adicionado ao valor da identidade da linha anterior que foi carregada. * * EXEMPLO: Criação: CREATE TABLE TESTE( test_cod int identity(100,2), test_nome varchar(30), constraint pkteste primary key(test_cod)) ; Inicialização : Registro com valor inicial de 100. Incremento de 2. Utilização: insert into teste values ('Teste 1') insert into teste values ('Teste 2') * * TRIGGERS Um Trigger, ou um gatilho, é um bloco de comandos Transact-SQL que é automaticamente executado quando um comando INSERT , DELETE ou UPDATE for executado em uma tabela do banco de dados. As Triggers são usadas para realizar tarefas relacionadas com validações, restrições de acesso, rotinas de segurança e consistência de dados ; desta forma estes controles deixam de ser executados pela aplicação e passam a ser executados pelos Triggers em determinadas situações. * * TRIGGERS A ativação de uma trigger está associada à ocorrência de um evento: INSERT UPDATE DELETE * * TRIGGERS Sintaxe: CREATE TRIGGER name ON {table view} FOR {DELETE INSERT UPDATE} AS <variable_declaration_list> =DECLARE VARIABLE variable datatype; [DECLARE VARIABLE variable datatype;...] <trigger comandos_body> Explicação: table view : é o nome tabela ou view que estará associada à trigger. delete insert update : indica em qual evento a trigger será executada. trigger body : é o corpo da trigger, o código ou comandos dela. * * EXEMPLO: create trigger tgi_baixaestoque on itensvenda for insert as Declare @qtde_vendida int; Declare @cod_produto int; begin select @qtde_vendida = qtde_vendida, @cod_produto = pro_cod from inserted update produto set pro_estoque = (pro_estoque - @qtde_vendida) where pro_cod = @cod_produto end * * EXEMPLO: insert into itensvenda values(6,1,2,1) insert into itensvenda values(6,2,1,1) insert into itensvenda values(6,3,30,1) insert into itensvenda values(6,4,30,1) * * EXEMPLO: Criar a tabela UF: Create table UF ( Cod_Estado integer not null primary key, Nome_Estado varchar(40) not null, Sigla char(02) not null); Criar uma trigger com uma regra de exceção. Ao se tentar excluir um registro da tabela estado, o banco mandará uma mensagem de que 'Não é possível excluir um estado'; * * EXEMPLO: CREATE TRIGGER T_UF ON UF FOR DELETE AS BEGIN PRINT('NÃO É POSSÍVEL EXCLUIR UM ESTADO!') ROLLBACK END Delete From UF; * * EXEMPLO: create trigger tgi_baixaestoquecomexcessao on itensvenda for insert as Declare @qtde_vendida int; Declare @cod_produto int; Declare @qtde_estoque int; begin select @qtde_vendida = qtde_vendida, @cod_produto = pro_cod from inserted select @qtde_estoque = pro_estoque from produto where pro_cod = @cod_produto if @qtde_vendida>=@qtde_estoque begin print('Acabou o estoque!') Rollback end else begin update produto set pro_estoque = (pro_estoque - @qtde_vendida) where pro_cod = @cod_produto end end * * EXEMPLO: Valor menor insert into itens_venda values (503,457,546,20) Valor maior insert into itens_venda values (504,457,546,200). Neste caso o banco manda a mensagem “acabou o estoque” e finaliza a transação. * * EXEMPLO: Create Table Pessoa ( Cod_Pessoa Integer Not Null primary key, Pessoa_Idade Int Not Null, Pessoa_Tipo Char(1) ); * * EXEMPLO: * * EXEMPLO: Insert: insert into pessoa (cod_pessoa,pessoa_idade) values (1,10) insert into pessoa (cod_pessoa,pessoa_idade) values (2,20) Update: Update Pessoa Set Pessoa_Idade = 5; * * TRIGGERS Excluindo uma trigger: DROP TRIGGER nome_trigger; * * STORED PROCEDURES Stored Procedure é um conjunto de comandos, ao qual é atribuído um nome. Este conjunto fica armazenado no Banco de Dados e pode ser chamado a qualquer momento tanto pelo SGBD (sistema Gerenciador de Banco de Dados) quanto por um sistema que faz interface com o mesmo. Stored Procedures são pre-compiladas. Portanto, não é necessário mandar o comando pela rede para ser validado e executado. Elas são simplesmente executadas. Procedures podem possuir parâmetros e, assim como o SELECT, retornar dados de uma tabela. * * STORED PROCEDURES Stored Procedure aceita todas as sentenças de manipulação de Dados e algumas extensões avançadas como : IF..THEN..ELSE, WHILE...DO, FOR SELECT..DO, EXCEPTIONS ... Existem dois tipos de SP : Sem parâmetros: A stored procedure retorna informação sem a utilização de parâmetros. Com parâmetros: Isso significa que a stored procedure utiliza os valores atribuídos aos parâmetros para alterar o seu comportamento em tempo de execução. * * STORED PROCEDURES Sintaxe: CREATE PROCEDURE @param1, @param2, AS <declaração de variáveis> declare <@nome da variável 1> tipo, <@nome da variável 2> tipo, BEGIN <corpo> END; * * EXEMPLO: create procedure Cliente_Count as declare @ClienteCount int begin select @ClienteCount=COUNT(cliente.cli_cod) from cliente print('A quantidade de clientes atual é '+convert(varchar(5),@ClienteCount)) end * * EXEMPLO: Executando a procedure criada anteriormente. exec Cliente_Count Apagando a procedure criada anteriormente. drop procedure Cliente_Count * * EXEMPLO: CREATE PROCEDURE Resumo_Vendas @vcodvendedor as int as declare @Soma float, @media float, @maximo float, @minimo float; begin select @Soma=SUM(ven_total),@media=AVG(ven_total), @maximo=MAX(ven_total),@minimo=Min(ven_total) from venda where vend_codigo=@vcodvendedor print('RESUMO SITUACAO VENDAS PARA O VENDEDOR DE CODIGO '+convert(varchar(3),@vcodvendedor)) print('SOMA das vendas '+convert(varchar(10),@Soma)) print('MÉDIA das vendas '+convert(varchar(10),@media)) print('MAIOR valor de venda alcancado '+convert(varchar(10),@maximo)) print('MENOR valor de venda alcancado '+convert(varchar(10),@minimo)) end * * EXEMPLO DE EXECUÇÃO STORED PROCEDURE COM PARÂMETROS: exec Resumo_Vendas 2 * * EXERCÍCIOS Usar o exemplo feito na ultima aula e aplicar os conceitos vistos.
Compartilhar