RespLista 8 1 e 8 2

Prévia do material em texto

8.1: Funções de Grupo
Vocabulário 
Identifique a palavra do vocabulário correspondente a cada definição a seguir. 
AVG - Calcula o valor médio excluindo nulos. 
COUNT - Retorna o número de linhas com valores não nulos na expressão. 
STDDEV - Para dois conjuntos de dados com aproximadamente a mesma média, quanto maior for a dispersão, maior será o desvio padrão. 
FUNÇÕES DE GRUPO - Operam em conjuntos de linhas para fornecer um resultado por grupo. 
MIN - Retorna um valor mínimo ignorando nulos. 
VARIANCE - Usada com colunas que armazenam dados numéricos para calcular a dispersão dos dados em torno da média. 
SUM - Calcula a soma ignorando valores nulos. 
MAX - Retorna o valor máximo ignorando nulos. 
AGGREGATE - Reunir em uma soma ou total.
Tente/solucione 
1. Defina e forneça um exemplo das sete funções de grupo: AVG, COUNT, MAX, MIN, STDDEV, SUM e VARIANCE. 
AVG – Calcula o valor médio
SELECT AVG(salary) as "Salário Médio"
FROM EMPLOYEES;
COUNT – Conta o número de linhas com valores não nulos
SELECT COUNT(salary) as "Número de registros"
FROM EMPLOYEES;
MAX – Valor máximo de um conjunto de linhas
SELECT MAX(salary) as "Maior Salário"
FROM EMPLOYEES;
MIN – Valor mínimo de um conjunto de linhas
SELECT MIN(salary) as "Menor Salário"
FROM EMPLOYEES;
STDDEV – Calcula o desvio padrão
SELECT STDDEV(salary) as "Desvio Padrão do Salário"
FROM EMPLOYEES;
SUM – Calcula a soma de várias linhas, ignorando valores nulos.
SELECT SUM(salary) as "Somatória de Salários"
FROM EMPLOYEES;
VARIANCE – Calcula a variância de um conjunto de linhas
SELECT VARIANCE(salary) as "Variânica do Salário"
FROM EMPLOYEES;
2. Crie uma consulta que exibirá o custo médio dos eventos da DJs on Demand. Arredonde para duas casas decimais. 
SELECT ROUND(AVG(cost),2) as "Custo Médio"
FROM d_events;
3. Encontre a média salarial dos membros das equipes da Global Fast Foods cujo gerente tem o ID 19. 
SELECT TO_CHAR(ROUND(AVG(salary),2), '$999999.99') as "Média Salarial"
FROM f_staffs
WHERE manager_id = 19;
4. Encontre a soma dos salários dos membros das equipes da Global Fast Foods com os IDs 12 e 9. 
SELECT TO_CHAR(ROUND(SUM(salary),2), '$999999.99') as "Soma dos salários"
FROM f_staffs
WHERE id in (12, 19);
5. Usando o banco de dados Oracle, selecione o menor salário, a data de contratação mais recente, além do sobrenome da primeira e da última pessoa que aparecem na lista alfabética de funcionários. Selecione somente os funcionários dos departamentos 50 ou 60. 
SELECT MIN(salary) as "Menor Salário", MAX(hire_date) as "Data de contratação", MIN(last_name) as "Primeira pessoa da lista", MAX(last_name) as "Última pessoa da lista"
FROM employees
WHERE department_id in (50, 60);
6. Seu novo negócio na Internet obteve um bom resultado financeiro este ano, com 1.289 pedidos recebidos. Sua tabela de pedidos de clientes tem uma coluna chamada total_sales. Se você executar a consulta a seguir, quantas linhas serão retornadas? 
SELECT sum(total_sales) FROM orders; 
Uma. 
7. Você foi solicitado a criar um relatório das médias salariais de todos os funcionários de cada divisão da empresa. Alguns funcionários da empresa são pagos por hora, em vez de receber um salário. Quando você executou o relatório, as médias aparentemente não foram as esperadas, ficando muito acima do que você imaginava! Qual poderia ter sido a causa? 
Os funcionários que recebem por mês devem ter elevado a média dos que são pagos por hora. 
8. As datas de nascimento dos funcionários da Global Fast Foods são 1º de julho de 1980, 19 de março de 1979 e 30 de março de 1969. Se você selecionar MIN(birthdate), qual data será retornada? 
30 de março de 1969
9. Crie uma consulta que retornará a média total de todos os pedidos da Global Fast Foods de 1º de janeiro de 2002 a 21 de dezembro de 2002. 
SELECT 'Média de ' || COUNT(order_number) || ' pedidos é : ' || AVG(NVL(order_total, 0)) as "Média"
FROM f_orders
WHERE order_date BETWEEN TO_DATE('01-01-2002', 'DD-MM-YYYY') AND TO_DATE('21-12-2002', 'DD-MM-YYYY');
10.Qual foi a data de contratação do último funcionário contratado pela Oracle? 
SELECT MAX(hire_date) FROM employees;
29 de Janeiro de 2000.
11.Na cláusula SELECT a seguir, qual valor retornado pela instrução SELECT será maior? SELECT SUM(operating_cost), AVG(operating_cost) 
SUM. 
12.Consulte a tabela D_EVENTS do banco de dados da DJs on Demand: Que código é válido como parte de uma consulta SQL?
 _______a. FROM event_date 
