Category Archives: SQL

The basic SQL select statement

Most modern ERP and accounting information systems are built on SQL databases, and as a Systems Accountant, you must have at least a basic grasp of SQL.

The scope of even a basic grasp is beyond a single article, and there are plenty of resources already out there on the internet (for just one example, see www.w3schools.com), but here’s what you need to know:

SQL select statements

You must be able to use simple SQL select statements to query the system you are working on, in order to quickly and efficiently extract data that is useful to the many data-related tasks you perform as a Systems Accountant.

The most basic form of select statement is:

SELECT * FROM table

With this, you only need to know the name of the table you wish to query. Once you run this script, you will be presented with a list of all the field names in the table, which you can use to cut the query down to concentrate only on the data you are interested in:

SELECT field FROM table WHERE condition = value

for example:

You know your system contains a table called “jobs”, so you run the query:

SELECT * FROM jobs

which returns a large table of data. From that, you can see that the field you are interested in is called “jobname”, and you want to select it based on another field called “jobnumber”. You are now in a position to refine your select statement to something like:

SELECT jobname FROM jobs WHERE jobnumber = 1234

Although this is a trivial example, it explains the sort of process I might go through when I am feeling my way around a new database. One modification I would probably make to my initial query is to limit the number of lines it returns by using the following format:

SELECT TOP 5 * FROM jobs

This will limit the return to five rows, which is often a wise idea when dealing with a large database, or one you suspect contains large amounts of data.

There is only one way of getting to grips with select statements, and that is by using them. If you don’t already do so, you should start using SQL selects at the earliest opportunity. The select statement is non-destructive so you can play around with it as much as you want without causing damage, other than by tying up system resources with statements that return large amounts of data, and to guard against that you can use the TOP clause to limit the number of rows in the return.

Further reading:
Select (SQL)
sql select
where