background top icon
background center wave icon
background filled rhombus icon
background two lines icon
background stroke rhombus icon
header logo icon

Download "Уроки SQL. Иерархические (рекурсивные) запросы / Илья Хохлов"

input logo icon
"videoThumbnail Уроки SQL. Иерархические (рекурсивные) запросы / Илья Хохлов
Similar videos from our catalog
|

Similar videos from our catalog

Video tags
|

Video tags

SQL
Базы данных
иерархические запросы
рекурсивные запросы
Илья Хохлов
sql
базы_данных
oracle
Subtitles
|

Subtitles

subtitles menu arrow
  • ruRussian
Download
00:00:01
dear subscribers thank you for
00:00:03
watching our channel thank you for studying
00:00:06
sql or database with us at the
00:00:09
time of the release of this video there are
00:00:11
already more than 300 of us
00:00:13
this video I prepared specifically for
00:00:16
those who already work with databases
00:00:19
if you have recently started studying both square and
00:00:21
eat, just write your first
00:00:23
queries, try to make the first join and
00:00:26
write the first ones, most likely you
00:00:29
may not understand a lot, but for those who
00:00:31
willingly write searched queries, I hope
00:00:34
that today you will also find a
00:00:35
lot of interesting and useful things in this topic at the link
00:00:40
below you will find a low file that
00:00:42
you can pass on your test to the
00:00:44
database and try to do the
00:00:46
same thing that we will do today in our
00:00:48
video lessons, all our students have
00:00:50
their own database test, go through your
00:00:52
1 and password, upload the downloaded file
00:00:55
and try to do the same Now
00:00:58
let’s understand what
00:00:59
hierarchical in recursive queries are and
00:01:02
why we don’t need hierarchical
00:01:05
or recursive queries when
00:01:06
we
00:01:09
have information about the main elements and
00:01:13
child elements in one single table, as well as the children of
00:01:16
these child elements, that
00:01:18
is, when we have a hierarchy structure
00:01:21
and everything is one table, that is, not another
00:01:23
table that you can johnny's
00:01:24
and get connections, everything is in the
00:01:26
same table, I have one table and
00:01:28
there are parent elements, the main
00:01:31
elements are related elements, that is,
00:01:33
sub elements in these sub elements can
00:01:35
have their own for elements and so the depth
00:01:37
can be very deep and
00:01:38
dynamic of any depth and when you need to
00:01:41
build a report that would show the entire
00:01:43
hierarchy the entire depth of all connections, that
00:01:48
is, erotic
00:01:49
or recursive queries, then a query
00:01:50
that allows you to build connections to
00:01:52
one table by itself the required
00:01:54
number of times you will win one table to
00:01:57
another and you indicate the freedom, indicate
00:02:01
that you need to connect with itself until the
00:02:04
entire hierarchy is built until you
00:02:05
have all the leaves of the tree until you
00:02:08
know the directory needs to be built
00:02:10
and you need to display all its
00:02:12
subordinates
00:02:13
all the subordinates of these subordinates and so
00:02:15
on until you you see for each
00:02:16
ordinary employee or for example if you
00:02:18
take the table of
00:02:19
employees if a brother is usually an example
00:02:21
of goods when you need to display goods under
00:02:22
goods under under goods and so on, or for
00:02:24
example there are
00:02:25
main offices under offices under branches, that
00:02:27
is, you need to build the entire
00:02:30
structure and the entire pyramid here for this,
00:02:32
when we are all listed in one table, this is why we
00:02:34
need hierarchical or
00:02:36
recursive queries; in practice, they are not
00:02:38
used so often, but nevertheless,
00:02:41
this question should be for an
00:02:43
interview and the topic takes place;
00:02:45
this is also used in ours in
00:02:48
practice, so let's figure out what
00:02:50
it is and be sure to download the file from the
00:02:52
description below and
00:02:53
carry it out to your database, you
00:02:56
will create a table from this and make the
00:02:58
same request, and so with the help of
00:03:00
hierarchical queries you can
00:03:02
build a complete tree of
00:03:06
connections from the main element to its children and
00:03:09
also on the contrary, you can do the
00:03:11
opposite and having some element you
00:03:14
can go to the most important
00:03:15
node, you can go to the most
00:03:18
important root, and that is, even if you
00:03:21
have, for example, some subordinate Ivanov
00:03:23
Ivanovich, you can display the
00:03:25
data who is his main
00:03:28
in fact, you can bring him to the main this
00:03:31
main thing and so on until you see you
00:03:33
go out to the chief director, for example,
00:03:35
if let’s say your person who you
00:03:37
are calling for example has an employee who is not in
00:03:39
the office and you need to find out the status of the tasks, you
00:03:41
contact the manager if
00:03:43
he there is no such leader,
00:03:44
that is, you can go to the most
00:03:47
important thing that is available, that is,
00:03:48
we build the entire chain either from root to
00:03:51
leaves or
00:03:53
Noel. you can build a connection to the
00:03:55
root by going through all the nodes,
00:03:57
so let’s figure it out, here’s an example of
00:03:59
a table that you will get after
00:04:00
a walk, you were looking for a file for your place in the
00:04:03
database, and so in this table there is a
00:04:07
column, in theory this is the
00:04:09
employee’s time sheet number or just go, let’s just
00:04:11
dress us there Harvard identifier, that
00:04:15
is, this is an indicator,
00:04:16
each employee has their own end-to-end
00:04:17
identifier 2 columns are the full name
00:04:20
of each employee with their phone numbers 3 columns are
00:04:22
very interesting 3 columns this is
00:04:25
actually who is the boss who is not
00:04:28
somehow the manager of each employee
00:04:31
here on the first line here in the column the status
00:04:34
is outlined We know the column
00:04:36
on the first line, let’s say
00:04:38
the chief Alexey Georgievich the chief
00:04:39
Alexey Georgievich
00:04:40
is our general director, he doesn’t have the
00:04:43
one to whom he reports, that is, this
00:04:46
employee is not subordinate to
00:04:48
anyone, he is the most important, everyone
00:04:50
reports to him in the end, but he us, the
00:04:52
most important thing is that he is not subordinate,
00:04:54
so his graph is basady, there is nothing
00:04:56
empty, then you brought it up, I’ll give you an
00:05:00
idea of ​​three employees who
00:05:02
report to the main most important thing, this is the
00:05:03
chief accountant, the
00:05:05
sales director, the chief super super
00:05:07
general manager and the
00:05:08
director of those departments, yes, we you
00:05:10
still have articles related to the area,
00:05:15
so I also took here the
00:05:16
9th department, let it be that all three of them are
00:05:20
directly subordinate to that
00:05:22
employee who has his own money 1 that
00:05:25
is, third employees thoughtful
00:05:28
Lyudmila Anatolyevna
00:05:29
chief accountant soon rich Anton
00:05:31
Vasilyevich sales director and
00:05:33
right-handed Petr Ivanovich, here are three
00:05:35
employees, they are directly
00:05:37
subordinate to the one who has his number one,
00:05:41
they are directly
00:05:47
subordinate to the chief director, only not three report to him directly, so he communicates with them,
00:05:49
talk about Shukadev in
00:05:51
each department, and so on for each of
00:05:52
these employees each of these chiefs
00:05:54
has their own subordinates, so let's figure
00:05:57
it out,
00:05:58
let's look at an example, that is, so
00:06:03
we have thoughtful radiant Alena Anton
00:06:06
Vasilyevich Pyotr Ivanovich are directly
00:06:07
subordinate to
00:06:08
the general director, let's
00:06:10
look at even greater depth,
00:06:14
for example, take Perry's managers,
00:06:15
let's see we have
00:06:16
Anatoly Andreevich Volodin Lyubova
00:06:20
Prokofievna
00:06:21
Oleg Pavlovich and John Ivanovich
00:06:24
these are all our managers, all our
00:06:26
managers, they report to the sales director,
00:06:28
that is, the employee
00:06:30
who has his own ID three because they have
00:06:34
about the basady column, that is, they are the boss to
00:06:38
whom they report, the number 3 is indicated, we are
00:06:40
looking for such an employee whose table
00:06:43
number three we find this skorobogat
00:06:46
Anton Vasilyevich
00:06:48
cool of course cool and all this can be
00:06:50
built with one query
00:06:51
let’s now make this title
00:06:53
go to the database go to our
00:07:01
database you all have your own database
00:07:03
some don’t please write about
00:07:06
it go to ours database here is the
00:07:09
downloaded file for me hierarchical queries
00:07:12
01 just drag it Scarif Oleg
00:07:16
in the perfil de opera window and click
00:07:18
execute data the file will create a sign in
00:07:23
Odessa Abbaszada
00:07:25
Garden with knowledge and fill it with data and
00:07:28
we will have ready tables so that
00:07:30
you can earn money here the file is
00:07:31
nothing allocated that is, to me only.
00:07:34
water is selected to complete everything, that
00:07:37
is, I don’t select anything, click
00:07:38
execute, this request is executed,
00:07:40
look, a table is created, data is added to
00:07:43
it, everything is saved, all tables are
00:07:46
ready for use, we have it
00:07:48
now in tribulus, here is the employee,
00:07:55
and here it is, and we’ll transfer it ourselves, we’ll write
00:07:58
our first one with you hierarchical query,
00:08:01
erotic or recursive, let's open
00:08:04
new screenshots of the cat and let's write
00:08:07
first, just we see all the employees
00:08:14
display from employees display
00:08:21
information and employees yes for now let's
00:08:23
put an asterisk let's see all
00:08:24
the columns click
00:08:26
execute enter the data to the
00:08:32
end click here we see all the
00:08:35
employees are still the same the very data
00:08:37
that we ourselves just saw on the
00:08:38
slide, let’s get our signs, yes Dima let’s let the forest
00:08:41
is called s, so first I’ll
00:08:43
display the employee indicator, that is, and
00:08:46
I can come the columns that I want to display,
00:08:48
let’s display the notifier of each
00:08:50
employee, display it while beer displays
00:08:56
fila and display the
00:08:59
position as an example status of this position,
00:09:05
you can reset it, we’ll see you just
00:09:08
need to, then only the columns are indicated and so to
00:09:10
connect the table in itself
00:09:12
so that the orderly point employees by
00:09:13
itself in our case it’s a little bit the employees of
00:09:15
your workers the
00:09:16
database of course it can
00:09:18
be called something else there products
00:09:21
departments
00:09:22
employees too and so in order to make
00:09:25
the connection by itself, to make a
00:09:26
hierarchical request, you need to write
00:09:29
this magic word connect buy here are
00:09:37
the words of the words words can i buy the command
00:09:41
can't buy
00:09:42
and will connect the employees by itself now
00:09:45
you need to indicate how to connect in
00:09:48
our example, so we had to give It’s
00:09:51
still weak from the planting, we’ll bring out social security, plant
00:09:56
the conclusions so that it can also be seen, for now
00:10:00
I’ll highlight only this without can i buy what
00:10:03
was done so far, show us again, we need
00:10:08
to connect to the boss all those who
00:10:10
belong to them,
00:10:11
then you so that after the boss the
00:10:14
first one and a half 3 are brought out after the chief
00:10:19
accountant and to see all those that
00:10:20
he owns after the
00:10:22
sales director all the managers after the director of the technical
00:10:24
department so that everything is maintained and the technicians are
00:10:26
software engineers, that’s what you see
00:10:28
everything and the technicians then add connect
00:10:30
buy that is, with it the tobacco itself and
00:10:32
indicate how simple the idea is equal to the boss
00:10:38
one everything and here you need to add one more
00:10:42
word simple one word that is, we
00:10:44
showed how to connect what needs to be
00:10:46
connected put nicknames with Komi clothes,
00:10:49
but you need to indicate in what order we
00:10:51
want to derive this from the root with a leaf,
00:10:53
that is, from the boss to subordinates to the most
00:10:56
subordinates of course, or from a specific
00:10:58
subordinate and you need to bring it to the
00:11:02
boss first, let's give it from the subordinate boss, that
00:11:04
is, which side we are now connecting,
00:11:05
that is, we have indicated that we are connecting boss
00:11:07
desany everything, and now we need to indicate in just
00:11:09
one word from there where these we will
00:11:12
go
00:11:13
to subordinates not to the boss but to the subordinates,
00:11:16
that is, from the boss to the subordinates, so
00:11:19
here we will write this
00:11:22
additional word, that is, to
00:11:24
indicate the direction of the connection, you need to
00:11:26
before this word
00:11:27
it before this, or before this,
00:11:30
add another word prior, if
00:11:36
you put used r here it
00:11:37
turns out that you will go to the boss,
00:11:40
knowledge of the subordinate, you will be the tekke
00:11:43
image from his bareback to his boss, and so
00:11:45
go to the most important thing,
00:11:46
and we are going on board from the boss, of course, to the
00:11:50
subordinate, so here it has become
00:11:51
overcome, and before this fact, everything
00:11:54
remains the final touch: who to
00:11:58
start with, that is, which boss to start with and go up
00:12:00
to the subordinates, you can start from the middle one, maybe the most
00:12:03
important one, let’s
00:12:05
start with the most important thing, how to understand
00:12:06
which one is the most important one, you can 11 or basady
00:12:09
and knew to start with the one whose
00:12:15
basady column is empty, that’s it yes, we
00:12:24
enter data from the employees, we connect it by
00:12:26
ourselves through the nuclear garden,
00:12:29
we go towards the employees, it’s just that the
00:12:31
foliage is Prior and we’ll start with the one whose
00:12:34
inspector is 1, or you can write as I
00:12:37
wrote just now, we need to start with the one
00:12:41
who doesn’t indicate that he
00:12:43
reports to someone that is, basady is not specified is not
00:12:45
specified in this eye to whom he reports, in
00:12:48
principle, the whole request is written, let’s
00:12:50
execute it, let’s look at the data, so
00:12:56
now we have the chief Alexey
00:12:58
Georgievich, the
00:12:59
general director, is entered while
00:13:01
his first subordinate is then
00:13:05
displayed. and now he’s an accountant in the
00:13:08
accountant’s office, then here’s the second
00:13:10
subordinate, the soon-to-be-rich
00:13:12
sales director, all his subordinate
00:13:13
managers are displayed, then 9 departments are displayed,
00:13:15
all his and the technicians are entered, the request
00:13:19
worked out just perfectly, let’s
00:13:22
play a little more with the beauty of the
00:13:24
data, and even now it will be a little
00:13:26
difficult if After all, you are writing a request, you have
00:13:28
just started writing or you are
00:13:30
not yet familiar with the square, let this lesson
00:13:32
not disappoint you in terms of complexity, then
00:13:35
everything will be simple for you, but for now I ask you to
00:13:38
stay at the screens only for those who already
00:13:40
consider themselves a guru and let's look at
00:13:43
some more things that can be
00:13:45
used in queries not for their intended
00:13:48
purpose in order to achieve interesting
00:13:49
results.
00:13:50
Now we ourselves are displaying our full name simply in
00:13:55
continuous text by giving topsy,
00:13:57
but we want, for example, to display it with an indentation,
00:14:01
that is, to display subordinates with a slight
00:14:04
indentation from the main date objects so that
00:14:07
the thoughtfulness is a little bit our air
00:14:10
with the beauty of 3 accountants who are
00:14:15
subordinate to the main one is suitable for them to be
00:14:17
with an even larger ledge so that they can see that they
00:14:18
are on the same level but lower than the
00:14:21
thoughtful loved Natalina,
00:14:23
let’s add indents on the left in
00:14:25
this interesting way, now it
00:14:27
will be a little difficult and so let’s go
00:14:31
if you have 40 stations, please write a question
00:14:34
in the note under this video, I will
00:14:37
definitely answer and so let’s
00:14:39
use the special volp function
00:14:42
from functions for the ipad, you remember, I give it, it
00:14:47
just glues some
00:14:49
symbols to some text on the left until this
00:14:54
text that we glue becomes the
00:14:56
expected length here the length we will
00:14:58
expect if we have an indentation level of pack
00:15:03
1 none if we have employees who are already
00:15:05
subordinates went from the first from the main one
00:15:08
to what indentation level let's say brackets
00:15:09
three characters so that we expect that
00:15:11
the spaces will be 300 in front or 5 let's
00:15:14
what was more if we were sent under
00:15:16
subordinates then
00:15:17
5 plus 5 10 until it’s under the approach, and
00:15:21
so we’ll give the Alpatov and glue it to the
00:15:25
fila itself, take a space,
00:15:35
take a limit,
00:15:36
we’ll expect the number of level characters to
00:15:43
be multiplied by 5 and we’ll glue
00:15:48
the spaces, in principle, that’s all, that is, if
00:15:54
we have a humidity level, then there’s more time
00:15:56
and time again it is shown by the level of
00:15:59
humidity which time this bull is
00:16:01
naturally shy, that is, how
00:16:04
subordinate a person is to us,
00:16:07
by the way, in principle, for the first employee,
00:16:08
for the most important ones, in principle, there is no need to make an indentation,
00:16:11
so there is no need for one, maybe by
00:16:14
5 and adding to the first five spaces
00:16:17
let's do it for the first one like this
00:16:19
so if the humidity level
00:16:24
is 11 minus 1 will be zero zero
00:16:30
multiplied by 5 will be zero so it will
00:16:32
stick 0 spaces 0 spaces
00:16:34
stick to it
00:16:35
if the humidity level is already two that
00:16:38
is, let's go employees subordinates
00:16:39
for example here thoughtful loved Anton she is
00:16:41
already at the second level we will have 2
00:16:44
minus 1 it turns out 11 multiplied by 5 5
00:16:48
come and paste 5 space and now
00:16:52
let's create
00:16:56
everything you can do this interesting
00:16:59
example we did with you using the
00:17:00
wpad function let's complete the request to the end let
00:17:03
's see for the result, here we make
00:17:07
access and the employee has no indents for the chief
00:17:12
director of the general director,
00:17:14
but because we have a level of
00:17:17
1 1 minus 1 will be
00:17:19
000 s for 50 ledges we are lost, yes
00:17:24
here are bears 5 ledges five spaces
00:17:26
here 10 spaces here with now we apparently look
00:17:28
like this in a beautiful way
00:17:30
we see the humidity that here they are and
00:17:32
that the employees are a debit credit to its balance sheet
00:17:36
on and are subordinate to the
00:17:39
chief accountant the chief accountant
00:17:42
is subordinate to the
00:17:43
general director so skorobogaty
00:17:46
is at the same level as the
00:17:48
thoughtful skorobogatova his
00:17:50
subordinates and so on now
00:17:55
let's let's do another example with
00:17:58
hierarchical queries, now let's
00:18:00
get it the other way around, knowing some
00:18:02
employee, let's get it to his boss
00:18:03
and here she is his boss and then to him
00:18:05
until we get to the main
00:18:06
director himself, we can get basically the same
00:18:09
thing or just fila
00:18:15
as you want I’ll come out with my full name, the dogs give out
00:18:19
the information, but only the prior, we
00:18:24
will now go to the boss, the boss will
00:18:28
fly in and we will go on a tour to the base from a
00:18:30
simple employee, we will be on this side of the
00:18:32
base and start with that, for example, let’s
00:18:36
take, let’s take some valuable
00:18:41
employee, let’s take, let’s go down here before
00:18:44
the engineer, let's find out who his
00:18:48
manager is, which driver is his
00:18:50
manager, and so on, that is, start
00:18:52
with and at Kazan, start at whose full name is
00:18:55
equal to start with the person whose phil,
00:18:58
for example, this is this, that is, we
00:19:01
did the same thing, we knocked out
00:19:03
all the employees from all the certificates for
00:19:05
employees, you
00:19:07
need to start with the task whose
00:19:13
name is like this, or you can
00:19:16
indicate the indicator below and connect the same
00:19:18
one basady
00:19:19
and go towards the boss from the ordinary
00:19:23
employee, we will go to unfold our
00:19:25
tree to match the bosses towards the boss and
00:19:27
his boss and so on further, until we reach the
00:19:28
most important thing, we perform this, here is our
00:19:33
result, we have an
00:19:39
engineer, let’s move on, we have his
00:19:43
head, these are the directors,
00:19:44
and these departments are his head, the most
00:19:47
important is the general
00:19:48
director, this is the one to whom he reports, he
00:19:53
was probably left here with more
00:19:54
beautiful indents it was,
00:19:56
but nevertheless the meaning is clear, I hope
00:19:58
that everything is clear, friends, thank you very much
00:20:00
for your viewing, please
00:20:03
like if you liked this video and
00:20:04
subscribe to the channel if you have not
00:20:06
subscribed yet Oleg Khokhlov was with you bye

