Common mistakes in solving SELECT statement exercises

The database schema consists of four tables (see Figure 1.1):

• Product(maker, model, type)
• PC(code, model, speed, ram, hd, cd, price)
• Laptop(code, model, speed, ram, hd, screen, price)
• Printer(code, model, color, type, price)

The Product table contains data on the maker, model number, and type of product ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all makers and product types. Each personal computer in the PC table is unambiguously identified by a unique code, and is additionally characterized by its model (foreign key referring to the Product table), processor speed (in MHz) – speed field, RAM capacity (in Mb) - ram, hard disk drive capacity (in Gb) – hd, CD-ROM speed (e.g, '4x') - cd, and its price. The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen. For each printer model in the Printer table, its output type (‘y’ for color and ‘n’ for monochrome) – color field, printing technology ('Laser', 'Jet', or 'Matrix') – type, and price are specified.

Fig. 1.1. Computer firm database schema

Additional information can be obtained from the logical database schema shown in Fig. 1.1. Each table containing data on products of a specific type (PCs, laptops, or printers) has a foreign key (model) referencing the Product table. The one-to-many relationship means that a model present in the Product table may be missing in the other tables. On the other hand, a model with the same number, even with identical technical characteristics may occur in these tables several times, since the code column represents the primary key here. The latter fact calls for additional explanation, since different people interpret the term model in different ways. Within the framework of this schema, a model is considered to comprise products with a uniform manufacturer and production technology. E.g., models having the same number may be equipped with storage devices that are technically identical but have different capacities, i.e., 60 and 80 GB. Among other things, this means that, say, two personal computers having identical model numbers yet different prices may be present in the PC table.

In terms of the subject area this schema may imply that the Product table holds information about all known suppliers of good types under consideration and models supplied by them, while the other tables contain models currently available (offered for sale). Therefore, it is quite possible there is a maker producing models none of which is in stock at the moment.

Content:

(1.1) Exercise #1
(1.2) Exercise #2
(1.3) Exercise #3
(1.4) Exercise #5
(1.5) Exercise #6
(1.6) Exercise #7
(1.7) Exercise #8
(1.8) Exercise #10
(1.8) Exercise #11
(1.8) Exercise 71
(1.10) Exercise #13
(1.21) Exercise 3 (rating)
(1.11) Exercise #15
(1.12) Exercise #16
(1.13) Exercise #17
(1.14) Exercise #18
(1.15) Exercise #20
(1.16) Exercise #23
(1.17) Exercise #24
(1.18) Exercise #25
(1.19) Exercise #26
(1.20) Exercise #27
(1.21) Exercise #28