Comandos SQL – Exercícios 1

Olá pessoal, como prometido,  colocarei aqui alguns exercícios e suas resoluções. Estes exercícios eu fiz em aula com o Prof. Carrilho da Fatec Americana.

Crie uma base de dados Universidade com as tabelas a seguir:

 Alunos (RA, nome, endereço, cidade)
 Disciplinas (COD_DISC, nome_disc, carga_hor)
 Professores (COD_PROF, nome, endereço, cidade)
 Turma (COD_DISC, COD_TURMA, COD_PROF, ANO,  horário)
 Histórico (RA, COD_DISC, COD_TURMA, COD_PROF,  ANO, freqüência, nota)

Após criar esta tabelas, e incluir alguns dados neslas, poderemos “brincar” e testar todos os comando que aprendemos nos artigos anteirores 1. Encontre o RA dos alunos com nota em BD em 2010 menor que 5 (obs: BD = código da disciplinas).

SELECT ra
FROM HISTORICO
WHERE cod_disc='BD' AND ano=2010 AND nota <5

2. Forneça o RA, nome e nota dos alunos com nota menor que 5 em BD em 2010.

SELECT a.RA, a.NOME, a.NOME
from HISTORICO h, ALUNOS a
where cod_disc='BD' and ano=2010 and nota <5 and a.RA=h.RA

3. Forneça o nome dos professores de BD em 2010.

SELECT distinct(p.NOME)
from PROFESSORES p, TURMA t
where p.COD_PROF=t.COD_PROF and t.ANO=2010 and t.COD_DISC='BD'

4. Encontre o nome, endereço, cidade dos alunos e código das disciplinas onde os alunos tiveram nota menor que 5 em 2010.

SELECT a.nome, a.ENDERECO, a.CIDADE, h.COD_DISC 
from ALUNOS a, HISTORICO h 
where a.RA = h.RA and h.NOTA<5 and h.ano=2010

5. Obtenha o nome e RA dos alunos do professor em 2010.

SELECT distinct a.NOME, a.RA
from ALUNOS a, PROFESSORES p, HISTORICO h 
where h.RA=a.RA and h.COD_PROF = p.COD_PROF
and p.NOME like '%NOME%'
and h.ANO = 2010

6. Localize o nome e RA dos alunos do professor JOSÉ em 2010 ou 2009 que tiveram aulas com o professor MARCOS em algum ano.

SELECT distinct a.NOME, a.RA
from ALUNOS a, PROFESSORES p, HISTORICO h 
where h.RA=a.RA and h.COD_PROF = p.COD_PROF
and p.NOME like '%JOSÉ%'
and (h.ANO = 2010 or h.ANO = 2009)
AND A.RA in
(select H.RA
from HISTORICO h, PROFESSORES p
where h.COD_PROF = p.COD_PROF
and p.nome like '%MARCOS%')

7. Forneça o histórico escolar do aluno de nome Alex, ou seja, seu RA, nome, a lista de disciplinas que ele já cursou contendo o código e nome da disciplina, freqüência e nota e ano que o aluno a cursou.

select a.RA, a.NOME,
h.COD_DISC, d.NOME_DISC,
h.ANO, h.FREQUENCIA, h.NOTA
from HISTORICO h, ALUNOS a, DISCIPLINAS d
where a.RA = h.RA
and h.COD_DISC = d.COD_DISC
and a.NOME like '%ALEX%'

8. Encontre o nome e endereço dos alunos e professores de Campinas.

select a.NOME ,a.ENDERECO, 'aluno'  as tipo
from ALUNOS a
where a.CIDADE='CAMPINAS'
union
select p.NOME ,p.ENDERECO, 'prof'
from PROFESSORES p
where p.CIDADE='CAMPINAS'

9. Forneça o nome dos alunos que cursaram disciplinas com carga horária menor que 60 horas, bem como os respectivos professores que as lecionaram.

select a.NOME, p.NOME
from ALUNOS a, DISCIPLINAS d, PROFESSORES p, HISTORICO h
where
a.RA = h.RA and
d.COD_DISC = h.COD_DISC and
p.COD_PROF = h.COD_PROF and
d.CARGA_HOR<60

