Baixe SQL Aplicações e Consultas no Banco de Dados e outras Notas de estudo em PDF para Informática, somente na Docsity!
Agora estamos contando quantos registros temos para cada CREDIT_RATING da tabela s_customer. SQL> select credit_rating,count(*) "# custo" 2 from s_customer 3 group by credit_rating; CREDIT_RA # custo
EXCELLENT 9 GOOD 3 POOR 3 No próximo exemplo é bom destacar o uso do NOT LIKE onde determina que não seja trazido registros parecidos com uma uma certa condição. Veja também o uso da função de grupo SUM que trás a soma dos salários. SQL> select title, sum(salary) soma_total 2 from s_emp 3 where title not like 'VP%' 4 group by title 5 order by 6 sum(salary); TITLE SOMA_TOTAL
President 2500 Warehouse Manager 6157 Sales Representative 7380 Stock Clerk 9490 Um outro exemplo do uso de ORDER BY, onde estamos grupando por Title. SQL> select title,max(salary) 2 from s_emp 3 group by title; TITLE MAX(SALARY)
President 2500 Sales Representative 1525 Stock Clerk 1400 VP, Administration 1550 VP, Finance 1450
VP, Operations 1450 VP, Sales 1400 Warehouse Manager 1307 8 rows selected. Nossa intenção no select abaixo é fazermos a média dos salários dos campos da coluna SALARY da tabela s_emp, e trazer a tela somente a média que der acima de 2000. Não dá certo porque não usamos a clausula where dentro do group by mas sim a função HAVING , isso quando queremos trazer dados a tela obedecendo uma certa condição. SQL> SELECT DEPT_ID,AVG(SALARY) 2 FROM S_EMP 3 WHERE AVG(SALARY) > 2000 4 GROUP BY DEPT_ID; WHERE AVG(SALARY) > 2000
ERROR at line 3: ORA-00934: group function is not allowed here Veja a maneira correta de se fazer o exercício anterior onde usamos a função HAVING que se dá da seguinte forma , sempre após o group by. SQL> SELECT DEPT_ID,AVG(SALARY) 2 FROM S_EMP 3 GROUP BY DEPT_ID 4 HAVING AVG(SALARY) > 2000; DEPT_ID AVG(SALARY)
50 2025S No exemplo abaixo estamos selecionando, contando e separando em grupos os departamentos isso pelo dept_id e depois o title, perceba a seqüência do grupo. SQL> select dept_id,title, count() 2 from s_emp 3 group by dept_id,title; DEPT_ID TITLE COUNT()
VP, Finance 10 1 VP, Operations 41 1 VP, Sales 31 1 Warehouse Manager 41 1 Warehouse Manager 42 1 Warehouse Manager 43 1 Warehouse Manager 44 1 Warehouse Manager 45 1 Observe o uso do HAVING em substituição a clausula WHERE, além de outros SELECTS. Quando quisermos trazer um registro que inicie por uma letra qualquer temos podemos usamos o cmdo LIKE procedendo da seguinte forma: SQL> select last_name, title 1 from s_emp 2 where last_name like 'V%' LAST_NAME TITLE
Velasquez President No exemplo abaixo estamos fazendo a seleção de todos os cargos de funcionários existentes assim como contando seus componentes (^) COUNT e ainda trazendo as suas respectivas medias salariais usando a função AVG, multiplicadas por 12. SQL> SELECT TITLE, 12 * AVG(SALARY) " Salário Anual", 2 count(*) " numero de funcionários" 3 from s_emp 4 group by title; TITLE Salário Anual numero de funcionários President $30,000.00 1 Sales Representative $17,712.00 5 Stock Clerk $11,388.00 10 VP, Administration $18,600.00 1 VP, Finance $17,400.00 1 VP, Operations $17,400.00 1 VP, Sales $16,800.00 1
Warehouse Manager $14,776.80 5 8 rows selected. Um pouco diferente do exemplo anterior o abaixo está primeiramente formatando a coluna Salário Anual para receber valores após ter sido feito um select em TITLE. Estamos multiplicando por 12 a soma da média dos salários feita pela função AVG , e então usamos a função COUNT para contar o numero de funcionários de s_emp grupados por title e que tenha no máximo 2 funcionários, olhando para o exemplo anterior podemos entender melhor e também destacar que o HAVING está fazendo a função de um WHERE dentro do GROUP BY. Vamos ao Exemplo: SQL> column " Salário Anual" FORMAT $99,999. SQL> SELECT TITLE, 12 * AVG(SALARY) " Salário Anual", 2 count() " numero de funcionários" 3 from s_emp 4 group by title 5 having count() > 2; TITLE Salário Anual numero de funcionários
Sales Representative $17,712.00 5 Stock Clerk $11,388.00 10 Warehouse Manager $14,776.80 5 Mais um exemplo do uso do HAVING: SQL> select title,sum(salary) soma 2 from s_emp 3 where title not like'VP%' 4 group by title 5 having sum(salary) > 5000 6 order by sum(salary); TITLE SOMA
Warehouse Manager 6157 Sales Representative 7380 Stock Clerk 9490
FIM DE CAPÍTULO
SQL> GRANT MANAGER TO NOVO;
Grant succeeded Como mudar a senha de um usuário: no caso estamos mudando do user novo que tinha como "senha" ou seja IDENTIFIED igual a TIGER passando a ser LION. SQL> ALTER USER NOVO IDENTIFIED BY LION; User altered. Agora estamos dando um novo GRANT para o USER "NOVO" referente a tabela s_emp, neste caso o GRANT é só de consulta. SQL> GRANT select 2 ON s_emp 3 TO novo; Grant succeeded. O exemplo abaixo mostra como adicionar um novo GRANT a uma ROLE , relacionado com colunas específicas de uma tabela no caso s_dept e atribuindo a permissão de update para o usuário "NOVO" e para a ROLE "manager". SQL> GRANT update(name,region_id) 2 ON s_dept 3 TO novo,manager; Grant succeeded. Este exemplo mostra como dar GRANT para um USER em uma tabela e este usuário ter permissão de passar este mesmo GRANT para outros usuários. SQL> GRANT select 2 ON s_emp 3 TO scott 4 WITH GRANT OPTION; Grant succeeded.
Agora estou tornando publico de todos o SELECT a tabela S_DEPT referente ao usuário GUIMA, ou seja todos podem dar select a tabela s_dept do usuário GUIMA. SQL> GRANT SELECT 2 ON GUIMA.S_DEPT 3 TO PUBLIC; Grant succeeded. Para sabermos quais privilégios nós possuímos podemos acessar no DICIONÁRIO DE DADOS nas seguintes tabelas: ROLE_SYS_PRIVS ROLE_TAB_PRIVS USER_ROLE_PRIVS USER_TAB_PRIVS_MADE USER_TAB_PRIVS_RECD USER_COL_PRIVS_MADE USER_COL_PRIVS_RECD Como ver as ROLES que eu tenho acesso: SQL> SELECT GRANTED_ROLE,OS_GRANTED 2 FROM USER_ROLE_PRIVS 3 WHERE USERNAME = 'GUIMA'; GRANTED_ROLE OS_
CONNECT NO DBA NO MANAGER NO Para tirar os privilégios de um USER procede-se da seguinte forma: SQL> REVOKE select 2 ON s_emp 3 FROM NOVO; Revoke succeeded.
Finalidade do Capítulo é Tratar Sobre INDEX e JOIN e OUTER JOIN : INDEX: Uma INDEX pode ser criada automaticamente(quando é criada uma PK ou UK constraint em uma tabela) ou manualmente. Para delatarmos uma INDEX temos que usar o cmdo DROP INDEX : Vamos ao Exemplo: SQL> DROP INDEX s_emp_last_name_idx; Index dropped. Para criar uma INDEX usa-se o cmdo CREATE INDEX : Vamos ao Exemplo: SQL> CREATE INDEX S_EMP_last_name_idx 2 on S_EMP(last_name); Index created. Para selecionar as INDEX de uma tabela faz-se um SELECT na tabela USER_INDEXES e USER_IND_COLUMNS : O uso do JOIN : Join é a ligação que fazemos entre duas tabelas na pesquisa de dados, necessariamente deve existir em um join a chave primaria fazendo relação com uma chave estrangeira, esta é a condição e ligação. No exemplo abaixo estamos selecionando o last_name e dept_id da tabela s_emp e também selecionando a coluna name da tabela s_dept isto onde a coluna id de s_dept for igual a coluna dept_id de s_emp , completando assim a condição do JOIN. Vamos ao exemplo: SQL> SELECT S_EMP.LAST_NAME,S_EMP.DEPT_ID,S_DEPT.NAME 2 FROM S_EMP,S_DEPT 3 WHERE S_EMP.DEPT_ID = S_DEPT.ID;
LAST_NAME DEPT_ID NAME
Velasquez 50 Administration Ngao 41 Operations Nagayama 31 Sales Quick-To-See 10 Finance Ropeburn 50 Administration Urguhart 41 Operations Menchu 42 Operations Biri 43 Operations Catchpole 44 Operations Havel 45 Operations Magee 31 Sales Giljum 32 Sales Sedeghi 33 Sales Nguyen 34 Sales Dumas 35 Sales Maduro 41 Operations Smith 41 Operations Nozaki 42 Operations Patel 42 Operations Newman 43 Operations Markarian 43 Operations No exemplo abaixo como no anterior temos a realização de um JOIN. Vamos ao exemplo. SQL> SELECT S_DEPT.ID " COD DO DEPT", 2 S_REGION.ID "COD DA REG", 3 S_REGION.NAME"NOME DA REGIÃO" 4 FROM S_DEPT,S_REGION 5 WHERE S_DEPT.REGION_ID = S_REGION.ID; COD DO DEPT COD DA REG NOME DA REGIÃO
10 1 North America 31 1 North America 32 2 South America 33 3 Africa / Middle East 34 4 Ásia 35 5 Europe 41 1 North America 42 2 South America
LAST_NAME NAME COMMISSION_PCT
Magee North America 10 Giljum South America 12. Sedeghi Africa / Middle East 10 Nguyen Ásia 15 Dumas Europe 17. No exemplo abaixo estamos mostrando um join que traz todos os registros que satisfaçam a condição da clausula where ou seja traz somente aquele que satisfação o JOIN não traz os registros que contem espaços em branco, em função da relação entre as duas tabelas no caso entre s_emp e s_customer. Mas e se quisermos que pesquisar inclusive os registros que não tem relacionamento, ou seja que estejam com o espaço em branco? Para isso temos que acrescentarmos o sinal de (+). Vamos ao Exemplo: SQL> select e.last_name,e.id,c.name 2 from s_emp e, s_customer c 3 where e.id = c.sales_rep_id 4 order by e.id; LAST_NAME ID NAME
Magee 11 Womansport Magee 11 Beisbol Si! Magee 11 Ojibway Retail Magee 11 Big John's Sports Emporium Giljum 12 Unisports Giljum 12 Futbol Sonora Sedeghi 13 Hamada Sport Nguyen 14 Simms Athletics Nguyen 14 Delhi Sports Dumas 15 Kam's Sporting Goods Dumas 15 Sportique Dumas 15 Sporta Russia Dumas 15 Kuhn's Sports Dumas 15 Muench Sports
O exemplo a seguir é idêntico ao anterior com a diferença que está acrescido do sinal de (+) para, mesmo satisfazendo a clausula where , traga os registros que não tenham satisfeito a condição de relacionamento da clausula where, no caso, estamos então fazendo um OUTER JOIN pegando o LAST_NAME e o ID da tabela s_emp e o NAME da tabela s_customer onde o id da tabela s_emp corresponda ao sales_rep_id dentro da tabela s_customer e com o sinal de (+) traga também os NAME de s_customer que não tenham relação com s_emp. SQL> select e.last_name,e.id,c.name 2 from s_emp e, s_customer c 3 where e.id (+) = c.sales_rep_id 4 order by e.id; LAST_NAME ID NAME
Magee 11 Womansport Magee 11 Beisbol Si! Magee 11 Ojibway Retail Magee 11 Big John's Sports Empori Giljum 12 Unisports Giljum 12 Futbol Sonora Sedeghi 13 Hamada Sport Nguyen 14 Simms Athletics Nguyen 14 Delhi Sports Dumas 15 Kam's Sporting Goods Dumas 15 Sportique Dumas 15 Muench Sports Dumas 15 Sporta Russia Dumas 15 Kuhn's Sports Sweet Rock Sports 15 rows selected.
FINAL DE CAPÍTULO
Finalidade do capítulo é mostrar o uso do ORDER BY. O exemplo abaixo mostra como usar o comando order by , onde no caso estamos ordenando a tabela s_emp pelo o last_name , é bom lembrar que sempre o comando order by vem por último na função sql. Vamos ao exemplo: SELECT LAST_NAME, DEPT_ID,START_DATE FROM S_EMP ORDER BY LAST_NAME LAST_NAME DEPT_ID START_DAT
Biri 43 07-APR- 90 Catchpole 44 09-FEB- 92 Chang 44 30-NOV- 90 Dancs 45 17-MAR- 91 Dumas 35 09-OCT- 91 Giljum 32 18-JAN- 92 Havel 45 27-FEB- 91 Maduro 41 07-FEB- 92 Magee 31 14-MAY- 90 Markarian 43 26-MAY- 91 Menchu 42 14-MAY- 90 Nagayama 31 17-JUN- 91 Newman 43 21-JUL- 91 Ngao 41 08-MAR- 90 Nguyen 34 22-JAN- 92 Nozaki 42 09-FEB- 91 Patel 42 06 - AUG- 91 Patel 34 17-OCT- 90 Quick-To-See 10 07-APR- 90 Ropeburn 50 04-MAR- 90 Schwartz 45 09-MAY- 91 LAST_NAME DEPT_ID START_DAT
Sedeghi 33 18-FEB- 91 Smith 41 08-MAR- 90 Urguhart 41 18-JAN- 91 Velasquez 50 03-MAR- 90 25 rows selected.
No comando sql order by temos a função desc que vem com a finalidade de colocar os dados dentro da coluna em ordem decrescente. No exemplo a seguir estamos colocando por ordem decrescente a coluna start_date da tabela s_emp. SQL> SELECT LAST_NAME, DEPT_ID,START_DATE 2 FROM S_EMP 3 ORDER BY START_DATE DESC; LAST_NAME DEPT_ID START_DAT
Catchpole 44 09-FEB- 92 Maduro 41 07-FEB- 92 Nguyen 34 22-JAN- 92 Giljum 32 18-JAN- 92 Dumas 35 09-OCT- 91 Patel 42 06-AUG- 91 Newman 43 21-JUL- 91 Nagayama 31 17-JUN- 91 Markarian 43 26-MAY- 91 Schwartz 45 09-MAY- 91 Dancs 45 17-MAR- 91 Havel 45 27-FEB- 91 Sedeghi 33 18-FEB- 91 Nozaki 42 09-FEB- 91 Urguhart 41 18-JAN- 91 Chang 44 30-NOV- 90 Patel 34 17-OCT- 90 Menchu 42 14-MAY- 90 Magee 31 14-MAY- 90 Quick-To-See 10 07-APR- 90 Biri 43 07-APR- 90 LAST_NAME DEPT_ID START_DAT
Ngao 41 08-MAR- 90 Smith 41 08-MAR- 90 Ropeburn 50 04-MAR- 90 Velasquez 50 03-MAR- 90 25 rows selected.
O exemplo seguinte mostra como usar a posição da coluna dentro do comando sql para definir a ordenação dos registros por aquela coluna, no caso abaixo estamos ordenando nossos dados pela coluna numero 4 que corresponde a START_DATE de S_EMP. Vamos ao exemplo: SQL> SELECT ID,LAST_NAME,FIRST_NAME,START_DATE 2 MANAGER_ID, SALARY 3 FROM S_EMP 4 ORDER BY 4; ID LAST_NAME FIRST_NAME MANAGER_I SALARY
1 Velasquez Carmen 03 - MAR- 90 2500 5 Ropeburn Audry 04 - MAR- 90 1550 2 Ngao LaDoris 08 - MAR- 90 1450 17 Smith George 08 - MAR- 90 940 4 Quick-To-See Mark 07 - APR- 90 1450 8 Biri Ben 07 - APR- 90 1100 7 Menchu Roberta 14 - MAY- 90 1250 11 Magee Colin 14 - MAY- 90 1400 23 Patel Radha 17 - OCT- 90 795 22 Chang Eddie 30 - NOV- 90 800 6 Urguhart Molly 18 - JAN- 91 1200 18 Nozaki Akira 09 - FEB- 91 1200 9 Catchpole Antoinette 09 - FEB- 92 1300 25 rows selected. Agora além de ordenar pela coluna numero 4 estamos também colocando em ordem decrescente usando para isto o cmdo desc. SQL> SELECT ID,LAST_NAME,FIRST_NAME,START_DATE 2 MANAGER_ID, SALARY 3 FROM S_EMP 4 ORDER BY 4 DESC; ID LAST_NAME FIRST_NAME MANAGER_I SALARY
9 Catchpole Antoinette 09 - FEB- 92 1300 16 Maduro Elena 07 - FEB- 92 1400 14 Nguyen Mai 22 - JAN- 92 1525 12 Giljum Henry 18 - JAN- 92 1490
No próximo exemplo o comando order by esta agrupando por departamento com o dept_id e depois por salário (dentro do grupo departamento) podemos constatar a melhor verificação do exemplo no departamento 41 onde o salário vem por ordem decrescente. SQL> SELECT LAST_NAME,DEPT_ID,SALARY 2 FROM S_EMP 3 ORDER BY DEPT_ID,SALARY DESC; LAST_NAME DEPT_ID SALARY
Quick-To-See 10 1450 Nagayama 31 1400 Magee 31 1400 Giljum 32 1490 Sedeghi 33 1515 Nguyen 34 1525 Patel 34 795 Dumas 35 1450 Ngao 41 1450 Maduro 41 1400 Urguhart 41 1200 Smith 41 940 Menchu 42 1250 Nozaki 42 1200 Patel 42 795 Biri 43 1100 Markarian 43 850 Newman 43 750 Catchpole 44 1300 Chang 44 800 Havel 45 1307 LAST_NAME DEPT_ID SALARY
Schwartz 45 1100 Dancs 45 860 Velasquez 50 2500 Ropeburn 50 1550 25 rows selected. Observação: se colocarmos duas ordenações incompatíveis de execução, o sql irá reconhecer a primeira da seqüência, observe que o exemplo abaixo mostra claramente isto. Observe também o departamento 41, onde o sql ordena primeiro por