Description:

При построении SQL запросов иногда может понадобиться соединение таблицы с самой собой. Если таблица содержит список сотрудников с указанием должностной подчиненности, или, например, филиалов и входящих в них под-филиалов, то можно вывести всю иерархию одним запросом, дописав всего лишь несколько слов! В этом уроке рассматриваются иерархические (рекурсивные) запросы. Практический SQL файл к уроку Вы можете скачать по ссылке: https://prime-soft.biz/std/sql/hierarchical_queries_01.sql Спасибо за Ваше внимание! Напишите, пожалуйста, под видео Ваши комментарии!

Preparing download options

popular icon
Popular
hd icon
HD video
audio icon
Only sound
total icon
All
* — If the video is playing in a new tab, go to it, then right-click on the video and select "Save video as..."
** — Link intended for online playback in specialized players

Questions about downloading video

mobile menu iconHow can I download "Уроки SQL. Иерархические (рекурсивные) запросы / Илья Хохлов" video?mobile menu icon

  • http://unidownloader.com/ website is the best way to download a video or a separate audio track if you want to do without installing programs and extensions.

  • The UDL Helper extension is a convenient button that is seamlessly integrated into YouTube, Instagram and OK.ru sites for fast content download.

  • UDL Client program (for Windows) is the most powerful solution that supports more than 900 websites, social networks and video hosting sites, as well as any video quality that is available in the source.

  • UDL Lite is a really convenient way to access a website from your mobile device. With its help, you can easily download videos directly to your smartphone.

