Exercício de Banco de Dados ll

Prévia do material em texto

Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
Exercício	
  de	
  fixação	
  	
  
Crie	
  os	
  seguintes	
  registros	
  nas	
  tabelas	
  do	
  banco	
  de	
  dados	
  “matricula”	
  disponibilizado	
  no	
  material	
  de	
  apoio.	
  	
  
Tabela	
  Pessoa	
  	
  
Id	
   Nome	
   Data_nascimento	
   Cpf	
  
1	
   Marcos	
  Miguel	
   31/08/1977	
   03188888801	
  
2	
   Melissa	
  Miguel	
   31/08/2000	
   64085274101	
  
3	
   José	
  da	
  Silva	
   25/01/1950	
   12345678901	
  
4	
   Maria	
  da	
  Silva	
   01/01/1960	
   12385274196	
  
5	
   Giuliano	
  Prado	
   01/01/1930	
   04185274122	
  
	
  
Tabela	
  Aluno	
  	
  
Id_pessoa	
   Matricula	
   Ano_inicio	
   Semestre_inicio	
  
2	
   2013011	
   2013	
   1	
  
3	
   2000011	
   2000	
   1	
  
4	
   2000012	
   2001	
   2	
  
	
  
Tabela	
  Professor	
  	
  
Id_pessoa	
   Titulação_maxima	
   Matricula	
  
1	
   3	
   20011	
  
5	
   4	
   20011	
  
	
  
Tabela	
  Instituição	
  
Id	
   CNPJ	
   Nome	
  
1	
   642587524000121	
   UNIVERSO	
  
2	
   642587524000122	
   UNIVERSIDADE	
  X	
  –	
  (Xavier)	
  
	
  
Tabela	
  Turma	
  
Id	
   Numero	
   Ano	
   Semestre	
   Descrição	
   Id_instituição	
  
1	
   25412541	
   2000	
   1	
   TURMA	
  1	
  	
   1	
  
2	
   56445656	
   2000	
   2	
   TURMA	
  2	
   1	
  
3	
   65455654	
   2013	
   1	
   TURMA	
  3	
   1	
  
4	
   46556555	
   2012	
   1	
   TURMA	
  AB	
   2	
  
	
  
Tabela	
  Professor_turma	
  	
  
	
  	
  	
  	
  
	
  
	
  
	
  
Tabela	
  aluno_turma	
  	
  
	
  
	
  
	
  
	
  
	
   	
  
Id_pessoa	
   Id_turma	
  
1	
   1	
  
5	
   1	
  
1	
   2	
  
1	
   3	
  
1	
   4	
  
5	
   2	
  
5	
   3	
  
Id_aluno	
   Id_turma	
  
2	
   1	
  
3	
   2	
  
4	
   3	
  
2	
   4	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
Questões	
  
1) Liste	
  todos	
  os	
  alunos	
  matriculados	
  na	
  turma	
  2.	
  
2) Liste	
  todos	
  os	
  professores	
  que	
  dão	
  aula	
  para	
  os	
  alunos	
  Melissa	
  Miguel	
  e	
  Maria	
  da	
  Silva.	
  
3) Liste	
  todas	
  as	
  turmas	
  e	
  alunos	
  da	
  UNIVERSIDADE	
  X	
  –	
  (Xavier).	
  
4) Liste	
  todos	
  os	
  professores	
  nascidos	
  antes	
  de	
  1978.	
  
5) Liste	
  todos	
  os	
  alunos	
  da	
  família	
  Silva.	
  	
  
6) Liste	
  todos	
  os	
  alunos	
  da	
  família	
  Miguel.	
  
7) Liste	
  todas	
  as	
  pessoas	
  que	
  tenham	
  o	
  CPF	
  começado	
  com	
  “021”.	
  
8) Liste	
  o	
  total	
  de	
  pessoas	
  cadastradas	
  no	
  sistema.	
  	
  
9) Liste	
  o	
  Professor	
  com	
  o	
  maior	
  ID	
  do	
  sistema.	
  	
  
10) Liste	
  o	
  Aluno	
  com	
  o	
  maior	
  ID	
  do	
  sistema.	
  	
  
11) Liste	
  o	
  total	
  de	
  turmas	
  de	
  cada	
  professor.	
  
12) Liste	
  o	
  total	
  de	
  alunos	
  de	
  cada	
  	
  professor.	
  	
  
13) Liste	
  todos	
  os	
  alunos	
  que	
  iniciaram	
  a	
  faculdade	
  em	
  2013.	
  
	
  
	
  
	
  
	
  
	
  
