Create a table with SQL

Tables contain information that you can insert, select, update. Before even doing that, you need to create these tables.

To create a table, you need to know about columns’ types

In the lesson about tables, columns and rows, we saw that:

  • tables have lines (rows).
  • tables have columns.
  • the intersection between a table and a column is a field.

All fields within a column have the same type. A field type tells you:

  • if the field contain text or numbers.
  • the biggest amount of information it can hold.
Example: one sales person’s first name in the table salespersons is contained in one field. All fields containing a sales person’s first name contain text and have the same size limit (for example not more than 30 characters).

In order to create a table, you have to show for each column:

  • the name of the column.
  • the type of fields in this column.

Optionally, you can indicate other properties of the column, like:

  • an indication that the field cannot contain null values. The syntax for this is : NOT NULL.
  • the default value taken by the column when inserting a new row without specifying the value to put in this column. The syntax for this is, in case of a text field: DEFAULT ‘defaultstringvalue’. In case of a number, the syntax is for example DEFAULT 5.
  • the indication that the column is the table’s primary key. Such a column contains in each row a text or a number that can identify a row uniquely. Two rows cannot have the same value in this column, and this value cannot be null. There can be only one primary key per table. To define a table as a primary key, add the text PRIMARY KEY just after the definition of the column’s type.
  • the indication that the value column is automatically generated when inserting a new row. Its value is then equal to the highest value in this column plus an increment (usually the increment is equal to 1). The syntax for this is AUTO_INCREMENT.

Syntax to create a table

Example of table creation with mySQL:

CREATE TABLE salesperson
(
ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
total_sales decimal(7,2),
first_name varchar(30) NOT NULL,
last_name varchar(30) NOT NULL,
state varchar(20),
status varchar(20) DEFAULT 'approved'
)

Example of table creation with SQL Server:

CREATE TABLE salesperson
(
ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
total_sales decimal(7,2),
first_name varchar(30) NOT NULL,
last_name varchar(30) NOT NULL,
state varchar(20),
status varchar(20) DEFAULT 'approved'
)

In this example:

  • there are 6 columns (ID, total_sales, first_name, last_name, state, status).
  • ID int means that fields in column ID contain integers. An integer is a number without any decimal point. Example: -1, 1, 2, 3,…. are integers. 1.5 (one and a half) is not an integer.
  • NOT NULL AUTO_INCREMENT PRIMARY_KEY means that fields in the column cannot be null and are generated automatically while inserting data by adding an increment to the highest value in the column. PRIMARY_KEY means that the column ID is the table’s primary key. In SQL server, AUTO_INCREMENT is replaced by IDENTITY(1,1).
  • first_name varchar(30) means that the fields in column first_name can contain from 0 to 30 characters. A character can be a letter, a number, or a special character (for example “&”).
  • total_sales decimal(7,2) means that the fields in column total_sales contain numbers that can have digits after the decimal point. Example: in 10.5 (ten and a half), 5 is a digit after the decimal point. These numbers cannot have more than 7 digits. From these 7 digits, 2 at the most can be after the decimal point (“.”). For example, the number “19878.51 “ has 7 digits in total(1,9,8,7,8,5 and 1), 2 digits being after the decimal point (5 and 1). The numbers 123456.12 and 1234.123 cannot be contained in this field: 123456.12 has too many digits in total (8) and 12345.123 has too many digits after the decimal point (3).
  • status varchar(20) DEFAULT 'approved' means that the fields in column status is set by default to ‘approved’ when inserting a new row, if the value for this field is not defined in the INSERT statement.

Why I should bother thinking about the size of a field ? Can I not just reserve a huge size for each of them ? :

The bigger the field size, the more information it can hold, but the more place it takes. A bit like a box. To save space, use the smallest field size you can.

A text field can contain numbers. Why can I not just always specify that the column should contain text?

Some operations like calculating totals and averages, comparing numbers are possible only when the field contains numbers.

If you want to test your understanding of this article, take the quiz about table creation wih SQL.
You can also read about modifying tables with ALTER TABLE.
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: August 15, 2014