SQL - Tables

Data is stored inside SQL tables which are contained within SQL databases. A single database can house hundreds of tables, each playing its own unique role in the database schema. While database architecture and schema are concepts far above the scope of this tutorial, we plan on diving in just past the surface to give you a glimpse of database architecture that begins with a thorough understanding of SQL Tables.


SQL tables are comprised of table rows and columns. Table columns are responsible for storing many different types of data, like numbers, texts, dates, and even files. There are many different types of table columns and these data types vary, depending on how the SQL table has been created by the SQL developer. A table row is a horizontal record of values that fit into each different table column.

SQL - Create a SQL Table

Let's now CREATE a SQL table to help us expand our knowledge of SQL and SQL commands. This new table will serve as a practice table and we will begin to populate this table with some data which we can then manipulate as more SQL Query commands are introduced. The next couple of examples will definitely be overwhelming to novice SQL programmers, but we will take a moment to explain what's going on.

SQL Create Table Query:

USE mydatabase;

CREATE TABLE orders
(id INT IDENTITY(1,1) PRIMARY KEY,
customer VARCHAR(50),
day_of_order DATETIME,
product VARCHAR(50),
quantity INT);

The first line of the example, "USE mydatabase;", is pretty straightforward. This line defines the query scope and directs SQL to run the command against the MyDatabase object we created earlier in the SQL Databases lesson. The blank line break after the first command is not required, but it makes our query easier to follow. The line starting with the CREATE clause is where we are actually going to tell SQL to create the new table, which is named orders.
Each table column has its own set of guidelines or schema, and the lines of code above contained in parenthesis () are telling SQL how to go about setting up each column schema. Table columns are presented in list format, and each schema is separated with a comma (,). It isn't important to fully understand exactly what all of these schema details mean just yet. They will be explained in more detail throughout the remainder of the tutorial. For now, just take note that we are creating a new, empty SQL table named orders, and this table is 5 columns wide.

SQL - INSERT DATA into your New Table

Next, we will use SQL's INSERT command to draw up a query that will insert a new data row into our brand new SQL table, orders. If you're already familiar with everything we've covered so far, please execute the query below and then skip ahead and start learning about other SQL Queries.

SQL Insert Query:

USE mydatabase;

INSERT INTO orders 
(customer,day_of_order,product, quantity)
VALUES('Tizag','8/1/08','Pen',4);

SQL Insert Query Results:

(1 row(s) affected)

This message ("1 row(s) affected") indicates that our query has run successfully and also informs us that 1 row has been affected by the query. This is the desired result as our goal was to insert a single record into the newly formed orders table.
Listed above is a typical INSERT query used to insert data into the table we had previously created. The first line ("USE mydatabase;") identifies the query scope and the line after indicates what it is we'd like SQL to do for us. ("INSERT INTO orders") inserts data into the orders table. Then, we have to list each table column by name (customer,day_of_order,product, quantity) and finally provide a list of values to insert into each table column VALUES('Tizag','8/1/08','Pen',4).
You may notice that we have not included the id column, and this is intentional. We have set this column up in a way that allows SQL to populate this field automatically, and therefore, we do not need to worry about including it in any of our INSERT statements. (More on this later.)

Posted in: SQL