SQL exercises
January 19, 22:18 MSK


forgot password?

Transact-SQL tests
Personal page
Developers & Thanks
For employers
Support SQL-EX.RU

Would you get the following certificate?
Such certificate is necessary on a site, but not for me
Probably I would get it for supporting the project
Such certificate is unnecessary one

View Results

Сколько стоит адвокат по уголовным делам
Адвокат по уголовным делам. Профессиональная помощь. Звоните

Аль факер
аль факер

SQL Exercises news letters, latest issue

#641 (2017-01-14)

Week news

As the important news can appear and between releases of news letters, I shall use this page for the publication of the information on all changes on the site.
The forum is not so suitable for this purpose since the message there "escapes" and can appear on previous page before it will lose a urgency.
So look in here if you wish to be well informed about last events on the site. The link to this page is in section of news at the main page below a subscription banner.
New exercise (SELECT) is available now for discussion under the number of 306 (1 point).

Mobile application from Dmitry Ponomarenko is now available for work with site forums. Try this.
The most popular topics of the forum
Topic        Messages
Authors of the week in the forum
Author	Messages
pegoopik  	23
Baser  		19
Gosha		10
Winnermann	7
temagr		3
Available competitions on query optimization
Exercise	Date	     Author
147	2012-02-23	$erges
159	2012-07-25	anddros
170	2011-07-15	qwrqwr
182	2012-02-16	Baser
192	2012-03-03	Baser
197	2014-11-06	pegoopik
214	2015-06-24	pegoopik
259	2012-07-23	anddros
Additional non-optimization competitions
Exercise	Date	Author/Organizer of competition
71	2010-12-26	anddros (closed)
88	2012-07-12	crescent/Pegoopik  (closed)
91	2012-05-31	smog/$erges
102	2016-07-04	GriGrim/pegoopik
121	2012-07-06	ZrenBy/anddros
153	2010-09-21	Ozzy
Changes among leaders (solved exercises of third stage):
11. al29 (194, 202)
22. Dmitriy Varenikov (145)
57. Anton (261)
Applicants for Top 100 have advanced in rating:
115. selber (163, 583.886)
138. VadimI (131, 18.593)
224. Gosha (107, 256.776)
Have been certified last week:
Airrule (A17260711) [BK] - Ufa, Rossiya
Chatte (A17100984) [BK] - Yaroslavl', Rossiya
strannik123 (A17142315) [BK] - Moskva, Rossiya
Number of subscribers - 3233
Number of rating's participants - 12928
Number of second-stage's participants - 2945
Number of third-stage's participants - 118
On the learning stage - 202272
Certified specialists in total - 755

ТОР 20

NoPersonNumber of
Last_SelNumber of
1Shuvaev A.A. (strannik123)14632602662413 Jan 2017
2>Karashasheva A. (lina_karashash)123418018121013 Jan 2017
3>Matveyeu A. (KsandrMatveyev)143117017249113 Jan 2017
4>Dedkov D.A. (dsni)102115015328513 Jan 2017
5Grishunin M.F. (MikhailFG)53713013109313 Jan 2017
6Lis K.D. (k1player)82813013273312 Jan 2017
7I (VadimI)51311201213812 Jan 2017
8Shkarednyj (Gosha)51071101122413 Jan 2017
9>country K. (countryKZ)426808143413 Jan 2017
10Semenov Yu.V. (uratol)336808180313 Jan 2017
11>Kostomarov A.V. (al29)22086061113 Jan 2017
12Votrina A. (Almir4ik)45460679812 Jan 2017
13>Gorbenko P.E. (Paukanishe)237606109613 Jan 2017
14>Poltoranin S.S. (Stas911)47606297913 Jan 2017
15Lozinskij A. (alozinskyi)45606585811 Jan 2017
16Pavlov A.V. (Anton)31725275713 Jan 2017
17Krasavina O.V. (Chatte)37750546013 Jan 2017
18Klimenko (Klimenko Andrej)37050573813 Jan 2017
19Mirzakhanov S. (Ubityj Enot)317505388312 Jan 2017
20Kuznetsov (avk-odn)47505545709 Jan 2017

Best results for last week

1>country K. (countryKZ)132217522167106 Jan 2017
2Idrisov R.I. (Airrule)7631501562803 Jan 2017
3>Matveyeu A. (KsandrMatveyev)101714014378606 Jan 2017
4Krasavina O.V. (Chatte)5741101150005 Jan 2017
5>Dedkov D.A. (dsni)81110010468006 Jan 2017
6Chernov D. (Kursist)4769172640006 Jan 2017
7Karashasheva A. (lina_karashash)622909161706 Jan 2017
8Tarasenko O.O. (Tarasenko_OO)439808109405 Jan 2017
9Kovalev I.V. (Funtik)29670726405 Jan 2017
10Grishunin M.F. (Grishunin M.F.)532606127306 Jan 2017
11>Lis K.D. (k1player)620606374106 Jan 2017
12Petrov Y. (YuriPetrov)21915492606 Jan 2017
13Shkarednyj (Gosha)310250524706 Jan 2017
14Chesnokov S.A. (chsa)47505271602 Jan 2017

Let's learn SQL

MySQL. Usage of query variables

E.A. Krasovskiy

People rather often ask whether there are equivalents for analytic (windowing) functions in MySQL. No, there are not. To replace them, self join queries, complex subqueries, etc, are used. Most of such workarounds turn out to be ineffective.

There are no recursive queries in MySQL either. However, a part of problems usually solved by analytic functions or recursive queries can be handled by features available in MySQL.

One of these features is the unique mechanism of processing variables within a SQL query, which is very unusual for other DBMS. In MySQL, you can declare a variable within a query, change its value, and put it into the result set of the SELECT statement for output. And the most notable thing is, the processing order for query rows, and thus the order values are assigned to variables, can be defined by custom sorting!

Note: this article implies expressions within the SELECT statement are processed in order from left to right; however, there is no confirmation of such a processing order in the official MySQL documentation. You need to keep that in mind when switching from one server version to another. To ensure the required evaluation order, a fake CASE or IF statement can be used.

Read more:
Equivalent of recursive CTEs
Equivalents for analytic functions

Useful links

§ All articles from news letters are putting on the site Books and articles about SQL thereafter.

§ We invite you to visit the Interactive SQL textbook.
    The resource is positioned as a "handbook" for this site SQL-EX.COM, but can be used irrespective of it also.

§ The following DBMS are used on the site for solving exercises: SQL Server 2012 Express, Oracle Database 11g Express Edition, MySQL 5, PostgreSQL 9.

§ If you like this site, vote for us:

Week news
Rambler's Top100
Copyright SQL-EX © 2002-2016. All rights reserved.