Obs:	
  Deve-­‐se	
  gerar	
  para	
  entrega	
  um	
  arquivo	
  SQL	
  para	
  cada	
  questão.	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
	
  
Resolução	
  da	
  lista	
  	
  
1)	
  
SELECT	
  DISTINCT	
  p.nome	
  FROM	
  pessoa	
  AS	
  p	
  	
  
INNER	
  JOIN	
  aluno	
  AS	
  a	
  ON	
  a.id_pessoa	
  =	
  p.id	
  
INNER	
  JOIN	
  aluno_turma	
  AS	
  at	
  ON	
  at.id_aluno	
  =	
  a.id_pessoa	
  
WHERE	
  at.id_turma	
  =	
  2	
  
	
  
	
  
	
  
	
  
	
  
2)	
  	
  
SELECT	
  DISTINCT	
  p1.nome	
  FROM	
  pessoa	
  AS	
  p	
  	
  
INNER	
  JOIN	
  aluno	
  AS	
  a	
  ON	
  a.id_pessoa	
  =	
  p.id	
  
INNER	
  JOIN	
  aluno_turma	
  AS	
  at	
  ON	
  at.id_aluno	
  =	
  a.id_pessoa	
  
A	
  clausula	
  DISTINCT	
  	
  elimina	
  as	
  duplicidades	
  dos	
  campos	
  resultantes,	
  
caso	
  existam.	
  	
  
	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
INNER	
  JOIN	
  professor_turma	
  AS	
  pt	
  ON	
  pt.id_turma	
  =	
  at.id_turma	
  	
  
INNER	
  JOIN	
  professor	
  AS	
  prof	
  ON	
  prof.id_pessoa	
  =	
  pt.id_pessoa	
  
INNER	
  JOIN	
  pessoa	
  AS	
  p1	
  ON	
  p1.id	
  =	
  prof.id_pessoa	
  
WHERE	
  p.nome	
  =	
  'Melissa	
  Miguel'	
  OR	
  p.nome	
  =	
  'Maria	
  da	
  Silva'	
  
	
  
3)	
  	
  
SELECT	
  p.*,	
  t.*	
  FROM	
  pessoa	
  AS	
  p	
  	
  
INNER	
  JOIN	
  aluno	
  AS	
  a	
  ON	
  a.id_pessoa	
  =	
  p.id	
  
INNER	
  JOIN	
  aluno_turma	
  AS	
  at	
  ON	
  at.id_aluno	
  =	
  a.id_pessoa	
  
INNER	
  JOIN	
  turma	
  as	
  t	
  ON	
  t.id	
  =	
  at.id_turma	
  	
  
INNER	
  JOIN	
  instituicao	
  AS	
  i	
  ON	
  i.id	
  =	
  t.id_instituicao	
  	
  
WHERE	
  i.nome	
  like	
  '%UNIVERSIDADE	
  X%'	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
	
  
4)	
  	
  
SELECT	
  DISTINCT	
  p.nome	
  FROM	
  pessoa	
  AS	
  p	
  	
  
INNER	
  JOIN	
  professor	
  AS	
  prof	
  ON	
  prof.id_pessoa	
  =	
  p.id	
  
WHERE	
  year(p.data_nascimento)	
  <	
  1978	
  
	
  
	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
5)	
  
SELECT	
  p.nome	
  FROM	
  pessoa	
  AS	
  p	
  	
  
INNER	
  JOIN	
  aluno	
  AS	
  a	
  ON	
  a.id_pessoa	
  =	
  p.id	
  
WHERE	
  	
  
p.nome	
  LIKE	
  '%Silva'	
  
	
  
	
  
6)	
  
SELECT	
  p.nome	
  FROM	
  pessoa	
  AS	
  p	
  	
  
INNER	
  JOIN	
  aluno	
  AS	
  a	
  ON	
  a.id_pessoa	
  =	
  p.id	
  
WHERE	
  	
  
p.nome	
  LIKE	
  '%Miguel’	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
	
  
	
  
7)	
  
SELECT	
  p.nome	
  FROM	
  pessoa	
  AS	
  p	
  	
  
WHERE	
  	
  
p.cpf	
  LIKE	
  '021%'	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
	
  
8)	
  	
  
SELECT	
  count(*)	
  FROM	
  pessoa	
  AS	
  p	
  
	
  
9)	
  
SELECT	
  p.nome	
  FROM	
  pessoa	
  AS	
  p	
  	
  
INNER	
  JOIN	
  professor	
  AS	
  prof	
  ON	
  prof.id_pessoa	
  =	
  p.id	
  
WHERE	
  prof.id_pessoa	
  =	
  (select	
  max(profx.id_pessoa)	
  from	
  professor	
  AS	
  profx)	
  
	
  
	
  
	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
