The Basics of the SQL Database (continued) - Page 8
August 16, 1998
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
Similarly, a view could be composed of the
results of a query on several tables all at once (sometimes called a "join").
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
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:
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
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.