Introduction to Databases for the Web: Pt. 1

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

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.