Solve a logical problem for students in SQL
It all started with the next screen in the whatsap group with the following content (the original is not in Russian, but this is not important):
The sum of the ages of Sasha, Petit and Viti is 67 years old. When Vitya was at the age of Sasha, there were still 3 years left until Petit was born. What is the sum of the ages of Sasha and Petit?
It is clear that the task is for schoolchildren, but after long hours of solving problems in SQL-EX (by the way, thanks to them more for the qualitative and interesting tasks), the first thing that comes to mind is:
Where tab is any sign with the number of lines about 100. Solution: 32, to which the colleague in the group responded: “And how many individually?”. We remove the DISTINCT and change the "+" to "," ... it turns out 31 pairs of options ... not quite the exact answer. For some reason, I wanted to answer for sure, and having at least some evidence on hand.
So. We take all existing customers in the improvised base with the names of the task and their birthdays:
In the original, the names of others, it is clear that the database names on the passport.
We connect three times and adding conditions that the sum of the ages of Sasha and Petit is 32, when Wite 35:
but I did not think about counting the number of clients in advance, and as it turned out, it was in vain. Of course, there were a lot of them, and multiplying each other, even despite some condition, it would be possible to wait for the result forever. As you need to reduce the number of lines, let's say we leave only namesakes. After adding the condition, the number of possible options turned out to be about half a million. The conditions were also added that age cannot be negative, and there was some confusion at birth dates on the same day, so I slightly corrected accuracy. But all the same there were a lot of results.
Already unsubscribed in the group that he tried to prove something, but nothing happened. One of his colleagues suggested searching for only clients with a rare middle name. This did not suit the conditions of the problem at all, but threw the next thought.
Why don't the task characters be brothers? Those. not the fact that it is the brothers, we can not verify, but namesakes with the same middle name - this is easy. Perhaps there are no such ones in the database, it scared a little, but after adding the condition, the query still issued 13,000 possible variants.
Understanding that I was spending time on all useless nonsense, before I left everything, I decided to check the names and middle names. And here it is, there are no problems with last names, but instead of a middle name on the endless pages of the sample there were dashes. Those. the request basically left only those clients whose middle names were not known. By simply adding the last condition, I received only 3 entries. This screen with the words "the task was hardly formulated in the 50s, and if you leave only 2001, then the Brothers were 35, 3, 29"
Of course, all this is very conditional and for the sake of a joke, it is not necessary to take everything seriously. Having fun as we can, we are programmers ...
The sum of the ages of Sasha, Petit and Viti is 67 years old. When Vitya was at the age of Sasha, there were still 3 years left until Petit was born. What is the sum of the ages of Sasha and Petit?
It is clear that the task is for schoolchildren, but after long hours of solving problems in SQL-EX (by the way, thanks to them more for the qualitative and interesting tasks), the first thing that comes to mind is:
SELECTDISTINCT r2 + r3
FROM (SELECTrownum r1 FROM tab) r1
CROSSJOIN (SELECTrownum r2 FROM tab) r2
CROSSJOIN (SELECTrownum r3 FROM tab) r3
WHERE r1 + r2 + r3 = 67AND r1 - r2 = r3 + 3Where tab is any sign with the number of lines about 100. Solution: 32, to which the colleague in the group responded: “And how many individually?”. We remove the DISTINCT and change the "+" to "," ... it turns out 31 pairs of options ... not quite the exact answer. For some reason, I wanted to answer for sure, and having at least some evidence on hand.
So. We take all existing customers in the improvised base with the names of the task and their birthdays:
WITH t AS
(SELECTDISTINCTupper(NAME) NAME,
to_date(birthdate, 'RRRRMMDD') dob
FROM clients
WHEREupper(NAME) IN ('САША', 'ПЕТЯ', 'ВИТЯ'))In the original, the names of others, it is clear that the database names on the passport.
We connect three times and adding conditions that the sum of the ages of Sasha and Petit is 32, when Wite 35:
SELECT v.dob vitya, --дни рождения реальных Вити, Саши и Пети
s.dob sasha,
p.dob petya,
add_months(v.dob, 35 * 12) data_zadachi -- Предположительная дата рождения задачи)FROM t v
CROSSJOIN t s
CROSSJOIN t p
WHERE v.name = 'ВИТЯ'AND s.name = 'САША'AND p.name = 'ПЕТЯ'AND trunc((add_months(v.dob, 35 * 12) - s.dob) / 365.24) +
trunc((add_months(v.dob, 35 * 12) - p.dob) / 365.24) = 32but I did not think about counting the number of clients in advance, and as it turned out, it was in vain. Of course, there were a lot of them, and multiplying each other, even despite some condition, it would be possible to wait for the result forever. As you need to reduce the number of lines, let's say we leave only namesakes. After adding the condition, the number of possible options turned out to be about half a million. The conditions were also added that age cannot be negative, and there was some confusion at birth dates on the same day, so I slightly corrected accuracy. But all the same there were a lot of results.
Already unsubscribed in the group that he tried to prove something, but nothing happened. One of his colleagues suggested searching for only clients with a rare middle name. This did not suit the conditions of the problem at all, but threw the next thought.
Why don't the task characters be brothers? Those. not the fact that it is the brothers, we can not verify, but namesakes with the same middle name - this is easy. Perhaps there are no such ones in the database, it scared a little, but after adding the condition, the query still issued 13,000 possible variants.
Understanding that I was spending time on all useless nonsense, before I left everything, I decided to check the names and middle names. And here it is, there are no problems with last names, but instead of a middle name on the endless pages of the sample there were dashes. Those. the request basically left only those clients whose middle names were not known. By simply adding the last condition, I received only 3 entries. This screen with the words "the task was hardly formulated in the 50s, and if you leave only 2001, then the Brothers were 35, 3, 29"
Of course, all this is very conditional and for the sake of a joke, it is not necessary to take everything seriously. Having fun as we can, we are programmers ...