Understanding Structured Query Language (SQL)
SQL stands for Structured Query Language. SQL is a standard programming language specifically designed for storing, retrieving, managing or manipulating the data inside a relational database management system (RDBMS). SQL became an ISO standard in 1987. SQL is the most widely implemented database language and supported by the popular relational database systems, like MySQL, SQL Server, and Oracle.
However, some features of the SQL standard are implemented differently in different database systems. SQL was originally developed at IBM in the early 1970s. Initially it was called SEQUEL (Structured English Query Language) which was later changed to SQL (pronounced as S-Q-L). In this blog, I will brief you about the importance and usage for SQL and will provide you some of the links by which one can get start with its learning and practice from the scratch.
SQL Basics: Relational Databases
A relational database is a database that stores related information across multiple tables and allows you to query information in more than one table at the same time. It’s easier to understand how this works by thinking through an example. Imagine you’re a business and you want to keep track of your sales information. You could set up a spreadsheet in Excel with all the information you want to keep track of as separate columns: Order number, date, amount due, shipment tracking number, customer name, customer address, and customer phone number.
This setup would work fine for tracking the information you need to begin with, but as you start to get repeat orders from the same customer, you’ll find that their name, address and phone number gets stored in multiple rows of your spreadsheet. As your business grows and the number of orders, you’re tracking increases and this redundant data will take up unnecessary space and generally decrease the efficiency of your sales tracking system. You might also run into issues with data integrity. There’s no guarantee, for example, that every field will be populated with the correct data type or that the name and address will be entered the same way every time.
With a relational database, like the one in the above diagram, you avoid all these issues. You could set up two tables, one for orders and one for customers. The ‘customers’ table would include a unique ID number for each customer, along with the name, address and phone number we were already tracking. The ‘orders’ table would include your order number, date, amount due, tracking number and, instead of a separate field for each item of customer data, it would have a column for the customer ID.
This enables us to pull up all the customer info for any given order, but we only must store it once in our database rather than listing it out again for every single order. For more deep knowledge about relational databases follow the link below:
What can SQL do:
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views
For more detail explanation about the characteristics of SQL, follow the below link:
Why SQL is great for businesses:
Beyond its essential purpose, to pull data out of tables and combine it in new ways, one of SQL’s other strengths is that it’s open-source and easy to learn and use. SQL also handles databases of all sizes; it’s database agnostic, for the most part. As long as you’re using an SQL database (that is, a type of database built to work with SQL — more on that later), you’ll be able to talk to it with SQL, no matter how much data is in there.
SQL is a powerful, versatile language and with the increase in the amounts of data that every company is capturing, buying, and sharing, there’s a huge demand for SQL experts. (A quick Indeed search showed almost 100,000 full-time job listings with the term “SQL” in them at time of writing.) Analytics and BI tools are allowing nontechnical team members to dig into this data in a structured environment without writing SQL, but there are still some tasks that SQL is uniquely well-suited for and many database professionals still choose to take advantage of their SQL skills to optimize their analysis.
For example, in marketing, the use of SQL is very vast and emerging every day: Look at a simple example of how to use SQL in marketing. Let’s say you’re working for a marketing department and need a good metric to calculate the churn of your advertising, audience, and funnel. The information of your churn, when compared with your industry’s churn, will guide your decision-making. For example, are you experiencing slowdowns at some stage of the funnel?
First, you need to identify your reach. To calculate reach, some advertising channels will show us exact numbers (e.g., the number of views on YouTube), but some will not. For example, if we are not the owners of the website, we cannot know how many views a certain blog post has. This is where our gut feeling can come in. By using a heuristic—i.e., a rule of thumb—we can define a fixed number to approximate our reach in a certain channel. Let’s say our gut feeling tells us that the whole blog channel has a reach of 1000 views per post. We’d define this reach in SQL like so:
SELECT CASE WHEN channel = ‘blog’ THEN 1000 ELSE reach END modified reach FROM media reach;
With a well-defined reach, you can now expand your analysis to answer other question:
In what countries do we have the most reach?
SELECT country.name,SUM(CASE WHEN channel = ‘blog’ THEN 1000 ELSE reach END) sum_modified_reach FROM media_reach JOIN country ON (media_reach.country_id = country.id)GROUP BY country.name ORDER BY 2 desc;
Of course, this is just a single example of how SQL can help you answer business questions. SQL is a powerful language with plenty of possibilities. If you don’t know SQL yet, it’s just about time. For quick learning and practice, follow any of the below link:
Data Analytics Analyst
About the Author
Mayank Jain is a data analytics analyst at Vertice cloud. Prior to his role Mayank has over 4 years of experience in IT industry and currently working on the oracle analytics cloud, autonomous data warehouse and machine learning. Mayank has a technical expertise in the field of data modelling, data mining and predictive/descriptive analytics. He holds a M.Sc. in data analytics with honours from National college of Ireland, Ireland and B. Tech in Electronics & communication engineering from RTU university, India. He has also carried out his research on transliteration of word phonemes of different languages by using machine learning approach which has been accredited by quality and qualification of Ireland (QQI).