|Lesson 3|| How to create a table|
|Objective||Learn 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.
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 NULL, in place of
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
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
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.