SQL exercises Language  July 31, 21:35 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

#305 (2010-07-31)

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.
§
Searching and bookmarks service have appeared in the site forum.

§
Two new exercises - #166 (anddros, complexity 1 point), #167 (qwrqwr, 2 points) are added to the third stage.
§
Has corrected a bug with checking the exercises:
82 (rating stage) - hint from author (_Virt_).
§
Changes among leaders (solved exercises of third stage):
1. $erges (166)
2. vlksm (166)
3. Arcan (166)
4. Ozzy (164)
6. anddros (166)
8. Angellore (166)
11. Eagleoff (152, 166)
12. Gavrila (166)
22. qwrqwr (156, 166, 167)
26. Valdaj (166)
§
Have completed the 2nd-stage testing:
71. Rybka (tasks 137, time 81.114)

§
Have advanced in rating:
72. Ramses (133, 19.727)
73. Vladius (129, 31.405)
84. Fencer (123, 483.612)
91. Magnetic (121, 635.623)
§
Applicants for Top 100 have advanced in rating:
113. Gendalf (109, 167.694)
115. IrinkaR (112, 16.888)
118. Cergej L (109, 289.030)
§
Have been certified last week:
polishchuk (A10055348) [BK] - г. Москва, Россия
Easy-Human (A10070369) [BK] - г. Оренбург, Россия
§
Statistics:
Number of subscribers - 3825
Number of rating's participants - 9607
Number of second-stage's participants - 2162
Number of third-stage's participants - 70
On the learning stage - 16523
Certified specialists in total - 451

ТОР 20

NoPersonNumber of
Sel_ex
Last_SelNumber of
DML_ex
ScoresDaysDays_2Days_3S_3LastSolvedLastVisit
1Sal'nikov S.A. ($erges)1168166224038828.5137.876802010-07-272010-07-30
2Karasyova N.V. (vlksm)1168166224031543132.55595.429802010-07-272010-07-30
3Kreslavskij O.M. (Arcan)1168166224031274128.472102.186802010-07-272010-07-30
4Zotov P.G. (Ozzy)116616422400868214.147217.219772010-07-262010-07-30
5Yatsuk A.A. (Faust_zp)1153252223751093121.91292.200722010-02-012010-05-22
6Drozdkov A.N. (anddros)5164166223927908.6858.274692010-07-262010-07-30
7Umrikhina E.V. (Umrikhina)11392522234248841.24447.336592009-08-252010-07-20
8>Bojko D.M. (Angellore)8160166223781088924.474212.056552010-07-302010-07-30
9>Dubinskij A.V. (_yizraor)8155672237179267.59928.051552010-07-302010-07-30
10Derzhal'tsev V.A. (MadVet)715415522363192393.87758.836552010-07-222010-07-30
11Orlov M.V. (Eagleoff)111561662236883264.58230.139452010-07-282010-07-30
12Tarasov D.B. (Gavrila)915516622365179778.46037.300442010-07-282010-07-30
13Senkevich S.V. (GreyC)11222512130761257.71826.238412009-10-072010-01-01
14Seleznyov A.S. (Artyom S.)11192512129944447.22134.492412009-01-252009-03-03
15Nikotin V.M. (@Nikotin)1123251213044718.3353.704352009-12-112010-07-30
16Murashkin I.V. (lepton)111525121284113969.43958.709322009-05-052010-04-18
17Pechatnov V.V. (pvv)11312522131584536.96317.410312010-02-102010-07-24
18Shindin A.V. (AlShin)101312512131349826.251418.630302010-02-282010-03-09
19Mullakhanov R.Kh. (rem)91252512230464914.59820.041292009-06-202010-02-02
20Anisimov D. (danilko)13128252223023212.7009.283262009-08-122009-08-17

Best results for last week

