SQL exercises
Language 
July 22, 09:58 MSK
Login:

Password:

forgot password?
Registration

Personal page
Developers & Thanks
For employers
References
Feedback
Support SQL-EX.RU







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

SQL Exercises news letters, latest issue

#720 (2018-07-21)

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 version of mobile application from Dmitry Ponomarenko is now available for work with site forums and more. Try this.

§
The most popular topics of the forum
Topic        Messages
§
Authors of the week in the forum
Author	Messages
Алексей В	6
pegoopik  	5
malika_st	4
bysidby		3
selber  	2
§
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
222	2018-02-14	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):
8. mcrain (211)
§
Applicants for Top 100 have advanced in rating:
182. Ranil (119, 343.887)
186. parra (114, 613.494)
236. Cordova (106, 8.695)
302. mazepa (94, 4776.476)
303. Poganka (93, 4996.149)
306. borey.mdb (81, 12.377)
355. Svitochev (84, 526.572)
§
Statistics:
Number of subscribers - 3046
Number of rating's participants - 12060
Number of second-stage's participants - 3089
Number of third-stage's participants - 127
On the learning stage - 257397
Certified specialists in total - 797

ТОР 20

NoPersonNumber of
Sel_ex
Last_SelNumber of
DML_ex
ScoresDaysDays_2Days_3S_3LastSolvedLastVisit
1Krasovskij E.A. (pegoopik)1264227356512927210.25414.1242402018-07-132018-07-20
2Kostomarov A.V. (al29)1252953462822624128.3412266.8982402018-07-052018-07-20
3Kreslavskij O.M. (Arcan)1263229356484181273.280214.6142372018-07-142018-07-20
4Boiko D. (Angellore)12632283364538465236.4073825.1402372018-02-202018-03-23
5Bezhaev A.Yu. (Baser)1262227346432924201.513178.8212342018-05-042018-07-06
6Dubinskij A.V. (_velial)12593073563931672797.3933371.3762282018-07-162018-07-18
7Karasyova N.V. (vlksm)1259225346334354290.336241.6822242018-04-152018-07-09
8Doschenko V.N. (mcrain)82382113559324812011.731496.9082222018-07-182018-07-19
9Kurochkin P.A. (qwrqwr)1255223356233170255.904164.3922122018-07-162018-07-20
10Vyazovetskov A.S. (alex_v)9240161235634361177.45294.1962052018-05-172018-05-17
11Filippkin D.V. (_dimon_)1244115295972737194.554127.9092022016-07-202016-09-29
12Movlyanov A.P. (Oct)7244102958813072353.7232448.8471932017-01-232017-04-03
13Sal'nikov S.A. ($erges)122720625551253317.50514.4981812015-02-022018-01-23
14Kukushkin S.A. (smog)92251632952723681054.5621122.1811532017-10-032018-07-12
15Yatsuk A.A. (Faust_zp)1186255244591984144.150108.8401372012-07-122012-07-30
16Kuznetsov V.S. (herrRo)1171260234181167478.51173.3351222012-02-082013-06-18
17Grinkevich D.L. (Dmitrij Grinkevich)1320410274672059367.128932.3431092017-05-312018-05-22
18Drozdkov A.N. (anddros)51921624445224412.34111.0911082014-07-212015-01-29
19Zajtsev V.S. (Sleeping)192091842746036011040.0531327.686992018-04-172018-04-19
20Shindin A.V. (AlShin)114725223366928408.505777.457992011-05-052014-05-18

Best results for last week

Nosurnamen_selsel_allsel_scoresdml_scoresscoresratinglast_visit
1Kalyuzhnyj D.S. (mazepa)9941721930220 Jul 2018
2>Agirre Kordova A.K. (Cordova)61061501523620 Jul 2018
3>Nesterkin A. (nestam)103414014127220 Jul 2018
4Shoshkin R. (ROM303)92314014163419 Jul 2018
5Bardin M.N. (MBardin)63714014168220 Jul 2018
6>Petrenko M.M. (Diaboliko)75112273989820 Jul 2018
7>Yakovlev M.V. (MikhailYakovlev535)5821201265420 Jul 2018
8>Yakovlenko V.G. (musthacrackezh)7681201267920 Jul 2018
9Burgess (MeestaDave)72210010304919 Jul 2018
10Rebrin A. (Hydra86)716819370717 Jul 2018
11Rakhmatullin R. (Ranil)311980818220 Jul 2018
12Karachyov A.O. (ZILIO)336808119420 Jul 2018
13Menyajlo S.S. (Kagerorus)340639108920 Jul 2018
14Kostyushko Yu.V. (kostushko)419606189417 Jul 2018
15Nikanorov (parra)211440418614 Jul 2018
16Mikhalenkov S.V. (black_horse)17240442016 Jul 2018
17Volkov S. (Wolf778)248404122719 Jul 2018

Let's learn SQL

Text data aggregation. STRING_AGG function.

S.I.Moiseenko

Let’s consider the following task.

List the names of all Japanese vessels in the Ships table in a single line, separated by commas.

There is no problem getting the list of Japanese ships:

SELECT name FROM Ships s JOIN Classes c ON s.class=c.class
WHERE country='Japan' ORDER BY name;

In MySQL, there is the remarkable GROUP_CONCAT aggregate function that provides the solution to this task:

SELECT GROUP_CONCAT(name) ships_list FROM Ships s JOIN Classes c ON s.class=c.class
WHERE country='Japan' ORDER BY name;

ships_list
haruna,hiei,kirishima,kon,musashi,yamato

By default, the comma is used as a list separator; however, we can choose any symbol.

Grouping makes it easy to obtain a ship list for each country:

SELECT country, GROUP_CONCAT(name) ships_list FROM Ships s JOIN Classes c ON s.class=c.class
GROUP BY country
ORDER BY country, name;

country ships_list
gt.britain	renown,repulse,resolution,ramillies,revenge,royal oak,royal sovereign
japan	haruna,hiei,kirishima,kongo,musashi,yamato
usa	iowa,missouri,new jersey,wisconsin,north carolina,south dakota,washington,california,tennessee

In SQL Server, our task can be solved in a less natural way – by retrieving the result set as XML:

SELECT STUFF(
(SELECT ','+name AS 'data()' FROM Ships s JOIN Classes c ON s.class=c.class
WHERE country='Japan'
ORDER BY name FOR XML PATH('')
0,1,1,'');

Grouping by country will make the query even more complex. Thus, we won’t even attempt to do that, since in SQL Server, beginning with version 2017, there is a function called STRING_AGG that allows concatenating strings. It takes two mandatory arguments – the string expression to be concatenated, and the string separator.

Read more...

Useful links

§ Official communities of the site sql-ex in social networks: SQL Exercises at VK.com and SQL Exercises at LinkedIn.com

§     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, MariaDB-10.2.13 (compatible with MySQL 8), PostgreSQL 10.3.

§ If you like this site, vote for us:
del.icio.us
dzone.com
Digg.com
stumbleupon.com



Home
Week news
Certification
SQL exercises
Forums
Ratings
HELP
Performance
Profile
References
Rambler's Top100
Copyright SQL-EX © 2002-2018. All rights reserved.
contact