Appendix 1. About educational databases

All exercises are performed on the databases described below.

1. Computer firm

The database scheme consists of four tables:
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 table "Product" includes information about the maker, model number, and type ('PC', 'Laptop', or 'Printer'). It is assumed that model numbers in the Product table are unique for all the makers and product types. Each PC uniquely specifying by a code in the table "PC" is characterized by model (foreign key referencing to Product table), speed (of the processor in MHz), total amount of RAM - ram (in Mb), hard disk drive capacity - hd (in Gb), CD ROM speed - cd (for example, '4x'), and the price. The table "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size - screen (in inches). For each printer in the table "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

2. Recycling firm

The firm has a few outlets that receive items for recycling. Each of the outlets receives funds to be paid to deliverers. Information on received funds is registered in a table:
Income_o(point, date, inc)
The primary key is (point, date), thus receiption of money (inc) takes place not more than once a day (date column does not include time component of the date). Information on payments to deliverers is registered in the table:
Outcome_o(point, date, out)
In this table the primary key (point, date) also ensures bookkeeping of the funds distribution at each point not more than once a day.
In case incomes and expenses may occur more than once a day, another database schema is used. Corresponding tables include code column as primary key:
Income(code, point, date, inc)
Outcome(code, point, date, out)
In this schema date column does not also include the day time.

3. Ships

The database of naval ships that took part in World War II is under consideration. The database has the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.
The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the Outcomes relation. Note: the Outcomes relation may include the ships not included in the Ships relation.

4. Airport

Database schema consists of 4 tables:
Company(ID_comp, name)
Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
Passenger(ID_psg, name)
Pass_in_trip(trip_no, date, ID_psg, place)
Company table has ID and name of the company, which transports passengers. Trip table has information about trips: trip number, company ID, plane type, departure city, arrival city, departure time, and arrival time. The passenger table has passenger's ID and passenger's name. Pass_in_trip table has information about the flights: trip number, departure date (day), passenger's ID and his place during the flight. We should note that,
- Any trip is being accomplished every day; duration of a flight is less than a calendar-day (24 hours);
- Time and date are considered comparatively one time zone;
- The departure time and the arrival time are given to within a minute;
- There can be the passengers bearing the same names (for example, Bruce Willis);
- The place during the flight is a number followed by a letter; the number defines the row number, the letter (a - d) – the place in the row (from the left to the right) in the alphabetical order;
- Relationships and restrictions are shown in the data schema.

5. Painting

Database schema consists of 3 tables:
utQ (Q_ID int,Q_NAME varchar(35))
utV (V_ID int,V_NAME varchar(35),V_COLOR char(1))
utB (B_Q_ID int,B_V_ID int,B_VOL tinyint, B_DATETIME datetime)
The table utQ includes square identifier, square name. Note that non-painted square is black.
The table utV includes balloon identifier, balloon name, and paint color.
The table utB shows information on painting square with paint balloon and includes the square identifier, the balloon identifier, the paint quantity, and time of painting.
It should be noted that
- balloon may be of one from three colors: red (V_COLOR='R'), green (V_COLOR='G'), or blue (V_COLOR='B');
- any balloon is originally full with volume of 255;
- square color is defined in accordance with RGB rule, i.e. R=0, G=0, B=0 is black, whereas R=255, G=255, B=255 is white;
- any record in the table utB decreases paint quantity in the balloon by B_VOL and increase paint quantity in the square by the same value;
- B_VOL must be more than 0 and less or equal 255;
- paint quantity of the same color in one square may not be over 255;
- paint quantity in a balloon may not be less than 0

Appendix 2. The list of exercises (SELECT)

Below is the list of all the first-phase exercises with the number of each exercise stated in the tasks, the database number for these exercises, and the complexity level.

