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 and replacements:
23 --> learn stage under number of 82
188 --> 23
188 - new exercise from qwrqwr (3 points)
189 - new exercise from Baser (4 points)
190 - new exercise from qwrqwr (1 point)
Glena4 (A12106024) [BK] - g. Lesnoj Gorodok, MO, Rossiya Yury Egorov (B12092980) [AR] - g. Moskva, Rossiya
§
Statistics:
Number of subscribers - 3713
Number of rating's participants - 7048
Number of second-stage's participants - 2339
Number of third-stage's participants - 85
On the learning stage - 45451
Certified specialists in total - 546
In the MySQL dialect, each date component can be extracted from a date value with the correspondent function. Suppose we must query the Airport database to get minutes of department time of the flights that departs between 1 and 2 p.m.
Solution 1
select time_out, MINUTE(time_out)
from trip
where HOUR(time_out) = 12;
time_out min
1900-01-01 12:35:00 35
1900-01-01 12:00:00 0
Time components can also be got with the EXTRACT function. In this case the solution can be written in the following form:
Solution 2
select time_out, EXTRACT(MINUTE FROM time_out) AS min
from trip
where EXTRACT(HOUR FROM time_out) = 12;
The EXTRACT function can also return composite components of date and/or time, for example, year and month. Let's solve the following exercise:
Query the Paintings database to count the number of paintings in months of each year.
Solution 3
select YEAR(b_datetime) AS y, MONTH(b_datetime) AS m, EXTRACT(YEAR_MONTH FROM b_datetime) AS ym, count(*) AS qty
from utb
group by EXTRACT(YEAR_MONTH FROM b_datetime);
y m ym qty
2000 1 200001 1
2001 1 200101 1
2002 1 200201 1
2002 6 200206 1
2003 1 200301 69
2003 2 200302 7
2003 3 200303 2
2003 4 200304 2
2003 5 200305 2
2003 6 200306 2
The full list of possible components can be found, for example, here.
Remark
It is appropriate to mention here rather free style of MySQL grouping. Colu9mns of a SELECT clause are allowed not to be present in the GROUP BY column list. It is obvious that an implicit aggregate function is supposed to be there, otherwise it would be impossible to interpret the statement unambiguously. I suppose that MIN or MAX functions are used implicitly but I'm not going to make it out here, because this feature contradicts SQL standard.
PostgreSQL
As far as I know, there are no functions like YEAR, MONTH, etc. in PostgreSQL. Still the EXTRACT function exists, and the second solution variant that we've written for MySQL is valid for this DBMS too.
§
We invite you to visit the Interactive SQL textbook.
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 2008 R2 Express, MySQL 5, PostgreSQL 9.