Creating tables

As mentioned in a previous lesson, the table is the primary object of importance in a database. In contains the organized data. Tables organize data into columns (fields) and rows (records). The technique of organizing data into efficiently organized tables is called normalization.


The purpose of normalization is to create tables within a database without having redundant information.  Typically we want to create tables that represent one object, and the fields are used to describe that object. Often one of the fields in the table will be used as a primary key and its value for each record must be unique. In this course the field that will be the primary key will have _id in the name of the field.  The primary key constraint will be covered in a later lesson.

Creating a table in SQL

To create a table we use the CREATE TABLE command followed by the name of the table. When we create a table we have to also define all the fields that are associated with it. Each field will also have a data type that tells the system what kind of data will be stored in the field.

Below is an example of creating a table named student, with the student_id field being an integer and also the primary key. The additional  fields first_name and last_name use the VARCHAR data type and allow for a maximum of 30 characters each. You must define the maximum length of characters when using VARCHAR. Be sure to include a comma after each field. Do not include a comma at the end of the last field.

student_id  INT  PRIMARY KEY,
first_name  VARCHAR(30),
last_name  VARCHAR(30)

Removing tables

Similar to removing databases the DROP command is used to remove tables. To remove a table use DROP TABLE followed by the table name. Below is an example of the statement.

DROP TABLE table_name;

Viewing the tables structure

After you create a table you may need to go back and see the order of fields and what data types were used to create the table. To view the design structure of your table use the DESCRIBE command followed by the table name. Below is an example of the statement.

DESCRIBE table_name;

Listing tables in the database

To view a list of the tables you have created in the database we use the SHOW TABLES command. Below is an example of the command.


Lesson Activities

  1. We will create the first table for the school database. Be sure that you have selected the school database. To create the student table use the following command:
    Click to see code
    CREATE TABLE student(
    student_id INT PRIMARY KEY,
    first_name VARCHAR(30),
    last_name VARCHAR(30)
  2. To view the structure of the table you just created use the DESCRIBE command.
    Click to see code
    DESCRIBE student;
  3. To view a list of the tables, which now is just one table use the SHOW TABLES command.
    Click to see code
  4. We will be creating a more detailed student table in upcoming lessons. We will now remove the student table to practice dropping them from the database. Use the following command:
    Click to see code
    DROP TABLE student;