LIMS.eu

Table of Contents:

1. Introduction: What is SQL?
2. SELECT Statement
3. DISTINCT Statement
4. WHERE Statement
5. AND & OR Operators
6. IN Operator
7. BETWEEN Statement
8. LIKE Statement
9. ORDER BY Statement
10. GROUP BY Statement
11. HAVING Statement
12. JOIN Statement
13. Further Link

## 1. Introduction: What is SQL?

SQL (Structured Query Language) is a database language used to create structures in databases and to edit and query existing data. With specific commands, all data in the Labordatenbank can be retrieved and provided to the user.
SQL is based on relational algebra and is the standard language when it comes to databases. Many SQL commands are derived from English and are therefore easy to learn.

## 2. SELECT Statement

The SQL SELECT command forms the basis for many SQL queries of a database. It allows you to query data from one or more tables (see JOIN). It defines which columns of a specific table should be displayed after the query is executed.

The syntax is structured as follows:
SELECT ColumnName1, ColumnName2, ColumnName+n FROM TableName

The samples table from the Labordatenbank is used as an example:

To display the name and description columns (marked in red) from the samples table in the Labordatenbank, the following command is used:

SELECT
samples.name AS 'Probe'
,samples.description AS 'Bezeichnung'
FROM samples
LIMIT 5;

! No comma is placed at the end of the line before FROM !

Hier will nun zusätzlich noch der SQL Befehl LIMIT benutzt, um in den Beispiel die Ergebnistabelle auf fünf Zeilen einzugrenzen.
So kann generell die Anzahl der Zeilen für die Ausgabe der Tabelle limitiert werden. Des Weiteren werden die Spaltenbezeichnungen mit dem SQL Befehl AS umbenannt. Dieser Befehl kann optional verwendet werden.

Output of the Labordatenbank analysis:

## 3. DISTINCT Statement

The SQL DISTINCT command is used to eliminate redundancies, i.e., values that appear multiple times, so that they are displayed only once in the table.
DISTINCT defines the column to be checked. The command is placed directly after SELECT in an SQL query.

The syntax is structured as follows:
SELECT DISTINCT ColumnName FROM TableName

To display the description column redundantly in the Labordatenbank from the samples table, the following command is used:

SELECT 
DISTINCT samples.description
FROM samples
LIMIT 5;

So, from the samples table:

with the additional SQL command DISTINCT, the following result is displayed:

The duplicate row with Testprobe 4 is now displayed only once in the selected description column. Since sample, order, and customer numbers are not duplicated in the Labordatenbank, this command will not be used as frequently in an SQL query analysis.

## 4. WHERE Statement

The SQL WHERE command is used when only specific records from a database should be displayed in an SQL query. The WHERE condition functions like a filter, so that only results that meet certain criteria are output.

The syntax is structured as follows:
SELECT ColumnName FROM TableName WHERE ColumnName = Value

WHERE defines which condition must be met to obtain a specific result.
The following comparison operators can be used:

• equals (=), not equals (<>, !=)
• greater than (>), less than (<)
• greater than or equals (>=) and less than or equals(<=)

To display the name (condition: only samples with a sample number > 250033) and description columns from the samples table in the Labordatenbank, the following command is used:

SELECT
samples.name AS 'Probe', 
samples.description AS 'Bezeichnung'
FROM samples
WHERE samples.name > '250033'
LIMIT 3;

Output of the Labordatenbank analysis:

In the Labordatenbank, a common WHERE condition is:
WHERE visible_to != 'deleted'
This condition is important if deleted samples should be hidden.

To filter an entire analysis by time periods, the following WHERE condition can be used:
WHERE date BETWEEN _FROMDATE_ AND _TILLDATE_

The filter is then displayed above the analysis in the Labordatenbank:

## 5. AND & OR Operators

To use multiple WHERE conditions, or to further narrow down query results, the SQL operators AND & OR must be included. The AND operator combines SQL conditions, and the OR operator differentiates between two or more SQL conditions.

The syntax with WHERE and AND is structured as follows:
SELECT ColumnName FROM TableName WHERE ColumnName1 = Value1 AND ColumnName2 = Value2

! In an SQL query, AND takes precedence over OR !

To display the name (condition: only samples with a sample number < 250033 and > 250035) and description columns from the samples table in the Labordatenbank, the following command is used:

SELECT 
samples.name AS 'Probe' 
,samples.description AS 'Bezeichnung'
FROM samples
WHERE samples.name < '250033'
AND samples.name > '250035'

