SQL ORACLE

Sumário:

• Data Retrieve Statement DRS ou DQL

  • SELECT

• Data manipulation language (DML)

• Data definition language (DDL)

  • CREATE

  • ALTER

  • DROP

  • TRUNCATE

  • RENAME

  • INDICE

• Data Transaction control (DTL)

  • COMMIT

  • ROLLBACK

  • SAVEPOINT

• Data control language (DCL)

Data Retrieve Statement (DRS) ou DQL (query language)

(Também chamada de DRS)

Utlizada para especificar consultas. É composta por várias cláusulas e opções, o que possibilita a elaboração de consultas simples à complexas.

Comandos:

• SELECT

SELECT

• Você pode projetar todas as colunas
• Você pode projetar apenas algumas colunas (coluna,coluna,…)
• Você pode projetar expressões (exemplo: salario*2, ‘TESTE’)
• Você pode colocar apelidos “alias” para as colunas ou expressões (operação renomear para colunas na álgebra relacional)

// Projetando (mostrando) todas as colunas de uma tabela
SELECT * FROM departments;

// Projetando apenas algumas colunas da tabela
SELECT department_id, location_id FROM departments;

// Usando expressões aritiméticas
SELECT last_name, salary, salary + 300 FROM employees;

// Usando apelido de colunas
SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees;

// Operação de concatenação de strings
SELECT last_name||job_id AS "Employees" FROM employees;

// Trabalhando com strings no SELECT
SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

// A exibição default de consultas mostra todas as linhas, inclusive as linhas duplicadas
SELECT DISTINCT department_id FROM employees;

// Restrinja as linhas retornadas com a cláusula WHERE
SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees WHERE departament_id = 90;

// Use a condição BETWEEN para exibir linhas com base em uma faixa de valores
SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ;

// Use a condição de associação IN para testar os valores de uma lista
SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201) ;

// Use a condição LIKE para executar pesquisas com curinga de valores válidos de strings de pesquisa
// % indica zero ou vários caracteres
SELECT first_name FROM employees WHERE first_name LIKE 'S%' ;

// _ indica um caractere
SELECT last_name FROM employees WHERE last_name LIKE '_o%' ;

// Teste valores nulos com o operador IS NULL
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ;

// AND exige que as duas condições sejam verdadeiras
SELECT employee_id, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%';

// OR exige que uma das condições seja verdadeira
SELECT employee_id, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%';

// Usando o Operador NOT
SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

// Ordenar as linhas recuperadas com a cláusula ORDER BY
SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date;

// Ordenação em ordem decrescente
SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date DESC;

// Ordenação por apelido de coluna
SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY annsal;

// Ordenação por várias colunas
SELECT last_name, department_id, salary FROM employees ORDER BY department_id, salary DESC;

JOIN

SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

SELECT DEPARTMENT_NAME, CITY
FROM DEPARTMENTS
NATURAL JOIN LOCATIONS;

SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES JOIN DEPARTMENTS
USING (DEPARTMENT_ID);

SELECT DEPARTMENT_NAME
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
WHERE JOB_ID = 'IT_PROG';


SELECT D.DEPARTMENT_NAME, SUM(E.SALARY)
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
HAVING SUM(SALARY) > 50000;

SELECT E.FIRST_NAME
FROM EMPLOYEES E JOIN DEPARTMENTS D USING(DEPARTMENT_ID)
WHERE D.LOCATION_ID IS NOT NULL;

Exercícios

---- CONSULTA O NOME DO DEPARTAMENTO 
---- E O NOME DA CIDADE QUE ESTE DEPARTAMENTO ESTA LOCALIZADO 
SELECT D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D, LOCATIONS L
WHERE D.LOCATION_ID = L.LOCATION_ID;

SELECT D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D JOIN LOCATIONS L 
ON (D.LOCATION_ID = L.LOCATION_ID);

SELECT D.DEPARTMENT_NAME, L.CITY
FROM DEPARTMENTS D NATURAL JOIN LOCATIONS L
WHERE L.CITY = 'Seattle';

-- UM RELATORIO QUE MOSTRE TODOS OS EMPREGADOS
-- QUE TRABALHAM EM Seattle

SELECT E.FIRST_NAME, D.DEPARTMENT_NAME, L.CITY
FROM EMPLOYEES E JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
JOIN LOCATIONS L ON (L.LOCATION_ID = D.LOCATION_ID)
WHERE L.CITY = 'Seattle'
ORDER BY 1;

