Harry Lewis Harry Lewis - 4 months ago 9
SQL Question

How to find people who have deceased through SQL from a table?

I have two doubts:

I have a table as follows:

authorID int NOT NULL,
authName varchar(255) NOT NULL,
authSurname varchar(255),
authPlaceOfBirth varchar(255),
authDOB date(),
authDoD varchar(255),

Now, I want to find the authors who have died. That is, if the value of DoD exists in the table, then they have died. How to do this? That is, a particular value in a column exists?

Something like this:

SELECT authName
WHERE authDoD is not NULL?

Second, I have two tables as follows:

TABLE inventory_genre
genreID int NOT NULL,
inventoryID int NOT NULL,
PRIMARY KEY (genreID,inventoryID)
inventoryID int NOT NULL,
title varchar(255),
wholesale int,
markup int,
qtyinStock int,
Discount int,
PRIMARY KEY (inventoryID)

I want to list all the genres that have no associated titles in the inventory. I know I have to subtract but I am not able to come up with it exactly. Please guide me in the right direction!


Not sure I understand the criteria you are describing in the first question, but either

select * from author where authDOB is not null;


select * from author where authDOB = 'some value that I dont know';

For the second one, you could use exists or in with a nested select:

select * from genre where id not in (select genreId from inventory_genre);