Revisão de SQL

Revisao - SQL - Prof. George Hamilton

11Conceitos básicos em SQLConceitos básicos em SQL


Linguagem SQL

A linguagem SQL é própria para a realização de operações como recuperar dados (consulta), atualizar ou eliminar dados, alterar colunas de tabelas, criar e eliminar tabelas e qualquer outras modificações que se deseje fazer em um banco de dados relacional.


Comandos da SQL

Os comandos SQL podem ser divididos em quatro grupos:

1. Seleção/Consulta;

2. Definição de dados;

3. Manipulação de dados;

4. Controle de dados.


O Comando SELECT

Recupera informações do banco de dados;Utilizado para realizar consultas.


Operações usando o SELECT

SELEÇÃO PROJEÇÃO

a1

JUNÇÃO

a2

a3

b1

b1

b2

b1

b2

b3

c1

c2

c3

a1

a2

a3

b1

b1

b2

c1

c1

c2

UNIÃO INTERSEÇÃO DIFERENÇA


Sintaxe básica do SELECT

SELECT [DISTINCT] {*, coluna [apelido],…}

FROM tabela;

SELECT identifica quais colunas serão recuperadas

FROM identifica as tabelas das quais as colunas serão recuperadas


Selecionando todas as colunas

SQL> SELECT * FROM dept;

DEPTNO DNAME LOC--------- -------------- --------------- 10 ACCOUNTING NEW YORK 20 RESEARCHING DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON


Selecionando colunas específicas

SQL> SELECT deptno, dname FROM dept;

DEPTNO DNAME--------- -------------- 10 ACCOUNTING 20 RESEARCHING 30 SALES 40 OPERATIONS


Expressões Aritméticas

É possível criar expressões sobre os campos do tipo numérico e data utilizando os operadores aritméticos.

Operador

+

-

*

/

Descrição

Soma

Subtração

Multiplicação

Divisão


Usando Operadores Aritméticos

SQL> SELECT ename, sal, sal*12 FROM emp;

ENAME SAL SAL*12--------------- --------- ---------KING 5000 60000BLAKE 2850 34200CLARK 2450 29400ALLEN 1600 19200WARD 1250 15000…

14 rows selected.


Precedência dos Operadores

Multiplicação e divisão têm prioridade sobre soma e subtração;

Operadores com a mesma prioridade são avaliados da esquerda para a direita;

Utilizar parêntses para definir prioridades e tornar os comandos mais legíveis.

* / + -


Precedência dos Operadores

SQL> SELECT ename, sal, 12*sal+10 FROM emp;

ENAME SAL 12*SAL+10--------------- --------- ---------KING 5000 60010BLAKE 2850 34210CLARK 2450 29410ALLEN 1600 19210WARD 1250 15010…14 rows selected.


Utilizando Parênteses

SQL> SELECT ename, sal, 12* (sal+10) FROM emp;

ENAME SAL 12*(SAL+10)--------------- --------- -----------KING 5000 60120BLAKE 2850 34320CLARK 2450 29520ALLEN 1600 19320WARD 1250 15120…

14 rows selected.


Valores Nulos

Um valor nulo é um atributo sem um valor.

Quando um valor nulo pode ocorrer?

O valor não é conhecidoO valor ainda não foi preenchidoO atributo não se aplica

Nulo é diferente de zero ou espaços


Valores NulosSQL> SELECT ename, job, comm FROM emp;

ENAME JOB COMM--------------- --------------- ---------KING PRESIDENTBLAKE MANAGERCLARK MANAGERALLEN SALESMAN 300WARD SALESMAN 50MARTIN SALESMAN 1400TURNER SALESMAN 0MILLER CLERK…14 rows selected.


Valores Nulos em Expressões Aritméticas

SQL> SELECT ename, job, 10*sal+comm FROM emp;

ENAME JOB 10*SAL+COMM--------------- --------------- -----------KING PRESIDENTBLAKE MANAGERCLARK MANAGERALLEN SALESMAN 16300WARD SALESMAN 12550…14 rows selected.

Expressões aritiméticas contendo nulos, retornam nulo.


Definindo Apelidos para Colunas

• Renomeia o cabeçalho da coluna;

• Segue o nome da coluna. Pode ser precedido da palavra-chave AS;

• Deve estar entre aspas duplas se contiver espaços, caracteres especiais ou para diferenciar maiúsculas de minúsculas.


Usando apelidos para colunas

SQL> SELECT empno as matricula, ename, mgr gerente FROM emp;

MATRICULA ENAME GERENTE--------- --------------- --------- 7839 KING 7698 BLAKE 7839 7782 CLARK 7839…14 rows selected.


Usando apelidos para colunas

SQL> SELECT ename, sal*12 “Salario Annual” FROM emp;

ENAME Salario Annual--------------- --------------KING 60000BLAKE 34200CLARK 29400ALLEN 19200…14 rows selected.


Operador de Concatenação

• Concatena colunas ou cadeias de caracteres a colunas;

