Column Data Types

To create a table that holds information for a customer record in a database, you'd need to consider various attributes that are typical for customer information. The structure of this table can vary depending on specific business requirements, but a general structure might include the following columns:

1. CustomerID: A unique identifier for each customer. It's often an integer and can be set to auto-increment for ease of management.
2. FirstName and LastName: To store the customer's name. These are typically VARCHAR fields, with a length that's suitable to accommodate most names.
3. Email: A VARCHAR field to store the customer's email address. It's important to ensure that this field is long enough to accommodate various email lengths.
4. PhoneNumber: This can also be a VARCHAR field. Even though phone numbers are numeric, they may include formatting characters like dashes or parentheses.
5. Address: This might include several fields such as StreetAddress, City, State, and ZipCode, depending on how detailed you need the address information to be. These are typically VARCHAR fields.
6. CreateDate: A DATE or DATETIME field to record when the customer record was created.
7. LastUpdateDate: A DATE or DATETIME field to track when the customer record was last updated.

Here's an example of how you might write the SQL statement to create this table:

CREATE TABLE Customers (
    CustomerID INT NOT NULL AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    PhoneNumber VARCHAR(20),
    StreetAddress VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    ZipCode VARCHAR(10),
    CreateDate DATETIME,
    LastUpdateDate DATETIME,
    PRIMARY KEY (CustomerID)
);

This structure is quite flexible and can be adjusted to fit specific needs. For example, if you need to store multiple addresses or phone numbers for a single customer, you might consider creating separate tables for addresses and phone numbers and linking them to the Customers table using foreign keys. Additionally, depending on the database system you're using, you might want to consider other data types or constraints to optimize performance and data integrity.

In SQL, you store different types of information and as a result, the table must possess the ability to store different data types. You can see that in the example table that we are creating. For most of the items, the type of information being stored is a character-based string of letters and numbers. That is, it supports the letters of the alphabet and numbers as well as some special characters. It is beyond the scope of this course to get into all the different data types available to you, but for the purposes of this module, you will need to understand that you may want to use the following basic data types:

1. CHAR(n) specifies that the information is a character type. In the parentheses, specify the maximum number of characters that you will allow in the column.
2. NUMERIC (precision, scale) indicates that the column will hold number values. The precision refers to the number of digits you want to store, and the scale indicates how many of those digits will be used for decimal values, those to the right of the decimal point. For example, if you want to use the column for money, you might indicate NUMERIC(10,2) for a field that will support 10 total digits, eight to the left and two to the right of the decimal place.
3. INT allows only whole numbers. For example, if you have a value of 1.2, the integer representation of this is 1.

A key part of a column's definitions is the data type. The following list summarizes the most common SQL data types:

1. BLOB: A Binary Large Object. This is any chunk of binary data such as a JPEG file, audio file, video file, or Word document. The database knows nothing about the internal structure of this data so, for example, if the BLOB contains a Word document the database cannot search its contents.
2. BOOLEAN: A true or false value.
3. CHAR: A fixed-length string. Use this for strings that always have the same length such as two-letter state abbreviations or five-digit ZIP Codes.
4. DATE: A month, date, and year such as February 29, 2012.
5. DATETIME: A date and time such as 12:34pm February 29, 2012.
6. DECIMAL(p, s): A fixed-point number where p (precision) gives the total number of digits and s (scale) gives the number of digits to the right of the decimal. For example, DECIMAL(6, 2) holds numbers of the form 1234.56.
7. INT: An integer value.
8. NUMBER: A floating point number.
9. TIME: A time without a date such as 3:14am.
10. TIMESTAMP: A date and time.
11. VARCHAR: A variable-length string. Use this for strings of unknown lengths such as names and street addresses.

Specific database products often provide extra data types and aliases for these types. They also sometimes use these names for different purposes. For example, in different databases the INT data type might use 32 or 64 bits, and the database may provide other data types such as SMALLINT, TINYINT, BIGINT, and so forth to hold integers of different sizes.

In general, all the popular database servers have the capacity to store the same types of data, such as strings, dates, and numbers. Where they typically differ is in the specialty data types, such as XML documents or very large text or binary documents. Since this is an introductory book on SQL, and since 98% of the columns you encounter will be simple data types, this book covers only the character, date, and numeric data types.

Character data can be stored as either fixed-length or variable-length strings; the difference is that fixed-length strings are right-padded with spaces and always consume the same number of bytes, and variable-length strings are not right-padded with spaces and don't always consume the same number of bytes. When defining a character column, you must specify the maximum size of any string to be stored in the column. For example, if you want to store strings up to 20 characters in length, you could use either of the following definitions:

char(20) /* fixed-length */
varchar(20) /* variable-length */

The maximum length for char columns is currently 255 bytes, whereas varchar columns can be up to 65,535 bytes. If you need to store longer strings (such as emails, XML documents, etc.), then you will want to use one of the text types (mediumtext and longtext), which I cover later in this section. In general, you should use the char type when all strings to be stored in the column are of the same length, such as state abbreviations, and the varchar type when strings to be stored in the column are of varying lengths. Both char and varchar are used in a similar fashion in all the major database servers.

Create Table - Exercise