Output of the Labordatenbank analysis:

To display the name (condition: only samples with a sample number like 250033 or 250035) and description columns from the samples table in the Labordatenbank, the following command is used:

SELECT 
name AS 'Probe', 
description AS 'Bezeichnung'
FROM samples
WHERE 
name LIKE '250034' OR
name LIKE '250035'

Output of the Labordatenbank analysis:

## 6. IN Operator

The SQL IN operator is used with a WHERE condition to combine multiple query results in one SQL command. This prevents, for example, having to include multiple OR operators in an SQL query.
With the combination of the SQL operators IN and NOT, result sets can also be excluded.

The syntax with WHERE and IN is structured as follows:
SELECT ColumnName FROM TableName WHERE ColumnName IN ('Value1', 'Value2')

To display the name (condition: only samples with the number 210034 and 210036) and description columns from the samples table in the Labordatenbank, the following command is used:

SELECT 
samples.name AS 'Probe' 
,samples.description AS 'Bezeichnung'
FROM samples
WHERE
samples.name IN ('250034', '250036')

Output of the Labordatenbank analysis:

## 7. BETWEEN Statement

The SQL BETWEEN command is used with a WHERE condition to query results within a specific range.
The SQL BETWEEN command is often used, for example, to display query results within a specific date range.

The syntax with WHERE and BETWEEN is structured as follows:
SELECT ColumnName FROM TableName WHERE ColumnName BETWEEN 'DATE1' AND 'DATE2'

To filter the name and description columns from the samples table in the Labordatenbank for a desired period, the following command is used:

SELECT 
samples.name AS 'Probe' 
,samples.description AS 'Bezeichnung'
FROM samples
WHERE 
samples.date BETWEEN _FROMDATE_ AND _TILLDATE_

The filter is displayed above the analysis.

Output of the Labordatenbank analysis:

Since the date in the Labordatenbank is stored as Unix time, the selected period is inserted as a Unix timestamp in FROMDATE and TILLDATE.
To display the date column in the Labordatenbank in the date format DD.MM.YY with the column name Eingang, the SQL function FROM_UNIXTIME must be used:

SELECT 
samples.name AS 'Probe' 
, samples.description AS 'Bezeichnung'
,FROM_UNIXTIME(samples.date, '%d.%m.%Y') as 'Eingang'
FROM samples

Output of the Labordatenbank analysis:

! To format the date from DDF fields, the following command is used: DATE_FORMAT(DDF_DATUM, '%d.%m.%y') !

## 8. LIKE Statement

The SQL LIKE command is used with a WHERE condition to query results based on a defined pattern. The LIKE command differentiates itself from the IN operator (fixed search term) and the SQL BETWEEN command (range search).
The SQL LIKE command is often used in texts to search for results with regular patterns.

The syntax with WHERE and LIKE is structured as follows:
SELECT ColumnName FROM TableName WHERE ColumnName LIKE 'Pattern'

The 'PATTERN' can contain the following structures:

• 'A_Z': All character strings that begin with 'A', contain one following character, and end with 'Z'.
• 'ABC%': All character strings that begin with 'ABC'.
• '%XYZ': All character strings that end with 'XYZ'.
• '%AB%': All character strings that contain the pattern 'AB' at any position.
• 'AB%C': All character strings that start with 'AB', then contain any characters, and end with 'C'.

Example: The following command is used to output the name and description columns from the samples table in the Labordatenbank, where the name begins with "T", contains 9 subsequent characters and ends with "1":

SELECT 
name AS 'Probe' 
,description AS 'Bezeichnung'
FROM samples
WHERE 
description LIKE 'T_________1'

Output of the Labordatenbank analysis:

## 9. ORDER BY Statement

The SQL ORDER BY command is used to sort results according to a defined order.

The syntax with ORDER BY is structured as follows:
SELECT ColumnName FROM TableName ORDER BY ColumnName SortingParameter

The sorting parameter can contain the following structures:

• ASC: the column is sorted in ascending order (ASC from ascending)
• DESC: the column is sorted in descending order (DESC from descending)

To, for example, display the name and description columns from the samples table in the Labordatenbank and sort the description column in descending order, the following command is used:

SELECT 
samples.name AS 'Probe' 
,samples.description AS 'Bezeichnung'
FROM samples
ORDER BY description DESC

Output of the Labordatenbank analysis:

## 10. GROUP BY Statement

The SQL GROUP BY command is used to group results. The command defines which data set should be grouped, and multiple columns can be grouped. GROUP BY can also be used with a WHERE condition.

