INTRODUÇÃO A SQL

Prévia do material em texto

1 
Banco de Dados 
SubUnidade 3.2: 
Introdução à SQL 
 
 
 
Luciana Vieira e Roger da Silva 
Date, C. J. – Introdução a Sistemas de Banco de Dados. 
2 
INTRODUÇÃO 
 
 
“SQL é a linguagem padrão para se lidar 
com bancos de dados relacionais”. 
Date 
 
SQL (Structured Query Language) 
 
 
3 
INTRODUÇÃO 
A SQL possui operações de definição de dados 
e operações de manipulação de dados. 
 
 
SQL utiliza os termos tabela (table), linha 
(row), e coluna (column) para relação ou 
entidade, tupla e atributo, respectivamente. 
4 
TIPOS DE DADOS 
 
5 
CREATE TABLE 
CREATE TABLE [nome_banco.[dono_tabela]. ] nome_tabela 
( 
[nm_c prop_c[constraint[constraint[...constraint]]] 
| [ [ , ] constraint ]] 
[ [ , ] [próximo_nome_col|próxima_constraint] ... ] 
); 
 
Ex: 
CREATE TABLE empresa 
 ( cod_empresa int NOT NULL, 
 nome_empresa varchar(45) NOT NULL, 
 cpf char(14) NOT NULL, 
 dt_fundacao smalldatetime, 
 nro_funcionarios smallint ) 
 
6 
CREATE TABLE 
Nomes de colunas devem ser únicos dentro de 
uma mesma tabela; 
Toda coluna deve ter um tipo de dado (datatype) 
Pode-se definir até 2 bilhões de tabelas por banco 
de dados 
Pode-se definir até 250 colunas por tabela 
Um valor NULL indica a ausência de dados. NULL 
não é branco e nem zero. Por default, o SQL 
assume NOT NULL, para as colunas que não estão 
declaradas com NULL. Pode-se alterar esse 
default na configuração do SQL Server. 
 
7 
CREATE TABLE 
CREATE TABLE funcionario 
 ( cod_func int, 
 nome_func varchar(45), 
 endereco varchar(50) NULL, 
 cidade varchar(30) NULL ) 
 
CREATE TABLE cliente 
 ( cod_cliente int Primary Key, 
 nome_cliente varchar(30), 
 CPF varchar(14), 
 Endereco varchar(50)) 
8 
INTEGRIDADE REFERENCIAL 
CREATE TABLE cliente 
 ( cod_cliente int Primary Key, 
 nome_cliente varchar(30), 
 CPF varchar(14), 
 Endereco varchar(50)) 
 
CREATE TABLE pedido 
 ( cod_cliente int, 
 nro_pedido int, 
 data_pedido smalldatetime, 
CONSTRAINT pk_pedido PRIMARY KEY (cod_cliente, nro_pedido), 
CONSTRAINT fk_cliente_pedido FOREIGN KEY (cod_cliente) 
REFERENCES cliente (cod_cliente)) 
 
9 
ALTER TABLE 
ALTER TABLE nm_tab 
 {{CHECK|NOCHECK } CONSTRAINT {nm_constraint |ALL } 
 | 
 [ ADD 
 {nm_col prop_col [constraints_col ] 
 | [[, ] constraint_tabela ]} 
 [, {proxima_col | proxima_constraint_tabela }] ... 
 ] 
 | 
 [DROP CONSTRAINT] 
 nome_constraint [, nome_constraint2 ] ... ] 
10 
ALTER TABLE - EXEMPLO 
ALTER TABLE depto 
 ADD nome_chefia varchar(20) NULL 
 
ALTER TABLE empregado 
 ADD CONSTRAINT fk_depto_emp FOREIGN KEY 
(cod_depto) 
 REFERENCES depto (cod_depto) 
 
ALTER TABLE empregado 
 DROP CONSTRAINT fk_depto_emp 
11 
DROP TABLE 
DROP TABLE nome_tabela 
 
EX: DROP TABLE empregado 
 
Não se pode excluir uma tabela se esta tiver 
alguma outra tabela “filha” ligada à ela. 
Quando se elimina uma tabela, todos os 
registros contidos nela são apagados e a 
tabela em si com toda sua estrutura é 
eliminada da Base de Dados. 
12 
13 
14 
SELECT 
SELECT [ALL | DISTINCT ] select_list 
 FROM nm_tab [, nm_tab2] [..., nm_tab16] 
 [WHERE cláusula ] 
 [GROUP BY cláusula ] 
 [HAVING cláusula ] 
 [ORDER BY cláusula ] 
 [COMPUTE cláusula ] 
