terça-feira, 9 de abril de 2013

LISTA DE EXERCÍCIOS - BDII

TOP E DISTINCT

01 - Selecione as 10 primeiras matrículas do curso:
R: select top 10 *
from Matricula

02 - Selecione as 5 primeiras disciplinas cadastradas:
R: select top 5 *
from Disciplina

03 - Selecione os 2 primeiros alunos cadastrados:
R: select top 2 *
from Aluno

04 - Selecione os 2 últimos alunos cadastrados:
R: select top 2 *
from Aluno order by Cod_Aluno desc

05 - Selecione as 5 primeiras notas diferentes em ordem crescente:
R: select distinct top 5 nota
from Matricula


LIKE, NULL, NOT NULL E BETWEEN

01 - Selecione todos os alunos cujo nome começa com ‘jo’:
R: select *
from Aluno
where Nome_Aluno like 'jo%'

02 - Selecione todos os alunos cujo nome termina com ‘a’:
R: select *
from Aluno
where Nome_Aluno like '%a'

03 - Selecione todos os alunos cujo nome contenha ‘an’:
R: select *
from Aluno
where Nome_Aluno like '%an%'

04 - Selecione todos os alunos que não tenham data de nascimento cadastrada:
R: select *
from Aluno
where Data_Nascimento is null

05 - Selecione todas as matriculas cujos alunos não tem nota:
R: select *
from Matricula
where nota is null

06 - Selecione todas as disciplinas que não tenham pré-requisitos:
R: select *
from Disciplina
where pre_requisito is null

07 - Selecione todas as disciplinas que tenha professor cadastrados:
R: select *
from Disciplina
where cod_prof is not null

08 - Selecione todas as matrículas que tenha todos os dados cadastrados:
R: select *
from Matricula
where nota is not null and situacao is not null

09 - Selecione todas as matrículas das disciplinas cujo código está entre 2 e 4:
R: select *
from Disciplina
where codigo_disciplina between 2 and 4

10 - Selecione todas as matrículas que contenha os alunos que tiraram nota entre 5 e 8 na disciplina com código 2:
R: select *
from Matricula
where nota between 5 and 8 and cod_Aluno = 2

11 - Selecione todos os alunos que nasceram entre os anos de 1980 e 1982:
R: select *
from Aluno
where year (Data_Nascimento)
Between 1980 and 1982


COMPARAÇÃO CONJUNTOS – SUB-QUERYS

01 - Mostre todos os alunos que obtiveram nota maior que o aluno código = 222 na disciplina código = 2:
R: select *
from Matricula
where nota >  (select nota  from matricula where cod_Disciplina = 2 and      cod_Aluno = 222)

02 - Mostre todas as disciplinas cujo crédito é maior que o crédito da disciplina código = 5:
R: select *
from Disciplina
where creditos > ( select creditos from Disciplina where codigo_disciplina = 5)

03 - Mostre todos os alunos que obtiveram nota maior que o aluno código = 333 na disciplina código = 5:
R: select *
from Matricula
where nota > (select nota from Matricula where cod_Aluno = 333 and cod_Disciplina = 5)
 --and cod_Disciplina = 5

04 - Mostre todos os alunos que obtiveram nota menor que o aluno código = 333 na disciplina código = 2:
R: select *
from Matricula
where nota < (select nota from Matricula where cod_Aluno = 333 and cod_Disciplina = 2)

05 - Mostre todos os alunos que obtiveram média menor que a média geral das notas:
R: select cod_aluno, avg(nota) as media from matricula
group by cod_aluno
having avg(nota) < (select avg(nota) from matricula)


JOIN

01 - Mostre o Nome do Aluno e o Nome das Disciplinas que ele está cursando:
R: select a.nome_aluno, d.nome
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina

02 - Mostre o Nome do Professor e o Nome das Disciplinas que ele está ministrando:
R: select p.nome_professor, d.nome
from professor p
inner join disciplina d on d.cod_prof=p.cod_professor

03 - Mostre o Nome do Aluno e o Nome dos seus Professores:
R: select a.nome_aluno, p.nome_professor
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina
inner join professor p on p.cod_professor=d.cod_prof

04 - Mostre o Nome do Aluno, Nome da Disciplina que ele está cursando e o Nome do Professor da Disciplina:
R: select a.nome_aluno, d.nome, p.nome_professor
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina
inner join professor p on p.cod_professor=d.cod_prof

05 - Mostre o Nome do Aluno e o nome das Disciplinas na qual ele passou:
R: select a.nome_aluno, d.nome
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina
where m.situacao='ok'

06 - Mostre o Nome do Aluno e o nome das Disciplinas na qual ele não passou:
R: select a.nome_aluno, d.nome
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina
where m.situacao='nok'