-- RELATORIO COM O NOME DOS EMPREGADOS
-- E O NOME DO SEU GERENTE 
SELECT E.FIRST_NAME "FUNCIONARIO", M.FIRST_NAME "GERENTE"
FROM EMPLOYEES E, EMPLOYEES M
WHERE E.MANAGER_ID = M.EMPLOYEE_ID
ORDER BY 1;

SELECT E.FIRST_NAME "FUNCIONARIO", M.FIRST_NAME "GERENTE" 
FROM EMPLOYEES E JOIN EMPLOYEES M
ON (E.MANAGER_ID = M.EMPLOYEE_ID)
ORDER BY 1;

SELECT E.EMPLOYEE_ID, E.FIRST_NAME "FUNCIONARIO", M.FIRST_NAME "GERENTE" 
FROM EMPLOYEES E LEFT OUTER JOIN EMPLOYEES M
ON (E.MANAGER_ID = M.EMPLOYEE_ID)
ORDER BY 1;

-- RELATORIO COM O NOME DE TODOS OS 
-- FUNCIONARIO E SEU DEPARTAMENTO

-- TRAZ TODOS OS FUNCIONARIOS, MAS NAO OS DEPARTAMENTOS SEM FUNCIONARIOS
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);

-- TRAZ TODOS OS DEPARTAMENTOS ATE OS QUE NAO TEM FUNCIONARIOS
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);

-- TRAZ TUDO DAS DUAS TABELAS
SELECT E.FIRST_NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);

-- Você precisa enviar um relatório dos funcionários que trabalham em Toronto. Faça uma
-- consulta para gerar o relatório, contendo o sobrenome, o cargo, o número do departamento e
-- o nome do departamento de todos os funcionários que trabalham em Toronto. 
SELECT 
    E.FIRST_NAME,
    E.JOB_ID,
    D.DEPARTMENT_NAME,
    L.CITY
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
JOIN LOCATIONS L 
ON (D.LOCATION_ID = L.LOCATION_ID)
WHERE L.CITY = 'Toronto';

-- Faça uma consulta que mostre o nome do funcionário, o código do funcionário, o nome do
-- seu gerente e o código do seu gerente. 
SELECT 
    E.LAST_NAME "EMPLOYEE",
    E.EMPLOYEE_ID "EMP#",
    M.LAST_NAME "MANAGER",
    M.EMPLOYEE_ID "MGR#"
FROM EMPLOYEES E LEFT OUTER JOIN EMPLOYEES M
ON (E.MANAGER_ID = M.EMPLOYEE_ID)
ORDER BY E.EMPLOYEE_ID, E.LAST_NAME;

-- Faça um relatório que mostre o nome de todos os funcionários e o nome do departamento
-- que o funcionário trabalha. Este relatório deve incluir os empregados que não trabalham em
-- nenhum departamento, assim como os departamentos que não tem nenhum empregado
-- trabalhando nele. 
SELECT 
    E.FIRST_NAME,
    D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
ORDER BY NVL(E.FIRST_NAME, 'A'), D.DEPARTMENT_NAME;

Data manipulation language (DML)

Utlizada para inserir, atualizar e apagar dados. Insere novas linhas no banco de dados, altera dados nas linhas já existentes e remove linhas do BD

Comandos:

• INSERT
• UPDATE
• DELETE
• MERGE

INSERT

• Adicione novas linhas a uma tabela usando a instrução INSERT
• Com esta sintaxe, apenas uma linha é inserida por vez.
• Insira uma nova linha com valores para cada coluna.
• Liste os valores na ordem default das colunas na tabela.
• Como alternativa, liste as colunas na cláusula INSERT.
• Delimite os valores de caractere e data com aspas simples.

// sem passar as colunas 
INSERT INTO departments VALUES (70, 'Public Relations', 100, 1700);

// passanso a ordem e as colunas
INSERT INTO departments(
    department_id
    , department_name
    , manager_id
    , location_id
) VALUES (70, 'Public Relations', 100, 1700);

// inserindo com valor NULL
INSERT INTO departments VALUES (100, 'Finance', NULL, NULL);

