Baixe o app para aproveitar ainda mais
Prévia do material em texto
�PAGE �19� LINGUAGEM SQL EM AMBIENTE ORACLE SQL*Plus 2LINGUAGEM SQL BENEFÍCIOS 2 AMBIENTE SQL 2 COMO ENTRAR NO AMBIENTE? 2 LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL) 3 LINGUAGEM DE MANIPULAÇÃO DE DADOS (DML) 3 OPERAÇÕES DE RECUPERAÇÃO: QUERY 4 RECUPERAÇÃO SIMPLES 4 A) RECUPERAÇÃO USANDO EXPRESSÕES ARITMÉTICAS 5 B) APELIDOS DE COLUNAS 6 C) OPERADOR DE CONCATENAÇÃO 6 D) MANUSEIO DE VALORES NULOS 7 CLÁUSULA DISTINCT 8 CLÁUSULA ORDER BY 9 CLÁUSULA WHERE 11 OPERADORES SQL 14 FUNÇÕES INTEGRADAS NO SQL 16 A) FUNÇÕES PARA MANIPULAR CARACTERES 16 B) FUNÇÕES PARA MANIPULAR NÚMEROS 17 C) FUNÇÕES PARA MANIPULAR DATAS 18 D) FUNÇÕES DE CONVERSÃO 20 E) FUNÇÕES PARA MANIPULAR GRUPOS 22 CLÁUSULA GROUP BY 24 CLÁUSULA HAVING 25 SUBQUERIES (SUBCONSULTAS) 25 A) SUBQUERY DE UMA LINHA 26 B) SUBQUERIES QUE RETORNAM MAIS DE UMA LINHA DA TABELA 26 C) CLÁUSULA HAVING COM SUBQUERIES ANINHADAS 28 EXTRAIR DADOS DE MAIS DE UMA TABELA 29 JOIN-EQUI-JOIN (SIMÉTRICO) 29 OPERADORES DE CONJUNTOS (SET OPERATORS) 30 REGRAS PARA UTILIZAR UNION, INTERSECT e MINUS 31 LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL) 31 CRIAR UMA TABELA 31 CRIAR UMA TABELA UTILIZANDO A ESTRUTURA DE OUTRA 32 DATA MANIPULATION LANGUAGE – DML 33 A) INSERIR LINHAS NA TABELA 33 A.1) UTILIZANDO SUBSTITUIÇÃO DE VARIÁVEIS 33 A.2) COPIAR LINHAS (ROWS) DE OUTRA TABELA 33 B) ALTERAR LINHAS DA TABELA - SINTAXE 34 C) EXCLUIR LINHAS DA TABELA - SINTAXE 34 � LINGUAGEM SQL A linguagem SQL é própria para a realização de operações como recuperar dados (QUERY), 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. Os comandos SQL podem ser divididos em quatro grupos de comandos: Query; Definição de dados; Manipulação de dados; Controle de dados; Em 1986 a ANSI adotou o SQL como uma linguagem padrão para banco de dados relacional - RDBMS. BENEFÍCIOS É uma linguagem não procedural; Permite trabalhar com várias tabelas; Permite utilizar o resultado de uma instrução SQL em outra instrução SQL (sub-queries); Não necessita especificar o método de acesso ao dado; É uma linguagem para vários usuários como: A) Administrador do sistema; B) Administrador do banco de dados (DBA); C) Programadores de aplicações; D) Pessoal de gerência e tomada de decisão; É de fácil aprendizado; Permite a utilização dentro de uma linguagem procedural como C, COBOL, FORTRAN, Pascal, Java – através de SQL embutida. LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL) Um banco de dados projetado através do modelo ER, deve ser mapeado para um esquema relacional através de uma linguagem de definição de dados (DDL). A linguagem que será utilizada neste contexto será SQL padrão ANSI/ISO. COMANDOS CREATE TABLE Cria uma tabela e define suas colunas e outras propriedades. CREATE VIEW Define uma visão sobre uma ou mais tabelas e /ou sobre outras visões. CREATE INDEX Cria índice para uma tabela. ALTER TABLE Inclui ou redefine uma coluna numa tabela existente (não consta no padrão ANSI). DROP TABLE Exclui uma tabela. DROP VIEW Exclui uma visão. DROP INDEX Exclui um índice. LINGUAGEM DE MANIPULAÇÃO DE DADOS (DML) A manipulação do banco de dados é feita através de uma ou mais linguagens comerciais, que devem respeitar as restrições de integridade do banco de dados. A linguagem utilizada neste contexto será o SQL. COMANDOS SELECT Seleciona linhas e colunas de uma ou mais tabelas. INSERT Inclui linhas numa tabela ou visão. UPDATE Muda o valor de uma ou mais campos de uma tabela. DELETE Exclui linhas de uma tabela. OPERAÇÕES DE RECUPERAÇÃO: QUERY A estrutura básica para a recuperação de informações do banco de dados, consiste de 2 cláusulas: FORMATO: SELECT coluna1, coluna2, ..., colunan FROM nome-da-tabela; A cláusula SELECT relaciona as colunas que se quer presentes no resultado da recuperação. A cláusula FROM relaciona a tabela ou tabelas que devem ser pesquisadas na execução da query. O SELECT e o FROM são necessários em todas as consultas e sempre devem aparecer antes de qualquer outra cláusula. O resultado de uma recuperação SQL é sempre uma tabela. Para recuperar todos os valores de todas as colunas da tabela, usa-se o asterisco (*) após a palavra SELECT. RECUPERAÇÃO SIMPLES SELECT * FROM emp; ou SELECT empno, ename, job, mgr, hiredate, sal, comm, depto FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTO ---------- ----------- -------------- ------ -------------- ------- --------- --------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7696 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAI-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7782 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 500 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-91 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. A linguagem SQL suporta as seguintes expressões simples: ADIÇÃO ( + ) SUBTRAÇÃO ( - ) MULTIPLICAÇÃO ( * ) DIVISÃO ( / ) Observação: Caso a expressão aritmética contenha mais de um operador, a prioridade é *, / e depois +, -, sempre da esquerda para a direita quando existir vários operadores com a mesma prioridade. Também podem ser utilizados parênteses para especificar a ordem de execução dos operadores. A) RECUPERAÇÃO USANDO EXPRESSÕES ARITMÉTICAS Recuperar os números dos empregados, salários e salário anual. SQL> SELECT empno, sal, sal*12 FROM emp; EMPNO SAL SAL*12 ---------- ------- ----------- 7369 800 9600 7499 1600 19200 7521 1250 15000 7566 2975 35700 7654 1250 15000 7698 2850 34200 7782 2450 29400 7788 3000 36000 7839 5000 60000 7844 1500 18000 7876 1100 13200 7900 950 11400 7902 3000 36000 7934 1300 15600 14 rows selected B) APELIDOS DE COLUNAS Para criar um apelido para uma coluna, entre com o apelido depois do nome da coluna no comando SELECT. SQL> SELECT ename, sal * 12 SAL_ANUAL, comm FROM emp; ENAME SAL_ANUAL COM ----------- ------------------ ---------- SMITH 9600 ALLEN 19200 300 WARD 15000 500 JONES 35700 MARTIN 15000 1400 . . . . . . . . . MILLER 15600 C) OPERADOR DE CONCATENAÇÃO O operador de concatenação permite ligar uma coluna a outra. SQL> SELECT empno||ename EMPREGADO FROM emp; EMPREGADO -------------------------- 7369SMITH 7499ALLEN 7521WARD 7566JONES . . . 7934MILLER D) MANUSEIO DE VALORES NULOS Sempre que existir um valor nulo na tabela, este deve ser tratado, pois toda expressão feita com valor nulo o resultado é nulo. Um valor nulo é diferente de zero. A função NVL converte um valor nulo para outro valor numérico. SQL> SELECT ename, sal, sal * 12 + comm SAL_ANUAL FROM emp; ENAME SAL SAL_ANUAL ----------- --------- --------------- SMITH 800 ALLEN 1600 19500 WARD 1250 15500 JONES 2975 MARTIN 1250 16400 . . . . . . . . . MILLER 1300 SQL> SELECT ename, sal, sal * 12 + NVL(comm,0) SAL_ANUAL FROM emp; ENAME SAL SAL_ANUAL -------------------- --------------- SMITH 800 9600 ALLEN 1600 19500 WARD 1250 15500 JONES 2975 35700 . . . . . . . . . MILLER 1300 15600 A função NVL (comm, 0) substitui por 0 os valores nulos, para que não afetem a soma. Na multiplicação utiliza-se 1 no lugar de um valor nulo para não afete a multiplicação. CLÁUSULA DISTINCT A cláusula distinct é utilizada para eliminar os valores duplicados na saída (no resultado). A) Recuperação não utilizando a cláusula DISTINCT: Obtenha todos os números dos departamentos da tabela EMP. SQL> SELECT deptno FROM emp; DEPTNO -------------- 20 30 30 20 30 30 10 20 10 30 20 30 20 10 14 rows selected. A) Recuperação utilizando a cláusula DISTINCT: Obtenha todos os números dos departamentos da tabela EMP. SQL> SELECT DISTINCT deptno FROM emp; DEPTNO ----------- 10 20 30 DISTINCT pode ser utilizado para múltiplas colunas. SQL> SELECT DISTINCT deptno, job FROM emp; DEPTNO JOB ----------- --------------- 10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANGER 30 SALESMAN 7 rows selected. CLÁUSULA ORDER BY A cláusula ORDER BY é utilizada para ordenar (classificar) as linhas da tabela . A ordem do resultado pode aparecer em ordem decrescente (DESC) ou ascendente (ASC) que é default. A cláusula order by sempre deve aparecer por último no comando select. A) Obtenha o nome, a profissão, o solário de todos os empregados em ordem ascendente de nome do empregado. SQL> SELECT ename, job, sal FROM emp ORDER BY ename; ENAME JOB SAL ----------- ------------------ -------- ADAMS CLERK 1100 ALLEN SALESMAN 1600 BLAKE MANAGER 2850 CLARK MANAGER 2450 FORD ANALYST 3000 JAMES CLERK 950 JONES MANAGER 2975 KING PRESIDENT 5000 MARTIN SALESMAN 1250 MILLER CLERK 1300 SCOTT ANALYST 3000 SMITH CLERK 800 TURNER SALESMAN 1500 WARD SALESMAN 1250 14 rows selected. B) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de nome do empregado. SQL> SELECT ename, job, sal FROM emp ORDER BY ename DESC; ENAME JOB SAL ----------- -------------- --------- WARD SALESMAN 1250 TURNER SALESMAN 1500 SMITH CLERK 800 SCOTT ANALYST 3000 MILLER CLERK 1300 MARTIN SALESMAN 1250 KING PRESIDENT 5000 JONES MANAGER 2975 JAMES CLERK 950 FORD ANALYST 3000 CLARK MANAGER 2450 BLAKE MANAGER 2850 ALLEN SALESMAN 1600 ADAMS CLERK 1100 14 rows selected. C) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de profissão e crescente de salário. SQL> SELECT ename, job, sal FROM emp ORDER BY job DESC, SAL ENAME JOB SAL ----------- --------------- --------- WARD SALESMAN 1250 MARTIN SALESMAN 1500 TURNER SALESMAN 1500 ALLEN SALESMAN 1600 KING PRESIDENT 5000 CLARK MANEGER 2450 BLAKE MANAGER 2850 JONES MANAGER 2975 ADAMS CLERK 1100 MILLER CLERK 1300 SCOTT ANALYST 3000 FORD ANALYST 3000 14 rows selected. D) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de profissão e salário. SQL> SELECT ename, job, sal FROM emp ORDER BY job DESC, sal DESC; ENAME JOB SAL -------------- ---------------- ------- ALLEN SALESMAN 1600 TURNER SALESMAN 1500 WARD SALESMAN 1250 MARTIN SALESMAN 1250 KING PRESIDENT 5000 JONES MANAGER 2975 BLAKE MANEGER 2850 CLARK MANAGER 2450 MILLER CLERK 1300 ADAMS CLERK 1100 JAMES CLERK 950 SMITH CLERK 800 SCOTT ANALYST 3000 FORD ANALYST 3000 14 rows selected. CLÁUSULA WHERE A cláusula WHERE é utilizada para especificar a condição ou condições que as linhas selecionadas devem satisfazer. As condições da cláusula WHERE podem ser valores de colunas, valores literais (seqüência de caracteres), expressões aritméticas ou funções. Seqüência de caracteres e datas devem ser englobadas entre aspas simples. WHERE especifica a condição desejada, que pode assumir os operadores =, !=, >, >=, <, <=; operadores booleanos AND, OR e NOT; e parênteses para indicar uma ordem desejada. OPERADORES LÓGICOS A cláusula WHERE pode assumir os seguintes operadores lógicos: Igual ( = ) Maior ( > ) Maior ou Igual ( >= ) Menor ( < ) Menor ou Igual ( <= ) SQL> SELECT ename, sal, FROM emp WHERE deptno = 10; ENAME SAL -------------- ------- CLARK 2450 KING 5000 MILLER 1300 SQL> SELECT ename, sal, FROM emp WHERE sal > 2000; ENAME SAL ----------- ------ JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000 6 rows selected. OPERADORES BOOLEANOS A cláusula WHERE pode assumir os seguintes operadores booleanos: AND; OR; NOT. AND - As duas condições devem ser satisfeitas para que a consulta retorne uma ou mais linhas da tabela. Sintaxe: SELECT colunas FROM nome-da-tabela WHERE condição1 AND condição2; Recuperação Qualificada: Obtenha os números dos empregados do departamento 20 com salário maior que 1500. SQL> SELECT ename, sal, depto FROM emp WHERE deptno = 20 AND sal > 1500; ENAME SAL DEPTNO ----------- ------ ----------- JONES 2975 20 SCOTT 3000 20 FORD 3000 20 OR - Quando uma ou ambas as condições forem satisfeitas. Sintaxe: SELECT colunas FROM nome-da-tabela WHERE condição1 OR condição2; Obtenha os números dos empregados do departamento 20 ou que possuem salário maior que 1500. SQL> SELECT ename, sal, depto FROM emp WHERE deptno = 20 OR sal > 1500; ENAME SAL DEPTNO ----------- --------- ----------- SMITH 800 20 ALLEN 1600 30 JONES 2975 20 BLAKE 2850 30 CLARK 2450 10 SCOTT 3000 20 KING 5000 10 ADAMS 1100 20 FORD 3000 20 9 rows selected. USANDO AND E OR NA MESMA CONSULTA Ao usar AND e OR na mesma consulta, você geralmente deve usar parêntese para tornar a consulta mais clara. A colocação dos parênteses pode alterar completamente o resultado. SQL> SELECT ename, job, hiredate, sal, deptno FROM emp WHERE sal > 500 AND job ='CLERK' AND (hiredate='03-DEC-81' OR hiredate='17-DEC-80); ENAME JOB HIREDATE SAL DEPTNO ---------- --------- -------------- ------ ----------- SMITH CLERK 17-DEC-80 800 20 JAMES CLERK 03-DEC-81 950 30 2 rows selected. SQL> SELECT ename, job, hiredate, sal, deptno FROM emp WHERE sal > 500 AND job = 'CLERK' OR (hiredate = '03-DEC-81' AND hiredate = '17-DEC-80'); ENAME JOB HIREDATE SAL DEPTNO ---------- --------- ------------- ------ ----------- SMITH CLERK 17-DEC-80 800 20 ADAMS CLERK 12-JAN-83 1100 20 JAMES CLERK 03-DEC-81 950 30 MILLER CLERK 23-JAN-82 1300 10 OPERADORES SQL A) Operador BETWEEN ...AND ... O operador BETWEEN..AND... testa os valores da coluna, e recupera os valores que estão entre um valor mínimo e um valor máximo inclusive especificados no BETWEEN. Obtenha os nomes e salários dos empregados que possuírem salários entre os valores 1000 e 2000. SQL> SELECT ename, sal FROM emp WHERE sal BETWEEN 1000 AND 2000; ENAME SAL ----------- ------- ALLEN 1600 WARD 1250 MARTIN 1250 TURNER 1500 ADAMS 1100 MILLER 1300 6 rows selected. B) Operador INO operador IN testa os valores da coluna, e recupera somente os valores que estão na lista de valores especificados pelo operador IN. Obtenha os números, os salários e os gerentes dos empregados que possuírem os gerentes com os números 7902, 7566, 7788. SQL> SELECT empno, sal, mgr FROM emp WHERE mgr in (7902, 7566, 7788); EMPNO SAL MGR ---------- ------ ------- 7369 800 7902 7788 3000 7566 7876 1100 7788 7902 3000 7566 4 rows selected. Observação Caso sejam usados caracteres ou data na lista, especificá-los entre aspas simples. C) Operador LIKE O operador LIKE permite recuperar linha da tabela sem saber exatamente o valor que está sendo procurado, utilizando para isso os símbolos (%) e ( _ ). O símbolo de sublinhado ( _ ) é usado para representar um único caractere. O símbolo de percentagem (%) é usado para representar uma cadeia de caracteres de qualquer tamanho (inclusive zero). Obtenha os nomes dos empregados que iniciam seu nome com S. SQL> SELECT ename FROM emp WHERE ename LIKE 'S%'; ENAME ------------------------------ SMITH SCOTT 2 rows selected. Observação O LIKE só pode ser usado com cadeia de caracteres ou com dados gráficos, e não com dados numéricos. D) Operador IS NULL O operador IS NULL testa os valores da coluna, e recupera somente as linhas da tabela que possuírem o valor null. Obtenha os números dos empregados que não possuírem gerente, ou seja o campo número do gerente (mgr) é igual a nulo. SQL> SELECT ename, mgr FROM emp WHERE mgr IS NULL; ENAME MGR -------------- ------- KING E) Utilizando a negação (NOT) Os operadores BETWEEN...AND..., IN, LIKE, e IS NULL podem ser combinados com o operador de negação NOT. Operador Significado NOT BETWEEN NÃO ENTRE DOIS VALORES ESPECIFICADOS NOT IN NÃO ENTRE UMA LISTA DE VALORES NOT LIKE NÃO ENTRE UM PADRÃO ESPECIFICADO IS NOT NULL NÃO IGUAL AO VALOR NULL FUNÇÕES INTEGRADAS NO SQL As funções são utilizadas para manipular campos (itens de dados). Estas funções utilizam um ou mais argumentos, retornando um valor. Um argumento pode ser uma constante, variável ou uma coluna. FORMATO function_name(argumento1, argumento2, ...) A) FUNÇÕES PARA MANIPULAR CARACTERES a.1) LOWER Converte em letras minúsculas. Exemplo: Obtenha o nome de todos os departamentos em letras minúsculas. SQL> SELECT LOWER(dname) FROM dept; a.2) UPPER Converte em letras maiúsculas. Exemplo: Obtenha o nome de todos os departamentos em letras maiúsculas. SQL> SELECT UPPER(dname) FROM dept; a.3) INITCAP Converte a primeira letra de cada palavra para maiúscula. Exempo: Obtenha o nome e a localização de todos os departamentos, mostrando a primeira letra do nome e localização em maiúscula. SQL> SELECT INITCAP(dname), INITCAP(LOC) FROM dept; a.4) LENGTH Retorna o número de caracteres ou dígitos de uma coluna ou de uma cadeia de caracteres (literal). Exemplo Obtenha o número de caracteres do literal 'SQL COURSE', das colunas deptno e dname. SQL> SELECT LENGTH('SQL COURSE'), LENGTH(deptno), LENGTH(dname) FROM dept; B) FUNÇÕES PARA MANIPULAR NÚMEROS b.1) ROUND Retorna o arredondamento de colunas, expressões ou valores para n casas decimais. Se n é omitido, nenhuma casa decimal é colocada. Exemplo: SQL> SELECT ROUND(45.923,1), ROUND(45.923), ROUND(SAL/32,2) FROM emp WHERE deptno = 10; SQL>SELECT ROUND(45.473), ROUND(45.4634,2) FROM dual; b.2) TRUNC Retorna o truncamento de colunas, expressões ou valores em n casas decimais. Se n é omitido nenhuma casa decimal é truncada. Exemplo: SQL> SELECT TRUNC(45.923, 1), TRUNC(45.923), TRUNC(45.452), TRUNC(SAL/32,2) FROM emp WHERE deptno = 10; b.3) SQRT Retorna a raiz quadrada de uma coluna ou valor. Se a coluna ou valor forem menores que zero, então retorna um valor nulo. Exemplo: SQL> SELECT sal, SQRT(sal), comm, SQRT(comm), SQRT(144) FROM emp WHERE comm > 0; b.4) SIGN Retorna -1 se a coluna, ou expressão, ou valor forem negativos; Retorna 0 se a coluna, ou expressão, ou valor forem zero; Retorna 1 se a coluna, ou expressão, ou valor forem positivos; Exemplo: SQL> SELECT sal - comm, sign(sal - comm), comm - sal, sign(comm - sal) FROM emp WHERE deptno = 30; b.5) ABS Retorna o valor absoluto de uma coluna ou valor. Exemplo: SQL> SELECT sal, comm, comm-sal, ABS(com-sal), ABS(-35) FROM emp WHERE comm > 0; b.6) MOD (valor1, valor2) Retorna o resto da divisão do valor1, dividido pelo valor2. Exemplo: SQL> SELECT sal, comm, MOD(sal, comm), MOD(100,40) FROM emp WHERE deptno=30 ORDER BY comm; C) FUNÇÕES PARA MANIPULAR DATAS c.1) SYSDATE Retorna a data e hora corrente. Exemplo: Obtenha a data corrente: SQL> SELECT SYSDATE FROM dual; OPERAÇÕES ARITMÉTICAS É possível adicionar e subtrair números constantes de datas. DATA + Número de dias DATA - Número de dias DATA - DATA DATA + Número/24 Exemplo: SQL> SELECT hiredate, hiredate+7, hiredate -7, sysdate - hiredate FROM emp WHERE hiredate LIKE '%JUN%'; SQL> SELECT ename, hiredate FROM emp WHERE ename='TURNER'; SQL> SELECT SYSDATE - hiredate FROM emp WHERE ename='TURNER'; c.2) MONTHS_BETWEEN Retorna o número de meses entre duas datas. Exemplo: SQL> SELECT MONTHS_BETWEEN (SYSDATE, hiredate) MONTHS_BETWEEN('01-JAN-84','05-NOV-88') FROM emp WHERE MONTHS_BETWEEN (SYSDATE, hiredate) > 160; SQL> SELECT MONTHS_BETWEEN('26-AUG-94','26-AUG-94') FROM dual; c.3) ADD_MONTHS Adiciona n número de meses na data, onde n deve ser um inteiro e pode ser negativo. Exemplo: SQL> SELECT hiredate ADD_MONTHS(hiredate,3), ADD_MONTHS(hiredate,-3) FROM emp WHERE deptno = 20; c.4) NEXT_DAY Retorna a data do próximo dia da semana informado. Exemplo: SQL> SELECT hiredate, NEXT_DAY(hiredate, 'FRIDAY'), NEXT_DAY(hiredate, 6) FROM emp WHERE deptno = 10; c.5) LAST_DAY Retorna a data do último dia do mês. Exemplo: SQL> SELECT SYSDATE, LAST_DAY(SYSDATE), hiredate, LAST_DAY(hiredate) FROM emp WHERE deptno = 20; D) FUNÇÕES DE CONVERSÃO d.1) TO_CHAR Converte um formato de data default para um formato alternativo especificado. TO_CHAR(date, 'date picture') default format date = DD-MON-YY Formatos de datas (date picture) Date picture Significado YYYY Ano Y, YYY Ano com vírgula MM Mês MONTH Nome do mês MON Nome do mês com abreviação WW ou W Mês do ano ou mês DDD ou DD ou D Dia do ano, mês ou semana DAY Nome do dia da semana HH ou HH12 Hora do dia (1 - 12) HH24 Hora do dia (0 - 23) MI Minuto SS Segundos Sufixos TH Número DDTH 14TH Exemplo: SQL> SELECT SYSDATE FROM dual; TO_CHAR também é utilizado para converter tipo de dado NUMBER para tipo de dado CHAR (novo formato). TO_CHAR (number, 'number picture') FORMATO DE NÚMEROS (Number picture) Number Picture Significado Exemplo 9 Numérico (o número de 9's determina o tamanho) 999999 = 1234 0 Mostra zero à esquerda 099999 = 001234 $ Sinal de Dólar $999999 = $1234 .(ponto) Especifica posição do ponto decimal 999999.99 = 1234.00 , (vírgula) Especifica posição da vírgula 999,999 = 1,234 d.2) TO_NUMBER Converte o número armazenado para um tipo de dado NUMBER. Exemplo: SQL> SELECT ename, job, sal FROM emp WHERE sal > TO_NUMBER('1500'); d.3) TO_DATE Permite formatar para outros formatos de datas. Exemplo: SQL> SELECT ename, hiredate FROM emp WHERE hiredate = TO_DATE('JUNE 9, 1981', 'MONTH DD, YYYY'); Obs.: O formato default da data é: DD-MON-YY Exemplo: hiredate = 09-JUN-81. d.4) NVL Converte um valor nulo para umvalor especificado. Exemplo: SQL> SELECT ename, sal comm, sal * 12 + NVL(comm, 0), NVL(comm, 1000) FROM emp WHERE deptno = 30; d.5) GREATEST Retorna o maior valor de uma lista de valores. Exemplo: SQL> SELECT ename, sal, comm, GREATEST(sal, comm), GREATEST(1000,2000) FROM emp WHERE deptno = 30; d.6) LEAST Retorna o menor valor de uma lista de valores. Exemplo SQL> SELECT ename, sal, comm, LEAST(sal, comm), LEAST(1000, 2000) FROM emp WHERE deptno = 30; d.7) VSIZE Retorna o número de bytes que são utilizados para armazenar internamente os dados (colunas ou valores) Exemplo: SQL> SELECT hiredate, VSIZE(hiredate), sal, VSIZE(sal), ename, VSIZE(ename) FROM emp WHERE deptno = 30; E) FUNÇÕES PARA MANIPULAR GRUPOS As funções de grupos servem para manipular um conjunto de linhas (ROWS) da tabela. Retornam um resultado baseado num grupo de linhas (ROWS). Por default todas as linhas (ROWS) da tabela são tratadas com um grupo. A cláusula GROUP BY é usada para dividir as linhas da tabela em grupos menores. FUNÇÕES DE GRUPO e.1) AVG Retorna a média dos valores da coluna, ignorando valores nulos. Exemplo: Obtenha a média de salários de todos os empregados. SQL> SELECT ename, sal, AVG(sal) FROM emp; SQL> SELECT AVG(sal ) FROM emp; e.2) COUNT Retorna o número de ocorrências da expressão, ou coluna. Usando *, retorna todas as ocorrências das linhas incluindo, duplicadas e nulas. Exemplo: Obtenha o número de empregados da empresa SQL> SELCT COUNT(*) FROM emp; Exemplo: Obtenha o número de empregados do departamento 10 SQL> SELECT COUNT(*) WHERE deptno = 10; e.3) SUM Retorna a soma dos valores da coluna, ignorando valores nulos. Exemplo: Obtenha a soma de todos os salários dos empregados. SQL> SELECT SUM(sal) FROM emp; Exemplo: Obtenha a soma de todos os salários do departamento 10. SQL> SELECT SUM(sal) FROM emp WHERE deptno = 10; e.4) MAX Retorna o valor máximo de uma coluna. Exemplo: Obtenha o maior salário dos empregados. SQL> SELECT MAX(sal) FROM emp; Exemplo: Obtenha o maior salário do departamento 20. SQL> SELECT MAX(sal) FROM emp WHERE deptno = 20; e.5) MIN Retorna o valor mínimo de uma coluna. Exemplo: Obtenha o menor salário dos empregados. SQL> SELECT MIN(sal) FROM emp; Exemplo: Obtenha o menor salário do departamento 10. SQL> SELECT MIN(sal) FROM emp WHERE deptno = 10; CLÁUSULA GROUP BY A cláusula GROUP BY referencia uma coluna especificada da tabela relacionada na cláusula FROM e agrupa as linhas com base nos valores iguais dessa coluna. O resultado da cláusula GROUP BY particiona a tabela em um conjunto de grupos, não efetuando qualquer tipo de ordenação. Exemplo: Obtenha o maior salário de cada profissão. SQL> SELECT job, MAX(sal ) FROM emp GROUP BY job; Exemplo: Obtenha o menor salário de cada profissão. SQL> SELECT job, MIN(sal) FROM emp GROUP BY job; Exemplo: Obtenha o maior salário da profissão CLERK. SQL> SELECT MAX(sal) WHERE job = 'CLERK' GROUP BY job; Exemplo: Obtenha o menor salário da profissão CLERK. SQL> SELECT MIN(sal) WHERE job = 'CLERK' GROUP BY job; Exemplo: Obtenha a média salarial por departamento. SQL> SELECT deptno, AVG(sal) FROM emp GROUP BY deptno; Observação: a cláusula WHERE só pode ser usada em funções de grupos em casos especiais CLÁUSULA HAVING A cláusula HAVING especifica uma restrição sobre a tabela agrupada que resultou de uma cláusula GROUP BY anterior e elimina os grupos que não satisfazem a condição estabelecida. A cláusula HAVING é usada para especificar a qualidade que um grupo deve ter para ser incluído no resultado. Ela efetua para os grupos a mesma função que a cláusula WHERE efetua para as linhas. A cláusula HAVING é sempre utilizada junto com a cláusula GROUP BY, sendo que o HAVING é especificado sempre após o GROUP BY. Exemplo: Obtenha a média de salário dos departamentos que possuem mais de 3 empregados. SQL> SELECT deptno, AVG(sal) FROM emp HAVING COUNT(*) > 3; Exemplo: Obtenha as profissões que o maior salário é igual ou maior que 3000. SQL> SELECT job, MAX(sal) FROM emp HAVING MAX(sal) >= 3000 GROUP BY job; SUBQUERIES (SUBCONSULTAS) Uma subquery é uma cláusula SELECT aninhada com outra cláusula SELECT e que retorna um resultado intermediário. Exemplo: SELECT coluna1, coluna2, .......... FROM tabela WHERE coluna = (SELECT coluna1, coluna2,........ FROM tabela WHERE condição); A) SUBQUERY DE UMA LINHA Exemplo: Obtenha o nome, profissão e salário do empregado que possui o menor salário. SQL> SELECT MIN(sal) FROM emp; {recupera o menor salário de todos os empregados} SQL> SELECT ename, job, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp) {o resultado acima é usado como parâmetro nesta query} Como as subqueries aninhadas são processadas Uma subquery é composta por duas cláusulas SELECT, o SELECT principal e SELECT interno. O comando SELECT interno é executado primeiro, produzindo um resultado. No exemplo acima o resultado é 800. O SELECT principal é processado usando o valor retornado pelo SELECT interno. Observação: sempre que o SELECT interno retorna uma linha da tabela podem ser utilizadas os operadores =, !=, <, <=, >, >=. Exemplo: Obtenha o nome, profissão, de todos os empregados que possuem a mesma profissão do empregado BLAKE. SQL> SELECT job FROM emp WHERE ename = 'BLAKE'; SQL> SELECT ename, job FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'BLAKE'); Observação: O SELECT interno retorna a profissão de BLAKE que é MANAGER, e o SELECT externo retorna o nome dos empregados com profissão de MANAGER. B) SUBQUERIES QUE RETORNAM MAIS DE UMA LINHA DA TABELA b.1) OPERADOR IN Relembrando, o operador IN testa (verifica) os valores que estão em uma lista de valores. Exemplo: Obtenha o nome, salário e número do departamento dos empregados que recebem o menor salário de cada empregado. SQL> SELECT MIN(sal) FROM emp GROUP BY deptno; SQL> SELECT ename, sal, deptno FROM emp WHERE sal in (SELECT MIN(sal) FROM emp GROUP BY deptno) Observação: Sempre que aparecer uma SUBQUERY, onde, no resultado retornam mais de uma linha da tabela, utiliza-se o operador IN. b.2) OPERADORES ANY E ALL Os operadores ANY e ALL são utilizados para subqueries que retornam mais de uma linha da tabela, e requerem o uso dos operadores igual ( = ), menor ( > ), maior ( < ), menor ou igual (>= ), maior ou igual ( <= ) ou diferente ( !=), aplicado ao resultado de uma subconsulta. ANY Compara um valor com cada valor da lista de valores que foi retornado pela subquery. Exemplo: Obtenha o nome, salário, profissão e número do departamento de todos empregados que recebem um salário maior que o menor salário dos empregados do departamento 30. SQL> SELECT DISTINCT sal FROM emp WHERE deptno = 30; SQL> SELECT ename, sal, job, deptno FROM emp WHERE sal > ANY (SELECT DISTINCT sal FROM emp WHERE deptno = 30); Observação: Quando a ANY é utilizado, frequentemente a cláusula DISTINCT é utilizada para prevenir que a mesma linha seja selecionada várias vezes. ALL Compara um valor com todos os valores da lista de valores retornados pela subquery. Exemplo: Obtenha o nome, salário, profissão e número do departamento de todos os empregados que recebem um salário maior, que todos os salários dos empregados do departamento 30. SQL> SELECT DISTINCT sal FROM emp WHERE deptno = 30; SQL> SELECT ename, sal, job, deptno FROM emp WHERE sal > ALL (SELECT DISTINCT sal FROM emp WHERE deptno = 30); b.3) OPERADOR NOT O operador NOT também pode utilizado com os operadores IN, ALL e ANY. Utilização do ORDER BY Não é possível utilizar o ORDERBY em um SELECT interno, a regra é utilizar somente um ORDER BY em uma subquery e este deve ser o último comando do SELECT, ou seja no SELECT mais externo. Aninhamento de subqueries Também é possível aninhar (encadear) várias queries uma dentro da outra, não existindo limites. C) CLÁUSULA HAVING COM SUBQUERIES ANINHADAS A cláusula WHERE refere-se a uma linha da tabela e o HAVING a um grupo de linhas especificadas na cláusula GROUP BY. Exemplo: Obtenha o número dos departamentos, onde os empregados recebem um salário médio maior que a média salarial do departamento 30. SQL> SELECT AVG(sal) FROM emp WHERE deptno = 30; SQL> SELECT deptno, AVG(sal) FROM emp HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno = 30) GROUP BY deptno; Exemplo: Obtenha a profissão dos empregados que recebem a mais alta média salarial. SQL> SELECT MAX(AVG(sal)) FROM emp GROUP BY job; SQL> SELECT job, AVG(sal) FROM emp GROUP BY job HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp GROUP BY job); Observação: O SELECT interno encontra a média salarial par aos grupos de profissões, e a função MAX encontra a maior média salarial dos grupos, que é o valor (5000), este é utilizado pela cláusula HAVING. A cláusula GROUP BY é necessária no SELECT principal para agrupar as profissões. EXTRAIR DADOS DE MAIS DE UMA TABELA Sempre que é necessário extrair dados de mais de uma tabela um JOIN é feito, também conhecido como ENCADEAMENTO. A finalidade do JOIN é obter informações que não estão em uma única tabela, e é especificado utilizando-se a cláusula WHERE. SELECT coluna1, coluna2,....FROM tabela1, tabela2,...... WHERE condição de join; JOIN-EQUI-JOIN (SIMÉTRICO) O JOIN-EQUI-JOIN ocorre quando a condição de JOIN compara a igualdade entre as colunas da tabela 1 as colunas da tabela 2. Podemos então observar que o relacionamento entre as duas tabelas (emp e dept) é uma condição de JOIN "EQUI-JOIN", pois os valores da coluna deptno em ambas as tabelas são iguais e o operador igual ( = ) é utilizado. Exemplo: Obtenha o nome e a profissão de todos os empregados e o nome dos departamentos em que os empregados estão lotados. Para resolver este exemplo, é necessário comparar (condição de join) o valor da coluna deptno da tabela emp cpm o mesmo valor da coluna deptno da tabela dept, extraindo os nomes dos departamentos. SQL> SELECT ename, job, dname FROM emp, dept WHERE emp.deptno=dept.deptno; APELIDOS (ALIAS) PARA TABELAS Para criar um apelido para uma tabela, defina-o na cláusula FROM. O apelido pode então ser utilizado como qualificador tanto na cláusula WHERE, quanto na cláusula SELECT. Os apelidos também são chamados de LABELS TEMPORÁRIOS, e podem ter até 30 caracteres, mas quanto menos melhor. Exemplo: SQL> SELECT ename, job, D.deptno, dname FROM emp E, dept D WHERE E.deptno = D.deptno ORDER BY D.deptno; OUTRO MÉTODO DE FAZER JOIN Fazendo um join da tabela com ela mesma. SQL> SELECT E.name emp_name, E.sal emp_sal, M.ename mgr_name, M.sal mgr_sal FROM emp E, emp M WHERE E.mgr = M.empno AND E.sal < M.sal; Observação: Os apelidos E e M para a tabela emp significa que, E são os empregados e M são os gerentes. OPERADORES DE CONJUNTOS (SET OPERATORS) UNION A união de duas relações é o conjunto de todas as linhas que estão em uma ou outra relação, ignorando as duplicada, ou seja, retorna a união de dois SELECTs, ignorando as linhas duplicadas. SQL> SELECT job FROM emp WHERE deptno = 10 UNION SELECT job FROM emp WHERE deptno = 30; INTERSECT A interseção é o conjunto de todas as linhas que estão simultaneamente em ambas as relações, ou seja, retorna a interseção de dois SELECTs. SQL> SELECT job FROM emp WHERE deptno = 10 INTERSECT SELECT job FROM emp deptno = 30; MINUS A diferença é o conjunto de todas as linhas que estão em apenas uma das relações, ou seja, retornam a subtração de dois SELECTs. SQL> SELECT job FROM emp WHERE deptno = 10 MINUS SELECT job FROM emp WHERE deptno = 30; REGRAS PARA UTILIZAR UNION, INTERSECT e MINUS A cláusula SELECT deve selecionar o mesmo número de colunas. As colunas correspondentes devem ser o mesmo tipo de dado. As linhas duplicadas são automaticamente eliminados. Os nomes das colunas do primeiro SELECT é que aparecem no resultado. A cláusula ORDER BY deve aparecer no final do comando. A cláusula ORDER BY somente pode ser usada indicando o número da coluna. Os operadores UNION, ITERSECT e MINUS podem ser utilizados em subqueries. As colunas SELECTs são executadas de cima para baixo. Vários SET OPERATORS podem ser utilizados. Para indicar a seqüência de execução deve-se utilizar-se parêntese. LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL) CRIAR UMA TABELA Para definir e criar uma tabela o comando CREATE TABLE é utilizado com a seguinte sintaxe: CREATE TABLE nome-da-tabela ( (nome_coluna1 tipo_dado(tamanho)) [NULL/NOT NULL], (nome_coluna2 tipo_dado(tamanho)) [NULL/NOT NULL], (nome_coluna3 tipo_dado(tamanho)) [NULL/NOT NULL], ............................ (nome_colunan tipo_dado(tamanho)) [NULL/NOT NULL] ) Se for especificado a restrição NOT NULL, cada linha deve ter valor diferente de NULL para esta coluna. Se não for especificado nada, a opção NULL default é utilizada. Exemplo: CREATE TABLE dept ( deptno NUMBER(2) not null, dname CHAR(12), loc CHAR(12) ) CRIAR UMA TABELA UTILIZANDO A ESTRUTURA DE OUTRA Utilizando o CREATE TABLE e SELECT, podemos criar uma tabela utilizando a estrutura de outra tabela e ao mesmo tempo carregar a nova tabela com dados, conforme a condição especificada. CREATE TABLE nome_tabela [(nome_coluna1 tipo_dado(tamanho) NULL/NOT NULL], (nome_coluna2 tipo_dado(tamanho) NULL/NOT NULL, .................... (nome_colunan tipo_dado(tamanho) NULL/NOT NULL], AS SELECT comando_select; A tabela será criada com as colunas e com as linhas especificadas no SELECT; Caso sejam definidos as colunas no CREATE TABLE, o número de colunas deve ser igual as do comando SELECT. Exemplo1: Criar uma tabela com as colunas número, nome, profissão e salário dos empregados do departamento 30. CREATE TABLE TAB30 AS SELECT empno, ename, job, sal FROM emp WHERE deptno = 30; Exemplo 2: Criar uma tabela com as colunas nome, salário e grade salarial de todos os empregados. CREATE TABLE EMP_SALS(nome, salario, gradesal) AS SELECT ename, sal, grade FROM emp WHERE emp.sal BETWEEN losal AND hisal; DATA MANIPULATION LANGUAGE – DML A DML é um grupo de comandos SQL utilizados para INSERIR novas linhas, ALTERAR linhas e EXCLUIR linhas. A) INSERIR LINHAS NA TABELA O comando INSERT é utilizado para inserir novas linhas (ou ROWS) na tabela. INSERT INTO nome_da_tabela(nome_coluna1, nome_coluna2, ............) VALUES(valor1, valor2, .....................................); Exemplo: Inserir o departamento número 50, com nome MARKETING e localização SAN JOSÉ. INSERT INTO dept(depto, dname, loc) VALUES(50, 'MARKETING','SAN JOSÉ'); Observação Colunas com tipo de dado CHAR e DATE, devem ser colocadas entre aspas simples. Para inserir uma data ou hora em outro formato utiliza-se a função TO_DATE. A.1) UTILIZANDO SUBSTITUIÇÃO DE VARIÁVEIS INSERT INTO dept(deptno, dname, loc) VALUES(&dept_number, '&dept_name','&location'); Observação: Sempre que o INSERT é executado, um prompt com o nome da variável é mostrado. A.2) COPIAR LINHAS (ROWS) DE OUTRA TABELA INSERT INTO nome_tabela[(coluna1, coluna2,............)] SELECT comando_select; Exemplo: INSERT INTO tabd10(empno, ename, sal, job, hiredate) SELECT empno, ename, sal, job, hiredate FROM emp WHERE deptno = 10; Observação: A tabela TABD10 deve ser criada antes. B) ALTERAR LINHAS DA TABELA - SINTAXE O comando UPDATE é utilizado para alterarvalores das linhas da tabela. UPDATE nome_tabela SET nome_coluna1 = valor/expressão [ , nome_coluna2 = valor/expressão, ...........] WHERE condição; Exemplo: Alterar a profissão para vendedor e aumentar o salário em 10% para o empregado com nome SCOTT. UPDATE emp SET job = 'VENDEDOR' sal = sal *1.1 WHERE ename = 'SCOTT'; C) EXCLUIR LINHAS DA TABELA - SINTAXE O comando DELETE é utilizado para excluir/remover uma ou mais linhas da tabela. DELETE FROM nome_tabela WHERE condição; Exemplo: Excluir todos os empregados que pertencem ao departamento 10. DELETE FORM emp WHERE deptno = 10; Observação: Cuidado - se WHERE for omitido, todas as linhas da tabela são excluídas.
Compartilhar