The syntax with GROUP BY is structured as follows:
SELECT ColumnName FROM TableName [WHERE Condition] GROUP BY ColumnName

The SQL GROUP BY command is frequently used in conjunction with an aggregate function.
The following commands are among the aggregate functions:

• AVG (from average): takes the average of numerical values
• COUNT (from count): counts the records of texts, numerical values, or date values
• MAX (from maximum): takes the highest value of texts, numerical values, or date values
• MIN (from minimum): takes the smallest value of texts, numerical values, or date values
• SUM (from sum): sums the entries of numerical values

The syntax with GROUP BY in combination with an aggregate function is structured as follows:
SELECT ColumnName, COUNT(ColumnName) FROM TableName GROUP BY ColumnName

! A comma is placed before COUNT and no space after it to the parenthesis !

To display the number of samples per day from the samples table in the Labordatenbank, the date is grouped with the following command and the number of samples with this date is counted:

SELECT
FROM_UNIXTIME(date, '%d.%m.%Y') AS 'Eingang'
,COUNT(date) AS 'Anzahl Proben'
FROM samples
GROUP BY date

! The spelling for the date was explained under BETWEEN !

Output of the Labordatenbank analysis:

## 11. HAVING Statement

The SQL HAVING command is used to restrict a grouped result set. HAVING represents the WHERE condition in a GROUP BY command. A WHERE condition cannot be combined with aggregate functions, which is why HAVING is used with GROUP BY.

The syntax of the HAVING command in combination with GROUP BY and an example aggregate function is structured as follows:
SELECT ColumnName, COUNT(ColumnName) FROM TableName GROUP BY ColumnName HAVING Expression

! HAVING always follows GROUP BY. Any WHERE condition used in the SQL query must precede GROUP BY !

To display the number of samples per day from the samples table in the Labordatenbank (condition: only days with > 2 samples), the date is grouped with the following command and the number of samples with this date is counted:

SELECT
FROM_UNIXTIME(date, '%d.%m.%Y') AS Eingang,
COUNT(date) AS 'Anzahl Proben'
FROM samples
GROUP BY date
HAVING COUNT(date) >2

Output of the Labordatenbank analysis:

## 12. JOIN Statement

The SQL JOIN command is used to obtain information from different tables in an SQL query. With the help of JOIN, tables that are related to each other through common data records can be linked. The linking of the columns to be compared from the desired tables is done via an ON condition and the definition of the JOIN type.

There are six different JOIN types:

• Cross-Join
• Inner Join
• Natural Join
• Left Join
• Right Join
• Full Outer Join

For analyses in the Labordatenbank, the JOIN types Inner and Left are relevant.
To narrow down the result set, a WHERE condition can also be used here.

INNER JOIN and LEFT JOIN:

In a "normal" JOIN = INNER JOIN SQL query, only results that exist in both tables are displayed.
With a LEFT JOIN, all results from the FROM table are displayed, and the corresponding results from the joined table, if available.
(Example: see instructions: SQL JOINs)

The Labordatenbank is based on the following data structure or table linkage:

A detailed linkage of the columns of the different tables in the Labordatenbank for the areas Customer-Order-Sample-Result-Parameter-Specification is shown in the following illustration:

The syntax with JOIN is structured as follows:
SELECT TableName1.ColumnName, TableName2.ColumnName... FROM TableName1 JOIN TableName2 ON TableName1.LinkingColumnName = TableName2.id

! In the Labordatenbank, all tables are in the plural (Tabs), the column names usually in the singular (Tab). This facilitates the distinction between table name and column name !

If, for example, the orders table is to be linked with the samples table, one can look in the Table Linkage in the LDB illustration to see which columns connect the two tables. In the image, the columns that can be linked are marked with the same color. This means that the id column in the orders table (= orders.id) can be linked with the order_id column in samples (= samples.order_id), both marked in green here, via a JOIN command.

The following command is used to display the name and description columns from the samples table and the corresponding name column from the orders table in the Labordatenbank:

SELECT
samples.name AS 'Probe' 
,samples.description AS 'Bezeichnung'
,orders.name AS 'Auftrag'
FROM samples
JOIN orders ON samples.order_id = orders.id
LIMIT 5

Output of the Labordatenbank analysis:

! When using the JOIN command or querying from different tables, the table name must always be specified with SELECT ColumnName: TableName.ColumnName !

## 13. Further Link

Further SQL commands and assistance can be found under the following link:
MySQL Functions and Operators