Saturday, May 28, 2022
Home Databases SQL Basics for Healthcare Professionals You Must Learn Now

SQL Basics for Healthcare Professionals You Must Learn Now

Prerequisites

  • Basic knowledge of what a database is
  • Basic knowledge of the relational database model
  • Basic knowledge of the healthcare industry

SQL stands for Structured Query Language and it is the standard language to extract, change, and remove data (amongst other things) from a relational database management system (RDBMS). SQL is vital for many healthcare professionals to learn because every segment of the healthcare industry values meaningful reporting. In fact, healthcare organizations will pay lots of money for sophisticated analytics and reporting tools that fit their specific needs. Most of the time, a database connection and SQL are behind the reports that run in these analytics platforms.

Additionally, most major EMRs (i.e. Epic, Meditech) still run on an RDBMS backend. Many hospitals and other healthcare organizations will request read-only database users to these systems so that they can create their own custom reports, dashboards, etc using SQL. This read-only database user is often used in conjunction with an analytics platform, as mentioned previously.

You might be in a support role for an EMR vendor or another healthcare application, for example. Someone in this role has a great understanding of the front-end of their application; however, the person may not have a great understanding of how the back-end (database) connects to the front-end. That is, a world-class support professional should be able to look at a section in an application and know what database table stores that the data that is visible to the user.

A support professional that knows SQL can quickly solve clients’ problems, which leads to happier customers and improved survey ratings. Furthermore, there are certain data points that can only be seen on the back end. For example, an application may house an administrative table that turns certain master settings on and off. Knowing the values in this table could help explain why the application is behaving in a certain way.

SQL Query Statement

When someone says “SQL Query”, he or she generally means reading (extracting) data from the database. The structure for a SQL query has three basic clauses:

  • SELECT
  • FROM
  • WHERE

SELECT

SELECT is the first clause in any SQL query. Every SQL query you write will start with SELECT. The SELECT clause indicates which columns you are picking from the table you are querying. For example, consider the PATIENTS table.

PATIENTS

PATIENT_IDFIRST_NAMELAST_NAME
1BobBrown
2SarahFinley

Notice that PATIENTS has three columns: PATIENT_ID, FIRST_NAME, and LAST_NAME. If you wanted to select FIRST_NAME and LAST_NAME the SELECT clause would be the following:

SELECT FIRST_NAME, LAST_NAME

In the SELECT clause, each column selected is separated by a comma. Keep in mind that the above is not a complete SQL query. To form a complete query, all you need is a SELECT and FROM clause.

FROM

Continuing from the previous example, the FROM clause tells the database which table to pull the data from. Consider the following SQL query:

SELECT FIRST_NAME, LAST_NAME
FROM PATIENTS;

The above query forms a complete SQL statement. Notice that the statement is terminated with a semicolon. All SQL statements should be terminated with a semicolon. If you are executing multiple queries in a SQL window of a DBMS tool, then you will definitely want to separate your queries with a semicolon.

The above query returns only the first name and last name of every row in PATIENTS. If PATIENTS only includes the two rows shown previously then the query will return the following:

BobBrown
SarahFinley

WHERE

WHERE allows you to filter out certain rows in your query and keep others. Consider the following query:

SELECT FIRST_NAME, LAST_NAME
FROM PATIENTS
WHERE PATIENT_ID = 1;

The above query pulls only the first name and last name for all rows that have PATIENT_ID equal to 1. The following result set is returned:

BobBrown

You must use WHERE to do most things that are meaningful on a database. Learning how to write good WHERE clauses is essential for your success.

SQL Update Statement

An update statement updates existing data in a database. There are three basic clauses that make up an update statement:

  • UPDATE
  • SET
  • WHERE

UPDATE

UPDATE is the first clause in an update statement. Every SQL update statement you write will start with UPDATE. The UPDATE clause indicates which table you are updating. Consider the following UPDATE clause:

UPDATE PATIENTS

Only one table should follow the UPDATE keyword. Also, keep in mind that the above is not a complete update statement. Specifically, you need at least the UPDATE and SET clauses to form a complete statement.

SET

SET indicates which columns are being changed in your statement and how the values are changing. Consider the following example on the PATIENTS table:

UPDATE PATIENTS
SET FIRST_NAME = 'TEST';

The above sets the first name of every patient in PATIENTS to ‘TEST’. Notice the single quotes to denote that FIRST_NAME is being set to a string value. As with a regular query, the update statement always ends with a semicolon.

Here are the results after executing the above update statement.

PATIENTS

PATIENT_IDFIRST_NAMELAST_NAME
1TESTBrown
2SarahFinley

WHERE

A WHERE clause in the context of an update statement works the same way as a regular query. Consider the following example:

UPDATE PATIENTS
SET LAST_NAME = LAST_NAME || '-James'
WHERE PATIENT_ID = 1;

As you can see below, only the row with PATIENT_ID of 1 is updated. In this example, the update statement hyphenates Bob’s last name using ||, the concatenation operator. to concatenate means to combine two or more strings together.

Also, notice that you can use the current value of the column in the SET clause. Specifically, the current value of LAST_NAME is being appended with ‘-James’ (See below).

PATIENTS

PATIENT_IDFIRST_NAMELAST_NAME
1BobBrown-James
2SarahFinley

SQL Delete Statement

A delete statement allows you to remove rows from a particular database. There are two different basic clauses that make up a delete statement:

  • DELETE FROM
  • WHERE

DELETE FROM

DELETE FROM indicates what table rows are being deleted from. Consider the following DELETE FROM clause:

