25/06/2017
1
Gerenciamento de Dados e InformaçãoGerenciamento de Dados e Informação
Valeria [email protected]
2
SQLSQLSQL - Structured Query Language
Linguagem de Consulta Estruturada
Apesar do QUERYQUERY no nome, não éapenas de consulta, permitindo definição(DDLDDL) e manipulação (DMLDML) de dados
Fundamentada no modelo relacional (álgebrarelacional)
Cada implementação de SQL pode possuiralgumas adaptações para resolver certasparticularidades do SGBD alvo
2
3
SQL SQL -- Origem/HistóricoOrigem/Histórico
Primeira versão: SEQUEL, definida por Chamberlainem 1974 na IBMEm 1975 foi implementado o primeiro protótipoRevisada e ampliada entre 1976 e 1977 e teve seunome alterado para SQL por razões jurídicasEm 1982, o American National Standard Institutetornou SQL padrão oficial de linguagem em ambienterelacionalUtilizada tanto de forma interativa como incluída emlinguagens hospedeiras
34
Enfoques de SQLEnfoques de SQL
Linguagem interativa de consulta (ad-hoc):usuários podem definir consultasindependente de programasLinguagem de programação para acesso abanco de dados: comandos SQL embutidosem programas de aplicaçãoLinguagem de administração de dados: o DBApode utilizar SQL para realizar suas tarefas
4
5
Enfoques de SQLEnfoques de SQL
Linguagem cliente/servidor: os programasclientes usam comandos SQL para secomunicarem e compartilharem dados com oservidorLinguagem para banco de dados distribuídos:auxilia na distribuição de dados por vários nóse na comunicação com outros sistemasCaminho de acesso a outros bancos de dadosem diferentes máquinas: auxilia na conversãoentre diferentes produtos em diferentesmáquinas
56
Componentes de SQLComponentes de SQLData Definition Language (DDL): permite acriação dos componentes do BD, comotabelas e índices.
Principais Comandos DDL:CREATE TABLEALTER TABLEDROP TABLECREATE INDEXALTER INDEXDROP INDEX
6
25/06/2017
2
7
Componentes de SQLComponentes de SQL
Data Manipulation Language (DML): permite amanipulação dos dados armazenados no BD.Principais Comandos DML:
INSERTDELETEUPDATE
Data Query Language (DQL): permite extrairdados do BD.Principal Comando DQL: SELECT
78
Componentes de SQLComponentes de SQLData Control Language (DCL): provê asegurança interna do BD.
Principais Comandos DCL:CREATE USERALTER USERGRANTREVOKECREATE SCHEMA
8
9
S Q L
DDL
Criar (CREATE)Destruir (DROP)Modificar (ALTER)
DMLConsultar (SELECT)Inserir (INSERT)Remover (DELETE)Atualizar (UPDATE)
Segurança
Controle
Administração
Implementação
Ambiente
Usos de SQLUsos de SQL
910
SQL SQL -- VantagensVantagens
Independência de fabricante
Portabilidade entre sistemas
Redução de custos com treinamento
Comandos em inglês
Consulta interativa
Múltiplas visões de dados
Manipulação dinâmica dos dados
10
11
SQL SQL -- DesvantagensDesvantagens
A padronização inibe a criatividade
Está longe de ser uma linguagem relacionalideal
Algumas críticas
falta de ortogonalidade nas expressões
discordância com as linguagenshospedeiras
não dá suporte a alguns aspectos domodelo relacional
1112
Num-Dep Num-LocLocais
Trabalha-em Cad-Emp Num-Proj Horas
Cad Nome Sexo Salario Num-Dep Cad-Spv
Numero Nome Cad-Ger Data-Ini
Cad Nome Data-nasc Grau-P
Numero Nome Num-Dep
Departamento
Empregado
Projeto
Dependente
12
Esquema Relacional dos ExemplosEsquema Relacional dos Exemplos
25/06/2017
3
13
Comandos SQL Comandos SQL (Padrão ANSI)(Padrão ANSI)
Criação, alteração e destruição de tabelas
Inserção, modificação e remoção de dados
Extração de dados de uma tabela (Consultas)
Definição de visões
Definição de privilégios de acesso
1314
Criação de TabelasCriação de TabelasDefinição de nova tabela → CREATE TABLECREATE TABLE
14
CREATE TABLE (
25/06/2017
4
19
Criação de TabelasCriação de Tabelas
Descrição de Restrições
Salário não pode ser inferior ao mínimo
19
CONSTRAINT nometabela_checkCHECK (salario >= 450 )
Só admite valor único
CONSTRAINT nometabela_constUNIQUE (nome )
20
Criação de TabelasCriação de TabelasExemplo 1
Empregado Cad Nome Sexo Salario Num-Dep Cad-Spv
20
CREATE TABLE Empregado(Cad number,Nome varchar2 (20),Sexo char,Salario number (10,2), Num_Dep number(1),Cad_Spv number,CONSTRAINT empregado_pkey PRIMARY KEY (Cad),CONSTRAINT empregado_fkey1 FOREIGN KEY
Num_Dep REFERENCES Departamento (Numero),CONSTRAINT empregado_fkey2 FOREIGN KEY
Cad_Spv REFERENCES Empregado (Cad) );
21
CREATE TABLE Trabalha_em(Cad_emp number,Num_Proj integer,Horas number (3,1) ,CONSTRAINT trabalha _em_pkey
PRIMARY KEY (Cad_emp, Num_proj),CONSTRAINT trabalha _em_fkey1
FOREIGN KEY (Cad_Emp) REFERENCESEmpregado(Cad),
CONSTRAINT trabalha _em_fkey2 FOREIGN KEY (Num_Proj )REFERENCESProjeto(Numero)); 21
CriaCriaçãção o de de TabelasTabelasExemplo 2
Trabalha-em Cad-Emp Num-Proj Horas
22
CriaCriaçãção de o de TabelasTabelas
Criação de índices em uma tabela existente →CREATECREATE INDEXINDEX
São estruturas que permitem agilizar abusca e ordenação de dados em tabelas
CREATE [UNIQUE] INDEX ON ([, …]);
22
23
AlteraçãoAlteração de de TabelasTabelas
Alterar definições de tabelas existentes →ALTERALTER TABLETABLE
Permite inserir/eliminar/modificar elementosda definição de uma tabela
23
ALTER TABLE ;
Análoga ao Create
24
AlteraçãoAlteração de de TabelasTabelas
Exemplos
Acrescentar coluna na tabela Empregado
ALTER TABLE EMPREGADOADD (Diploma varchar2(20) );
Remover coluna na tabela Empregado
ALTER TABLE EMPREGADO DROP (Diploma );
24
25/06/2017
5
25
RemoçãoRemoção de de TabelasTabelas
Exemplo
DROP TABLE ;
DROP TABLE Empregado ;
25
Eliminar uma tabela que foi previamentecriada → DROPDROP TABLETABLE
Observação
Os dados são também excluídos
26
ExtraçãoExtração de Dados de de Dados de umaumaTabelaTabela ((ConsultaConsulta))
Consultar dados em uma tabela → SELECTSELECT
Selecionando atributos (ProjeçãoProjeção)
SELECT FROM ;
Exemplo: Listar nome e salário de todos osempregados
SELECT Nome, Salario FROM Empregado ;
26
27
ExtraçãoExtração de Dados de de Dados de umaumaTabelaTabela ((ConsultaConsulta))
Selecionando todos os atributos
SELECT * FROM ;
SELECT * FROM Empregado ;
27
Exemplo
Observação
Deve ser usado com cautela pois podecomprometer o desempenho
28
ExtraçãoExtração de Dados de de Dados de umaumaTabelaTabela ((ConsultaConsulta))
Selecionando tuplas da tabela → cláusulaWHEREWHERE
SELECT FROM WHERE ;
Onde
28
Uma constante, variável ou consulta aninhada
29
ExtraçãoExtração de Dados de de Dados de umaumaTabelaTabela ((ConsultaConsulta))
ExemplosListar nome e sexo dos empregadosdo departamento 15
Listar nome e sexo dos empregadosdo departamento 15 com salário > R$1.000,00
SELECT Nome, Sexo FROM EmpregadoWHERE Num_Dep = 15;
SELECT Nome, Sexo FROM EmpregadoWHERE Num_Dep = 15 AND Salario > 1000;
2930
ExtraçãoExtração de Dados de de Dados de umaumaTabelaTabela ((ConsultaConsulta))
Consulta para o usuário fornecer valores parao SELECTSELECT só na hora da execução
Colocar parâmetro na forma &
Exemplo
Listar nome e salário dos empregados dodepartamento com um dado código
30
SELECT Nome, Salario FROM EmpregadoWHERE Num_Dep = &cod_dep ;
25/06/2017
6
31
OperadoresOperadores SQLSQL
BETWEENBETWEEN e NOTNOT BETWEENBETWEEN: substituem ouso dos operadores =
Exemplo: Listar os nomes dosempregados com salário entre R$1.000,00 e R$ 2.000,00
... WHERE BETWEEN AND ;
SELECT Nome FROM EmpregadoWHERE Salario BETWEEN 1000 AND 2000;
3132
OperadoresOperadores SQLSQL
LIKELIKE e NOTNOT LIKELIKE: só se aplicam sobreatributos do tipo char. Operam como = e < >,utilizando os símbolos % (substitui umapalavra) e _ (substitui um caractere)
Exemplo: Listar os empregados que têmcomo primeiro nome José
...WHERE LIKE ;
SELECT Nome FROM EmpregadoWHERE Nome LIKE ‘ José %’;
32
33
OperadoresOperadores SQL SQL
ININ e NOTNOT ININ: procuram dados que estão ounão contidos em um dado conjunto de valores
Exemplo: Listar o nome e data denascimento dos dependentes com grau deparentesco ‘M’ ou ‘P’
... WHERE IN ;
SELECT Nome, Data_Nasc FROM DependentesWHERE Grau_P IN (‘M’, ‘P’);
3334
OperadoresOperadores SQL SQL
ISIS NULLNULL e ISIS NOTNOT NULLNULL: identificam se oatributo tem valor nulo (não informado) ou não
Exemplo: Listar os dados dos projetos quenão tenham local definido
... WHERE IS NULL;
SELECT * FROM ProjetoWHERE Local IS NULL;
34
35
OrdenandoOrdenando osos Dados Dados SelecionadosSelecionados
Cláusula ORDERORDER BYBY
SELECT FROM [WHERE ]
ORDER BY { ASC | DESC};
3536
OrdenandoOrdenando osos Dados Dados SelecionadosSelecionados
ExemplosListar todos os dados dos empregadosordenados ascendentemente por nome
Listar todos os dados dos empregadosordenados descendentemente por salário
SELECT * FROM EmpregadoORDER BY Nome;
SELECT * FROM EmpregadoORDER BY Salario DESC;
36
25/06/2017
7
37
RealizandoRealizando CálculoCálculo com com InformaçãoInformação SelecionadaSelecionada
Pode-se criar um campo que não pertença àtabela a partir de cálculos sobre atributos databela
Uso de operadores aritméticos
3738
RealizandoRealizando CálculoCálculo com com InformaçãoInformação SelecionadaSelecionada
Exemplo: Mostrar o novo salário dosempregados calculado com base no reajustede 60% para os que ganham abaixo de R$1.000,00
38
SELECT Nome, (Salario * 1.60) AS Novo_salarioFROM Empregado WHERE Salario < 1000;
RenomearRenomear
39
FunçõesFunções AgregadasAgregadas
Utilização de funções sobre conjuntos
Disparadas a partir do SELECTSELECT
3940
FunçõesFunções AgregadasAgregadas
Exemplos
Mostrar o valor do maior salário dosempregados e o nome do empregado que orecebe
SELECT Nome, Salario FROM EmpregadoWHERE Salario IN (SELECT MAX (Salario )FROM EMPREGADO);
Consulta aninhada
41
FunçõesFunções AgregadasAgregadas
ExemplosMostrar qual o salário médio dosempregados
Quantos empregados ganham mais deR$1.000,00?
SELECT AVG (Salario ) FROM Empregado ;
SELECT COUNT (*) FROM EmpregadoWHERE Salario > 1000;
4142
CláusulaCláusula DISTINCTDISTINCT
Elimina tuplas duplicadas do resultado de umaconsulta
Exemplo: Quais os diferentes salários dosempregados?
SELECT DISTINCT Salario FROM Empregado ;
42
25/06/2017
8
43
CláusulaCláusula GROUP BYGROUP BYOrganiza a seleção de dados em grupos
Exemplo: Listar os quantitativos deempregados de cada sexo
SELECT Sexo, Count(*) FROM EmpregadoGROUP BY Sexo;
Atributos do GROUP BY devem aparecer no SELECT
43
Exceção: Funções agregadas
44
Cláusula HAVINGCláusula HAVINGAgrupando Informações de forma condicional
Vem depois do GROUPGROUP BYBY e antes doORDERORDER BYBYExemplo: Listar o número total deempregados que recebem salários superiora R$1.000,00 de cada departamento commais de 5 empregados
SELECT Num_Dep , COUNT (*) FROM EmpregadoWHERE Salario > 1000GROUP BY Num_Dep HAVING COUNT(*) > 5;
44
45
Uso de “Alias”Uso de “Alias”
Para substituir nomes de tabelas emcomandos SQL
São definidos na cláusula FROM
SELECT A.nome FROM Departamento AWHERE A.Numero = 15;
Alias
4546
Consultando Dados Consultando Dados de de Várias Tabelas Várias Tabelas -- JunçãoJunção
Junção de Tabelas (JOINJOIN)
Citar as tabelas envolvidas na cláusulaFROMFROM
Qualificadores de nomes - utilizados paraevitar ambigüidades
Referenciar os nomes de Empregado ede Departamento
Empregado.NomeDepartamento.Nome
46
47
Junção de TabelasJunção de TabelasExemplos
Listar o nome do empregado e nome dodepartamento no qual está alocado
Listar os nomes dos departamentos quetêm projetos
SELECT E.Nome, D.NomeFROM Empregado E, Departamento DWHERE E.Num_Dep = D.Numero ;
SELECT D.NomeFROM Departamento D, Projeto PWHERE P.Num_Dep = D.Numero ;
4748
Junção de TabelasJunção de Tabelas
Pode-se utilizar as cláusulas (NOTNOT) LIKELIKE,(NOTNOT) IN, ISIS (NOTNOT) NULLNULL misturadas aosoperadores ANDAND, OROR e NOTNOT nas equações dejunção ( cláusula WHERE )
Exemplo: Listar os nomes dosdepartamentos que têm projetos comnúmero superior a 99 e localizados em RJou SP, ordenados por nome dedepartamento
48
25/06/2017
9
49
Junção de TabelasJunção de Tabelas
SELECT D.NomeFROM Departamento D, Projeto PWHERE P.Local IN (‘RJ’, ‘SP’)AND P.Numero > 99 AND P.Num_Dep = D.NumeroORDER BY D.Nome;
4950
Junção de TabelasJunção de Tabelas
SELECT D.NomeFROM Departamento D, Projeto PWHERE P.Local IN (‘RJ’, ‘SP’)AND P.Numero > 99 AND P.Num_Dep = D.NumeroORDER BY D.Nome;
50
51
Junção de TabelasJunção de Tabelas
SELECT D.NomeFROM Departamento D, Projeto P, Locais LWHERE L.Num_Loc IN (‘RJ’, ‘SP’)AND P.Numero > 99 AND P.Num_Dep = D.NumeroAND P.Num_Dep = L.Num_DepORDER BY D.Nome;
5152
Junção de TabelasJunção de Tabelas
Classificando uma JunçãoExemplo: Para cada departamento, liste onome do departamento, e para cada umdeles, listar o número, o nome e o saláriode seus empregados, ordenando aresposta
SELECT D.Nome, E.Cad, E.Nome, E.SalarioFROM Departamento D, Empregado EWHERE D.Numero = E.Num_DepORDER BY D.Nome, E.Salario DESC ;
52
53
Junção de TabelasJunção de Tabelas
Agrupando através de mais de um atributo emuma Junção
Exemplo: Encontre o total de projetos decada funcionário por departamento,informando o cadastro do empregado.
SELECT E.Num_Dep , E.Cad, COUNT(*) AS Total FROM Trabalha_em T, Empregado E WHERE E.Cad = T.Cad_Emp GROUP BY E.Num_Dep , E.CadORDER BY E.Num_Dep , E.Cad;
5354
Junção de TabelasJunção de Tabelas
Juntando mais de duas tabelasExemplos
Listar o nome dos empregados, com seurespectivo nome de departamento quetrabalhem mais de 20 horas em algumprojeto
54
25/06/2017
10
55
Junção de TabelasJunção de Tabelas
SELECT E.Nome, D.NomeFROM Empregado E, Departamento D,
Trabalha_em TWHERE T.Horas > 20 AND T.Cad_Emp = E.CadAND E.Num_Dep = D.Numero ;
5556
Junção de TabelasJunção de Tabelas
Inner join (às vezes chamada de "junçãosimples")
É uma junção de duas ou mais tabelas queretorna somente as tuplas que satisfazem àcondição de junção
Equivalente à junção natural
56
57
Junção de TabelasJunção de Tabelas
Outer joinRetorna todas as tuplas de uma tabela esomentesomente as tuplas de uma tabelasecundária onde os campos de junção sãoiguais ( condição de junção é encontrada)Para todas as tuplas de uma das tabelasque não tenham tuplas correspondentes naoutra, pela condição de junção, é retornadonull para todos os campos da lista doselect que sejam colunas da outra tabela
5758
Junção de TabelasJunção de Tabelas
Outer join (Cont.)
Para escrever uma consulta que executauma outer join das tabelas A e B e retornatodas as tuplas de A além das tuplascomuns, utilizar
SELECT FROM LEFT [OUTER] JOIN ON ;
58
59
Junção de TabelasJunção de Tabelas
Exemplo: Listar os nomes de todos osdepartamentos da companhia e os nomes eos locais dos projetos de que sãoresponsáveis
SELECT Departamento .Nome, Projeto .Nome,Projeto .LocalFROM Departamento LEFT OUTER JOINProjetoON Departamento .Numero = Projeto .Num_Dep ;
Outer join (Cont.)
5960
Junção de TabelasJunção de Tabelas
Outer join (Cont.)
Para escrever uma consulta que executauma outer join das tabelas A e B e retornatodas as tuplas de B além das tuplascomuns, utilizar
SELECT FROM RIGHT [OUTER] JOIN ON ;
60
25/06/2017
11
61
Junção de TabelasJunção de Tabelas
Exemplo: Listar os nomes dosdepartamentos da companhia com osnomes e locais dos projetos de que sãoresponsáveis e os nomes dos demaisprojetos
SELECT Departamento .Nome, Projeto .Nome,Projeto .LocalFROM Departamento RIGHT OUTER JOINProjetoON Departamento .Numero = Projeto .Num_Dep ;
Outer join (Cont.)
6162
Junção de TabelasJunção de Tabelas
Outer join (Cont.)
Para escrever uma consulta que executauma outer join e retorna todas as tuplas deA e B, estendidas com nulls se elas nãosatisfizerem à condição de junção, utilizar
SELECT FROM FULL [OUTER] JOIN ON ;
62
63
Junção de TabelasJunção de Tabelas
Exemplo: Listar os nomes de todos osdepartamentos da companhia, os nomes elocais dos projetos de que sejamresponsáveis e os nomes dos demaisprojetos
Outer join (Cont.)
SELECT Departamento .Nome, Projeto .Nome,Projeto .LocalFROM Departamento FULL OUTER JOINProjetoON Departamento .Numero = Projeto .Num_Dep ;
6364
InserçãoInserção de Dados de Dados emem TabelasTabelas
Adicionar uma ou várias tuplas à tabela →INSERTINSERT
INSERT INTO ( ) VALUES ( );
INSERT INTO Empregado (Cad, Nome, Sexo,Salario , Num_Dep , Cad_Supv ) VALUES (015, ‘José da Silva ’, ‘M’,
1000.00, 1, 020);
Exemplo: Inserir dados de um empregado
64
Uma LinhaUma Linha
65
InserçãoInserção de Dados de Dados emem TabelasTabelas
Inserir dados recuperados de uma tabela emoutra tabela – uso do SELECTSELECT
INSERT INTO ( )SELECT FROM WHERE ;
Exemplo: Armazenar em uma tabela paracada departamento com mais de 50empregados, o número de empregados e asoma dos salários pagos
65
Várias LinhasVárias Linhas
66
InserçãoInserção de Dados de Dados emem TabelasTabelas
INSERT INTO Depto_info (nome_depto ,num_emp , total_sal )
SELECT D.nome , COUNT(*), SUM (E.salario )FROM Departamento D, Empregado EWHERE D.numero = E.Num_DepGROUP BY D.nomeHAVING COUNT (*) > 50;
66
25/06/2017
12
6767
AtualizaçãoAtualização de Dados de Dados ememTabelasTabelas
Com base nos critérios especificados, alterarvalores de campos de uma tabela → UPDATEUPDATE
Exemplo: Atualizar salário do empregado15 para R$1500,00
UPDATE SET = WHERE ;
UPDATE Empregado SET Salario = 1500.00WHERE Cad = 15;
68
RemoçãoRemoção de de TuplasTuplas de de TabelaTabela
Exclusão de dados de uma tabela → DELETEDELETE
Exemplo: Remover todos os empregadoscom salário superior a R$ 5000,00
DELETE FROM WHERE ;
DELETE FROM Empregado WHERE Salario > 5000.00;
68
69
Utilizando Visões (VIEWS)Utilizando Visões (VIEWS)
São tabelas virtuais que não ocupam espaçofísicoOperações
Criação e utilizaçãoInserção e modificação (semânticadepende da definição/natureza da visão)
CREATE VIEW AS SELECT... ;
6970
Utilizando Visões (VIEWS)Utilizando Visões (VIEWS)
Exemplo: Criar uma visão dos empregadosdo departamento 10 que tenham mais de20 horas de trabalho em projetos
CREATE VIEW Dep_10 ASSELECT E.Nome, T.Num_ProjFROM Empregado E, Trabalha_em TWHERE T.Horas > 20 AND T.Cad_Emp = E.CadAND E.Num_Dep = 10;
70
71
Consultas Consultas Encadeadas Encadeadas (Aninhadas(Aninhadas))
O resultado de uma consulta é utilizado poroutra consulta, de forma encadeada e nomesmo comando SQL
O resultado do comando SELECTSELECT maisinterno (subselect) é usado por outro SELECTSELECTmais externo para obter o resultado final
O SELECTSELECT mais interno (subconsulta ouconsulta aninhada) pode ser usado apenasnas cláusulas WHEREWHERE e HAVINGHAVING docomando mais externo ou em cálculoscálculos
7172
Consultas Consultas Encadeadas Encadeadas (Aninhadas(Aninhadas))
Subconsultas devem ser escritas entre (( e ))
Existem 3 tipos de subconsultas
ESCALARESCALAR → Retornam um único valor
ÚNICAÚNICA LINHALINHA → Retornam várias colunas,mas apenas uma única linha é obtida
TABELATABELA → Retornam uma ou mais colunase múltiplas linhas
72
25/06/2017
13
73
Consultas Consultas Encadeadas Encadeadas (Aninhadas(Aninhadas))
Exemplos
Usando uma subconsulta com operador deigualdade: Listar os empregados quetrabalham no departamento de Informática
73
SELECT Cad, Nome, SalarioFROM EmpregadoWHERE Num_Dep =
(SELECT NumeroFROM Departamento
WHERE Nome = ‘Informática ’ );
Subconsulta escalar
74
Consultas Consultas Encadeadas Encadeadas (Aninhadas(Aninhadas))
Usando uma subconsulta com funçãoagregada: Listar os empregados cujossalários são maiores do que o saláriomédio, mostrando o quanto são maiores
SELECT Cad, Nome, Sexo, Salario –(SELECT AVG (Salario ) FROM Empregado )
AS DifSalFROM EmpregadoWHERE Salario > ( SELECT AVG ( Salario )
FROM Empregado );74
7575
Consultas Consultas Encadeadas Encadeadas (Aninhadas(Aninhadas))
Mais de um nível de aninhamento: Listar osdependentes dos funcionários quetrabalham no departamento de Informática
SELECT Nome, Data_nasc , Grau_PFROM Dependente WHERE Cad IN( SELECT Cad FROM Empregado
WHERE Num_Dep =( SELECT Numero FROM Departamento
WHERE Nome = ‘Informática ’ ) );76
Cláusulas ANY/SOME Cláusulas ANY/SOME
São usadas com subconsultas que produzemuma única coluna de números
Exemplo: Listar os empregados cujossalários são maiores do que o salário depelo menos um funcionário dodepartamento 20
SELECT Cad, Nome, Sexo, SalarioFROM EmpregadoWHERE Salario > SOME ( SELECT Salario FROM Empregado
WHERE Num_Dep = 20) ;76
77
Cláusula ALLCláusula ALLÉ utilizado com subconsultas que produzemuma única coluna de números
Exemplo: Listar os empregados cujossalários são maiores do que o salário decada funcionário do departamento 15
SELECT Cad, Nome, Sexo, SalarioFROM EmpregadoWHERE Salario > ALL ( SELECT Salario
FROM EmpregadoWHERE Num_Dep = 15) ;
7778
Cláusulas EXISTS Cláusulas EXISTS e NOT EXISTSe NOT EXISTS
Foram projetadas para uso apenas comsubconsultas
EXISTS
Retorna TRUETRUE ⇔ existe pelo menos umalinha produzida pela subconsulta
Retorna FALSEFALSE ⇔ a subconsulta produzuma tabela resultante vazia
78
25/06/2017
14
79
Cláusulas EXISTS Cláusulas EXISTS e NOT EXISTSe NOT EXISTS
Exemplo: Liste todos os empregados quetrabalham no departamento de Informática
SELECT Cad, Nome, Sexo, SalarioFROM Empregado E WHERE EXISTS( SELECT D.Numero FROM Departamento D
WHERE E.Num_Dep = D.Numero ANDD.Nome = ‘Informática ’) ;
7980
Regras Regras Genéricas Genéricas de de SubconsultasSubconsultas
A cláusula ORDERORDER BYBY não pode ser usadaem uma subconsultaA lista de atributos especificados no SELECTSELECTde uma subconsulta deve conter um únicoelemento (exceto para EXISTS)Nomes de atributos especificados nasubconsulta estão associados às tabelaslistadas na cláusula FROMFROM da mesma
É possível referir-se a uma tabela dacláusula FROMFROM da consulta mais externautilizando qualificadores de atributos
80
81
Regras Regras Genéricas Genéricas de de SubconsultasSubconsultas
Quando a subconsulta é um dos operandosenvolvidos em uma comparação, ela deveaparecer no lado direito da comparação
8182
Operações de ConjuntoOperações de Conjunto
UNIONLinhas duplicadas são removidas da tabelaresultanteExemplo: Construa uma lista de todos oslocais onde existe um departamento ou umprojeto
( SELECT Local FROM ProjetoWHERE Local IS NOT NULL )
UNION( SELECT Local FROM Locais ) ;
82
83
Operações de ConjuntoOperações de Conjunto
INTERSECT
Exemplo: Construa uma lista de todos oslocais onde existe ambos um departamentoe um projeto
( SELECT Local FROM Projeto )INTERSECT
( SELECT Local FROM Locais ) ;
8384
Operações de ConjuntoOperações de Conjunto
MINUS
Exemplo: Construa uma lista de todos oslocais onde existe um departamento masnenhum projeto
( SELECT Local FROM Locais )MINUS
( SELECT Local FROM Projeto );
84
25/06/2017
15
85
Garantindo Garantindo Privilégios Privilégios de de AcessoAcesso
Comando GRANTGRANT
Onde
: SELECT, INSERT, DELETE,UPDATE, ALL PRIVILEGES e
: usuário cadastrado, PUBLIC
GRANT ON TO ;
8586
Garantindo Garantindo Privilégios Privilégios de de AcessoAcesso
Exemplo: Conceder a permissão de consultasobre a tabela EMPREGADO à usuária acs
GRANT SELECT ON Empregado TO acs;
86
87
Removendo Removendo Privilégios Privilégios de de AcessoAcesso
Comando REVOKEREVOKE
Exemplo: Remover a permissão deconsulta dada aos demais usuários
REVOKE ON FROM ;
REVOKE SELECT ON Projeto FROM PUBLIC;
8788
88
ExercícioExercício
CodigoG: NumberGravadora
Nome : Varchar2 (60)Endereço : Varchar2 (60)Telefone : Varchar2 (20)Contato: Varchar2 (20)URL: Varchar2 (80)
CodigoCD: NumberCD
Nome : Varchar2 (60)Preco : Number (14, 2)DataLançamento : DateCD_indicado: NumberCod_gravadora: Number
CodigoCD: NumberFaixa
Numero_faixa: Number
CodigoMusica: Number
CodigoM: IntegerMusica
Nome : Varchar2 (60)Duracao : Number (6, 2)
Escreva comandos SQL para criar as tabelas:
Categoria: Number
8989
ExercícioExercício
CodigoA: NumberAutor
Nome : Varchar2 (60)Endereço : Varchar2 (60)Telefone : Varchar2 (20)Idade: Number
CodigoC: NumberCD_Categoria
Menor_preco : Number (14, 2)
Maior_preco : Number (14, 2)
CodigoAutor: NumberMusica_AutorCodigoMusica: Number
Escreva comandos SQL para criar as tabelas:
9090
ExercícioExercícioEscreva comandos SQL para criar as tabelas:
CREATE TABLE ( Atributo1 Tipo1,Atributo2 Tipo2, ... ,AtributoN TipoN,
CONSTRAINT _pkey PRIMARY KEY (),
CONSTRAINT _fkey FOREIGN KEY( )
REFERENCES ( ) );
25/06/2017
16
9191
ExercícioExercícioEscreva comandos SQL para criar as tabelas:
CREATE TABLE Gravadora( CodigoG Number,
Nome Varchar2 (60),Endereco Varchar2 (60),Telefone Varchar2 (20), Contato Varchar2 (20),URL Varchar2 (80),
CONSTRAINT gravadora_pkey PRIMARY KEY(CodigoG) );
9292
ExercícioExercícioEscreva comandos SQL para criar as tabelas:
CREATE TABLE Faixa( CodigoCD Number,CodigoMusica Number,Numero_faixa Number,
CONSTRAINT faixa_pkey PRIMARY KEY (CodigoCD, CodigoMusica),
CONSTRAINT faixa_fkey1 FOREIGN KEY (CodigoCD ) REFERENCESCD
(CodigoCD ),CONSTRAINT faixa _fkey2
FOREIGN KEY (CodigoMusica ) REFERENCESMusica(CodigoM ) );
9393
ExercícioExercícioEscreva comandos SQL para criar as tabelas:
CREATE TABLE CD( CodigoCD Number,Nome Varchar2 (60),Preco Number (14, 2),DataLancamento Date,CD_indicado Number,Cod_gravadora Number, Categoria Number,
CONSTRAINT cd_pkey PRIMARY KEY (CodigoCD),
CONSTRAINT cd_fkey1 FOREIGN KEY (Cod_gravadora ) REFERENCESGravadora (CodigoG )
CONSTRAINT cd_fkey2 FOREIGN KEY (Categoria ) REFERENCES CD_Categoria (CodigoC) );
9494
ExercícioExercícioEscreva comandos SQL para criar as tabelas:
CREATE TABLE Musica( CodigoM Number,Nome Varchar2 (60),Duracao Number (6, 2),
CONSTRAINT musica_pkey PRIMARY KEY (CodigoM) );
CREATE TABLE CD_Categoria( CodigoC Number,
Menor_preco Number (14, 2), Maior_preco Number (14, 2),
CONSTRAINT CD_categoria_pkey PRIMARY KEY (CodigoC) );
9595
ExercícioExercícioEscreva comandos SQL para criar as tabelas:
CREATE TABLE Autor( CodigoA Number,
Nome Varchar2 (60),Endereco Varchar2 (60),Telefone Varchar2 (20), Idade Number,
CONSTRAINT autor_pkey PRIMARY KEY (CodigoA) );
9696
ExercícioExercícioEscreva comandos SQL para criar as tabelas:
CREATE TABLE Musica_Autor( CodigoAutor Number,CodigoMusica Number,
CONSTRAINT musica_autor_pkey PRIMARY KEY (CodigoAutor, CodigoMusica),
CONSTRAINT musica_autor_fkey1 FOREIGN KEY (CodigoAutor ) REFERENCESAutor
(CodigoA ),CONSTRAINT musica_autor _fkey2
FOREIGN KEY (CodigoMusica ) REFERENCESMusica(CodigoM ) );
25/06/2017
17
9797
ExercícioExercícioConsidere o seguinte esquema relacional:
9898
ExercícioExercícioConsidere o seguinte esquema relacional:
9999
ExercícioExercícioConsidere o seguinte esquema relacional:
100100
ExercícioExercícioEscreva as seguintes consultas em SQL:
a) Listar o nome, endereço, telefone e contatode todas as gravadoras
SELECT Nome, Endereco, Telefone, ContatoFROM Gravadora ;
b) Listar todos os atributos de CD
SELECT *FROM CD ;
101101
ExercícioExercício
c) Listar nome e endereço dos autores cujaidade maior que 20
SELECT Nome, Endereco FROM AutorWHERE Idade > 20;
102102
ExercícioExercício
d) Listar código e nome das músicas cuja
duração maior que 1h e cujo nome começa com ‘A’
SELECT CodigoM, NomeFROM MusicaWHERE Duracao > 1 AND NOME LIKE ‘A%’;
25/06/2017
18
103103
ExercícioExercício
e) Listar nome, preço, e data de lançamento de
um CD de um dado código
SELECT Nome, Preco, DataLancamentoFROM CDWHERE CodigoCD = &cod ;
104104
ExercícioExercício
f) Listar o código, nome, endereço e contato
das gravadoras ordenados pelo contato
SELECT CodigoG, Nome, Endereco, ContatoFROM GravadoraORDER BY Contato ;
105105
ExercícioExercício
g) Listar nome e preço dos CDs e os nomes
de suas respectivas gravadoras, ordenados
pelo preço dos CDs
SELECT C.Nome, C. Preco, G.NomeFROM CD C, Gravadora GWHERE C.Cod_gravadora = G.CodigoGORDER BY C. Preco ;
106106
ExercícioExercício
h) Mostrar os códigos, nomes e novos preços dosCDs calculados com base no reajuste de 20%para aqueles cuja categoria é igual a 5
SELECT CodigoCD , Nome, (Preco * 1.20 ) AS Reajuste
FROM CD WHERE Categoria = 5 ;
107107
ExercícioExercícioi) Mostrar o valor do CD mais caro e o
código e nome do CD que possui este valor
SELECT CodigoCD , Nome, PrecoFROM CD WHERE Preco IN
(SELECT MAX(Preco ) FROM CD );
j) Quantos CDs custam mais que R$50,00?
SELECT Count(*)FROM CD WHERE Preco > 50 ;
108108
ExercícioExercíciok) Indique o preço médio dos CDs
SELECT AVG (Preco )FROM CD;
l) Quais os diferentes preços dos CDs?
SELECT Distinct PrecoFROM CD ;
25/06/2017
19
109109
ExercícioExercício
m) Listar os quantitativos de CDs de cadagravadora
SELECT Cod_gravadora , COUNT(*)FROM CDGROUP BY Cod_gravadora ;
110110
ExercícioExercício
n) Listar o número total de CDs que custammenos que R$40,00 de cada gravadora que tenhaproduzido mais de 5 CDs
SELECT Cod_gravadora , COUNT(*)FROM CDWHERE Preco < 40GROUP BY Cod_gravadoraHAVING COUNT(*) > 5;
111111
ExercícioExercícioo) Listar o nome do CD e o nome da gravadoraque o produziu.
SELECT C.Nome, G.NomeFROM CD C, Gravadora GWHERE C.Cod_gravadora = G.CodigoG ;
p) Listar os nomes das músicas que foramgaravadas em CDs.
SELECT M.NomeFROM Musica M , Faixa F, CD CWHERE CD.CodigoCD = F.CodigoCD AND
F.CodigoMusica = M.CodigoM ;112
112
ExercícioExercício
q) Listar os nomes dos CDs, em ordemalfabética, cujas gravadoras possuem “João” comocontato e são localizadas em Recife.
SELECT C.Nome, G.NomeFROM CD C, Gravadora GWHERE C.Cod_gravadora = G.CodigoG AND G.Contato LIKE ‘Joao %’ORDER BY C.Nome;
113113
ExercícioExercício
r) Para cada CD, liste o nome do CD, e paracada um deles, listar o número da faixa, o nome e aduração da música, ordenando a resposta pelonome do CD e pelo número da faixa
SELECT C.Nome, F.NumeroFaixa , M.Nome, M.DuracaoFROM CD C, Faixa F, Musica MWHERE C.CodigoCD = F.CodigoCD AND F.CodigoMusica = M.CodigoM ORDER BY C.Nome , F.NumeroFaixa ;
114114
ExercícioExercício
s) Encontre o total de músicas de cada CD porgravadora, informando o cadastro do CD em ordemcrescente
SELECT C.Cod_gravadora , C.CodigoCD ,COUNT(*) AS TotalFROM CD C, Faixa FWHERE C.CodigoCD = F.CodigoCD GROUP BY C.Cod_gravadora , C.CodigoCDORDER BY C.Cod_gravadora , C.CodigoCD ;
25/06/2017
20
115115
ExercícioExercício
t) Listar os nomes dos CDs e de suasrespectivadas gravadoras que possui algumamúsica com duração maior que 1h
SELECT DISTINCT C.Nome , G.NomeFROM CD C, Gravadora G , Faixa F, Musica MWHERE C.Cod_gravadora = G.CodigoG AND C.CodigoCD = F.CodigoCD AND F.CodigoMusica = M.CodigoMAND M.Duracao > 1;
116116
ExercícioExercício
u) Listar os nomes dos autores e de suasrespectivadas músicas com duração maior que 1h
SELECT A.Nome , M.NomeFROM Autor A , Musica M , Musica_Autor UWHERE A.CodigoA = U.CodigoAutor AND U.CodigoMusica = M.CodigoMGROUP BY A.Nome ;
117117
ExercícioExercícioPara este esquema relacional, escreva asseguintes consultas aninhadas:
118118
ExercícioExercício
119119
ExercícioExercício
120120
ExercícioExercícioa) Use o conceito de subconsulta para listar os
nomes, preços e datas de lançamento dosCDs gravados pela ‘somlivre’
SELECT Nome, Preco , DataLancamentoFROM CDWHERE Cod_gravadora =
(SELECT CodigoGFROM Gravadora
WHERE Nome = ‘somlivre ’ );
25/06/2017
21
121121
ExercícioExercíciob) Listar os códigos e nomes dos CDs e osnomes de suas respectivas gravadoras para osCDs cujos preços são maiores do que o preçomédio, mostrando o quanto são maiores
SELECT C.CodigoCD , C.Nome, G.Nome,C.Preco – (SELECT AVG (Preco ) FROM CD) ASDiferencaFROM CD C, Gravadora GWHERE C. Preco > ( SELECT AVG ( Preco )
FROM CD) AND C.Cod_gravadora = G.CodigoG ;
122122
ExercícioExercícioc) Listar os CDs cujos preços são maiores doque o preço de pelo menos um CD dagravadora ‘somlivre’
SELECT CodigoCD , Nome, DataLancamento FROM CDWHERE Preco > SOME ( SELECT Preco
FROM CD C, Gravadora GWHERE C.Cod_gravadora = G.CodigoG AND G.Nome = ‘somlivre’) ;
123123
ExercícioExercíciod) Listar os CDs cujos preços são maiores doque o preço de cada CD da gravadora ‘somlivre’
SELECT CodigoCD , Nome, DataLancamento FROM CDWHERE Preco > ALL ( SELECT Preco
FROM CD C, Gravadora GWHERE C.Cod_gravadora = G.CodigoG AND G.Nome = ‘somlivre’) ;
124124
ExercícioExercícioe) Construa uma lista de todas as cidadesonde existe uma gravadora ou um autor
( SELECT Cidade FROM GravadoraWHERE Cidade IS NOT NULL )
UNION( SELECT Cidade FROM Autor
WHERE Cidade IS NOT NULL ) ;
125125
ExercícioExercíciof) Construa uma lista de todas as cidadesque possuem uma gravadora e um autor.
( SELECT Cidade FROM GravadoraWHERE Cidade IS NOT NULL )
INTERSECT( SELECT Cidade FROM Autor
WHERE Cidade IS NOT NULL ) ;
126126
ExercícioExercíciog) Construa uma lista de todas as cidadesque possuem uma gravadora mas nenhumautor
( SELECT Cidade FROM GravadoraWHERE Cidade IS NOT NULL )
MINUS( SELECT Cidade FROM Autor
WHERE Cidade IS NOT NULL ) ;
25/06/2017
22
127127
ExercícioExercício2) Escreva comandos em SQL para inserçãode dados nas tabelas do esquema anterior
INSERT INTO Gravadora (CodigoG , Nome,Endereco , Cidade , Telefone , Contato , URL) VALUES (100, ‘somlivre ’, ‘R. do Futuro 98 ’,‘Recife ’, 34270209, ‘Pedro Alves ’, ‘https://www.somlivre.com ’);
INSERT INTO Gravadora (CodigoG , Nome,Endereco , Cidade , Telefone , Contato , URL) VALUES (200, ‘sony music ’, ‘Praia do Flamengo 200 ’, ‘Rio ’, 98289696, ‘Maria Costa ’, ‘https://www.sonymusic.com ’);
128128
ExercícioExercício
INSERT INTO CD (CodigoCD , Nome, Preco ,DataLancamento , CD_indicado , Cod_gravadora , Categoria ) VALUES (10, ‘Avenida Brasil ’, 57.00, TO_DATE(’01/01/2012’, ‘dd/mm/aaaa ’), 2, 100, 150);
INSERT INTO CD (CodigoCD , Nome, Preco ,DataLancamento , CD_indicado , Cod_gravadora , Categoria ) VALUES (20, ‘Extraordinário Amor de Deus ’, 45.00, TO_DATE(’18/10/2013’, ‘dd/mm/aaaa ’), 1, 200, 250);
129129
ExercícioExercício
3) Escreva um comando em SQL paraatualizar o preço dos CDs gravados pela‘somlivre’ para refletirem um aumento de 20%
UPDATE CD SET Preco = Preco * 1.20WHERE Nome = ‘somlivre ’;
130130
ExercícioExercício
4) Escreva um comando em SQL pararemover os CDs cujas gravadoras estão emSão Paulo ou Rio de Janeiro
DELETE FROM CD WHERE Cod_gravadora =(SELECT CodigoG FROM Gravadora
WHERE Cidade = ‘Rio de Janeiro ’ OR Cidade = ‘São Paulo ’ );
131131
ExercícioExercício
5) Escreva um comando em SQL para criaruma visão dos CDs produzidos pela ‘somlivre’que têm músicas com duração mínima de 1h
CREATE VIEW Cdsomlivre ASSELECT C.CodigoCD , C.Nome , C.PrecoFROM CD C, Gravadora G , Faixa F, Musica MWHERE G.Nome = ‘somlivre ’AND C.Cod_gravadora = G.CodigoGAND C.CodigoCD = F.CodigoCDAND F.CodigoMusica = M. CodigoMAND M.Duracao > 1;