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.
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)
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.
Notes: 1) Outcomes relation may include the ships not included in the Ships relation. 2) Sunk ship can not participate in battles after that.
The database schema consists of 4 tables:
Trip(trip_no, id_comp, plane, town_from, town_to, time_out, time_in)
Pass_in_trip(trip_no, date, ID_psg, place)
The Company table contains IDs and names of the airlines transporting passengers. The Trip table contains information on the schedule of flights: trip (flight) number, company (airline) ID, plane type, departure city, destination city, departure time, and arrival time. The Passenger table holds IDs and names of the passengers. The Pass_in_trip table contains data on flight bookings: trip number, departure date (day), passenger ID and her seat (place) designation during the flight. It should be noted that
- scheduled flights are operated daily; the duration of any flight is less than 24 hours; town_from <> town_to;
- all time and date values are assumed to belong to the same time zone;
- departure and arrival times are specified with one minute precision;
- there can be several passengers bearing the same first name and surname (for example, Bruce Willis);
- the seat (place) designation consists of a number followed by a letter; the number stands for the row, while the letter (a – d) defines the seat position in the grid (from left to right, in alphabetical order;
- connections and constraints are shown in the database schema below.
The 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 utQ table contains the identifiers and names of squares, the initial color of which is black.
The utV table contains the identifiers and names of spray cans and the color of paint they are filled with.
The utB table holds information on squares being spray-painted, and contains the square and spray can identifiers, the quantity of paint being applied, and the time of the painting event.
It should be noted that
- a spray can may contain paint of one of three colors: red (V_COLOR='R'), green (V_COLOR='G'), or blue (V_COLOR='B');
- any spray can initially contains 255 units of paint;
- the square color is defined in accordance with the RGB model, i.e. R=0, G=0, B=0 is black, whereas R=255, G=255, B=255 is white;
- any record in the utB table decreases the paint quantity in the corresponding spray can by B_VOL and accordingly increases the amount of paint applied to the square by the same value;
- B_VOL must be greater than 0 and less or equal to 255;
- the paint quantity of a single color applied to one square can’t exceed 255, and there can’t be a less than zero amount of paint in a spray can;
- the time of the painting event (B_DATETIME) is specified with one second precision, i.e. it does not contain milliseconds;
- for historical reasons, the spray cans are referred to as “balloons” by many of the exercises, and the utV table contains spray can names (V_NAME column) such as “Balloon # 01”, etc.
Appendix 2. The list of exercises (SELECT)
Below is the list of all the first-phase rating exercises which are optional with respect to certification and advance over stages.
The number of each exercise, the database number for the corresponding exercise, and the exercise complexity level are given in the list.
|-1||5||1||With the least-square method, calculate the linear dependence of paint expenses on time:
V = at + b,
where V - paint supply; t - time in seconds, counted from the first painting (t = 0).
Result set: a with 8 digits that can be placed to the right of the decimal point; b with 2 digits that can be placed to the right of the decimal point.|
|-2||3||2||For 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, year|
|-3||5||2||There are torussians of three colors (red, green, blue) who live in the Torus 6x4 planet.
The first 24 squares of utQ table (ordered by B_Q_ID) - the countries of the planet.
T00 - first square
T03 - square number four
T10 - square number five
T53 - square number twenty four
Amount of the paint on a square - the amount of torussians of each color(R, G, B)
On the Merkadot's birthday all torussians in each country split groups - 8 groups of each color; The amount of torussians in each group equals R/8, G/8, B/8. All the groups are sent to the 8 neighboring countries (by one group of each color in each neighboring country).
The rest of the torussians are staying at home (R%8, G%8, B%8 respectively)
Find the amount torussians of each color in every country on this significant day
Output: amount of torussians of each color on the Mercadot's map for country "T00" using the format: "Txy - cccR cccG cccB" where Txy - country name, ccc - the amount with leading zeroes.
|-4||5||2||Find the squares with the time interval between first and finish paintings greater than average time interval for painted squares.
Result set: square name, maximal time between its sequential paintings in seconds.|
|-5||3||1||The Battles table contains not only major battles with the participation of many ships but also records, starting with symbol # and containing data about minor sea conflicts. Related conflicts combined in a groups from 1 to 6 items.
Name format of such records is as follows:
- # followed by group code (does not contain digits, may be missing)
- continues with group registration number (integer, is required)
- continues with additional conflict number inside of group. It could be arabian or roman numerals, letters of Latin with different separators, for example a,b,c... /1,/2,/3... .1,.2,.3... i,ii,iii... etc., but the same for a whole group and with no gaps.
The first or only conflict in a group may not have such additional number.
Get list of conflicts records, sorted by group code, group number, additional number.
Result set: The name of conflict, the number by order (starting with
|-6||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.|
|-7||1||1||In Product table, find out the models the first symbol of which represents an even digit, and the last one - odd digit.
The first symbol should also be less than the last one.
Result set: model, type of model, product of first and last digits in model's No.
|-8||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).|
|-9||3||1||A ship can be mentioned more than once in the Outcomes table if it took part in more than one battle.
It's needed to find out ship names which differ by only case of characters, say, "Duke of York" and "duke Of york".
Result set: ship's name (any variant of writing), number of different writing variants that could be used for this ship in the table.|
|-10||4||1||Display flights map on Google Static Maps.
SHow only unique directions so that name of the first city is earlier than second city’s name in alphabetic order.
For example, if there is a flight from Rome to Berlin, but there is no one from Berlin to Rome, you need to show a direction Berlin-Rome. Or if there are both flights, from Milan to Madrid and from Madrid to Milan, you need to show only one direction Madrid-Milan.
Output resulting directions in alphabetical order of their cities.
For each city display a marker with first letter of city’s name.
Resulting string should be like this (without line breaks):
<img src="http://maps.googleapis.com/maps/api/staticmap - directive to use Google Static Maps;
?path=weight:3|Aaa|Bbb - first direction, from city Aaa to city Bbb with line thickness 3;
&path=weight:3|... - all other directions;
&markers=label:A|Aaa - marker (A) of city Aaa;
&size=512x512 - indication of size of the map 512x512;
&sensor=false"> - required parameter.|
|-11||3||1||Provide each ship's name from Ships table in base64 (wikipedia).
Output: name, base64name.|
|-12||4||1||At the airport, mathematician Ivanov has been calculating the factorial of the trip number for fun.
For each trip, compute the number of trailing zeros in the result obtained by Ivanov.
Note: the trip number cannot contain more than 4 digits.
Output: trip_no, quantity of trailing zeros.|
|-13||4||1||Find number of passengers for each day in the first half of the year 2003 from the date of a first trip up to the date of a last trip in this half of a year. First half of a year is the interval from 03.01.01 to 03.06.30. Result set: date, number of passenger |
|-14||3||1||Determine the names of only those classes, which include only the ships with names consisting of only the characters used in the name of some class.
Note. Case of characters does not matter.|
|-15||5||2||Round parasols are installed in the coordinates of B_Q_ID, B_V_ID (utB table) on a flat sandy beach. Radius of each parasol equals B_VOL. Parasols are parallel to the sand. The sun is at its zenith. Each value of B_DATETIME corresponds to a separate task. Find out the area of shadow for each B_DATETIME value.
Output: B_DATETIME; the area of shadow rounded to the nearest integer.|
|-16||2||1||Determine such outlets (points) which have in Outcome table the records corresponding to every working day within some week (from Monday till Friday, without taking holidays into account).
Deduce: point, date of Monday of full working week in the format "YYYY-MM-DD", total value of `out` for this week.