Banco de dados e SQL

Banco de dados e SQL

(Parte 6 de 8)

7) Selecione os Nomes dos Departamentos que estejam na fábrica.

Resp: SELECTDEPNOME FROMDEPT WHERE DEPLOCA = "SAO PAULO";

O exemplo exigiu uma restrição (São Paulo) que nos obrigou a utilizar da instrução Where. Alguns analistas costumam afirmar em tom jocoso que SQL não passa de

"Selecione algo De algum lugar Onde se verificam tais relações"

Acreditamos que esta brincadeira pode ser útil ao estudante, na medida em que facilita sua compreensão dos objetivos elementares do SQL.

OperadorSignificado
betweenand... entredoisvalores(inclusive)
in() listadevalores
likecomumpadraodecaracteres
isnulléumvalornulo

Demais Operadores

Exemplos: SELECT EMPNOME, EMPSALA FROM EMP WHERE EMPSALABETWEEN 500 AND 1000;

SELECT EMPNOME, DEPNUME FROM EMP WHERE DEPNUME IN (10,30);

O símbolo "%" pode ser usado para construir a pesquisa ("%" = qualquer sequência de nenhum até vários caracteres).

operadordescrição
<>diferente
notnome_coluna=diferentedacoluna
notnome_coluna>nãomaiorque
notbetweennãoentredoisvaloresinformados
notinnãoexistentenumadadalistadevalores
notlikediferentedopadraodecaracteresinformado
isnotnullnãoéumvalornulo

Operadores Negativos

8) Selecione os Empregados cujos salários sejam menores que 1000 ou maiores que 3500. Resp: Necessitaremos aqui a utilização de expressão negativas. A seguir apresentamos operadores negativos.

SELECT EMPNOME, EMPSALA FROM EMP WHERE EMPSALA NOT BETWEEN 1000 AND 3500;

9) Apresente todos os funcionários com salários entre 200 e 700 e que sejam Vendedores. Resp: Necessitaremos de consultas com condições múltiplas. Operadores "AND"(E)e "OR"(OU).

SELECT EMPNOME,EMPSALA, EMPSERV FROM EMP WHERE EMPSALA BETWEEN 700AND2000 AND EMPSERV= 'VENDEDOR';

10) Apresente todos os funcionários com salários entre 200 e 700 ou que sejam Vendedores.

Resp: SELECT EMPNOME,EMPSALA, EMPSERV FROM EMP WHEREEMPSALA BETWEEN 700AND2000 OR EMPSERV= 'VENDEDOR'; 1) Apresente todos os funcionários com salários entre 200 e 700 e que sejam Vendedores ou Balconistas.

Resp: SELECT EMPNOME,EMPSALA, EMPSERV FROM EMP WHERE EMPSALA BETWEEN 700AND2000 AND(EMPSERV= 'BALCONISTA'OR EMPSERV= 'VENDEDOR');

Lower-forçacaracteresmaiúsculosaparecerememminúsculos.
Upper-forçacaracteresminúsculosaparecerememmaiúsculos.
Concat(x,y)-concatenaastring"x"comastring"y".
Substring(x,y,str)-extraiumsubstringdastring"str",começandoem"x",eterminaem"y".
To_Char(num)-converteumvalornuméricopara umastringdecaracteres.
To_Date(char,fmt)-converteumastringcaracteremumadata.
Q-convertedataparaoformatoapresentado.

Funções de Caracteres

12) Apresente o nome de todos os empregados em letras minúsculas.

Resp: SELECTLOWER(EMPNOME) FROMEMP;

13) Apresente o nome de todos os empregados (somente as 10 primeiras letras).

Resp: SELECT SUBSTRING (1,10,EMPNOME) FROMEMP;

14) Apresente o nome de todos os empregados admitidos em 01/01/80.

funçãoretorno
avg(n)médiadovalorn,ignorandonulos
count(expr)vezesqueonúmerodaexpravaliaparaalgonaonulo
max(expr)maiorvalordaexpr
min(expr)menorvalordaexpr
sum(n)somadosvaloresden,ignorandonulos

Funções Agregadas (ou de Agrupamento) 15) Apresente a Média, o Maior, o Menor e também a Somatória dos Salários pagos aos empregados.

Resp: SELECT AVG(EMPSALA) FROM EMP;

Agrupamentos

As funções de grupo operam sobre grupos de tuplas(linhas). Retornam resultados baseados em grupos de tuplas em vez de resultados de funções por tupla individual. A claúsula "group by" do comando "select" é utilizada para dividir tuplas em grupos menores.

A cláusula "GROUP BY" pode ser usada para dividir as tuplas de uma tabela em grupos menores. As funções de grupo devolvem uma informação sumarizada para cada grupo.