• Representado por duas barras verticais : ||

• Cria uma uma cadeia de caracteres como coluna resultante.


Usando o Operador de Concatenação

SQL> SELECT ename||’ ‘||job AS “Funcionarios” FROM emp;

Funcionarios-------------------------------KING PRESIDENTBLAKE MANAGERCLARK MANAGERALLEN SALESMANWARD SALESMANMARTIN SALESMAN…

14 rows selected.


Literal

• Literal é um caracter, expressão ou número incluído no SELECT

• Literais do tipo data e caracter devem aparecer entre aspas simples

• A cadeia de caracteres é repetida para cada linha retornada pelo comando de seleção


Usando Literais

SQL> SELECT ename||’ is a ‘||job as “Funcionarios” FROM emp;

Funcionarios------------------------------------KING is a PRESIDENTBLAKE is a MANAGERCLARK is a MANAGERALLEN is a SALESMANWARD is a SALESMANMARTIN is a SALESMAN…14 rows selected.


Linhas duplicadas O resultado padrão das consultas inclui todas as linhas,

inclusive as duplicadas.

SQL> SELECT deptno FROM emp;

DEPTNO--------- 10 30 10 30 30 30 30 10…14 rows selected.


Eliminando linhas duplicadas

Cláusula DISTINCT

SQL> SELECT distinct deptno FROM emp;

DEPTNO--------- 10 20 30


Apresentando a estrutura da tabela

DESC[RIBE] tabela


Apresentando a estrutura da tabela

SQL> DESCRIBE EMP

Name Null? Type ------------------- -------- -------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(15) JOB VARCHAR2(15) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)


22Seleções com CritériosSeleções com Critérios

OrdenaçãoOrdenação


Limitando as linhas retornadas pelo SELECT

Selecione todos os empregados cujo cargo é SALESMAN

EMPNO ENAME JOB--------- --------------- --------------- 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7654 MARTIN SALESMAN 7844 TURNER SALESMAN


Limitando as linhas retornadas pelo SELECT

Cláusula WHERE

SELECT [DISTINCT] {*, coluna [apelido],…}

FROM tabela

WHERE condição(ões);


Cláusula WHERE

SQL> SELECT ename, job, deptno FROM emp WHERE job=‘PRESIDENT’;

ENAME JOB DEPTNO--------------- --------------- ---------KING PRESIDENT 10


Operadores de Comparação

Operador

=

>

>=

<

<=

<>

Descrição

Igual a

Maior que

Maior ou igual a

Menor que

Menor ou igual a

Diferente de


Usando os Operadores de Comparação

SQL> SELECT ename, sal, comm FROM emp WHERE sal<=comm;

ENAME SAL COMM--------------- --------- ---------MARTIN 1250 1400


Outros Operadores de Comparação

Operador

BETWEEN… AND…

IN (lista)

LIKE

IS NULL

Descrição

Valores entre um valor mínimo e um máximo (inclusive)

valores que estão na lista de valores especificados pelo operador IN

valores que sigam a um determinado padrão

valores que sejam nulos


Usando o Operador BETWEEN

SQL> SELECT ename, sal Mínimo Máximo FROM emp WHERE sal BETWEEN 1000 and 2000;

ENAME SAL--------------- ---------ALLEN 1600WARD 1250MARTIN 1250TURNER 1500MILLER 1300ADAMS 1100

6 rows selected.


Usando o Operador IN

SQL> SELECT empno, ename, mgr FROM emp WHERE mgr IN (‘7902’,’7698’,’7782’)

EMPNO ENAME MGR--------- --------------- --------- 7499 ALLEN 7698 7521 WARD 7698 7654 MARTIN 7698 7844 TURNER 7698 7934 MILLER 7782 7900 JAMES 7698 7369 SMITH 7902

7 rows selected.


Usando o Operador LIKE

• As condições de busca podem conter letras ou números

• % significa nenhum ou vários caracteres quaisquer

• _ significa um caracter qualquer


SQL> SELECT ename FROM emp WHERE ename like ‘W%’

Usando o Operador LIKE

ENAME---------------WARD


Usando o Operador LIKE• É possível buscar por % ou _ utilizando o

caracter de escape

• Para utlizar o caracter de escape é preciso especificá-lo na consulta

• Para selecionar todos os nomes de empregado começando por S_% o comado seria:

SQL> SELECT ename FROM emp WHERE ename like ‘S\_\%%’ ESCAPE ‘\’;


Usando o Operador IS NULL

SQL> SELECT ename, mgr FROM emp WHERE mgr IS NULL;

ENAME MGR--------------- ---------KING


Operadores Lógicos

Operador

AND

OR

NOT

Descrição

Retorna VERDADEIRO se as duas condições forem satisfeitas

Retorna VERDADEIRO se pelo menosUma das condições for satisfeita

Retorna VERDADEIRO se a condiçãoFor FALSA


Usando o Operador AND

SQL> SELECT ename, job, sal FROM emp WHERE sal>=1000 and job=‘SALESMAN’;