10. Localize o nome dos professores que lecionaram matérias nas quais o aluno “Pedro Paulo Cunha” foi reprovado. (nota < 5)

select p.NOME
from ALUNOS a, PROFESSORES p, HISTORICO h
where
a.RA = h.ra and
p.COD_PROF = h.COD_PROF and
upper(a.NOME) = 'PEDRO PAULO CUNHA' AND
h.NOTA<5

11. Encontre o RA dos alunos que já cursaram todas as disciplinas lecionadas pelo prof.João.

Select distinct a.NOME, a.Ra
from ALUNOS a
where not exists (select Cod_disc
                  from PROFESSORES p, TURMA t
                  where p.COD_PROF = t.COD_PROF and
                  P.NOME like '%JOÃO%'
                  and Cod_disc not in (select cod_disc
                                       from HISTORICO h
                                       where a.RA = h.RA))

12. Encontre o Ra, nome e média das notas dos alunos que cursaram todas as matérias lecionadas por professores de Campinas.

Select distinct A.RA, A.NOME, AVG(nota)
from ALUNOS A , HISTORICO h where A.RA= h.RA  and
 not exists
(select Cod_disc
from PROFESSORES p, TURMA t
where p.CIDADE = 'CAMPINAS'and Cod_disc not in
     (select cod_disc
      from HISTORICO h
      where A.RA = h.RA))
group by A.RA, A.NOME

13. Localize o RA e nome dos alunos com nota menor que qualquer uma das notas do aluno de RA 20090121.

select a.RA,a.NOME
from ALUNOS a, HISTORICO h
where a.RA = h.RA and
h.NOTA <any
(select nota from HISTORICO where RA = 20090121)

14. Forneça o Ra, nome e média das notas por alunos.

select a.RA, a.NOME, avg(h.NOTA)
from ALUNOS a, HISTORICO h
where a.RA=h.RA
group by a.RA, a.NOME

15. Encontre o nome dos alunos que não cursaram nenhuma disciplina oferecida em 2010.

select nome
from ALUNOS
where ra not in (select distinct ra from HISTORICO where ano=2010 )

16.  Forneça o nome dos professores que somente lecionaram matérias com carga horária foi inferior a 60 horas.

Select p.NOME
from PROFESSORES p,TURMA t
where p.COD_PROF = t.COD_PROF and 
p.COD_PROF not in ( select t.COD_PROF
                    from TURMA t, DISCIPLINAS d
                    where t.COD_DISC = d.COD_DISC and CARGA_HOR >=60)

17. Encontre o nome dos alunos que não foram reprovados em nenhuma matéria.

select a.NOME
from  ALUNOS a
where a.RA not in (select a.RA
                   from HISTORICO h, DISCIPLINAS d
                   where h.COD_DISC = d.COD_DISC and h.NOTA <6.0)

18. Forneça o RA e nota dos alunos com nota em BD em 2010 menor que a média das notas na disciplina.

select a.NOME, h.NOTA
from  ALUNOS a , HISTORICO h
where a.RA = h.RA and  h.COD_DISC='BD' and ano=2010
            and nota <all (select avg(NOTA)
                           from HISTORICO 
                           where COD_DISC='BD'and ano=2010)

19. Encontre o código e nome das disciplinas e media das notas por disciplina, mostre apenas as disciplinas com média de notas maior que 5.0.

select d.COD_DISC, d.NOME_DISC, avg(nota) 
from  DISCIPLINAS d, HISTORICO h 
where  h.COD_DISC = d.COD_DISC
 group by d.COD_DISC, d.Nome_DISC having avg(nota) >5

20. Forneça o número de alunos que fizeram BD em 2010.

SELECT count(*) from histórico where cód_disc =’bd’ and ano =2010

21. Encontre a disciplina com maior média de notas.

Select nome_disc, avg(nota)
from Disciplinas D, Historico H
Where D.cod_disc = h.cod_disc
group by nome_disc
having avg(nota) >= all( Select avg(nota)
                         from historico
                         group by cod_disc)

22. Forneça o código das disciplinas com média menor que a média das notas em BD.

