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.
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)
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); town_from <> town_to;
- 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.
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;
- time of painting (B_DATETIME) is given to within a second, i.e. it does not contain milliseconds.
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||1||1||For each fifth model (in ascending order of model numbers) in the Product table
find out the product type and average price of that model.|
|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 existing 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||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).
|6||4||1||Under assumption that there is no flight number greater than 65535 report a flight number and its binary number representation (without leading zeroes)|
|7||4||1||Among the passengers who only flew by the planes of the same model, find names of those who arrived at the same town at least twice.|
|8||3||1||For each battle, define the day being the latest Friday of a month, in which the given battle has occured.
Output: battle, date of battle, date of latest Friday of battle month.
Dates should be in the format "yyyy-mm-dd".
|9||3||1||For each month (taking a year into account) from the Battles table, find out how many times each day of the week repeats in this month.
Output: the month (in the format of "YYYY-MM"), number of Mondays, Tuesdays, ...Sundays.
|10||4||2||Select from the Trip table such cities, which names contain at least 2 different vowels from the list (a,e,i,o,u),
and any existing in the name vowel appears in a name the same times as other ones. |
|11||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.|
|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 NULLs.
Example: For the sequence of square IDs 1,2,5,7, the result must be 3 and 6|
|13||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.
|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 only 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||Assuming that the town from where a passenger takes the first flight is his residence, find out the names and the residence of those passengers who have still not returned home.|