Banco de Dados I – Aula 16A
Exercícios SQL
A empresa ABC VENDASCAR LTDA é uma emprea que atua em vendas de carros seminovos e usados multimarcas, principalmente em São Paulo (Capital) e no grande ABC.
Atualmente a empresa implantou o seguinte sistema para melhor controle seus orcamentos e suas vendas. Como a empresa NÃO faz vendas exclusiva de um fabricante, ela optou por trabalhar com veículos dos fabricantes Volkswagem, Chevrolet (GM), Ford e Fiat, para que não fique preso à uma única marca.
O cliente pode ser tanto pessoa física ou juridica, a qual na compra de mais de um carro em uma única negociação, é possível ter descontos mais vantajosos.
Primeiro é necessário que o cliente tenha um pré-cadastro no sistema para que os vendedores apresentem um orcamento prévio. Caso seja aprovado o orcamento, aí sim o orcamento se transforma em uma venda efetiva.
A empresa tem o seguinte esquema de tabelas em seu banco de dados:
FABRICANTE (COD_FAB, DESC_FAB)
MODELO (COD_MOD, DESC_MOD, COD_FAB)
COR (COD_COR, DESC_COR)
VEICULO (COD_VEI, COD_MOD, ANO_FABRICACAO, ANO_MODELO, VALOR_CUSTO, VALOR_VENDA, COD_COR)
CIDADE (COD_CID, DESC_CID, UF)
PLACA_VEICULO (COD_VEI, PLACA, COD_CID)
CLIENTE (COD_CLI, NOME_CLI, TIPO_PESSOA, COD_CID)
ORCAMENTO (COD_ORC, DATA_ORC, VALIDADE_ORC, COD_CLI)
ITEM_ORCAMENTO (COD_ORC, ITEM_ORC, COD_VEI, QUANTIDADE, VALOR_ORC, VALOR_DESCONTO)
VENDA (COD_VENDA, COD_ORC, DATA_VENDA)
ITEM_VENDA (COD_VENDA, ITEM_VENDA, COD_VEI, QUANTIDADE, VALOR_VENDA)
Baseado no cenário descrito, faça os exercícios em SQL e também crie o schema VENDASCAR, com as tabelas descritas acima. Por enquanto, as tabelas irão estar sem os relacionamentos necessários, porém tenha ciência de que os dados precisam ter relação entre si. Aconselhável fazer em dupla, onde 1 aluno pode fazer a criação do schema e tabelas e outro pode fazer as consultas SQL.
- Selecione o nome do veículo que tenha menor custo do estoque; (função de grupo)
- Liste todos os nomes de fabricantes de veículos que tiveram orcamentos feitos no período de 01/06/2017 a 30/06/2017;
- Selecione o cliente que mais orçou veículos no período de 01/01/2017 a 30/06/2017;
- Selecione as cores dos 5 veículos mais orçados período de 01/01/2017 a 30/06/2017; (pesquisar sobre limitação de registros)
- Liste as cidades dos carros vendidos dos fabricantes Volkswagem e Chevrolet (GM), no período de venda de 01/01/2017 a 31/12/2017;
- Liste os veículos (placa, cidade, fabricante, modelo) da cidade de Jundiaí, Campinas e São Paulo que foram vendidos entre 01/07/2017 a 31/12/2017;
- Selectione os orcamentos feitos (Código do orcamento, data, fabricante, modelo, quantidade, valor, desconto, subtotal (quantidade * valor – desconto) feitos no período do ano de 2016;
- Selecione os 5 veículos mais vendidos no período do ano de 2016; (use funções de Grupo)
- Selecione os orcamentos feitos no período de 01/01/2017 a 31/03/2017, porém que não foram fechados;
- Selecione a média de vendas de junho, julho e agosto de 2017; (função de grupo)
Vídeo 01
Vídeo 02
Banco de Dados I – Aula 15A
FUNÇÕES SQL
As funções são um recurso avançado do SQL e podem ser usadas para realizar:
– Executar cálculos usando dados;
– Modificar itens de dados individuais;
– Manipular saída para grupos de linhas;
– Formatar datas e números para exibição;
– Converter tipos de dados de coluna;
As funções SQL podem aceitar argumentos e sempre retornar um valor;
Há dois tipos de funções:
– Funções de uma única linha;
– Funções de várias linhas;
FUNÇÕES DE UMA ÚNICA LINHA
Essas funções operam somente linhas únicas e retornam um resultado por linha. Há dois tipos diferentes de funções de uma única linha.
– Caractere;
– Número;
– Data;
– Conversão;
Essas funções manipulam grupos de linha a fim de obter um resultado por grupo de linhas.
– Manipulam itens de dados;
– Aceitam argumentos e retornam um valor;
– Agem em cada linha retornada;
– Retornam um resultado por linha;
– Podem modificar o tipo de dados;
– Podem ser aninhadas;
Um argumento pode ser:
– Constante fornecida pelo usuário;
– Valor variável;
– Nome da coluna;
– Expressão;
Os recursos de funções de uma única linha:
– Atuam em cada linha retornada na consulta;
– Retornam um resultado por linha;
– Podem retornar um valor de dados de um tipo diferente do mencionado;
– Podem esperar um ou mais argumentos;
– Podem ser usados em cláusulas SELECT, WHERE e ORDER BY; podem ser aninhados;
– Funções de caractere: aceitam entrada de caractere e podem retornar valores de número e caractere;
– Funções numéricas: aceitam entrada numérica e reotrnam valores numéricos;
– Funções de data: operam sobre valores do tipo de dados data (todas as funções de data retornam data, exceto a função MONTHS_BETWEEN, que retorna um número)
– Funções de conversão: Convertem um valor de tipo de dados para outro;
– Funções gerais:
- Função NVL;
- Função DECODE;
FUNÇÕES DE CARACTERE
Podem ser divididas em duas características:
–Funções de conversão de maiúscula e minúscula;
- LOWER, UPPER e INITCAP
–Funções de manipulação de caractere;
- CONCAT, SUBSTR, LENGTH, INSTR, LPAD, TRIM
FUNÇÕES DE CONVERSÃO DE MAIÚSCULAS / MINÚSCULAS
– LOWER: Converte string de caracteres com letras maiúsculas e minúsculas ou só maiúsculas para letras minúsculas;
– UPPER: Converte string de caracteres com letras maiúculas e minúsculas ou só minúsculas para letras maiúsculas;
– INITCAP: Converte a primeira letra de cada palavra para maiúscula e mantém as outras letras em minúsculas;
SQL> SELECT UPPER(NMDEPTO)
2 FROM DEPTO;
SQL> SELECT LOWER(NMDEPTO)
2 FROM DEPTO;
SQL> SELECT INITCAP(NMDEPTO)
2 FROM DEPTO;
SQL> SELECT UPPER(NMDEPTO)
2 FROM DEPTO
3 WHERE UPPER(NMDEPTO) LIKE '%A%';FUNÇÕES DE MANIPULAÇÃO DE CARACTERE
– CONCAT, SUBSTR, LENGTH, INSTR, LPAD e TRIM são as seis funções de manipulação de caractere;
– CONCAT: Une valores de junção (Voce está limitado a usar dois parâmetros com CONCAT);
– SUBSTR: Extrai uma string de determinado tamanho;
– LENGTH: Exibe o tamanho de uma string como um valor numérico;
– INSTR: Localiza a posição numérica do caractere nomeado;
– LPAD: Preenche o valor do caractere justificado à direita;
– RPAD: Preenche o valor de caractere justificado à esquerda;
– TRIM: Organiza cabeçalho ou caracteres de fim de linha (ou os dois) a partir de uma string de caractere;
SQL> SELECT CONCAT(NRMATRIC, NMFUNC),
2 LENGTH(NMFUNC),
3 INSTR(NMFUNC,’A’)
4 FROM FUNCIONARIO
5 WHERE SUBSTR(NMDEPTO,1,2) = 'JO';FUNÇÕES NUMÉRICAS
ROUND: Arredonda valor para determinado decimal;
- ROUND(45.926,2) –> 45.93
TRUNC: Trunca valor para determinado decimal;
- ROUND(45.926,2) –> 45.92
MOD: Retorna o restante da divisão;
- MOD(1600,300) –> 100
USANDO A FUNÇÃO ROUND
SQL> SELECT ROUND(45.923,2),
2 ROUND(45.923,0),
3 ROUND(45.923,-1)
4 FROM DUAL;USANDO A FUNÇÃO TRUNC
SQL> SELECT TRUNC(45.923,2),
2 TRUNC(45.923),
3 TRUNC(45.923,-1)
4 FROM DUAL;USANDO A FUNÇÃO MOD
SQL> SELECT MOD(VRSALARIO,2)
2 FROM CARGO;TRABALHANDO COM DATAS
O Oracle armazena datas em um formato numérico interno: século, ano, mês, dia, horas, minutos, Segundo;
O formato de data default é DD-MON-YY;
SYSDATE é uma função de retorno de data e hora;
DUAL é uma tabela fictícia usada para visualizar SYSDATE;
EXIBINDO A DATA ATUAL
SQL> SELECT SYSDATE
2 FROM DUAL;ARITMÉTICA COM DATAS
Adicionar ou subtrair um número de, ou para, uma data para um valor de data resultante;
Subtrair duas datas a fim de localizer o número de dias entre estras datas;
Adicionar horas por uma data dividindo o número de horas por 24;
ARITMÉTICA COM DATAS
USANDO OPERADORES ARITMÉTICOS COM DATAS
SQL> SELECT NMFUNC,
2 (SYSDATE – DTADM) / 7 SEMANA
3 FROM FUNCIONARIO;FUNÇÕES COM DATAS
FUNÇÕES COM DATAS
MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
--> 19.6774194
ADD_MONTHS('11-JAN-96',6)
--> ’11-JUL-94’
NEXT_DAY('01-SEP-95','FRIDAY')
--> '08-SEP-95'
LAST_DAY('01-SEP-95')
--> '30-SEP-95'
ROUND('25-JUL-95','MONTH')
--> 01-AUG-95
ROUND('25-JUL-95','YEAR')
--> 01-JAN-96
TRUNC('25-JUL-95','MONTH')
--> 01-JUL-95
TRUNC('25-JUL-95','YEAR')
--> 01-JAN-95
Vídeo 01
Banco de Dados I – Aula 14C
EXERCÍCIOS: Utilize o Schema HR do Oracle para resolver os problemas abaixo:
1 – Selecione todos os ids, nomes e sobrenomes de empregados, que estejam nas faixas de salários de 0 a 2000 e maiores ou iguais a 5000. Dê 3 tipos de consultas para resolver esse problema)
2 – Selecione todos os ids e nomes de departamentos, o id do gerente do departamento que estejam nos id de locais entre 1500 a 2000;
3 – Selecione todos os nomes de departamentos e nomes dos gerentes de departamentos onde a cidade de localização seja Sao Paulo (faça pela antiga e nova sintaxa. Se possível, resolva também em algebra relacional)
4 – Selecione todos os ids, nomes e sobrenome de empregados que possuem salario maior que 5000 (Dê duas soluções para este problema. Se possível, resolva também em algebra relacional)
5 – Selecione todos ids, nomes e sobrenomes (como um único campo – nome completo) de empregados, a data de admissao (hire date),data de inicio e fim (end_date) dos determinados serviços (jobs). Obs: um empregado pode ter trabalhado em mais de um servico em um período de tempo; (Dê 2 soluções para esse problema. Se possível, resolva também em algebra relacional)
6 – Selecione todos o nome da região, o nome do pais que os nomes de paises iniciem com as letras A,B,J (Dê 2 soluções para esse problema. Se possível, resolva também em algebra relacional)
7 – Selecione todos os id´s e nomes dos gerentes, ids e nomes de departamentos gerenciados por esses gerentes. (De 3 soluções para esse problema)
8 – Selecione todos os ids e nomes de departamentos, a qual não possuem nenhum empregado alocado (dê 2 soluções para esse problema);
9 – Selecione todos os ids e nomes de empregados, o nome do departamento, o nome do pais e o nome da região, onde as regiões devem ser iguais a Europe e Asia (dê 3 soluções para esse problema);
Vídeo 01
Banco de Dados I – Aula 14B
USANDO O SCHEMA HR DO ORACLE
JUNÇÃO DE TABELA (SINTAXE ANTIGA)
SQL> SELECT tabela1.coluna, tabela2.coluna
2 FROM tabela1, tabela2
3 WHERE tabela1.coluna = tabela2.coluna;
JUNÇÃO DE TABELA (SINTAXE NOVA)
SQL> SELECT tabela1.coluna, tabela2.coluna
2 FROM tabela1
3 INNER JOIN tabela2
4 ON tabela1.coluna =
5 tabela2.coluna;
GERANDO UM PRODUTO CARTESIANO (SINTAXE ANTIGA)
Gera-se um produto cartesiano caso uma condição de junção seja omitida.
SQL> SELECT FIRST_NAME,
2 DEPARTMENT_NAME
3 FROM EMPLOYEES,
4 DEPARTMENTS;
GERANDO UM PRODUTO CARTESIANO (SINTAXE NOVA)
SQL> SELECT FIRST_NAME,
2 DEPARTMENT_NAME
3 FROM EMPLOYEES
4 CROSS JOIN DEPARTMENTS;
GERANDO REGISTROS COM JUNÇÕES IDÊNTICAS (SINTAXE ANTIGA)
SELECT FIRST_NAME,
EMPLOYEES.DEPARTMENT_ID,
DEPARTMENTS.DEPARTMENT_ID,
DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID =
DEPARTMENTS.DEPARTMENT_ID
GERANDO REGISTROS COM JUNÇÕES IDÊNTICAS (SINTAXE NOVA)
SELECT FIRST_NAME,
EMPLOYEES.DEPARTMENT_ID,
DEPARTMENTS.DEPARTMENT_ID,
DEPARTMENT_NAME
FROM EMPLOYEES
INNER JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENT_ID =
DEPARTMENTS.DEPARTMENT_ID
CONDIÇÕES DE PESQUISA ADICIONAIS USANDO O OPERADOR AND (SINTAXE ANTIGA)
SELECT EMPLOYEE_ID, FIRST_NAME,
DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID =
DEPARTMENTS.DEPARTMENT_ID
AND FIRST_NAME = 'Diana';
CONDIÇÕES DE PESQUISA ADICIONAIS USANDO O OPERADOR AND (SINTAXE NOVA)
SELECT EMPLOYEE_ID, FIRST_NAME,
DEPARTMENT_NAME
FROM EMPLOYEES
INNER JOIN DEPARTMENTS
ON EMPLOYEES.DEPARTMENT_ID =
DEPARTMENTS.DEPARTMENT_ID
WHERE FIRST_NAME = 'Diana';
UNINDO MAIS DE DUAS TABELAS (SINTAXE ANTIGA)
SELECT E.FIRST_NAME,
J.JOB_TITLE,
D.DEPARTMENT_NAME
FROM EMPLOYEES E,
JOBS J,
DEPARTMENTS D
WHERE E.JOB_ID = J.JOB_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
UNINDO MAIS DE DUAS TABELAS (SINTAXE NOVA)
SELECT E.FIRST_NAME,
J.JOB_TITLE,
D.DEPARTMENT_NAME
FROM EMPLOYEES E
INNER JOIN JOBS J
ON E.JOB_ID = J.JOB_ID
INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
JUNÇÕES NÃO-IDÊNTICAS (SINTAXE ANTIGA)
SELECT E.FIRST_NAME, E.LAST_NAME,
E.SALARY, J.MIN_SALARY, J.MAX_SALARY, J.JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE E.SALARY
BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
JUNÇÕES NÃO-IDÊNTICAS (SINTAXE NOVA)
SELECT E.FIRST_NAME, E.LAST_NAME,
E.SALARY, J.MIN_SALARY, J.MAX_SALARY, J.JOB_TITLE
FROM EMPLOYEES E
JOIN JOBS J
ON E.SALARY BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
EXEMPLO DE JUNÇÕES EXTERNAS (SINTAXE ANTIGA)
SELECT E.FIRST_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E,
DEPARTMENTS D
WHERE E.DEPARTMENT_ID(+) =
D.DEPARTMENT_ID;
EXEMPLO DE JUNÇÕES EXTERNAS (SINTAXE NOVA)
SELECT E.FIRST_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E,
LEFT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID =
D.DEPARTMENT_ID;
EXEMPLO DE JUNÇÕES EXTERNAS (SINTAXE ANTIGA)
SELECT E.FIRST_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E,
DEPARTMENTS D
WHERE E.DEPARTMENT_ID =
D.DEPARTMENT_ID(+);
EXEMPLO DE JUNÇÕES EXTERNAS (SINTAXE NOVA)
SELECT E.FIRST_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E
RIGHT JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID =
D.DEPARTMENT_ID;
EXEMPLO DE AUTOJUNÇÕES (SINTAXE ANTIGA)
SELECT WORKER.FIRST_NAME
||' TRABALHA PARA ' ||
MANAGER.FIRST_NAME
FROM EMPLOYEES WORKER,
EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID =
MANAGER.EMPLOYEE_ID;
EXEMPLO DE AUTOJUNÇÕES (SINTAXE NOVA)
SELECT WORKER.FIRST_NAME
||' TRABALHA PARA ' ||
MANAGER.FIRST_NAME
FROM EMPLOYEES WORKER
LEFT JOIN EMPLOYEES MANAGER
ON WORKER.MANAGER_ID =
MANAGER.EMPLOYEE_ID;Video 01
Banco de Dados I – Aula 14A
EXIBINDO DADOS DE VÁRIAS TABELAS
Há situações em que precisamos buscar dados de várias tabelas, onde um select simples não consegue obter esses dados;
Nesses casos, é necessário utilizar dados a partir de uma ou mais tabelas;
Para isso, precisamos fazer junção de tabelas;
O QUE É JUNÇÃO?
Use a junção para consultar dados a partir de uma ou mais tabelas;
Criar uma condição de junção na cláusula WHERE;
Prefixar o nome da coluna com o nome da tabela quando o mesmo nome da coluna aparecer em mais de uma tabela
Exemplo de junção
SQL> SELECT tabela1.coluna, tabela2.coluna
2 FROM tabela1, tabela2
3 WHERE tabela1.coluna = tabela2.coluna;
USANDO O SCHEMA HR DO ORACLE
PRODUTO CARTESIANO
Um produto cartesiano é formado quando:
– Uma condição de junção estiver omitida;
– Uma condição de junção estiver inválida;
– Todas as linhas na primeira tabela estão unidas a todas as linhas da segunda tabelas;
Para evitar um produto Cartesiano, sempre inclua uma condição de junção válida em uma cláusula WHERE;
GERANDO UM PRODUTO CARTESIANO
Gera-se um produto cartesiano caso uma condição de junção seja omitida.
SQL> SELECT FIRST_NAME,
2 DEPARTMENT_NAME
3 FROM EMPLOYEES,
4 DEPARTMENTS;
TIPOS DE JUNÇÃO
Há dois tipos principais de condições de junção:
– Junção idênticas;
– Junção não-idêntcias;
Métodos de junção adicional incluem:
– Junções externas;
– Autojunções;
– Operadores de conjunto;
JUNÇÃO IDÊNTICA
RECUPERANDO REGISTROS COM JUNÇÕES IDÊNTICAS
SQL>SELECT FIRST_NAME,
EMPLOYEES.DEPARTMENT_ID,
DEPARTMENTS.DEPARTMENT_ID,
DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID =
DEPARTMENTS.DEPARTMENT_ID;QUALIFICANDO NOMES DE COLUNA AMBÍGUAS
Use os prefixos de tabela para qualificar nomes de colunas que estão em várias tabelas;
Diferencie colunas que possuem nomes idênticos, mas que residam em tabelas diferentes usando apelidos de coluna;
CONDIÇÕES DE PESQUISA ADICIONAIS USANDO O OPERADOR AND
Além da junção, é possível ter critérios para a cláusula WHERE;
SELECT EMPLOYEE_ID, FIRST_NAME,
DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID =
DEPARTMENTS.DEPARTMENT_ID
AND FIRST_NAME = 'Diana';USANDO APELIDOS DE TABELA
Apelidos de tabelas podem ter um tamanho de até 30 caracteres;
Se um apelido de tabela for usado para um determinado nome de tabela na cláusula FROM, deve-se utilize-lo para toda a instrução SELECT;
Apelidos devem ser significativos;
O apelido de tabela é válido somente para a instrução SELECT atual;
USANDO APELIDOS DE TABELA
SELECT E.FIRST_NAME,
J.JOB_TITLE,
D.DEPARTMENT_NAME
FROM EMPLOYEES E,
JOBS J,
DEPARTMENTS D
WHERE E.JOB_ID = J.JOB_ID
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;
JUNÇÕES NÃO-IDÊNTICAS
O relacionamento de junção não-idênticas significa que não há nenhum coluna correspondente diretamente entre as duas tabelas;
SELECT E.FIRST_NAME, E.LAST_NAME,
E.SALARY, J.MIN_SALARY, J.MAX_SALARY,
J.JOB_TITLE
FROM EMPLOYEES E, JOBS J
WHERE E.SALARY
BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
Se uma linha não satisfazer uma condição de junção, a linha não aparecerá no resultado da consulta.
Use uma junção externa para consultar também todas as linhas que em geral não atendem à condição de junção.
O operador de junção externo é um sinal de adição (+);
EXEMPLO DE JUNÇÕES EXTERNAS
SELECT E.FIRST_NAME,
D.DEPARTMENT_NAME
FROM EMPLOYEES E,
DEPARTMENTS D
WHERE E.DEPARTMENT_ID(+) =
D.DEPARTMENT_ID
O operador da junção externa pode aparecer somente de um lado da expressão – o lado que possui informações ausentes. Ele retorna estas linhas de uma tabela que não possui correspondênciadireta em outra tabela;
Uma condição envolvendo uma junção externa não pode usar o operador IN ou vincular a outra condição pelo operador OR;
AUTOJUNÇÕES
Algumas vezes será necessário unir uma tabela a ela mesma.
EXEMPLO DE AUTOJUNÇÕES
SELECT WORKER.FIRST_NAME
||' TRABALHA PARA ' ||
MANAGER.FIRST_NAME
FROM EMPLOYEES WORKER,
EMPLOYEES MANAGER
WHERE WORKER.MANAGER_ID =
MANAGER.EMPLOYEE_ID;
Vídeo 01
