Getting Acquainted with DISTINCT
An excellent keyword to get acquainted with is "
DISTINCT". If you have
data that is duplicated, but you only want to see one instance of it,
DISTINCT is just the ticket.
For example, you have a table named
CUSTOMERS. It is loaded with the names,
addresses, phone numbers, and company names of all your best customers.
Your boss needs to know what companies have been buying from you.
Frequently you have multiple buyers from the same company, perhaps
different divisions, so running a standard
SELECT statement like this:
SELECT FIRST_NAME, LAST_NAME, COMPANY
will return something like this:
|Donna||Sprint||MegaData Systems Corp|
As is obvious, you have two customers from IsoLunacy, Inc in your query
results. If this were thousands of duplicates, it would be a terrible
headache to create a list manually of companies you deal with. So, you
fire off a
DISTINCT query such as this:
SELECT DISTINCT COMPANY FROM CUSTOMERS;
|MegaData Systems Corp|
SELECT statements are also adept at handling comparisons such as greater
than and less than. Imagine the table "
EMPLOYEES" with Names, Addresses,
Start dates, vacation days accrued, and vacation days taken contained
within it. The boss wants to know (for the Human Resources Intranet)
whether anyone has taken more vacation days than they have accrued. Your
SELECT might look like this:
SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES
WHERE VACATION_TAKEN > VACATION_ACCRUED;
The results might look like:
Good information for HR, and bad information for the employee names
resulting from this query.