If the answer is logically right, it will always be regarded as right answer. There is a small possibility that logically wrong answer will be regarded as right. To figure out if the query is right two databases with identical structure and different data are used. If system tells you that your query is wrong but you can see matching results, that means your query gives the wrong answer on the second database. And still there is a possibility that logically incorrect query will return right resulting set. That possibility can be decreased by accurate data selecting or by using more than two databases to check the query. Second way is less attractive because it will take more time for response to users. Let us note, that once formulated query should always return correct result regardless of current database state. If you have reasoned doubts about the correctness of judgment of the query, please send your code to the developers of the system.

According to database schema there can be ships in Outcomes table witch are absent in Ships table. But if the name of that ship coincides with the name of a class in Classes table, that ship is a head ship in that class and we possess all information about it, excepting its launch year. If for each class launch time of its head ship is needed, the launch time field will contain NULL value in the result set, which is mean the absence of information. In the places, which may bring you to multiple-valued understanding of the task, it is said, that you should also consider the ships from Outcomes table. Additionally, the words "all ships from the database" also mean that ships from both Ships table and Outcomes table should be considered.

It is not recommended, while designing databases, to follow the example of Ships database, where Outcomes table may have hanging rows, i.e. the ships with unknown class. As a result it is impossible to provide referential integrity between Outcomes and Ships tables. Usage of this structure in training can be justified by ability to build more complex queries and usage of SELECT statement syntax at full. We should note, that tables of that database are quite normalized, i.e. they suit 3NF and even BCNF.

The system's answer does not depend on the sort order. Specificity of the old version of checking system did not suppose use of the Top clause in the main query. The new version of the program does not impose any restrictions on query syntax, besides what are imposed by used realization (SQL Server). However, use of Top N that restricts a result set is not recommended for two reasons. First, the Standard forbidding sort in subqueries does not support the given or similar clause. Second, the logic of solving the problem, based on sorting, can bring at change of data, especially at use without the clause WITH TIES.

The rating position is defined by a score of all solved exercises (with exception of third stage - see FAQ#7). In case the score coincides at several participants, above in a rating there will be the one who has spent less time for solving the problems. Thus time at the first stage is equal to interval from the first solved task up to the last. At the second stage, time spent for the decision of a problem is summarized only. In more detail about the second stage of testing, see FAQ#7. Now on a site there are 42 exercises of the first stage.

In exercise #26, it is needed to define the average price of the PCs and laptops produced by maker A.
Above query takes the sum price of both the PCs and laptops produced by maker A. Then that sum price is divided by the overall quantity of these PCs and laptops. It is all right from the mahtematical point of view, but is not from the point of view of SQL. If the reference database does not include any PC or laptop, the COUNT function returns 0 (which is the expected result), but the SUM function returns NULL-value. As a result, the sum price will be NULL, but will not be the sum price of other products, which is required to be.

1. Only the ones who have already done all exercises of first stage (up to #43) are admitted to the certification (second) testing phase. For these people the result that has been shown before second testing phase will be only considered in case of equality of their new results.
2. In the second phase, the exercises are given one by one in the same order for everyone. The number of the next exercise will be in exercises list. The period of time from the sending of the page with exercise up to it's solving will be fixed.
3. Rating in the second phase, with equal scores, depends on the sum of periods of solving the tasks (the less time it takes the better rating you have). This means that now rating does not depend on the absolute time: it is possible to do all of the exercises during one hour or during one year. And, of course, it allows developers to add new exercises, as they will be prepared.
4. One worst result of task's solution time is eliminated from the rating to compensate the losses of time caused by unforeseen circumstances.
5. The developers can substitute some exercises on the site, so the participants will have to solve the new exercise prior to advancing further.
Since the certificate is granted on the basis of the second stage, You are strongly recommended to adhere to following simple rules:
a) You should not publish the formulation of exercises and ask the help in their decision from the third parties.
b) In challenged cases You can ask the help by sending e-mail to developers. In the case of infringement of rule #1 we reserve the right to apply repressive punishments down to removal of registration record.
6. Third testing stage (optimization) starts with exercise #139. Now solution must not only be correct but also must not be executed longer than two times of author solution at least. To access to the third stage You should solve the exercise #138.
Conditions of the third stage:
- Time spent for solving exercises of the second stage is not regarded in a rating, i.e. competition is only conducted by results of the third stage.
- The participant who reaches the third stage will have higher rating position compared with all those participants who are at the second stage yet, irrespective of total of points.
- Exercises may be solved in any order and more than one exercise may be started to solve simultaneously. Naturally, for all begun exercises the time will be going.
- The participants of the third stage will not lose in a rating even if they will not solve all or any new problems of the previous stages. Points of the previous stages will be only considered in the case of equality of parameters of the third stage.

