A crash course in SQL

by Jeff Davis | Jul 17, 2001 7:00:00 AM

Tags: Jeff Davis

Takeaway: Frustrated because you don't know or understand SQL? Here's a crash course to help you get started.

As a tech support professional, you rely on many different skill sets to support your users, including your knowledge of hardware, operating systems, and desktop applications. If knowing how to query a database with SQL isn't in your bag of cybertricks, it's time to knuckle down and learn the basics. Here's a crash course to get you started.

What is SQL?
SQL is the abbreviation for structured query language, and you pronounce it either see-kwell or S-Q-L. Many different versions of SQL exist, but it's the current industry standard query language for requesting information from databases.

SQL's relatively long history began in the mid 1970s, when IBM created structured English query language (SEQUEL). Oracle later introduced SQL as a commercial database application. Most versions of SQL conform to the American National Standards Institute (ANSI) standards, although some "flavors" support different command sets. In this lesson, we'll present syntax that you can use with almost any release of SQL.

Talking to SQL
Most database applications talk to you via fancy screens. You get an attractive layout, and the fields are clearly labeled. When you make changes or deletions, the application says, "Are you sure you want to save these changes?"

SQL, on the other hand, isn’t a fancy application. It sits and waits for commands. When you "talk" to an SQL database program, you have to know its language, its syntax and rules. Once you’ve invested a little bit of time learning the idiosyncrasies of the SQL language, you’ll feel as comfortable entering SQL commands as you do creating and sending e-mail messages.

Show me the records
In SQL, SELECT means "show me." Specifically, it means "show me some records out of some table." Here’s the basic outline of a SELECT statement:
SELECT [some or all of the columns] FROM [one or more tables] WHERE [the records meet some condition]

When you issue a SELECT command, SQL scans the records in the table and displays the results on-screen. The SELECT command’s first argument tells SQL which columns (or fields) to include in the results. To see all of the columns in the table, type an asterisk (*) immediately following SELECT in this form:
SELECT * FROM mytable

Some flavors of SQL let you use the keyword ALL instead of an asterisk. The benefit to selecting all of the columns is that you don't have to know any of the column names in advance. The problem with selecting all of the columns, of course, is that you might have to scroll through a dozen or more columns to find the information you need.

To limit the number of columns in your results, simply list the columns you want in this form:
SELECT column1, column2, column3 FROM mytable

For example, if you want to view the first name, last name, and telephone number columns from your Customers table, you'd enter a command in the form:
SELECT FName, LName, Phone FROM Customers

Controlling the order in which the records appear
In addition to deciding which columns SQL displays, you can also control the order in which those records appear by adding the ORDER BY clause. Just add it to your SELECT statement in the form:
SELECT Column1, Column2, Column3 FROM mytable ORDER BY Column3

You can use more than one key to sort the results of the query. For instance, to sort your customers in alphabetical order by last name and then by first name, you'd issue a command like this:
SELECT Name, LName, Phone FROM Customers ORDER BY Lname,Fname

By default, SQL assumes you want those records in ascending order. If you want to display the records in descending order, you add the keyword DESC to the SQL command in this form:
SELECT Column1, Column2, Column3 FROM tablename ORDER BY Column3 DESC

Using the WHERE clause to specify certain records
We’ve seen how the SELECT command lets you limit the number of columns that appear in your results—you simply list the columns you want to see. But by default, SQL returns all of the records in the table. Most of the time, when you’re querying a table, you want to display records that meet certain conditions or criteria.

In general, to zoom in on a set of particular records, you add a WHERE clause to your SELECT command in the form:
SELECT column1, column2 FROM tablename WHERE [some condition is true about some field]

For instance, suppose you want to display only the records for customers who live in Zip code 10010. Assuming that the Zip_code field is a character field (not numeric), you'd issue a SELECT statement in the form:
SELECT FName, LName, Phone FROM Customers WHERE Zip_code="10010" ORDER BY Lname,Fname

When your SQL engine evaluates this statement, it will return only records in which 10010 appears in the Zip_code field. The ORDER BY clause tells SQL to arrange the records in order by Lname. Notice that you don't have to include Zip_code in the list of fields in order to refer to it in the WHERE clause.

In the WHERE clause, if the field you're checking is numeric, you don't need to include quotation marks around the data. For example, if you want to find customers whose last_purchase field contains a value of 100 or greater, you'd issue a command like this:
SELECT FName, LName, Phone FROM Customers WHERE Last_purchase >=100

Only the beginning
If you have access to an SQL user interface, you can use the statements covered in this lesson to perform rudimentary queries. Of course, SQL offers many other verbs and options for querying and updating your tables. Consult the documentation for your version of SQL for more information. If you'd like to learn more about SQL, please drop us a note and let us know what version of SQL you use and what kinds of queries you're interested in writing.

---

Share your SQL tips
To recommend a great resource for learning SQL or to share your favorite SQL tip, please post a note below or write to Jeff Davis.

---

 

Print/View all Posts Comments on this article

A crash course in SQL Seagull Data Services | 07/16/01

FYI TommieBurchfield@... | 02/13/02

Oops!!! TommieBurchfield@... | 02/13/02

Great FREE on-line SQL course w_dozier@... | 07/17/01

sql maint plan KES22 | 09/18/01

Free SQL server to play with timgray | 07/17/01

sql sacolla@... | 07/17/01

MySQL limitations Dave Brown | 07/18/01

Weird syntax epepke@... | 07/19/01

i would like info. on get the freesql server to learn on. develle21@... | 03/25/04

Another free online SQL learning site daiquiria2@... | 07/18/01

Brinkster.com Bucky Kaufman (MCSD) | 09/18/01

SQL Crash Courses jkm02571@... | 12/05/01

Join In ElectronicArt.com | 02/13/02