Description

1 ICPC
The ICPC traces its roots to 1970 when the first competition was hosted by pioneers of the Alpha Chapter of the UPE Computer Science Honor Society. The initiative spread quickly within the United States and Canada as an innovative program to raise increase ambition, problem-solving aptitude, and opportunities of the strongest students in the field of computing.
Over time, the contest evolved into a multi-tier competition with the first championship round conducted in 1977. Since then, the contest has expanded into a worldwide collaborative of universities hosting regional competitions that advance teams to the annual global championship round, the ICPC World Finals.The brand and the model of each car are also recorded, which we call as the make of the car.
The International Collegiate Programming Contest (ICPC) is the premier global programming competition conducted by and for the world‘s universities. The ICPC is affiliated with the ICPC Foundation.
1.1 Regions and Sites
1.2 Contest
In each contest, teams compete for 5 hours trying to solve over 10 questions. The teams are sorted according to the following.
1. By number of problem solved in descending order (i.e., solve). In other words, the more problem solved, the higher the rank.
2. If there are ties, then by total time penalty in ascending order (i.e., time). In other words, the smaller the time penalty, the higher the rank.
3. If there are still ties, then by the time of the last problem solved in ascending order (i.e., last). In other words, the faster they solve the last problem, the higher the rank.
2 Data
1. ICPC_clean.sql: Drops all tables from the database.
2. ICPC_schema.sql: Create all tables in the database.
3. ICPC_data.sql: Insert all rows/tuples into all tables.
4. ICPC_check.sql: Checks if all data has been correctly inserted.
If you have problems with the data import or any question about the database, you can post your questions to the Canvas Discussion on Assessment DB. We provide the logical diagram to help you understand the tables, their columns, primary keys, as well as foreign keys. This diagram is generated using pgAdmin.

2.1 Correctness
It is important to note that only the data we provide via the assessment database matters. For example, if team A solved 10 problems according to the original source but it is recorded as 11 problems, then as far as we are concerned, team A solved 11 problems. All examples in this note is obtained from this assessment database.
2.2 Complete Output
3 questions
The result contains 923 rows with the first 5 rows shown below. Note that the first team below actually starts with a whitespace.
team
‘ Codedem Innit’
‘#000000’
‘#40E0D0’
‘#911992’
‘#FFBF00’
The result contains 60 rows with the first 5 rows shown below.
team solve
‘Jagiellonian teapots’ 10
‘UW5’ 9
‘UWr 1’ 9
‘Zagreb 1’ 8
‘UW1’ 8
The result contains 51 rows with the first 5 rows shown below.
4. Find the different team name that managed to solve all problems in at least one contest they participated in. Order the result in ascending order of team name.
The result contains 25 rows with the first 5 rows shown below.
team
‘Bill Poucher’
‘Columbia-CU Again’
‘Down The Dictator’
‘Forgetful functors’
‘GT Iridescent’
The result is as shown.
4 Submission
Submit the file named Assignment01.sql to Canvas. Your answer should consists of only SQL queries. This means, you are restricted from creating tables regardless of whether it is permanent or temporary. Also, you are not allowed to create VIEW. Your code will be executed as a user that can only perform SELECT operation.
References