Structured Query Language (SQL) tutorial
Creating tables
In this section of the SQL course we will explore the commands to create database tables and select the database. These are, of course, generic and apply to all RDBMS.
Databases store data in tables. Tables consist of rows and columns, in which each column (field) defines data of a particular type. Rows (records) contain the information about a particular individual thing.
Consider the following:
| Name | Age | Country | |
|---|---|---|---|
| May East | 28 | USA | may@kasabsoftware.net |
| Jane Doe | 52 | Australia | jane@kasabsoftware.net |
| John Smythers | 21 | Australia | johns@kasabsoftware.net< |
| Antoni Person | 49 | USA | antoni@kasabsoftware.net |
The table above contains four columns that store the name, age, country and email. Each row contains data for one individual. This is called a record. To find the country and email of Jane Doe, you'd find the name in the first column and then look in the third and fourth columns of the same row.
The SQL command for creating tables follows. Do not type this yet – just read and familiarize yourself with the syntax.
CREATE TABLE booking
(
booking_id INT IDENTITY NOT NULL PRIMARY KEY,
trip_date DATETIME,
trip_duration INTEGER,
DESTINATION VARCHAR(12),
bus_id INTEGER,
driver VARCHAR(8),
customer VARCHAR(15)
deposit INTEGER
);
Note: In SQL, commands and column names are not case-sensitive; however, table and database names might be sensitive to case depending on the platform (as in Linux). You can therefore use create table instead of CREATE TABLE.
The CREATE TABLE keywords are followed by the name of the table we want to create, ie bus.
Each line inside the parenthesis represents one column. These columns store the booking id (as booking), trip date (as trip_date) and duration (as trip_duration), destination, bus (as bus_id), names of the driver (as driver) and customer (as customer) and the amount of any deposit paid (as deposit).
Why have a column with unique values?
Our company XYZ Transport has grown tremendously over the past two years. We've seen thousands of customers, some with the same name. When that happens, we can distinguish the records of these customers by giving them unique identification numbers. If we have a column with unique values, we can easily distinguish the two records. The best way to assign unique numbers is to let SQL Server do it!
Creating your table
Now let’s start actually creating tables. Once you've selected the employees database, enter the following CREATE TABLE command into the editor window. If you entered the CREATE TABLE command at the top of page 5, you will not need to type the following. If you did not do this previously,
- enter the following command:
CREATE TABLE booking
(
booking_id INT IDENTITY NOT NULL PRIMARY KEY,
trip_date DATETIME,
trip_duration INTEGER,
DESTINATION VARCHAR(12),
bus_id INTEGER,
driver VARCHAR(8),
customer VARCHAR(15)
deposit INTEGER
);
- Click the Run button,
.
Your table will be created in the database and appear on the Database Explorer.