Arquivos de Categoria: Desenvolvimento em Banco de Dados

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.

  1. Selecione o nome do veículo que tenha menor custo do estoque; (função de grupo)
  2. Liste todos os nomes de fabricantes de veículos que tiveram orcamentos feitos no período de 01/06/2017 a 30/06/2017;
  3. Selecione o cliente que mais orçou veículos no período de 01/01/2017 a 30/06/2017;
  4. 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)
  5. 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;
  6. 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;
  7. 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;
  8. Selecione os 5 veículos mais vendidos no período do ano de 2016; (use funções de Grupo)
  9. Selecione os orcamentos feitos no período de 01/01/2017 a 31/03/2017, porém que não foram fechados;
  10. Selecione a média de vendas de junho, julho e agosto de 2017; (função de grupo)

 

Vídeo 01

 

Vídeo 02

 

Anúncios

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

ScreenHunter_298 Nov. 11 19.31

USANDO OPERADORES ARITMÉTICOS COM DATAS

SQL> SELECT NMFUNC,
2    (SYSDATE – DTADM) / 7 SEMANA
3  FROM FUNCIONARIO;

FUNÇÕES COM DATAS

ScreenHunter_299 Nov. 11 19.32

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:

hr

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

hr

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 12A

SOBRE O PL/SQL

O PL/SQL (Procedural Language/SQL) é uma extensão do SQL com recursos de design de linguagem de programação, sendo uma extensão de linguagem procedural da Oracle;

Incorpora muitos recursos avançados feitos em linguagem de programação projetadas durante as décadas de 70 e 80;

É uma tecnologia empregada pelo Oracle Server e por algumas ferramentas Oracle;

SCRIPT PARA SER EXECUTADO VIA LINHA DE COMANDO NO SQLPLUS

Salvar em arquivo texto dentro do linux e executar via sqlplus

create tablespace banco1 datafile '/u01/app/oracle/oradata/XE/banco1.dbf' size 10m autoextend on next 5m maxsize 50m online permanent extent management local autoallocate segment space management auto;

create user banco1 identified by banco1 default tablespace banco1 temporary tablespace temp;

grant create session, connect, resource to banco1;

alter user banco1 quota unlimited on banco1;

connect banco1/banco1;

CREATE TABLE DEPTO (
  CDDEPTO CHAR(02) PRIMARY KEY,
  NMDEPTO VARCHAR(30),
  RAMAL NUMBER(3)
);

CREATE TABLE CARGO (
  CDCARGO CHAR(02) PRIMARY KEY,
  NMCARGO VARCHAR(30),
  VRSALARIO NUMBER(8,2)
);

CREATE TABLE FUNCIONARIO (
  NRMATRIC NUMBER(04) PRIMARY KEY,
  NMFUNC   VARCHAR(30),
  DTADM    DATE,
  SEXO     CHAR(01),
  CDCARGO  CHAR(02),
  CDDEPTO  CHAR(02),
  CONSTRAINT FK_FUNC_CDCARGO FOREIGN KEY(CDCARGO) REFERENCES CARGO(CDCARGO),
  CONSTRAINT FK_FUNC_CDDEPTO FOREIGN KEY(CDDEPTO) REFERENCES DEPTO(CDDEPTO)
);

INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C1','COZINHEIRA',350);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C3','AUX ESCRITORIO',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C7','VIGIA',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C2','MECANICO',750);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C5','GERENTE',2300);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C4','ESCRITURARIO',600);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C8','PROGRAMADOR',NULL);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C9','ANALISTA_DE_SISTEMAS',2100);

COMMIT;

INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D1','ADMINISTRACAO',221);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D2','OFICINA',235);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D3','SERVICOS GERAIS',243);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D4','VENDAS',258);
COMMIT;

INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1001,'JOAO SAMPAIO','10-AUG-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1004,'LUCIO TORRES','02-MAR-94','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1034,'ROBERTO PEREIRA','23-MAY-92','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1021,'JOSE NOGUEIRA','10-NOV-94','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1029,'RUTH DE SOUZA','05-JAN-93','F','C1','D3');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1095,'MARIA DA SILVA','03-SEP-92','F','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1023,'LUIZ DE ALMEIDA','12-JAN-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1042,'PEDRO PINHEIRO','29-JUL-94','M','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1048,'ANA SILVEIRA','01-JUN-93','F','C5','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1015,'PAULO RODRIGUES','17-AUG-92','M','C2','D2');
COMMIT;