// Crie a instrução INSERT com uma subconsulta
INSERT INTO sales_reps(
    id
    , name
    , salary
    , commission_pct
) SELECT employee_id, last_name, salary, commission_pct FROM employees WHERE job_id LIKE '%REP%';

UPDATE

• Modifique as linhas existentes com a instrução UPDATE
• Pode se atualizar mais de uma linha por vez (se necessário).
• A quantidade de linhas alteradas depende do filtro utilizado.
• Se não existir filtro, todas as linhas da tabela são alteradas

// Se você omitir a cláusula WHERE, todas as linhas da tabela serão modificadas
UPDATE copy_emp SET department_id = 110;

// Uma ou mais linhas específicas são modificadas quando a cláusula WHERE é especificada
UPDATE employees SET department_id = 70 WHERE employee_id = 113;

// Atualizando Linhas usando Subconsulta
UPDATE copy_emp
SET department_id = (SELECT department_id
                     FROM employees
                     WHERE employee_id = 100)
WHERE job_id =      (SELECT job_id
                     FROM employees
                     WHERE employee_id = 200);

DELETE

• É possível remover as linhas existentes de uma tabela com a instrução DELETE

// Se você usar a cláusula WHERE, as linhas específicas serão deletadas:
DELETE FROM departments WHERE department_name = 'Finance';

// Se você omitir a cláusula WHERE, todas as linhas da tabela serão deletadas
DELETE FROM copy_emp;

// Deletando Linhas usando Subconsulta
DELETE FROM employees
WHERE department_id =
                      (SELECT department_id
                       FROM departments
                       WHERE department_name
                       LIKE '%Public%');

MERGE

• Use theMERGEstatement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.
• This statement is a convenient way to combine multiple operations. It lets you avoid multipleINSERT,UPDATE, andDELETEDML statements.
• MERGEis a deterministic statement. That is, you cannot update the same row of the target table multiple times in the sameMERGEstatement.

// Sintaxe do comando

MERGE [hints] INTO [nome_tabela]
USING [nome_tabela_visão_ou_consulta]
   ON ([condição])
 WHEN MATCHED THEN [cláusula_de_update]
      DELETE [cláusula_where]
 WHEN NOT MATCHED THEN [cláusula_de_insert]
     [LOG ERRORS [cláusula_de_log_erros][REJECT LIMIT [inteiro | ilimitado]];

// exemplos 
MERGE INTO dept d
USING (SELECT deptno, dname, loc
         FROM dept_online) o
ON (d.deptno = o.deptno)
WHEN MATCHED THEN
     UPDATE SET d.dname = o.dname, d.loc = o.loc
WHEN NOT MATCHED THEN
     INSERT (d.deptno, d.dname, d.loc)
     VALUES (o.deptno, o.dname, o.loc);

MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
        WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN 
    UPDATE SET D.bonus = D.bonus + S.salary*.01
    DELETE WHERE (S.salary > 8000)
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
    VALUES (S.employee_id, S.salary*0.1)
    WHERE (S.salary <= 8000);

Data definition language (DDL)

Utlizada para definir tabelas e elementos associados.Trata da criação, alteração e remoção de estruturas de dados.

Comandos:

• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME
• INDICE

CREATE

Usado para criar as tabelas do banco de dados.

// adiciona a CONSTRAINT no final do CREATE
CREATE TABLE employees(
    employee_id NUMBER(6)
    , last_name VARCHAR2(25) NOT NULL
    , email VARCHAR2(25)
    , salary NUMBER(8,2)
    , commission_pct NUMBER(2,2)
    , hire_date DATE NOT NULL

    , CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID)
    , CONSTRAINT emp_email_uk UNIQUE(email)
); 

// adiciona a CONSTRAINT na declaração do atributo
// tabela que contém CHECK
//
CREATE TABLE employees(
    employee_id NUMBER(6) CONSTRAINT emp_employee_id PRIMARY KEY
    , first_name VARCHAR2(20)
    , last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL
    , email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL CONSTRAINT emp_email_uk UNIQUE
    , phone_number VARCHAR2(20)
    , hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL
    , job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL
    , salary NUMBER(8,2) CONSTRAINT emp_salary_ck CHECK (salary > 0)
    , commission_pct NUMBER(2,2)
    , manager_id NUMBER(6)
    , department_id NUMBER(4) CONSTRAINT emp_dept_fk REFERENCES departments (department_id)
);

