What is a Database? (Part 4 of 4)
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
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
area_code = 213= PREDICATE = who live in the 213 area code
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
|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
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.
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, "
PRODUCTS", and "
SALES". These tables will look something like the following:
|001||Lim Li Chuen||10%||90,000 USD|
|002||Lim Sing Yuen||20%||40,000 USD|
|003||Loo Soon Keat||20%||50,000 USD|
|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|
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.
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.
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
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
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
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
DELETE DATABASE MY_COMPANY;
In the case of a table, you use the DROP command:
DROP TABLE TABLE_NAME;
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);