INSTRUÇÃO SELECT BÁSICA

SELECT [DISTINCT] {*, coluna [apelido],…}
FROM tabela;

Uma cláusula SELECT especifica as colunas a serem exibidas;

Uma cláusula FROM especifica a tabela que contém as colunas listadas na cláusula SELECT

Na sintaxe:

SELECT –> é a lista de uma ou mais colunas

DISTINCT –> suprime os itens duplicados

* –> selectiona todas as colunas

coluna –> seleciona a coluna nomeada

apelido –> fornece cabeçalhos diferentes às   colunas selecionadas

FROM –> tabela especifica a tabela contend   as colunas

CRIANDO INSTRUÇÕES SQL

Instruções SQL sem distinção entre maiusculas / minusculas;

Instruções SQL podem estar em uma ou mais linhas;

Palavras-chave não podem ser abreviadas ou divididas entre as linhas;

Normalmente, as cláusulas são colocadas em linhas separadas;

Guias e indentações são usadas para aperfeiçoar a legibilidade;

SELECIONANDO COLUNAS

SQL> SELECT *
  2  FROM DEPTO;

SQL> SELECT CDDEPTO, NMDEPTO, RAMAL
  2  FROM DEPTO;

SQL> SELECT CDDEPTO, NMDEPTO
  2  FROM DEPTO;

AJUSTANDO CABEÇALHO DAS COLUNAS

SQL> COL "CDDEPTO" FORMAT "A15";

SQL> SELECT *
  2  FROM DEPTO;

A15 –>quantidade de caracteres a ser apresentado

DEFAULTS DE CABEÇALHOS DE COLUNA

Justificativa default

Esquerda: dados de caractere e data

Direita: Dados numéricos

Exibição default: Letra maiúscula

SQL> SELECT cddepto, nmdepto
  2  FROM DEPTO; 

Obs –> mesmo digitando os campos selecionandos em minúsculo, será exibido em maiúsculo

EXPRESSÕES ARITMÉTICAS

Criar expressões com dados NUMBER e DATE usando operadores aritméticos.

OPERADOR DESCRIÇÃO
+ Adicionar
Subtrair
* Multiplicar
/ Dividir

USANDO OPERADORES ARITMÉTICOS

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  VRSALARIO + 1000
   4  FROM CARGO;

PRECEDENCIA DE OPERADORES

A multiplicação e a divisão têm prioridade sobre a adição e a substração;

Os operadores com a mesma prioridade são avaliados da esquerda para a direita;

Os parênteses são usados para forçar a avaliação priorizada e para esclarecer as instruções;

Exemplo 1:

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  12 * VRSALARIO + 100
   4  FROM CARGO;

Exemplo 2:

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  12 * (VRSALARIO + 100)
   4  FROM CARGO;

DEFININDO UM VALOR NULO

Um valor nulo não está disponível, não é atribuído, é desconhecido ou não é aplicável;

Um valor nulo não é o mesmo que um zero ou um espaço em branco;

SQL> SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2  FROM CARGO;

VALORES NULOS NAS EXPRESSÕES ARITMÉTICAS

Se qualquer valor da coluna em uma expressão aritmética for nulo, o resultado será nulo;

SQL> SELECT CDCARGO, NMCARGO, 
  2  VRSALARIO + 100
  3  FROM CARGO;

DEFININDO UM APELIDO (ALIAS) PARA COLUNA

Renomeia um cabeçalho de coluna;

É útil para cálculos;

Segue imediatamente o nome da coluna;

Palavra-chave “as” é opcional entre o nome da coluna e o apelido;

Necessita de aspas duplas caso contenha espaço ou caracteres especiais ou faça a distinção entre maiúscula e minúscula;

USANDO UM APELIDO DE COLUNA

SQL> SELECT CDCARGO AS CODIGO_DO_CARGO, 
   2 NMCARGO AS "Nome do Cargo", 
   3 VRSALARIO VALOR$SALARIO
   4 FROM CARGO;

Observação: Os caracteres especiais # e $ são permitidos serem utilizados em alias sem utilização das aspas duplas;

OPERADOR DE CONCATENAÇÃO

Concatena colunas ou strings de caractere a outras colunas;

É representado por duas barras verticais “||” (pipe);

Cria uma coluna resultante que é uma expressão de caracteres

