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 autoincrement not null primary key,
trip_date datetime,
trip_duration integer,
destination text(12),
bus_id integer,
driver text(8),
customer text(15),
deposit currency
)
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).
Each column name is followed by the column type. This defines the sort of data the column can contain. In our example,
- Columns, destination, driver and customer contain small text strings, so we set the column type to TEXT. The maximum number of characters for text columns is specified by a number enclosed in parenthesis immediately following the column name.
- Columns trip_duration and bus_id contain numbers (integers), so we set the column type to INTEGER. Access interprets this as a Long Integer.
- The deposit column stores details of currency. Access is unusual in using a currency data type. Many other databases use decimal numbers for this.
- The trip_date column will contain date and time values, so we set the column type to DATETIME.
- Our first column (booking_id) contains a booking number.
- autoincrement: specifies that the column type is an integer (a whole number) that increments by one each time a record is added to the database. When Access finds a new record containing a column with an auto_increment attribute, it generates a new value that is one greater than the largest value in the column. Therefore we don't need to supply values for this column. Access generates it for us! Also, it follows that each value in this column will be unique.
- not null: specifies that the value cannot be null (empty); that is, each row in the column would have a value.
- primary key: helps index the column for faster searches. Each value has to be unique.
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 Microsoft Access do it!
Creating your table
Now let’s start actually creating tables. Once you've selected and opened the bus database, run the following CREATE TABLE command using Access’ SQL editor. 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 in Access’ SQL query window, and execute it using the Run button,
.
CREATE TABLE booking
(
booking_id autoincrement not null primary key,
trip_date datetime,
trip_duration integer,
destination text(12),
bus_id integer,
driver text(8),
customer text(15),
deposit currency
);
You will see no change at this point. If you switch to the Tables tab on the Database Window (see the figure below), you will see that the new table exists.
- Select the booking table and
- Click the Design button to view the table’s structure.
You can use this window to verify that your table has the correct structure.