SQL: Mastering the Most In-Demand Query Language

SQL

Structured Query Language (SQL, pronounced sequel) is a versatile language that is designed for analyzing data from Relational Database Management Systems (RDBMS).

Any database that comes from the System R model developed by IBM is considered a Relational Database. The three most popular RDBMS systems are Oracle Database, Microsoft’s SQL Server, and MySQL (also made by Oracle).

Even though the language wasn’t designed to be a programming language, there are extensions to SQL that allow you to write executable programs.

According to 2016 data from Indeed, SQL was the most sought after language in the job market.

“CandGoLang”

(Source: CodingDojo)

According to Oracle, “SQL is the natural language for data analysis”. This is because relational algebra is the backbone of SQL, more so than any other language.

Using SQL With Data

SQL and eCommerce go hand in hand. Standard protocol for an eCommerce company is usually to store customer info in a RDBMS. By taking the time to learn SQL, you can quickly sort through the vast rows of customers that you (hopefully) have stored in your database.

IBM Developers Donald Chamberlin and Ray Boyce created SQL with the idea that you could pick a specific instance (or region) of a large group of data by describing it with the different language elements.

Perhaps the most commonly used SQL statement is select. Suppose we have a table called “Customers” in our database. If we wanted to see all the different rows and columns of the “Customers” table, we would type into the SQL command line:

SELECT * FROM Customers;

Suppose instead that we are only interested in two particular columns from the Customers table: CustomerName, and CustomerCity. We would replace the * from the above statement with the names of the columns that we’re looking for.

SELECT CustomerName, CustomerCity FROM Customers;

The ‘Where’ Clause

After reading the data regions above, we might want to narrow the output region to only show customers from Chicago.

SELECT CustomerName, CustomerCity FROM Customers;
WHERE CustomerCity = ‘Chicago’;

This will show us only the names of customers who are from Chicago. We could reverse this clause and only show customers not from Chicago by changing the = to <>.

WHERE CustomerCity <> ‘Chicago’.

The Where clause is also compatible with inequalities. Let’s suppose that you wanted to see a list of customers from Chicago with CustomerID numbers greater than 150.

SELECT CustomerName, CustomerCity, CustomerID, FROM Customers;
WHERE CustomerCity = ‘Chicago’;
WHERE CustomerID > 150

Selecting Rows with SQL

All of the previous example queries pulled columns from the Customer database. Suppose that we would like to create a new query of the Customer database that will give us the first five rows of the database.

SELECT * FROM Customers;
WHERE ROWNUM <=5;

If you’re familiar with any of the object oriented programing languages, you would expect ROWNUM to be a user defined variable. What makes SQL unique as a language is that the database will declare and maintain certain variables like ROWNUM.

Did you notice the asterisk after SELECT? That asterisk tells the database that this query is inclusive of all columns. The second line is what tells us that we’re only interested in the first five rows.

Now let’s assume that we are only interested in the top 25 percent of the rows in the database. Assuming that the rows of the database are organized by CustomerID, we can literally tell the SQL command line to select the top 25 percent.

SELECT TOP 25 PERCENT * FROM Customers;

If you had 100 customers ranked ordinally, the above query would give you all columns of information for the first 25 customers.

Criticism of SQL

Much of the criticism of SQL stems from the fact that the language is so different from all the other programming languages.

SQL isn’t a programming language. While there are add-ons and spin offs of SQL which allow you to write executable programs, SQL was made for data mining.

Because SQL is so different than all of the other programming languages, SQL has developed a reputation for being a difficult language to learn.

Sometimes programmers have trouble making the jump from the typical variable declaration languages like Java and Python to relational languages.

Some people have become so frustrated with the relational databases built for SQL that they have joined what’s known as the “NoSQL” or “NotOnlySQL” category. This means that they are exclusively interested in databases that offer alternatives to the SQL language for mining data.

The driving forces behind the development of the NoSQL databases are Amazon.com, Facebook, and Google. This has led some people to believe that SQL peaked in the 90s.

There are a lot of reasons for the NoSQL trend, but it all comes back to relational algebra. Nearly all of the NoSQL alternatives are designed around the variable assignment language structure that we see in Python and Java.

Conclusion: Don’t Believe The Hype

Despite Google siding with the NoSQL movement in their cloud database, the SQL language is far from dead.

The NoSQL phenomena combined with the initial difficulty that comes with learning a relational query language is enough to deter most people from learning SQL.

However, the 2016 job market still ranks SQL as the most valuable language in the job market in terms of available jobs.

If you have the patience to learn the non-programming language, SQL is a great supplemental language to have on your resume.