X b. SELECT SUM(cost) 
_______c. SELECT SUM(event_date) 
X d. SELECT AVG(cost) AS "Expense" 
_______e. WHERE MIN(id) = 100 
_______f. SELECT MAX(AVG(cost)) 
X g. SELECT MIN(event_date)
8.2: Count, Distinct, NVL
Vocabulário 
Identifique a palavra do vocabulário correspondente a cada definição a seguir. 
COUNT - Retorna o número de valores não nulos na coluna da expressão. 
DISTINCT - A palavra-chave usada para retornar somente valores não duplicados ou combinações de valores não duplicados em uma consulta. 
COUNT (DISTINCT) - Retorna o número de valores não nulos exclusivos na coluna da expressão.
Tente/solucione 
1. Quantas músicas estão listadas na tabela D_SONGS da DJs on Demand? 
SELECT COUNT(id)
FROM d_songs;
Seis.
2. Em quantos tipos de locais diferentes a DJs on Demand teve eventos? 2
SELECT COUNT(DISTINCT venue_id)
FROM d_events;
3. A tabela d_track_listings do banco de dados da DJs on Demand tem uma coluna chamada song_id e outra chamada cd_number. Quantos IDs de música e quantos números diferentes de CD existem na tabela? 
SELECT COUNT(song_id) AS "Músicas diferentes", COUNT(distinct cd_number) "CDs diferentes"
FROM d_track_listings;
4. Quantos clientes da DJs on Demand têm endereços de e-mail? 
SELECT COUNT(email) "contas com email"
FROM d_clients;
5. Alguns dos parceiros da DJs on Demand não têm valores de despesas autorizados (auth_expense_amt). Quantos parceiros têm esse privilégio?
SELECT (COUNT(*) - COUNT(auth_expense_amt)) "Parceiros com privilégio"
FROM d_partners;
6. Quais valores serão retornados quando a instrução abaixo for executada?
SELECT COUNT(shoe_color), COUNT(DISTINCT shoe_color) FROM shoes;
COUNT(shoe_color) retorna 4
COUNT(DISTINCT shoe_color) retorna 3
7. Crie uma consulta que converterá todos os valores nulos da coluna auth_expense_amt da tabela D_PARTNERS da DJs on Demand em 100000 e encontre a média dos valores dessa coluna. Arredonde o resultado para duas casas decimais. 
SELECT TO_CHAR(ROUND(AVG(NVL(auth_expense_amt,100000)),2), '$999999.99')
FROM d_partners;
8. Quais afirmativas são Verdadeiras sobre a instrução SQL a seguir: 
SELECT AVG(NVL(selling_bonus, 0.10)) FROM bonuses;
 _____ a. Os valores da cláusula NVL podem ser de qualquer tipo de dados, exceto dados de data. 
X b. Se a coluna selling_bonus tiver um valor nulo, esse valor será substituído por 0.10. 
X c. Não existirão valores nulos na coluna selling_bonus quando a média for calculada. 
_____ d. Essa instrução gerará um erro. Não podem existir duas funções na instrução SELECT. 
9. Quais das seguintes afirmativas são VERDADEIRAS sobre a consulta a seguir? SELECT DISTINCT colors, sizes FROM items; 
_____ a. Cada cor aparecerá apenas uma vez no conjunto de resultados. 
_____ b. Cada tamanho aparecerá apenas uma vez no conjunto de resultados. 
X c. Combinações exclusivas de cor e tamanho aparecerão apenas uma vez no conjunto de resultados. 
_____ d. Cada combinação de cor e tamanho aparecerá mais de uma vez no conjunto de resultados.