QUERY your data

A short but practical introduction about Query Tools [ plus some basics of SQL ( Structured Query Language ) ]
 

Esp. suitable for beginners is

WinSQL32 from:    www.indus-soft.com

The lite version is FREE (and totally sufficient for our target group)

The most versatile Query Tool we have ever seen for our target group. It works perfect with the files from our applications.

Download the documentation from their site in .HTML, .DOC or .PDF format (MainDoc)

It is also an excellent introduction about setting up ODBC (Open Data Base Connectivity) on your computer.

There is also an interesting shareware package available from George Paulose: QTODBC which is available from the Internet.
It has similar features like WinSQL (the evaluation period is 70 days after which you are required to register for US $ 7.--). This package gives you also plenty of information about the ODBC drivers you use.

We recommend to compare both packages.
 

Querying their data is for most people a fairly complex undertaking. With this item we want to take out some of the obstacles and do it "step-by-step".

Data is the information kept in the records you have gathered over time, like the address details for your customers.

You can actually do a lot of things with this data, for example: you can retrieve "information" from existing data to make better decisions for your business. Information based on existing data could be: the profit you make with your customers in a certain suburb of the city. So - if there is a way to retrieve this information from the records in your database it would be beneficial for your business to compare several suburbs and then focus on those who are the most profitable ones.

If you have only a few dozens of customers you will probably do it easier with your calculator or manually  .... but the story is quite different if you have some hundred customers on file.
 

What is SQL ?

SQL is the "Structured Query Language" which allows you to retrieve information from a database (which in turn may contain several tables). Esp. with WinSQL it is easy to use (and the results with a bigger data volume go far beyond what could be achieved otherwise).

SQL works with ODBC data. In this case the ODBC drivers should be installed on your system.
Please read our page dealing with the installation of the ODBC drivers at:     odbc.html

The introduction text from the IndusSoft web site (MainDoc.doc, MainDoc.html or MainDoc.pdf) starts with a very easy to follow instruction about how to set up ODBC on your computer.

Go to their web site at:  www.indus-soft.com

and then select PRODUCTS from the menu.

One of the most frequently used queries goes like the following:

SELECT  Name, First Name, Suburb
FROM  Customer
WHERE  Suburb =  'Kensington'

This is called the SELECT statement.

Some VERY SIMPLE SQL statements you can use with WinSQL and other similar tools with data from our applications:

Start WinSQL, click on FILE - NEW and then select the "ODBC data source" which you have already set up - preferably in folder: c:\tbsc\odbcfiles\

Remember to set up the DATE fields properly as described in our ODBC item:   odbc.html

Now in the QUERY tab just type in the following line as listed below:

select address_id from unnamed where city = 'Verona'

and then click on the button in the MENU BAR with the GREEN arrow on it, the query is executed
and only the address_id of  CARAMIA is listed. Try it with other cities.

select address_id from unnamed where city = 'Stuttgart'

Now there should be 3 address_id be listed (when using our test data)

address_id     is one of the fields in a table
unnamed        is the name of the table
city                is the criteria you are searching for

With the SELECT statement you select one (or more) fields from one (or more) tables where one
(or more) conditions are met based on field contents.

Simple SELECT statements are mostly sufficient for our target group. However you can create more demanding SELECT statements if you wish - for example:

select address_id, name_company, firstname, category_id, city, suburb
from unnamed
where city = 'Stuttgart' and userdef07 >= 1000.0000

This lists you the ID, Name, First Name, Business Category, City, Suburb from the table with the
name UNNAMED where the City field contains the value 'Stuttgart' and where the user-defined
amount field is equal or greater 1000

or from our test data (please check out if the data actually exists in the fields - the test data might
become incomplete after a while during testing):

select address_id from unnamed where city = 'Verona' and userdef07 > 100.0000

Use the Catalog tab to explore the tables in the database (default name if there is only one table is: UNNAMED)

Using WinSQLs "SQL Wizard" (accessible from the VIEW menu) is a big help - however some smaller things have to be considered, for example when using DATE fields. First you will see a list of  tables to choose from, then you start to develop the query by ticking the fields in the list. The first and second line of the SELECT statement ('select' and 'from') are filled in automatically. Then you build up the rest of the SELECT statement - the 'where' clause - by selecting from 'CRITERIA' the field name, the operator and the value.

The SELECT statement created by the SQL Wizard looks like below:

select UNN.ADDRESS_ID, UNN.NAME_COMPANY, UNN.FIRSTNAME, UNN.DATEOFBIRTH
from UNNAMED UNN
where (UNN.DATEOFBIRTH >= 1959-12-05)

Because date fields are such a tricky thing we have to make some alterations to the 'where' clause:

select UNN.ADDRESS_ID, UNN.NAME_COMPANY, UNN.FIRSTNAME, UNN.DATEOFBIRTH
from UNNAMED UNN
where (UNN.DATEOFBIRTH >= {d '1959-12-05'})

{d '    before the date and   '}  after the date.

There is a 'blank' (space) between the  d   and the  ' .

Now it will work perfectly !
 

We are using the following resources and recommend to visit these web sites for further information  ...

       www.kdnuggets.com