SQL exercises Language  March 10, 19:44 MSK
Login:

Password:

forgot password?
Registration

Transact-SQL tests
Modelling of data
Personal
page
Exercises voting
Query optimization
Developers & Thanks
For employers
References
Feedback
Support SQL-EX.RU

Poll
For what DBMS, besides SQL Server, you would like to have exercises on the site?
Oracle
PostgreSQL
MySQL
Another
Nothing is necessary any more



View Results
AddThis Social Bookmark Button

SQL Exercises news letters, latest issue

#284 (2010-03-06)

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.
§
Has corrected a bug with checking the exercises:
18 (SELECT, rating stage) - formulation from Lady.
112 (SELECT, rating stage) - formulation from qwrqwr.
162 (SELECT, rating stage) - formulation from author.
17 (DML) - data from alex2008.
4 (SELECT, rating stage) - data from risp
§
Changes among leaders (solved exercises of third stage):
8. _yizraor (154, 155, 158)
13. Angellore (140, 152)
16. Eagleoff (162)
17. AlShin (152)
23. mz (152)
36. Valdaj (140)
§
New persons in TOP 100 or have returned (solved exercises and 2nd-stage time):
79. Ckpa6 (128, 14.180)
98. qwrqwr (121, 24.982)
§
Have advanced in rating:
66. lilyok (138, 118.022)
82. Corleone (125, 7.444)
§
Applicants for Top 100 have advanced in rating:
108. Pavel_yu (117, 258.269)
112. tolikas (118, 20.094)
121. vtan (117, 100.518)
125. uon (117, 315.188)
164. Vladius (110, 21.617)
171. hamp (123, 62.164)
§
Have been certified last week:
ANDREAS_PITER (A10055059) [BK] - g. Sankt-Peterburg, Rossiya
§
Statistics:
Number of subscribers - 3849
Number of rating's participants - 11371
Number of second-stage's participants - 2097
Number of third-stage's participants - 65
On the learning stage - 10944
Certified specialists in total - 426

ТОР 20

NoPersonNumber of
Sel_ex
Last_SelNumber of
DML_ex
ScoresDaysDays_2Days_3S_3LastSolvedLastVisit
1>Zotov P.G. (Ozzy)116416422403725199.850211.3737505 Mar 2010 05 Mar 2010
2Karasyova N.V. (vlksm)1164251224051369127.33294.1367403 Feb 2010 05 Mar 2010
3Yatsuk A.A. (Faust_zp)1164251224051093121.91295.3557401 Feb 2010 02 Mar 2010
4Kreslavskij O.M. (Arcan)1164251224051097119.11495.8677431 Jan 2010 05 Mar 2010
5Drozdkov A.N. (anddros)5160140223946258.1708.4376611 Feb 2010 05 Mar 2010
6Sal'nikov S.A. ($erges)1160162223937245.9815.9106519 Feb 2010 05 Mar 2010
7Umrikhina E.V. (Umrikhina)11502512237248841.24447.6516125 Aug 2009 29 Sep 2009
8Dubinskij A.V. (_yizraor)81581552238864467.18431.9085704 Mar 2010 05 Mar 2010
9Senkevich S.V. (GreyC)11332512133761257.71826.2384107 Oct 2009 01 Jan 2010
10Seleznyov A.S. (Artyom S.)11302512132944447.22134.4924125 Jan 2009 03 Mar 2009
11Tarasov D.B. (Gavrila)101507622365164067.65435.6303721 Feb 2010 05 Mar 2010
12Nikotin V.M. (@Nikotin)1134251213344718.3353.7043511 Dec 2009 04 Mar 2010
13>Bojko D.M. (Angellore)1315114022361941805.616135.8273305 Mar 2010 05 Mar 2010
14Pechatnov V.V. (pvv)1142182134584536.96319.8883310 Feb 2010 04 Mar 2010
15Murashkin I.V. (lepton)112625121314113969.43958.7093205 May 2009 14 Dec 2009
16>Orlov M.V. (Eagleoff)151501622236168747.27420.5493005 Mar 2010 05 Mar 2010
17Shindin A.V. (AlShin)101421522134349826.251418.6303028 Feb 2010 02 Mar 2010
18Mullakhanov R.Kh. (rem)91362512233464914.59820.0412920 Jun 2009 02 Feb 2010
19Anisimov D. (danilko)13139160223323212.7009.5422812 Aug 2009 17 Aug 2009
20Lyubchenko V.A. (IAS56)6129251213161048419.368138.5802318 Jul 2009 04 Mar 2010

