SISTEMAS DE INFORMAÇÃO BANCO DE DADOS SQL PROF: EDSON THIZON

SISTEMAS DE INFORMAÇÃO

BANCO DE DADOS

SQL

Histórico

Entre 1974 e 1979, o San José Research Laboratory da IBM

desenvolveu um SGDB relacional que ficou conhecido como Sistema R.

Para a criação e acesso aos dados foi adotada uma linguagem chamada SEQUEL, mais tarde rebatizada SQL (Structured Query

Language).

Embora a query em sua definição, a SQL foi projetada de forma a permitir que além de consultas (queries), inserções, alterações e

deleções fossem feitas, além da própria criação das tabelas e campos. Dividiu-se a SQL então em duas partes:

-DDL: Data Description Language (Linguagem de Descrição de Dados);

-DML: Data Manipulation Language (Linguagem de Manipulação de Dados).

Conceito

• SQL ( STRUCTURED QUERY LANGUAGE)

• Uma linguagem utilizada para consultar , atualizar

e gerenciar Banco de Dados .

• Uma linguagem de aplicação para Banco de Dados

relacionais , e não um sistema ou uma linguagem

de programação .

DDL

A DDL, uma parte muito pequena da SQL, permite a criação e manutenção do dicionário de dados. O dicionário de dados contém a definição de cada tabela, de cada campo, enfim, contém a definição da base de dados propriamente dita. Em outras palavras, o dicionário de dados guarda dados sobre os dados.

Embora existam algumas outras construções, a mais importante das construções da DDL é a destinada a criação de tabelas. Por

exemplo, a sentença para a criação das tabelas funcionário, cidade, e

setor, poderia ser como segue:

Adotada como padrão mundial pela ISSO em 1987, é uma linguagem exclusiva de banco de dados Cliente/Servidor;

Não dispõe dos seguinte recursos: - Repetição e desvio;

- Comandos para manipulação de telas e impressão de relatórios;

Os Fabricantes de SGBD podem expandir a linguagem SQL padrão ANSI, desde que os comandos básicos sejam aceitos.

DML

É a parte mais ampla da SQL. Permite

pesquisar, alterar, incluir e deletar dados da

base de dados. São quatro as sentenças mais

importantes da DML:

• SELECT: permite a pesquisa de dados;

• UPDATE: permite a atualização de dados;

• DELETE: permite a deleção de dados;

LIMITES DO ORACLE RDBMS

ITEM LIMITE

Tabela na Base de dados Não há limites. Linhas por Tabelas Não há limites Colunas por tabelas 254

Indices por tabelas Não há limites Tebelas ou views joined em

uma query

Não há limites Niveis de ninho de subqueries 30

Caracteres em um nome 255 Colunas por índices 16

TIPOS DE DADOS(DATATYPES)

TIPO DESCRIÇÃO DO TIPO DE DADO

Char(n) Tamanho Fixo, pode conter uma seqüência de 1 a 255 bytes alfanuméricos;

Varchar2(n) Tamanho Variável, pode conter uma seqüência de 1 a 2000 bytes - alfanuméricos.

Long Tamanho Variável até 2 Gigabytes alfanuméricos

nota : só pode existir uma coluna long em cada tabela Number(p,s) Numérico com sinal e ponto decimal,

sendo precisão de 1 a 38 dígitos Raw Binário - Variável até 255 bytes

Long Raw Binário - Variável até 2 gigabytes - imagem Date Data c/ hora, minuto e segundo

Passos para utilizar o Banco de Dados Oracle : 1- Localize o ícone Start Database e execute-o; 2- Localize o ícone SQL Plus e execute-o;

3- Utilize o Usuário: SCOTT e a Senha: TIGER; Tabelas utilizadas nos exemplos:

EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL,

COMM, DEPTNO);

EMPNO = Código do Empregado MGR = Código do Gerente ENAME = Nome do Empregado SAL = Salário

JOB = Função do Empregado COMM = Comissão

HIREDATE= Data de Nascimento DEPTNO = Código Departamento

DEPT (DEPTNO, DNAME)

DEPTNO = Código Departamento DNAME = Nome do Departamento

SALGRADE (GRADE, LOSAL, HISAL)

Verificando estrutura da tabela

Desc EMP;

Desc DEPT;

SELECT