ENAME JOB SAL--------------- --------------- ---------ALLEN SALESMAN 1600WARD SALESMAN 1250MARTIN SALESMAN 1250TURNER SALESMAN 1500


Usando o Operador OR

SQL> SELECT ename, job, sal FROM emp WHERE sal>=2000 or job=‘MANAGER’;

ENAME JOB SAL--------------- --------------- ---------KING PRESIDENT 5000BLAKE MANAGER 2850CLARK MANAGER 2450JONES MANAGER 2975SCOTT ANALYST 3000FORD ANALYST 3000

6 rows selected.


Usando o Operador NOTSQL> SELECT ename, job FROM emp WHERE job not in (‘SALESMAN’, ‘MANAGER’)

ENAME JOB--------------- ---------------KING PRESIDENTMILLER CLERKJAMES CLERKSCOTT ANALYSTFORD ANALYSTSMITH CLERKADAMS CLERK

7 rows selected.


AND e OR na mesma consulta

SQL> SELECT ename, job, hiredate, sal FROM emp WHERE sal > 500 AND job =‘SALESMAN' AND (hiredate>='03-DEC-83' OR hiredate <='17-DEC-84’);

ENAME JOB HIREDATE SAL--------------- --------------- --------- ---------ALLEN SALESMAN 15-AUG-83 1600WARD SALESMAN 26-MAR-83 1250MARTIN SALESMAN 05-DEC-83 1250TURNER SALESMAN 04-JUN-84 1500

Usar parênteses para tornar a consulta mais clara;


ORDER BY

• Ordena as linhas retornadas pela consulta;– ASC: ordem crescente– DESC: ordem decrescente

• Se nada for especificado o padrão será ASC


Selecionando em Ordem CrescenteSQL> SELECT ename, job, hiredate FROM emp ORDER By hiredate;

ENAME JOB HIREDATE--------------- --------------- ---------WARD SALESMAN 26-MAR-83SMITH CLERK 13-JUN-83ALLEN SALESMAN 15-AUG-83JONES MANAGER 31-OCT-83MILLER CLERK 21-NOV-83MARTIN SALESMAN 05-DEC-83FORD ANALYST 05-DEC-83SCOTT ANALYST 05-MAR-84…14 rows selected.


Selecionando em Ordem DecrescenteSQL> SELECT ename, job, hiredate FROM emp ORDER By hiredate DESC;

ENAME JOB HIREDATE--------------- --------------- ---------JAMES CLERK 23-JUL-84KING PRESIDENT 09-JUL-84BLAKE MANAGER 11-JUN-84TURNER SALESMAN 04-JUN-84ADAMS CLERK 04-JUN-84CLARK MANAGER 14-MAY-84SCOTT ANALYST 05-MAR-84MARTIN SALESMAN 05-DEC-83…14 rows selected.


Ordenando pelo apelido da colunaSQL> SELECT empno, ename, sal*12 anual FROM emp ORDER BY anual;

EMPNO ENAME ANUAL--------- --------------- --------- 7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7521 WARD 15000 7654 MARTIN 15000 7934 MILLER 15600 7844 TURNER 18000 7499 ALLEN 19200…14 rows selected.


Ordenando por múltiplas colunasSQL> SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC;

ENAME DEPTNO SAL--------------- --------- ---------KING 10 5000CLARK 10 2450MILLER 10 1300SCOTT 20 3000FORD 20 3000JONES 20 2975ADAMS 20 1100SMITH 20 800BLAKE 30 2850…14 rows selected.


33Funções SQLFunções SQL


Funções SQL

FUNÇÃO

ValorResultante

Entrada Saída


Tipos de Funções SQL

FUNÇÃO

Função deGrupo

Função de1 linha


Funções de 1 linha

FUNÇÃOde 1 linha

Caracter

NúmeroGenéricas

Conversão Data


Funções de Caracteres

Funções deCaracteres

Manipulaçãode caracteres

Conversão deMáiúsculas/Minúsculas

LOWERUPPERINITCAP

CONCATSUBSTRLENGTHINSTRLPADRPAD

LTRIMRTRIMREPLACE


Conversão de Máiúsculas/Minúsculas

Função

LOWER (‘Curso para PAP’)

UPPER (‘Curso para PAP’)

INITCAP (‘Curso para PAP’)

Resultado

curso para pap

CURSO PARA PAP

Curso Para Pap


Conversão de Máiúsculas/Minúsculas

SQL> SELECT empno, ename, deptno FROM emp WHERE ename=‘martin’;no rows selected

SQL> SELECT empno, ename, deptno FROM emp WHERE LOWER(ename)=‘martin’;

EMPNO ENAME DEPTNO--------- --------------- --------- 7654 MARTIN 30


Manipulação de Caracteres

Função

CONCAT (‘Bom’,’Dia’)

SUBSTR (‘Oracle’,1,3)

LENGTH (‘Oracle’)

