Mathematical and Physical Journal
for High Schools
Issued by the MATFUND Foundation
 Already signed up? New to KöMaL?

# Problem I. 145. (December 2006)

I. 145. Prepare a database for a certain informatics company and answer the questions below. The first table of the database ITEMS contains some hardware and software items: ID number, name and unit selling price of the item. The second table of the database IMPORT gives information on the purchase and suppliers: ID number of an item, unit price, date of purchase, number of items purchased, and ID number of supplier. The third table SUPPL contains further information on the suppliers: ID number, name, location (city) of the supplier, and name of the contact person. The questions to be answered or completed are the following:

a) make the table ITEMS;

b) make the table IMPORT;

c) make the table SUPPL;

d) list the names and locations of suppliers of items containing the word ''modem'';

e) list the names of suppliers shipping items of unit price below 1500 HUF;

f) list the names of items that can be bought from at least three suppliers;

g) list the ID number, name and total number of each item;

h) for each supplier, give the number of items with total value that can be bought from them. This list should contain the number of items ordered in descending order;

i) list those contact persons with company names that have not shipped anything for more than a year;

j) make a list of each item, containing the name, ID number, and cheapest unit price with the name of the corresponding supplier. The list should contain the names of items in ascending order.

A text file I145.txt should be submitted, with each line containing the appropriate SQL commands solving the questions above.

(10 pont)

Deadline expired on January 15, 2007.

Sorry, the solution is available only in Hungarian. Google translation

A sokféle SQL változatban készült megoldása közül mintaként Ócsvári Ádám budapesti versenyző munkáját közüljük (i145mo.txt).

### Statistics:

 22 students sent a solution. 10 points: Balambér Dávid, Czigler András, Danka Miklós András, Gál Tibor, Gilián Zoltán, Gombos Gergely, Györök Péter, Kiss Dániel Miklós, Kovács 129 Péter, Nádudvari Péter, Ócsvári Ádám, Polgárfi Bálint, Póta Kristóf, Szoldatics András, Véges Márton. 9 points: Földes Imre, Jégh Tamás, Kalló Bernát, Vincze János. 7 points: 1 student. 6 points: 1 student. 3 points: 1 student.

Problems in Information Technology of KöMaL, December 2006