Aula 08 - Sublinguagem de Banco de dados-DML Select (1)

Prévia do material em texto

Banco de dados
Aula 08: Linguagem de Consulta de dados- DQL
Introdução à Programação
AULA 01: Introdução à Lógica de Programação
Agenda
Banco de dados
• Objetivos
• Resumo sobre modelagem
• introdução à linguagem SQL e suas divisões sintáticas 
- DDL
- DML
- DCL
- DQL
- Entre outras
• Sentenças para criação, alteração e exclusão de tabelas.
• Select
• Relacionamento com duas tabelas
• Relacionamento com três tabelas
• Função de agregação (count, sum, max, min, avg) 
com Group by
• Função between
• Função in
• Função exists
• Problemas
3
Agenda
• Linguagem SQL I
• A instrução SELECT é a mais importante no SQL, pois, é
a partir dela, que selecionamos quais dados desejamos
recuperar.
– Dados são retornados em formato de tabelas.
• SINTAXE:
– SELECT campo1, campo2, ..., campon FROM tabela
4Para selecionar todos os campos de uma tabela utiliza-se o * (asterisco).
Instrução SELECT
5
SELECT *
FROM tb_pacientes;
--------+---------+-------------+-----------+-------------+-----------------
----
paccod | pacnome | pacdatanasc | paccidade | paccpf | pacdoenca
--------+---------+-------------+-----------+-------------+-----------------
----
1 | Paula | 1984-05-12 | Teresina | 61000111000 | Gripe
2 | João | 1977-09-30 | Teresina | 12345678910 | Fratura
3 | Lucia | 1987-02-02 | Bom Jesus | 22000200000 | Tendinite
4 | Carlos | 1987-06-20 | Barras | 11000110000 | Sarampo
5 | Tamires | 1995-12-25 | Teresina | 46081633454 | Febre
6 | Thiago | 1986-11-10 | Teresina | 55789638899 | Renite alérgica
7 | Maria | 1975-08-06 | Picos | 98745612345 | Infecção 
Intestinal
(7 ROWS)
Selecionar os dados de todos os pacientes.
PROBLEMA I
6
Apresentar quantos grupos de 5 leitos podem ser
formados em cada ambulatório.
SELECT ambnum, (ambcapacidade / 5)
FROM tb_ambulatorios;
--------+-----------
ambnum | ?column?
--------+-----------
1 | 6
2 | 10
3 | 8
4 | 5
5 | 11
(5 ROWS)
PROBLEMA II
• Linguagem SQL I
• Alias (AS)
• Com Alias podemos dar um nome diferente (amigável) a
uma coluna ou tabela ao retornar o resultado de uma
consulta.
– Para isso, usamos a clausula AS.
• A definição dos ALIASES é feita na cláusula SELECT e
FROM.
• SINTAXE:
– SELECT campo AS novo_nome FROM tabela AS
novo_nome
7
O uso do Alias (AS)
8
Apresentar o nome e o salário líquido dos
funcionários após um desconto de 10%.
SELECT funcnome, funcsalario - (funcsalario * 0.1) AS Salario_Liquido
FROM tb_funcionarios;
-----------+-----------------
funcnome | salario_liquido
-----------+-----------------
Rita | 1080.000
Maria | 2898.000
Caio | 990.000
Carlos | 1980.000
Paula | 2250.000
Taiane | 1800.000
Tamires | 1620.000
Valdirene | 2250.000
Marlon | 4050.000
Thiago | 3780.000
Mario | 3915.000
(11 ROWS)
PROBLEMA III
• Linguagem SQL I
• O parâmetro WHERE é um argumento opcional da
instrução SELECT e de outra instruções.
• Seu objetivo é filtrar o conjunto de resultados
retornados na consulta.
• SINTAXE:
• SELECT campo FROM tabela WHERE condições
9
Parâmetro WHERE
• Linguagem SQL I
• Operadores relacionais e lógicos podem ser
utilizados na cláusula WHERE para
respectivamente compararem valores ou
expressões.
10
Operador Relacionais
= Igual
<> ou != Diferente
< Menor que
> Maior que
<= Menor ou igual a
>= Maior ou igual a
Quando a coluna é do tipo caractere, o <valor> deve estar entre aspas simples.
Operador Lógicos
AND Conjunção
OR Disjunção
NOT Negação
Parâmetro WHERE
11
SELECT pacnome, paccpf, (CURRENT_DATE - pacdatanasc)/365 As IDADE
FROM tb_pacientes
WHERE (CURRENT_DATE - pacdatanasc)/365 > 18;
---------+-------------
pacnome | paccpf
---------+-------------
Paula | 61000111000
João | 12345678910
Lucia | 22000200000
Carlos | 11000110000
Tamires | 46081633454
Thiago | 55789638899
Maria | 98745612345
(7 ROWS)
Selecionar nome e cpf dos pacientes cujas idades
sejam maiores do que 18 anos.
Retorna a data atual.
PROBLEMA IV
12
Selecionar nome e cpf dos pacientes, e a data das
consultas que estão marcadas a partir das 12h.
SELECT pacnome, paccpf, consdata
FROM tb_pacientes, tb_consultas
WHERE conshora > '12:00'
AND tb_pacientes.paccod = tb_consultas.paccod;
---------+-------------+------------
pacnome | paccpf | consdata
---------+-------------+------------
Paula | 61000111000 | 2015-12-06
Lucia | 22000200000 | 2015-12-14
Carlos | 11000110000 | 2015-12-14
Paula | 61000111000 | 2015-12-19
Carlos | 11000110000 | 2015-12-19
Carlos | 11000110000 | 2015-12-20
Carlos | 11000110000 | 2015-12-20
(7 ROWS)
PROBLEMA V
13
Selecionar o cpf, nome e data de nascimento de todos
os pacientes que possuem consultas marcadas.
SELECT pacnome, paccpf, pacdatanasc
FROM tb_pacientes AS p, tb_consultas AS c
WHERE p.paccod = c.paccod;
---------+-------------+-------------
pacnome | paccpf | pacdatanasc
---------+-------------+-------------
Paula | 61000111000 | 1984-05-12
Carlos | 11000110000 | 1987-06-20
Paula | 61000111000 | 1984-05-12
João | 12345678910 | 1977-09-30
Lucia | 22000200000 | 1987-02-02
Carlos | 11000110000 | 1987-06-20
Paula | 61000111000 | 1984-05-12
Lucia | 22000200000 | 1987-02-02
Carlos | 11000110000 | 1987-06-20
(11 ROWS)
Valores repetidos
PROBLEMA VI
Linguagem SQL I
14
• Funções SQL 
• O comando DISTINCT pode ser utilizado para
evitar que valores repetidos sejam visualizados em
uma consulta.
• Para eliminar a duplicidade, podemos inserir a
palavra-chave DISTINCT após a palavra-chave
SELECT.
• SINTAXE:
– SELECT DISTINCT campo FROM tabela
[WHERE condições]
15
Parâmetro DISTINCT
16
Selecionar as especialidades dos médicos.
SELECT DISTINCT medespecialidade
FROM tb_medicos;
------------------
medespecialidade
------------------
Ortopedia
Traumatologia
Pediatria
Neurologia
(4 ROWS)
PROBLEMA VII
17
Selecionar o cpf, nome e data de nascimento de todos
os pacientes que possuem consultas marcadas.
SELECT DISTINCT paccpf, pacnome, pacdatanasc
FROM tb_pacientes, tb_consultas
WHERE tb_pacientes.paccod = tb_consultas.paccod;
-------------+---------+-------------
paccpf | pacnome | pacdatanasc
-------------+---------+-------------
61000111000 | Paula | 1984-05-12
22000200000 | Lucia | 1987-02-02
11000110000 | Carlos | 1987-06-20
12345678910 | João | 1977-09-30
(4 ROWS)
PROBLEMA VIII
• Funções SQL 
• A partir de uma consulta, é possível visualizar os
primeiros “n” registros retornados, assim como os
últimos “n” registros.
• Este comando tem várias utilidades, tais como:
– Listar os cinco livros mais vendidos;
– Listar os três piores tempo de uma corrida;
• SINTAXE:
– SELECT campo FROM tabela [WHERE condições]
LIMIT x OFFSET y
18
Parâmetro LIMIT
19
Apresentar os três primeiros pacientes.
SELECT *
FROM tb_pacientes
LIMIT 3;
--------+---------+-------------+-----------+-------------+-----------
paccod | pacnome | pacdatanasc | paccidade | paccpf | pacdoenca
--------+---------+-------------+-----------+-------------+-----------
1 | Paula | 1984-05-12 | Teresina | 61000111000 | Gripe
2 | João | 1977-09-30 | Teresina | 12345678910 | Fratura
3 | Lucia | 1987-02-02 | Bom Jesus | 22000200000 | Tendinite
(3 ROWS)
PROBLEMA IX
20
Selecionar o nome e salario dos funcionario a partir
do 3º registro até o 7º.
SELECT funcnome, funcsalario
FROM tb_funcionarios
LIMIT 7 OFFSET 2;
-----------+-------------
funcnome | funcsalario
-----------+-------------
Caio | 1100.00
Carlos | 2200.00
Paula | 2500.00
Taiane | 2000.00
Tamires | 1800.00
Valdirene | 2500.00
Marlon | 4500.00
(7 ROWS)
PROBLEMA X
• Funções SQL 
• O parâmetro COUNT executa a contagem de registros de uma tabela.
• O parâmetro SUM realiza a soma de valores de campos específicos.
• O parâmetro AVG calcula a média de valores em uma consulta SQL.
• Os parâmetros MAX/MIN são utilizados para obtenção do maior
(MAX) e menor (MIN) valor de uma dada coluna.
21
Parâmetro COUNT | SUM | AVG | MAX | MIN
• Funções SQL 
• SINTAXE:
– SELECT PARÂMETRO(campo) FROM tabela [WHERE
condições]22
COUNT SUM AVG MAX MIN
Parâmetro COUNT | SUM | AVG | MAX | MIN
23
Apresentar o total de médicos ortopedistas.
SELECT COUNT(*) AS TotalOrtopedista
FROM tb_medicos
WHERE medespecialidade = 'Ortopedia';
------------------
totalortopedista
------------------
1
(1 ROW)
PROBLEMA XI
24
Apresentar a capacidade total dos ambulatórios do
primeiro andar.
SELECT SUM(ambcapacidade) AS TotalAndar1
FROM tb_ambulatorios
WHERE amdandar = 1;
------------------
totaloandar1
------------------
80
(1 ROW)
PROBLEMA XII
25
Apresentar a média de salário dos funcionários da
cidade de Teresina.
SELECT AVG(funcsalario) AS MediaFuncThe
FROM tb_funcionarios
WHERE funccidade = 'Teresina';
------------------
mediafuncthe
------------------
2893.75
(1 ROW)
PROBLEMA XIII
26
Apresentar o menor e o maior salário pago aos
Funcionários cuja idade seja maior a 40 anos.
SELECT MIN(funcsalario) AS Min_Salario, MAX(funcsalario) AS Max_Salario
FROM tb_funcionarios
WHERE (CURRENT_DATE - funcdatanasc)/365 > 40;
-----------+------------
min_salario | max_salario
-------------+---------------
1100.00 | 4500.00
(1 ROW)
PROBLEMA XIV
• Funções SQL 
• A cláusula GROUP BY tem funcionalidades de realizar
operações baseadas em agrupamentos de valores de campos
específicos.
• É aplicada ao se utilizar uma função de agregação cuja
intenção seja organizar o resultado em determinadas
categorias.
• SINTAXE:
– SELECT operação FROM tabela GROUP BY campo27
Parâmetro GROUP BY
28
Selecionar a especialidade e quantidade de médicos
de cada especialidade.
SELECT medespecialidade, COUNT(medespecialidade)
FROM tb_medicos;
ERRO: erro de sintaxe em ou próximo a "SELECT medespecialidade"
LINE 1: SELECT medespecialidade, COUNT(medespecialidade)
^
********** Error **********
ERRO: erro de sintaxe em ou próximo a "SELECT medespecialidade"
SQL state: 42601
Character: 1
PROBLEMA XV
29
Selecionar a especialidade e quantidade de médicos
de cada especialidade.
SELECT medespecialidade, COUNT(medespecialidade)
FROM tb_medicos
GROUP BY medespecialidade;
-----------------+-------
medespecialidade | count
-----------------+-------
Ortopedia | 1
Traumatologia | 2
Pediatria | 2
Neurologia | 2
(4 ROWS)
PROBLEMA XVI
• Funções SQL 
• A ordem em que as linhas de uma consulta são retornadas não é
garantida, a menos que a cláusula ORDER BY seja especificada.
• A cláusula ORDER BY é usada para organizar os dados em ordem
ascendente (ASC) ou descendente (DESC).
– Por padrão os resultados são organizados de forma ASCENDENTE.
• SINTAXE:
– SELECT colunas FROM tabela [WHERE condições]
ORDER BY colunas [ASC | DESC]
30
Parâmetro ORDER BY
31
Selecionar todos os pacientes e ordena-los pelo nome.
SELECT *
FROM tb_pacientes
ORDER BY pacnome;
--------+---------+-------------+-----------+-------------+---------------------
paccod | pacnome | pacdatanasc | paccidade | paccpf | pacdoenca
--------+---------+-------------+-----------+-------------+---------------------
4 | Carlos | 1987-06-20 | Barras | 11000110000 | Sarampo
2 | João | 1977-09-30 | Teresina | 12345678910 | Fratura
3 | Lucia | 1987-02-02 | Bom Jesus | 22000200000 | Tendinite
7 | Maria | 1975-08-06 | Picos | 98745612345 | Infecção Intestinal
1 | Paula | 1984-05-12 | Teresina | 61000111000 | Gripe
5 | Tamires | 1995-12-25 | Teresina | 46081633454 | Febre
6 | Thiago | 1986-11-10 | Teresina | 55789638899 | Renite alérgica
(7 ROWS)
PROBLEMA XVII
32
Selecionar o nome e o salario dos pacientes e ordena-
los pelo salário (decrescente). Caso tenha funcionários
com salários iguais ordena-los pelo nome.
SELECT funcsalario, funcnome
FROM tb_funcionarios
ORDER BY funcsalario DESC, funcnome;
-------------+-----------
funcsalario | funcnome
-------------+-----------
4500.00 | Marlon
4350.00 | Mario
4200.00 | Thiago
3220.00 | Maria
2500.00 | Paula
2500.00 | Valdirene
2200.00 | Carlos
2000.00 | Taiane
1800.00 | Tamires
... | ...
(11 ROWS)
PROBLEMA XVIII
• Funções SQL 
• O parâmetro HAVING é muito similar ao parâmetro WHERE.
• A diferença é que a cláusula HAVING deve ser utilizada ao se usar o
argumento GROUP BY.
• É utilizada quando desejamos definir condições e aplicá-las aos grupos ao
invés de fazê-lo a cada linha separadamente.
– A condição da cláusula HAVING é aplicada após a formação dos
grupos.
33
Parâmetro HAVING
• Funções SQL 
• SINTAXE:
SELECT campo1, campo2, ..., campon
FROM tabela
[WHERE condições]
GROUP BY campo
HAVING condições
34
Parâmetro HAVING
35
Selecionar as especialidade que possuam mais de um
médico.
SELECT medespecialidade, COUNT(medespecialidade)
FROM tb_medicos
GROUP BY medespecialidade
HAVING COUNT(medespecialidade) > 1;
------------------+-------
medespecialidade | count
------------------+-------
Traumatologia | 2
Pediatria | 2
Neurologia | 2
(3 ROWS)
PROBLEMA XIX
• Funções SQL 
• Os parâmetros LIKE e NOT LIKE são usados na clausula
WHERE para pesquisar por um padrão específico em uma
coluna.
– São aplicáveis apenas a colunas dos tipos CHAR e
VARCHAR.
• Devem ser combinados com os símbolos “%” e “_”.
– “%” substitui uma palavra e “_” substitui um caractere
qualquer.
• SINTAXE:
– SELECT colunas FROM tabela WHERE campo LIKE
‘%palavra’
‘palavra%’
36
Retorna os resultados que TERMINEM com a palavra.
Retorna os resultados que INICIEM com a palavra.
Parâmetro LIKE | NOT LIKE
37
Selecionar nome e cpf dos médicos com inicial M.
SELECT mednome, medcpf
FROM tb_medicos
WHERE mednome LIKE 'M%';
---------+-------------
mednome | medcpf
---------+-------------
Maria | 98745612345
Marcia | 98798612345
(2 ROWS)
PROBLEMA XX
• Funções SQL 
• O operador BETWEEN é usado para selecionar valores dentro de
um intervalo.
• Isto é, tal operador possibilita a seleção de uma faixa de valores
sem a necessidade do uso dos operadores lógicos e relacionais.
• SINTAXE:
– SELECT colunas FROM tabela
WHERE campo BETWEEN valor1 AND valor2
38
Parâmetro BETWEEN
39
Selecionar os dados das consultas marcadas para o
período da tarde.
SELECT *
FROM tb_consultas
WHERE conshora BETWEEN '14:00' AND '18:00';
---------+------------+----------+--------+--------
conscod | consdata | conshora | medcod | paccod
---------+------------+----------+--------+--------
1 | 2015-12-06 | 14:00:00 | 1 | 1
5 | 2015-12-14 | 14:00:00 | 2 | 3
6 | 2015-12-14 | 17:00:00 | 2 | 4
7 | 2015-12-19 | 18:00:00 | 3 | 1
(4 ROWS)
PROBLEMA XXI
• Funções SQL 
• O operador UNION é usado para combinar os resultados de duas ou
mais querys (consultas).
• As colunas de cada consulta devem ter tipos de dados similar e
estarem na mesma ordem.
• SINTAXE:
– SELECT colunas FROM tabela1
UNION
SELECT colunas FROM tabela2 40
Parâmetro UNION
41
Selecionar o nome e o CPF dos médicos e pacientes.
SELECT mednome, medcpf
FROM tb_medicos
UNION
SELECT pacnome, paccpf
FROM tb_pacientes;
---------+-------------
mednome | medcpf
---------+-------------
Carlos | 11294586792
Maria | 98745612345
Ycaro | 48665671129
Eduardo | 13379438863
Tamires | 46081633454
Paula | 61000111000
João | 12345678910
Carlos | 11000110000
... | ...
(12 ROWS)
PROBLEMA XXII
Introdução à Programação
AULA 01: Introdução à Lógica de Programação
Bibliografia básica
FONSECA, Cleber Costa da. Implementação de banco de dados. 1. ed. Rio de 
Janeiro:
SESES, 2016.
PUGA, Sandra; FRANÇA, Edson; GOYA, Milton. Banco de Dados: implementação
em SQL, PL/SQL e Orable 11g (Biblioteca Virtual). 1. ed. São Paulo: Pearson, 2013.
SILBERSCHATZ, Abraham; KORTH, Henry F; SUDARSHAN, S. A. Sistema de
banco de dados. 6. ed. Rio de Janeiro: Elsevier, 2012.
Implementação de Banco de Dados
Introdução à Programação
AULA 01: Introdução à Lógica de Programação
Bibliografia complementar
Projeto e Otimização em D
BALIEIRO, Ricardo. Banco de dados. 1. ed. Rio de Janeiro: SESES, 2015.
ELMASRI, R.; NAVATHE, S. Sistemas de Banco de Dados (Biblioteca Virtual). 4.
ed. São Paulo: Pearson Education do Brasil,2005.
KLINE,Daniel; KLINE , Kevin. SQL - O Guia Essencial - Manual de Referencia
Profissional. 1. ed. Rio de Janeiro: Alta Books, 2010.
NETO, Geraldo H. MODELAGEM DE DADOS. 1. ed. Rio de Janeiro: SESES, 2015.
RAMALHO, Jose Antonio Alves. SQL a linguagem dos bancos de dados. São Paulo:
BERKELEY BRASIL, 1999.
Implementação de Banco de Dados
Introdução à Programação
AULA 01: Introdução à Lógica de Programação
Sugestões de Livros
Implementação de Banco de Dados
DOWNLOADS
Prof. Hilson Silva – Banco de Dados
45
• Oracle 11g
https://www.oracle.com/technetwork/database/enterprise-
edition/downloads/112010-win64soft-094461.html
• Downloads do PLSQL –Developer
https://www.allroundautomations.com/plsqldev.html?gclid=Cj0KC
Qjw3PLnBRCpARIsAKaUbguK1QvhcBEZCv26hA0G2lorZzKFTIVZxGz
zPKGZzEZ2aiWwWU8SSkcaAnHIEALw_wcB
• Power architect
http://www.bestofbi.com/page/architect_download_os
Implementação de Banco de Dados
https://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html
https://www.allroundautomations.com/plsqldev.html?gclid=Cj0KCQjw3PLnBRCpARIsAKaUbguK1QvhcBEZCv26hA0G2lorZzKFTIVZxGzzPKGZzEZ2aiWwWU8SSkcaAnHIEALw_wcB
http://www.bestofbi.com/page/architect_download_os
DOWNLOADS
Prof. Hilson Silva – Banco de Dados
46
• MySQL - http://www.mysql.com/
• Downloads do MySQL –
http://dev.mysql.com/downloads/
• MySQL Workbench OSS -
http://dev.mysql.com/downloads/workbench/
• SQL Manager 2010 Lite -
•http://sqlmanager.net/products/mysql/manager/download
• DB Tools Manager – Standard Edition -
http://www.dbtools.com.br/EN/downloads/
DOWNLOADS
http://www.mysql.com/
http://dev.mysql.com/downloads/
http://dev.mysql.com/downloads/workbench/
http://sqlmanager.net/products/mysql/manager/download
http://www.dbtools.com.br/EN/downloads/