INSTR (‘Oracle’,’a’)

Resultado

BomDia

Ora

6

3


Manipulação de Caracteres

SQL> SELECT ename, CONCAT (ename,job), LENGTH(ename) len, INSTR(ename,’A’) ins FROM emp WHERE substr(job,1,5)=‘SALES’;

ENAME CONCAT(ENAME,JOB) LEN INS--------------- ----------------- --------- ---------ALLEN ALLENSALESMAN 5 1WARD WARDSALESMAN 4 2MARTIN MARTINSALESMAN 6 2TURNER TURNERSALESMAN 6 0


Função NVL

• Converte nulos para um valor especificado• Pode ser usado com dados do tipo data,

caracter e numérico• O dado retornado deve ter o mesmo tipo

que o dado comparado– NVL (comm,0)– NVL(hiredate,’01-JAN-97’)– NVL(job, ‘Sem cargo’)


TO_CHAR com DatasSQL> SELECT ename, sal,comm, (sal*12)+NVL(comm,0) FROM emp; ENAME SAL COMM (SAL*12)+NVL(COMM,0)--------------- --------- --------- --------------------KING 5000 60000BLAKE 2850 34200CLARK 2450 29400ALLEN 1600 300 19500WARD 1250 50 15050MARTIN 1250 1400 16400TURNER 1500 0 18000MILLER 1300 15600JONES 2975 0 35700…

14 rows selected.


44Funções de GrupoFunções de Grupo


Funções de Grupo Operam em um conjunto de linhas fornecendo um resultado para o

grupo

DEPTNO SAL--------- --------- 10 5000 30 2850 10 2450 30 1600 30 1250 30 1250 30 1500 10 1300 20 2975 30 950 20 3000 20 3000 20 800 20 1100

Maior salário naTabela EMP

MAX(SAL)--------- 5000


Tipos de Funções de GrupoFunção

AVG

COUNT

MAX

MIN

STDEV

SUM

VARIANCE

Descrição

Média aritmética

Contador de linhas retornadas

Maior valor do grupo

Menor valor do grupo

Desvio padrão

Somatório

Variância


Funções de Grupo

SELECT coluna, função_de_grupo(coluna)FROM tabela[WHERE condição][GROUP BY coluna]


Funções AVG e SUM

SQL> SELECT AVG(sal), SUM(sal) FROM emp;

AVG(SAL) SUM(SAL)--------- ---------2073.2143 29025


Funções MAX e MIN

SQL> SELECT MAX(hiredate), MIN(hiredate) FROM emp;

