SQL Foundations  «Prev  Next»

Lesson 4Create a table statement
ObjectiveWrite SQL Statement to Create a Table to match Specifications.

SQL Create Statement

Table to match Specifications

The basic concept here that you need to understand is the fundamental format of the statement. First, you tell the SQL engine what you want to do and where you want to do it:

CREATE TABLE BasicTable

This portion of the statement says, "I want to make a new table and it needs to be called BasicTable".
It also sets up the engine to get ready for the balance of the statement that will call out the columns included in the table. You have to start the list of columns to be created with an opening parenthesis.

(
 Firstname char (50) NULL ,
 Lastname char (50) NULL ,
 PhoneNumber char (10) NULL 
)

Determine Column Data Types

What remains is to call out the columns and define what type of information they hold. Think of the type as referring to the contents of the column. If you are planning on placing letters, letters and numbers, or numbers with spaces in the column, you are probably looking at creating a CHAR column type.
There are several different types that allow text and numeric information in the column. Though they vary slightly from database engine to database engine, you can count on using the following:
  1. TEXT: For unknown length items
  2. CHAR: For items of a known length
  3. VARCHAR: For character-based fields that vary in length, up to 255 characters

There are others, but for this class, we will be using the CHAR type, because it is most universal and the simplest to implement. Here is some additional information about databases and column types.

Create Column using determined Data Type

Create a column called FirstName that has a length of 50 as a character-based column. Allow nulls in that column, which means that the user will not have to provide the information for that item when it is saved. Second, create the LastName column, again of length 50, as a character-based column that can be empty.
The PhoneNumber field is interesting because it holds numbers. You might think at first that this would be a numeric field, but keep in mind that the user will likely be entering dashes, parentheses, and so on. These are characters, and they are not allowed in a numeric field. For this reason (the spaces and dashes), this field is considered a text-based field and is created as a CHAR type.

Create Table - Exercise

Click the Exercise link below to write a statement that will create a particular table.
Create Table - Exercise
In the next lesson, we will discuss what an index is.