Basic SQL: A Business User’s Guide to Writing Queries

Most people are familiar enough with basic SQL to be able to provide a simple explanation of it. Unfortunately, few know how to use the language or apply it to their business goals.

Many see SQL as an advanced language intended for use by data professionals and programmers. In reality, it’s a fairly simple language to learn, and a very good business tool.

Now more than ever, data is a driving factor behind the business decisions people make every day. If you understand SQL, you have skills required to access and analyze that data quickly.

If you don’t, that’s okay. The beauty of SQL is that beginners can become proficient quite quickly. Pick up SQL today, and you could be writing some slick queries tomorrow. This guide will help you get started.

The Value of SQL

If you work data that’s been stored in a relational database, you can use SQL to access that data. Don’t worry about the relational database stuff. It’s more likely than not, that this is how your data is stored.

Imagine that you own or work for a medium to large ecommerce business. You have lots of data stored about customer purchase history. There’s a lot of valuable information in there. You could use it to learn who is buying which products, and when they are purchasing them, if you used SQL. There are many other applications beyond that.

The problem with very large sets of data is that they are too cumbersome. Excel is limited in what it can handle, and attempting to open up huge files can slow systems down to a halt. Yes, there are manipulation tricks such as creating a CSV file. Still, those are crap shoots at best. On the other hand, SQL was made for data sets like this. It can do everything you’re used to doing in Excel and so much more.

Writing a Basic SQL Query

The first thing you’ll need to do is identify how many databases you have, what they are called, the names of the tables in each database, and finally the data within each of those. Once you have this information, you can decide which database, and which tables you want to work with.

In SQL there’s a simple command, ‘SHOW DATABASES’ (Note: this is MySQL Specific). Let’s go back to the original ecommerce example. Imagine that your company is in the farm and garden space. Specifically, you sell seeds, fertilizer, and other goods. One division of your business is focused on selling to consumers with hobby gardens. You have a database for them called CustomerDB. The other division sells to commercial farming operations. That information is stored in a database you’ve named BusinessDB. As you use SQL, you will need to pick which database concerns you at any moment.

Let’s go back to the original ecommerce example. Imagine that your company is in the farm and garden space. Specifically, you sell seeds, fertilizer, and other goods. One division of your business is focused on selling to consumers with hobby gardens. You have a database for them called CustomerDB. The other division sells to commercial farming operations. That information is stored in a database you’ve named BusinessDB. As you use SQL, you will need to pick which database concerns you at any moment.

The next command to learn is ‘SHOW TABLES’ (Note: this is MySQL Specific). This will list the tables in each database. So, if you type ‘SHOW TABLES IN CustomerDB’, you’ll receive a list of files in that database. Those might be:

• Customers_Midwest
• Customers_Southeast
• Customers_California
• Cust_Products
• Customers_NewEngland

Now that you know the tables (sometimes called files), you can determine which information is in each. Use the DESCRIBE command for this (Note: This is MySQL Specific). For example, ‘DESCRIBE Cust_Products’ will show you the information in that table such as:

• Product_Name
• Product_Number
• Product_Description
• Unit_Price
• UnitsSold_Quarter1
• UnitsSold_Quarter2
• UnitsSold_Quarter3
• UnitsSold_Quarter4
• UnitsSold_LastYear

Your database hierarchy is the name of the database, the tables within it, and then the fields within each table. If you have used Excel to access data, a table would be like an Excel file. The fields would be like the columns in the file. Finally, a set of related information would be like a row in an Excel table.

A Basic Query Example

Once you know your hierarchy, and the information that’s in each table, you can use SQL to extract useful information. Imagine you want to know which products were sold in the first quarter of the year that cost more than 50 dollars per unit. You want this information sorted by product number.

