SQL - Create

SQL CREATE is the command used to create data objects, including everything from new databases and tables to views and stored procedures. In this lesson, we will be taking a closer look at how table creation is executed in the SQL world and offer some examples of the different types of data a SQL table can hold, such as dates, number values, and texts.


To accomplish this, it is best to first take a look at the entire CREATE TABLE query and then review each line individually.

SQL Create Table Code:

USE mydatabase;

CREATE TABLE inventory
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   product VARCHAR(50) UNIQUE,
   quantity INT,
   price DECIMAL(18,2)
);

Line 1 identifies the scope of the query specifying a target database for query execution (USE mydatabase) and we've seen it before so let's skip ahead to the next line, line 2 (CREATE TABLE inventory). This line informs SQL of the plan to create a new table using the CREATE clause and specifies the name of the new table (inventory). In this case, we plan on creating an inventory table to maintain a current inventory of store items for an imaginary e-commerce web site.

SQL Create Line 3:

id INT IDENTITY(1,1) PRIMARY KEY,

Line 3 should appear more foreign as there is a lot of information embedded in this line, but it is not as hard as it seems. This is the first line that declares how to set up the first table column inside the new inventory table.

• id = The name of this new table column.
• INT = The data type. INT is short for integer.

The first word, id, is the name of this new column and the second word declares the data type, INT (integers). SQL will now expect this table column to house only integer data type values.

• IDENTITY (1,1) = The id column will be an identity column.

The next phrase, IDENTITY (1, 1) is a very special attribute and when a table column is marked as an identity column, the column essentially turns into an automated counter. As new rows are inserted into the table, this column value will automatically increment (count up). The parameters (1,1) tell SQL which number to start counting from and by how many to increment each value. In this case, we'll start with 1, and increment by 1 each time a new row is inserted into our database. For example, the first INSERT command run against the inventory table will have an id value of 1, and each consecutive row inserted thereafter will increment by one (1, 2, 3, 4 ... etc). This identity table column is essentially counting each inserted row and also ensuring that we have a unique identifier value. This is important since this column has also been identified as a primary key (see below).

• PRIMARY KEY = This places a restraint on this column (no duplicate values)

Bringing up the tail-end of Line 3 is reserved for specifying any unique attributes to associate with this table column. In this case, we have told SQL that this column will act as the PRIMARY KEY for the inventory table. Declaring this column as the PRIMARY KEY places a restraint on this column meaning no duplicate values may exist in this column and SQL will throw an error message if an attempt is made to enter duplicate data. Since this row is set to automatically increment each time a new record is added, we know that this column will always be a unique value.

SQL Create Line 4:

product VARCHAR(50) UNIQUE,

Line 4 specifies the name and type of the second column in the inventory table. Product stands for the inventory table product name and this column is set as a VARCHAR(50), which means it will be able to handle numbers, letters, and special characters as values. In other words, "Any words, numbers, or special characters can be placed into this column value, with a 50 character limit."
The UNIQUE attribute tells SQL that this table column must be a UNIQUE value at all times. This restraint will stop us from accidentally inserting duplicate records for the same product, which will serve as an aid to us to help maintain data integrity.

SQL Create Line 5,6:

quantity INT,
   price DECIMAL(18,2)

Now that you are more familiar with the structure of this query, lines 5 and 6 should look less like a foreign language and more like SQL code. These lines are creating two more table columns: quantity and price. Since the price column will be dealing with decimals, we have set this column to a DECIMAL data type to handle decimals (sometimes called floating point numbers).
And there you have it, here's another look at the query:

SQL Create Table Query:

USE mydatabase;

CREATE TABLE inventory
(
   id INT IDENTITY(1,1) PRIMARY KEY,
   product VARCHAR(50) UNIQUE,
   quantity INT,
   price DECIMAL(18,2)
);

This SQL command will create a new, empty table called inventory, where we will begin to capture store inventory data to keep track of the price and current stock of items for our make believe online store.
At this point, this table has been created but remains empty, containing no data. Let's go ahead and add some records into this table so that we can then use this table to further our learning of SQL. Since we are now already familiar with the INSERT command, we can run the following commands all at once, so feel free to copy and paste this code into the query window and execute.

SQL Insert Into:

USE mydatabase;

INSERT INTO inventory
VALUES('19" LCD Screen','25','179.99');
INSERT INTO inventory
VALUES('HP Printer','9','89.99');
INSERT INTO inventory
VALUES('Pen','78','0.99');
INSERT INTO inventory
VALUES('Stapler','3','7.99');
INSERT INTO inventory
VALUES('Hanging Files','33','14.99');
INSERT INTO inventory
VALUES('Laptop','16','499.99');

Successful execution of the above query should yield messages indicating that the queries have run successfully.

SQL Results:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

We can double-check the results by running a SELECT (*) query and doing so will retrieve all records SQL has stored inside the inventory table.

SQL Code:

USE mydatabase;

SELECT *
FROM inventory;

SQL Results:

id	product	quantity	price
1	19" LCD Screen	25	179.99
2	HP Printer	9	89.99
3	Pen	78	0.99
4	Stapler	3	7.99
5	Hanging Files	33	14.99
6	Laptop	16	499.99

In creating this new table with data that relates to data inside the orders table, you have unknowingly created a relational database. We can now take a list of items ordered by our customers and verify that these items are in stock as purchases continue to flow in, so long as we maintain an up-to-date inventory table.
This is terrific news as you are now well on your way to take your SQL programming skills to the next level!

Posted in: SQL