System's database stores the first and the last variants of solution of each task for each participant. Subsequent solutions are rewritten without changing the time of the initial solution. Therefore the main rating doesn't change.
However, time of last modification plays an important part in rating on effectiveness (see FAQ#19), that's why when solving repeatedly, the system asks if it should replace the solution. So it is recommended to replace Your solution only if it is no longer actual or if the new solution is more effective.

A typical error. In exercise #23, it is needed to find the maker producing at least two different computers (both PC and laptop) having speed not less than 750 MGz. Above query gives the result set which will also include a maker producing only PC (or only laptop) with required speed. This does not meet the exercise conditions.

You can discuss own solution along with solutions of other participants on the forum. Note that
1. You can do it only if you have solved an exercise already.
2. Forum is only available for the registered users.
3. Only author may show his own solution. We shall not do this without permissions from author.

The exercise asks to point out the battles in which at least three ships from the same country took part. A typical error is some duplicates of records in the resulting set. For example, if in a battle there were three ships from each country, the battle will appear mare than once in the resulting set without eliminating duplicates.

Neither on a rating position, nor on the fact of the solution of a problem it will not affect. However it is recommended to support the solutions in an "actual" condition that it was possible to take part in competition of effective solutions. We plan realize this competition in the near future.

Unexpected (not processable) error of the system. If the error repeats, we ask to inform developers.

The departure (arrival) time of a trip is written in a field having DATETIME data type. Because of this, when entering data in the format of time, the system supplements this value by a date accepted as origin point. Since we cannot guarantee the same date in these fields, when writing queries, you should not take into account any assumptions about this date. For example, exact departure date and time of a trip you can define with using the following structure:
dateadd(mi,datepart(hh,trip.time_out)*60+datepart(mi,trip.time_out),pass_in_trip.[date])

In this task you must format the date in accordance with correct answer given by the system. Dates do not include the time component.

Models which are produced in principle are in the Product table. We can say that the table PC contains only the PCs available now. Since the exercise asks us about models but not about PCs, only the Product table is to be analysed; as a result the missing models will be found.

This situation occurs when your query (correct, entirely possible) exceeds the timeout set on the server. When the "Without checking" check box is selected, a query is only executed against the main database, which is not near so big as the second (checking) database. As a result, query execution and page generation do not exceed the timeout.
What should you do? Rewrite the query to improve performance. Note that the test queries take not longer than 2-3 seconds.

Wrong number of columns (A,B) - Your result set contains A columns while the result set given by correct query contains B columns.
Wrong number of records (more|less by X) - Your query produced X records more or less than the correct query.
In SELECT exercises results "less" occur, for example, when giving too strict search conditions or not using all required sources of data. Cases of "more" - when giving insufficiently strict search conditions or taking into account the same information many times.
In DML exercises You should reason in same way, but keep in mind that when checking the results, system compares tables themselves after applying DML operator over them.
Data mismatch (X) - Your query produced record set that differs from one given by correct query in values of some fields (total X such fields). When data mismatch occures on main database these values are marked with red color.

It is obvious that each exercise can have many solutions, each of them will give the same correct result, but may vary in quality. Effective query is that executes faster, which takes less resources of DBMS (CPU, memory, disk operations). Query cost is a numeric evaluation of it's effectiveness, as DBMS measures. Therefore finding a query with least cost is the object of optimization.
We offer You to compete not only in speed of solutions but also in their quality. Theoretical footing of queries optimization is available in articles section.

Task 1 (SELECT) Task 2 (SELECT learn) Task 3 (SELECT) Task 7 (SELECT learn) Task 8 (SELECT) Task 10 (SELECT learn) Task 11 (SELECT) Task 14 (SELECT) Task 16 (SELECT learn) Task 17 (SELECT learn) Task 18 (SELECT learn) Task 23 (SELECT) Task 24 (SELECT)

Task 25 (SELECT) Task 26 (SELECT) Task 27 (SELECT) Task 28 (SELECT) Task 33 (SELECT) Task 35 (SELECT) Task 37 (SELECT) Task 38 (SELECT learn) Task 38 (SELECT) Task 39 (SELECT) Task 40 (SELECT) Task 41 (SELECT)

Task 42 (SELECT) Task 43 (SELECT learn) Task 50 (SELECT learn) Task 51 (SELECT learn) Task 53 (SELECT learn) Task 54 (SELECT learn) Task 55 (SELECT learn) Task 56 (SELECT learn) Task 57 (SELECT learn) Task 59 (SELECT learn) Task 60 (SELECT learn) DML exercises

Generation of numerical sequence
Numbering of the query rows
The least-square method. Used in solution of an exercise.
Grouping of time intervals
MS SQL 2005: window functions

1. It helps to outrun your competitors for getting a job with other things being equal.
2. It gives you an opportunity to publish your resume at this site which will be accessible to potential employers and employment agencies.
3. It gives an opportunity to publish an URL of your personal site or your firm's site.
4. It helps to develope this site that, in turn, raises your popularity in Internet.
5. A distinguishing feature of our certificate is confirmation of practical skills rather than successful passing the test. In our opinion, knowledge of language syntax and skill by means of this language to solve a problem is far not the same.

On the certificate confirmation page one of two symbols - "+" or "-" appears near to a photo.
Plus (+) means that the certificate is up to date, but the minus (-) means that this one is out of date. Confirmation will be given in any case. Certificate may become out of date as a result of replacement of exercises at certification stages or in the case of changing a certification threshold.
Thus we recognize that the maintaining the qualification at high level demands the constant practice. For confirmation of the qualification it will be necessary for professional to solve the replaced or added exercises. It will not demand a lot of time (new problems at certification stages appear infrequently and in a small number) and absolutely no money.
We always inform about new exercises in newsletters and at a forum of a site.

CTE - common tabular expressions - have appeared only in SQL Server 2005. We have used SQL Server 2000 within several years, so this restriction was introduced for maintenance of equal conditions with the participants of rating system of those years. Nevertheless, CTE are allowed to use with the mark "without checking " or when the exercise has been solved. Moreover participants of the third rating stage are able to use them for the solution of any exercises on this site.