SQL. Problems and Solutions
Appendixes
Appendix 2. Exercises list

Appendix 2. Exercises list

Only the exercises considered in the book are listed here. On a site SQL-EX.RU, besides them, there are many other exercises, and new ones are being added on a regular basis.

No.DBLevelTaskStageT&S
1A11Find the model number, speed and hard drive capacity for all the PCs with prices below $500.  Result set: model, speed, hd.Learn
2A11Find printer makers.  Result set: maker.Learn+
3A11Find the model number, RAM and screen size of the laptops with prices over $1000.Learn
5A11Find the model number, speed and hard drive capacity of the PCs having 12x CD and prices less than $600 or having 24x CD and prices less than $600.Learn
6A12Point out the maker and speed of the laptops having hard drive capacity more or equal to 10 Gb.Learn+
7A12Find out the models and prices for all the products (of any type) produced by  maker B.Learn+
8A12Find out the makers that sale PCs but not laptops.Learn+
10A11Find the printers having the highest price. Result set: model, price.Learn+
11A11Find out the average speed of PCs.Learn+
13A11Find out the average speed of the PCs produced by maker A.Learn
15A12Find the hard drive sizes that are equal among two or more PCs.   Result set: hd.Learn+
16A12Find the pairs of PC models having similar speeds and RAM. As a result, each resulting pair is shown only once, i.e. (i, j) but not (j, i).   Result set: model with higher number, model with lower number, speed, and RAM.Learn+
17A12Find the laptop models having speeds less than all PCs. Result set: type, model, speed.Learn+
18A12Find the makers of the cheapest color printers.  Result set: maker, price.Learn+
20A12Find the makers producing at least three distinct models of PCs.  Result set: maker, number of models.Learn
No.DBLevelTaskStageT&S
23A13Find the makers producing at least both a pc having speed not less than 750 MHz and a laptop having speed not less than 750 MHz.   Result set: MakerLearn+
24A13Find the model number of the product (PC, laptop, or printer) with the highest price.  Result set: model.Learn
25A13Find the printer makers which also produce PCs with the lowest RAM and the highest-speed processor among PCs with the lowest RAM.  Result set: maker.Learn
26A13Define the average price of the PCs and laptops produced by maker A.  Result set: single total price.Learn+
27A13Define the average size of the PC hard drive for each maker that also produces printers.  Result set: maker, average capacity of HD.Learn+
71A11Find all the PC makers who have all their models of PC type in the PC table.Learn
30A23Under the assumption that the income (inc) and expenses (out) of the money at each outlet (point) are registered any number of times a day, get a result set with fields: outlet, date, expense, income.  Note that a single record must correspond to each outlet at each date.   Use Income and Outcome tables.Learn+
59A23Calculate the remainder  at each outlet for the database with accounts drawn not more than once a day.  Result set: point, remainder.Learn+
60A21Calculate the remainder at each outlet prior to 4/15/2001 for the database with accounts for not more than once a day.    Result set: point, remainder.Learn+
32A33One of characteristics of a ship is one-half cube of calibre of its main guns (mw). Within 2 decimal places, define the average value of mw for the ships of each country which has ships in database.Learn
37A32Find the classes including only one ship in the database (taking into account Outcomes table).Learn+
38A32Find the countries having classes of both ordinary ships and cruisers.Learn
39A33Define the ships that “survived for future battles”; being damaged in one battle, they took part in another.Learn+
46A33Point out the names, displacements and number of guns of ships participating  in the battle at Guadalcanal.Learn+
No.DBLevelTaskStageT&S
51A33Find the names of the ships having the largest amount of guns among all the ships with the same displacement (taking into account Outcomes table).Learn+
53A31To within two decimal digits, define the average amount of guns for the battleship classes.Learn+
54A32To within two decimal digits, define the average amount of  guns for all the battleships (taking into account Outcomes table).Learn+
55A31For each class, define the year in which the first ship of this class was launched.    If year of launch of the head ship is unknown, define the minimum launch year for ships of this class.  Result set: class, year.Learn+
56A33For each class, define the number of ships of this class that were sunk in battles.    Result set: class, number of sunked ships.Learn+
57A33For each class, consisting of at least three ships, define the number of ships (that must be at least 1) of this class sunk in battles.   Result set: class, number of sunken ships.Learn+
70A33Point out the battles in which at least three ships from the same country took part.Learn+
77A42Find the dates where number of trips from town Rostov were the maximum. Result set: number of trips, date.Learn-
93A42For each company, find time the company’s planes have spent during accomplished flights. Result set: company name, time in minutes.Learn-
11A42Among the passengers, who flew by the planes of more then one company, find those who made the same number of trips by the planes of each of these companies. Result set: passenger name.Rating-
(-2)A32For each country, find the year, in which the maximal number of ships had been launched.  In the case of more than one year in question, take a minimal year. Result set: country, number of ships, yearRating+
17A31Define the names of all the ships in the database which were definitely launched before 1941.Rating+

#Sql-Ex.ru#Computer Firm#Exercises#Ships#Recycled Materials