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").
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
By the way, many databases allow
you to store "views" in the data dictionary as if they were
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
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
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
||Contains a string of characters.
Usually, these fields will have a specified maximum length that is defined
when the table is created.
||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.
||Similar to NUMERIC except that it is more
||Only accepts integers
||Same as INTEGER except that precision
must be smaller than INT precisions in the same table.
||Contains floating point numbers
||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
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.