Return the name of the course and the names of the disciplines for which there are no enrolled students

Asked

Viewed 16 times

0

I have these tables and would like to know how I select course name and the names of the disciplines that do not have students.

create table aluno(
codaluno int primary key,
nome varchar(30),
datanasc date);

create table curso(
codcurso int primary key,
descricao_c varchar(200));

create table disciplina(
coddisc int primary key,
descricao_d varchar(200),
creditos int,
cod_curso int,
constraint fk_codcurso foreign key (cod_curso) references curso (codcurso));

create table matricula(
cod_aluno int,
cod_disc int,
data date,
nota1 number(3,1),
nota2 number(3,1),
nota3 number(3,1),
constraint fk_codaluno foreign key (cod_aluno) references aluno (codaluno),
constraint fk_coddisc foreign key (cod_disc) references disciplina (coddisc));


insert into aluno values (01, 'John', to_date('07/04/1998', 'DD/MM/YYYY'));
insert into aluno values (02, 'Anna', to_date('03/07/2000', 'DD/MM/YYYY'));
insert into aluno values (03, 'Marcio', to_date('05/12/1995', 'DD/MM/YYYY'));

insert into curso values (01, 'Computação');
insert into curso values (02, 'Direito');

insert into disciplina values (01, 'Algoritmos 1', 4, 1);
insert into disciplina values (02, 'Código Penal', 4, 2);

insert into matricula values (01, 01, to_date('08/01/2018', 'DD/MM/YYYY'), 9.3, 10.0, 8.8);
insert into matricula values (02, 01, to_date('07/01/2019', 'DD/MM/YYYY'), 8.7, 7.5, 10.0);
insert into matricula values (03, 01, to_date('09/01/2017', 'DD/MM/YYYY'), 10.0, 10.0, 9.5);

Thank you in advance

  • Search for "NOT EXISTS", a way.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.