DELETE FROM PATIENTS

The DELETE FROM clause is only followed by the table name, PATIENTS in this case. Keep in mind that the above is not a complete delete statement. Specifically, all you need a DELETE FROM and WHERE clause to form a complete statement.

WHERE

The WHERE clause indicates which rows to delete from the table. If a row meets the criteria outlined in WHERE then that entire row will be deleted. Consider the following transaction on the original PATIENTS table.

DELETE FROM PATIENTS
WHERE FIRST_NAME = 'Bob' AND LAST_NAME = 'Brown';

The AND keyword in WHERE indicates that only rows that have FIRST_NAME of ‘Bob’ and LAST_NAME of ‘Brown’ should be deleted. Any other row should not be deleted. See below for the state of the table after the delete transaction is committed.

PATIENTS

PATIENT_IDFIRST_NAMELAST_NAME
2SarahFinley

SQL Operators

In the examples above, you were exposed to a few different SQL operators such as AND, OR, etc. However, the tables below showcase most operators that you would want to leverage in a SQL statement.

Logical Operators

Logical operators evaluate to either true or false in a SQL statement. These are typically used within a WHERE clause. However, it is possible for them to appear in other clauses.

OperatorDescriptionExample
ALLUsed in conjunction with a subquery. Returns TRUE if all the values in the subquery meet the conditionWHERE PATIENT_ID = ALL (SELECT P.PATIENT_ID FROM PATIENTS P WHERE P.ADMIT = ‘I’)
ANDReturns TRUE if both conditions on either side of the operator are TRUE.DELETE FROM PATIENTS
WHERE FIRST_NAME = ‘Bob’ AND LAST_NAME = ‘Brown’;
ANYUsed in conjunction with a subquery. Returns TRUE if any of the values in the subquery meet the condition.WHERE PATIENT_ID = ANY (SELECT P.PATIENT_ID FROM PATIENTS P WHERE P.ADMIT = ‘I’)
BETWEENReturns TRUE if the value lies somewhere between the two valuesWHERE PH BETWEEN 4 AND 7
EXISTSUsed in conjunction with a subquery. Returns TRUE if the subquery returns any values at all.WHERE EXISTS (SELECT * FROM PATIENTS WHERE ADMIT = ‘O’)
INUsed in conjunction with a subquery. Returns TRUE if the value is found within the results of the subquery.WHERE PATIENT_ID IN (SELECT PATIENT_ID FROM PATIENTS)
LIKEReturns TRUE if the value conforms to the same form as indicated in the query.WHERE FIRST_NAME LIKE ‘%Jane%’
NOTInverts a condition. If the condition is FALSE, NOT will make it TRUE.WHERE PATIENT_ID NOT IN (SELECT PATIENT_ID FROM PATIENTS)
ORReturns TRUE if either condition on left or right of the operator is TRUE.WHERE FIRST_NAME = ‘Sam’ OR FIRST_NAME = ‘Samuel’
SOMEEquivalent to ANYWHERE PATIENT_ID = SOME (SELECT P.PATIENT_ID FROM PATIENTS P WHERE P.ADMIT = ‘I’)

Note that the ‘%’, when found in the context of a string, acts as a wildcard. The above example returns all rows that contain ‘Jane’. Also, when SELECT is followed by ‘*’, that indicates that all table columns should be picked.

Comparison Operators

Comparison operators deal with the absolute or relative values in a WHERE clause.

OperatorDescriptionExample
=Equal toWHERE PATIENT_ID = 27360
>=Greater than or equal toWHERE PH >= 5
<=Less than or equal toWHERE PH <= 10
>Greater thanWHERE PH > 5
<Less thanWHERE PH < 10
<>Not equal toWHERE PATIENT_ID <> 27360

Arithmetic Operators

Arithmetic operators allow you to perform mathematical operations on values in your database.

OperatorDescriptionExample
SubtractionWHERE AGE – 10 > 20
+AdditionWHERE AGE + 10 < 30
/DivisionWHERE AGE / 3 = 20
*MultiplicationWHERE AGE * 5 = 40
%ModuloWHERE AGE % 2 = 0

While this article does not contain all details of the SQL language, it gives you everything you need to start writing queries for your healthcare organization. Healthcare professionals of all niches can benefit from applying the SQL outlined in this article.

RELATED ARTICLES

Vacuum Your PostgreSQL mirthdb and Prevent Disk Storage Issues Today

Prerequisites Basic knowledge of PostgreSQLPostgreSQL installed and configured as Mirth's database Things to Know Beforehand Vacuuming a PostgreSQL database will first increase your drive size before making...

Most Popular

Mirth Connect Extensions: What You Need to Know

Prerequisistes Basic Knowledge of Mirth Connect Overview Users can navigate to Mirth Connect's Extensions screen by selecting Extensions on the left-hand side. You should now see the following...

Take a Tour of System Settings in Mirth Connect Now

Prerequisites Basic Knowledge of Mirth Connect Introduction Mirth Connect has a multitude of system settings that are available for configuration. In larger companies, there will often be...

Why You Must Master Mirth’s Message Template Tool Today

Prerequisuites Basic Knowledge of Mirth ConnectKnowledge of Programming in Mirth Connect Introduction When writing scripts in Mirth Connect, you have a message template tool that allows you...

Make Sense of HL7 v2 Messages Effortlessly Now

Prerequisites Beginner Knowledge of HL7 Introduction If you've been in Healthcare Information Technology for any period of time, you have definitely heard of HL7 (Health Level 7)....

Recent Comments