Select cod_disc, AVG(nota)
from historico
group by cod_disc
having avg(nota) < all (select avg(nota)
                        from historico
                        where cod_disc = 'BD')

23. Forneça o nome dos professores que já lecionaram alguma disciplina para o aluno de RA 5400001.

Select P.nome
from Professores P, historico h
where P.cod_prof = H.cod_prof and H.RA = 5400001

24. Encontre o nome das disciplinas com média de freqüência abaixo de 80%.

Select nome_disc
From Disciplinas D,Historico H
Where H.cod_disc = D.cod_disc
group by nome_disc, carga_hor
having avg(frequencia) < 0.8 * carga_horaria

25. Forneça o nome dos alunos que tiveram no mínimo 2 reprovações em 2010.

Select A.Nome
From Alunos A, Historico H
Where A.RA = H.RA and ano = 2010 and nota < 6.0
group by A.Nome
having count(*) >= 2

26. Quantas vezes o aluno “José da Silva” cursou a disciplina de Banco de Dados.

select count(*)
from ALUNOS a, HISTORICO h, DISCIPLINAS d
where a.ra=h.ra
and d.COD_DISC=h.COD_DISC
and a.NOME ='Jose da Silva'
and d.NOME_DISC='Banco de Dados'

27. Quantos alunos já cursaram a disciplina de Banco de Dados em 2009 e 2010.

select count(*)
from HISTORICO h inner join DISCIPLINAS d on D.COD_DISC= h.COD_DISC
where d.NOME_DISC='Banco de Dados' and (ano=2009 or ano=2010)

28 Forneça o nome dos alunos que obtiveram mais que 2 reprovações em algum ano.

select a.NOME, h.ano, count(*)
from ALUNOS a, HISTORICO h
where h.RA = h.RA and h.NOTA<6.0
group by a.NOME, h.ANO
having count(*) >2

29. Encontre o nome dos professores e das disciplinas onde o número de reprovações foi superior a 20 alunos em 2010.

select p.NOME, d.NOME_DISC
from PROFESSORES p, DISCIPLINAS d, HISTORICO h
where h.COD_PROF = p.COD_PROF and d.COD_DISC= h.COD_DISC
and h.NOTA<6 and h.ANO='2010'
group by p.NOME, d.NOME_DISC
having count(*) >20

30. Forneça o nome dos professores e código das disciplinas lecionadas por ele em 2010 e a media das notas por disciplina.

select p.NOME, h.COD_DISC, avg(h.NOTA)
from PROFESSORES p, HISTORICO h
where h.COD_PROF=p.COD_PROF and h.ANO = 2010
group by p.NOME, h.COD_DISC

Operações Diversas

Agora, iremos fazer algumas atualizações nas tabelas que criamos acima.

1. Insira seus dados no BD: tabela de alunos (seu nome, Ra, endereço, cidade), tabela de histórico cursando BD em 2009 na turma A com o professor JAC e obtendo nota 8.2 e freqüência 80, e a disciplina de EDA em 2010 na turma A com o professor RCSV e obtendo nota 7.5 e freqüência 75.

INSERT into ALUNOS
values (0912252, 'Aline Bossi', 'Rua manoel', 'Piracicaba')

INSERT into HISTORICO
values (0912252,'BD','A','JAC','2009','80','8.2')

INSERT into HISTORICO
values (0912252,'EDA','A','RCSV',2010,75,7.5)

2. Insira todos os alunos da disciplina de BD (Banco de Dados) em 2009 e tiveram nota > 5, cursando a disciplina BDII (Banco de Dados II) em 2010 com o mesmo código de turma e o mesmo professor, mas com freqüência e nota desconhecidas.
insert into HISTORICO

select h.RA, 'BDII',
h.COD_TURMA, h.COD_PROF,
2010, null, null   
from HISTORICO h
where h.COD_DISC = 'BD'
and h.ANO = 2009 and h.NOTA >5

