Exercícios de Sub-consultas

Resolver as consultas que seguem usando SQL.

As consultas referem-se ao banco de dados acadêmico das questões anteriores. O modelo lógico da base de dados está disponível tanto em formato texto, quanto em formato de diagrama.

1. Obter os códigos dos diferentes departamentos que tem turmas no ano-semestre 2002/1
   SELECT DISTINCT coddepto
   FROM turma
   WHERE anosem=20021
   
2. Obter os códigos dos professores que são do departamento de código 'INF01' e que ministraram ao menos uma turma em 2002/1.
   SELECT DISTINCT Professor.CodProf
   FROM Professor, ProfTurma
   WHERE Professor.CodProf=ProfTurma.CodProf
   AND AnoSem=20021
   AND Professor.CodDepto='INF01'
   
3. Obter os horários de aula (dia da semana,hora inicial e número de horas ministradas) do professor "Antunes" em 20021.
   SELECT DISTINCT
   Horario.DiaSem,
   Horario.HoraInicio,
   Horario.NumHoras
   FROM Professor,
   ProfTurma,
   Horario
   WHERE Professor.NomeProf='Antunes'
   AND Horario.AnoSem=20021
   AND Professor.CodProf = ProfTurma.CodProf
   AND ProfTurma.AnoSem = Horario.AnoSem
   AND ProfTurma.CodDepto = Horario.CodDepto
   AND ProfTurma.NumDisc = Horario.NumDisc
   AND ProfTurma.SiglaTur = Horario.SiglaTur
   
4. Obter os nomes dos departamentos que têm turmas que, em 2002/1, têm aulas na sala 101 do prédio denominado 'Informática - aulas'.
   SELECT DISTINCT NomeDepto
   FROM Depto,
   Horario,
   Predio
   WHERE Depto.CodDepto=Horario.CodDepto
   AND Horario.CodPred=Predio.CodPred
   AND NumSala=101
   AND NomePred='Informática - aulas'
   AND AnoSem=20021
   
5. Obter os identificadores das salas (código do prédio e número da sala) que, em 2002/1:
   • nas segundas-feiras (dia da semana = 2), tiveram ao menos uma turma do departamento 'Informática', e
   • nas quartas-feiras (dia da semana = 4), tiveram ao menos uma turma ministrada pelo professor denominado 'Antunes'.
   SELECT CodPred, NumSala
   FROM Horario,Depto
   WHERE Horario.CodDepto=Depto.CodDepto
   AND NomeDepto='Informática'
   AND DiaSem=2
   AND AnoSem=20021
   INTERSECT
   SELECT CodPred, NumSala
   FROM Horario,
   ProfTurma,
   Professor
   WHERE Professor.CodProf=ProfTurma.CodProf
   AND ProfTurma.AnoSem=Horario.AnoSem
   AND ProfTurma.CodDepto=Horario.CodDepto
   AND ProfTurma.NumDisc=Horario.NumDisc
   AND ProfTurma.SiglaTur=Horario.SiglaTur
   AND NomeProf='Antunes'
   AND DiaSem=4
   AND Horario.AnoSem=20021
   

