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:
The Language of SQL by Larry Rockoff:
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!
|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.
Preview of the Full Select Statement that we will dive into on the next SQL Starter Series Post: