Common mistakes in solving SELECT statement exercises

Let`s note some moments which we should consider in analyzing scheme at picture 3.1. The Outcomes table have the composite primary key {ship, battle}. This restriction denies entering the same ship which was in action in the same battle more then one time in database. But the same ship may be present in this table more then once if it was in different actions. The class of ship is defined in the Ships table. This table has foreign key (`class` column) to Classes table.

The peculiarity of this scheme is in the fact that the Outcomes and Ships tables have no connection between each other. In other words, the Outcomes table may have ships which are absent in the Ships table. Here is the reason of most mistakes in solutions. It seemes that ships from Outcomes table have unknown class, therefore the characteristics are also unknown. That`s not so. As it follows from the description of knowledge domain, the name of lead ship is the same as name of class of such ships. That`s why if the name of ship in the Outcomes table matches the class name in the Classes table, so this is the lead ship and its characteristics are certain.

The way of improving this scheme is obviously to everyone: is`s need to connect the Ships and the Outcomes table by ship name, in this case the `ship` column in the Outcomes table becomes a foreign key to Ships table. Undoubtedly, it is so, but in the real case all information might be not available. For instance, there is archive information about ships in action, but there are no data about classes of this ships. In this case we need to enter this ship in Ships table first, and the `class` column must to allow to enter NULL value.

From the other side, there is no obstacles for us to enter the lead ship from Outcomes table to the Ships table, moreover. It is true, because the year of launch is not obligatory for entering. In this reason, we should note that the administrator of DB and the application developer are the different peoples as a rule. The developer and his users doesn`t have permissions to modify data in all cases.

The bad structure isn`t mean the impossibility to extract reliable data. The evidence is in this tasks. Such "bad" structure is more useful for educational purposes then "good", because it makes us to write more complex queries. The authors of this scheme follows this reasons seemingly [2]. Besides, queries for "bad" scheme will get true results (but becomes less efficient) even if we improve structure , in the case we set connection between the Ships and the Outcomes tables.

Finally, we should note that `launched` column in the Ships table allow to enter NULL value. It means that launch year may be unknown. This is also true for ships from Outcomes table which is absent in Ships table.

Well, let`s move to the task`s solving. We note, that in this chapter we do not consider very simple tasks anymore, because the majority of typical solutions were considered previously.

Content:

(3.7.) Exercise #70
(3.2.) Exercise #37
(3.3.) Exercise #38
(3.4.) Exercise #39
(3.5.) Exercise #151
(3.6.) Exercise #(-2)
(3.1.) Exercise #46
(3.8.) Exercise #51
(3.9.) Exercise #53
(3.10.) Exercise #54
(3.11.) Exercise #55
(3.12.) Exercise #56
(3.13.) Exercise #57
(3.14.) Exercise #32
(3.15) Exercise 78