Baixe Comandos SQL - Apostilas - Informática Part3 e outras Notas de estudo em PDF para Informática, somente na Docsity!
Finalidade do capítulo é tratar sobre SEQUENCE. Para ver se existe uma sequence selecionamos o objeto no select object_name colocando a clausula where com o objeto_type igual a sequence. SQL> SELECT OBJECT_name from user_objects 2 where object_type = 'SEQUENCE'; OBJECT_NAME
S_CUSTOMER_ID S_DEPART_ID S_DEPT_ID S_DEPT_ID_SEQ S_EMP_ID S_IMAGE_ID S_LONGTEXT_ID S_ORD_ID S_PRODUCT_ID S_REGION_ID S_WAREHOUSE_ID S_WORKER WORKER_ID_SEQ 13 rows selected. O que é uma SEQUENCE: Sequence são números criados pelo ORACLE que fazem a contagem de registros assumindo valores únicos, servindo de ID, uma SEQUENCE pode ser usada por mais de uma tabela, cada qual com seus números, sem que ocorra repetição é claro. O exemplo seguinte mostra como criar uma SEQUENCE , o nome da SEQUENCE é S_TESTE_id , que está relacionada com o id da tabela TESTE ( tabela que foi criada anteriormente), o INCREMENTE BY serve para que a SEQUENCE evolua de um valor, o START WITH serve para que a SEQUENCE comece com o numero 51, o MAXVALUE é o valor máximo que uma SEQUENCE pode assumir, NOCACHE especifica se será alocada a memória cash ou não, NOCYCLE serve para especificar ou não um ciclo de SEQUENCE ou seja os números vão contando em um ciclo de tempo determinado.
Vamos ao Exemplo: SQL> CREATE SEQUENCE S_TESTE_id 2 INCREMENT BY 1 3 START WITH 51 4 MAXVALUE 9999999 5 NOCACHE 6 NOCYCLE; Sequence created. Como mostrar todas as SEQUENCES que estão disponíveis para seu user: SQL> select sequence_name, min_value, max_value, 2 increment_by,last_number 3 from user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
S_CUSTOMER_ID 1 9999999 1 216 S_DEPT_ID 1 9999999 1 51 S_EMP_ID 1 9999999 1 26 S_IMAGE_ID 1 9999999 1 1981 S_LONGTEXT_ID 1 9999999 1 1369 S_ORD_ID 1 9999999 1 113 S_PRODUCT_ID 1 9999999 1 50537 S_REGION_ID 1 9999999 1 6 S_TESTE 1 9999999 1 51 S_TESTE_ID 1 9999999 1 51 S_WAREHOUSE_ID 1 9999999 1 10502 11 rows selected. No exemplo abaixo estamos criando uma SEQUENCE para o ID de s_dept, foi escolhido como nome para a SEQUENCE s_dept_id , sempre para SEQUENCE usa-se o "S" no começo do nome da sequence. SQL> CREATE SEQUENCE S_dept_id 2 INCREMENT BY 1 3 START WITH 51 4 MAXVALUE 9999999 5 NOCACHE 6 NOCYCLE; Sequence created.
Para dropar uma SEQUENCE temos que seguir os passos a seguir: SQL> DROP SEQUENCE s_dept_id; Sequence dropped. SQL> DROP SEQUENCE S_TESTE; Sequence dropped. SQL> DROP SEQUENCE S_TESTE_ID; Sequence dropped.
FINAL DE CAPÍTULO
Finalidade do capítulo é o uso de SUBQUERYS: Uma subquery é um cmdo select dentro de um outro cmdo select onde retorna uma ou mais linhas a fim de satisfazer uma clausula WHERE. No exemplo abaixo temos um select em s_emp onde procuramos trazer o last_name e o title , onde o title pesquisado seja o mesmo do " Smith" , para isso é realizado uma subquery que nada mais é que um select , que neste caso retorna um valor somente para a comparação na where. SQL> select last_name, title 2 from s_emp 3 where title = 4 (select title 5 from s_emp 6 where last_name = 'Smith'); LAST_NAME TITLE
Maduro Stock Clerk Smith Stock Clerk Nozaki Stock Clerk Patel Stock Clerk Newman Stock Clerk Markarian Stock Clerk Chang Stock Clerk Patel Stock Clerk Dancs Stock Clerk Schwartz Stock Clerk 10 rows selected. Outro exemplo de subquery , que neste caso está comparando os valores da coluna SALARY com a média dos salários da tabela s_emp. A função AVG está trazendo a média dos salários. SQL> select last_name, title, salary 2 from s_emp 3 where salary< 4 (select avg(salary)
6 where name = 'Finance' or region_id =2); LAST_NAME FIRST_NAME TITLE
Quick-To-See Mark VP, Finance Menchu Roberta Warehouse Manager Giljum Henry Sales Representative Nozaki Akira Stock Clerk Patel Vikram Stock Clerk O uso do having em subquery: Neste exemplo estamos querendo selecionar o dept_id e a média dos salários de s_emp , grupados pelo dept_id , com a condição de que a média dos salários de s_emp seja maior que a média dos salários do dept 32 para isso usamos o HAVING. Vamos ao Exemplo: SQL> select dept_id,avg(salary) 2 from s_emp 3 group by dept_id 4 having avg(salary)> 5 (select avg(salary) 6 from s_emp 7 where dept_id = 32); DEPT_ID AVG(SALARY)
33 1515 50 2025
Agora dentro do group by estamos usando o having e dentro da subquery selecionando o menor valor da média da tabela s_emp grupado por title SQL> 1 select title,avg(salary) 2 from s_emp 3 group by title 4 having avg(salary) = 5 (select min(avg(salary)) 6 from s_emp 7* group by title) TITLE AVG(SALARY)
Stock Clerk 949 SQL> select min(avg(salary)) 2 from s_emp 3 group by title; MIN(AVG(SALARY))
949
FINAL DE CAPÍTULO
Finalidade do capítulo é tratar sobre o comando UPDATE. O CMDO UPDATE serve para fazermos alterações em registros dentro de nossa tabela observe os exemplos abaixo: Estamos vendo o nr do dept onde o id é igual a 2: SQL> select dept_id 2 from 3 S_EMP 4 where id = 2; DEPT_ID
41 Agora todos que tiverem nr do id igual a 2 passará a ter o id do dept igual a 10. SQL> UPDATE S_EMP 2 SET dept_id = 10 3 WHERE id = 2; 1 row updated. Verificando se realmente se concretizou o UPDATE SQL> select dept_id 2 from 3 S_EMP 4 where id = 2; DEPT_ID
10 Estamos selecionado dept_id, salary de s_emp onde o id for igual a 1. SQL> select dept_id,salary 2 from s_emp 3 where id = 1; DEPT_ID SALARY
Agora vamos alterar os valores de dept_id para 32 e de salary para 2550 onde o id for igual a 1. SQL> UPDATE s_emp 2 SET dept_id = 32, salary = 2550 3 where id = 1; 1 row updated. Verificando a concretização da mudança. SQL> select dept_id,salary 2 from s_emp 3 where id = 1; DEPT_ID SALARY
32 2550 Quando não usamos a clausula where no UPDATE fazemos alterações em todos os registros da tabela, no caso estamos mudando todos os campos de commission_pct para 10 sem distinção. SQL> UPDATE S_EMP 2 SET commission_pct = 10; 26 rows updated. Neste exemplo estamos tentando fazer uma alteração em um valor que é uma FOREIKEY , e o valor que queremos adicionar não existe na tabela de origem da FK portanto irá ferir uma constraint. SQL> UPDATE S_EMP 2 SET DEPT_ID = 60 3 WHERE DEPT_ID = 10; UPDATE S_EMP
ERROR at line 1: ORA-02291: integrity constraint (GUIMA.S_EMP_DEPT_ID_FK) violated - parent key not found
Finalidade do capítulo é tratar sobre VIEW. Uma VIEW é como se fosse uma janela que dá acesso aos dados da tabela, só que com restrições. No exemplo abaixo estamos criando uma VIEW usando uma SUBQUERY, trazendo dados específicos de uma tabela.Vamos ao exemplo: SQL> CREATE VIEW empvu45 2 AS SELECT id,last_name,title 3 FROM s_emp 4 WHERE dept_id = 45; View created. Estamos agora realizando um select em nossa VIEW: SQL> select * from 2 empvu45; ID LAST_NAME TITLE
10 Havel Warehouse Manager 24 Dancs Stock Clerk 25 Schwartz Stock Clerk Estamos fazendo um select na tabela S_EMP, referente ao dept_id 45 e vemos que nossa VIEW é idêntica. SQL> select id,last_name,title 2 FROM s_emp 3 WHERE dept_id = 45; ID LAST_NAME TITLE
10 Havel Warehouse Manager 24 Dancs Stock Clerk 25 Schwartz Stock Clerk Criando uma VIEW usando ALIASES : SQL> CREATE VIEW salvu
2 AS SELECT id,first_name PRIMEIRO, 3 last_name ULTIMO, salary SALARIO_MENSAL 4 FROM 5 s_emp 6 WHERE dept_id = 41; View created. Vendo a VIEW criada: SQL> desc salvu Name Null? Type
ID NOT NULL NUMBER(7) PRIMEIRO VARCHAR2(25) ULTIMO NOT NULL VARCHAR2(25) SALARIO_MENSAL NUMBER(11,2) Mais um exemplo de como criar uma VIEW: Estamos criando uma VIEW chamada "dept_sum_vu" com os nomes de colunas criados por eu da forma que achar melhor, observe que está sendo realizado uma SUBQUERY e um JOIN dentro da SUBQUERY entre as tabelas s_emp e s_dept , onde pega-se o menor salário função MIN , o maior salário(função MAX ) a média dos salários(função AVG ) com a condição estabelecida da clausula WHERE que não é mais que a ligação do JOIN , e por último nossa VIEW virá agrupada pelo NOME de s_dept. SQL> EDIT Wrote file afiedt.buf 1 create VIEW dept_sum_vu 2 (name, minsal,maxsal, avgsal) 3 AS SELECT d.name, MIN (e.salary), 4 MAX(e.salary),AVG(e.salary) 5 FROM s_emp e,s_dept d 6 WHERE e.dept_id = d.id 7* GROUP BY d.name SQL> / View created. Este é o formato de nossa VIEW , verifique o nome das colunas correspondentes a SUBQUERY dentro do CREATE VIEW , e agrupados
Estamos agora criando uma VIEW com a condição WITH CHECK OPTION CONSTRAINT que tem a finalidade de não permitir alteração na VIEW em relação a clausula WHERE. SQL> edit Wrote file afiedt.buf 1 create or replace VIEW empvu 2 AS SELECT * 3 FROM s_emp 4 WHERE dept_id = 41 5* WITH CHECK OPTION CONSTRAINT empvu41_ck SQL> / View created. Tentamos fazer uma alteração na VIEW empvu41 , não será permitida a alteração nos moldes sugeridos abaixo ou seja a alteração no nr do departamento. SQL> UPDATE empvu 2 SET DEPT_ID = 42 3 WHERE ID = 16; UPDATE empvu
ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation. Criando uma VIEW e determinando que ela seja somente de leitura. SQL> CREATE OR REPLACE VIEW empvu 2 (id_number, employee, job) 3 AS SELECT id,last_name,title 4 FROM S_EMP 5 WHERE dept_id = 45 6 WITH READ ONLY; View created. Estamos tentando deletar registros da VIEW empvu45 só que ela foi criada com a opção WITH READ ONLY , ou seja somente leitura. SQL> DELETE FROM empvu 2 WHERE ID_NUMBER = 10;
DELETE FROM empvu
ERROR at line 1: ORA-01752: cannot delete from view without exactly one key- preserved table Para saber as VIEWS existentes vamos a USER_VIEWS que pertence ao dicionário de dados e então podemos pesquisar veja só: SQL> describe user_views; Name Null? Type
VIEW_NAME NOT NULL VARCHAR2(30) TEXT_LENGTH NUMBER TEXT LONG -- selecionando as VIEWS existentes: SQL> SELECT * FROM 2 user_views; VIEW_NAME TEXT_LENGTH TEXT
EMPVU41 194 SELECT"ID","LAST_NAME","FIRST_NAME","USE RID","START_DATE","COMMENTS","MANAG EMPVU45 74 SELECT id,last_name,title FROM S_EMP WHERE dept_id = 45 WITH READ ONLY Para deletar uma VIEW usamos o cmdo DROP : Quando deletamos uma VIEW não alteramos em nada a tabela. SQL> DROP VIEW EMPVU45 ; View dropped.
FINAL DE CAPÍTULO
2 FROM S_EMP
3 WHERE START_DATE BETWEEN '09-MAY-91'
4 AND '17-JUN-91';
FIRST_NAME LAST_NAME START_DAT
Midori Nagayama 17 - JUN- 91 Alexander Markarian 26 - MAY- 91 Sylvie Schwartz 09 - MAY- 91 Agora vamos fazer um desc na tabela s_dept : SQL> DESC S_DEPT; Name Null? Type
ID NOT NULL NUMBER(7) NAME NOT NULL VARCHAR2(25) REGION_ID NUMBER(7) Agora vamos fazer uma seleção onde region_id seja (1,3) e somente estes. SQL> SELECT ID,NAME,REGION_ID 2 FROM S_DEPT 3 WHERE REGION_ID IN (1,3); ID NAME REGION_ID
10 Finance 1 31 Sales 1 33 Sales 3 41 Operations 1 43 Operations 3 50 Administration 1 6 rows selected. No exemplo abaixo o uso comando null na clausula where , especifica e traz o s_customer onde sales_rep_id for nulo ou seja onde o customer não tiver sales_rep_id. Mas neste exemplo abaixo não cumpre a finalidade, apesar de estar correto.
SQL> select ID,NAME,CREDIT_RATING 2 FROM S_CUSTOMER 3 WHERE SALES_REP_ID = NULL; no rows selected Forma errada de se pesquisar por um valor nulo: SQL> SELECT ID,NAME,CREDIT_RATING 2 FROM S_CUSTOMER 3 WHERE SALES_REP_ID =' '; ERROR: ORA-01722: invalid number no rows selected Veja a maneira correta de pesquisar usando a clausula where com a condição de campos nulos. SQL> SELECT ID,NAME,CREDIT_RATING 2 FROM S_CUSTOMER 3 WHERE SALES_REP_ID IS NULL; ID NAME CREDIT_RA
207 Sweet Rock Sports GOOD / O exemplo abaixo demostra o uso do comando and dentro da clausula where , onde dentro da tabela s_emp será pesquisado os registros que sejam do departamento 41 e com o nome do title " stock clerk". SQL> SELECT LAST_NAME,SALARY,DEPT_ID,TITLE 2 FROM S_EMP 3 WHERE DEPT_ID = 41 4 AND TITLE = 'Stock Clerk'; LAST_NAME SALARY DEPT_ID TITLE
Maduro 1400 41 Stock Clerk Smith 940 41 Stock Clerk