USANDO UM OPERADOR DE CONCATENAÇÃO

SQL> SELECT CDCARGO || NMCARGO 
   2	AS codigo_nome 
   3  	FROM CARGO;

STRING LITERAIS DE CARACTERES

Uma literal é um caractere, um número ou uma data incluída na lista SELECT;

Valores literais de caractere e data devem estar entre aspas simples;

Cada string de caractere é gerada um vez para linha retornada;

LINHAS DUPLICADAS

A exibição das consultas é de todas as linhas, incluindo linhas duplicadas:

SQL> SELECT CDCARGO
   2  FROM FUNCIONARIO;

ELIMINANDO LINHAS DUPLICADAS

Elimine as linhas duplicadas usando a palavra chave DISTINC na cláusula SELECT:

SQL> SELECT DISTINCT CDCARGO
   2  FROM FUNCIONARIO;

O DISTINCT atua sobre a linha do select, não sendo possível ter 2 DISTINCTS na mesma SELECT

LIMITANDO LINHAS SELECIONADAS

Restringe as linhas retornadas usando a cláusula WHERE:

A cláusula WHERE pode comparer valores em colunas, valores literais, expressões aritméticas ou funções. É formada por tres elementos:

– Nome da coluna;
– Operadores de comparação;
– Nome da coluna, constant ou lista de valores;

USANDO A CLÁUSULA WHERE

SQL> SELECT NRMATRIC, NMFUNC
   2  FROM FUNCIONARIO
   3  WHERE CDCARGO = 'C2';

STRINGS DE CARACTERES E DATAS

As strings de caractere e valores de dados aparecem entre aspas simples;

Os valores de caractere fazem distinção entre maiusculas e minúculas e o valores de data diferenciam formatos;

O format de data default é DD-MON-YY.

Exemplo:

SQL> SELECT NRMATRIC, NMFUNC, DTADM
   2 FROM FUNCIONARIO
   3 WHERE SEXO = 'F' 
   4 AND DTADM = '01-JUN-93';

OPERADORES DE COMPARAÇÃO

OPERADOR SIGNIFICADO
= Igual
> Maior do que
>= Maior do que ou igual a
< Menor
<= Menor ou igual a
Diferente de

UTILIZAÇÃO DOS OPERADORES DE COMPARAÇÃO

Use os operadores de comparação em condições que comparam uma expressão a outra.

Eles são usados na cláusula WHERE seguindo os exemplos:

WHERE hiredate = '01-JAN-95'

WHERE SAL >= 500

WHERE ENAME = 'SMITH'

OUTROS OPERADORES DE COMPARAÇÃO

OPERADOR SIGNIFICADO
BETWEEN … AND… Entre dois valores (inclusive)
IN (list) Vincula qualquer um de uma lista de valores
LIKE Vincula um padrão de caracteres
IS NULL É um valor nulo

USANDO O OPERADOR BETWEEN

Use o operador BETWEEN apra exibir linhas baseadas em uma faixa de valores

SQL>SELECT NMCARGO, VRSALARIO
  2 FROM CARGO
  3 WHERE VRSALARIO BETWEEN 450 AND 750;

USANDO O OPERADOR IN

Use o operador IN para testar os valores de uma lista

SQL>SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
  2 FROM FUNCIONARIO
  3 WHERE CDCARGO IN ('C1','C3','C4');

USANDO O OPERADOR LIKE

Use o operador LIKE para executar pesquisas curinga com valores de string válidas;

As condições de pesquisa podem conter caracteres literais ou números;

– % denota zero ou muitos caracteres;

– _ (underline) denota um caractere;

Voce pode combiner caracteres de vinculação de padrão;

SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE 'JO%';
SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE '%RO%'
SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE '_O%'

USANDO A OPÇÃO ESCAPE

Quando for necessário ter uma correspondencia exata para os caracteres ‘%’ e ‘_’, use a opção ESCAPE

SQL> SELECT CDCARGO, NMCARGO
   2 FROM CARGO
   3 WHERE NMCARGO LIKE '%@_%' ESCAPE '@';

USANDO O OPERADOR IS NULL

O operador IS NULL testa valores que são nulos. Um valor nulo significa que o valor não está disponível, não-atribuído, desconhecido ou não aplicável. Assim, não é possível testar com “=” porque um valor nulo não pode ser igual ou desigual a qualquer valor.

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE VRSALARIO IS NULL;