10)	
  
SELECT	
  p.nome	
  FROM	
  aluno	
  AS	
  a	
  	
  
INNER	
  JOIN	
  pessoa	
  AS	
  p	
  ON	
  p.id	
  =	
  a.id_pessoa	
  
WHERE	
  a.id_pessoa	
  =	
  (select	
  max(ax.id_pessoa)	
  from	
  aluno	
  AS	
  ax)	
  
	
  
	
  
	
  
11)	
  	
  
SELECT	
  p.nome,	
  count(	
  pt.id_turma	
  )	
  	
  
FROM	
  professor_turma	
  pt	
  
INNER	
  JOIN	
  pessoa	
  AS	
  p	
  ON	
  p.id	
  =	
  pt.id_pessoa	
  
GROUP	
  BY	
  p.nome	
  
	
   	
  
A	
  Clausula	
  Group	
  by	
  é	
  um	
  agrupador	
  de	
  valores.	
  Em	
  conjunto	
  com	
  as	
  clausulas	
  count,	
  avg,	
  max,	
  min,	
  sum	
  permite	
  
gerar	
  valores	
  agrupados	
  pelo	
  campo	
  desejado.	
  
Em	
  nosso	
  exemplo	
  	
  somamos	
  o	
  total	
  de	
  turmas	
  	
  e	
  queremos	
  que	
  os	
  mesmos	
  sejam	
  agrupados	
  pelo	
  professor.	
  	
  
Caso	
  seja	
  omitido,	
  	
  o	
  somatórioserá	
  de	
  todos	
  os	
  registros	
  e	
  o	
  resultado,	
  portanto,	
  errado.	
  	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
	
  
	
  
Segunda	
  opção	
  	
  
SELECT	
  p.nome,	
  (SELECT	
  COUNT(	
  pt.id_turma	
  )	
  FROM	
  	
  professor_turma	
  AS	
  pt	
  WHERE	
  pt.id_pessoa	
  =	
  p.id)	
  
FROM	
  pessoa	
  p	
  
	
  
	
  
SUBSELECT	
  –	
  A	
  partir	
  de	
  um	
  comando	
  SELECT	
  qualquer,	
  podemos	
  gerar	
  outros	
  
comandos	
  SELECT	
  	
  chamados	
  de	
  SUBSELECT,	
  que	
  fazem	
  outra	
  busca	
  no	
  banco	
  para	
  
trazer	
  um	
  único	
  registro	
  que,	
  de	
  acordo	
  com	
  as	
  especificações	
  e	
  condições	
  do	
  WHERE,	
  
pertencem	
  ao	
  dado	
  corrente.	
  	
  
No	
  exemplo	
  acima	
  ao	
  busca	
  o	
  nome	
  da	
  pessoa	
  o	
  SUBSELECT	
  vai	
  ao	
  banco	
  de	
  dados	
  e	
  busca	
  o	
  total	
  de	
  turmas	
  que	
  
essa	
  pessoa	
  possui	
  filtrados	
  na	
  condição	
  WHERE	
  pelo	
  ID	
  da	
  pessoa	
  corrente.	
  	
  
	
  
	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
	
  
	
  
	
  
	
  
12)	
  	
  
SELECT	
  p.nome,	
  count(at.id_aluno)	
  from	
  aluno_turma	
  AS	
  at	
  	
  
INNER	
  JOIN	
  professor_turma	
  AS	
  pt	
  ON	
  pt.id_turma	
  =	
  at.id_turma	
  	
  
INNER	
  JOIN	
  pessoa	
  AS	
  p	
  on	
  p.id	
  =	
  pt.id_pessoa	
  	
  
GROUP	
  BY	
  p.nome	
  
	
  
	
  
	
  
	
  
	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll	
  
	
  
	
  
13)	
  
SELECT	
  p.nome,	
  t.ano	
  	
  FROM	
  aluno_turma	
  AS	
  at	
  	
  
INNER	
  JOIN	
  turma	
  AS	
  t	
  ON	
  	
  t.id	
  =	
  at.id_turma	
  	
  
INNER	
  JOIN	
  pessoa	
  AS	
  p	
  ON	
  p.id	
  =	
  at.id_aluno	
  	
  
WHERE	
  	
  t.ano	
  =	
  2013	
  
Universidade	
  Salgado	
  de	
  Oliveira	
  –	
  UNIVERSO	
  
	
  
CURSO	
  DE	
  ANALISE	
  DE	
  SISTEMAS	
  
Disciplina:	
  Banco	
  de	
  Dados	
  ll