- Lista os atributos desejados como resultados de uma consulta; - Corresponde a operação de projeção da álgebra relacional;

- Lista de atributos pode ser substituído por “*” (todos);

From

- Lista de relação (tabelas) a serem usados na execução da expressão;

WHERE

-São definidos critérios de pesquisa envolvendo atributos das relações (tabelas) definidas na cláusula from ;

-Corresponde ao predicado da operação de seleção da álgebra relacional;

Exemplos:

1- Selecionar todos os atributos de cada empregado: SELECT *

FROM emp;

2- Selecionar todos os empregados com emprego de Gerente (Manager) e ordenado por nome;

SELECT * FROM emp

WHERE job = ‘MANAGER’;

Observe que o Oracle é sensitivo quanto a letras maiúsculas e minúsculas, e veja como os nomes dos empregados foram registrados.

Comparação de Valores

3- Selecionar os nomes de todos os empregados Gerentes com salários maior que R$ 500,00

SELECT ename FROM emp

Where job = ‘MANAGER’ and sal > 500;

4- Selecionar o nome dos empregados do departamento 10 com salario maior que 600.

SELECT ENAME FROM EMP

Order By

- Especifica a seqüência de ordenação da tabela criada pela consulta;

- Comando opcional;

- Qualificador opcional : asc / desc.

Select A1, A2,...,An From r1,r2,...rm

Where P

Onde A são os campos a serem selecionados, R são as tabelas, e P é uma condição.

5- Selecionar o nome do empregado em ordem alfabética: SELECT ename

FROM emp

ORDER BY ename;

6- Selecionar para cada empregado o seu nome e a sua função ordenado primeiramente por função e depois por empregado:

SELECT ename, job FROM emp

COMANDO DE COMPAÇÃO DE VALORES

Between

Faz uma pesquisa entre uma faixa de valores para um campo da tabela.

Not Between

Exemplo

Selecionar todos empregados que tem comissao entre 0 e 1000 Select *

from emp

where comm between 0 and 1000;

Selecionar o nome dos gerentes com salario entre 1000 e 3000.

SELECT ENAME FROM EMP

WHERE JOB='MANAGER' AND SAL between 1000 AND 3000;

IN

Consulta a presença de um campo em um conjunto de valores

NOT IN

Consulta a não presença de um campo em um conjunto de valores

Exemplo

Selecionar o nome dos empregados com códigos 7499, 7566, 7788.

SELECT EMPNO, ENAME FROM EMP

WHERE EMPNO IN (7566,7788, 7499);

Selecionar o nome dos empregados que não possuem os códigos 7499, 7566, 7788.

SELECT EMPNO, ENAME FROM EMP

Like

Compara a existência de uma caracter em uma determinada posição em uma string.

‘_‘ Testa a existência de um caracter não nulo na posição; ‘%’ representa qualquer seqüência de n caracteres.

Not Like

Compara a não existência de um caracter em uma determinada posição em uma string.

Exemplo

Selecionar o nome e código dos empregados que o nome inicia com a letra 'C'.

SELECT EMPNO,ENAME FROM EMP

WHERE ENAME LIKE 'C%';

SELECT * FROM EMP

WHERE ENAME NOT LIKE ‘%FORD%’

Neste caso localiza todos os empregados que não possuem sobrenome “FORD” .

Is Null

Verifica se o valor do campo comparado é zero ou vazio;

Is Not Null

Verifica se o valor do campo comparado não é zero ou vazio;

Exemplo

Selecionar o nome, código e salario dos empregados que não possuem comm.

SELECT EMPNO,ENAME,SAL FROM EMP

WHERE COMM IS NULL;

E os que possuem:

SELECT EMPNO,ENAME,SAL FROM EMP

Operadores Aritméticos

+ adição

- Subtração

* Multiplicação

/ Divisão

Exemplo

Selecionar o nome dos empregados, seu salário e o

salário mais 300:

Select ename, sal, sal + 300

from emp

Selecionar o nome dos empregados, seu salário e 10 %

do salário:

Select ename, sal, sal *0.10

from emp

Usando Coluna Alias

Select ename As nome, sal salario

from emp;

Nome salario --- ---...

Usando o comando de

concatenação

Concatena colunas ou caracteres com outras colunas (||)

Exemplo

Select ename ||job as “Empregado” from emp

Eliminando Linhas Duplicadas

