Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Aug 18, 2000

Simple SQL: Pt. 1 - Page 2

By Ted Brockwood

The SELECT Query

Now, let's return to the mission given to you at the beginning of this article to see how SQL can help you.

The company database is not as old as you think, and much to your joy, it is SQL-92 compliant. This is great as it allows you to exploit more advanced SQL features. Let's say your first mission is to find any customers with the last name of "Grayson".

To view or extract data from a table, you will run a SELECT query. As the name implies, you are "selecting" specific data from the table and viewing it. SELECT queries are fairly obvious.

Assuming your database table (called CUSTOMERS) looks like this:

CUST_IDFIRST_NAMELAST_NAMELAST_PURCHASE
1JoeSmithDecember 5, 1996
4TomGraysonJanuary 3, 1999
22JoelJonesOctober 1, 1988

You would run a query like this:

SELECT * FROM CUSTOMERS
       WHERE LAST_NAME = "Grayson";

And get these results:

CUST_IDFIRST_NAMELAST_NAMELAST_PURCHASE
4TomGraysonJanuary 3, 1999

Let's assume that, as is always the case, someone might have mis-keyed the last name during input. Perhaps they keyed in "Greyson" or "Graysun". How can you account for this possible error? With the power of wildcards of course!

Assuming the same table of data as before, your query might look like this:

SELECT * FROM CUSTOMERS
        WHERE LAST_NAME LIKE "Gr*";

This will return all the data on any customers with a last name beginning with "GR". The "*" (asterisk) wildcard is very common, however, you will find some SQL applications that use "%" (percent sign) in place of, or in conjunction with the "*" character. Microsoft Access97, for example, has no problem with using "*", but MySQL for Linux uses both, and I've found it best to use "%" in MySQL. Check with your application's help files for the proper wildcard to use. For ease and consistency throughout this, I will be relying on "*" as the wildcard.

The boss now returns to your desk, pleased that you found the name of the contact he lost the business card for. Just to be a pain though, he decides he doesn't need anything more than the first and last name of Mr. Grayson. You can kill him, or you can modify your query to select only the fields you need.

Again, assuming the same CUSTOMERS table, your query will now look like this:

SELECT FIRST_NAME, LAST_NAME FROM CUSTOMERS
       WHERE LAST NAME LIKE "Gr*";

You will get the same results as before, however the CUST_ID and LAST_PURCHASE information will not display.

Dissecting the SELECT statement will help give you a further understanding of it. As with all statements in SQL, the keywords define the functions to be used, and the parameters to follow.

Example:

(1) SELECT FIRST_NAME  
(2)        FROM CUSTOMERS
(3)        WHERE LAST_NAME 
(4)        LIKE "Gray*"
(5)        ;
  1. The SELECT keyword tells the system what type of query you are going to run. The field after it (FIRST_NAME) tells it what field you want to view.

  2. The FROM keyword lets you choose the database table you are going to examine.

  3. WHERE is setting the criteria, or parameters for the data within the field.

  4. LIKE sets the conditions of the query, whether it is an exact match or a close match.

  5. The ";" closes out and executes the statement. Some SQL variants may use "\g" or even simply the word "go" to execute a statement.

The previous SELECT examples, while useful for learning, probably won't help you much in the real world. Most SELECT statements are far larger and more complex than what we've worked with so far. So we need to dive into comparisons and more sophisticated keyword usage.



SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM