11 - SQL

Prévia do material em texto

*
*
 Prof: Márcio Soussa Centro Universitário Jorge Amado
*
 
		Referências Bibliográficas
 Sistemas de Banco de Dados, Navathe
 Introdução a Sistemas de Bancos de Dados, C.J. Date, Ed. Campus.
 Sistema de Banco de Dados, A Silberschatz, H.F. Korth, Ed. Makron Books
 
*
SQL Introdução
 Motivação: sistemas de banco de dados comerciais precisam de uma linguagem de consulta mais fácil para o usuário. 
Não se trata apenas de uma linguagem de consulta (inclui recursos para definição da estrutura de dados, para modificação de dados no banco de dados e para a especificação de restrições de segurança) 
*
SQL Histórico
Versão original IBM, chamava-se Sequel
implementada em 1970
Evoluiu e passou a chamar-se SQL (Structured Query Language) 
Existem vários produtos no mercado que suportam SQL (Oracle, SQL Server, Sybase)
*
SQL Histórico
1986: American National Standards Institute (ANSI) e a International Standards Organization (ISO) publicaram padrões para SQL ( SQL-86)
1987: IBM publica seus padrões - Systems Application Architecture Database Interface (SAA-SQL)
versão em uso: padrão ANSI/ISO SQL (SQL-92)
*
SQL Características
DDL - Linguagem de definição de dados (Data-definition Language) 
Definição das estruturas e esquemas
Definição de visões dos usuários
Definição de restrições de integridade
Definição de segurança e autorizações
Controle de transações
DML - Linguagem de manipulação de dados (Data-manipulation Language) 
Consultas
Inserções, Atualizações e Deleções
Pode ser embutida em outras linguagens
*
SQL DDL (Data Definition Language)
Alguns domínios aceitos por SQL-92:
char (n)	varchar(n)
Int			smallint
Numeric	real, double precision
float(n)		date
time 
Domínios criados por usuário:
Create domain nome_pessoa char(20)
*
SQL DDL (Data Definition Language)
CREATE TABLE
Create table R (A1 D1, A2 D2,.....,An Dn)
			<regras de integridade1>
			<regras de integridade2>
			.....
			<regras de integridaden>
ALTER TABLE
Adicionar ou retirar atributos
Alterar definição de coluna
Adicionar ou retirar restrições de tabela
Alter table R Add/Drop A Tipo do atributo
		DROP TABLE
			Drop table R
*
SQL DDL (Data Definition Language)
create table Empregado
 (nome varchar(20) not null,
 rua varchar(30),
 cod_cidade int not null,
 primary key (nome),
	 foreign key (codigo) references cidade)
create table cidade
 (codigo int not null,
 nome varchar(15) not null,
 UF char(02),
	 primary key codigo),
 check (UF in (“BA”, “RJ”)))
*
SQL DDL (Data Definition Language)
create table Empregado
 (codigo int identity,
 nome varchar(45) not null,
	 sexo char(1) default ‘F’,
	 RG int unique not null,
	 CPF int unique not null,
	 datanasc smalldatetime not null,
	 codcargo int,
	 telefone varchar(10),
	 dataatualizacao smalldatetime default getdate(),
	 primary key (codigo),
	 foreign key (codcargo) references Cargo on delete 		set null on update cascade,
	 check (sexo in (‘F’, ‘M’)))		
*
*
SQL DDL (Data Definition Language)
DROP TABLE
Remove a tabela
	 Drop table Empregado
*
*
*
ALTER TABLE
 Adicionar coluna em uma tabela
	ALTER TABLE Cargo ADD sigla char(5)
 Alterar coluna em uma tabela
	ALTER TABLE Cargo Alter column sigla varchar(10) 	 		
 Excluir coluna em uma tabela
 	ALTER TABLE Cargo DROP column sigla
SQL DDL (Data Definition Language)
*
*
*
 Adicionar uma restrição de verificação (CHECK) a uma tabela
	ALTER TABLE Cargo ADD CONSTRAINT simbolo
	check (simb in (‘A’, ‘B’, ‘C’))
Remover uma restrição de verificação
	ALTER TABLE Cargo DROP CONSTRAINT 	simbolo
	
SQL DDL (Data Definition Language)
*
*
SQL DDL (Data Definition Language)
Adicionar uma chave primária 
	ALTER TABLE Funcao ADD CONSTRAINT pk_codigo 	PRIMARY KEY (codigo)
