SQL - Doubt Query Exists and Not Exists

Question

Guys, I’m a bit of a beginner in SQL Database and I’m having a problem doing a query. I need a light, help because I’m not yet understanding how a query with a exist (has to be exists and exists only!) within another.

I have the tables Work in which is stored the Work (books), Author, Authorship(Relates Author to Work), Publisher(Publisher of books) and Copy(Which are the books themselves).

Database entities required for resolution (did select * from them):

Work:

Author:

Authorship:

Publishing house:

Exemplary:

Creation of tables with their keys:

Work:

create table Obra
(CodObr number (2) not null,
 NomObr char (35) not null,
 TipObr number (1) not null,
 constraint PK_Obra primary key (CodObr),
 constraint FK_Obra_TipoObra foreign key (TipObr) references TipoObra);

Author:

create table Autor 
(CodAut number (2) not null, 
 NomAut char (35) not null, 
 CodPai number (2) not null, 
 constraint PK_Autor primary key (CodAut), 
 constraint FK_Autor_Pais foreign key (CodPai) references Pais);

Authorship:

Create table Autoria
(CodObr number(2) not null,
 CodAut number(2) not null,
 constraint PK_Autoria primary key (CodObr, CodAut),
 constraint  FK_Autoria_Autor foreign key (CodAut) references Autor,
 constraint FK_Autor_Obra foreign key (CodObr) references Obra on delete cascade);

Publishing house:

create table Editora
(CodEdi number(2) not null,
 NomEdi char(35) not null,
 constraint PK_Editora primary key (CodEdi));

Exemplary:

create table Exemplar
(NumExe number(2) not null,
 CodObr number(2) not null,
 AnoEdi number(4) not null,
 CodEdi number(2) not null, 
 ValExe number(6,2) not null,
 constraint PK_Exemplar primary key (NumExe),
 constraint FK_Exemplar_Editora foreign key (CodEdi) references Editora, 
 constraint FK_Exemplar_Obra foreign key (CodObr) references Obra);

I have to get the code and the name of the publishers of José Lins do Rego’s Works.

OBS: Remembering that I think I can only use exists and not exists!

I’ve tried to do that:

select codEdi, nomEdi from Editora where exists (select codEdi from Exemplar where exists (select codObr from Autoria where exists (select codAut from Autor where nomAut = 'José Lins do Rego' and codAut = Autoria.codAut)));

and this:

select codEdi, nomEdi from Editora where exists (select codObr from Exemplar where exists (select codAut from Autoria where exists (select codAut from Autor where nomAut = 'José Lins do Rego' and codAut = Autoria.codAut) where codObr = Exemplar.codObr) where codEdi = Editora.codEdi);

But either error or return me all Publishers of the bank, something I do not want.

The correct result would be Editora FTC and Editora LTC.

You could tell me how one exists inside the other?

Answer 1

Hello, good morning!

Your query can be done as below:

select editora.CODEDI, editora.NOMEDI 
from Editora editora 
inner join Exemplar exemplar on editora.CODEDI = exemplar.CODEDI
inner join Obra obra on exemplar.CODOBR = obra.CODOBR
inner join Autoria autoria on obra.CODOBR = autoria.CODOBR
inner join Autor autor on autoria.CODAUT = autor.CODAUT
where autor.NOMAUT = 'José Lins do Rego'

Explanations: The command will select in the Publisher table, in the code and Name fields and where the author of their works is Mr. José Lins do Rego. However, to know which works are authored by him we need to look at the copies. Then we add to the Publisher table:

1) The table of Exemplary where the publishing code on the table Publishing house is equal to publishing code on the table Exemplary.

2) The table below Work where the work code on the table Exemplary is equal to work code on the table Work

3) Table Authorship where the work code table Work is equal to work code table Authorship

4) The table Author where the author code table Authorship is equal to author code table Author

And finally we put the condition that we will have the name of the author, in the table Author be Mr. José Lins do Rego.

Answer 2

There are several ways to do this query, but the best I see is the use of INNER JOIN.

See the example below.

select ed.codEdi, ed.nomEdi 
from Editora ed
JOIN Exemplar ex on ex.codEdi = ed.codEdi
JOIN Autoria au on au.CodObr = ex.CodObr 
JOIN Autor aut on aut.codAut = au.codAut 
where aut.nomAut = 'José Lins do Rego'