sql - cin ufpeasf2/gdi/material/sql_if685.pdfcomandos sql (padrão ansi) criação, alteração e...

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


Selecionando todos os atributos

SELECT * FROM ;

SELECT * FROM Empregado ;

27

Exemplo

Observação

Deve ser usado com cautela pois podecomprometer o desempenho

28


Selecionando tuplas da tabela → cláusulaWHEREWHERE

SELECT FROM WHERE ;

Onde

28

Uma constante, variável ou consulta aninhada

29


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


CREATE TABLE Autor( CodigoA Number,

Nome Varchar2 (60),Endereco Varchar2 (60),Telefone Varchar2 (20), Idade Number,

CONSTRAINT autor_pkey PRIMARY KEY (CodigoA) );

9696


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


9999


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


c) Listar nome e endereço dos autores cujaidade maior que 20

SELECT Nome, Endereco FROM AutorWHERE Idade > 20;

102102


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


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


f) Listar o código, nome, endereço e contato

das gravadoras ordenados pelo contato

SELECT CodigoG, Nome, Endereco, ContatoFROM GravadoraORDER BY Contato ;

105105


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


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


m) Listar os quantitativos de CDs de cadagravadora

SELECT Cod_gravadora , COUNT(*)FROM CDGROUP BY Cod_gravadora ;

110110


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


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


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


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


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


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


119119


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.


INTERSECT( SELECT Cidade FROM Autor


126126

ExercícioExercíciog) Construa uma lista de todas as cidadesque possuem uma gravadora mas nenhumautor


MINUS( SELECT Cidade FROM Autor


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


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


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


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


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;

sql - cin ufpeasf2/gdi/material/sql_if685.pdfcomandos sql (padrão ansi) criação, alteração e...

Documents