Íà ãëàâíóþ ñòðàíèöó
Microsoft's employee about SQL-EX.RU
Alexey Shulenin,
subject matter expert (SQL Server & BI),
evangelist, DPE, Microsoft
Being an open educational research source, the site SQL Exercises (http://www.sql-ex.ru/ )
is quite a valuable educational and methodical project, enabling within limited
period of time to develop the skills of SQL use up to a master-class level. Notwithstanding
the fact that the number of distance learning systems is exponentially growing during
the recent period of time, I would characterize this project not only as innovative
by nature, but also as unique due to its following characteristics.
1. The chosen field of activity.
1.1. Software development embraces numerous fields beginning with design of user
interface and up to system programming. Nowadays the majority of programmers in
the world are applied specialists, focused on the aspect of businesses logics. With
few exceptions, there is no such thing as pure applied specialist. For example,
having no idea, how transactions operate on the level of DBMS, it would be rather
problematic, I suppose, to operate them on the level EnterpriseServices / ÑÎÌ+.
How can one expect to model the scalable application, not knowing the principles
of blocking and isolation of transactions? How can one submit the necessary database
data on the form or to determine the component of business logics, having no knowledge
of SQL? Thus, each developer of business logics is bound to be to a certain extent
also a database developer, and thus the content of sql-ex.ru site will be useful
for numerous developers.
1.2. I have come across research of Gartner, in which the technological priorities
of development of IT were determined, the results being based on the inquiry of
1400 managers of information departments all over the world: the key question was
what were the major fields your company is going to invest in the nearest future
and what will be the proportional share of such investment (http://www.gartner.com/it/page.jsp?id=501189 )?
As it is clear from the results of the report, the mysterious BI abbreviation is
number one in the rating. This term was introduced in 1958 (http://www.research.ibm.com/journal/rd/024/ibmrd0204H.pdf ),
and initially its meaning was similar to that currently associated with DSS notion
(decision support systems). Later on it acquired a more general meaning. Currently
BI (Business Intelligence) signifies «a set of technology tools focused on querying,
reporting and multi-dimensional analysis on top of a data warehouse». Data warehouses
are traditionally supplied with object-relational) database servers, and thereby
SQL is used as means of communication with such servers. Teaching SQL, sql-ex.ru
site ultimately helps people to acquire profession that according to the forecasts
is expected to be in high demand.
1.3. CIO-priorities are reinforced by the development of the market of BI-applications
on the whole (http://www.cnews.ru/news/top/index.shtml?2007/07/02/257161 ),
the amount of growth in this sphere amounted to 11.5% in 2006 and to 12.5% in 2007.
It is expected that this rate will steadily increase up to 2011, when the total
market amount will constitute 7 billion $, and after that the growth rate will become
lower as compared to the previous period (it will decrease up to 8-9%), but nevertheless
this dynamics will be characterized by stability (it’s first order derivative will
be a positive number). Thus the profession the key requirements of which are SQL
knowledge and skills of DBMS operation, will not only guarantee the employment in
future, but will pay on the regular basis.
2. Process of education
It’s not enough to communicate useful knowledge, which will be needed for practical
purposes; it’s of paramount importance to do this in the correct way. Surfing on
ru.net one can come across distance learning sites that have formalized approach
to presenting content for the purpose of which investments into such sites have
been made, but their efficiency is made null and void due to negligence with which
the learning process has been organized. The leader of SQL Exercises project is
the high school teacher, who solves such problems within the framework of his job
duties. S.I.Moiseenko is an associate professor of the faculty of higher technologies,
South Federal University; he is a PhD in physics and mathematics, author of SQL
textbook.
3. Community of experts
SQL Exercises, the most popular resource on databases, unfailingly attracts the
most experienced professionals in this field, who are present on all the forums
of the site and provide aid in finding the optimal solution, considering the task
from another perspective or paying attention to some peculiarities of SQL, that
are not the matter of general knowledge, or to its concrete case of use. Communication
on the forum takes place in the atmosphere of friendliness and well-wishing cooperation;
nobody ever blames the newcomer for his ignorance, as it is quite often the case
with sql.ru, which is notorious for its snobbishness and inadequate behavior of
its moderators. The ratings of participants are calculated automatically in accordance
with the number of the problems solved, the level of complexity of these problems
and time of their solving on sql-ex site. Such ratings help the participants to
adequately estimate their level of knowledge as well as the level of competence
of their interlocutors on the forum.
4. The quality of exercises
All the exercises are grouped into 3 blocks, which can be conventionally called
qualification, credit and optimization units. Suffice to say that SQL tasks, set
at Microsoft and Oracle certification examinations (as well as those, presented
at some other examinations) won’t pose any difficulty for the person, who has completed
the three blocks of exercises from beginning to end. Basically, on the site there
are those exercises that can be characterized as the problems of master class level
or, if it can be formulated in this way, Olympiad-class exercises. One can be pretty
sure that, the specialist, who has acquired extensive practical experience of solving
such problems, won’t find SQL tasks, which he will face during his practical work,
too difficult to solve.
5. Certification process
One of the major advantages of sql-ex.ru site is that it provides educational exercises
and guidance free of charge. Payment is required only for the certificates of the
1st and the 2nd level of education (in the amount of 40 USD each) (depending on
the number of solved problems). When the first certificate is obtained, the second
one costs only 20 USD. If the participant chooses to do so, he can make no order
for such a certificate – the acquired knowledge is his major achievement in any
case. However the certificates of sql-ex.ru win more and more acknowledgement among
recruitment agencies and employers who are selecting the personnel for the jobs
connected with database operation, as they evidence that the person, awarded with
such certificates, is a brilliant specialist in SQL.
Disadvantages
As it is often the case, our drawbacks are nothing more than the outcome of our
positive traits and vice a versa. I cannot say that I have noticed a lot of negative
things about sql-ex.ru, however there are certain things I would like to point out,
and they are immediately connected with the merits of the site.
I have already mentioned the fact that the level of difficulty of the majority of
problems presented on sql-ex.ru site is rather high, they can be even recommended
for programming Olympiads. However this can be treated not only as the advantage
of the site but also as its drawback. Is it really worthwhile in terms for practical
usefulness to solve such problems, if such a high level of difficulty is not really
needed for practical purposes? One has an impression that the authors of the problems
have not satisfied their Olympiad enthusiasm and there is a certain gap between
their methods and reality. There is no denying of the fact that exercise is good
for brain, but it’s better to mark such extremely difficult problems with a special
sign and to subdivide them into a separate group. Is it really needed to train people
to survive in the jungles of the Amazon river if in all probability no one of them
will find himself there ever in his life? It is better to concentrate on more practical
things that have more chances to come up in their potential work situations. If
someone is ready to find time in order to get acquainted with the exotic tasks of
high complexity, he is welcome to have a look at the corresponding section. In order
to make such a subdivision it is necessary to verify the level of complexity of
the problems. There are certain problems, the level of complexity of which is clearly
misinterpreted; the latter is proved by the amount of time spent by the participants
on their solving. Initially it was probably assessed in accordance with the considerations
of the author, but when certain statistical data is accumulated, the level of complexity
should be adjusted in accordance with this data.
Apart from real «Olympiad-class» problems, the solving of which is really worthwhile
(on the condition that one has some time to spend); there are certain problems,
the solution of which is artificially complicated because of the limitations set
on sql-ex.ru site. The most essential to my mind is the fact, that the problem should
be solved using the single SELECT-query in accordance with the limit of the plan
cost. In real life one uses local variables, temporary tables, operational constructions
based on conditional operators and cycles, one also programs his own procedure or
function using CLR. It is easier and simpler. In addition to that, if in real work
situation you solved such a problem using a la sql-ex.ru method, nobody would be
happy about it. On the contrary, people would consider you to be a little bit of
an oddball because of the amount of time wasted on it. On the whole, it is clear,
where such limitations on sql-ex.ru site originate from: in the first place measures
are taken for no abuser to cause any damage under such settings and in the second
place the reason is to safeguard the server against run-away queries. But the limitation
prohibiting me to use ÑÒÅ regardless of the fact that all such problems have been
solved and verified on SQL Server 2005 for quite a long time – occurs as extremely
queer to me.
The number of exercises, the application of SQL to which is unlikely and the presentation
of which on the site cannot be sufficiently justified, are quite a separate issue.
The most demonstrative example is problem number 59. There is a certain instrument
for solution of each problem. For example, hammer is intended to drive in a nail
and corkscrew is intended to take the cork out of the bottle, and it is not worthwhile
to teach people to do it in any other way. God save them from doing vice a versa
at their workplace, they are hardly likely to be praised for it. It is hardly likely
that someone of the site visitors is trying to master SQL out of theoretical interest.
It is clear, that the majority is going to use the acquired knowledge in practice.
SQL is a declarative language, characterized by the functional style of programming
and targeted for operation of data sets. It is its advantage. Even taking into account
the affection with which the authors treat SQL, it is not worth pretending that
it can be used for solving problems of any type, it is not really so. In reality
no one would ever go to such extremes, using SQL for solution of algorithmic and
imperative problems. If the worst comes to the worst, one can program a function.
My conclusion is that this problem is of no practical value. It is of no use and
even unfortunate to waste time on such problems, because the artistic skills with
which you would cope with their solving in the end are nowhere to be applied in
the long run.
Another useless complication is posed by the exercises of the 3-rd database («Ships»).
In my opinion the main difficulty here is produced by the peculiarities of its scheme,
which is characterized by an artificially slipshod design (for example, "the ships,
not related to Ships table can be related to Outcomes table”), such a wording was
purposefully formulated in order to complicate the process of solving, and such
a complication has nothing to do with difficulties of real life projects. In real
life nobody would ever use such a scheme in work and the first stage of fulfillment
of the project would be its normalization and clarification in order to avoid the
consequent difficulties when formulating the queries using the slipshod scheme.
Another disadvantage, arising due to the automated mode of fulfillment and check
of exercises, is the use of two databases to test a query: the major one and the
verification one. If the results of processing of the query have coincided with
those of template solution, the problem is considered to be solved. One can scan
the results of processing of the query on the major database, but the results for
the verification database are not available. You are merely informed on whether
the results have coincided or not. The creation of verification database is quite
justified, it helps to avoid bare adjustment of the results using the functions
select ... union all … .Its structure is analogous to that of the major one, but
it has its own data set. Such a structure is seemingly logical. But the problem
is that the data are distributed much easier in the major database. One has an impression
that someone with secret malevolence has stuffed the verification database with
extreme examples and exclusive situations that no one would ever think of, considering
the structure of data in the major one. The typical type of error is «Your query
has selected the correct data from the major database, but it has failed verification
procedure on the invisible one» and it means, that actually the speculative type
of SQL study is suggested to the visitor. To my mind this is also an artificial
complication, that could occur mostly to academics, having the academic way of thinking,
but it has not much in common with real life tasks. In his opinion Dmitry Valuyev
(http://shop.piter.com/book.phtml?978546901362 )
states in a rather involved way «The purpose of the verification database is the
safeguard against adjustment of the result; however this is an example of what is
called in statistics "general total"». Actually it is the neither case. First, the
use of verification database does not guarantee that the results won’t be adjusted
after the process of examining of the structure of data using the method of errors
and some indirect information. To adduce such an example I have solved the problem
¹98 in this way, having got the query at record minimal speed and having spent on
its solution the shortest time possible. Second, there is no such a term as «general
total» in real life and the verification database is not an example of it. There
are some exercises, upon the fulfillment of which the verification database has
been upgraded so to say. The most severe testing procedure, which is applied to
the program, won’t make you 100% sure that it is free from bugs. It can guarantee
only that, within the limits of a given set of initial terms and of some particular
distribution of data it will produce the forecasted results. Basically it is the
criterion of the correctness of the program. It is a black box, and no other functions
are delegated to it. In practice simple economical factors are at play: why should
one increase the safety level from 0.999 up to 0.9999, if such works would cost
dozens of times more than it has already been spent on the program. Let’s consider
one more point. «In fact all over the world thousands of programmers make thousands
queries, that would never be admitted upon the procedure of verification database
testing, if there would ever be such a verification database in real life. However
the majority of them never undergo the “verification database” testing process».And
one can be 99.9% sure that such a process will never be initiated. Excuse me my
impertinence, but I strongly doubt that all the SQL-queries used in applications
of South Federal University for example were written in an absolutely and ultimately
correct way. But nobody is ever interested in this fact, just because they tend
to produce correct results within the concrete data set they are intended to address
to. And as long as they do so, nobody will ever learn about it. And if some unforeseen
amendment of rare type has been introduced into the data set, and the results have
become incorrect, the error is identified and eliminated for that particular case,
because the data set can be scanned and it is not necessary to guess what it is
like. There is no such a thing as invisible data sets in practice. It is better,
it seems to me, to interchange the functions of the major and the invisible database,
because using the approach that is applied now (determine the situation in which
such a query can fail to work) on sql-ex.ru site – the site should be intended not
for developers but for testers.
Íà ãëàâíóþ ñòðàíèöó