SELECT 
SELECT - especifica as colunas 
FROM - especifica as tabelas 
WHERE - especifica as linhas 
Quando se utiliza * na lista de select, 
seleciona-se todas as colunas da tabela. 
Não se utilizando a cláusula WHERE, todas 
as linhas serão selecionadas. 
SELECT - EXEMPLO 
SELECT * FROM empregado 
 
SELECT nome, idade, salario FROM empregado 
 
SELECT cod_depto, cod_empregado, salario 
 FROM empregado 
 WHERE salario > 1200 
18 
SELECT 
Por default, o cabeçalho mostrado no 
resultado de uma query é o nome da coluna 
definido na criação da tabela. Porém, é 
possível mudar este cabeçalho, inserindo-o 
na lista do Select. 
 
SELECT * FROM empregado 
 
SELECT nome AS NOME_EMPREGADO, 
 dt_admissao AS DATA_DE_ADMISSAO 
 FROM empregado 
 
19 
SELECT E OPERADORES 
ARITMÉTICOS 
Os operadores aritméticos podem ser 
utilizados em qualquer coluna numérica 
(numeric, decimal, int, smallint, tinynt, 
float, real, money ou smallmoney). 
 
 
 
 
O operador de módulo (%) não pode ser 
executado nos tipos de dados money, 
smallmoney, float ou real. 
 
 
 
20 
EXEMPLO 
 
 
 
SELECT nome, salario, (salario * 1.15) 
 FROM empregado 
 
 
 
21 
FUNÇÕES - NÚMEROS 
22 
FUNÇÕES - EXPRESSÃO 
23 
FUNÇÕES - EXPRESSÕES 
24 
FUNÇÕES - TEMPO 
25 
FUNÇÕES - TEMPO 
SELECT DATE_ADD(CURDATE(), INTERVAL 120 DAY); 
SELECT NOW(),CURDATE(),CURTIME() 
SELECT DATEDIFF('2010-01-05','2009-01-05'); 
SELECT DATE_FORMAT(Curdate(), '%d %m %Y'); 
SELECT nome, dt_admissao, DATEDIFF (Curdate(), 
dt_admissao) FROM empregado 
SELECT nome, dt_admissao FROM empregado 
 WHERE DAYNAME(dt_admissao) = 
 "MONDAY” 
26 
FUNÇÃO 
Obs: Evitar a utilização de condições de 
Negação, tal como NOT IN e NOT BETWEEN, 
pois essas condições não são reconhecidas 
pelo otimizador de query. 
 
27 
FUNÇÃO - EXEMPLO 
 
SELECT nome, codigo FROM empregado 
 WHERE codigodepto IN (1,3,4) 
 
SELECT * FROM empregado 
 WHERE nome LIKE '%Jose%„ 
 
SELECT * FROM empregado 
 WHERE (nome LIKE 'S%' OR codigodepto = 1) 
 AND (salario > 500.00) 
28 
ORDER BY 
Utilizado para organizar de A-Z ou de Z-A. 
Pode-se definir na cláusula ORDER BY o número 
relativo da coluna ao invés do nome da coluna. 
Por default, a forma ascendente (ASC) é assumida. 
Pode-se classificar por até 16 colunas. 
 
EX: 
SELECT nome, codigodepto, salario FROM empregado 
 ORDER BY codigodepto, salario DESC 
 
29 
RESUMINDO DADOS 
Retorna valores resumidos 
O resultado aparece como novas colunas 
Utilizado na Lista de Select ou na Cláusula HAVING 
 
 
 
 
EX: 
SELECT COUNT(*) FROM empregado 
SELECT SUM (salario) FROM empregado 
SELECT AVG (salario) FROM empregado 
SELECT MAX(salario) FROM empregado 
 
 
30 
INSERT 
O comando INSERT é utilizado para 
adicionar linhas a uma tabela 
Utilize uma sentença INSERT para cada linha 
a ser adicionada 
A ordem e o tipo de dado dos item a serem 
inseridos devem corresponder a ordem e tipo 
de dado das colunas da tabela 
Os itens de dados na cláusula VALUES deve 
corresponder a Lista dos nomes de coluna 
 