MAX(HIRED MIN(HIRED--------- ---------23-JUL-84 26-MAR-83


Função COUNT

SQL> SELECT COUNT(*) FROM emp WHERE deptno=30;

COUNT(*)--------- 6


Função COUNT

SQL> SELECT COUNT(comm) FROM emp WHERE deptno=30;

COUNT(COMM)----------- 4

COUNT(expr) retorna o número de linhas não nulas


Função de Grupo e Valores Nulos

SQL> SELECT AVG(comm) FROM emp;

AVG(COMM)--------- 350

Funções de Grupo ignoram os valores nulos


Usando NVL com Função de Grupo

SQL> SELECT AVG(NVL(comm,0)) FROM emp;

AVG(NVL(COMM,0))---------------- 125

NVL força as funções de grupo a considerarem os nulos


Criando Grupos de Dados

DEPTNO SAL--------- --------- 10 5000 30 2850 10 2450 30 1600 30 1250 30 1250 30 1500 10 1300 20 2975 30 950 20 3000 20 3000 20 800 20 1100

Média de saláriosTabela EMP por departamento

DEPTNO AVG(SAL)--------- --------- 10 2916.6667 20 2175 30 1566.6667


Criando Grupos de Dados:Cláusula GROUP BY

SELECT coluna, função_de_grupo(coluna)FROM tabela[WHERE condição][GROUP BY expressão_de_group_by][ORDER BY coluna];


Cláusula GROUP BY

SQL> SELECT AVG(sal) FROM emp GROUP BY deptno;

AVG(SAL)---------2916.6667 21751566.6667

As colunas na cláusula GROUP BY não precisam estar naCláusula SELECT


GROUP By com mais de uma coluna

DEPTNO SAL--------- --------- 10 5000 30 2850 10 2450 30 1600 30 1250 30 1250 30 1500 10 1300 20 2975 30 950 20 3000 20 3000 20 800 20 1100 Soma dos salários Tabela EMP por departamento e cargo

DEPTNO JOB SUM(SAL)--------- --------------- --------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600


Cláusula GROUP BYSQL> SELECT deptno, job, sum(sal) FROM emp GROUP BY deptno, job;

DEPTNO JOB SUM(SAL)--------- --------------- --------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600

9 rows selected.


Uso Incorreto das Funções de Grupo

SQL> SELECT deptno, COUNT(enam) FROM emp;

SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group function

Qualquer coluna na cláusula SELECT que não esteja em uma Função de grupo, deve aparecer na cláusula GROUP BY


Uso Incorreto das Funções de Grupo

SQL> SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal)>2000 GROUP BY deptno;

WHERE AVG(sal)>2000 *ERROR at line 3:ORA-00934: group function is not allowed here

A cláusula WHERE não pode ser usada para filtrar funções de grupo.


Filtrando Resultados das Funções de Grupo

DEPTNO SAL--------- --------- 10 5000 30 2850 10 2450 30 1600 30 1250 30 1250 30 1500 10 1300 20 2975 30 950 20 3000 20 3000 20 800 20 1100

Maior salário pordepartamento queseja maior que 2900

DEPTNO MAX(SAL)--------- --------- 10 5000 20 3000


Filtrando Resultados das Funções de Grupo Cláusula HAVING

SELECT coluna, função_de_grupo(coluna)FROM tabela[WHERE condição][GROUP BY expressão_de_group_by][HAVING condição_de_grupo][ORDER BY coluna];

1. As linhas são agrupadas2. A função de grupo é aplicada3. Grupos que satisfaçam a condição_de_grupo são selecionados


Cláusula HAVING

SQL> SELECT deptno, max(sal) FROM emp GROUP BY deptno HAVING max(sal)>2900;

DEPTNO MAX(SAL)--------- --------- 10 5000 20 3000


Cláusula HAVING

SQL> SELECT job, sum(sal) FROM emp WHERE job <> ‘SALESMAN’ GROUP BY job HAVING sum(sal)>5000 ORDER BY SUM(sal);

JOB SUM(SAL)--------------- ---------ANALYST 6000MANAGER 8275


55Extraindo Dados de Extraindo Dados de Mais de Uma TabelaMais de Uma Tabela


Extraindo Dados de Mais de Uma Tabela

EMPNO ENAME DEPTNO--------- ------- --------- 7839 KING 10 7698 BLAKE 30 7782 CLARK 10 7499 ALLEN 30…

DEPTNO DNAME LOC--------- -------------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCHING DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

EMPNO DEPTNO LOC--------- --------- --------------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7499 30 CHICAGO…


Junção• Usada para obter dados de mais de uma tabela;

• A condição de junção de ser escrita na cláusula WHERE

• Se a coluna aparecer com o mesmo nome em mais de uma tabela, ele deve ser prefixada pelo nome da tabela

SELECT tabela1.coluna, tabela2.colunaFROM tabela1, tabela2WHERE tabela1.coluna = tabela2.coluna


Junção

SQL> SELECT emp.ename, dept.deptno, dept.dname FROM emp, dept WHERE emp.deptno=dept.deptno;

ENAME DEPTNO DNAME------- --------- --------------KING 10 ACCOUNTINGBLAKE 30 SALESCLARK 10 ACCOUNTINGALLEN 30 SALESWARD 30 SALESMARTIN 30 SALESTURNER 30 SALES…


Produto Cartesiano

• É realizado quando:– A codição de junção é omitida– A condição de junção é inválida– Todas as linhas da primeira coluna são

combinadas com todas as linhas da segunda coluna


Produto Cartesiano

SQL> SELECT emp.ename, dept.deptno, dept.dname FROM emp, dept;

ENAME DEPTNO DNAME------- --------- --------------KING 10 ACCOUNTINGBLAKE 10 ACCOUNTINGCLARK 10 ACCOUNTINGALLEN 10 ACCOUNTINGWARD 10 ACCOUNTINGMARTIN 10 ACCOUNTING…56 rows selected.


Usando Apelidos para Tabelas

SQL> SELECT e.ename, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;



Junções Equivalentes

SQL> SELECT emp.ename, dept.deptno, dept.dname FROM emp, dept WHERE emp.deptno=dept.deptno;


O operador utilizado é o =


Junções de uma tabela com ela mesma

SQL> SELECT funcionario.ename || ‘ trabalha para ’|| gerente.ename FROM emp funcionario, emp gerente WHERE funcionario.mgr=gerente.empno;

FUNCIONARIO.ENAME||'TRABALHAPARA'||GERENTE.EN---------------------------------------------BLAKE trabalha para KINGCLARK trabalha para KINGALLEN trabalha para BLAKEWARD trabalha para BLAKEMARTIN trabalha para BLAKETURNER trabalha para BLAKEMILLER trabalha para CLARK…


66Consultas AninhadasConsultas Aninhadas


Consulta Aninhada

É aquela que está contida dentro de uma outra consulta, e que retorna valores intermediários.

SELECT coluna1, coluna2FROM tabelaWHEREcoluna1 = (SELECT coluna FROM tabela WHERE condição)

Também conhecida como subconsulta


Tipos de Consulta Aninhada

• Retornam apenas uma linha

• Retornam mais de uma linha


Subconsultas que retornam uma linha

Operador

=

>

>=

<

<=

<>

Descrição

Igual

Maior

Maior ou igual

Menor

Menor ou igual

Diferente

Utilizar os operadores de comparação


Subconsultas que retornam uma linha

SQL>SELECT ename, job FROM emp WHERE job= (SELECT job FROM emp WHERE

empno=7369) AND sal > (SELECT sal FROM emp WHERE

empno=7876); ENAME JOB------- ---------------MILLER CLERK

CLERK

1100


Erros nas subconsultas que retornam uma linha

SQL>SELECT ename FROM emp WHERE empno= (SELECT empno FROM emp WHERE job=‘SALESMAN’);

WHERE empno= (SELECT empno FROM emp WHERE job='SALESMAN') *ERROR at line 3:ORA-01427: single-row subquery returns more than one row


Subconsultas que retornam mais de uma linha

Operador

IN

ANY

ALL

Descrição

Igual a qualquer um dos elementos da lista

Compara o valor com cada valor retornado pelasubconsulta

Compara o valor com todos os valores retornadosPela subconsulta

Utilizar os operadores de comparação múltipla


Operador IN

SQL>SELECT ename, job FROM emp WHERE empno IN (SELECT empno FROM emp WHERE job=‘SALESMAN’);

ENAME JOB------- ---------------ALLEN SALESMANWARD SALESMANMARTIN SALESMANTURNER SALESMAN


Operador ANY

SQL>SELECT empno, ename, job FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job=‘CLERK’) AND job<>’CLERK’;

