While most people in the business world work within spreadsheets, larger data is stored in databases, and to access this data, we often employ the use of a Structured Query Language, or SQL. While a lot of computer programming is done through unique languages that have their own rules and syntax, SQL is almost universal among relational databases, so no matter which database system you use, you can access data in SQL.
Data in a database is set up through tables. Each table is set up as a series of columns of data, which represent each category, and rows, which contain an entry of each category.
To access our data, we send the computer a statement called a query, which tells the computer the set of data we want to receive. Every query contains keywords which allow the computer to select the data we want. SQL requires these keywords to have a certain order, and we’ll explore a few of the keywords in this article. A fully constructed query would contain the following keywords:
SELECT (DISTINCT)
FROM
JOIN (ON)
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
;
On the most basic terms, we can think of selecting columns of data from our tables, so we will explore the first two basic keywords that are essential to every query, select and from. [insert practice table here]
SELECT/FROM
SELECT selects the columns of data from a table. We enter the table name we want to select using FROM. To finish the query, we add a semicolon (;). We can select one or more columns separated by commas using:
SELECT col1, col2
FROM table1;
To select all the columns in a table, we would use an asterisk (*) to represent selecting everything. To select everything in a table, we would want to use:
SELECT *
FROM table1;
DISTINCT
We often have repeating values in our tables, and sometimes we want to return a single value. For example, if we had a database of purchases, people that have made multiple purchases will show up in our table. If we wanted to make a customer list, we would have our query only show each customer’s name once. For this, we would use the DISTINCT keyword:
SELECT DISTINCT name
FROM customers;
LIMIT
Sometimes we only want to see a small amount of data at once. A table can contain hundreds, thousands, even millions of rows, and maybe we only want to see a smaller number. We can add a limit to the end of this query to only return a certain number of rows. This might be helpful if we want to get a small snapshot of what the data looks like. So if we wanted to see what the first 15 entries of our database were, we would do:
SELECT *
FROM table1
LIMIT 15;
ORDER BY (DESC)
Whenever we look at columns of data, we often want to consider sorting the data by one or more columns rather than the original entry order. If we use the ORDER BY keyword, we can choose one or more queries to sort in either alphabetical (for strings) or numeric order. For example, if we wanted our customer list from earlier to sort by the last name in a customer database, we would enter the following:
SELECT DISTINCT name
FROM customers
ORDER BY name;
If we wanted to either go by reverse alphabetical order for string data or largest to smallest for numerical data, we would use the keyword DESC, which stands for descending. For example, if we wanted to list prices from largest to smallest, we would use:
SELECT item, price
FROM sales
ORDER BY price DESC;
In our next article, we will talk about filtering using the WHERE keyword, which allows us to filter data based on the information contained inside each column.