Conceitos básicos de SQL e ORACLE SQL* PLUS

1. Conceitos básicos de SQL e ORACLE SQL* PLUS

2. O Comando SELECT SELECT [DISTINCT] {*, column [alias],...} FROM table; • SELECT identifica as colunas • FROM identifica as tabelas

3. SQL> SELECT depto_num, depto_loc 2 FROM depto; SQL> SELECT enome, sal, sal+300 2 FROM emp; SQL> SELECT enome, sal, 12*sal+100 2 FROM emp; Selecionando linhas SQL> SELECT * 2 FROM depto;

4. Nome Salario Anual ------------- -------------... Definindo Alias para Colunas SQL> SELECT enome AS nome, sal AS salario 2 FROM emp; NOME SALARIO ------------- ---------... SQL> SELECT enome "Nome", 2 sal*12 “Salario Anual" 3 FROM emp;

5. Eliminando Linhas Duplicadas • DISTINCT SQL> SELECT DISTINCT depto_num 2 FROM emp; DEPTO_NUM --------- 10 20 30

6. SQL*Plus

7. SQL Plus • Aplicação cliente para conexão com Oracle. • Localização: • Start  Programs  Banco de dados  Oracle OraHome81  Application Development  SQL Plus • Usuário e senha são idênticos

8. Interface gráfica do SQL Plus

9. Configurações • Opcoes ambiente • Linesize – modifica a quantidade de caracteres por linha. Escolher personalizado. Escolhar ativar. Especificar 1000 caracteres. Aumentar largura do buffer para 1000.

10. Listando a Estrutura de Tabelas SQL> DESCRIBE depto Name Null? Type ----------------- -------- ------------ DEPTO_NUM NOT NULL NUMBER(2) DEPTO_NOM VARCHAR2(14) DEPTO_LOC VARCHAR2(13)

11. Executando um script no SQL Plus @caminho_completo

12. Restringindo consultas e ordenando resultados

13. Utilizando a Cláusula WHERE SQL> SELECT enome, cargo, depto_num 2 FROM emp 3 WHERE cargo='CAIXA'; ENOME CARGO DEPTO_NUM ---------- --------- --------- RONALDO CAIXA 30 MANUEL CAIXA 20 PAULO CAIXA 20 LUCIANO CAIXA 10

14. Operadores de Comparação Operador = > >= < <= <> Significado Igual a Maior que Maior ou igual a Menor que Menor ou igual a Diferente de

15. Outros Operadores Operador BETWEEN...AND... IN(lista) LIKE IS NULL Significado Entre dois valores (inclusive) Satisfaz uma lista de valores Satisfaz um padrão de caracteres É um valor nulo (null)

16. limite inferior limite superior Operador BETWEEN SQL> SELECT enome, sal 2 FROM emp 3 WHERE sal BETWEEN 1000 AND 1500; ENOME SAL ---------- --------- MARIA 1250 SERGIO 1500 MATHEUS 1250 PAULO 1100 LUCIANO 1300

17. Operador IN SQL> SELECT enum, enome, sal, ger 2 FROM emp 3 WHERE ger IN (7902, 7566, 7788); ENUM ENOME SAL GER --------- ---------- --------- --------- 7902 JOSE 3000 7566 7369 MANUEL 800 7902 7788 FABIO 3000 7566 7876 PAULO 1100 7788

18. Operador LIKE • Utilize o operador LIKE para realizar pesquisas por padrões (wildcards). • % substitui zero ou mais caracteres • _ substitui um único caracter SQL> SELECT enome 2 FROM emp 3 WHERE enome LIKE ‘M%';

19. Operador IS NULL • Testando valores nulos (null) SQL> SELECT enome, ger 2 FROM emp 3 WHERE ger IS NULL; ENOME GER ---------- --------- CARLOS

20. Operadores Lógicos Operador ANDOR NOT Significado Retorna TRUE se a condição de ambos os componentes for TRUE Retorna TRUE se a condição de um dos componentes for TRUE Retorna TRUE se a condição for FALSE (vise-versa)