EMPNO ENAME JOB--------- ------- --------------- 7521 WARD SALESMAN 7654 MARTIN SALESMAN


Operador ALL

SQL>SELECT empno, ename, job FROM emp WHERE sal > ALL (SELECT AVG(sal) from emp GROUP BY deptno);

EMPNO ENAME JOB--------- ------- --------------- 7839 KING PRESIDENT 7566 JONES MANAGER 7788 SCOTT ANALYST 7902 FORD ANALYST


Consulta interna correlacionada

• É executada para cada uma das linhas consideradas candidatas na consulta principal ;

• A execução usa o valor da coluna da consulta principal;• É identificada pela presença de uma coluna da consulta principal na consulta interna.


Consulta interna correlacionada

SQL> SELECT empno, ename, sal, deptno FROM emp e WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno) ORDER BY deptno;

EMPNO ENAME SAL DEPTNO--------- ------- --------- --------- 7839 KING 5000 10 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 20 7698 BLAKE 2850 30 7499 ALLEN 1600 30

Para encontrar os empregados que recebem mais do que o salário médio de seus departamentos:


Operador EXISTS

SQL>SELECT empno, ename, job, deptno FROM emp e WHERE EXISTS (SELECT empno FROM emp p where p.mgr=e.empno) ORDER BY empno;

EMPNO ENAME JOB DEPTNO--------- ------- --------------- --------- 7566 JONES MANAGER 20 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7788 SCOTT ANALYST 20…

Usado com consultas aninhadas correlacionadas, testando se o valor existe. Se o valor existir, retorna verdadeiro, caso contrário retorna falso.

Para listar todas as empregados com ao menos um funcionário subordinado:


66Linguagem de ManipulaçãoLinguagem de Manipulação

de Dados (DML)de Dados (DML)


Linguagem de Manipulação de Dados(DML)

• Utilizada para:– Incluir novas linhas em uma tabela;– Alterar linhas existentes na tabela;– Excluir linhas da tabela.

• Uma transação é um conjunto de comandos da LMD que formam uma unidade lógica de trabalho.


Comando INSERT

INSERT INTO tabela [ (coluna, [,coluna…])]VALUES (valor [,valor…]);

Inclui linhas na tabela

SQL> INSERT INTO dept(deptno, dname, loc) 2 VALUES (50,'DEVELOPMENT', 'DETROIT');

1 row created.


Incluindo linhas com valores nulos

Método Implícito: omitir o nome da coluna

SQL> INSERT INTO dept(deptno, dname) 2 VALUES (60,'MIS');

1 row created.

Método Explícito: especificar a palavra-chave NULL

SQL> INSERT INTO dept 2 values (70,'FINANCE',NULL);

1 row created.


Incluindo linhas

SQL> INSERT INTO emp(empno, ename, job, mgr, hiredate, sal,comm,deptno) 2 VALUES (7196,'GREEN','SALESMAN',7782,SYSDATE,2000,NULL,10);

1 row created.

SQL> INSERT INTO emp 2 VALUES (2296,'AROMANO','SALESMAN',7782, 3 TO_DATE ('FEB 3, 97', 'MON DD, YY'), 4 1300,null,10);

1 row created.


Copiando dados de outra tabela

SQL> INSERT INTO managers (id, name, salary,hiredate) 2 SELECT empno, ename, sal,hiredate 3 FROM emp 4 WHERE job=‘MANAGER’;

3 rows created.

•O INSERT é escrito como uma subconsulta;

•A cláusula VALUES não deve ser utilizada;

•O Número de colunas na cláusula INSERT deve ser compatível

•Com o número de colunas da subconsulta.


Comando UPDATE