Best results for last week

Nosurnamen_selsel_allsel_scoresdml_scoresscoresratinglast_visit
1>Batyrshin R.N. (Ramses)3244672289113305 Mar 2010
2>Charyshkin O.Yu. (O.Charyshkin)2727632588153805 Mar 2010
3>Kotov A. (Ckpa6)31128800807905 Mar 2010
4Rusanovskij (March)1932382967137405 Mar 2010
5Polikarpov I.A. (Polik)2121471764235205 Mar 2010
6>Didenko T. (Tey)205027154277505 Mar 2010
7>Zheka (jek)242440040458705 Mar 2010
8Goncharov S. (MIK_URRI)5582937495403 Mar 2010
9Kozlikova D.A. (kozlik)153034034239704 Mar 2010
10>Tkachev A.B. (Alexandre)173633033204005 Mar 2010
11Mokrov E.G. (Laszlo Kovacs)6486232983601 Mar 2010
12tumbr (tumbr70)0202727590205 Mar 2010
13Fomchenkov V.N. (hamp)81232102117105 Mar 2010
14Krapchatova N. (Ninoko)8819019734802 Mar 2010
15Ivanov A. (ANDREAS_PITER)9671801847505 Mar 2010
16gg (bert988)6618018745902 Mar 2010
17>Mikhajlov A.N. (goodron)449716422205 Mar 2010
18>Gololobov (Victor)4286915138005 Mar 2010
19Dymchakov I. (Il'ya Dymchakov)5511415716503 Mar 2010
20>knyaginichev S. (knyagich)64312012162005 Mar 2010
21Smirnov D. (dmitry_smirnov)63012012256805 Mar 2010

Let's learn SQL

Notes about data types. CHAR and VARCHAR

by S.I.Moiseenko

Let us now see what happened with the definition of data. Below is a test script.

CREATE TABLE Test_char(
chr CHAR,
vchr VARCHAR
);

DELETE FROM Test_char;

INSERT INTO Test_char VALUES
('1','11111111112222222222333333333344444444445555555555');
INSERT INTO Test_char VALUES
('11111111112222222222333333333344444444445555555555', '1');
INSERT INTO Test_char VALUES
('2',CAST('111111111122222222223333333333' AS VARCHAR));
INSERT INTO Test_char VALUES
(CAST('111111111122222222223333333333' AS CHAR), '2');
INSERT INTO Test_char VALUES
('3', '3');

SELECT * FROM Test_char;
SQL Server 2008
chr	vchr  
3	3

So, only one row be inserted containing one character for each column. When you insert the remaining rows we get an error message:

String or binary data would be truncated.
The statement has been terminated.


which means that we should reduce the size of the string.

Although there is standard compliance, it seems to me that there is a contradiction that an explicit cast to the column type of the table does not work:

INSERT INTO Test_char VALUES
(CAST('111111111122222222223333333333' AS CHAR), '2');
PostgreSQL 8.3
chr	vchr  
1	11111111112222222222333333333344444444445555555555
2	111111111122222222223333333333
1	2
3	3

It may be noted the sequence in behavior: VARCHAR is arbitrary size; the second row was not inserted due to an error exceeding the size (ERROR: value too long for type character(1)); explicit value conversion to the column type of the table works, cutting off the extra characters from the right.

MySQL 5.0

VARCHAR type is not supported without specifying the size of the string. CHAR corresponds CHAR (1) - as standard. Since the explicit conversion to CHAR leaves the length of the string without changing, then into the table, defined as

CREATE TABLE Test_char(
chr CHAR,
vchr VARCHAR(1)
);

in the result, as in SQL Server, single row will be added:

chr	vchr 
3	3

Conclusions. In my humble opinion, none of these databases does not meet the standard behavior in those cases where the size of type is not specified. In my opinion, PostgreSQL is most consistent in the "particular implementation". In order to code portability, I would recommend that you always explicitly specify the size.

Useful links

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

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



Home
Week news
Profile
References
Rambler's Top100 KMindex Copyright SQL-EX © 2002-2010. All rights reserved.
contact