Information Systems Program

Module 3
Relational Data Model and
CREATE TABLE Statement

Lesson 3: Basics of the SQL CREATE TABLE

Statement
Lesson Objectives
• Write CREATE TABLE statements with column
specifications including data types
• Read CREATE TABLE statements to see
columns and associated data types

Information Systems Program

CREATE TABLE Syntax
• CREATE TABLE <table-name> ( <column-list>
[<constraint-list>] )
• Column list with data types and optional and
inline constraints
• Optional external constraint list
– CONSTRAINT [ ConstraintName ] <Constraint-Spec>
– Primary key
– Foreign key
– Unique
– Check
3

Information Systems Program

CREATE TABLE Statement Example

CREATE TABLE Student

( StdNo CHAR(11),
StdFirstName VARCHAR(50),
StdLastName VARCHAR(50),
StdCity VARCHAR(50),
StdState CHAR(2),
StdZip CHAR(10),
StdMajor CHAR(6),
StdClass CHAR(6),
StdGPA DECIMAL(3,2) );

Information Systems Program

Common SQL Data Types
• CHAR(L)
• VARCHAR(L)
• INTEGER
• FLOAT(P)
• DECIMAL(W, R)
• Date/Time: DATE, TIME, TIMESTAMP
• BOOLEAN

Information Systems Program

Summary
• Important definitional statement
• Data types not always portable
• Somewhat tedious specification although
relatively portable
• Other interfaces for more productivity

Information Systems Program