21. Operador NOT SQL> SELECT enome, cargo 2 FROM emp 3 WHERE cargo NOT IN('CAIXA','GERENTE','ANALISTA'); ENOME CARGO ---------- --------- CARLOS PRESIDENTE MARIA VENDEDOR CELSO VENDEDOR SERGIO VENDEDOR MATHEUS VENDEDOR

22. Cláusula ORDER BY SQL> SELECT enome, cargo, depto_num, dtinicio 2 FROM emp 3 ORDER BY dtinicio DESC; ENOME CARGO DEPTO_NUM DTINICIO ---------- --------- --------- --------- PAULO CAIXA 20 12-JAN-83 FABIO ANALISTA 20 09-DEC-82 LUCIANO CAIXA 10 23-JAN-82 RONALDO CAIXA 30 03-DEC-81 JOSE ANALISTA 20 03-DEC-81 CARLOS PRESIDENTE 10 17-NOV-81 MARIA VENDEDOR 30 28-SEP-81 ... 14 rows selected.

23. Manipulando dados

24. O Comando INSERT • Adicione linhas a uma tabela utilizando o comando INSERT. • O comando INSERT insere apenas uma linha por vez. Não esqueça o COMMIT INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

25. Inserindo Novas Linhas • Insira uma nova linha informando os valores para cada coluna da tabela. • Liste os valores na mesma ordem das colunas na tabela. • Opcionalmente, liste as colunas na cláusula do INSERT. • Strings e datas devem ser informando entre aspas simples. SQL> INSERT INTO depto (depto_num, depto_nome, depto_loc) 2 VALUES (50, 'DESENVOLVIMENO', ‘RECIFE'); 1 row created.

26. Inserindo Linhas com Nulls • Implicitamente: omita o nome da coluna da lista de colunas. SQL> INSERT INTO depto (depto_num, depto_nome ) 2 VALUES (60, ‘REC HUMANOS'); 1 row created. • Explicitamente: especifique o valor NULL. SQL> INSERT INTO depto 2 VALUES (70, ‘JURIDICO', NULL); 1 row created.

27. Inserindo Data e Hora do Sistema • A função SYSDATE informa a data e a hora corrente. SQL> INSERT INTO emp (enum, enome, cargo, 2 ger, dtinicio, sal, comis, 3 depto_num) 4 VALUES (7196, ‘ANTONIO', ‘VENDEDOR', 5 7782, SYSDATE, 2000, NULL, 6 10); 1 row created.

28. Inserindo Datas • Adicionando um novo empregado. SQL> INSERT INTO emp 2 VALUES (2296,'ROMANO',‘VENDEDOR',7782, 3 TO_DATE(‘03-02-1997','DD-MM-YYYY'), 4 1300, NULL, 10); 1 row created. • Verificando a data de admissão. ENUM ENOME CARGO GER DTINICIO SAL COMIS DEPTO_NUM ---- ------- -------- ---- --------- ---- ----- --------- 2296 ROMANO VENDEDOR 7782 03-FEB-97 1300 10

29. O Comando UPDATE • Modificando linhas existentes com o comando UPDATE. • Modifique mais de uma linha por vez especificando uma condição na cláusula WHERE. Não esqueça o COMMIT UPDATE table SET column = value [, column = value] [WHERE condition];

30. Atualizandolinhas em uma tabela • Linhas específicas podem ser modificadas utilizando a cláusula WHERE. • Todas as linhas da tabela são modificadas se a cláusula WHERE for omitida. SQL> UPDATE emp 2 SET depto_num = 20 3 WHERE enum = 7782; 1 row updated. SQL> UPDATE emp 2 SET depto_num = 20; 14 rows updated.

31. Eliminando Linhas de uma Tabela • Linhas específicas podem ser eliminadas utilizando a cláusula WHERE. • Todas as linhas da tabela são eliminadas se a cláusula WHERE for omitida. SQL> DELETE FROM depto 2 WHERE depto_nome = 'DESENVOLVIMENTO'; 1 row deleted. SQL> DELETE FROM depto; 4 rows deleted.

32. Exibindo dados de múltiplas tabelas

