Let us take a look at the following fields in the table and what work they perform:
Column name |
Datatype |
Comments |
---|---|---|
|
|
This stores the unique identifier for a student |
|
|
This stores the first name of the student |
|
|
This stores the last name of the student |
|
|
This stores the address of the student |
|
|
This stores the name of the city |
|
|
This stores the two letter abbreviation for states in the United States |
|
|
This stores the five digit zip code for an address in the United States |
Note
It is advised to use a character datatype for fields such as zip codes or SSNs. Though the data is going to be a number, integer datatypes are notorious for removing preceding zeroes, so if there is a zip code that starts with a zero, such as 06909
, of an integer datatype, the zip code would end up in the column as 6909
.
Now let us convert this table structure into executable SQL, to create our table, we will be using the CREATE
DDL command, followed by TABLE
and then append it with the table structure. In SQL, the column description is done by mentioning the column name first and then adding the datatype of the column. The STUDENTS
table has multiple columns, and the column information has to be separated by a comma (,
).
Now that the query has been executed, the students table has been created. To verify if the students table has been successfully built, and to view a list of existing tables that are in the current database, we can use the SHOW
utility command and append that with TABLES
:
We have successfully used the show tables;
command SQL statement to retrieve a list of existing tables, and have verified that our students
table exists in our course_registry
database. Now, let us verify if our students
table has the same table structure as we originally intended it to have. We will use the DESCRIBE
utility command followed by the table name to understand the table structure:
Tip
The DESCRIBE
and DESC
commands can be used interchangeably, both the commands would need the table name to return their structure.
Now let us move on to the courses
table, this table will house all the available courses for which a student can register. The courses table will contain a unique identifier for the course (course_id
), the name of the course (course_name
), and a brief description of the course (course_description
).
Let us now look at the fields and the type of values they are storing:
Column name |
Datatype |
Comments |
---|---|---|
|
|
This stores the unique identifier for a course. |
|
|
This stores the title of the course. |
|
|
This stores the description of a course. |
Now let us convert this table structure into executable SQL to create our courses
table:
Now that the query has been executed, let us run the SHOW TABLES
command to verify if the courses
table has been created:
The output from the SHOW TABLES
command returns the list of current tables, and the courses
table is one of them. Now that we have built the students
table and the courses
table, let us build the bridge
table that would hold the association between the two tables. This table would contain the data about the students who were enrolled to a particular course.
Let us now look at the fields in this table and their respective values:
Column name |
Datatype |
Comments |
---|---|---|
|
|
This stores the unique identifier for a course |
|
|
This stores the unique identifier for a student |
Now, let us convert this table structure into executable SQL, to create our courses
table using the following command:
Now that the query has been executed, let us run the SHOW TABLES
command to verify if the courses
table has been created:
The output from the SHOW TABLES
command returns the list of current tables, and the students_courses
table is one of them.