Sql Simplified: Learn to Read and Write Structured Query Language

Example 1

The following SQL script creates a table named Employees with seven columns (SocialSecNum, Firstname, Lastname, Address, Zipcode, Areacode, PhoneNumber).

CREATE TABLE Employees (
SocialSecNum CHAR (11) NOT NULL PRIMARY KEY,
Firstname CHAR (50) NOT NULL,
Lastname CHAR (50) NOT NULL,
Address CHAR (50) NOT NULL,
Zipcode CHAR (10) NOT NULL,
Areacode CHAR (3) NULL,
PhoneNumber CHAR (8) NULL
);

The preceding script includes several keywords. The keywords are typed in all caps so that they can be easily identified. Although it is not necessary to capitalize keywords, it is good practice since it causes the keywords to stand out. This makes SQL script easier to read. Remember, keywords are reserved words used to interact with the database.

Notice the spacing in the CREATE TABLE script. You must separate all keywords and column names with a space. Additionally, some DBMSs require a closing semicolon at the end of SQL script to indicate where the script ends.

The CREATE TABLE keywords tell the database management system that you want to create a new table. The name (Employees) of the table must be typed directly after the CREATE TABLE keywords.

Each column (SocialSecNum, Firstname, Lastname, Address, Zipcode, Areacode and PhoneNumber) must contain a datatype and a field size. Some datatypes do not require a field size.

A datatype specifies the type of data a column can store. The field size specifies the maximum number of characters that can be entered into a cell within a column. For example, the datatype and field size for the first column (SocialSecNum) is CHAR (11). This means that the SocialSecNum column can only store alphanumeric data no more than eleven characters long.

Following the datatype and field size, specify either NULL or NOT NULL. The NULL keyword indicates that a column can be left blank when entering data in the table. The NOT NULL keywords indicate that a column cannot be left blank. The DBMS will generate an error message whenever a NOT NULL field is left blank.

Note: In Microsoft Access, when you do not state NULL or NOT NULL the column is automatically set to NULL.

The primary key is usually specified after NULL | NOT NULL. In figure 2.1, the SocialSecNum column is the primary key column. In some versions of Microsoft Access, the primary key is set as follows:

SocialSecNum CHAR (11) NOT NULL CONSTRAINT PriKey
Primary Key

This method uses the CONSTRAINT keyword. You will learn more about constraints in chapter 10.