HearAid Version 5.0

SQL Syntax

SQL Syntax

Previous topic Next topic  

SQL Syntax

Previous topic Next topic  

SQL stands for Structured Query Language and is a "standard" language for manipulating data in databases; most database manufacturers implement their own proprietary extensions to the standard language forms. Firebird is highly compliant with the SQL-92 standard. Firebird's implementation of the SQL-92 standard is described in the file Firebird_v1.5.ReleaseNotes.pdf which is installed in the main Firebird folder.

HearAid users will be primarily interested in using SQL to analyse their database. Database analysis makes use of the SQL SELECT command, which has the basic form (brace yourself!!):

SELECT

[DISTINCT | ALL]

{* | <val> [, <val> …]}

FROM <tableref> [, <tableref> …]

[WHERE <search_condition>]

[GROUP BY col [, col  …]

[HAVING <search_condition>]

[UNION <select_expr> [ALL]]

[PLAN <plan_expr>]

[ORDER BY <order_list>];

 

<val> = Column | <constant> | <function>

 

<constant> = num | 'string' | charsetname 'string'

 

<function> = COUNT (* | [ALL] <val> | DISTINCT <val>)

| SUM ([ALL] <val> | DISTINCT <val>)

| AVG ([ALL] <val> | DISTINCT <val>)

| MAX ([ALL] <val> | DISTINCT <val>)

| MIN ([ALL] <val> | DISTINCT <val>)

| CAST (<val> AS <datatype>)

| UPPER (<val>)

 

<tableref> = <joined_table> | table | view | procedure

[(<val> [, <val> …])] [alias]

 

<joined_table> = <tableref> <join_type> JOIN <tableref>

ON <search_condition> | (<joined_table>)

 

<join_type> = [INNER] JOIN

| {LEFT | RIGHT | FULL } [OUTER]} JOIN

 

<search_condition> = <val> <operator> {<val> | (<select_one>)}

 

| <val> [NOT] BETWEEN <val> AND <val>

 

| <val> [NOT] LIKE <val>

 

| <val> [NOT] IN (<val> [, <val> …] | <select_list>)

 

| <val> IS [NOT] NULL

 

| <val> {>= | <=}

 

| <val> [NOT] {= | < | >}

 

| {ALL | SOME | ANY} (<select_list>)

 

| EXISTS (<select_expr>)

 

| SINGULAR (<select_expr>)

 

| <val> [NOT] CONTAINING <val>

 

| <val> [NOT] STARTING [WITH] <val>

 

| (<search_condition>)

 

| NOT <search_condition>

 

| <search_condition> OR <search_condition>

 

| <search_condition> AND <search_condition>

 

<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

 

<order_list> =

{col | int}

[ASC[ENDING] | DESC[ENDING]]

[, <order_list> …]

 

Got that? OK, it is quite overwhelming if you have not encountered it before, but a few examples will help put it into a more realistic context... Below is a screen-shot of a simple query generated in HearAid Query which shows some of the basic elements of a SELECT statement:

 

QuerySQLExample

 

Before discussing the anatomy of the SELECT statement, you need to know something about primary keys. A primary key is a field which uniquely defines the identity of each record in a table i.e. no two records in the same table can have the same primary key. In the HearAid database you will find that the primary key is

1.Always the first field in the table
2.Usually an integer column (except for most of the code tables e.g. postcodes) and has _PK at the end of its name
3.Is used as the cross-reference field in other tables. In HearAid, the CLIENTS table is the central data table to which nearly all the other data tables connect, so the other tables contain a reference to CLIENT_PK which is the primary key of the clients table. For example, if you look at the snap-shot above (look at the box where ITEM is highlighted) you will see that the CLAIMS table has CLAIM_PK as its primary key, but the second field is CLIENT_PK, which is a reference to the unique identifier in the CLIENTS table that tells which client the claim record belongs to; this information is vital to JOINing tables together. Similarly, clinical test result tables (REPORTS_...) connect to the REPORTS table (primary key REPORT_PK), as well as the CLIENTS table so they contain a reference to both tables: REPORT_PK and CLIENT_PK (if you are a database purist you will realise that this is a small departure from "normalised form" but it is done deliberately to speed up certain queries!)

