Sponsored by: Corsair’s TradeCraft (launching soon)
The Practical Explainer — SQL
Simplifying the landscape of analytics — one concept at a time
SQL (pronounced Sequel) — is hands down, the most common language used in data & analytics today. It stands for Structured Query Language. While some people do call it S-Q-L, never tell an interviewer “I know the S-Q-L”. It is a sure give away that anything you know is self taught and poorly informed.
SQL is the language of databases. It is a very simple language at that. This does not make it any less powerful, nor does it diminish the complexity that can be developed using it. It is a framework.
The Power Of Simplicity
SELECT, FROM, WHERE — so much of SQL is based on three simple words. Perhaps that is why it is still listed on 83% of job listing in Data Science & Analytics.
So lets start with:
SQL is a very simple language that is used as a framework for working with information in a database.
Then let’s break it down:
S — Structured. SQL has both internal and external structure. The language is highly structured. Any novice reader would quickly note how similar all SQL code looks. It has a fairly strict order and pattern to it. It also provides structure to the data it is used to query and manipulate. For readers of our last article, SQL is often used for pieces of ETL.
Q — Query. Some would tell you that SQL allows you to ask questions of the data. This may be a little optimistic. It certainly can be used to ask questions, with a little art work. SQL queries really ask the database to produce a list or summary with a set of conditions.
L — Language. Some people would debate this. Compared to languages like Python, R, and even Visual Basic, the heavy structure and simplicity of SQL causes some to question its language status. It is a language, perhaps a language of few words.
In practice:
SQL allow you to “talk” to the database, to ask it to produce tables and summary statistics. This may be a list of courses that start with MIC12, like the image above. It may be the total sales that occurred in the month of March. It might be all the data contained in a specific table. All the users needs to do is SELECT the fields they want, inform the database which source these fields should be taken FROM, and state any conditions that should be met — using WHERE.
With more practice:
A complete novice can learn to read (and even write) SQL in a matter of hours. It just isn’t that hard. But the skills that develop over weeks, months, and years are formidable indeed. With a little more experience, SQL can be used to sort (ORDER BY) and organize (GROUP BY), SUM and COUNT, and to CREATE more tables or JOIN a few together.
With a lot of practice:
Nearly anything is possible. SQL is powerful. It is also iterative. Queries can be run in sequence allowing the user to JOIN information, summarize it, sort it, JOIN it to additional data TABLES, CREATE new tables, and new FIELDS. The biggest limits are ones experience and the fact that SQL only operates within data sources (warehouses, lakes, etc) that support it. Now almost all of them do, but not everything is a data source (in this sense).
What is NoSQL?
A topic for another article. That said:
A large number of NoSQL databases support SQL! They simply don’t require it.
NoSQL has really struggled to steal market share. An argument can be made that even some recent success only came once developers added SQL capabilities.
Because their are always quirks:
Take any language and make it popular enough, long enough and you will soon grow dialects. SQL is no exception. One critical quirk that often frustrates novice practitioners — each system has the potential of using its own dialect (or flavor). Most differences are subtle — but because of SQL’s structured nature, omitting an AS or using a slightly different keyword, most often prevents a job (query) from running. Fix = Just Google it!
Some final associations:
Queries are often called jobs. Common dialects include — PostgreSQL, t-sql, pl-sql, and MySQL. SQL is often associated with relational databases. If you don’t have one, SQL may not work for you (at least not well). Joins are also called merges. Tables are often replaced by views (equivalent to all but the engineers and security folks). Your DBAs may spend a lot of time talking about indexes and partitions (just ways to optimize storage and recall).
So just remember:
SQL is the key to the data world. It only gets you in the door. Though with sufficient practice, it can do much, much more. Learn it first. Practice it often. A confident SQL programmer has a lot of job security and will for the foreseeable future.
Thanks for reading. More Practical Explainer coming soon…
Want to train, learn, and practice SQL? Checkout our sponsor.