Nosurnamen_selsel_allsel_scoresdml_scoresscoresratinglast_visit
1Lebedev S.Yu. (Klukvin)264148348276630 Jul 2010
2>Leschev A.O. (ArtIMen)2323443478125629 Jul 2010
3tester (tester_psevdonim)2131412263123129 Jul 2010
4Ivanishyna (Tanya86)2535381755125730 Jul 2010
5>bsh L. (O.o)134020345478130 Jul 2010
6>Bagirov M.N. (Murik)1515322254190630 Jul 2010
7Lezvinskii A. (DICE)232845045108929 Jul 2010
8NeSkazhu O.A. (olga2206)2243438270029 Jul 2010
9Naumkin M.Yu. (MikhailYu)5607293656730 Jul 2010
10>Sheringa A.A. (degluker)173036036183530 Jul 2010
11>Zolotarev A.N. (Musette)143530030166630 Jul 2010
12>Smirnov M. (sms_311)6681725392630 Jul 2010
13>Mats V.V. (Easy-Human)11762402434530 Jul 2010
14Anonim (WereWolf777)16662402444427 Jul 2010
15Ovsyankin (Kazaam)10812302332428 Jul 2010
16Krasovskij E.A. (Pegoopik)2241721470930 Jul 2010
17Asadullin K. (Invisible44)002121482430 Jul 2010
18Bykov (Nor)001919521030 Jul 2010
19>Gukasyan A. (Artur)11761701734430 Jul 2010
20>Frolov (simple09)121617017412930 Jul 2010

Let's learn SQL

FLOAT

by S.I.Moiseenko

Once in a social network was asked how to remove trailing zeros in decimal numbers. This was associated with the preparation of a report, which sums concatenate with the text. In the conditions of the problem was stated limit to two decimal places.

Here's an example of data and the desired result:

given	to obtain
0.00	0
10.00	10
2.50	2.5
100.00	100
11.33	11.33

Solutions have been proposed, based on analysis of the line. I also chose the wrong way and proposed the following solution:

SELECT num,
CASE WHEN CAST(num AS INT) = num
           THEN CAST(CAST(num AS INT) AS VARCHAR)
           WHEN CAST(num*10 AS INT) = num*10
           THEN LEFT(CAST(num AS VARCHAR), LEN(CAST(num AS VARCHAR)) - 1)
            WHEN CAST(num*100 AS INT)=num*100
            THEN CAST(num AS VARCHAR)
END fnum
FROM(
SELECT 0.00 as num
UNION ALL SELECT 10.00
UNION ALL SELECT 2.50
UNION ALL SELECT 100
UNION ALL SELECT 11.33
) X

I do not know how much it would still be continued, if one member did not notice that all problems are solved by conversion to the data type FLOAT. Really:

SELECT num, CAST(num AS FLOAT) fnum
FROM(
SELECT 0.00 as num
UNION ALL SELECT 10.00
UNION ALL SELECT 2.50
UNION ALL SELECT 100
UNION ALL SELECT 11.33
) X

However, you need to remember the approximate nature of this type, namely the magnitude of the mantissa in scientific representation of the number.

In accordance with the standard in this type of data specified argument - FLOAT (n), which can take values from 1 to 53. The SQL Server, an argument value in the range 1 - 24, interprets it as 24, which corresponds to the accuracy of 7 digits, and in the range 25 - 53 as 53, which corresponds to the accuracy of 15 digits. The default is 53.

The following example illustrates the above:

SELECT num,
CAST(num AS FLOAT(24)) num_24,
CAST(num AS FLOAT(53)) num_53
FROM(
SELECT 1234567.80 AS num
UNION ALL SELECT  12345678.90
UNION ALL SELECT 123456789012345.60
UNION ALL SELECT 1234567890123456.70
) x

num 			num_24  		num_53
1234567.80		1234568		1234567,8
12345678.90		1,234568E+07	12345678,9
123456789012345.60		1,234568E+14	123456789012346
1234567890123456.70	1,234568E+15	1,23456789012346E+15

MySQL (version 5.0)

Does not support the conversion to type FLOAT.

PostgreSQL (version 8.3.6)

Almost similar behavior, except that for the parameter in the range 1 - 24 precision is 6 digits. Accordingly, recent results will look like this:

num 			num_24  		num_53 
1234567.80		1.23457e+006	1234567.8
12345678.90		1.23457e+007	12345678.9
123456789012345.60		1.23457e+014	123456789012346
1234567890123456.70	1.23457e+015	1.23456789012346e+015

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