So, if you want to join tables together for more complex queries, look for common _PK fields to use in the JOIN.

Tip: Using a SELECT query will not alter or damage your data in anyway! So feel free to experiment to your heart's content!

 

Let's look at the main components of a SELECT statement:

SELECT:

The SELECT clause defines the columns you want to retrieve. The simplest form of SELECT is to retrieve all columns:

 

SELECT * ...

 

but you can specify just one column, or as many specific columns as you need, just by listing the column names, separated by a comma:

 

SELECT

 GIVEN_NAME,  FAMILY_NAME, DOB...

 

You can also ensure that there are no duplicate rows in the result by adding the DISTINCT keyword:

 

SELECT DISTINCT

 ITEM, SERVICE_DATE...

 

You can also do some useful and interesting things like COUNT the number of rows, SUM or AVERAGE the values in a number field or find the MAX (maximum) or MIN (minimum) values in a field. For example, if I want to know the number of clients in the database SELECT COUNT(*) FROM CLIENTS will give me the answer. But say you want to know the number of clients who have aid fittings but most clients have more than one hearing aid and you want to just count each client once, then SELECT COUNT(DISTINCT CLIENT_PK) FROM FITTINGS will do the trick!

 

FROM:

The FROM clause defines the tables which contain the fields you want to retrieve. SELECT FAMILY_NAME FROM CLIENTS will select just one column of data, FAMILY_NAME, from the CLIENTS table. If you need to retrieve data from more than one table you must JOIN the tables together using a column which is common to each table. For example, the screen-shot shows how to join the CLAIMS table to the CLIENTS table by using

 

... FROM

   CLIENTS

  JOIN CLAIMS

    ON CLAIMS.CLIENT_PK=CLIENTS.CLIENT_PK...

 

and the result will be that for every row in CLIENTS the query will retrieve those rows of data in CLAIMS which match the CLIENT_PK value for each row. In JOINs, the table before the word JOIN is the master or parent table and the table after the word JOIN is the detail or child table. The simple JOIN will not return a row unless there are matching values in both tables.

 

But what if you want to return values from the master table even if there are no values in the detail table? To do that you must use a LEFT JOIN:

 

SELECT

 CLIENTS.LIST_NAME,

 CLAIMS.ITEM

FROM

 CLIENTS

LEFT JOIN CLAIMS

  ON CLAIMS.CLIENT_PK=CLIENTS.CLIENT_PK

 

will return a result which will have empty values for CLAIMS.ITEM for clients who have no claims (such as private clients). When using JOINs it is good practice to prefix the table name to the column name to avoid ambiguity, otherwise the database engine will not process the statement and return an error message instead e.g.

 

SELECT

 CLIENT_PK

FROM

 CLIENTS

JOIN CLAIMS

  ON CLAIMS.CLIENT_PK=CLIENTS.CLIENT_PK

 

will fail, because CLIENT_PK is in both tables and the database engine does not know which one you want. You must also specify the table names on each side of the =; this will not work

 

...FROM

   CLIENTS JOIN CLAIMS

    ON CLIENT_PK=CLIENT_PK

 

WHERE:

 

The WHERE clause allows you to "filter" the result so that only records matching certain criteria will be returned. One useful thing to remember is that the fields used in the filter do not have to appear in the SELECT list. Operators used in the WHERE clause include

 

=

Equal to

<>

Not Equal to

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

between val1 and val2,

not between

In the range between (and including) value 1 and value 2 e.g. WHERE CLIENT_PK BETWEEN 1 AND 100, WHERE DOB BETWEEN '1920-01-01' AND '1940-12-31'