Para Eliminar as linhas duplicadas use a palavra DISTINCT na cláusula SELECT.

Exemplo:

Select Distinct deptno form emp;

Funções Numéricas

retorna a raiz quadrada de 25 SQRT(25)

SQRT

Se SAL maior que O retorna +1

Se SAL menor que 0 retorna -1

Se SaL igual a 0 retorna 0. Sign(sal)

Sign

Retorna o sal elevado ao quadrado.

Power(sal,2) Power

Retorna o resto da divisão de sal/comm

Mod(sal,comm) Mod

Sal é truncado para 2 dígitos após o ponto decimal. TRUNC(SAL,2)

Trunc

sal é arredondado para 2 casas apos o ponto decimal

ROUND(SAL,2) Round

Exemplo

Selecionar o nome dos empregados, seu salário e o

salário mais 300 com duas casas decimais:

Select ename, round(sal,2), round(sal + 300,2)

from emp

Selecionar o nome dos empregados, seu salário e 10 %

do salário sem nenhuma casa decimal:

Select ename, sal, trunc(sal *0.10)

from emp

Funções de data

TRUNCA A DATA PARA A PRIMEIRA DATA DO 'FMT' TRUNC(SYSDATE,FMT) TRUNC RETORNA A DATA TOMANDO COMO PARAMETRO O 'FMT' LAST_DAY(SYSDATE) LAST_DAY

PROCURA UMA SEXTA-FEIRA APÓS HIREDATE NEXT_DAY(HIREDATE,'FRID AY') NEXT_DAY CALCULA O NÚMERO DE MESES BETWEEN ENTRE AS DATAS MONTHS_BETWEEN(HIRED ATE,SYSDATE) MONTHS_BETWEEN ADICIONA 5 MESES NA DATA HIREDATE ADD_MONTHS(HIREDATE,5) ADD_MONTHS

Funções de caracteres

RETORNA UMA PARTE DA STRING, PASSADO O CARACTER INICIAL E A QUANTIDADE A RETORNAR SUBSTR(JOB,1,3)

Substr

Upper

Lower

length

RETORNA A STRING COM A PRIMEIRA LETRA EM MAIÚSCULO

INITCAP(ENAME)

initcap

TRIM TRIM(JOB) retira os espaços em branco da direita e esquerda

Funções de conversão

CONVERTE AS 3 ULTIMAS (EM FORMATO CHAR) PARA MUMBER.

TO_NUMBER(SUBSTR(‘$150’, 2,3))

TO_NUMBER

CONVERTE COLUNAS DO TIPO CHAR PARA O FORMATO DE DATA.

TO_DATE(‘15/05/90’,’DD/MM/ YY’)

TO_DATE

CONVERTE COLUNAS DO TIPO NUMBER E DATA PARA CHAR. TO_CHAR(SYSDADE,’YY’) TO_CHAR Resultado Exemplo Funções

FUNÇÕES

AVG ([DISTINCT | ALL] n) - valor médio de n, ignorando valores nulos. COUNT ([DISTINCT | ALL] expr * ) - número de vezes que a expressão de

número EXPR avalia algo diferente de NULO. '*' faz com que COUNT conte todas as linhas selecionadas, incluindo duplicadas e linhas com nulos.

MAX ([DISTINCT | ALL] expr) - valor máximo de expr MIN ([DISTINCT | ALL] expr) - valor mínimo de min

SUM ([DISTINCT | ALL] n) - soma os valores de n ignorando valores nulos NVL(expr, 0) - converte um valor nulo.

Exemplo

Select max(sal), Min(sal) from emp;

Exemplo Selecionar a média de salário. SELECT AVG(SAL) FROM EMP; Selecionar a quantidade de empregados. SELECT COUNT(ENAME) FROM EMP;

Selecionar total de salário pago pela empresa.

SELECT SUM(SAL) FROM EMP;

Selecionar o maior salario entre os gerentes.

SELECT MAX(SAL) FROM EMP

GROUP BY

Forma grupos com as tuplas da tabela especificada na cláusula from, que possuem o mesmo valor no atributo especificado na cláusula grupo by;

Para ter resultado em ordem, deve ser especificado também a clausula order by(após a clausula group by).

Exemplo

Select deptno, count(empno) from emp

group by deptno;

