SQL Essentials Part One: ORDER BY and WHERE

In this post we begin our journey of learning the fundamentals of the SQL programming language by looking into the ORDER BY and WHERE Clauses!

SQL is a cornerstone in software development, shaping how we interact with databases. In this post, we'll begin our journey into understanding the essentials of SQL by focusing on two crucial aspects of SQL—the ORDER BY and WHERE clauses. These components not only allow us to sort and filter data but also play a pivotal role in enhancing the efficiency and precision of our database queries.  Let's delve into the heart of database manipulation and discover the art of crafting finely tuned queries for optimal performance. 

Tables, Rows and Simple Queries

Data is stored in Tables and for every table record there is a row, think of it like an excel spreadsheet. Each column is a different element of the table and every row is a specific record of the table. Take for example the following table. Each column has a specific element about an employee and each row is a specific record of an employee.

A sophisticated database will have many tables each with their own sets of Columns and Rows, therefore in order to retrieve data from a specific table, you will need to specify which table and column's you wish to query. For example the data in the image above uses a simple query that looks like this...

This query will SELECT all columns FROM the Employees table. Now that we have retrieved some data let's try being a little more specific.

Say instead of getting all of an employees info we wish to only see an employees first and last names. Instead of selecting all columns we would instead SELECT the specific columns we wish to see.

 

The query now returns a different set of data.

You have now successfully queried a database to retrieve a specific set of data. But that's only the tip of the iceberg with what else you can do with SQL!

ORDER BY

The ORDER BY statement allows you order information from a database based on a particular column in a table. This includes strings, integers, and dates. By default ORDER BY will return a list in ascending order. This means that it will order strings alphabetically, integers by increasing order, and dates by oldest to newest. For example let's say we would like to order our new list of employees in alphabetical order. Our initial query would remain the same but now we would add an ORDER BY statement at the end of the query, accompanied by the column we wish to order by.

Our updated query should now yield the following result.

And just like that we were able to use the ORDER BY statement to alphabetically order our employee names! But that's not all you can do with ORDER BY. Say you want the name's to return in reverse order? All you have to do is add DESC at the end of your ORDER BY statement to return a list in descending order.

The updated query now returns a list of employee names in descending alphabetical order!

Now say we have a list of suppliers from around the world. In our database each supplier has a variety of information stored such as the company name, phone number, country of origin etc. We would like to write a query that will first sort the list of suppliers in descending alphabetical order based on their country of origin, then we would like to sort the supplier in alphabetical order based on the countries name. How would we go about using the ORDER BY statement with two columns? Well thankfully it's actually very simple. All you would have to do is add the extra column in the ORDER BY statement separating it with a comma just like you did with the SELECT statement. Following these rules, we can now write a query that looks like this.

As you can see, the results show a list of companies sorted by their country in descending order first, then for companies in the same country they are sorted alphabetically by their names!

Now that we understand the purpose of the ORDER BY statement lets now talk about the WHERE clause.

WHERE

In SQL the WHERE clause allows you to select only certain pieces of information. In order to use a WHERE clause you must provide some sort of conditional, similar to an if statement. For example, let's say you need a list of customer's names as well as their company name, but you only want to see their names if they're from the city of Berlin. Well by adding a WHERE clause at the end of your query to show only the customers who are only from London you can see just that!

This query will yield the following result.

 Also, just like the ORDER BY statement can use multiple columns, you can use multiple conditions in your WHERE clause but instead of using a comma, you would instead separate the two conditions using an AND statement. For example, lets say that you would like a list with all the columns from your orders where the cost of an orders freight is over $400 and where the order's ship date is past the order's due date. Lets also add another layer of complexity and say that we would like this list to be returned in descending order based on the order date. Now we require not only multiple conditionals from our WHERE clause but we also must use an ORDER BY in the same query. Well by combining the two we can write a query that looks like this.

Now we're stepping it up a bit. The query now makes use of a WHERE clause and an ORDER BY statement. The result is as follows.

As you can see our table now returns only two results but if we look at the info we will first notice that the freight value is over 400 and the shipped date is after the required date meaning that both our conditionals in our WHERE clause were met. We will also see that the order date starts with the more recent one first meaning that our ORDER BY statement was also successfully executed! 

Wrapping It UP

As you saw, the ORDER BY statement and WHERE clause are powerful tools in SQL that allow you to order a set of information based on one or multiple columns in ascending or descending order, and to select only certain pieces of data based one one or more conditionals. There are plenty more possibilities to be explored with just these two alone but for now hopefully you understand the fundamentals!