SQL exercises
Language 
August 19, 01:34 MSK
Login:

Password:

forgot password?
Registration

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



SQL Exercises news letters, latest issue

#724 (2018-08-18)

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 exercises :
36 (DML) from selber (2 points).
230 (SELECT) from pegoopik (2 points).
§
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
Kursist		6
Andalon		2
TridenT		2
Koliuchiy	2
REDPILL		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):
3. mcrain (164)
23. Kursist (207, 208, 219)
§
Applicants for Top 100 have advanced in rating:
158. Ranil (126, 367.749)
164. Cordova (125, 19.130)
195. borey.mdb (102, 16.565)
§
Statistics:
Number of subscribers - 3051
Number of rating's participants - 12051
Number of second-stage's participants - 3097
Number of third-stage's participants - 127
On the learning stage - 260138
Certified specialists in total - 800

ТОР 20

NoPersonNumber of
Sel_ex
Last_SelNumber of
DML_ex
ScoresDaysDays_2Days_3S_3LastSolvedLastVisit
1Krasovskij E.A. (pegoopik)1265230366542946210.25414.1242412018-08-012018-08-17
2Kostomarov A.V. (al29)1252953462722624128.3412266.8982392018-07-052018-08-15
3Doschenko V.N. (mcrain)32471643661225102028.539513.7162392018-08-162018-08-17
4Kreslavskij O.M. (Arcan)1263229366494195273.280214.6142362018-07-282018-08-17
5Boiko D. (Angellore)12632283364438465236.4073825.1402362018-02-202018-08-16
6Bezhaev A.Yu. (Baser)1262227346422924201.513178.8212332018-05-042018-07-06
7Dubinskij A.V. (_velial)12593073664031802797.3933371.3762272018-07-292018-08-17
8Karasyova N.V. (vlksm)1259225346324354290.336241.6822232018-04-152018-08-15
9Kurochkin P.A. (qwrqwr)1255223366243182255.904164.3922112018-07-282018-08-02
10Vyazovetskov A.S. (alex_v)9240161235624361177.45294.1962042018-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-08-16
18Drozdkov A.N. (anddros)51921624445224412.34111.0911082014-07-212015-01-29
19Shindin A.V. (AlShin)114725223366928408.505777.457992011-05-052014-05-18
20Zajtsev V.S. (Sleeping)192091842745936011040.0531327.686982018-04-172018-04-19

Best results for last week

Nosurnamen_selsel_allsel_scoresdml_scoresscoresratinglast_visit
1>Zhulanov S.G. (borey.mdb)71022002019517 Aug 2018
2Rebrin A. (Hydra86)64213013133416 Aug 2018
3Bezgubenko A.S. (oWart)63612012119116 Aug 2018
4Rakhmatullin R. (Ranil)412690915816 Aug 2018
5Agirre Kordova A.K. (Cordova)31258101816415 Aug 2018
6Chernov D.V. (Kursist)32068082317 Aug 2018
7Eryomenko D. (Eryomenko Dar'ya)56480852113 Aug 2018
8>Yurin D.I. (Diman9425)65680884517 Aug 2018
9>Nasibulin K.N. (megadeth.nn)622808171117 Aug 2018
10>Gavrilov (dzhoncheg)519808183217 Aug 2018
11Sal K.S. (Kseksekse)715808250017 Aug 2018
12Burgess (MeestaDave)533707193917 Aug 2018
13Soldatov (sergio97)3341014453617 Aug 2018
14Mikhalenkov S.V. (black_horse)27540440313 Aug 2018
15>Frolova E. (ELinka)33404740117 Aug 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