M
M
Mortech2020-11-10 15:56:33
SQL
Mortech, 2020-11-10 15:56:33

How to write similar sql query?

Good afternoon. There is a similar task, so far there are only assumptions on it.
I myself learn sql by sql-ex solving problems (stopped at 15). I've been scratching my head over it for about a week now. Thanks in advance for any hints.
from attempts there is a similar one, but I'm not sure if this is what you need

SELECT id_object 
FROM ( — объекты где умер последний собственник более указанного количества месяцев
SELECT id_object, max(dead) as lastdead
FROM  —список собствеников
—- INNER JOIN др. таблицы для опр. даты смерти (dead)
INNER JOIN some tables for death date
GROUP BY id_object
HAVING max(dead) < нужная дата
) AS deadobjects

WHERE ( — на момент смерти последнего собственника не было ни одного живого зарегистрированного
NOT EXISTS ( — список живых зарегистрированных по указанному объекту на заданную дату
SELECT * FROM residence
WHERE id_residence_type =1 and begin_date<lastdead and end_date>lastdead —живые на указанную дату
AND deadobjects.id_object =id_residence.type.id_object —по указанному объекту
)
)
AND
NOT EXISTS ( —живые собственники
)

But here I don’t quite understand what will be the table with the list of owners and what table will be used to determine the date of death.
Write an SQL query that will return a list of objects (id_object) where the last owner died for more than the specified number of months and at the time of the death of the last owner there was not a single living registered (id_residence_type=1). During implementation, it should be taken into account that the task is formulated so that the situation should be correctly processed when a person dies, for example, on 01/01/2005, and he is discharged on 01/25/2005 (or they forget to write him out).
Structure itself: residence table
id_object: NUMBER(8)
id_residence_type:NUMBER(2)
begin_date: DATE
id_agent: NUMBER(8)
id_personal_doc: NUMBER(8)
end_date: DATE
note: VARCHAR2(1000)

table residence_type
id_residence_type:NUMBER(2)
name:VARCHAR2(50)

dda6e0b9c0.png
b6a21b4f70.jpg

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question