Okay, as we have said before, it would be too difficult for
us to cover how to install and configure all of the myriad of relational
databases around, so it is your job to get something installed on your
local system or to arrange with your systems administrator to give you access
to an existing database system.
However, for the purposes of example,
I am going to use Microsoft Access as an example of an SQL
database. Microsoft Access comes with Microsoft Office and
is a good database to practice with since it is available for
Mac, Windows and UNIX and is a relational-based database that
|Note: Stephan Wik pointed out that I made a
blunder here. Apparently, Access is not a Mac-friendly App. Doh!
Microsoft blows it again. Stephen also noted that Mac Dbs include
Filemaker, Butler and Oracle. Fortunately, Oracle supports DBI. I am
not sure about Filemaker and Butler, so you would have to contact those
companies and ask them for the Perl DBI driver.
Of course, I wouldn't necessarily use Access
for a web application, because...well...because it is a
Microsoft product. But it is pretty useful for practicing and
demonstrating SQL since it is pretty ubiquitous. Regardless,
in these days of mature software applications, whichever database you
use, will have a process that is similar to the one I will
describe for Access.
Specifically, to get a database working, you will
1) install the application on the host computer (insert disk A,
click install.exe), 2) create a database according to the instructions of the
database application you install, 3) populate your database with tables, and
4) populate your tables with data.
In the case of Access and many other database
systems around these days, the process of creating databases and tables
is pretty much handled by Wizards and GUI tools. Thus, you rarely need
to use SQL for such operations. More likely, you would choose
something like "FILE|NEW DATABASE" from the main menu.
Below is an example of the Wizard used by
Microsoft Access. In this case, we have chosen "File|New Database"
from the main menu at the top of the application window and then
we double click "Blank Database". Of course, although Access and other
database systems offer template databases for your convenience, we
will create our own for practice.
In Access, as will be the case in most databases,
once you create a database, you will then be asked to define the
Specifically, you will be asked
to define some tables in your database (and perhaps other more advanced tools
like Macros, Indexes, Views, Forms, Queries, etc). Below is a screen shot from
Access that offers a series of database definition tabs.
To create a table, simply choose "New" from the
"Tables" tab and follow instructions for defining your fields. Nothing
could be simpler. Hopefully, now that you understand what the database
is doing in the background, you will be easily able to understand what you need
to do to get it working.
However, even though GUIs are pretty swank
these days, it is probably a good idea to learn
the SQL to which is being used in the background to create the database.
Specifically, you use the
CREATE command to create a database
such as in the following example
CREATE DATABASE DATABASE_NAME;
We might use the following code to create a database
CREATE DATABASE MY_COMPANY;
Once you have created your database you can then
start populating it with tables. In the case of Access, as you saw on
the last page, creating tables is as easy as clicking "New" in the table
tab of the "Database" tab.
However, you should know that in the background,
Access, and other GUI database systems are using the
command to create a new table.
This command looks like the following:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE,
IN DATABASE DATABASE_NAME
For example, you might see the
following SQL code to create a table called
PRODUCTS with three
columns in the
MY_COMPANY database we just created. Note that the
three columns would be
P_NUM which would be an integer value and could
not be null, the
P_QUANTITY which would also accept integers as values,
P_PRICE column which would accept decimal numbers
with 8 digits before and 2 digits after the decimal point.
CREATE TABLE PRODUCTS (P_NUM INT NOT NULL,
IN DATABASE MY_COMPANY;
Notice that as we mentioned before, when you
create a table, you must specify the data type for each column.
Notice also that you may use the "
NOT NULL" keyword to tell the
database that it should not allow any
NULL values to be added to
As a final note, I would like to mention
that you can also typically create Views, Indexes, and Synonyms,
however, those topics are beyond the scope of this tutorial since
you will most likely not be doing database administration types of
activities. For most web development work, it is simply enough to define