UPDATE tabelaSET coluna = valor [,coluna=valor]WHERE condição];

Altera linhas existentes na tabela

SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782;

1 row updated.


Comando UPDATE

Se a cláusula WHERE não for especificada, todas aslinhas da tabela serão alteradas

SQL> UPDATE emp 2 SET deptno = 20

14 rows updated.


Alterando linhas

SQL> UPDATE emp 2 SET (job, deptno) = 3 (SELECT job, deptno 4 FROM emp 5 WHERE empno = 7499) 6 WHERE empno = 7698;

1 row updated


Alterando linhas

SQL> UPDATE employee 2 SET deptno = 3 (SELECT deptno 4 FROM emp 5 WHERE empno = 7788) 6 WHERE job = 7 (SELECT job 8 FROM emp 9 WHERE empno=7788);

2 rows updated


Erro de Restrição de Integridade ao alterar linhas

SQL> UPDATE emp SET deptno = 55 WHERE deptno = 10;

UPDATE emp *ERROR at line 1:ORA-02291: integrity constraint (SCOTT.FK_EMPDPTO) violated - parent key not found

O departamento 55 não existe na tabela dept !


Comando DELETE

DELETE [FROM] tabela[WHERE condição]

Exclui linhas da tabela

SQL> DELETE FROM dept 2 WHERE dname='DEVELOPMENT';

1 row deleted.


Excluindo linhas

SQL> DELETE FROM emp 2 WHERE deptno = 3 (SELECT deptno 4 FROM dept 5 WHERE dname=‘SALES’)

6 rows deleted


Erro de Restrição de Integridade ao excluir linhas

SQL> DELETE FROM dept WHERE deptno=10;

DELETE FROM dept *ERROR at line 1:ORA-02292: integrity constraint (SCOTT.FK_EMPDPTO) violated - child record found

Não é permitido excluir uma linha que contenha uma chave primáriaQue esteja sendo usada como chave estrangeira em outra tabela!


77Linguagem de Definição de Linguagem de Definição de

Dados (DDL)Dados (DDL)


Objetos do Banco de Dados

Objeto

Tabela

Visão

Sequência

Índice

Sinônimo

Descrição

Unidade básica de armazenamento. Composta de linhas e colunas.

Subconjuntos de dados de uma ou mais tabelas

Gerador de números sequenciais

Estrutura criada para melhorar a performance deConsultas em tabelas

Nome alternativo para os objetos


Convenções para os nomes de objeto

• Deve começar com uma letra

• Pode conter de 1 a 30 caracteres

• Deve conter apenas A-Z, a-z, 0-9, _ , $ e #

• Não pode haver nomes duplicados

• Não pode ser uma palavra reservada do SGBD Oracle


Comando CREATE TABLE

Cria uma tabela no banco de dados

CREATE TABLE [esquema.]tabela (coluna tipo_de_dado [DEFAULT expressão]);

A opção DEFAULT especifica o valor padrão que a coluna receberácaso nenhum valor seja especificado no momento do INSERT.


Criando Tabelas

CREATE TABLE dept(deptno number(2), dname varchar2(14), loc varchar2(15));


Tipos de DadosObjeto

VARCHAR2(tamanho)

CHAR (tamanho)

NUMBER

DATE

LONG

CLOB

RAW e LONG RAW

BLOB

BFILE

Descrição

Cadeia de caracteres de tamanho variável

Cadeia de caracteres de tamanho fixo

Dados númericos de tamanho variável

Data e hora

Cadeias de caracteres de até 2Gb

Cadeias de caracteres de até 4Gb

Dados binários

Dados binários de até 4Gb

Dados binários usados em arquivo externo. Até 4 Gb.


Criando uma tabela usando uma subconsulta

CREATE TABLE tabela (coluna tipo_de_dado [DEFAULT expressão])AS subconsulta;

CREATE TABLE dept30AS SELECT empno, ename, sal*12 ANNSAL, hiredate from emp WHERE deptno=30;


Comando ALTER TABLE

Usado para:• Adicionar uma coluna à tabela;• Excluir uma coluna da tabela;• Alterar uma coluna;• Definir um valor DEFAULT para a coluna.

ALTER TABLE tabela ADD (coluna tipo_de_dado [DEFAULT expressão]);

ALTER TABLE tabela MODIFY (coluna tipo_de_dado [DEFAULT expressão]);

ALTER TABLE tabela DROP COLUMN (coluna);


Adicionando colunas

ALTER TABLE dept30ADD (job VARCHAR2(9));


Alterando colunas

ALTER TABLE dept30MODIFY (ename VARCHAR2(15));


Excluindo colunas

ALTER TABLE dept30DROP COLUMN (job);


Excluindo uma Tabela

DROP TABLE tabela;

DROP TABLE dept30;

• Dados eestrutura da tabela são eliminados;

• Transções pendentes são efetivadas;

• Todos os índices da tabela são excluídos;

• Não é possível realizar ROLLBACK.