is null

Blank/Has no value Note: Empty strings '' are not the same as null

is not null

Is not blank/Has a value

like, not like

Match using "wild card" characters % and _ e.g. LIKE 'BA%" will match any character string beginning with BA, LIKE '%BA' will match any string ending in BA, LIKE 'B_A' will match any string starting with B and has A as the third character

in, not in

Match values in a single selected column with a set of values e.g. WHERE CLIENT_PK IN (1, 2, 3, 4) will return all records which have 1, 2, 3 or 4 as the CLIENT _PK value. This also works with strings e.g. WHERE FAMILY_NAME IN ('Smith', 'Jones', 'Symons'). The in operator can also be used with sub-selects e.g. this query will count the number of clients who have at least one claim:

 

SELECT COUNT(*) FROM CLIENTS

WHERE CLIENTS.CLIENT_PK IN (SELECT CLAIMS.CLIENT_PK FROM CLAIMS)

 

For a sub-select to work, the SELECT in the IN (SELECT...) must be on a single column

 

More than one condition can be set using AND and OR; when using multiple conditions, enclose each separate condition in parentheses:

 

SELECT

 LIST_NAME

FROM

 CLIENTS

JOIN CLAIMS

  ON CLAIMS.CLIENT_PK = CLIENTS.CLIENT_PK

WHERE

 (CLIENTS.MAINTENANCE_EXPIRES BETWEEN '2004-07-01' AND '2004-07-31') AND

 ((CLIENTS.ACTIVE_CLIENT = 'T') OR (CLIENTS.HSC = 'T')) AND

 (CLAIMS.ITEM NOT IN ('630', '640', '700', '710'))

 

When specifying date criteria in WHERE, you should use the standard 'yyyy-mm-dd' format, making sure you enclose the data in single quotes! e.g. DOB = '1921-05-14'
When specifying string criteria, you must enclose the string in single quotes e.g. FAMILY_NAME = 'Symons'
Matching string criteria is case-sensitive i.e. 'Symons' is not the same as 'SYMONS'. If you want a string criterion to be case-insensitive you must use either the UPPER() or LOWER() function e.g. WHERE UPPER(FAMILY_NAME) = 'SYMONS', WHERE LOWER(FAMILY_NAME) = 'symons'

 

GROUP BY:

 

GROUP BY is really useful for when you want to produce counts, sums and averages for sub-groups of records. The key thing to remember here is that if you do use one of the summary functions, you must include all the fields in the SELECT clause in the GROUP BY clause:

 

SELECT

 REPAIRS.SUPPLIER_PK,

 REPAIRS.WARRANTY_REPAIR,

count(*)

FROM

 REPAIRS

WHERE

 REPAIRS.DATE_RETURNED IS NULL

GROUP BY

 REPAIRS.SUPPLIER_PK,

 REPAIRS.WARRANTY_REPAIR

 

will return the number of outstanding warranty and non-warranty repairs grouped by supplier

 

ORDER BY:

 

The ORDER BY clause allows you to define how you want the data sorted. The default sort order is ASC(ENDING) i.e. form smallest value to highest value for numbers, or normal alphabetical order for strings, or earliest to latest for dates and timestamps/date-times. To sort in reverse order, use DESC. You can specify any number of columns in the ORDER BY clause e.g.

 

SELECT

 GIVEN_NAME, FAMILY_NAME, DOB

FROM

 CLIENTS

ORDER BY

 FAMILY_NAME, GIVEN_NAME DESC

 

will sort in reverse order of FAMILY_NAME then GIVEN_NAME

 

This is just a brief introduction into the power of SQL as a tool for analysing and reporting on your data. The \Reports\Quick Reports and \Reports\SQL folders contain the SQL scripts used in HearAid and you may find them quite helpful in learning to harness the power of Firebird and SQL.

 

http://sqlzoo.net/ is a useful place for more introductory information on using SQL.