31 
INSERT 
INSERT INTO depto VALUES (1, 'CONTABILIDADE') 
 
 
INSERT INTO empregado (nro_empregado, nome, 
cod_depto) VALUES (7000, 'Jose Luiz de 
Oliveira', 1) 
 
INSERT INTO empreg_salarioanual 
 SELECT nome_empreg , (salario * 12 ) 
 FROM empregado 
 WHERE idade > 21 
 
 
 
32 
UPDATE 
O comando UPDATE altera os dados de linhas 
de uma tabela 
A cláusula SET especifica a coluna e o valor a 
ser alterado 
A cláusula WHERE identifica exatamente as 
linhas a serem alteradas 
Se o UPDATE viola alguma constraint de 
integridade, a alteração não será executada 
 
33 
UPDATE 
UPDATE empregado 
 SET salario = salario * 1.05 
 
 
UPDATE empregado 
 SET codigodepto = 5 
 WHERE codigodepto = 2 
34 
DELETE 
O comando DELETE remove uma ou mais 
linhas de uma tabela 
A cláusula WHEREespecifia os critérios pelos 
quais as linhas serão removidas 
Se A cláusula WHERE não é especificada, 
todas as linhas da tabela serão removidas. 
 
DELETE from empregado 
WHERE DATEDIFF (YEAR, dt_admissao, 
GETDATE()) >= 5 
 
35 
DELETE 
O comando DELETE remove uma ou mais 
linhas de uma tabela 
A cláusula WHERE especifica os critérios 
pelos quais as linhas serão removidas 
Se A cláusula WHERE não é especificada, 
todas as linhas da tabela serão removidas. 
 
DELETE from empregado 
WHERE Salario is null 
 
36 
GROUP BY 
Organiza os dados em grupos 
Geralmente utilizada com funções de 
agregação 
Resulta um único valor para cada grupo 
Cada coluna na lista de Select deve estar na 
cláusula GROUP BY 
 
 
 
 
 
37 
GROUP BY - EXEMPLO 
SELECT cod_depto, AVG (salario) AS MediaSalario 
 FROM empregado 
 GROUP BY cod_depto 
 
 
 
 
 
38 
HAVING 
Cláusula de restrição de grupo 
Permite funções de agregação 
Similar a cláusula WHERE 
As colunas na cláusula HAVING devem ter 
somente um único valor 
Uma query com HAVING deve ter um GROUP 
BY 
 
 
 
 
 
39 
HAVING - EXEMPLO 
 
 
SELECT cod_depto, SUM( salario ) AS SalarioDepto 
 FROM empregado 
 GROUP BY cod_depto 
 HAVING SUM ( salario ) > 5000.00 
 
 
40 
JOIN 
Operação que permite o tratamento de duas ou 
mais tabelas 
Implementado pela utilização da sentença 
SELECT 
Na lista de SELECT colocamos as colunas de 
uma ou mais tabelas que desejamos listar 
Na cláusula FROM definimos todas as tabelas 
envolvidas no Join, 
Na cláusula WHERE especificamos quais linha 
serão incluídas no resultado. Na cláusula 
WHERE, um operador de join é utilizado entre 
os componentes que estão fazendo a junção. 
41 
JOIN 
Pode-se definir até 16 tabelas na cláusula 
FROM. 
Normalmente, uma chave primária é comparada 
a uma chave estrangeira em qualquer join 
O operador utilizado para o join é o sinal de 
igual (=), que resulta somente nas linhas onde o 
valor da coluna de uma tabela é igual ao valor 
da coluna de outra tabela. 
Caso não se defina nenhuma condição de Join 
(com a cláusula WHERE), é gerado um produto 
Cartesiano entre as tabelas. (Join Irrestrito) 
42 
JOIN 
Exemplo de Join Irrestrito (Produto Cartesiano 
entre as Tabelas): 
 
SELECT nome_empreg, nome_depend 
 FROM empregado, dependente 
 
 
No exemplo acima, supondo que a Tabela 
Empregado tenha 10 linhas, e a Tabela 
Dependente 20 linhas, então o resultado do 
Join Irrestrito será 10 * 20 = 200 linhas. 
 
43 
EQUI JOIN 
Especifica uma condição de igualdade entre 
as tabelas para satisfazer o Join. 
 
 
SELECT d.nome, e.nome FROM depto d, empregado e 
 WHERE d.cod_depto = e.cod_depto 
 
SELECT d.nome, e.nome, dp.nome, dp.idade 
 FROM depto d, empregado e, dependente dp 
 WHERE d.cod_depto = e.cod_depto 
 AND e.cod_empreg = dp.cod_empreg 
44 
OUTER JOIN 
Mostra todas as linhas do join, inclusive 
aquelas que não encontram linhas 
correspondentes na tabela relacionada. 
Só se pode fazer um outer join entre duas 
tabelas. 
 
Operadores do Outer Join: 
Left join -> Inclui todas as linhas da primeira 
tabela especificada nas restrições da sentença 
Right joint  Inclui todas as linhas da Segunda 
tabela especificada nas restrições da sentença 
45 
OUTER JOIN 
 
SELECT titles.title_id, title, qty 
 FROM titles LEFT JOIN sales 
 ON titles.title_id = sales.title_id 
 
SELECT titles.title_id, title, qty 
 FROM titles RIGHTJOIN sales 
 ON titles.title_id = sales.title_id 
 
46 
SUBQUERIES 
São sentenças SELECT aninhadas 
A sentença SELECT de uma subquery é sempre 
colocado dentro de parênteses. 
Uma subquery pode estar inserida na lista de 
SELECT, numa cláusula WHERE ou HAVING da 
query externa. 
Em aninhamentos normais de subqueries, as mais 
internas são executadas primeiro. 
47 
SUBQUERIES 
SELECT cod_depto, nome FROM depto 
 WHERE cod_depto IN ( SELECT DISTINCT 
 cod_depto FROM empregado) 
 
 
SELECT NomeEstado FROM aula.Estado 
 WHERE UF in (select UF FROM aula.cliente 
where salario is not null) 
48 
SUBQUERIES CORRELATAS 
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. 
 
SELECT title_id, au_id, royaltyper FROM titleauthor ta 
 WHERE royaltyper = (SELECT MAX(royaltyper) FROM 
 titleauthor tb WHERE tb.title_id = ta.title_id) 
 
49 
EXERCÍCIO 
Para os exercícios a seguir considere os 
seguintes esquemas: 
 
Atleta= (cod_atleta, nome, sexo, cidade, data_nasc) 
Equipe = (cod_equipe, nome) 
Equipe_Atleta = (cod_equipe, cod_atleta) 
Competicao = ( cod_competicao, data, cidade, pais) 
Participacao = (cod_atleta, cod_equipe, 
cod_competicao, qualificacao) 
Penalidades = (cod_atleta, cod_penalidade, data, 
pontos, valor) 
 
50 
EXERCÍCIO 
 
51 
EXERCÍCIO 
1. Mostre a relação de todos os atletas que 
sofreram penalidades; 
2. Mostre apenas a relação dos códigos de 
todos os atletas que sofreram penalidades; 
3. Mostre o número do atletas que receberam 
mais de duas penalidade. 
5. Mostre a relação das cidades de onde 
provêm os atletas. 
6. Mostre quantos atletas moram em BELÉM 
52 
EXERCÍCIO 
1. Mostre a relação de todos os atletas que 
sofreram penalidades; 
2. Mostre apenas a relação dos códigos de 
todos os atletas que sofreram penalidades; 
3. Mostre o número do atletas que receberam 
mais de duas penalidade. 
5. Mostre a relação das cidades de onde 
provêm os atletas. 
6. Mostre quantos atletas moram em BELÉM 
53 
EXERCÍCIO 
7. Mostre o número total de penalidades aplicadas 
8.Mostre a penalidade de maior valor 
9.Mostre quantas outras penalidades tem valor 
igual a penalidade de menor valor. 
10.Mostre os nomes distintos das cidades de onde 
provêm os atletas. ( mostrar apenas uma vez o 
nome de cada cidade) 
54 
EXERCÍCIO 
11.Mostre o código dos atletas cuja penalidade 
mais recente recebida foi em 2009. 
SELECT atleta_codatleta, max(data) FROM competicao.penalidade p 
 group by atleta_codatleta 
 having max(data) like '%2009%' 
 
12.Mostre o código dos atletas cujo valor total das 
penalidades aplicadas á ele ultrapassam R$ 
1000,00 
SELECT atleta_codatleta, sum(valor) FROM competicao.penalidade p 
 group by atleta_codatleta 
 having sum(valor) > 1000 
55 
EXERCÍCIO 
13. Mostre todas as cidades que tem pelo 
menos 4 atletas provenientes 
14. Mostre todas os atletas que participaram 
de pelo menos 2 competições. 
15. Mostre a relação de competições e a 
qualificação dos seus participantes 
organizado por competição e por atletas. 
16. Mostre o nome de cada atleta que começa 
coma letra M. 
17. Mostre o nome de cada atleta que não 
começa coma letra M.