SQL exercises
September 20, 21:09 MSK


forgot password?

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

SQL Exercises news letters, latest issue

#728 (2018-09-15)

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
Metallage	8
selber  	6
NewEXE		6
prafful namdev	5
OwlSoul		4
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):
19. Kursist (232)
34. BW (164)
37. Gosha (203)
53. vasilyvanc (162)
Applicants for Top 100 have advanced in rating:
172. parra (123, 657.668)
188. Tsarapkin (119, 823.456)
293. SergeyKiev (91, 6.275)
374. Galya (83, 110.823)
Number of subscribers - 3052
Number of rating's participants - 12105
Number of second-stage's participants - 3101
Number of third-stage's participants - 127
On the learning stage - 263184
Certified specialists in total - 803

ТОР 20

NoPersonNumber of
Last_SelNumber of
1Doschenko V.N. (mcrain)12502313762125182034.553519.7302462018-08-242018-09-09
2Kostomarov A.V. (al29)12553073764023254133.2702271.5352442018-09-062018-09-10
3Krasovskij E.A. (pegoopik)1265230376562973210.25414.1242412018-08-282018-09-06
4Kreslavskij O.M. (Arcan)1265306376544236276.271217.5812382018-09-072018-09-14
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)12603063764332212797.4613371.3762272018-09-082018-09-13
8Karasyova N.V. (vlksm)1259225376384494290.336241.6822232018-09-022018-09-02
9Kurochkin P.A. (qwrqwr)1256306376273225255.917164.3922112018-09-092018-09-14
10Vyazovetskov A.S. (alex_v)9240161235624361177.45294.1962042018-05-172018-05-17
11Filippkin D.V. (_dimon_)1244115295972737194.554127.9092022016-07-202018-08-29
12Movlyanov A.P. (Oct)7244102958813072353.7232448.8471932017-01-232018-08-27
13Sal'nikov S.A. ($erges)122720625551253317.50514.4981812015-02-022018-01-23
14Kukushkin S.A. (smog)92251632952723681054.5621122.1811532017-10-032018-09-13
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-09-13
18Drozdkov A.N. (anddros)51921624445224412.34111.0911082014-07-212015-01-29
19Chernov D.V. (Kursist)1921123237490674674.537633.1811032018-09-092018-09-13
20>Shindin A.V. (AlShin)115010233693617408.527777.457992018-09-142018-09-14

Best results for last week

1>Verdiyev A. (Nuklinoid)131817017385014 Sep 2018
2>Khomutova (anzhela6660)83310010168914 Sep 2018
3Nabokov I.A. (Ivan_Krsk)72210010171614 Sep 2018
4>Kudryavtsev V.A. (Vladmir_A)52591019150814 Sep 2018
5Ksynina N. (KsNV_)47160652714 Sep 2018
6Burgess (MeestaDave)553606110012 Sep 2018
7>German (o4karik88)419606348314 Sep 2018
8Abdulmanapov A. (Dvarik)323505166614 Sep 2018
9>IVV L. (BaLeo)3341620408614 Sep 2018
10Chernov D.V. (Kursist)22114041913 Sep 2018
11Shustov V.V. (shustovvlad)242404104912 Sep 2018

Let's learn SQL

UNPIVOT and NULL values


Suppose we need to rotate a row containing a NULL value in one of its fields.

WITH utest AS
(SELECT 1 a, 2 b, NULL c)
SELECT * FROM utest;

I.e., we need to transform

a	b	c
1	2	NULL


a	1
b	2

Let’s use UNPIVOT:

WITH utest AS
(SELECT 1 a, 2 b, NULL c)
SELECT col, value FROM utest
value FOR col IN (a,b,c)
) AS unpvt;

The first surprise we’re in for is a compile error:
The type of column "c" conflicts with the type of other columns specified in the UNPIVOT list.

This means the server didn’t implicitly convert the column "c" containing NULL to the type of the first two columns (that can be considered to be of integer type).

Let’s do it explicitly:

WITH utest AS
(SELECT 1 a, 2 b, CAST(NULL AS INT) c)
SELECT col,value FROM utest
value FOR col IN (a,b,c)
) AS unpvt;

col value
a	1
b	2

Now, here’s the second surprise – as it turns out, UNPIVOT ignores NULL values, and doesn’t include them in the result set.

The first thing that comes to one’s mind is, to replace NULL with some valid value definitely not present in the column. Say, if the subject area doesn't allow negative values in column c, we can replace NULL with -1:


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:

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