ALTER

Use a instrução ALTER TABLE para:

• Adicionar uma nova coluna

• Modificar uma coluna existente

• Definir um valor default para a nova coluna

• Eliminar uma coluna

// Adicionando uma nova Coluna
ALTER TABLE DEPT ADD Total_sal number(10,2);

// Modificando uma coluna existente
ALTER TABLE DEPT Modify COLUMN total_sal number(12,2);

// Renomeando uma coluna
ALTER TABLE DEPT RENAME COLUMN total_sal TO Total_Salario;

// Removendo uma coluna
ALTER TABLE DEPT DROP COLUMN total_Salario;

ALTER TABLE DEPT SET UNUSED COLUMN total_Salario;

ALTER TABLE DEPT DROP UNUSED COLUMNS;

// adiciona CONSTRAINT PRIMARY KEY na coluna
ALTER TABLE EMPLOYEES ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID);

// adiciona CONSTRAINT NOT NULL na coluna
ALTER TABLE EMPLOYEES MODIFY COLUMN SALARY NOT NULL;

// adiciona CONSTRAINT UNIQUE na coluna
ALTER TABLE EMPLOYEES ADD CONSTRAINT emp_email_uk UNIQUE (email);

// adiciona CONSTRAINT CHECK 
ALTER TABLE EMPLOYEES ADD CONSTRAINT emp_salary_ck CHECK (salary > 0);

// adiciona FOREIGN KEY na coluna
ALTER TABLE EMPLOYEES
    ADD CONSTRAINT emp_dept_fk 
    FOREIGN KEY (DEPARTMENT_ID)
    REFERENCES departments (DEPARTMENT_ID);

DROP

Use a instrução DROP TABLE para:

• Remover uma tabela do banco de dados
• Não pode ser desfeito
• Se existirem constraints de Foreign Key referenciando a tabela será necessário usar cascade constraints

// Removendo uma tabela
DROP TABLE EMPREGADOS;

// Removendo uma tabela sem enviar para lixeira (oracle)
DROP TABLE EMPREGADOS PURGE;

// Removendo uma tabela com referência de FK
DROP TABLE DEPT CASCADE CONSTRAINTS;

// Restaurando uma tabela da lixeira (oracle)
FLASHBACK TABLE <NOME> TO BEFORE DROP;

TRUNCATE

• Remove todas as linhas de uma tabela, esvaziando a tabela e mantendo a estrutura intacta.
• É muito mais rápido que remover (usando delete) todas as linhas da tabela.
• É uma instrução DDL (Data Definition Language), e não DML; não pode ser desfeita facilmente

TRUNCATE TABLE table_name; 

TRUNCATE TABLE copy_emp;

RENAME

Use theRENAMEstatement to rename a table, view, sequence, or private synonym.

• Oracle Database automatically transfers integrity constraints, indexes, and grants on the old object to the new object.

• Oracle Database invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.

RENAME departments_new TO emp_departments;

// criando uma nova tabela com os dados de outra e renomeando
CREATE TABLE temporary 
   (employee_id, start_date, end_date, job_id, dept_id) 
AS SELECT 
     employee_id, start_date, end_date, job_id, department_id
FROM job_history; 

DROP TABLE job_history; 

RENAME temporary TO job_history;

INDICE

• É um objeto de esquema
• É usado pelo banco de dados para acelerar a recuperação de linhas.
• Pode reduzir a entrada/saída de disco por um método de acesso de caminho rápido para localizar dados rapidamente
• É independente da tabela que indexa
• É usado e mantido automaticamente pelo banco de dados
• Para eliminar um índice, você precisa ser o proprietário dele ou ter o privilégio DROP ANY INDEX.

Como Criar Índices?

• Automaticamente: Um índice exclusivo é criado automaticamente quando você define uma constraint de chave PRIMARY ou UNIQUE em uma definição de tabela.
• Manualmente: Os usuários podem criar índices não exclusivos em colunas para acelerar o acesso às linhas.

// Aumente a velocidade de acesso da consulta à coluna LAST_NAME da tabela EMPLOYEES
CREATE INDEX emp_last_name_idx ON employees(last_name);

// Índices compostos são índices criados com mais de uma coluna
CREATE INDEX emp_name_idx ON employees(last_name,first_name);

