|Lesson 4||Create a table statement|
|Objective||Write 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:
TEXT: For unknown length items
CHAR: For items of a known length
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
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.