Номер	База	Уровень	Упражнение
1	3	3	Point out the battles in which at least three ships from the same country took part.
2	3	1	In the Outcomes table, transform names of the ships containing more than one space, as follows: replace all characters between the first and the last spaces (excluding these spaces) by symbols of an asterisk (*). The number of asterisks must be equal to number of replaced characters. Result set: the name of a ship, the transformed name of the ship
3	2	2	Find inlets with greater total payout in different pairs of inlets with equal inlet No's from outcome and outcome_o tables considering only those dates when waste was collected at least at one of the inlets involved. Result set: Inlet #, date, text: - "once a day", if daily reporting inlet is in the lead; - "more than once a day", if several times a day reporting inlet is in the lead; - "both", for draw cases.
4	1	1	Calculate the sum of digits in each model's ID (model column) from Product table. Result set: model, sum of digits
5	3	1	For each ship from Ships table, determine the name of the time-nearest battle from Battles table, in which the ship could participate after year of launch . If year of launch is unknown, take the latest battle. If there is no battle which have occured after the ship was launched, deduce NULL instead of the name of battle. Remark: Consider that the ship can participate in battle which has occured in the year of launch of the ship. Result set: name of the ship, year of launch, name of battle
6	3	1	For each country, determine battles in which the ships of the given country did not participate. Result set: country, battle
7	4	2	For each company, find the number of passengers (if any) that have been transported in April 2003 by ten-day periods. Result set: Company name, number of passengers by ten-day periods
8	3	1	For each battle find out the first and the last day of the month when the battle took place. Result set: tne battle name, the first day of month, the last day of month. Note: dates must be given in the format "yyyy-mm-dd".
9	4	2	Among the clients which only use a single company, find the different passengers who have flown more often than others. Result set: passenger name, number of trips, and company name.
10	4	1	Find the passengers which have spent the most time during flyings than others. Result set: passenger name, total flying time in minutes.
11	1	1	Find all the makers who have all their models of PC type in the PC table.
12	5	1	Under assumption that there are holes among IDs (q_id) of squares, find the minimal and maximal "free" IDs in a range between available maximal and minimal IDs. If holes are not present, deduce NULL. Example: For the sequence of square IDs 1,2,5,7, the result must be 3 and 6
13	2	2	A scrap recycling company is examining status of their outlets. Assuming each outlet starts with $10000 dollars find the date which is was profitable, in case the outlet is not profitable, write the last date an income was recorded. To determine the profit of a company we will look at the Outcome_o only and not look at either incomes tables or the the outcome table. Result set: Outlet ID (point), profitable date (or last working day), profit (or remaining to be profit).
14	1	2	For Product table, receive result set in the form of a table with columns: maker, pc, laptop, and printer. For each maker, this table must include "yes" if a maker has products of corresponding type or "no" otherwise. In the first case (yes), specify in brackets (without spaces) the quantity of available distinct models of corresponding type (i.e. being in PC, Laptop, and Printer tables).
15	1	1	Dima and Misha use products of the same maker. Type of Tanya's printer is not the same as that of Viktor, but a "color" property is match. Screen size of Dima's laptop is more by 3 inches then Olga's one. Misha's PC is 4 times more expensive then Tanya's printer. Model numbers of Viktor's printer and Olga's laptop differ with third symbol. Konstantin's PC processor speed equals to that of Misha's PC, hard drive equals to that of Dima's laptop, ram equals to that of Olga's laptop, and the price is the same as that of Viktor's printer. List all the possible model numbers of Konstantin's PC.
16	4	2	One second of a flight of each passenger gives 1 cent ($0.01) profit to airline company. Taking this fact into account, perform ABC analysis of passengers attractiveness (an airline company does not matter). ABC analysis is based on Pareto principle - 80% of your sales come from 20% of your clients. After the analysis, passengers should be grouped into 3 categories: A, B, and C. The algorithm: 1. Calculate ratio of each passenger's profit contribution with running totals (S) to total profit from all passengers (TS). Contribution with running totals for each passenger is calculated by summation of his profit with profits from all those passengers that give larger or equal profits. In the case of equal profits, the smaller running totals will be for the passenger, name of which goes earlier in alphabetic order. 2. Group the passengers over ABC categories. Category "A" - passengers with S/TS between 0.00% and 80.00% (inclusive). Category "B" - S/TS from 80.01% up to 95.00%. Category "C" - S/TS from 95.01% up to 100%. Result set: Passenger's name, sum of profit from this passenger ($), S/TS of the passenger in percent (accuracy within 0.01), ABC category of the passenger.
17	5	1	Group the all paintings by days, months and years separately. Format the identifier of each group as follows: "yyyy" for a year, "yyyy-mm" for a month and "yyyy-mm-dd" for a day. Get only those groups that include more than 10 paintings at distinct moments of time (b_datetime). Result set: group identifier, the total quantity of a paint used within a group.
18	2	2	Output all records from Outcome and Income tables which dates are not less than 2 calendar months distant from the maximum date selected from these both tables ( ex. when max date is "2009-12-05" last output date must be less or equal "2009-09-30"). Partition these records and assign a sequenced number to every month (in view of a year) which has been selected. Result set: the sequenced number of the month, the first day of the month in "yyyy-mm-dd" format, the last day of the month in "yyyy-mm-dd" format, code, point, sum (which should be negative for Outcome table).
19	3	2	Define the names of all the ships in the database which were definitely launched before 1941.
20	4	1	For each company, find time the company's planes have spent during accomplished flights. Result set: company name, time in minutes.
21	4	1	Among 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.
22	2	2	Write a query that displays all the operations from the Income and Outcome tables in the following form: date, entry number for this date, outlet of income, income, outlet of expense, expense. All the income operations in all outlets occurred in a single day, must be sorted by the "code" field, and also all the expense operations must be sorted by the "code" field. If the operations of income/expense in one day was not an equal number, use NULL in the corresponding columns in place of missing operations.
23	4	2	Find the dates where number of trips from town Rostov was the maximum. Result set: number of trips, date
24	3	2	Determine names of all the ships in Ships table that meet a combination of at least four criteria from the following list: numGuns = 8 bore = 15 displacement = 32000 type = bb launched = 1915 class = Kongo country = USA
25	1	3	Find 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.
26	1	3	Define the average price of the PCs and laptops produced by maker A. Result set: single total price.
27	1	3	Define the average size of the PC hard drive for each maker that also produces printers. Result set: maker, average capacity of HD.
28	1	3	Define the average HD size (single value for all makers) of PCs produced by those makers that also produce printers. Result set: average size of HD.
29	2	3	Under the assumption that the income (inc) and expenses (out) of the money at each outlet are written not more than once a day, get a result set with the fields: point, date, income, expense. Use Income_o and Outcome_o tables.
30	2	3	Under 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.
31	3	2	Get all the ship's classes of Russia. If there are no classes of Russia in the database, get all the classes in the database. Result set: country, class