|SQL exercises||May 29, 14:30 MSK|
1. Is it possible, that the right query will be regarded as wrong and vice versa?
2. Why is there Bismarck ship, witch is absent in Ships table, in resulting data sets of some queries concerning class ships?
3. Why do you use poorly structured Ships database to do these exercises?
4. Does the system's answer depend on the sort order? Why ORDER BY does not work when running a solution?
5. How do you define my place in the list of best results (first stage)?
5'. Why do I disappear from rating in the learning stage, or arise at the bottom of it?
5". How can I reset my results to begin at the beginning?
6. Who takes part in the certification (second) testing phase?
7. When solving previously solved SELECT exercise, a button "Replace" appears. What is it? Can this affect my rating?
8. Can I see other solutions to an exercise?
9. What will be if the solution accepted by system will appear wrong and will cease to pass at addition of data?
10. What is the cause of the message "SYSTEM ERROR..."?
11. What this may mean, for example, about departure time in the Trip table: "1900-01-01 14:30:00.000"?
12. What is the cause of the situation where only the one-half exercises page is displayed when the "Run" button have been pressed?
13. What do the results "Data mismatch", "Wrong number of records" etc. mean when checking the query?
14. What is "cost", "effectiveness", "execution plan", "optimization"; what is it for?
15. If you have not found answers to the questions related with solving the exercises, look the list of tasks, which were discussed in "The site news" newsletters (in Russian) and are available at sqlbooks.ru (in English also).
16. If you have any principal problems in constructing a query, give your attention to the themes that have been considered in "The site news" newsletters (in Russian) and at sqlbooks.ru (in English also).
17. What does the certificate give to me?
18. What "+" and "-" do symbols on the certificate confirmation page mean?
19. Why I cannot use CTE for solving some of the exercises on the site?
20. To what stage belong the exercises with negative numbers, and whether do I need to solve them?
21. What does the message "This task has hints, but these are not accessible as yet..." mean (red hint icon), and what should I do to make these hints accessible?
22. Can I get the second (checking) DB for testing my queries off-line?
23. What exercise categories are there on the site, and what is the difference between them?
24. Can I submit my own exercise for publication on the site?
1. Is it possible, that the right query will be regarded as wrong and vice versa? ↑
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.
2. Why is there Bismarck ship, witch is absent in Ships table, in resulting data sets of some queries concerning class ships? ↑
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.
3. Why do you use poorly structured Ships database to do these exercises? ↑
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.
4. Does the system's answer depend on the sort order? Why ORDER BY does not work when running a solution? ↑
The system's answer does not depend on the sort order. ORDER BY in your query will work when the checkbox "Without checking" is checked. Otherwise the system sorting over all the columns left to right is used.
5. How do you define my place in the list of best results (first stage)? ↑
The rating position is defined by a score of all solved exercises (with exception of third stage - see FAQ#6). 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#6. Now on a site there are 9 exercises of the first stage.
5'. Why do I disappear from rating in the learning stage, or arise at the bottom of it? ↑
You can participate only in one rating. If you'll solve a few rating-stage exercises, you pass on to the main rating and can't participate in the learning-stage rating any more (but all your solutions are retained in the system). In this case, if you return to the learning stage, the points for solved exercises there will grow from zero, and will be reset when recalculating rating weekly.
5". How can I reset my results to begin at the beginning? ↑
You haven't this opportunity. This is due to our trying to reach objective rating and make adequate estimate of effectiveness of teaching.
6. Who takes part in the certification (second) testing phase? ↑
1. Only the ones who have already done all exercises of first stage (up to #10) 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.
7. When solving previously solved SELECT exercise, a button "Replace" appears. What is it? Can this affect my rating? ↑
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.
8. Can I see other solutions to an exercise? ↑
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.
9. What will be if the solution accepted by system will appear wrong and will cease to pass at addition of data? ↑
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.
10. What is the cause of the message "SYSTEM ERROR..."? ↑
Unexpected (not processable) error of the system. If the error repeats, we ask to inform developers.
11. What this may mean, for example, about departure time in the Trip table: "1900-01-01 14:30:00.000"? ↑
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:
12. What is the cause of the situation where only the one-half exercises page is displayed when the "Run" button have been pressed? ↑
Alternatively, the exercises page works correctly when the "Without checking" check box is selected.
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.
13. What do the results "Data mismatch", "Wrong number of records" etc. mean when checking the query? ↑
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.
14. What is "cost", "effectiveness", "execution plan", "optimization"; what is it for? ↑
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
15. If you have not found answers to the questions related with solving the exercises, look the list of tasks, which were discussed in "The site news" newsletters (in Russian and English) ↑
16. If you have any principal problems in constructing a query, give your attention to the themes that have been considered in "The site news" newsletters (in Russian). ↑
17. What does the certificate give to me? ↑
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.
18. What "+" and "-" do symbols on the certificate confirmation page mean? ↑
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.
19. Why I cannot use CTE for solving some the exercises on the site? ↑
CTE - common tabular expressions - have appeared in SQL Server since 2005 version. Now the restriction on usage of CTE is introduced with the aim that the some exercises adopt set-based solutions which are more effective than procedural solutions using recursive CTE. As the site pursues the learn purposes in the first place, we "force" you to look at set-based solutions . Nevertheless, CTE are allowed to use with the option "without checking " or when the exercise has been solved by another way.
20. To what stage belong the exercises with negative numbers, and whether do I need to solve them? ↑
Formally exercises with negative numbers are included into the first stage, but solution of them is not necessarily (and it is not recommended to beginners at all) for passing on to the second stage and higher. These exercises do not influence certification also. Though points for these exercises are considered in the general rating.
In this - negative - section, there are exercises which do not fit for other stages for some reasons, for example, exercises with ambiguous treatment of a formulation.
21. What does the message "This task has hints, but these are not accessible as yet..." mean (red hint icon), and what should I do to make these hints accessible? ↑
Hints will be accessible (green hint icon) if, first, a query gives correct result on the available (open) DB and, second, a certain time must elapse from the beginning of solution. This time is individual for an exercise and is intended to give you opportunity to solve an exercise without hints.
Note that when time is elapsed the hints do not appear automatically, you should run the query anyway.
22. Can I get the second (checking) DB for testing my queries off-line? ↑
The goal of the checking DB is to defend solutions from fitting to data, so we aren't sharing this database.
Moreover we believe that the ability of data-modelling for testing own queries (in head or via local computer) is useful skills which you could obtain when solving exercises at the site.
23. What exercise categories are there on the site, and what is the difference between them? ↑
Learning stage. Learning stage ranking points are assigned that don't affect the main ranking list. The solution time (the period from the opening of the exercise till the moment the system accepts the user's solution as correct) is recorded for each exercise. Note that, since this stage allows opening several exercises simultaneously, the total solution time may exceed the actual time the user spent solving them.
First stage (exercises 1-9). Solution time is not recorded, main ranking points are assigned (see Score column). This stage will finally be eliminated. One has to solve all exercises of this stage to move on to the second stage. The exercises can be solved in any order.
Exercises with negative numbers. Those are optional exercises users don't need to solve to get certificates and to move on to more advanced ranking stages. However, points assigned for solving them are added to the user's score.
DML. Those are data manipulation exercises. Solution time is recorded but doesn't affect the user's rank (just the points do).
Second stage (exercises 9-138). Solution time is recorded; therefore, the user has to open the exercises one at a time, with the next exercise becoming available after the previous one has been solved.
Third stage (optimization training, exercises 139-250). Solution time is recorded, and the exercise is deemed to be solved if the solution passes all checks, and its CPU execution time doesn't exceed the execution time of the test query more than twice. Points are added up in the Score_3 column, and the solution time is calculated into the Days_3 column.
Puzzles (exercises 251-300). Time control is established similarly to stage 3, with the difference that only the assigned points affect the user's rank, not the solution time.
Exercises with 301+ numbers. This category is intended for discussion of new exercises and their subsequent transfer to other stages (except for the third one).
24. Can I submit my own exercise for publication on the site? ↑
Any registered user may submit an exercise. Please note, however, that, since similar exercises are to be avoided, it is advisable for the future author to solve most of the exercises already existing. Guide to Exercise Authors.