// Índices únicos não indexam valores repetidos. São similares a uma constraing UNIQUE 
CREATE UNIQUE INDEX emp_email_idx ON employees(email);

// Criar um índice único para a coluna CPF da tabela CANDIDADTO
CREATE UNIQUE INDEX IX_CPF_CANDIDATO ON CANDIDATO(CPF); 

// Para remover um índice do dicionário de dados
DROP INDEX emp_last_name_idx;

Data Transaction control (DTL)

Utlizada para controlar as transações de banco de dados. Controle de transações dentro de um banco de dados.

• Uma transação consiste em um conjunto de instruções DML que formam uma unidade lógica de trabalho.
• Uma transação pode iniciar automaticamente, quando o primeiro comando DML é executado ou então explicitamente, como no caso do SQL Server (begin transaction)

Estado dos Dados antes de COMMIT ou ROLLBACK

• É possível recuperar o estado anterior dos dados.
• O usuário atual pode verificar os resultados das operações DML usando a instrução SELECT.
• Outros usuários não podem visualizar os resultados das instruções DML executadas pelo usuário atual.
• As linhas afetadas são bloqueadas; outros usuários não podem alterar os dados nessas linhas.

Comandos:

• COMMIT
• ROLLBACK
• SAVEPOINT

COMMIT

• As alterações de dados tornam-se permanentes no banco de dados.
• O estado anterior dos dados é perdido permanentemente.
• Todos os usuários podem visualizar os resultados.
• Os bloqueios nas linhas afetadas são liberados; essas linhas estão disponíveis para manipulação por outros usuários.

// deleta mas ainda não está salvo no banco
DELETE FROM employees WHERE employee_id = 99999;

1 row deleted. //resultado

// insere mais ainda não está salvo no banco
INSERT INTO departments VALUES (290, 'Corporate Tax', NULL, 1700);

1 row created. //resultado

// Submeta as alterações 
COMMIT;

Commit complete. //resultado

ROLLBACK

Descarte todas as alterações pendentes com a instrução ROLLBACK.

• As alterações de dados são desfeitas.
• O estado anterior dos dados é restaurado.
• Os bloqueios nas linhas afetadas são liberados.

// faz as alterações 
DELETE FROM copy_emp;

22 rows deleted. // resultado

// volta ao estado anterior, refaz as operações
ROLLBACK ;

Rollback complete. // resultado

// faz as alterações 
DELETE FROM test;

25,000 rows deleted. // resultado

// volta ao estado anterior, refaz as operações
ROLLBACK;

Rollback complete.// resultado

// faz as alterações 
DELETE FROM test WHERE id = 100;

1 row deleted. // resultado

// faz as alterações 
SELECT * FROM test WHERE id = 100;

No rows selected. // resultado

// Submeta as alterações 
COMMIT;

Commit complete. // resultado

SAVEPOINT

• Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.

UPDATE employees 
    SET salary = 7000 
    WHERE last_name = 'Banda';
SAVEPOINT banda_sal;

UPDATE employees 
    SET salary = 12000 
    WHERE last_name = 'Greene';
SAVEPOINT greene_sal;

SELECT SUM(salary) FROM employees;

ROLLBACK TO SAVEPOINT banda_sal;

UPDATE employees 
    SET salary = 11000 
    WHERE last_name = 'Greene';

COMMIT;

Data control language (DCL)

Utlizada para controlar autorização de acesso a dados e operações. Conceder e remover privilégios de acesso ao BD e a estruturas de BD.

Comandos:

• GRANT
• REVOKE

Regras e convenções

Veja algumas regras e tipificação que podem ajudar no dia-a-dia.

Regras de Nomeação

Os nomes de tabelas e colunas:

• Devem começar com uma letra
• Devem ter de 1 a 30 caracteres
• Devem conter apenas os caracteres A a Z, a a z, 0 a 9, _, $ e #
• Não devem duplicar o nome de outro objeto pertencente ao mesmo usuário
• Não devem ser palavras reservadas do servidor Oracle

Tipos de Dados

Violando Constraints

Não é possível deletar uma linha que contém uma chave primária usada como chave estrangeira em outra tabela.

DELETE FROM departments WHERE department_id = 60;

// resultado
DELETE FROM departments
 *
ERROR at line 1:
ORA-02292: integrity constraint (HR.EMP_DEPT_FK)
violated - child record found