3. Altere as notas dos alunos de Banco de Dados (BD) em 2010 com o professor JAC através da seguinte regra:
     – notas entre [4.0 e 5.0[ ficaram com 4.0 (inclusive a esquerda e exclusive a direita).
     – notas entre [5.0 e 9.5[ terão acréscimo de 0.5 na nota.
    – notas acima de 9.5, inclusive, ficarão com 10.0

update HISTORICO
set nota  = 4
where nota>=4 and nota<5
and cod_disc = 'BD'
and ano = '2010'
and cod_prof='JAC';

update  HISTORICO
set  nota  = 10
where nota>=9.5 and cod_disc = 'BD'
and ano = '2010' and cod_prof='JAC';

update  HISTORICO
set nota = nota +0.5
where nota>=5.0 and nota<9.5 and cod_disc = 'BD'
and ano = '2010' and cod_prof='JAC';

4. Remova todas as informações referentes ao aluno “Jorge dos Santos” do sistema.

delete from  HISTORICO
where RA IN
(select RA from Alunos where nome='Jorge dos Santos');

delete from ALUNOS 
where nome = 'Jorge dos Santos';

5. Faça uma atualização na freqüência dos alunos de BD em 2010 com o professor JC de acordo com a seguinte regra:
    – freqüência abaixo de 45, inclusive, acréscimo de 3.0%
    – freqüência acima de 45, acréscimo de 5.0%

update Historico h
set  h.FREQUENCIA = h.FREQUENCIA + 0.05* h.FREQUENCIA
where h.FREQUENCIA >45
and h.COD_DISC='Bd'
and h.ANO = '2010’ and h.Cod_prof = ‘JAC’;
update Historico h
set  h.FREQUENCIA = h.FREQUENCIA * 1.03
where h.FREQUENCIA <=45
and h.COD_DISC='Bd'
and h.ANO = '2010’
and h.Cod_prof = ‘JAC’;

6. Insira os seguintes dados: Aluna de RA 20090301 obteve nota 8.0 e freqüência 60 em BD (EM 2009 COM O PROFESSOR JAC NA TURMA A).

INSERT into HISTORICO
values(20090301, 'BD','A', 'JC', 2009, 60, 8 )

7. Execute todas as operações necessárias para substituir o nome do Prof. João Pedro por José Pedro, visto ter ocorrido um erro no momento de cadastrar o nome do professor.

update PROFESSORES
set nome='Jose Pedro'
where nome='Joao Pedro'

8. Substitua todas as matérias lecionadas pelo Prof. Raimundo Claudio Vasconcelos em 2010 pelo Prof. JAC.

update TURMA
set cod_prof = 'JAC'
where ano = 2010 and cod_prof in ( select cod_prof
                                   from PROFESSORES
                                   where nome = 'João")

Bom, estes são alguns exercícios para praticar,

Bons estudos e até logo!

Comandos SQL – PARTE 2

Continuando a falar sobre comandos básicos em SQL , neste artigo darei enfoque principal para os comando de seleção de dados. Este comando pertence ao DQL (Linguagem de Consulta de Dados) que embora tenha apenas um comando, a DQL é a parte da SQL mais utilizada.

O comando SELECT permite ao usuário especificar uma consulta (“query”) como uma descrição do resultado desejado. Esse comando é composto de várias cláusulas e opções, possibilitando elaborar consultas das mais simples às mais elaboradas.

O exemplo mais comum deste comando é usando as seguintes clausulas:

SELECT
     (lista de atributos, ou colunas que serão exibidos)
FROM
    (tabela cujo os dados serão combinados para a resolução da consulta)
WHERE
    (condições  para consulta)

ESPECIFICANDO AS CLÁUSULAS

As cláusulas são condições de modificação utilizadas para definir os dados que deseja selecionar ou modificar em uma consulta.

FROM – Utilizada para especificar a tabela em que os registros serão selecionados.

WHERE – Utilizada para especificar as condições que devem reunir os registros que serão selecionados.

GROUP BY – Utilizada para separar os registros selecionados em grupos específicos.

HAVING – Utilizada para expressar a condição que deve satisfazer cada grupo.

ORDER BY – Utilizada para ordenar os registros selecionados com uma ordem especifica.

DISTINCT – Utilizada para selecionar dados sem repetição.

OPERADORES LÓGICOS

Nestas consultas você pode usar alguns conectivos lógicos, são eles:

AND – (E) Avalia as condições e devolve um valor verdadeiro caso ambos sejam corretos.

OR – (OU). Avalia as condições e devolve um valor verdadeiro se algum for correto.

NOT – (Negação ).Devolve o valor contrário da expressão.

OPERADORES RELACIONAIS

< – Menor que

> – Maior que

<> – Diferente de

<= – Menor ou Igual que

>= – Maior ou Igual que

= – Igual a

BETWEEN – Utilizado para especificar um intervalo de valores.

LIKE – Utilizado na comparação de um modelo e para especificar registros de um banco de dados.
Exemplos: “Like” + extensão % vai significar buscar todos resultados com o mesmo início da extensão.

“Like” + % extensão  vai significar buscar todos resultados com o mesmo fim da extensão.

“Like” + % extensão %  vai significar buscar todos resultados onde a extensão esteja, tanto no início quanto no fim

FUNÇÕES DE AGREGAÇÃO
As funções de soma se usam dentro de uma cláusula SELECT em grupos de registros para devolver um único valor que se aplica a um grupo de registros.

AVG – Utilizada para calcular a média dos valores de um campo determinado.
COUNT – Utilizada para devolver o número de registros da seleção.
SUM – Utilizada para devolver a soma de todos os valores de um campo determinado.
MAX – Utilizada para devolver o valor mais alto de um campo especificado.
MIN – Utilizada para devolver o valor mais baixo de um campo especificado.

Estas funções de grupo podem ser aplicadas a todas as tuplas relacionadas pela consulta ou em grupos de tuplas selecionadas.  Vale lembrar que para aplicar a função em grupos de tuplas é necessário o agrupamento das tuplas usando a cláusula Group by, e também que as funções de  grupo podem ser aplicadas a todas as tuplas relacionadas pela consulta ou em grupos de tuplas selecionadas.

OPERADORES DE CONJUNTO

Quando há necessidade de unir dados de diversas tabelas, é imprescindível a utilização de Operadores de conjunto, são eles:

UNION: faz a união de colunas (tuplas) de duas tabelas compatíveis (mesmo numero de colunas e colunas correspondentes pertencem ao mesmo domínio).

IN:  ste conectivo verifica se a coluna selecionada pertence a outra seleção (subconsultas), retonando verdadeiro ou falso.

NOT IN: este testa se um registro(tupla) não pertence a uma tabela.

ANY (OU > SOME): usado para verificar se um valor é maior que algum dos valores armazenados em uma tabela.  Monta-se uma tabela através de uma consulta aninhada para testar os valores. (Análogos: < ANY (ou < some),  ANY (ou  some),  ANY (ou  some),  =ANY (ou = some), <> ANY (ou <> some)).

ALL: usado para verificar se um valor é maior que todos os valores armazenados em uma tabela.

Muitas vezes, para simplificar referencias e nomes de tabelas nas consultas, utilizando VARIÁVEIS DE TUPLAS que servem  para associar nomes alternativos (mais simples) às relações, para simplificar referencias a nomes de tabelas em consultas e possibilitar a comparação de duas tuplas (registros) da mesma tabela.

ORDENÇÃO

Nas consultas SQL é possível retornar os dados ordenados de acordo com nossa necessidade. Para isso, utilizamos a claúsula “ORDER BY”.

O padrão de ordenação é ordem crescente (Asc), para obter ordem decrescente usaremos um desc após o nome do atributo.

Neste artigo, adicionei aos seus conhecimentos mais alguns comandos que podem ser utilizados na manipulação de dados do banco de dados.

No próximo, sairemos deste conforto conceitual e partiremos para alguns exemplos práticos, assim fixando toda esta teoria.

Muito obrigada pela visita, e bons estudos!

Comandos SQL – PARTE 1

Olá queridos leitores, neste artigo mudarei um pouco os rumos do blog e falarei um pouco sobre SQL.  Farei um grande resumo conceitual sobre alguns comandos e  nos próximos artigos postarei alguns exemplos e exercícios para praticar o que estudamos.

Primeiramente, a  Linguagem de consulta é  por onde o usuário irá obter informações do banco de dados. Estas linguagens podem ser classificadas em procedurais e não-procedurais. Na primeira o usuário diz como o sistema irá realizar uma seqüência de operações no BD para obter o resultado desejado, já na segunda o usuário descreve a informação desejada, sem fornecer um procedimento para obtenção dessa informação.

Embora  SQL seja uma linguagem de consulta ela incorpora operações de manipulação de dados (DML) e operações de definição de dados (SQL-DDL).

Structured Query Language, ou Linguagem de Consulta Estruturada ou SQL

Falando uma pouco mais sobre SQL, esta é uma linguagem de consulta padrão para sistemas de BD relacionais, suas características são essencialmente do modelo relacional  (tabelas  e colunas com nomes distintos, as linhas contém informações de um registro da tabela, a ordem das linhas e colunas são irrelevantes, nunca teremos duas linhas iguais, além da chave primária entre outras ).

A linguagem SQL  se diferencia de outras linguagens de consulta a banco de dados no sentido em que uma consulta SQL especifica a forma do resultado e não o caminho para chegar a ele. Ela é uma linguagem declarativa em oposição a outras linguagens procedurais. Isto reduz o ciclo de aprendizado daqueles que se iniciam na linguagem.

Neste artigo darei enfoque para os elementos da  DML – (Data Manipulation Language – Linguagem de Manipulação de Dados). A DML é um subconjunto da linguagem usada para inserir, atualizar e apagar dados.

INSERT é usada para inserir um registro (formalmente uma tupla) a uma tabela existente.

UPDATE para mudar os valores de dados em uma ou mais linhas da tabela existente.

DELETE permite remover linhas existentes de uma tabela.

INSERINDO DADOS EM SUA TABELA

Uma das maneiras de inserir tuplas em tabelas usando SQL é especificando valor a valor à tupla a ser inserida.

INSERT INTO  r

VALUES  (V1, V2, V3, . . . . . Vn)

Os valores V1, V2, V3, . . . . . Vn devem pertencer ao domínio dos atributos correspondentes em “r”.

Outra forma de adicionar uma tupla na tabela é  especificando o as colunas da tabela e posteriormente  informando os valores que serão incluídos . A diferença entre os dois métodos é que o segundo você pode incluir valores nulos ou vazios. Desta forma:

INSERT INTO  r (coluna1, coluna2, coluna3,….. colunaN)

VALUES  (V1, V2, V3, . . . . . Vn)

Campos para o quais não temos valores a inserir devem ser preenchidos com “null”, neste caso, a inserção não será permitida se o campo tiver sido definido como obrigatório, isto é, “not-null”.

MODIFICANDO DADOS DE SUA TABELA

O comando SQL que nos permite atualizar dados é o comando UPDATE. A sintaxe básica do comando UPDATE é a seguinte:

UPDATE nome_tabela

SET nome_coluna1=expr1 [, nome_coluna2=expr2 ...]

WHERE [definição_where]

UPDATE é aplicado a uma tabela e a cláusula SET atribui a um campo o valor de uma expressão que pode ou não conter o valor de um campo da própria tabela. A cláusula WHERE restringe as atualizações apenas aos registro que satisfação suas condições.

EXCLUINDO DADOS DE SUA TABELA

O comando DELETE exclui as linhas que satisfazem a cláusula WHERE na tabela especificada.

Se a condição (cláusula WHERE) estiver ausente, o efeito é a exclusão de todas as linhas da tabela. O resultado vai ser uma tabela válida, porém vazia.

A sintaxe deste comando é a seguinte:

DELETE tabela  WHERE condição

Ou

DELETE FROM tabela WHERE condição

Todas as tuplas da relação (tabela)  que satisfazem a condição são excluídas integralmente dessa tabela.

Cada comando delete opera em uma única tabela => é necessário usar um comando para cada tabela.

A cláusula where pode ser omitida => todas as tuplas da relação serão excluídas (a tabela ficará vazia).

Bom pessoal esta foi a presentação de alguns comandos básicos e muito úteis, em breve postarei sua utilização.

Obrigada a todos  e seus comentários são sempre bem vindos.

 

Até logo!