This SQL example shows how to use a simple statement to get the customer names of customers that have the same shipping address as their invoice address, and who have a credit limit less than $4,000. Figure 1 shows a list of customers and their details such as Name, Customer Number, Invoice Addess, Shipping Address etc. The basic format of the SQL statement is:
SELECT
FROM
WHERE
ORDER BY
In other words, "which information needs to be found", "which table is it in", and "what restrictions are there".
The SQL SELECT Command
SELECT defines which of the information in the data set to return. To return all of the columns in the table the syntax is:
SELECT *
If all that is needed is the name and number of the customer, then the format is
SELECT CUSTNAME, CUSTNUM
The SQL FROM Command
This line of the SQL statement defines which table contains the information that is needed. If the table that contains the customer details is called CUSTOMER, then the syntax of the statement is:
FROM CUSTOMER
Sometimes the information needs to come from two or more tables. If the table containing order details is called ORDERS, and the user needs to know what ITEM has been ordered, the SELECT and FROM statements change slightly to:
SELECT A.CUSTNAME, A.CUSTNUM, B.ITEM
FROM CUSTOMER A, ORDERS B
The FROM line declares the CUSTOMER details table to be table A, and the orders table to be table B.
The SQL WHERE Command
The SQL WHERE line is used to prevent every line (or record) in the table being listed. In this example, all that should be returned is customers whose invoice address and shipping address are the same, and whose credit limit is less than $4,000. The syntax is:
WHERE INVADDRESS = SHIPADDRESS
AND CREDIT < 4000
These two lines will eliminate customers W. Clinton (who has an invoice address that is different to his shipping address), and G. Ford and J. Kennedy, who have credit limits greater than $4,000.
The WHERE command is versatile and allows for many other ways to limit the records returned, such as:
CUSTNUM IN ('123-0001','123-0002')
CUSTNAME LIKE 'A%'
These mean "the customer number can be 123-0001 or 123-0002", and "the customer name should begin with A".
The SQL ORDER BY and GROUP BY Commands
The ORDER BY and GROUP BY commands are used to indicate how the data should be presented or sorted. These are not mandatory parts of the SQL statement, but they are useful. The syntax is as follows:
ORDER BY CUSTNUM
It is also possible to sort in descending order with
ORDER BY CUSTNUM DESC
The GROUP BY command follows the same format.
Summary of Basic SQL Commands
This SQL tutorial explains how the SELECT, FROM, WHERE, ORDER BY, and GROUP BY commands are used. The examples show what values are returned when the options described are used. SQL is so widely used - either as a stand-alone query language or as embedded code in other applications such as statistical analysis software such as JMP, that a good knowledge of it is extremely useful to any programmer. The commands described here are the most basic used, and more advanced query commands can be found here. A list of functions to manipulate strings is also available.
Martin Bell - Martin holds a B.Sc. degree in chemical engineering, and an M.Sc. degree in electronics and computing. He has spent more than 25 years ...
Join the Conversation