SQL Foundations  «Prev  Next»
Lesson 3 How to create a table
ObjectiveLearn to Create an SQL Table and how Table structure affects SQL.

Create Table using SQL

Although we will not go into all the specifics of creating tables, it is important to understand what you need to know before you create a table, and to see the basic statement that you use to create the table. Understanding what these statements look like will be instrumental in querying the database at a later time. The structure of a table, the names and types of columns that it includes determines how your will structure your sql query.
For example, let us look at a simple table definition:

CREATE TABLE BasicTable (
 Firstname   char (50) NULL ,
 Lastname    char (50) NULL ,
 PhoneNumber char (10) NULL 
)

This would produce a table that contains the following types of columns:
Column Name FirstName LastName PhoneNumber
Column Type text, 50 characters text, 50 characters alpha-numeric, 10 characters
Column Description Contact's first name Contact's last name Contact's phone number

You can see how this statement (the act of table creation or creating the table) maps to the design and layout shown above. The column names are called out, and the size of the columns, or the maximum number of characters allowed, is indicated.
The NULL indicates whether we allow a blank value for the given column. If the keyword NULL is provided, it means that we allow NULL values in the column. If you want to prevent NULLs in this column, include the NOT keyword, NOT NULL, in place of NULL.
We will examine the CREATE TABLE statement more closely in the next lesson.

Operators of the Relational Algebra

The operators of the relational algebra allow us to start with some given relations and obtain further relations from those given ones (for example, by doing queries). The given relations are referred to as base relations, the others are derived relations. In order to get us started, therefore, a relational system has to provide a means for defining those base relations in the first place. In SQL, this task is performed by the CREATE TABLE statement (the SQL counterpart to a base relation being a base table, which is what CREATE TABLE creates).
The base relations obviously need to be named:

CREATE TABLE S ... ;
But certain derived relations, including in particular what are called views, are also named. A view (also known as a virtual relation) is a named relation whose value at any given time t is the result of evaluating a certain relational expression at that time t. Here is an SQL example:
CREATE VIEW SST_PARIS AS
( SELECT SNO , STATUS
FROM S
WHERE CITY = "Paris" ) ;

In principle, you can operate on views just as if they were base relations, but they are not base relations. Instead, you can think of a view as being materialized .
You can think of a base relation being constructed, whose value is obtained by evaluating the specified relational expression, at the time the view in question is referenced. But I must emphasize that thinking of views being materialized in this way when they are referenced is purely conceptual and is just a way of thinking.