Baixe o app para aproveitar ainda mais
Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original
* * * 1 Conceitos básicos em SQL Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * Comandos da SQL Os comandos SQL podem ser divididos em quatro grupos: Seleção/Consulta; Definição de dados; Manipulação de dados; Controle de dados. Revisao - SQL - Prof. George Hamilton * * * O Comando SELECT Recupera informações do banco de dados; Utilizado para realizar consultas. Revisao - SQL - Prof. George Hamilton * * * Operações usando o SELECT Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Selecionando todas as colunas SQL> SELECT * FROM dept; DEPTNO DNAME LOC --------- -------------- --------------- 10 ACCOUNTING NEW YORK 20 RESEARCHING DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Revisao - SQL - Prof. George Hamilton * * * Selecionando colunas específicas SQL> SELECT deptno, dname FROM dept; DEPTNO DNAME --------- -------------- 10 ACCOUNTING 20 RESEARCHING 30 SALES 40 OPERATIONS Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Usando Operadores Aritméticos SQL> SELECT ename, sal, sal*12 FROM emp; ENAME SAL SAL*12 --------------- --------- --------- KING 5000 60000 BLAKE 2850 34200 CLARK 2450 29400 ALLEN 1600 19200 WARD 1250 15000 … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * 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. * / + - Revisao - SQL - Prof. George Hamilton * * * Precedência dos Operadores SQL> SELECT ename, sal, 12*sal+10 FROM emp; ENAME SAL 12*SAL+10 --------------- --------- --------- KING 5000 60010 BLAKE 2850 34210 CLARK 2450 29410 ALLEN 1600 19210 WARD 1250 15010 … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * Utilizando Parênteses SQL> SELECT ename, sal, 12* (sal+10) FROM emp; ENAME SAL 12*(SAL+10) --------------- --------- ----------- KING 5000 60120 BLAKE 2850 34320 CLARK 2450 29520 ALLEN 1600 19320 WARD 1250 15120 … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * Valores Nulos Um valor nulo é um atributo sem um valor. Quando um valor nulo pode ocorrer? O valor não é conhecido O valor ainda não foi preenchido O atributo não se aplica Nulo é diferente de zero ou espaços Revisao - SQL - Prof. George Hamilton * * * Valores Nulos SQL> SELECT ename, job, comm FROM emp; ENAME JOB COMM --------------- --------------- --------- KING PRESIDENT BLAKE MANAGER CLARK MANAGER ALLEN SALESMAN 300 WARD SALESMAN 50 MARTIN SALESMAN 1400 TURNER SALESMAN 0 MILLER CLERK … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * Valores Nulos em Expressões Aritméticas SQL> SELECT ename, job, 10*sal+comm FROM emp; ENAME JOB 10*SAL+COMM --------------- --------------- ----------- KING PRESIDENT BLAKE MANAGER CLARK MANAGER ALLEN SALESMAN 16300 WARD SALESMAN 12550 … 14 rows selected. Expressões aritiméticas contendo nulos, retornam nulo. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * Usando apelidos para colunas SQL> SELECT ename, sal*12 “Salario Annual” FROM emp; ENAME Salario Annual --------------- -------------- KING 60000 BLAKE 34200 CLARK 29400 ALLEN 19200 … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * Usando o Operador de Concatenação SQL> SELECT ename||’ ‘||job AS “Funcionarios” FROM emp; Funcionarios ------------------------------- KING PRESIDENT BLAKE MANAGER CLARK MANAGER ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Usando Literais SQL> SELECT ename||’ is a ‘||job as “Funcionarios” FROM emp; Funcionarios ------------------------------------ KING is a PRESIDENT BLAKE is a MANAGER CLARK is a MANAGER ALLEN is a SALESMAN WARD is a SALESMAN MARTIN is a SALESMAN … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * Eliminando linhas duplicadas Cláusula DISTINCT SQL> SELECT distinct deptno FROM emp; DEPTNO --------- 10 20 30 Revisao - SQL - Prof. George Hamilton * * * Apresentando a estrutura da tabela DESC[RIBE] tabela Revisao - SQL - Prof. George Hamilton * * * 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) Revisao - SQL - Prof. George Hamilton * * * 2 Seleções com Critérios Ordenação Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Limitando as linhas retornadas pelo SELECT Cláusula WHERE SELECT [DISTINCT] {*, coluna [apelido],…} FROM tabela WHERE condição(ões); Revisao - SQL - Prof. George Hamilton * * * Cláusula WHERE SQL> SELECT ename, job, deptno FROM emp WHERE job=‘PRESIDENT’; ENAME JOB DEPTNO --------------- --------------- --------- KING PRESIDENT 10 Revisao - SQL - Prof. George Hamilton * * * Operadores de Comparação Operador = > >= < <= <> Descrição Igual a Maior que Maior ou igual a Menor que Menor ou igual a Diferente de Revisao - SQL - Prof. George Hamilton * * * Usando os Operadores de Comparação SQL> SELECT ename, sal, comm FROM emp WHERE sal<=comm; ENAME SAL COMM --------------- --------- --------- MARTIN 1250 1400 Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Usando o Operador BETWEEN SQL> SELECT ename, sal Mínimo Máximo FROM emp WHERE sal BETWEEN 1000 and 2000; ENAME SAL --------------- --------- ALLEN 1600 WARD 1250 MARTIN 1250 TURNER 1500 MILLER 1300 ADAMS 1100 6 rows selected. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Usando o Operador LIKE SQL> SELECT ename FROM emp WHERE ename like ‘W%’ ENAME --------------- WARD Revisao - SQL - Prof. George Hamilton * * * 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 ‘\’; Revisao - SQL - Prof. George Hamilton * * * Usando o Operador IS NULL SQL> SELECT ename, mgr FROM emp WHERE mgr IS NULL; ENAME MGR --------------- --------- KING Revisao - SQL - Prof. George Hamilton * * * Operadores Lógicos Operador AND OR NOT Descrição Retorna VERDADEIRO se as duas condições forem satisfeitas Retorna VERDADEIRO se pelo menos Uma das condições for satisfeita Retorna VERDADEIRO se a condição For FALSA Revisao - SQL - Prof. George Hamilton * * * Usando o Operador AND SQL> SELECT ename, job, sal FROM emp WHERE sal>=1000 and job=‘SALESMAN’; ENAME JOB SAL --------------- --------------- --------- ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 TURNER SALESMAN 1500 Revisao - SQL - Prof. George Hamilton * * * Usando o Operador OR SQL> SELECT ename, job, sal FROM emp WHERE sal>=2000 or job=‘MANAGER’; ENAME JOB SAL --------------- --------------- --------- KING PRESIDENT 5000 BLAKE MANAGER 2850 CLARK MANAGER 2450 JONES MANAGER 2975 SCOTT ANALYST 3000 FORD ANALYST 3000 6 rows selected. Revisao - SQL - Prof. George Hamilton * * * Usando o Operador NOT SQL> SELECT ename, job FROM emp WHERE job not in (‘SALESMAN’, ‘MANAGER’) ENAME JOB --------------- --------------- KING PRESIDENT MILLER CLERK JAMES CLERK SCOTT ANALYST FORD ANALYST SMITH CLERK ADAMS CLERK 7 rows selected. Revisao - SQL - Prof. George Hamilton * * * 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 1600 WARD SALESMAN 26-MAR-83 1250 MARTIN SALESMAN 05-DEC-83 1250 TURNER SALESMAN 04-JUN-84 1500 Usar parênteses para tornar a consulta mais clara; Revisao - SQL - Prof. George Hamilton * * * ORDER BY Ordena as linhas retornadas pela consulta; ASC: ordem crescente DESC: ordem decrescente Se nada for especificado o padrão será ASC Revisao - SQL - Prof. George Hamilton * * * Selecionando em Ordem Crescente SQL> SELECT ename, job, hiredate FROM emp ORDER By hiredate; ENAME JOB HIREDATE --------------- --------------- --------- WARD SALESMAN 26-MAR-83 SMITH CLERK 13-JUN-83 ALLEN SALESMAN 15-AUG-83 JONES MANAGER 31-OCT-83 MILLER CLERK 21-NOV-83 MARTIN SALESMAN 05-DEC-83 FORD ANALYST 05-DEC-83 SCOTT ANALYST 05-MAR-84 … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * Selecionando em Ordem Decrescente SQL> SELECT ename, job, hiredate FROM emp ORDER By hiredate DESC; ENAME JOB HIREDATE --------------- --------------- --------- JAMES CLERK 23-JUL-84 KING PRESIDENT 09-JUL-84 BLAKE MANAGER 11-JUN-84 TURNER SALESMAN 04-JUN-84 ADAMS CLERK 04-JUN-84 CLARK MANAGER 14-MAY-84 SCOTT ANALYST 05-MAR-84 MARTIN SALESMAN 05-DEC-83 … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * Ordenando pelo apelido da coluna SQL> 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. Revisao - SQL - Prof. George Hamilton * * * Ordenando por múltiplas colunas SQL> SELECT ename, deptno, sal FROM emp ORDER BY deptno, sal DESC; ENAME DEPTNO SAL --------------- --------- --------- KING 10 5000 CLARK 10 2450 MILLER 10 1300 SCOTT 20 3000 FORD 20 3000 JONES 20 2975 ADAMS 20 1100 SMITH 20 800 BLAKE 30 2850 … 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * 3 Funções SQL Revisao - SQL - Prof. George Hamilton * * * Funções SQL FUNÇÃO Valor Resultante Entrada Saída Revisao - SQL - Prof. George Hamilton * * * Tipos de Funções SQL FUNÇÃO Função de Grupo Função de 1 linha Revisao - SQL - Prof. George Hamilton * * * Funções de 1 linha Revisao - SQL - Prof. George Hamilton * * * Funções de Caracteres LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR LPAD RPAD LTRIM RTRIM REPLACE Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Manipulação de Caracteres Função CONCAT (‘Bom’,’Dia’) SUBSTR (‘Oracle’,1,3) LENGTH (‘Oracle’) INSTR (‘Oracle’,’a’) Resultado BomDia Ora 6 3 Revisao - SQL - Prof. George Hamilton * * * 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 1 WARD WARDSALESMAN 4 2 MARTIN MARTINSALESMAN 6 2 TURNER TURNERSALESMAN 6 0 Revisao - SQL - Prof. George Hamilton * * * 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’) Revisao - SQL - Prof. George Hamilton * * * TO_CHAR com Datas SQL> SELECT ename, sal,comm, (sal*12)+NVL(comm,0) FROM emp; ENAME SAL COMM (SAL*12)+NVL(COMM,0) --------------- --------- --------- -------------------- KING 5000 60000 BLAKE 2850 34200 CLARK 2450 29400 ALLEN 1600 300 19500 WARD 1250 50 15050 MARTIN 1250 1400 16400 TURNER 1500 0 18000 MILLER 1300 15600 JONES 2975 0 35700… 14 rows selected. Revisao - SQL - Prof. George Hamilton * * * 4 Funções de Grupo Revisao - SQL - Prof. George Hamilton * * * 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 na Tabela EMP MAX(SAL) --------- 5000 Revisao - SQL - Prof. George Hamilton * * * Tipos de Funções de Grupo Funçã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 Revisao - SQL - Prof. George Hamilton * * * Funções de Grupo SELECT coluna, função_de_grupo(coluna) FROM tabela [WHERE condição] [GROUP BY coluna] Revisao - SQL - Prof. George Hamilton * * * Funções AVG e SUM SQL> SELECT AVG(sal), SUM(sal) FROM emp; AVG(SAL) SUM(SAL) --------- --------- 2073.2143 29025 Revisao - SQL - Prof. George Hamilton * * * Funções MAX e MIN SQL> SELECT MAX(hiredate), MIN(hiredate) FROM emp; MAX(HIRED MIN(HIRED --------- --------- 23-JUL-84 26-MAR-83 Revisao - SQL - Prof. George Hamilton * * * Função COUNT SQL> SELECT COUNT(*) FROM emp WHERE deptno=30; COUNT(*) --------- 6 Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Função de Grupo e Valores Nulos SQL> SELECT AVG(comm) FROM emp; AVG(COMM) --------- 350 Funções de Grupo ignoram os valores nulos Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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ários Tabela EMP por departamento DEPTNO AVG(SAL) --------- --------- 10 2916.6667 20 2175 30 1566.6667 Revisao - SQL - Prof. George Hamilton * * * 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]; Revisao - SQL - Prof. George Hamilton * * * Cláusula GROUP BY SQL> SELECT AVG(sal) FROM emp GROUP BY deptno; AVG(SAL) --------- 2916.6667 2175 1566.6667 As colunas na cláusula GROUP BY não precisam estar na Cláusula SELECT Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Cláusula GROUP BY SQL> 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. Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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 por departamento que seja maior que 2900 DEPTNO MAX(SAL) --------- --------- 10 5000 20 3000 Revisao - SQL - Prof. George Hamilton * * * 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]; As linhas são agrupadas A função de grupo é aplicada Grupos que satisfaçam a condição_de_grupo são selecionados Revisao - SQL - Prof. George Hamilton * * * Cláusula HAVING SQL> SELECT deptno, max(sal) FROM emp GROUP BY deptno HAVING max(sal)>2900; DEPTNO MAX(SAL) --------- --------- 10 5000 20 3000 Revisao - SQL - Prof. George Hamilton * * * 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 6000 MANAGER 8275 Revisao - SQL - Prof. George Hamilton * * * 5 Extraindo Dados de Mais de Uma Tabela Revisao - SQL - Prof. George Hamilton * * * 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 … Revisao - SQL - Prof. George Hamilton * * * 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.coluna FROM tabela1, tabela2 WHERE tabela1.coluna = tabela2.coluna Revisao - SQL - Prof. George Hamilton * * * Junção SQL> SELECT emp.ename, dept.deptno, dept.dname FROM emp, dept WHERE emp.deptno=dept.deptno; ENAME DEPTNO DNAME ------- --------- -------------- KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING ALLEN 30 SALES WARD 30 SALES MARTIN 30 SALES TURNER 30 SALES … Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * Produto Cartesiano SQL> SELECT emp.ename, dept.deptno, dept.dname FROM emp, dept; ENAME DEPTNO DNAME ------- --------- -------------- KING 10 ACCOUNTING BLAKE 10 ACCOUNTING CLARK 10 ACCOUNTING ALLEN 10 ACCOUNTING WARD 10 ACCOUNTING MARTIN 10 ACCOUNTING … 56 rows selected. Revisao - SQL - Prof. George Hamilton * * * Usando Apelidos para Tabelas SQL> SELECT e.ename, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno; ENAME DEPTNO DNAME ------- --------- -------------- KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING ALLEN 30 SALES WARD 30 SALES MARTIN 30 SALES TURNER 30 SALES … Revisao - SQL - Prof. George Hamilton * * * Junções Equivalentes SQL> SELECT emp.ename, dept.deptno, dept.dname FROM emp, dept WHERE emp.deptno=dept.deptno; ENAME DEPTNO DNAME ------- --------- -------------- KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING ALLEN 30 SALES WARD 30 SALES MARTIN 30 SALES TURNER 30 SALES … O operador utilizado é o = Revisao - SQL - Prof. George Hamilton * * * 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 KING CLARK trabalha para KING ALLEN trabalha para BLAKE WARD trabalha para BLAKE MARTIN trabalha para BLAKE TURNER trabalha para BLAKE MILLER trabalha para CLARK … Revisao - SQL - Prof. George Hamilton * * * 6 Consultas Aninhadas Revisao - SQL - Prof. George Hamilton * * * Consulta Aninhada É aquela que está contida dentro de uma outra consulta, e que retorna valores intermediários. SELECT coluna1, coluna2 FROM tabela WHERE coluna1 = (SELECT coluna FROM tabela WHERE condição) Também conhecida como subconsulta Revisao - SQL - Prof. George Hamilton * * * Tipos de Consulta Aninhada Retornam apenas uma linha Retornam mais de uma linha Revisao - SQL - Prof. George Hamilton * * * Subconsultas que retornam uma linha Operador = > >= < <= <> Descrição Igual Maior Maior ou igual Menor Menor ou igual Diferente Utilizar os operadores de comparação Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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 pela subconsulta Compara o valor com todos os valores retornados Pela subconsulta Utilizar os operadores de comparação múltipla Revisao - SQL - Prof. George Hamilton * * * Operador IN SQL>SELECT ename, job FROM emp WHERE empno IN (SELECT empno FROM emp WHERE job=‘SALESMAN’); ENAME JOB ------- --------------- ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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: Revisao - SQL - Prof. George Hamilton * * * 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: Revisao - SQL - Prof. George Hamilton * * * 6 Linguagem de Manipulação de Dados (DML) Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * Comando UPDATE UPDATE tabela SET 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. Revisao - SQL - Prof. George Hamilton * * * Comando UPDATE Se a cláusula WHERE não for especificada, todas as linhas da tabela serão alteradas SQL> UPDATE emp 2 SET deptno = 20 14 rows updated. Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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 ! Revisao - SQL - Prof. George Hamilton * * * Comando DELETE DELETE [FROM] tabela [WHERE condição] Exclui linhas da tabela SQL> DELETE FROM dept 2 WHERE dname='DEVELOPMENT'; 1 row deleted. Revisao - SQL - Prof. George Hamilton * * * Excluindo linhas SQL> DELETE FROM emp 2 WHERE deptno = 3 (SELECT deptno 4 FROM dept 5 WHERE dname=‘SALES’) 6 rows deleted Revisao - SQL - Prof. George Hamilton * * * 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ária Que esteja sendo usada como chave estrangeira em outra tabela! Revisao - SQL - Prof. George Hamilton * * * 7 Linguagem de Definição de Dados (DDL) Revisao - SQL - Prof. George Hamilton * * * 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 de Consultas em tabelas Nome alternativo para os objetos Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * Criando Tabelas CREATE TABLE dept (deptno number(2), dname varchar2(14), loc varchar2(15)); Revisao - SQL - Prof. George Hamilton * * * Tipos de Dados Objeto 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. Revisao - SQL - Prof. George Hamilton * * * Criando uma tabela usando uma subconsulta CREATE TABLE tabela (coluna tipo_de_dado [DEFAULT expressão]) AS subconsulta; CREATE TABLE dept30 AS SELECT empno, ename, sal*12 ANNSAL, hiredate from emp WHERE deptno=30; Revisao - SQL - Prof. George Hamilton * * * 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); Revisao - SQL - Prof. George Hamilton * * * Adicionando colunas ALTER TABLE dept30 ADD (job VARCHAR2(9)); Revisao - SQL - Prof. George Hamilton * * * Alterando colunas ALTER TABLE dept30 MODIFY (ename VARCHAR2(15)); Revisao - SQL - Prof. George Hamilton * * * Excluindo colunas ALTER TABLE dept30 DROP COLUMN (job); Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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; Revisao - SQL - Prof. George Hamilton * * * 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; Revisao - SQL - Prof. George Hamilton * * * 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 Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * 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)); Revisao - SQL - Prof. George Hamilton * * * 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: Revisao - SQL - Prof. George Hamilton * * * 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) ) Revisao - SQL - Prof. George Hamilton * * * 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)) Revisao - SQL - Prof. George Hamilton * * * 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)); Revisao - SQL - Prof. George Hamilton * * * 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)); Revisao - SQL - Prof. George Hamilton * * * 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); Revisao - SQL - Prof. George Hamilton * * * 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)); Revisao - SQL - Prof. George Hamilton * * * Adicionando uma Restrição de Integridade ALTER TABLE tabela ADD [CONSTRAINT restrição] tipo_de_dado (coluna); ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr) REFERENCES emp(empno); Restrição de Integridade NOT NULL deve ser adicionada usando a cláusula MODIFY. ALTER TABLE emp MODIFY job VARCHAR2(15) NOT NULL); Revisao - SQL - Prof. George Hamilton * * * Removendo uma Restrição de Integridade ALTER TABLE tabela DROP CONSTRAINT restrição; ALTER TABLE emp DROP 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 dept DROP PRIMARY KEY CASCADE; Revisao - SQL - Prof. George Hamilton * * * Desabilitando Restrições de Integridade ALTER TABLE emp DISABLE CONSTRAINT emp_empno_pk; Revisao - SQL - Prof. George Hamilton * * * Habilitando Restrições de Integridade ALTER TABLE emp ENABLE CONSTRAINT emp_empno_pk; Revisao - SQL - Prof. George Hamilton * * * 8 Visões Revisao - SQL - Prof. George Hamilton * * * 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. Revisao - SQL - Prof. George Hamilton * * * Aplicabilidade Restringir o acesso ao banco de dados; Simplificar consultas complexas; Provê independência de dados Revisao - SQL - Prof. George Hamilton * * * Visões Simples X Complexas Revisao - SQL - Prof. George Hamilton * * * Criando uma Visão CREATE [OR REPLACE] VIEW nome_da_view AS subconsulta A subquery não pode conter a cláusula ORDER BY Revisao - SQL - Prof. George Hamilton * * * Excluindo uma Visão DROP VIEW visão; DROP VIEW empvu10;; Revisao - SQL - Prof. George Hamilton
Compartilhar