none
Dúvida em consulta RRS feed

  • Pergunta

  • Bom galera, sou novo aqui e novo no SQL também. 

    Peguei alguns exercícios pra resolver. (código abaixo)

    create database RevisaoFinal
    use Revisaofinal

    -- tabela disciplinas
    Create table disciplina(
    Codigo int identity not null,
    Disciplina char(30),
    Denominacao char(150) not null,
    Nivel int null,
     Primary key(Codigo))
     
     --drop table disciplina

    -- tabela alunos
    Create table aluno(
    Matricula int not null,
    Nome char(75) not null,
    Sexo char(1),
    Idade int null,
    Primary key(matricula))

    -- tabela professores
    Create table professor(
    Codigo int not null identity, 
    Professor varchar(50) not null,
    Formacao varchar(30),
    Primary key(codigo))


    create table turma(
    codigo int not null identity,
    Nome varchar(30) not null,
    Ano_Sem varchar(8) not null,
    Sala varchar(10),
    primary key(codigo))

    --drop table turma
    create table aluno_turma(
    Codigo int not null identity,
    CodAluno int not null,
    CodTurma int not null,
    primary key(Codigo),
    foreign key (CodAluno) references Aluno(Matricula),
    foreign key (CodTurma) references Turma(codigo))



    create table disciplina_turma(
    Codigo int not null identity,
    CodDisciplina int not null,
    CodTurma int not null,
    primary key(Codigo),
    foreign key (CodDisciplina) references Disciplina(Codigo),
    foreign key (CodTurma) references Turma(codigo))


    create table Professor_turma(
    Codigo int not null identity,
    CodProfessor int not null,
    CodTurma int not null,
    primary key(Codigo),
    foreign key (CodProfessor) references Professor(Codigo),
    foreign key (CodTurma) references Turma(codigo))

    --DROP TABLE Notas
    Create table Notas(
    codigo int not null identity,
    Nota1 numeric,
    Nota2 numeric,
    Nota3 numeric,
    Media numeric,
    CodAluno int not null,
    CodDisciplina int not null,
    CodTurma int not null,
    primary key(codigo),
    foreign key (CodAluno) references Aluno(Matricula),
    foreign key (CodTurma) references Turma(codigo),
    foreign key (CodDisciplina) references Disciplina(codigo))


    -- ---------------------DADOS -----------------------

    Insert into disciplina values('Algoritmos e Programação I','',1)--1
    Insert into disciplina values('Engenharia de Software I','',4)--2
    Insert into disciplina values('Banco de Dados I','',2)--3
    Insert into disciplina values('Modelagem Conceitual','',1)--4
    Insert into disciplina values('Analise e Comple de Algoritmos','',5)--5
    Insert into disciplina values('Teoria da Computação','',6)--6
    Insert into disciplina values('Linguagens Formais e Automatos','',8)--7
    Insert into disciplina values('Compiladores','',8)--8
    Insert into disciplina values('Tópicos Avanç em Programação','',8)--9
    Insert into disciplina values('Trabalho de Concl de Curso I','',6)--10
    Insert into disciplina values('Trabalho de Concl de Curso II','',7)--11
    Insert into disciplina values('Trabalho de Concl de Curso III','',8)--12
    Insert into disciplina values('Algoritmos e Programação II','',2)--13
    Insert into disciplina values('Ling de Prog Orien a Objetos','',3)--14
    Insert into disciplina values('Ling de Prog Orien Objetos II','',4)--15
    Insert into disciplina values('Engenharia de Software II','',5)--16

    Insert into aluno values(93106842,'Jovana da Silva','F',26)
    Insert into aluno values(94103839,'Eduardo da Silva','M',22)
    Insert into aluno values(94108293,'Marcelo da Silva','M',22)
    Insert into aluno values(94112046,'Tiago da Silva','M',22)
    Insert into aluno values(94112192,'Vanessa da Silva','F',23)
    Insert into aluno values(94201018,'Carla da Silva','F',21)
    Insert into aluno values(94206067,'Rogerio da Silva','M',22)
    Insert into aluno values(95280018,'Solange da Silva','F',26)
    Insert into aluno values(95280023,'Marcelo da Silva','M',23)
    Insert into aluno values(95280027,'Katia da Silva','F',20)
    Insert into aluno values(96104543,'Marcos da Silva','M',26)

    Insert into professor values('Ana Paula', 'Sistemas de Informação')--1
    Insert into professor values('Yamaguti', 'Tec. Desenv. Dados')--2
    Insert into professor values('Bastos', 'Ciencia da Computação')--3
    Insert into professor values('Afonso', 'Engenharia de Sistemas')--4
    Insert into professor values('Karin', 'Engenharia de Hardware')--5
    Insert into professor values('Carlos', 'Sistemas de Informação')--6

    insert into turma values ('1º Série A', '01/2008', 'Sl 102')
    insert into turma values ('2º Série A', '02/2008', 'Sl 103')
    insert into turma values ('3º Série A', '01/2009', 'Sl 201')
    insert into turma values ('4º Série A', '02/2009', 'Sl 202')
    insert into turma values ('5º Série A', '01/2010', 'Sl 301')
    insert into turma values ('6º Série A', '02/2010', 'Sl 302')
    insert into turma values ('7º Série A', '01/2011', 'Sl 401')
    insert into turma values ('8º Série A', '02/2011', 'Sl 402')
    insert into turma values ('1º Série B', '01/2008', 'Sl 103')
    insert into turma values ('2º Série B', '02/2008', 'Sl 102')
    insert into turma values ('3º Série B', '01/2009', 'Sl 202')
    insert into turma values ('4º Série B', '02/2009', 'Sl 201')
    insert into turma values ('5º Série B', '01/2010', 'Sl 302')
    insert into turma values ('6º Série B', '02/2010', 'Sl 301')
    insert into turma values ('7º Série B', '01/2011', 'Sl 402')
    insert into turma values ('8º Série B', '02/2011', 'Sl 401')


    Insert into aluno_turma values(93106842, 1)
    Insert into aluno_turma values(94103839, 1)
    Insert into aluno_turma values(94108293, 1)
    Insert into aluno_turma values(94112046, 1)
    Insert into aluno_turma values(94112192, 1)
    Insert into aluno_turma values(94201018,2)
    Insert into aluno_turma values(94206067,2)
    Insert into aluno_turma values(95280018,2)
    Insert into aluno_turma values(95280023,3)
    Insert into aluno_turma values(95280027,4)
    Insert into aluno_turma values(96104543,4)


    insert into disciplina_turma values (4,1)
    insert into disciplina_turma values (1,1)
    insert into disciplina_turma values (13,2)
    insert into disciplina_turma values (3,2)
    insert into disciplina_turma values (14,2)
    insert into disciplina_turma values (15,3)
    insert into disciplina_turma values (2,3)
    insert into disciplina_turma values (5,3)
    insert into disciplina_turma values (16,3)
    insert into disciplina_turma values (6,4)
    insert into disciplina_turma values (10,6)
    insert into disciplina_turma values (11,7)
    insert into disciplina_turma values (12,8)
    insert into disciplina_turma values (7,8)
    insert into disciplina_turma values (8,8)
    insert into disciplina_turma values (9,8)
    insert into disciplina_turma values (4,9)
    insert into disciplina_turma values (1,9)
    insert into disciplina_turma values (13,10)
    insert into disciplina_turma values (3,10)
    insert into disciplina_turma values (14,10)
    insert into disciplina_turma values (15,11)
    insert into disciplina_turma values (2,11)
    insert into disciplina_turma values (5,11)
    insert into disciplina_turma values (16,11)
    insert into disciplina_turma values (6,12)
    insert into disciplina_turma values (10,14)
    insert into disciplina_turma values (11,15)
    insert into disciplina_turma values (12,16)
    insert into disciplina_turma values (7,16)
    insert into disciplina_turma values (8,16)
    insert into disciplina_turma values (9,16)

    insert into Professor_turma values (1,1)
    insert into Professor_turma values (2,1)
    insert into Professor_turma values (2,2)
    insert into Professor_turma values (3,2)
    insert into Professor_turma values (4,2)
    insert into Professor_turma values (6,3)
    insert into Professor_turma values (5,3)
    insert into Professor_turma values (4,3)
    insert into Professor_turma values (1,3)
    insert into Professor_turma values (1,4)
    insert into Professor_turma values (6,6)
    insert into Professor_turma values (6,7)
    insert into Professor_turma values (6,8)
    insert into Professor_turma values (1,8)
    insert into Professor_turma values (2,8)
    insert into Professor_turma values (4,8)

    INSERT INTO Notas VALUES ('1.0', '6.0', '4.0', '0.0', 93106842,1,1) 
    INSERT INTO Notas VALUES ('6.0', '7.0', '5.0', '0.0', 94103839,1,1) 
    INSERT INTO Notas VALUES ('7.0', '6.0', '6.0', '0.0', 94108293,1,1) 
    INSERT INTO Notas VALUES ('3.0', '4.0', '7.0', '0.0', 94112046,1,1) 
    INSERT INTO Notas VALUES ('8.0', '7.0', '2.0', '0.0', 94112192,1,1) 
    INSERT INTO Notas VALUES ('8.0', '8.0', '5.0', '0.0', 93106842,4,1) 
    INSERT INTO Notas VALUES ('6.0', '9.0', '3.0', '0.0', 94103839,4,1) 
    INSERT INTO Notas VALUES ('4.0', '5.0', '3.0', '0.0', 94108293,4,1) 
    INSERT INTO Notas VALUES ('3.0', '7.0', '6.0', '0.0', 94112046,4,1) 
    INSERT INTO Notas VALUES ('9.0', '6.0', '7.0', '0.0', 94112192,4,1) 
    INSERT INTO Notas VALUES ('5.0', '3.0', '4.0', '0.0', 94201018,13,2) 
    INSERT INTO Notas VALUES ('6.0', '6.0', '6.0', '0.0', 94206067,13,2) 
    INSERT INTO Notas VALUES ('7.5', '5.0', '4.0', '0.0', 95280018,13,2) 
    INSERT INTO Notas VALUES ('6.0', '7.0', '4.0', '0.0', 94201018,3,2) 
    INSERT INTO Notas VALUES ('8.0', '9.0', '8.0', '0.0', 94206067,3,2) 
    INSERT INTO Notas VALUES ('7.0', '10.0', '2.0', '0.0', 95280018,3,2) 
    INSERT INTO Notas VALUES ('8.0', '9.0', '5.0', '0.0', 95280023,15,3) 
    INSERT INTO Notas VALUES ('8.0', '7.0', '6.0', '0.0', 95280023,2,3) 
    INSERT INTO Notas VALUES ('8.0', '5.0', '4.0', '0.0', 95280023,5,3) 
    INSERT INTO Notas VALUES ('8.0', '6.0', '7.0', '0.0', 95280023,16,3) 
    INSERT INTO Notas VALUES ('6.0', '2.0', '7.0', '0.0', 95280027,6,4) 
    INSERT INTO Notas VALUES ('4.0', '4.0', '8.0', '0.0', 96104543,6,4) 



    a) Selecione os Alunos e suas respectivas turmas:

    select * from turma
    select a.Nome, t.Nome from aluno a, turma t, aluno_turma at
    where a.Matricula = at.CodAluno and t.codigo = at.CodTurma

    b) Selecione os professores e suas respectivas turmas;

    select p.Professor, t.Nome from professor p, turma t, Professor_turma pt
    where p.Codigo = pt.CodProfessor and t.codigo = pt.CodTurma

    c) selecione as dispciplinas e suas respectivas turmas;

    select d.Disciplina , t.Nome from disciplina d, turma t, disciplina_turma dt
    where d.Codigo = dt.CodDisciplina and t.codigo = Dt.CodTurma

    d) Selecione, o nome do aluno, sua matricula, e as disciplinas que ele estuda;

    select a.Nome, a.Matricula, d.Disciplina from aluno a, aluno_turma at, disciplina_turma dt, disciplina d
    where a.Matricula = at.CodAluno and at.CodTurma = dt.CodTurma and d.Codigo = dt.CodDisciplina
    order by a.nome

    e) Conte quantos alunos há em cada Turma;

    select * from turma
    select t.Nome ,count(a.matricula) as Quantidade from aluno a, turma t, aluno_turma at
    where a.Matricula = at.CodAluno and t.codigo = at.CodTurma
    group by t.nome

    f) Selecione as disciplinas que nao há alunos matriculados;

     
    select * from disciplina
    where codigo not in(
    select codturma from disciplina_turma)


    g) Selecione as turmas que não há alunos matriculados,

    select * from turma
    where codigo not in(
    select codturma from aluno_turma)

    h) Selecione todas as turmas e se houver alunos vinculados às turmas, os mesmos;



    select a.Nome, t.Nome from aluno a, turma t, aluno_turma at
    where a.Matricula = at.CodAluno and t.codigo = at.CodTurma

    i) Faça a Média dos alunos, baseados nas notas existentes

    select (Nota1+Nota2+Nota3)/3, CodAluno, CodDisciplina from Notas

    Acontece que essa letra H, eu realmente não consegui, alguém poderia me dar uma ajudinha?

    quarta-feira, 4 de maio de 2016 13:43