Renomeando Objetos

Usado para alterar o nome de tabelas, visões,

Sequências, ou sinônimos.

RENAME nome_antigo TO nome_novo;

RENAME dept TO departament;


Truncando uma Tabela

- Remove todas as linha da tabela;- Mantém a estrutura da tabela;- Disponibiliza o espaço que estava sendo usado;- Não é possível realizar ROLLBACK

TRUNCATE TABLE tabela;

TRUNCATE TABLE department;


Restrições de Integridade• Implementam regras no nível das tabelas;• Previnem a exclusão dos dados em uma tabela se

houver dependências.• Restições de Integridade válidas no Oracle:

– NOT NULL– UNIQUE Key– PRIMARY Key– FOREIGN Key– CHECK


Restrições de Integridade

• Podem ser criadas:– No momento da criação da tabela– Após a criação da tabela

• Podem ser definidas no nível da tabela ou da coluna.


Definindo Restrições de Integridade

CREATE TABLE [esquema.]tabela (coluna tipo_de_dado [DEFAULT expressão] [restrição_de_integridade], … [restrição_de_integridade_da_tabela]);

CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10) deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_empno_pk PRIMARY KEY(empno));


Definindo Restrições de Integridade

coluna [CONSTRAINT nome_da_restrição] tipo_da_restrição,

Restrição de integridade no nível da coluna:

coluna,…[CONSTRAINT nome_da_restrição] tipo_da_restrição(coluna,…),

Restrição de integridade no nível da tabela:


NOT NULL

• Não permite que seja incluído nulo na coluna• Definida no nível da coluna

create table EMP(EMPNO number(4) not null, ENAME varchar2(15) , JOB varchar2(15) , MGR number(4) , HIREDATE date , SAL number(7,2) , COMM number(7,2) , DEPTNO number(2) )


UNIQUE Key

• Definida no nível da coluna ou da tabela• Obriga que os valores para a coluna sejam únicos

CREATE TABLE dept( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT detp_dname_uk UNIQUE (dname))


PRIMARY Key

• Definida no nível da coluna ou da tabela• Cria uma chave primária para a tabela

CREATE TABLE dept( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT detp_deptno_pk PRIMARY KEY (deptno));


Foreign Key• Definida no nível da coluna ou da tabela• Cria uma chave estrangeira em uma tabela que referencia uma chave primária em outra tabela

CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), deptno NUMBER(7,2), CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept(deptno));


Foreign Key• A opção ON DELETE CASCADE permite a exclusão em tabelas pai que possuam

registros associados em tabelas filhas e exclui automaticamente as linhas das tabelas filhas.

CREATE TABLE emp( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), deptno NUMBER(7,2), CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE);


CHECK

• Definida no nível da coluna ou da tabela• Especifica uma codição que as linhas devem satisfazer

CREATE TABLE dept( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT dept_deptno_ck CHECK (deptno BETWEEN 10 and 00));


Adicionando uma Restrição de Integridade

ALTER TABLE tabelaADD [CONSTRAINT restrição] tipo_de_dado (coluna);

ALTER TABLE empADD CONSTRAINT emp_mgr_fkFOREIGN KEY (mgr) REFERENCES emp(empno);

Restrição de Integridade NOT NULL deve ser adicionadausando a cláusula MODIFY.

ALTER TABLE empMODIFY job VARCHAR2(15) NOT NULL);


Removendo uma Restrição de Integridade

ALTER TABLE tabelaDROP CONSTRAINT restrição;

ALTER TABLE empDROP CONSTRAINT emp_mgr_fk;

Para remover a PRIMARY KEY da tabela dept e a FOREIGN KEY associada da coluna deptno na tabela emp:

ALTER TABLE deptDROP PRIMARY KEY CASCADE;


Desabilitando Restrições de Integridade

ALTER TABLE empDISABLE CONSTRAINT emp_empno_pk;


Habilitando Restrições de Integridade

ALTER TABLE empENABLE CONSTRAINT emp_empno_pk;


88VisõesVisões


O que é uma Visão?

• É como uma janela através da qual os dados das tabelas podem ser vistos e alterados;

• É derivada de uma tabela ou de outra visão, a qual é chamada de tabela ou visão base. - uma tabela real com os dados fisicamente armazenados~;

• Não tem dados próprios. Os dados são manipulados a partir das tabelas base.


Aplicabilidade

• Restringir o acesso ao banco de dados;

• Simplificar consultas complexas;

• Provê independência de dados


Visões Simples X Complexas

Característica

Número de tabelas

Contém funções

Contém grupos de dados

Permite DML

Simples

Uma

Não

Não

Sim

Complexa

Uma ou mais

Sim

Sim

Nem sempre


Criando uma Visão

CREATE [OR REPLACE] VIEW nome_da_viewAS subconsulta

A subquery não pode conter a cláusula ORDER BY


Excluindo uma Visão

DROP VIEW visão;

DROP VIEW empvu10;;

Documents

Revisão de SQL