What is a Database? (Part 4 of 4)

Related Articles

Views

When you submit a query to an SQL database using SQL, the database will consult its data dictionary and access the tables you have requested data from. It will then put together a "view" based upon the criteria you have defined in your SQL query.

A "view" is essentially a dynamically generated "result" table that is put together based upon the parameters you have defined in your query. For example, you might instruct the database to give you a list of all the employees in the EMPLOYEES table with salaries greater than 50,000 USD per year. The database would check out the EMPLOYEES table and return the requested list as a "virtual table".

Similarly, a view could be composed of the results of a query on several tables all at once (sometimes called a "join"). Thus, you might create a view of all the employees with a salary of greater than 50K from several stores by accumulating the results from queries to the EMPLOYEES and STORES databases. The possibilities are limitless.

By the way, many databases allow you to store "views" in the data dictionary as if they were physical tables.

Basics of an SQL Query

As we have already alluded to, a "query" is a structured request to the database for data. At its core, a query is something like, "Hey, give me a list of all the clients in the CLIENTS table who live in the 213 area code!"

Or, in more specific terms, a query is a simple statement (like a sentence) which requests data from the database.

Much as is the case with English, an SQL statement is made up of subjects, verbs, clauses, and predicates.

Let's take a look at the statement made above. In this case, the subject is "hey you database thing". The verb is "give me a list". The clause is "from the CLIENTS table". Finally, the predicate is "who live in the 213 area code."

We'll explain the code later, but let me show you what the above statement might look like in SQL:

SELECT * FROM CLIENTS WHERE area_code = 213
  • SELECT = VERB = give me a list
  • FROM CLIENTS = CLAUSE = from the CLIENTS table
  • area_code = 213 = PREDICATE = who live in the 213 area code

Data Types

Okay, we are about to go into the details of SQL queries, but before that we should say one last thing about SQL database structures. Specifically, most databases store their data in terms of data types. Defining data types allows the database to be more efficient and helps to protect you against adding bad data to your tables.

There are several standard data types including

Type Alias Description
CHARACTER CHAR Contains a string of characters. Usually, these fields will have a specified maximum length that is defined when the table is created.
NUMERIC NONE Contains a number with a specified number of decimal digits and scale (indicating a power to which the value should be multiplied) defined at the table creation.
DECIMAL DEC Similar to NUMERIC except that it is more proprietary.
INTEGER INT Only accepts integers
SMALLINT NONE Same as INTEGER except that precision must be smaller than INT precisions in the same table.
FLOAT NONE Contains floating point numbers
DOUBLE PRECISION NONE Like FLOAT but with greater precision

It is important to note that not all databases will implement the entire list and that some will implement their own data types such as calendar or monetary types. Some fields may also allow a NULL value in them even if NULL is not exactly the correct type.

Okay, we will explain data types when we actually start using them, so for now, let's go on to some real examples of doing things with SQL. Let's log on to a database and start executing queries using SQL.

Logging on to the Database

It is important to note that for most databases, you will actually need to log on. That is, most databases implement a security system that gives various users different privileges to do different things such as READ ONLY or READ/WRITE.

Most likely, your database administrator will have provided you with a login name and a password. She will also have either provided you with the net address of the database so you can log in remotely, or has given you a direct command line shell.

Once you are logged in to a database, you can begin to populate the database with data or extract already existing data. Let's look first at populating the database.

A Sample Database

Okay, let's define a simple relational database that we can use to practice with...

We will define a database called "MY_COMPANY" with four tables, "CLIENTS", "EMPLOYEES", "PRODUCTS", and "SALES". These tables will look something like the following:

EMPLOYEES Table
EMP_NUM EMP_NAME EMP_COMMISSION EMP_SALARY
001 Lim Li Chuen 10% 90,000 USD
002 Lim Sing Yuen 20% 40,000 USD
003 Loo Soon Keat 20% 50,000 USD

 

CLIENTS Table
C_NUM C_NAME C_ADDR C_CITY C_STATE C_ZIP C_PHONE
001 Jason Lim 100 W 10th St LA CA 90027 456-7890
002 Rick Tan 21 Jack St LA CA 90031 649-2038
003 Stephen Petersen 1029#A Kent Ave. LA CA 90102 167-3333

 

PRODUCTS Table
P_NUM P_QUANTITY P_PRICE
001 104 99.99
002 12 865.99
003 2000 50.00

 

SALES Table
S_NUM P_NUM S_QUANITY S_AMOUNT E_NUM C_NUM
001 001 1 99.99 101 102
002 001 2 199.98 102 101
003 002 1 865.99 101 103

Creating Databases

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 understands SQL.

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.

MS Access Create Database

In Access, as will be the case in most databases, once you create a database, you will then be asked to define the database structure.

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.

MS Access table

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 called MY_COMPANY.

CREATE DATABASE MY_COMPANY;

Creating Tables

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 CREATE TABLE command to create a new table.

This command looks like the following:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE,
                         COLUMN_NAME DATA_TYPE,
                         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, and the 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,
                       P_QUANTITY INT,
                       P_PRICE DECIMAL(8,2))
       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 the column.

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 some tables.

Deleting Databases and Tables

It is also simple to delete tables and databases using GUI tools or SQL. In the case of a GUI, you will simply select a table or database from your main menu and delete it as you would a file in a file system manager.

In SQL, you would simply use the DELETE or DROP commands depending on if you were deleting a whole database or just a table in a single database.

In the case of deleting a whole database, you will use the DELETE command as follows:

DELETE DATABASE DATABASE_NAME;

The following example would delete the database MY_COMPANY:

DELETE DATABASE MY_COMPANY;

In the case of a table, you use the DROP command:

DROP TABLE TABLE_NAME;

such as:

DROP TABLE EMPLOYEES;

Essentially when you use delete and drop, you are modifying the database management system's data dictionary. It shouldn't have to be said, but I will say it...BE CAREFUL WHEN DELETING OR DROPPING!

Altering a Table

Finally, you should know that it is possible to "alter" a table after it has been created using either a standard GUI tool or by using the ALTER SQL command as follows:

ALTER TABLE  TABLE_NAME
      DROP   COLUMN_NAME, COLUMN_NAME
      ADD    COLUMN_NAME DATA_TYPE, COLUMN_NAME DATA_TYPE
      RENAME COLUMN_NAME NEW NAME
      MODIFY COLUMN_NAME DATA_TYPE;

such as the following case in which we alter the table named EMPLOYEES by dropping the E_GENDER Column and adding an E_ZIP column which will accept INTEGERS and which must be filled in for every new employee added to the table, and the E_MIDDLE_INIT column which will accept a single character as a value.

ALTER TABLE EMPLOYEES 
      DROP  E_GENDER
      ADD   E_ZIP INTEGER NOT NULL, 
            E_MIDDLE_INIT CHAR (1);

Publication Date: Friday 1st August, 2003
Author: Selena Sol View profile

Related Articles