Adicionar uma chave estrangeira em uma tabela
	ALTER TABLE Empregado ADD CONSTRAINT 	fk_codfuncao FOREIGN KEY (codFuncao) 	REFERENCES Funcao (codigo)
	
*
*
Comandos de Manipulação de dados
INSERT ( Inserção de dados )
INSERT INTO CARGO (codigo, nome, simb) VALUES 
( 1, ‘PROGRAMADOR’, ’PRG’ )
	INSERT INTO empregadonovo (nome, sexo, rg, cpf, 	datanasc, codcargo, telefone)
	SELECT nome, sexo, rg, cpf, datanasc, codcargo, 	telefone FROM empregado WHERE codigo <= 5
*
*
Comandos de Manipulação de dados
DELETE (Deleção de dados)
DELETE FROM Cargo
WHERE CODIGO = 1
DELETE FROM EMPREGADONOVO
DELETE FROM EMPREGADO
WHERE CODIGO NOT IN
(SELECT COD_EMPREGADO FROM DEPENDENTE)
*
*
Comandos de Manipulação de dados
UPDATE (Atualização de dados)
UPDATE CARGO 
SET codigo = 20
WHERE codigo = 1
UPDATE EMPRESA SET PORTE = ‘M’
WHERE COD_EMPRESA IN (
SELECT COD_EMPRESA FROM FATURAMENTO
WHERE TOTAL_FATURADO_ANO < 150000)
*
*
Comandos de uma Consulta
SELECT <lista de atributos>
FROM <lista de tabelas>
[ WHERE <condição> ]
[ GROUP BY <atributos de agrupamento> ]
[ HAVING <condição de agrupamento> ]
[ ORDER BY <lista de atributos> ] 
*
SQL DML (Data Manipulation Language)
 A cláusula select: corresponde à operação de projeção da álgebra relacional por ser usada para relacionar os atributos desejados no resultado de uma consulta
A cláusula from: corresponde à operação de produto cartesiano da álgebra relacional, pois, associa as relações que serão pesquisadas durante a evolução de uma expressão
*
SQL DML (Data Manipulation Language)
A cláusula where: corresponde à seleção do predicado da álgebra relacional, pois, consiste em um predicado envolvendo atributos da relação que aparece na cláusula from
Consultas básicas
 SELECT * FROM EMPREGADO 
 SELECT nome, cpf FROM empregado WHERE 
	sexo = “F” AND codcargo = 1
 
*
*
Comando order by asc/desc : controla a ordem através da qual as tuplas de uma relação serão apresentadas
select * from empregado
	where empregado.cod_cidade = 7
	order by nome
select codcargo, nome FROM EMPREGADO 	ORDER BY codcargo, nome
select codcargo, nome FROM EMPREGADO 	ORDER BY codcargo desc, nome asc	
*
*
Comando Distinct 
	Retorna tuplas sem duplicatas
	select distinct codcargo from EMPREGADO
 	
	select distinct codcargo, sexo from EMPREGADO
Comando Rename
Renomeia atributos e tabelas
Exemplo: nome_antigo as nome_novo
select nome, salario AS salario_mensal, codcargo as cargo
 from EMPREGADO;
*
*
Comando NULL/NOT NULL
Verifica a existência ou não de valores
	 select * from empregado
		where telefone is null
 	 select * from empregado
		where telefone is not null
*
*
Comandos com EXPRESSÃO ARITMÉTICA
+,-,*,/
select nome, salario * 12 AS salario_anual, 			 	 from EMPREGADO
JOIN
Faz a junção entre tabelas
INNER JOIN
OUTER [LEFT ou RIGHT ou FULL] JOIN
	 select empregado.nome as nome, empregado.codcargo as cargo, 		 cargo.nome as nome_cargo 
 	 from empregado 
	 join cargo on empregado.codcargo = cargo.codigo
*
*
LEFT OUTER JOIN
	 select e.nome as nome, e.codcargo as cargo, c.nome as 		 nome_cargo 
 	 from empregado e 
	 left join cargo c on e.codcargo = c.codigo
RIGHT OUTER JOIN
	 select e.nome as nome, e.cargo as codigo, c.nome as nome_cargo 
 	 from empregado e 
	 right join cargo c on e.cargo = c.codigo
	 FULL OUTER JOIN
 	 select e.nome as nome, e.cargo as codigo, c.nome as nome_cargo 
 	 from empregado e 
	 full join cargo c on e.cargo = c.codigo
