Article Index
SQL Tutorial
Microsoft SQL Server
Stored In The Master Database?
Installing A Production Data Server
SQL Server B (Middle Of The Road)
SQL Commands
SQL BETWEEN
SQL Aggregate Functions
SQL ALIAS
SQL Subquery
SQL INTERSECT
Table Manipulation
SQL CREATE INDEX Statement
SQL PRIMARY KEY
SQL UPDATE Statement
Advanced SQL
SQL Running Totals

 SQL CREATE TABLE Statement

Tables are the basic structure where data is stored in the database. Given that in most cases, there is no way for the database vendor to know ahead of time what your data storage needs are, chances are that you will need to create tables in the database yourself. Many database tools allow you to create tables without writing SQL, but given that tables are the container of all the data, it is important to include the CREATE TABLE syntax in this tutorial.

Before we dive into the SQL syntax for CREATE TABLE, it is a good idea to understand what goes into a table. Tables are divided into rows and columns. Each row represents one piece of data, and each column can be thought of as representing a component of that piece of data. So, for example, if we have a table for recording customer information, then the columns may include information such as First Name, Last Name, Address, City, Country, Birth Date, and so on. As a result, when we specify a table, we include the column headers and the data types for that particular column.

So what are data types? Typically, data comes in a variety of forms. It could be an integer (such as 1), a real number (such as 0.55), a string (such as 'sql'), a date/time expression (such as '2000-JAN-25 03:22:22'), or even in binary format. When we specify a table, we need to specify the data type associated with each column (i.e., we will specify that 'First Name' is of type char(50) - meaning it is a string with 50 characters). One thing to note is that different relational databases allow for different data types, so it is wise to consult with a database-specific reference first.

The SQL syntax for CREATE TABLE is

CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )

So, if we are to create the customer table specified as above, we would type in

CREATE TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

 SQL CREATE VIEW Statement

Views can be considered as virtual tables. Generally speaking, a table has a set of definition, and it physically stores the data. A view also has a set of definitions, which is build on top of table(s) or other view(s), and it does not physically store the data.

The syntax for creating a view is as follows:

CREATE VIEW "VIEW_NAME" AS "SQL Statement"

"SQL Statement" can be any of the SQL statements we have discussed in this tutorial.

Let's use a simple example to illustrate. Say we have the following table:

TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

and we want to create a view called V_Customer that contains only the First_Name, Last_Name, and Country columns from this table, we would type in,

CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer

Now we have a view called V_Customer with the following structure:

View V_Customer
(First_Name char(50),
Last_Name char(50),
Country char(25))

We can also use a view to apply joins to two tables. In this case, users only see one view rather than two tables, and the SQL statement users need to issue becomes much simpler. Let's say we have the following two tables:

Table Store_Information

Table Geography

and we want to build a view that has sales by region information. We would issue the following SQL statement:

CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name

This gives us a view, V_REGION_SALES, that has been defined to store sales by region records. If we want to find out the content of this view, we type in,

SELECT * FROM V_REGION_SALES

Result: