Howto use SQL

by Matthew Craig (article in progress, incomplete)

SQL stands for Standard Query Language, and is used by many databases currently on the market.  SQL is a standard maintained by ANSI. 

A database is a system used to store large amounts of data in an organized way.

Databases are broken into Tables, and each table is broken down into rows and columbs, very much like a spread sheet.

Each table is broken into columbs and rows.  Each columb holds the "datatype" and the rows hold actual entries.

Table
	Columb (Name)	Columb (Address)	Columb (Email Address)
Row1	Data (John Doe)	Data (1313 Mocking Bird Lane)	Data (jdoe@hotmail.com)
Row2	Data (Jane Doe)	Data (14 Everwood Terrace)	Data (jdoe_babe@hotgirl.com)

All Data in a columb must be of the same Data type.

Each and every Database system uses its own Datatypes, Some are more transferable to other database systems than others.

The basic data types are:

• String
• Numeric
• Boolean (Some consider this a form of Numeric)

And they have many subclasses of each one but they will all fall pretty much into those catagories. Check into the specifics for your SQL distrobution or "flavor".

Beginings:

For this text I will create a fake company "CellularCaterpillar" that sells cellualr networking equipment to create a relational database.

Now that we have some basic terms out of the way What is SQL, it is a laguage created for the purpose of communicating data between programs and databases.  The laguage is very simple, but can become imensly complex when adding in features.

So, lets start with the basics.  Here I assume you have a SQL compatable database system installed, that you have adiquate permssions to add/remove/modify data within that database.

Howto request the creation of a database using SQL:

Now that we have database software installed we need to create a database for your project, so here is where we start you connect to your database (see your database softwares documentation for how to do this and enter the following code:

CREATE DATABASE CellularCaterpillar;

This tells the databse software to create a new database called CellularCaterpillar.  This database will have no tables, and no data in it.  That is for us to create.

 

Howto create a table within a database using SQL:

Now before we create a table we need to know what type of data it will hold. In this table we want to hold the list of vendors. Here is the information that we want to keep track of:

• Account Number
• Vendor Name
• Vendor Address
• Contact Name
• Contact Phone Number
• Account Balance

Those are the pieces of information that we need to keep track of for our accountant.  But there is information that we will need to keep track of on the programing side.  It is good practice to create a primary key for every table. A Primary Key is a unique number associated with each entry that can be used to possitively identify that entry. So we will create an entry for that as well we will call it "Vendor_Key".

Now that we know what fields we need, we need to decide the datatype for each feild. So we decide that all of them except Account Balance and Vendor Key, should be text, and that Account balance should be a float point number, and that Vendor Key should be an integer. So lets create the table:

CREATE TABLE Vendor_List (
	Vendor_Key     INTEGER,
	Account        CHAR(30),
	Name           CHAR(30),
	Address        CHAR(30),
	Contact        CHAR(45),
	Phone          CHAR(20),
	Balance        FLOAT

);

So, lets analize what we just typed and go from there. We used the key word "CREATE" to tell the database it is going to create something, we used the key word "TABLE" to tell it that it was going to be creating a table then we gave that new table a name "Vendor_List". The syntax here is

CREATE TABLE table_name (

    variable_name       VARIABLE_TYPE,

    next_variable       VARIABLE_TYPE

);

Please note the comma after the first variable type, and not after the last. You do not have to have the underscore in the names, you can use any naming convention you like but do not use a " " space.