All exercises are performed on the databases described below.
Below is the list of all the firstphase 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.
no.  DB  level  exercise 
1  5  1  With the leastsquare 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 1) 
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 BerlinRome. Or if there are both flights, from Milan to Madrid and from Madrid to Milan, you need to show only one direction MadridMilan. 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 ?path=weight:3AaaBbb &path=weight:3AaaCcc &path=weight:3BbbCcc &markers=label:AAaa &markers=label:BBbb &markers=label:CCcc &size=512x512&sensor=false"> , where <img src="http://maps.googleapis.com/maps/api/staticmap  directive to use Google Static Maps; ?path=weight:3AaaBbb  first direction, from city Aaa to city Bbb with line thickness 3; &path=weight:3...  all other directions; &markers=label:AAaa  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 "YYYYMMDD", total value of `out` for this week. 
17  4  1  Concatenate the trip numbers sorted in ascending order into a single string. Starting from the left, break up the resulting string into substrings consisting of three characters each; leftover symbols are discarded. Treating each substring as a number, get their sum, as well as the minimum and maximum numbers. Result set: minimum number, maximum number, sum. 
18  2  2  The Income and Income_o tables are considered, with records in them corresponding to the same set of buyback centers. Each center is deemed to have been working on all dates occurring in the aforementioned tables, no matter to which centers they are corresponding, that lie within the time period between the first and the last date this center received funds. For each center having the highest average cash receipts per working day, determine the date(s) it received the largest sum of money. For these dates, display ALL information present in the tables, namely point, date, inc, code (or NULL if the entry belongs to Income_o). 
Home  SELECT exercises (rating stages)  DML exercises  Developers 
