Sample Tables
This study guide uses several different database and table names in examples. However, one set of tables occurs repeatedly: the tables in a database named world. This section discusses the structure of these tables. Throughout this study guide, you're assumed to be familiar with them.
The world database contains three tables, Country, City, and CountryLanguage:
The Country table contains a row of information for each country in the database:
mysql> DESCRIBE Country;
+----------------+-------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------------+------+-----+---------+-------+
| Code | char(3) | | PRI | | |
| Name | char(52) | | | | |
| Continent | enum('Asia', ...) | | | Asia | |
| Region | char(26) | | | | |
| SurfaceArea | float(10,2) | | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | | | | |
| GovernmentForm | char(45) | | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | | | | |
+----------------+-------------------+------+-----+---------+-------+
The entire output of the DESCRIBE statement is too wide to display on the page, so the Type value for the Continent line has been shortened. The value enum('Asia', …) as shown actually stands for enum('Asia', 'Europe', 'North America', 'Africa', 'Oceania', 'Antarctica', 'South America'). The City table contains rows about cities located in countries listed in the Country table:
mysql> DESCRIBE City;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL | auto_increment |
| Name | char(35) | | | | |
| CountryCode | char(3) | | | | |
| District | char(20) | | | | |
| Population | int(11) | | | 0 | |
+-------------+----------+------+-----+---------+----------------+
The CountryLanguage table describes languages spoken in countries listed in the Country table:
mysql> DESCRIBE CountryLanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | | PRI | | |
| Language | char(30) | | PRI | | |
| IsOfficial | enum('T','F') | | | F | |
| Percentage | float(3,1) | | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
The Name column in the Country table contains full country names. Each country also has a three-letter country code stored in the Code column. The City and CountryLanguage tables each have a column that contains country codes as well, though the column is named CountryCode in those tables.
In the CountryLanguage table, note that each country may have multiple languages. For example, Finnish, Swedish, and several other languages are spoken in Finland. For this reason, CountryLanguage has a composite (multiple-column) index consisting of both the Country and Language columns.
|