07 - Mostre o Nome do Aluno, o nome das Disciplinas na qual ele passou, o nome do professor da disciplina e a sua nota:
R: select a.nome_aluno, d.nome, p.nome_professor, m.nota
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina
inner join professor p on p.cod_professor=d.cod_prof
where m.situacao='ok'

08 - Mostre o Nome do Aluno, Nome da Disciplina que ele está cursando e o Nome do Professor da Disciplina, a nota que ele tirou e a média das notas da turma nessa disciplina:
R: select a.nome_aluno,
       d.nome,
       p.nome_professor,
       m.nota,
      (select avg(nota)
       from matricula m2
       where m2.cod_disciplina=m.cod_disciplina) as media
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina
inner join professor p on p.cod_professor=d.cod_prof



TRATAMENTO DE DATAS

01 - Mostre o Nome dos Alunos que nasceram no dia 5:
R: select nome_aluno
from aluno
where day(data_nascimento)=5

02 - Mostre o Nome dos Alunos que nasceram no mês 12:
R: select nome_aluno
from aluno
where month(data_nascimento)=12

03 - Mostre o Nome dos Alunos que nasceram após 1980:
R: select nome_aluno
from aluno
where year(data_nascimento)>1980
 
04 - Mostre o Nome dos Alunos e a Matérias cursadas pelos alunos que nasceram entre os anos de 1980 e 1982:
R: select a.nome_aluno,
       d.nome
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina
where year(data_nascimento) between 1980 and 1982
 
05 - Mostre o Nome, a idade em anos e os nomes dos professores dos Alunos:
R: select a.nome_aluno,
       datediff(yy,a.data_nascimento, getdate()),
       p.nome_professor
from aluno a
inner join matricula m on m.cod_aluno=a.cod_aluno
inner join disciplina d on d.codigo_disciplina=m.cod_disciplina
inner join professor p on p.cod_professor=d.cod_prof
 
06 - Mostre o Nome dos Alunos e o dia da semana em que ele nasceu (em português):
R: select nome_aluno,
           case datename(dw, data_nascimento)
              when 'sunday' then 'domingo'
              when 'monday' then 'segunda-feira'
              when 'tuesday' then 'terça-feira'
              when 'wednesday' then 'quarta-feira'
              when 'thursday' then 'quinta-feira'
              when 'friday' then 'sexta-feira'
              when 'saturday' then 'sábado'
              else null
           end as dia 
from aluno
 
07 - Mostre quantos dias já se passaram do início das aulas (09/02/2009) até hoje:
R: select datediff(dd,'02-09-2009',getdate())
 
08 - Mostre o dia da semana (em inglês) do dia em que você nasceu:
R: select datename(dw,'09-09-1989')
 
09 - Mostre o dia da semana (em inglês) do dia de hoje no ano que vem:
R: select datename(dw,dateadd(yy,1,getdate()))
 
10 - Mostre a data de hoje por extenso no formato do exemplo a seguir. Ex: Quarta-feira, 05 de março de 2009:
R: select    dia=case datename(dw, getdate())
              when 'sunday' then 'domingo'
              when 'monday' then 'segunda-feira'
              when 'tuesday' then 'terça-feira'
              when 'wednesday' then 'quarta-feira'
              when 'thursday' then 'quinta-feira'
              when 'friday' then 'sexta-feira'
              when 'saturday' then 'sabado'
              else null
           end+', '+convert(char(2),day(getdate()))+' de '+
               case datepart(mm, getdate())
              when 1 then 'janeiro'
              when 2 then 'fevereiro'
              when 3 then 'março'
              when 4 then 'abril'
              when 5 then 'maio'
              when 6 then 'junho'
              when 7 then 'julho'
              when 8 then 'agosto'
              when 9 then 'setembro'
              when 10 then 'outubro'
              when 11 then 'novembro'
              when 12 then 'dezembro'
           end+' de '+convert(varchar(15),year(getdate()))
 
 
CONVERSÃO DE DADOS
 
01 - Mostre a pesquisa que retorna o resultado abaixo:
R: select nome_aluno, convert(char,data_nascimento,103) as data_nascimento
from aluno
where data_nascimento is not null
 
02 - Mostre a pesquisa que retorna o resultado abaixo:
 
R: select resultado='o(a) aluno(a): '+nome_aluno+
   ' nasceu no dia '+convert(char(2),day(data_nascimento))+
   ' do mês de '+
   case month(data_nascimento)
      when 1 then 'janeiro'
      when 2 then 'feveiro'
      when 3 then 'março'
      when 4 then 'abril'
      when 5 then 'maio'
      when 6 then 'junho'
      when 7 then 'julho'
      when 8 then 'agosto'
      when 9 then 'setembro'
      when 10 then 'outubro'
      when 11 then 'novembro'
      when 12 then 'dezembro'
   end+' de '+convert(char(4),year(data_nascimento))
from aluno
where data_nascimento is not null
 
