SQL stands for structured query language, a popular method to query databases of all sizes. While professional database administrators and SQL programmers are in high demand, you don’t need a computer science degree to understand basic SQL. If you are a financial analyst, business owner, or online entrepreneur, these basic SQL tips will help you navigate the data behind your website, customers, and more.
What is SQL?
You can use SQL to get data from a database. Whether the database has one record or one million, SQL is likely the best tool to quickly gather and summarize information from the database. Often pronounced “sequel,” the first version goes all the way back to 1974. The most recent update to the language released in December 2016.
There are two popular versions of SQL that most non-programmers who care about databases should be aware of. The first is the main version of SQL, which is commercially available to use with large database programs. Oracle is one of the largest corporate database providers in the world, and you can hunt for data in Oracle databases using SQL. A lightly modified version of SQL, Access SQL, is the query language in Microsoft Access, the most popular database app for small businesses and a common tool on computers at Fortune 500 companies alike.
The second version of SQL that you should know about is MySQL. MySQL is an open source version that works very similarly to the core version of SQL for our purposes. MySQL is most popular on the web. If you have a WordPress site with CPanel hosting, you can login to an app called phpMyAdmin to access your databases and run SQL queries.
SQL and MySQL are flexible, and there are many options and interfaces to run queries. You can build SQL queries in a command line text based editor or you can use a query builder with a graphical interface. Either way, it gets you to the same result. Now let’s look at some of the basic SQL queries you may need to use for your work or business.
SELECT Query
The most basic and common method to get data from a table is a SELECT query. As the name implies, this query is used to select data from a database. You can create a select query with many variables and permutations. To better understand how it works, here is an example of where you may want to use a SELECT query.
Let’s say you have a database with one table called Customers, and that table contains fields with the customer name, address, city, state, zip code, and phone number. The fields are named in all capital letters, because that’s how it was built in the beginning. You want to send a mailing to all customers, so you want to generate a list of all customers with their address information. Select query to the rescue!
To run a select query, we need to tell the computer what we want and where to find it. In this example, the SQL code would be written as follows:
SELECT CUSTOMERNAME, ADDRESS, CITY, STATE, ZIPCODE
FROM Customers;
But what if you only want to mail to customers in California and Oregon? You could export your results to Excel, filter on California and Oregon, and do a mail merge. Or you can skip a couple of steps and just export your california customers. In this case, you just have to add one line to your query to tell SQL you only want those customers. In this case, we will use WHERE to limit the query results to California and Oregon.
SELECT CUSTOMERNAME, ADDRESS, CITY, STATE, ZIPCODE
FROM Customers
WHERE STATE IN (‘California’ ,’Oregon’);
Note that in both cases, the query ends with a semicolon. This is required to tell the computer where your query code ends and to run that query. The commands, SELECT, FROM, and WHERE, are all capitalized. There are some other required characters, like commas and apostrophes, but you can always search online for an example of what you are trying to do and find the basic code so you can just edit with your database information before running the query.
A select query can include multiple where clauses, from clauses, and more. Find more examples and rules here at SQLCourse.
UPDATE Query
If you are working with a database and want to add to the dataset, SQL allows for that as well! You need permissions to both read and write to the database for this to work, and should always proceed with caution when manipulating data. If you run a bad SELECT command, the worst case scenario is overloading the server or getting bad results. If you run a bad UPDATE command, you can completely ruin your data. Always backup and proceed with caution.
Let’s say you are working with the same database and want to update one customer’s phone number. Here is the code you would use:
UPDATE Customers
SET PHONE = ‘805-555-5555’
WHERE CUSTOMERNAME = ‘Due’;
In this case, the customer Due would have its phone number updated to 805-555-5555. But if you forgot to add WHERE, it would update every customer’s phone number to 805-555-555. This is why it is important to be very careful and pay attention to every detail when updating or editing data with SQL.
Gateway to data mastery
SQL is a gateway to data mastery, and after a little practice you can whip up your own queries very quickly. If you want to know more about what SQL can do, there are some great online courses and resources, such as W3 Schools. SQL can do a lot more than just SELECT and UPDATE. Here is a longer list of popular SQL commands, courtesy of W3 Schools:
Info from W3 Schools:
- SELECT – extracts data from a database
- UPDATE – updates data in a database
- DELETE – deletes data from a database
- INSERT INTO – inserts new data into a database
- CREATE DATABASE – creates a new database
- ALTER DATABASE – modifies a database
- CREATE TABLE – creates a new table
- ALTER TABLE – modifies a table
- DROP TABLE – deletes a table
- CREATE INDEX – creates an index (search key)
- DROP INDEX – deletes an index
I have been working with SQL on and off for about a decade, and I’ve never used most of these. I use SELECT by far more than anything else. Occasionally, I use commands to drop a table, update data, and insert into. What you use and need is based on your work and goals.
But either way, you are off to the races. SQL is a powerful tool and opens up many doors, and maybe even a career path. But one thing is certain, if you don’t practice and try, you won’t learn and get better. So go play around with SQL and find opportunities to improve your business and skills with data.