REGRA PARA UTILIZAR FUNÇÕES DE GRUPO: Se você incluir uma função de grupo em um comando SELECT, você não pode selecionar resultados individuais a menos que a coluna individual apareça na cláusula GROUP BY.

Exemplos

Selecionar a média salarial de cada departamento.

- SELECT AVG(SAL), DEPTNO

FROM EMP

GROUP BY DEPTNO;

Selecionar a média salarial de cada profissão.

- SELECT AVG(SAL), JOB

FROM EMP

HAVING

Having é para os grupos o que o where é para as linhas. Em outras palavras, é utilizado para eliminar grupos, onde where é utilizado para eliminar linhas.

Exemplo:

Select curso_alu, avg(idade) from alunos

group by curso_alu

Exemplos

• Selecionar a mediA de salario dos departamentos

com media superiora 700.

SELECT AVG(SAL), DEPTNO

FROM EMP

GROUP BY DEPTNO

HAVING AVG(SAL)>700;

• Selecionar a media salarial das funções com

media inferior a 1550.

SELECT AVG(SAL), JOB

FROM EMP

GROUP BY JOB

JUNÇÕES (JOIN)

EQUI-JOIN

O relacionamento entre a tabela EMP e a tabela DEPT é um EQUI-JOIN, onde os valores na coluna DEPTNO em ambas as tabelas são iguais. O operador de comparação ' = ' é utilizado.

A condição de Join é especificada na cláusula WHERE. Para juntar as duas tabelas EMP e DEPT, digite:

SELECT ENAME, JOB, DNAME FROM EMP, DEPT

Cont...

Caso deseje-se listar colunas que são ambíguas para ambas as tabelas, deve-se especificar no nome da tabela ANTES DO nome da coluna desejada. Exemplo:

SELECT DEPT.DEPTNO, ENAME, JOB, DNAME FROM EMP, DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO ORDER BY DEPT.DEPTNO;

Cont..

NON- EQUI-JOIN

O relacionamento entre EMP e SALGRADE caracteriza um NON-EQUI-JOIN, onde nenhuma coluna em EMP

corresponde diretamente a uma coluna em SALGRADE. Neste caso, um operador diferente do = precisa ser

utilizado. No caso abaixo utilizamos o BETWEEN. Para determinar a qual faixa salarial um determinado

empregado se encaixa, usamos:

SELECT E.ENAME, E.SAL, S.GRADE FROM EMP E, SALGRADE S

DICA:

o número de joins = número

de tabelas - 1

Subconsultas

1- Empregados que possuem salário maior do que a média:

SELECT ENAME, SAL FROM EMP

Cont...

Exemplos:

2- Empregados que possuem salário menor do que a média dos MANAGER com seu respectivo cargo:

SELECT ENAME, SOB, SAL FROM EMP

WHERE SAL < (SELECT AVG(SAL) FROM EMP WHERE JOB = ‘MANAGER’);

Rodando um Script no SQL Plus

• Para rodar um Script digite:

• @CAMINHO DO ARQUIVO

Exemplo:

Exist

Verifica se o resultado do cálculo da subconsulta representada por “select * from “ Não é vazia.

Select x from A

Where exists (select * from B

where y=x)

O campo X da relação A será selecionado, se existir um elemento y=x na relação B.

Exemplo

Not Exists

Verifica se o resultado do calculo da subconsulta representada por “Select * from “ é vazia.

COMANDO CREATE TABLE

CREATE TABLE DEPT

(DEPTNO NUMBER(2) ,

DNAME VARCHAR2(14),

LOC VARCHAR2(13));

OUTRO EXEMPLO

CREATE TABLE EMP

(EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB CHAR(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2),

INSERINDO DADOS EM TABELAS

INSERT INTO DEPT

VALUES (50, ´TESOURARIA', 'TUBARAO',1000);

INSERT INTO DEPT(DNAME,DEPTNO) VALUES(´CAIXA',60);

ATUALIZANDO REGISTROS

UPDATE EMP

SET JOB='MANAGER'

WHERE ENAME='MARTIN';

ATUALIZANDO VÁRIOS REGISTROS

UPDATE EMP

SET JOB='VENDEDOR'

DELETANDO LINHAS DAS TABELAS

DELETE FROM EMP

WHERE ENAME='MARTINS';

DELETE FROM DEPT WHERE DEPTNO=60;