Problem I. 356. (October 2014)
I. 356. Patients in a town can make an appointment for the working days with any of the three dentists. Dental services are offered at fixed prices. Our database contains data from 2011, and its structure is simpler than that of the official tables.
1. Create a new database fogorvos, and import the four given (tabulator-separated and UTF-8 encoded) text files paciens.txt, beavatkozas.txt, kategoria.txt and fizetendo.txt into the database by using the same names (paciens, beavatkozas, kategoria, fizetendo), then set the appropriate types and keys. The first line of each file contains the field names.
When solving the following tasks, queries should be saved by using names given in the parentheses. Make sure that your solution contains only the requested fields.
2. Make a query to determine the most expensive treatment type. (2legdragabb)
3. In 2011 certain workdays and holidays were swapped so that some Saturdays became workdays. This also affected our dentists' timetable. Make a query to give these special Saturdays. (3szombat)
4. Make a query to determine the income of each dentist in 2011. (4bevetel)
5. Make a query to determine the percentage of wisdom teeth removals relative to all wisdom teeth treatment. (5eltavolitas)
6. Make a query to determine the people whom patient ``1111'' could not meet at the dentist's office. (6nemtalalkozott)
7. Make a query to give whether the first or second premolars are treated more frequently. The query should display only the tooth name, and we assume that the answer to this question can be uniquely determined. (7kisorlo)
8. Create a query to list the patient IDs who visited the dentist's office at least 3 times during the past 3 weeks (that is, 21 days). (8harom)
9. Make a query to give the patients with two adjacent teeth treated in 2011. (9szomszedos)
The database containing your solution or the text file containing the SQL queries (fogorvos.odb, fogorvos.mdb, fogorvos.sql), together with a short documentation (i356.txt, i356.pdf) also describing the name and version number of the database application, should be submitted in a compressed file (i356.zip).
Deadline expired on November 10, 2014.
|16 students sent a solution.|
|10 points:||Kovács Balázs Marcell, Mócsy Miklós.|
|8 points:||3 students.|
|7 points:||1 student.|
|6 points:||4 students.|
|5 points:||3 students.|
|4 points:||1 student.|
|2 points:||2 students.|