mobile menu iconWhich format of "Уроки SQL. Иерархические (рекурсивные) запросы / Илья Хохлов" video should I choose?mobile menu icon

  • The best quality formats are FullHD (1080p), 2K (1440p), 4K (2160p) and 8K (4320p). The higher the resolution of your screen, the higher the video quality should be. However, there are other factors to consider: download speed, amount of free space, and device performance during playback.

mobile menu iconWhy does my computer freeze when loading a "Уроки SQL. Иерархические (рекурсивные) запросы / Илья Хохлов" video?mobile menu icon

  • The browser/computer should not freeze completely! If this happens, please report it with a link to the video. Sometimes videos cannot be downloaded directly in a suitable format, so we have added the ability to convert the file to the desired format. In some cases, this process may actively use computer resources.

mobile menu iconHow can I download "Уроки SQL. Иерархические (рекурсивные) запросы / Илья Хохлов" video to my phone?mobile menu icon

  • You can download a video to your smartphone using the website or the PWA application UDL Lite. It is also possible to send a download link via QR code using the UDL Helper extension.

mobile menu iconHow can I download an audio track (music) to MP3 "Уроки SQL. Иерархические (рекурсивные) запросы / Илья Хохлов"?mobile menu icon

  • The most convenient way is to use the UDL Client program, which supports converting video to MP3 format. In some cases, MP3 can also be downloaded through the UDL Helper extension.

mobile menu iconHow can I save a frame from a video "Уроки SQL. Иерархические (рекурсивные) запросы / Илья Хохлов"?mobile menu icon

  • This feature is available in the UDL Helper extension. Make sure that "Show the video snapshot button" is checked in the settings. A camera icon should appear in the lower right corner of the player to the left of the "Settings" icon. When you click on it, the current frame from the video will be saved to your computer in JPEG format.

mobile menu iconWhat's the price of all this stuff?mobile menu icon

  • It costs nothing. Our services are absolutely free for all users. There are no PRO subscriptions, no restrictions on the number or maximum length of downloaded videos.