6. Obter o dia da semana, a hora de início e o número de horas de cada horário de cada turma ministrada por um professor de nome `Antunes', em 2002/1, na sala número 101 do prédio de código 43423.
   SELECT DiaSem, HoraInicio, NumHoras FROM Professor,
   ProfTurma,
   Horario
   WHERE Professor.CodProf=ProfTurma.CodProf
   AND ProfTurma.AnoSem=Horario.AnoSem
   AND ProfTurma.CodDepto=Horario.CodDepto
   AND ProfTurma.NumDisc=Horario.NumDisc
   AND ProfTurma.SiglaTur=Horario.SiglaTur
   AND NumSala=101
   AND ProfTurma.AnoSem=20021
   AND NomeProf='Antunes'
   AND CodPred=43423
   
   
7. Um professor pode ministrar turmas de disciplinas pertencentes a outros departamentos. Para cada professor que já ministrou aulas em disciplinas de outros departamentos, obter o código do professor, seu nome, o nome de seu departamento e o nome do departamento no qual ministrou disciplina.
   SELECT Professor.CodProf,
   NomeProf,
   DeptoProf.NomeDepto AS DeptoProf,
   DeptoDisc.NomeDepto AS DeptoDisc
   FROM Professor,
   ProfTurma,
   Depto AS DeptoProf,
   Depto as DeptoDisc
   WHERE Professor.CodProf=ProfTurma.CodProf
   AND Professor.CodDepto<>ProfTurma.CodDepto
   AND Professor.CodDepto=DeptoProf.CodDepto
   AND ProfTurma.CodDepto=DeptoDisc.CodDepto
   
8. Obter o nome dos professores que possuem horários conflitantes (possuem turmas que tenham a mesma hora inicial, no mesmo dia da semana e no mesmo semestre). Além dos nomes, mostrar as chaves primárias das turmas em conflito.
   SELECT DISTINCT Professor.NomeProf
   FROM Horario,
   Horario AS Horario2,
   ProfTurma AS ProfTurma2,
   Professor
   WHERE Horario.AnoSem = Horario2.AnoSem
   AND Horario.DiaSem = Horario2.DiaSem
   AND Horario.HoraInicIO = Horario2.HoraInicIO
   
   AND Horario.CodDepto = ProfTurma.CodDepto
   AND Horario.NumDisc = ProfTurma.NumDisc
   AND Horario.AnoSem = ProfTurma.AnoSem
   AND Horario.SiglaTur = ProfTurma.SiglaTur
   
   AND Horario2.CodDepto = ProfTurma2.CodDepto
   AND Horario2.NumDisc = ProfTurma2.NumDisc
   AND Horario2.AnoSem = ProfTurma2.AnoSem
   AND Horario2.SiglaTur = ProfTurma2.SiglaTur
   
   AND ProfTurma.CodDepto = ProfTurma2.CodDepto
   AND Professor.CodProf = ProfTurma2.CodDepto
   
9. Para cada disciplina que possui pré-requisito, obter o nome da disciplina seguido do nome da disciplina que é seu pré-requisito.
   SELECT Disciplina.NomeDisc, DiscPre.NomeDisc
   FROM Disciplina,
   PreReq,
   Disciplina AS DiscPre
   WHERE Disciplina.CodDepto=PreReq.CodDepto
   AND Disciplina.NumDisc=PreReq.NumDisc
   AND PreReq.CodDeptoPreReq=DiscPre.CodDepto
   AND PreReq.NumDiscPreReq=DiscPre.NumDisc
   
10. Obter os nomes das disciplinas que não têm pré-requisito.
    SELECT Nomedisc
    FROM Disciplina
    EXCEPT
    SELECT Nomedisc
    FROM PreReq,
    Disciplina
    WHERE Disciplina.CodDepto=PreReq.CodDepto
    AND Disciplina.NumDisc=PreReq.NumDisc
    
11. Obter o nome de cada disciplina que possui ao menos dois pré-requisitos.
    SELECT DISTINCT NomeDisc
    FROM Disciplina,
    PreReq AS Pre1,
    PreReq AS Pre2
    WHERE Disciplina.CodDepto=Pre1.CodDepto
    AND Disciplina.NumDisc=Pre1.NumDisc
    AND Disciplina.CodDepto=Pre2.CodDepto
    AND Disciplina.NumDisc=Pre2.NumDisc
    AND (Pre1.CodDeptoPreReq<>Pre2.CodDeptoPreReq
    OR Pre1.NumDiscPreReq<>Pre2.NumDiscPreReq)

Aula 5 - Sub-consultas

Sub-consultas

Consulta que se faz sobre os dados que nos dá outra consulta. Seu formato é:

SELECT______
FROM________
WHERE CONDICAO OPERADOR (SELECT ______
FROM ___________
WHERE CONDICAO OPERADOR); Exemplo:

Obtemos os jogadores com a mesma posição que "Sanchez":

SELECT SOBRENOME
FORM EMPLE
WHERE POSICAO = (SELECT OFICIO
FROM EMPLE
WHERE SOBRENOME LIKE 'GIL');

Selecionamos em todos os campos da tabela Jogadores cuja sede está em Madrid ou Barcelona:

SELECT *
FROM JOGADORES
WHERE EQUIPE_NOM IN (SELECT EQUIPE_NOM
FROM SEDE
WHERE LOC IN ('MADRID', 'BARCELONA');
FROM SEDE
WHERE LOC IN ('MADRID', 'BARCELONA');

Aula 4 - Relatórios

Relatórios

Comando:

REPORT DISTINCT / UNIQUE

[ atributo(s) ]

REPORTTOP

PAGETOP

TOP

DETAIL

NONE

BOTTOM

PAGEBOTTOM

REPORTBOTTOM

FROM [ tabela(s) ]

[ WHERE clausula-where ]

[ GROUP BY clausula-grupo ]

[ ORDER BY clausula-order by ];

Como exemplo converteremos um simples Select em um Report, temos:

SELECT EMPNOME

FROM EMP

WHERE DEPNUME = 1000;

REPORT

DETAIL EMPNOME

WHERE DEPNUME = 1000;

Podemos direcionar a saida de um relatório tanto para um arquivo como para uma impressora.

Para um arquivo:

REPORT ON “RELAT.DAT” ...

Para uma impressora:

REPORT ON LP:” ...

Agora incrementando um report temos:

REPORT

REPORTTOP COL 10, “*** RELATORIO DE FUNCIONARIOS *** “,

TODAY %Q”DD/MM/YY”, SKIP,

COL 10, “=================================“, SKIP 2

DETAIL COL 10, NOME %C22, SALARIO %FS, ADMISSAO %Q”DD/MM/YY”

REPORTBOTTOM COL 10,

“=================================“, SKIP,

COL 20, “TOTAL:”, TOTAL(SALARIO)

FROM EMP

ORDER BY NOME;

Onde:

REPORTTOP - O que sera impresso no topo do relatório.

PAGETOP - Impresso em cada topo de pagina.

TOP - Impresso em cada Topo do Sort-Grupo do relatório.

DETAIL - O que sera impresso em cada linha.

NONE - Se não tiver resultado o select, não sera impresso o relatório.

BOTTOM - Impresso em cada Bottom do Sort-Grupo do relatório

PAGEBOTTOM - O que sera impresso no rodapé de cada pagina.

REPORTBOTTOM - O que sera impresso no rodape do relatório.

Formatos:

%C - caracter

%D - data

y - ano,

n - mes numérico,

a - mes alfanumérico,

d - dia,

j - dia e ano juliano

Exemplo: %D”dd/mm/yy”

%I - inteiro

%F - ponto flutuante

%FSZ onde: S - separador de 3 digitos e decimal point

Z - zeros serão suprimidos

%Q - data

%J - Hora

h - hora, m - minutos, s - segundos

%T - hora

E temos as funções: TOTAL, AVERAGE, MAXIMUM, MINIMUM.

Exercícios de manipulação de dados

Exercícios de manipulação de dados

1. Crie o diretório labs dentro do diretório c:\oracle e copie o script lab9_1.sql para
este diretório. Execute o script lab9_1.sql para criar a tabela my_employee:

@c:\orant\labs\lab9_1

2. Veja a estrutura da tabela my_employee:

3. Adicione a primeira linha da tabela abaixo na tabela my_employee. Não liste as
colunas no comando insert.

Insert into my_employee
Values
(1, 'Patel', 'Ralph', 'rpatel', 795);

4. Crie um script chamado loademp.sql para inserir dados na tabela my_employee
de forma interativa. Este script deve colocar um prompt para o usuário entrar
com o primeiro nome, último nome e salário. Concatene a primeira letra do
primeiro nome com os primeiros sete caracteres do último nome para produzir o
userid:

SET ECHO OFF
SET VERIFY OFF
PROMPT 'Por favor entre com o primeiro nome: '
ACCEPT p_first_name
PROMPT 'Por favor entre com o ultimo nome: '
ACCEPT p_last_name
PROMPT 'Por favor entre com o numero do empregado: '
ACCEPT p_id
PROMPT 'Por favor entre com o salario: '
ACCEPT p_sal
INSERT INTO my_employee
(id, last_name, first_name, userid, salary)
Values
(&p_id, '&p_last_name',
'&p_first_name',
substr('&p_first_name',1,1) || substr('&p_last_name', 1, 7),
&p_sal)
/
SET VERIFY ON
SET ECHO ON

5. Insira as duas linhas seguintes através do script criado:

@loademp.sql

6. Confirme as adições realizadas na tabela:

Select *
From my_employee;

7. Faça com que as alterações sejam permanentes:
Commit;

Aula 3 - Manipulação de dados

Manipulação de dados

Insert, Update e Delete:

Insert:

Adicionam-se filas de dados em uma tabela:

INSERT INTO NOMBTABELA [(COL [,COL]…)]
VALUES (VALOR [,VALOR]…);

Nometabela= É a tabela na que se vão inserir as filas.

Propriedades:

• Se as colunas não se especificam na cláusula Insert se consideram, por padrão, todas as colunas da tabela.
  
• As colunas às quais damos valores se identificam pelo seu nome.
  
• A associação coluna valor é posicional.
  
• Os valores que se dão às colunas devem coincidir com o tipo de dado definido na coluna.
  
• Os valores constantes de tipo caractere tem de ir fechados entre aspas simples (' ') (os de tipo data também).

Com Select:

Adicionam-se tantas filas como devolva a consulta:

INSERT INTO NOMBTABELA [(COL [,COL]…)]
SELECT {COLUNA [, COLUNA]… | *}
FROM NOMETABELA2 [CLAUSULAS DE SELECT];

Update:

Atualiza os valores das colunas para uma ou várias filas de uma tabela:

UPDATE NOMETABELA
SET COLUNA1= VALOR1, …, COLUNAN= VALORN
WHERE CONDICAO;

Set= Indica as colunas que vão se atualizar e seus valores.

Com Select:

Quando a subconsulta (orden select) forma parte de SET, deve selecionar o mesmo número de colunas, (com tipos de dados adequados) que os que existem entre parênteses ao lado de SET.

UPDATE NOMETABELA
SET COLUNA= VALOR1, COLUNA2= VALOR2, …
WHERE COLUNA3= (SELECT…)

OU

UPDATE NOMETABELA
SET (COLUNA1, COLUNA2, …)= (SELECT …)
WHERE CONDICAO;

Delete:

Elimina uma ou várias filas de uma tabela:

DELETE [FROM] NOMETABELA
WHERE CONDICAO;

Exercicios de criação de tabelas

Exercicios de criação de tabelas

1- Crie uma tabela Departamento com os respectivos campos: Codigo, nome, locação, orçamento. Utilize dados mais adequados!

create table Dept

(DepNume integer(4) not null,

DepNome char(20) not null,

DepLoca char(20) not null,

DepOrca integer(12,2),

primary key (DepNume)

);

2- Crie uma tabela Empregados com os respectivos campos: Codigo, nome, gerente, serviço, departamento, sala. Utilize dados mais adequado!
Nao se esqueça da chave estrangeira!

Resposta:

create table Emp

(EmpNume integer(5) not null,

EmpNome char(30) not null,

EmpGere integer(5) ,

EmpServ char(20) ,

DepNume integer(4) not null,

EmpAdmi date not null,

EmpSala integer(10,2),

EmpComi integer(10,2),

primary key (EmpNume),

foreign key has (DepNume)

);

3- Delete a tabela Departamento

drop table Departamento;

Aula 2 - Criando Tabelas

Criação de tabelas

Em geral, a maioria dos bancos de dados possui potentes editores de bancos que permitem a criação rápida e simples de qualquer tipo de tabela com qualquer tipo de formato.

Entretanto, uma vez que o banco de dados esteja hospedado no servidor, pode acontecer o caso de que queiramos introduzir uma nova tabela já seja com caráter temporário (para gerenciar um carrinho de compra, por exemplo) ou permanente, por necessidades concretas de nossa aplicação.

Nestes casos, podemos, a partir de uma sentença SQL, criar a tabela com o formato que desejarmos, o qual pode nos poupar mais que um quebra-cabeça.

Estes tipos de sentenças são especialmente úteis para bancos de dados como Mysql, os quais trabalham diretamente com comandos SQL e não por meio de editores.

Para criar uma tabela devemos especificar diversos dados: O nome que queremos atribuir, os nomes dos campos e suas características. Ademais, pode ser necessário especificar quais destes campos vão ser índices e de que tipo serão.

A sintaxe de criação pode variar ligeiramente de um banco de dados para outro já que os tipos de campos aceitos, não estarão completamente padronizados.

A seguir explicamos meramente a sintaxe desta sentença e lhes propomos uma série de exemplos:

Sintaxe

Create Table nome_tabela
(
nome_campo_1 tipo_1
nome_campo_2 tipo_2
nome_campo_n tipo_n
Key(campo_x,...)
)

Ponhamos agora como exemplo a criação da tabela pedidos que empregamos em capítulos anteriores:

Create Table pedidos
(
id_pedido INT(4) NOT NULL AUTO_INCREMENT,
id_cliente INT(4) NOT NULL,
id_artigo INT(4)NOT NULL,
data DATE,
quantidade INT(4),
total INT(4), KEY(id_pedido,id_cliente,id_artigo)
)

Neste caso criamos os campos id os quais são considerados de tipo inteiro de uma longitude especificada pelo número entre parênteses. Para id_pedido requeremos que tal campo se incremente automaticamente (AUTO_INCREMENT) de uma unidade a cada introdução a um novo registro para, desta forma, automatizar sua criação. Por outro lado, para evitar uma mensagem de erro, é necessário requerer que os campos que vão ser definidos como índices não possam ser nulos (NOT NULL).

O campo data é armazenado com formato de data (DATE) para permitir sua correta exploração a partir das funções previstas a tal efeito.

Finalmente, definimos os índices numerando-os entre parênteses precedidos da palavra KEY ou INDEX.

Da mesma forma poderíamos criar a tabela de artigos com uma sentença como esta:

Create Table artigos
(
id_artigo INT(4) NOT NULL AUTO_INCREMENT,
titulo VARCHAR(50),
autor VARCHAR(25),
editorial VARCHAR(25),
preco REAL,
KEY(id_artigo)
)

Neste caso pode-se ver que os campos alfanuméricos são introduzidos da mesma forma que os numéricos. Voltemos a recordar que em tabelas que têm campos comuns é de vital importância definir estes campos da mesma forma para o bom funcionamento da base.

Muitas são as opções que se oferecem ao gerar tabelas. Não vamos a trata-las detalhadamente, pois sai do estritamente prático. Mostraremos somente alguns dos tipos de campos que podem ser empregados na criação de tabelas com suas características:

Tipo	Bytes	Descrição
INT ou INTEGER	4	Números inteiros. Existem outros tipos de maior ou menor longitude específicos de cada banco de dados.
DOUBLE ou REAL	8	Números reais (grandes e com decimais). Permitem armazenar todo tipo de número não inteiro.
CHAR	1/caractere	Alfanuméricos de longitude fixa pré-definida
VARCHAR	1/caractere+1	Alfanuméricos de longitude variável
DATE	3	Datas, existem múltiplos formatos específicos de cada banco de dados
BLOB	1/caractere+2	Grandes textos não indexáveis
BIT ou BOOLEAN	1	Armazenam um bit de informação (verdadeiro ou falso)