16) Apresente a média de salário pagos por departamento.

Resp: SELECT DUPNUME, AVG(EMPSALA) FROMEMP GROUPBY DEPNUME;

Obs.: Qualquer coluna ou expressão na lista de seleção, que não for uma função agregada, deverá constar da claúsula "group by". Portanto é errado tentar impor uma "restrição" do tipo agregada na cláusula Where.

Having A cláusula "HAVING" pode ser utilizada para especificar quais grupos deverão ser exibidos, portanto restringindo-os.

17) Retome o problema anterior, porém apresente resposta apenas para departamentos com mais de 10 empregados.

Resp: SELECT DEPNUME, AVG(EMPSALA) FROM EMP GROUPBYDEPNUME HAVINGCOUNT(*) > 3;

Obs.: A claúsula "group by" deve ser colocada antes da "having", pois os grupos são formados e as funções de grupos são calculadas antes de se resolver a cláusula "having".

A cláusula "where" não pode ser utilizada para restringir grupos que deverão ser exibidos. Exemplificando ERRO típico - Restringindo Média Maior que 1000:

SELECT DEPNUME,AVG(EMPSALA) FROMEMP WHERE AVG(SALARIO) > 1000 GROUPBY DEPNUME; ( Esta seleção está ERRADA! )

SELECT DEPNUME, AVG(EMPSALA) FROMEMP GROUPBY DEPNUME HAVING AVG(EMPSALA) > 1000; ( Seleção Adequada )

SELECTcoluna(s)
FROMtabela(s)
WHEREcondição(ões)da(s)tupla(s)
GROUPBYcondição(ões)do(s)grupo(s)detupla(s)
HAVINGcondição(ões)do(s)grupo(s)detupla(s)
ORDERBYcoluna(s);

Seqüência no comando "Select": A "sql" fará a seguinte avaliação:

a) WHERE, para estabelecer tuplas individuais candidatas (não pode conter funções de grupo) b) GROUP BY, para fixar grupos. c) HAVING, para selecionar grupos para exibiçao.

Equi-Junção ( Junção por igualdade )

O relacionamento existente entre tabelas é chamado de equi-junção, pois os valores de colunas das duas tabelas são iguais. A Equi-junção é possível apenas quando tivermos definido de forma adequada a chave estrangeira de uma tabela e sua referência a chave primária da tabela precedente. Apesar de admitir-se em alguns casos, a equi-junção de tabelas, sem a correspondência Chave Primária-Chave Estrangeira, recomendamos fortemente ao estudante não utilizar este tipo de construção, pois certamente em nenhum momento nos exemplos propostos em nossa disciplina ou nas disciplinas de Análise e Projeto de Sistemas, serão necessárias tais junções. 18) Listar Nomes de Empregados, Cargos e Nome do Departamento onde o empregado trabalha.

Resp: Observemos que dois dos três dados solicitados estão na Tabela Emp, enquanto o outro dado está na Tabela Dept. Deveremos então acessar os dados restringindo convenientemente as relações existentes entre as tabelas. De fato sabemos que DEPNUME é chave primária da tabela de Departamentos e também é chave estrangeira da Tabela de Empregados. Portanto, este campo será o responsável pela equi-junção.

Obs.: Note que as tabelas quando contém colunas com o mesmo nome, usa-se um apelido "alias" para substituir o nome da tabela associado a coluna. Imagine que alguém tivesse definido NOME para ser o Nome do Empregado na Tabela de Empregados e também NOME para ser o Nome do Departamento na Tabela de Departamentos. Tudo funcionaria de forma adequada, pois o aliás se encarregaria de evitar que uma ambiqüidade fosse verificada. Embora SQL resolva de forma muito elegante o problema da nomenclatura idêntica para campos de tabelas, recomendamos que o estudante fortemente evite tal forma de nomear os campos. O SQL nunca confundirá um A.NOME com um B.NOME, porém podemos afirmar o mesmo de nós mesmos?

19) Liste os Códigos do Cada Funcionário, seus Nomes, seus Cargos e o nome do Gerente ao qual este se relaciona.

Resp: Precisamos criar um auto-relacionamento, ou seja, juntar uma tabela a ela própria. É possível juntarmos uma tabela a ela mesma com a utilização de apelidos, permitindo juntar tuplas da tabela a outra tuplas da mesma tabela.

As Sub-Consultas

Uma sub-consulta é um comando "select" que é aninhado dentro de outro "select" e que devolve resultados intermediários.

20) Relacione todos os nomes de funcionários e seus respectivos cargos, desde que o orçamento do departamento seja igual a 300000.

Resp:

(Parte 6 de 8)

Comentários