10 Reasons Why SQL is a Powerful Language for Data Analysis

Knowing how to code in SQL (Structured Query Language) is a necessity if you want to be a Data Analyst or a Data Scientist. I have not come across a firm, whether a startup or multinational, that didn't use SQL for their data analysis in some way or the other. SQL is simple and easy to understand with a variety of functions for accessing, cleaning and analysing data enabling businesses to solve incredibly challenging problems through data-driven solutions.

In this article, I will go through 10 reasons why I think SQL is a powerful language for data analysis.

1. Relational algebra mainly provides the theoretical foundation for relational databases and SQL

● Relational Algebra is the backbone of SQL – it's used to translate an arbitrary SQL query into a logical query plan

● Relational Algebra eases the task of reasoning about queries.

● Relational algebra allows transparent optimisation of the query

● It allows a programmer to code in common steps since it based on a powerful framework of projection, filter, joins and aggregations

2. SQL is a declarative language

● Easy to code – allows the developer to express just the logic of the computation and not the internal workings

● No need to describe the actual control flow unlike in procedural languages

● SQL looks into “what” should be accomplished rather than “how”. SQL is declarative, which means that you tell the DBMS what you want, but not how it is to be calculated.

3. You can optimise your query using SQL

● Optimisation happens at the source which means that the front end will also a get a share of the optimisation

● SQL builds an optimal query plan in the background which can be tweaked if need be

● SQL operated through parallel execution making the query run faster

● SQL makes join far less complicated and also reduce table size ensuring faster runs

4. SQL has a variety of aggregate functions

Ability to conduct aggregation on data – useful for descriptive analytics. 

Variety of aggregate functions available such as:

● Average

● Count

● Maximum & Minimum

● Standard Deviation

● Sum

● Variance & Standard Deviation

5. SQL has plenty of analytical functions which will enable you to prepare your data like a boss

Analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, analytic functions can return multiple rows for each group. 

Variety of analytical functions available such as:

● Cumulative Distribution of a value

● First or last value in an ordered set

● Access data from previous or subsequent row in the same result set

● Calculate a percentile based on a continuous distribution of the column value

● Relative rank of a row within a group of rows

● Accesses data from a subsequent/previous row in the same result set without the use of a self-join starting (LEAD and LAG functions)

6. SQL has many mathematical functions to aid your data analysis

● Ability to apply arithmetic functions such as absolute, degree to radian conversion and vice versa, floor, power, and sign.

● Ability to apply trigonometric and other functions such as exponential, log, square, and square root.

7. SQL has powerful ranking functions

Ranking functions return a ranking value for each row in a partition.

● Ranking of each row within a result set partition, with gaps or no gaps in the ranking values

● Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, return the number of the group to which the row belongs.

8. SQL has powerful data cleaning and transformation functions

● Referential integrity and constraints: Ability to set up primary or unique keys go a long way in ensuring data quality. Simple methods exist in SQL to see that your data is not orphaned and meets a business rule

● Function to handles NULLs as required

● String manipulation functions such as concatenation, formatting, retrieve substrings, trimming of trailing and preceding spaces, replace characters, difference/similarity between two strings and splitting strings

● Date and time manipulation and conversation functions. E.g. converting an unstructured text date to a date datatype.

● Ability to convert fields to appropriate data type

● Ability to explore and check the distribution of data

● SQL can bin values

9. SQL can do descriptive statistics with ease

● Measure central tendency via mean, median and mode

● Measure the value variability using minimum and maximum, distribution using count, standard deviation and variance

10. You can sub-query, break your analysis into pieces and handle large datasets

● More often than not, data analysis starts with a large data set that is complex and messy. SQL enables sub-query and activates some sort of optimisation to handle large datasets

● SQL is faster, separates analysis from data

Hope this article serves as a nice summary of things you can do in SQL as a data analyst. Let me know in the comments below if I have missed out something vital. 

Take care.