Hey Friends,

Today it’s all about SQL. We are going to dedicate the next few posts to create a starter series for SQL! These posts will be a great beginner resource for anyone looking to increase their knowledge of relational databases. The goal for the SQL Starter Series is to introduce key concepts and terms and help facilitate a passion for the reader to dive deeper into these topics.

Let’s get Started!

SQL, pronounced “sequel”, stands for Structured Query Language, which is used to manage, manipulate, and extract data from relational database management systems (RDBMS). There are many “flavors” of SQL currently in use (MySQL, PostgreSQL, SQLite). There are pros and cons to each, but the main point is that the key underlying concepts are the same, just the RDBMS and syntax will differ from each.

MySQL is a free open source RDBMS which can be downloaded here: https://www.mysql.com/

MySQL is a great tool for anyone to start learning SQL. There are large datasets that you can import and start manipulating data. Some of my favorite healthcare data to use for learning is from Medicare’s website: https://data.medicare.gov/data

There are tons of datasets on Medicare’s website that are large, contain many fields, and simply are great for pharmacists and other healthcare professionals to learn from.

Some other great resources to learn SQL from, that I personally love are:

Maven Analytics SQL Intro Course:

https://www.udemy.com/course/mysql-for-data-analysis

The Language of SQL by Larry Rockoff:

https://read.amazon.com/kp/card?preview=inline&linkCode=kpd&ref_=k4w_oembed_bI9GqOzEtWdQe2&asin=0134658256&tag=kpembed-20

Of course there are many free resources out there as well. The main point is find a resource that is easy for you to understand and fits well with your learning style.

Let’s get started with the most basic data retrieval query: the Basic Select Statement.

The keywords for the Basic Select Statement are SELECT and FROM. See the basic syntax below:

SELECT *    
FROM table   

Your first question might be “What does the asterisk * mean”? In SQL it means “all columns” FROM a table will be returned in the output. So let’s revisit the Basic Select Statement below:

SELECT columns
FROM table

Your second question might be “Why are the keywords (SELECT and FROM) in all caps. The answer is quite simple: for readability. You do not have to do this, but it makes your query much easier to read for anyone taking a look at it. It is also a best practice to return a new line for each keyword. Again, it makes your query much more readable.

The italicized words columns and table will be substituted for the actual column names and table name that you are trying to write your query on. Before we end this post let’s take a look at a practical example of a Basic Select Statement in action!

Table: Invoice

Inv_Num Inv_Date Account Med_Dsc NDC Qty_Pur Unit
A653421 5/1/2020 340b lisinopril 5 mg 00000-0000-01 10 BTL
A286381 5/1/2020 WAC levothyroxine 100 mcg 00000-0000-02 3 BTL
A200098 5/2/2020 GPO amlodipine 5 mg 00000-0000-03 2 BTL

Basic Medication Invoice Table Example

SELECT   
            Inv_Num,
            Inv_Date,
            Account,
            Med_Dsc,
            NDC,
            Qty_Pur,
            Unit
FROM
          Invoice

This query above would return everything in our example invoice table above. You could also use the asterisk * after the select instead of typing each of the column names. Notice that each column name is separated by a comma. Important: you do not need a comma after your last column name.

Performing the Basic Select Statement is a great first step in investigating any new tables that you come across. It helps get a feel for the data types stored in each column. Pro tip: adding a top 10 or top 100 statement to your Basic Select Statement will help ensure you do not SELECT * enormous amounts of data that will slow the performance of your server, PC, and other systems. See the syntax below for an example.

SELECT top 10 *
FROM table

By adding the top 10 clause, you will only return the top 10 rows that are stored for this table in the database. It is important to note that the top 10 is not ranked by anything. The query is simply returning the top 10 rows in how the database stores them. Which means, it might not be the last or first 10 rows of your table.

Congrats! You now know the Basic Select Statement. Try it out with some example tables from the Medicare healthcare database: https://data.medicare.gov/data

We will be covering the Full Select Statement in the next SQL Starter Series Post. Drop some comments and questions below.

Stay Digital,

Ben

Preview of the Full Select Statement that we will dive into on the next SQL Starter Series Post:

Leave a Reply

Processing...
Thank you! Your subscription has been confirmed. You'll hear from us soon.
Subscribe For More Content
Digital Pharmacist
ErrorHere