Respostas

  • Bom dia.

    O exercício H quer que você traga os dados da tuma independentemente de existirem alunos para ela. Para isso você deve usar o LEFT JOIN.

    Veja se o código abaixo te ajuda.

    SELECT T.Codigo, T.Nome, T.Ano_Sem, T.Sala, Al.Matricula, Al.Nome, Al.Sexo, Al.Idade
    FROM Turma T
    LEFT JOIN Aluno_Turma AlT ON Alt.CodTurma = T.Codigo
    LEFT JOIN Aluno al ON Al.Matricula = AlT.CodAluno


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    • Marcado como Resposta AdSoNaTuRaL quarta-feira, 4 de maio de 2016 14:10
    • Não Marcado como Resposta AdSoNaTuRaL quarta-feira, 4 de maio de 2016 14:10
    • Sugerido como Resposta Danilo Telles quarta-feira, 4 de maio de 2016 14:19
    • Marcado como Resposta AdSoNaTuRaL quarta-feira, 4 de maio de 2016 14:27
    quarta-feira, 4 de maio de 2016 13:51

Todas as Respostas

  • Bom dia.

    O exercício H quer que você traga os dados da tuma independentemente de existirem alunos para ela. Para isso você deve usar o LEFT JOIN.

    Veja se o código abaixo te ajuda.

    SELECT T.Codigo, T.Nome, T.Ano_Sem, T.Sala, Al.Matricula, Al.Nome, Al.Sexo, Al.Idade
    FROM Turma T
    LEFT JOIN Aluno_Turma AlT ON Alt.CodTurma = T.Codigo
    LEFT JOIN Aluno al ON Al.Matricula = AlT.CodAluno


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    • Marcado como Resposta AdSoNaTuRaL quarta-feira, 4 de maio de 2016 14:10
    • Não Marcado como Resposta AdSoNaTuRaL quarta-feira, 4 de maio de 2016 14:10
    • Sugerido como Resposta Danilo Telles quarta-feira, 4 de maio de 2016 14:19
    • Marcado como Resposta AdSoNaTuRaL quarta-feira, 4 de maio de 2016 14:27
    quarta-feira, 4 de maio de 2016 13:51
  • Mariana Del Nero, sua resposta é bastante útil. É exatamente o que preciso. O grande problema é que anda não cheguei nessa parte de Inner join, left join :(

    • Editado AdSoNaTuRaL quarta-feira, 4 de maio de 2016 14:09
    quarta-feira, 4 de maio de 2016 14:09
  • Bom dia,

    um arquivo legal para entender JOIN.

    http://www.devmedia.com.br/sql-join-entenda-como-funciona-o-retorno-dos-dados/31006

    Marque a resposta se foi útil.

    Abraço


    quarta-feira, 4 de maio de 2016 14:20
  • É bem fácil mesmo, mas seria possível fazer sem JOIN?
    quarta-feira, 4 de maio de 2016 14:36
  • Então...

    Pra fazer isso sem JOIN você teria que ter um SELECT que trouxesse apenas as turmas e outro SELECT que trouxesse os alunos que possuem registros na tabela Aluno_Turma.

    Acho isso um pouco estranho, mas é a única maneira de trazer esses dados, segundo a descrição do exercício, sem utilizar JOIN. Mas ainda defendo que a maneira correta e mais simples é fazer com JOIN.


    Mariana Del Nero /* Se a resposta foi útil, não esqueça de marcá-la */

    • Sugerido como Resposta Danilo Telles quinta-feira, 5 de maio de 2016 12:45
    quarta-feira, 4 de maio de 2016 19:31