33. Joins • Utilize uma junção para consultar dados de mais de uma tabela. • Especifique a condição de junção na cláusula WHERE. • Informe o nome da tabela junto com o nome da coluna, se tabelas diferentes possuírem colunas com os mesmos nomes. SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;

34. Criando Alias para Tabelas SQL> SELECT emp.enum, emp.enome, emp.depto_num, 2 depto.depto_num, depto.depto_loc 3 FROM emp, depto 4 WHERE emp.depto_num = depto.depto_num; SQL> SELECT e.enum, e.enome, e.depto_num, 2 d.depto_num, d.depto_loc 3 FROM emp e, depto d 4 WHERE e.depto_num = d.depto_num;

35. Funções de Grupo(Agregação)

36. O que são Funções de Grupo? • Funções de grupo operam em conjuntos de linhas, produzindo um resultado por grupo. EMP DEPTO_NUM SAL --------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 MAX(SAL) --------- 5000 “o maior salário da tabela EMP”

37. Funções AVG e SUM • Utilize o AVG e SUM apenas para dados numéricos SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE cargo LIKE ‘VEND%'; AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) -------- --------- --------- --------- 1400 1600 1250 5600

38. Funções MIN e MAX • Utilize MIN e MAX para qualquer tipo de dado SQL> SELECT MIN(dtinicio), MAX(dtinicio) 2 FROM emp; MIN(DTINI MAX(DTINI --------- --------- 17-DEZ-80 12-JAN-83

39. Função COUNT • COUNT(*) retorna o número de linhas na tabela SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE depto_num = 30; COUNT(*) --------- 6

40. Função COUNT • COUNT(coluna) retorna o número de linhas não nulas da tabela SQL> SELECT COUNT(comis) 2 FROM emp 3 WHERE depto_num = 30; COUNT(COMIS) ------------ 4

41. 2916.6667 2175 1566.6667 Criando Grupos de Dados EMP DEPTO_NUM SAL --------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 DEPTO_NUM AVG(SAL) --------- --------- 10 2916.6667 20 2175 30 1566.6667 “média salarial por departamento”

42. Criando Grupos de Dados: A Cláusula GROUP BY • Divida as linhas de uma tabela em pequenos grupos usando a cláusula GROUP BY. SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

43. A Cláusula GROUP BY • Colunas utilizadas em funções de grupo não precisam estar listadas no GROUP BY. SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY depto_num; AVG(SAL) --------- 2916.6667 2175 1566.6667

44. Utilizando GROUP BY em Múltiplas Colunas SQL> SELECT depto_num, cargo, sum(sal) 2 FROM emp 3 GROUP BY depto_num, cargo; DEPTO_NUM CARGO SUM(SAL) --------- --------- --------- 10 CAIXA 1300 10 GERENTE 2450 10 PRESIDENTE 5000 20 ANALISTA 6000 20 CAIXA 1900 ... 9 rows selected.

45. Criando e Manipulando Tabelas

46. Tabela • Unidade básica de armazenamento da base de dados, formada por colunas e linhas (tuplas)

47. Criando Tabelas • Criando a tabela depto. SQL> CREATE TABLE depto 2 (depto_num NUMBER(2), 3 depto_nom VARCHAR2(14), 4 depto_loc VARCHAR2(13)); Table created. • Listando a estrutura da tabela criada. SQL> DESCRIBE depto Name Null? Type --------------------------- -------- --------- DEPTO_NUM NOT NULL NUMBER(2) DEPTO_NOME VARCHAR2(14) DEPTO_LOC VARCHAR2(13)

48. Consultando o Dicionário de Dados SQL> SELECT * 2 FROM user_tables; • Tabelas do usuário • Objetos SQL> SELECT DISTINCT object_type 2 FROM user_objects; • Tabelas, visões, sinônimos e seqüências SQL> SELECT * 2 FROM user_catalog;

49. Descartando uma Tabela • Todos os dados e a estrutura da tabela são destruídos. • Qualquer transação pendente é encerrada. • Todos os índices são descartados. Essa operação não pode ser desfeita. SQL> DROP TABLE depto30; Table dropped.

50. Renomeando Objetos da Base • Tabelas, visões, sinônimos e seqüências SQL> RENAME depto TO departamento; Table renamed.