OPERADORES LÓGICOS

OPERADOR SIGNIFICADO
AND Retorna TRUE se as condições de componmentes forem TRUE
OR Retorna TRUE se cada condição de componente for TRUE
NOT Retorna TRUE se a condição seguinte for FALSE

Um operador lógico combina o resultado de duas condições de componente para produzir um único resultado com base neles ou inverter o resultado para a condição única. Três operadores lógicos estão disponíveis no SQL: AND, OR e NOT

USANDO O OPERADOR AND

AND exige que ambas as conduições sejam TRUE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2   FROM CARGO
   3   WHERE CDCARGO = 'C5'
   4   AND VRSALARIO >= 2000;

TABELA VERDADE AND

AND TRUE FALSE NULL
TRUE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL NULL FALSE NULL

USANDO O OPERADOR OR

OR exige que cada condição seja TRUE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE CDCARGO = 'C5'
   4 OR VRSALARIO >= 2000;

TABELA VERDADE OR

OR TRUE FALSE NULL
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL

USANDO O OPERADOR NOT

NOT nega o operador usado na operação

O operador NOT pode ser utilizado também com outros operadores SQL, como BETWEEN e LIKE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE VRSALARIO IS NOT NULL;

TABELA VERDADE NOT

NOT TRUE FALSE NULL
TRUE FALSE TRUE NULL

EXEMPLO DO OPERADOR NOT

... WHERE CDDEPTO NOT IN ('D1','D3');

... WHERE VRSALARIO NOT BETWEEN 50 AND 500;

... WHERE NMCARGO NOT LIKE 'VIG%';

... WHERE RAMAL IS NOT NULL;

REGRAS DE PRECEDÊNCIA

ORDEM DE AVALIAÇÃO OPERADOR
1 Todos os operadores de comparação
2 NOT
3 AND
4 OR

Sobreponha regras de precedência usando parênteses

REGRAS DE PRECEDÊNCIA

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE CDCARGO = 'C1'
   4 OR  CDCARGO = 'C7'
   5 AND VRSALARIO >= 350

A ordem de execução usando regras de precedencia é:

A primeira condição é que o CDCARGO seja igual a ‘C7’ e o VRSALARIO SEJA >= 350

A segunda condição é que o cargo CDCARGO seja igual a ‘C1’;

CLÁUSULA ORDER BY

ORDER BY Classifica as linhas de 2 formas:

–ASC: ordem crescente, default (do menor para o maior);

–DESC: ordem decrescente (do maior para o menor);

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 ORDER BY NMCARGO;
SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2  FROM CARGO
   3  ORDER BY CDCARGO ASC;
SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 ORDER BY VRSALARIO DESC;

CLASSIFICANDO POR APELIDO DE COLUNA

SQL> SELECT NMCARGO, VRSALARIO,
   2 VRSALARIO * 12 AS SAL_ANUAL
   3 FROM CARGO
   4 ORDER BY SAL_ANUAL

CLASSIFICANDO POR POSIÇÃO DE COLUNA

SQL> SELECT NMCARGO, VR_SALARIO,
   2 VRSALARIO * 12 AS SAL_ANUAL
   3 FROM CARGO
   4 ORDER BY 2

CLASSIFICANDO POR VÁRIAS COLUNAS

SQL> SELECT NMFUNC, SEXO, DTADM
   2 FROM FUNCIONARIO
   3 ORDER BY NMFUNC,DTADM DESC;

CONECTANDO DO SHELL DO LINUX PARA O SQL/PLUS

Como SYSDBA

[oracle@oracle ~]$ sqlplus / as sysdba

Como usuário comum

[oracle@oracle ~]$ sqlplus banco1/banco1

CONSULTANDO TABELAS DO SCHEMA NO SQL/PLUS

SQL> SELECT TABLE_NAME
   2 FROM USER_TABLES;

CONSULTANDO A ESTRUTURA DE UMA TABELA NO SQL/PLUS

SQL> DESC FUNCIONARIO;

SAINDO DO SQL/PLUS

SQL> exit;

 

Vídeo – Parte 01

 

Vídeo – Parte 02

 

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

ROMANO DBA

Administração de Bancos de Dados

Tércio Costa, Oracle Developer, OCE SQL

Guia de estudos para certificação ORACLE SQL Expert(1Z0-047) e PL/SQL(1Z0-144)

Strate SQL

Data Adventures with an Architect