Problem 1. Please create the following tables with appropriate primary keys & foreign keys.
The database stores information about First Lego League (A robotic competition organized by Lego).
The list of tables:
- Team table with 5 columns: tid (team ID), tname (team name), coach (coach name), cphone (coach phone), state (team state).
- Team_member table with 4 columns: mid (team member id), tid (team id), mname (member name), mage (member age).
- Competition table with 5 columns: cid (competition id), cname (name of the competition), state (state), location (location of competition), cdate (date of competition)
- Team_competition table with 3 columns: tid (team id), cid (competition id), ranking (ranking of the team in the competition, the top few teams will enter competition in the next level).
Problem 2. Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints. [20 points]
Problem 3. Please write ONE SQL statement for each of the following tasks using tables created in Problem 1. You can ONLY use conditions listed in the task description. For example, you CANNOT manually look up the tid of electrons in task 3. [60 points, 10 points each]
Task 1: Return the total number of teams
Task 2: Return the name of the team coached by Susan.
Task 3: Return the names of members in the team electrons.
Task 4: Return the names competition the team 'electrons' has participated and the team's ranking in each such competition
Task 5: Return the number of teams participated in each competition along with the name and date of the competition.
Task 6: Return the name of the team that has at least 4 members.