The Columns of a Table

In the previous lesson, we defined a table as an arrangement of a list of items. Here is an example:

This means that information is organized in columns. When creating a table, after specifying the name of the table, you must list the columns of the table.

The list of columns starts with an opening parenthesis "(". The list ends with a closing parenthesis ")". Each column must be separated from the next with a comma, except for the last column. You can include all columns on the same line if possible as follows. Here is an example of a statement that starts a table named Country:

CREATE TABLE Country(Column1, Column2, Column3);

There are two primary pieces of information you must specify for each column: its name and its type. The name of a column should follow the same rules and suggestions we reviewed for the database objects. Here is an example of a statement that starts a table named Country with three columns named Name, Area, and Population:

CREATE TABLE Country(Name, Area, Population);

After typing the name of the column, or on the right side of the name of a column, type the desired or appropriate data type for the column. Based on this, the actual formula to create a column is:

ColumnName DataType Options

Here is an example of a statement that starts a table named Country with three columns named Name, Area, and Population:

CREATE TABLE Country(Name DataType, Area DataType, Population DataType);

Types of Data

Introduction

To better organize the information in a table, each column should follow some specific rule(s) conform to a type allowed for that particular column. Based on this, if you create a column that should include people's names, you would not like any cell under that column to list an employee's date hired. To help with configuring columns, the SQL provides categories of information. A data type is a type of information that can be entered in the cells of a column. There are various types of data and you should use the right one for each column of a table.

Boolean Fields

If you want a column to hold only values as being true or being false, specify its data type as bit. The bit is the smallest data type of the SQL.

Natural Numeric Fields

A column qualifies as numeric if its cells can allow only numeric values. There are various types of numbers available.

An integer, also called a natural number, or a whole number, is a number that can start with a + or a - sign and is made of digits only. Between the digits, no symbol is allowed. When the number starts with +, such as +44 or +8025, such a number is referred to as positive and you should omit the starting + sign; this means that the number should be written as 44 or 8025. Any number that starts with + or simply a digit is considered as greater than 0 or positive. A positive integer is also referred to as unsigned. On the other hand, a number that starts with a - symbol is referred to as negative.

If a column would hold numbers in the range of -2,147,483,648 to 2,147,483,647, set its data type as int.

If you except to use very small numbers such as student's ages, or the number of pages of a brochures or newspaper, set the column's data type as tinyint. A column with the tinyint data number can hold positive numbers that range from 0 to 255.

The smallint data type follows the same rules and principles as the int data type except that it is used to store smaller numbers that would range between -32,768 and 32,767.

If you anticipate a column to use very large natural numbers, such as a country population, create it with the bigint data type. The bigint data type is used for a column that can hold numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Decimal Numeric Fields

A decimal number is a number that can have a period (or the character used as the decimal separator for your operating system and language) between the digits. An example would be 12.125 or 44.80. Like an integer, a decimal number can start with a + or just a digit, which would make it a positive number. A decimal number can also start with a - symbol, which would make it a negative number. If the number represents a fraction, a period between the digits specifies what portion of 1 was cut.

If you anticipate a column to hold decimal numbers, specify its data type as numeric or decimal. You can also use dec for decimal; both dec and decimal mean the same thing and represent the same data type. Because a field that uses numeric or decimal data types is a decimal number, SQL allows you to express how precise you want the number to be. If you want to control the number of digits that should be used on the left and the right side of the period (or the character used as the decimal separator of your language), use the dec(Precision), decimal(Precision), or numeric(Precision) data type. The syntaxes you can use are:

dec(Precision)
decimal(Precision)
numeric(Precision)

To be even more precise, SQL allows you to assign a scale of precision to your field. The syntaxes you can use are:

dec(Precision, Scale)
decimal(Precision, Scale)
numeric(Precision, Scale)

If you use dec(Precision, Scale), decimal(Precision, Scale), or numeric(Precision, Scale) types, the Precision number would control the maximum number of digits on the left side of the period (or the character used as the decimal separator of your language). The Scale factor specifies the maximum number of digits used on the right side of the period (or the character used as the decimal separator of your language).

In all cases, remember that the Precision and the Scale factors are not the value of the field in which they are used. They specify the type of precision to use. Therefore, both must be positive numbers.

A floating-point number is a fractional number, like the decimal type. Floating-point numbers can be used if you would allow the database engine to apply an approximation to the actual number that a field is supposed to carry. To create a column for a floating number, use the float or the real data type. The syntaxes of these types are: 

float(Precision)
real(Precision)

Text-Based Fields

A field of characters can consist of any kinds of alphabetical symbols in any combination, readable or not. If you want a column to hold a fixed number of characters, such as the book shelf numbers of a library, use the char data type for such a column. In such a case, all fields under that column would use the same number of character. The desired number of characters must be typed in the parentheses of the char data type. The syntax used is:

char(Number)

In this case, the Number factor represents the number of characters that each field would use. The Number factor must be a positive number between 0 and 255.

If you are creating a column that would use a fixed number of international characters, that is, characters of languages other than US English, set its data type to nchar. This is done following the rules of Unicode formats.

In the computer world, a string is a character or a combination of characters that are considered "as is" with regards to the scenario in which they are used. In a table, some columns are meant to hold such strings. One of the most significant characteristics of such a field is that data entered into it can change from one record to another. An example would be the first names of employees: people have different first names of different lengths.

If a column will hold strings of different lengths, create it with the varchar data type. The syntax of this type is:

varchar(Number)

Since the number of characters can vary, you can specify the maximum number of characters that a field would allow. The Number factor controls the length of the string. The Number must range between 1 and 255.

The fact that Microsoft SQL Query Analyzer sets the default number of characters to 1 is an indication that MySQL is right by not allowing you not to specify the number of characters. Therefore, you should (always) specify the maximum Number of characters. This is done by typing the number of characters as in varchar(Number).

The text data type can be applied to a field whose data would consist of ASCII characters. As opposed to a varchar type of field whose maximum number of characters is 255, a text type of field can hold text that as long as 65535 (2¹⁶ - 1) characters (8 kilobytes on Microsoft SQL Server).

Dates and Times Fields

When you are creating a column that would hold date or time values, set its data type to datetime. The entries must be valid date or time values but SQL allows a lot of flexibility, even to display a date in a non-traditional format.

In Microsoft SQL Server, the date value of a datetime field can be comprised between January 1st, 1753 and December 31, 9999.

Practical Learning: Creating Tables