The first thing you need to learn is the SELECT command. You use this to choose which fields you are interested in. Remember that a large table could have dozens of fields in it, and you likely only care about a few. In this case, you want product number, product name, unit price, and the units sold in quarter 1. Your query would look like this:

 
SELECT Product_Number,
       Product_Name,
       Unit_Price,
       UnitsSold_Quarter1; 

Next is the FROM clause. This simply states from where you want the information. Here, you are interested only in the Cust_Products table. You can leave the others out of this particular SQL statement. Adding the FROM clause looks like this:

SELECT Product_Number,
       Product_Name,
       Unit_Price,
       UnitsSold_Quarter1
FROM   Cust_Products;

Next comes the WHERE clause. You use this to pull only the data that meets your criteria. In this case, you only want products where the unit_price is more than 50 dollars. That will look like this:

 SELECT Product_Number,
       Product_Name,
       Unit_Price,
       UnitsSold_Quarter1
FROM   Cust_Products 
WHERE  Unit_Price > 50;

Now imagine that you wanted to add some additional criteria. This is where the AND operator comes in. Let’s say you wanted to limit your query to products that cost more than 50 dollars but less than 75.

 SELECT Product_Number,
       Product_Name,
       Unit_Price,
       UnitsSold_Quarter1
FROM   Cust_Products 
WHERE  Unit_Price > 50 and Unit_Price < 75;

In most cases, you’ll want your data sorted in a certain way. One clause to learn is ORDER BY. Use this to sort your results by any of the fields you have selected. In this case, you want your results in product number order.

SELECT   Product_Number,
         Product_Name,
         Unit_Price,
         UnitsSold_Quarter1
FROM     Cust_Products 
WHERE    Unit_Price > 50 and Unit_Price < 75;
ORDER BY Product_Number;

Next is the GROUP BY clause. This will put together data that has similarities. Maybe you have several products with the same Unit_Price. By using the GROUP BY clause you can ensure that all like priced items are listed together.

SELECT   Product_Number,
         Product_Name,
         Unit_Price,
         UnitsSold_Quarter1
FROM     Cust_Products 
WHERE    Unit_Price > 50 and Unit_Price < 75
GROUP BY Unit_Price ;

Sometimes, you deal with such a large amount of data that running a single query of a file can take ages. In many instances, you don’t even need all of that information. This is where the TOP argument comes in handy. It allows you to limit the number of results a query returns to you.

The TOP argument is very useful if you aren’t 100% that a query you’ve written will give you the results you need. Rather than running an SQL command that could take several minutes and eat up a lot of CPU, you can apply the TOP command.

Your query will quickly return a small set of results. You can look at those, and if you see what you need, run the query without the TOP statement. The following statement will limit your results to 250 products.

SELECT   TOP 250 Product_Number,
         Product_Name,
         Unit_Price,
         UnitsSold_Quarter1
FROM     Cust_Products 
WHERE    Unit_Price > 50 and Unit_Price < 75;
ORDER BY Product_Number;

Using SQL Results

Once you have a set of results, your next step is determining where to use them. What you have is data, and that is a powerful thing. You can use data in blog posts, reports, presentations, and landing pages.

Basically, any place where you think data will provide proof of what you are saying, and make your words more convincing is suitable.

There are many tools and resources you can use to turn data into useful content. These include, Canva, Visual.LY, SlideShare and FlowingData Raw.

For a global audience, you can hire a translation service such as The Word Point to localize and translate your data. This will guarantee that it is useful to all who encounter it.

For a global audience, you can hire a translation service such as The Word Point to localize and translate your data. This will guarantee that it is useful to all who encounter it.

Conclusion Learning Advanced Techniques

SQL is an extraordinarily powerful language. You can do a lot with it simply by using the commands described here. However, you don’t need to stop there. If you dig deeper, you can learn advanced commands and techniques to accomplish a variety of tasks. This includes querying files from different databases at once, joining files together, even modifying tables.

If you looking to learn SQL, then you have come to the right place. I would recommend exploring the Getting Started page. It will walk you through key articles so you’ll get the basics down in no time.