*
*
Funções de agregação e agrupamento
COUNT - Número de tuplas
SUM 	 - Soma de valores
MAX	 - Valor máximo
MIN	 - Valor mínimo
AVG	 - Média aritmética
GROUP BY –Agrupamento
HAVING – Condição do agrupamento
*
*
 COUNT
Select Count(*) from empregado
Select Count(distinct sexo) from empregado
 SUM
 Select Sum(Salario) from empregado
MAX
 Select MAX(Salario) from empregado
MIN
 Select MIN(Salario) from empregado
 AVG
Select Avg(Salario) from empregado
*
*
 Group BY / Having
	Select Avg(Salario) 
	 from empregado
	 group by sexo
	Select codcargo, sexo, Avg(SALARIO) 
	from empregado 
	group by codcargo,	sexo 
	having codcargo > 2
	
	
*
*
Operações com strings
LIKE - Permite a verificação de strings
	 Case Sensitive
Ex.: “Maria%” – Qualquer string que comece com Maria
Ex.: “%Maria” – Qualquer string que possua uma substring Maria
Select nome from empregado where nome like ‘Maria%’
Ex.: “_a%” – Qualquer string que possua o 2ª caracter a
Select nome from empregado where nome like ‘_a%’
*
*
Operações com strings
Concatenação de strings
select (logradouro || ', ' || complemento || ', ' || bairro) as endereço 
	from empregado
Operador de intervalo
 select nome, salario 
	from EMPREGADO
	where (salario between 3000 and 4000)	
*
*
Operadores de conjuntos
Correspondem às operações da álgebra relacional
União [all] (Union)
Exceção (Except ou Minus) 
Interseção (Intersect) 
As consultas envolvidas na consulta precisam ter o mesmo conjunto de atributos. 
*
*
UNION anexa o resultado da consulta2 ao resultado da consulta1 (embora não haja garantia que esta seja a ordem que as linhas realmente retornam). São eliminadas do resultado as linhas duplicadas, do mesmo modo que no DISTINCT, a não ser que seja utilizado UNION ALL.
SELECT codigo, nome, simb FROM cargo
	UNION 
	(SELECT codigo, nome, simb FROM cargo1) 
SELECT codigo, nome, simb FROM cargo
	UNION ALL
	(SELECT codigo, nome, simb FROM cargo1) 
*
*
INTERSECT retorna todas as linhas presentes tanto no resultado da consulta1 quanto no resultado da consulta2 .
	SELECT codigo, nome, simb FROM cargo
	INTERSECT
	(SELECT codigo, nome, simb FROM cargo1) 
EXCEPT ou MINUS retorna todas as linhas presentes no resultado da consulta1, mas que não estão presentes no resultado da consulta2 (às vezes isto é chamado de diferença entre duas consultas). 
	SELECT codigo, nome, simb FROM cargo
	EXCEPT ou MINUS
	(SELECT codigo, nome, simb FROM cargo1) 
*
*
Sub-consultas
IN / NOT IN – testa membros de um conjunto. Executa a sub-consulta 		 para depois a consulta principal.
SELECT Nome, salario
 FROM Empregado 
 WHERE Codigo IN
 (SELECT codigo_empregado
 FROM empregado_demitido)
 
SELECT Nome, salario
 FROM Empregado 
 WHERE Codigo NOT IN
 (SELECT codigo_empregado
 FROM empregado_demitido)
*
*
ALL – com operadores de comparação 
>ALL – maior de todos 
	SELECT codigo, nome, salario
 		FROM empregado
	WHERE salario >all 
	(select salario from empregado where cargo = 5)
Encontrar o nome dos empregados que possuem salário maior que a média dos empregados
SELECT Nome, salario
 FROM Empregado 
 WHERE Salario > ( SELECT AVG(Salario) FROM Empregado)
*
*
 EXISTS / NOT EXISTS – executa a consulta e depois a 	sub-consulta (em cada registro da consulta da 	principal)
 SELECT E.codigo, E.Nome, E.salario
 FROM Empregado E
 WHERE EXISTS 
 (SELECT * FROM Dependente D
 WHERE E.codigo = D.codigo_empregado)
SELECT E.codigo, E.Nome, E.salario
 FROM Empregado E
 WHERE NOT EXISTS 
 (SELECT * FROM Dependente D
 WHERE E.codigo = D.codigo_empregado)