03 - Mostre a pesquisa que retorna o resultado abaixo:
R: select top 10 resultado=
       'o(a) aluno(a): '+a.nome_aluno+
       ' obteve nota = '+convert(char(4),m.nota)+
       ' na disciplina '+d.nome+
       ' a média geral foi '+(select convert(nvarchar(14),convert(numeric(4,2),avg(m2.nota))) from matricula m2 where m2.cod_disciplina=m.cod_disciplina)
from aluno a inner join
     matricula m on m.cod_aluno=a.cod_aluno inner join
     disciplina d on d.codigo_disciplina=m.cod_disciplina
where m.nota is not null
order by d.nome, a.nome_aluno
 
04 - Mostre a pesquisa que retorna o resultado abaixo:
R: select top 10 resultado=
       'o(a) aluno(a): '+a.nome_aluno+
       ' obteve nota = '+convert(char(4),m.nota)+
       ' na disciplina '+d.nome+
       ', estando '+case m.situacao
                                   when 'nok' then 'reprovado'
                                   when 'ok' then 'aprovado'
                            end
from aluno a inner join
     matricula m on m.cod_aluno=a.cod_aluno inner join
     disciplina d on d.codigo_disciplina=m.cod_disciplina
where m.nota is not null
  and m.situacao is not null
order by d.nome, a.nome_aluno
 
05 - Mostre a pesquisa que retorna o resultado abaixo:
R: select distinct resultado='os alunos do(a) professor(a) '+p.nome_professor+
' obtiveram média = '+
(select convert(nvarchar(14),convert(numeric(4,2),avg(m2.nota)))
 from matricula m2
 where m2.cod_disciplina=m.cod_disciplina)
from matricula m inner join
     disciplina d on d.codigo_disciplina=m.cod_disciplina inner join
     professor p on p.cod_professor=d.cod_prof
order by resultado
 
06 - Mostre a pesquisa que retorna o resultado abaixo:
R: select resultado='existem '+(select convert(char(4),count(*))
                             from matricula m, aluno a
                             where a.cod_aluno=m.cod_aluno)+
       'alunos matriculados. a média geral é '+(select convert(char(4),convert(numeric(4,2),avg(m.nota)))
                                                from matricula m, aluno a
                                                where a.cod_aluno=m.cod_aluno)+
       ' e a soma de todas as notas é '+(select convert(char(5),convert(numeric(4,2),sum(m.nota)))
                                         from matricula m, aluno a
                                         where a.cod_aluno=m.cod_aluno)
 
07 - Mostre a pesquisa que retorna o resultado abaixo:
R: select distinct resultado='na disciplina '+d.nome+
                 ' a maior nota foi '+(select convert(char(5),convert(numeric(4,2),max(m2.nota))) from matricula m2 where m.cod_disciplina=m2.cod_disciplina)+
                         ' a menor nota foi '+(select convert(char(5),convert(numeric(4,2),min(m2.nota))) from matricula m2 where m.cod_disciplina=m2.cod_disciplina)+
                         ' a média foi '+(select convert(char(5),convert(numeric(4,2),avg(m2.nota))) from matricula m2 where m.cod_disciplina=m2.cod_disciplina)
from matricula m inner join
     disciplina d on d.codigo_disciplina=m.cod_disciplina    
order by resultado
 
 
Funções de Manipulação de Dados Character
 
01 - Mostre a pesquisa que retorna o resultado abaixo:
R: select top 10 resultado=substring(nome_aluno,1,4) from aluno

02 - Mostre as letras do nome do aluno anteriores à letra ‘s’. Exemplo: Andressa à Resultado=’Andre’
R: select distinct resultado=substring(nome_aluno,1,
patindex('%s%',nome_aluno)-1)
from aluno
where patindex('%s%',nome_aluno)>0

03 - Mostre a pesquisa que retorna o resultado abaixo:
R: select resultado=substring(nome,1,patindex('% %',nome)) from disciplina

04 - Mostre a pesquisa que retorna o resultado abaixo:
R: select resultado=reverse(nome) from disciplina

05 - Mostre as notas obtidas pelos alunos de forma distinta, onde o formato seja xx.xx. Exemplo: 8.00 à Resultado: 08.00  10.00 à Resultado: 10.00
R: select distinct resultado=replicate('0',5-len(nota))+convert(varchar(5),nota) from matricula where nota is not null

06 - Mostre a pesquisa que retorna o resultado abaixo:
R: select codaluno=replicate('0',5-len(cod_aluno))+convert(varchar(5),cod_aluno), nome_aluno, data=convert(char,data_nascimento,103) from aluno where data_nascimento is not null

07 - Usando a função soundex, mostre o nome do aluno que se assemelha foneticamente a 'GEOVANI'.
R: select * from aluno where soundex(nome_aluno)=soundex('geovani')












 
 
 
 

Nenhum comentário:

Postar um comentário