Querying Data (Simple)

In this blog post, I will show how to create simple queries on the database in order to filter data. This may be required for a number of reasons, for example if you wish to produce a report showing the number of items bought by a particular customer and the details of those items.

For the purpose of this tutorial - I have used a Red Gate Tool called SQL Data Generator in order to pre-populate the database I created in the last tutorial with 1000 rows of data (who really wants to spend time typing all that in manually?!) For more information on Red Gate and for a downloadable link, please click here: 

http://www.red-gate.com/products/sql-development/sql-data-generator/

Ok, so now your database is full of data. You may decide you wish to bring the entire data set of a table back using the following: 

To run a query in SQL Server Management Studio, make sure that you click the "Execute" button:

You should then see results similar to the following (obviously it won't be identical as the data contents will vary):

However as you can probably guess although the query runs quickly as it is only bringing back 1000 

records of data, imagine how the time will increase when bringing back 100,000 records with every single column, with lots of joins into other tables and bringing back all their data too. Some databases may have millions of records! Plus is it really necessary to bring back every single column of data? In almost all cases, the answer will be no. Therefore we need to think about adding clauses and conditions.

It'll be very rarely that you will want to bring back absolutely everything from a database table without having some form of condition on it; there's just no need for it, especially not if you're creating well formatted specific reports. 

So, let's reduce the number of columns. I have decided that I only want to bring back the CustomerId, FirstName, LastName and ContactNo. The query would read: 

Don't forget to execute the query! 

You will see that the number of columns brought back will be decreased. You can bring back as many or as few results as you need. Now you have decided that you wish to filter by all the Customers who have a last name beginning with the letter S, as this will reduce the number of records brought back. The query would read: 

Again, don't forget to execute the query!

The LIKE operator allows you to match a character string found within any column in the table specified to a specific pattern using a WHERE clause. The LIKE operator has the following wildcard characters:

This can be particularly useful when searching for similar or like data, particularly for items that have similar names or for similar last names. There are different types of operator that impact on what the result set will look like, for example, IN, BETWEEN and AS. These will be discussed and used in later tutorials.

Now, we can also add an ORDER BY expression that will allow us to specify which column we wish to display in either ascending or descending order. An example of this would read:

By default, a straight ORDER BY expression will order a column in ascending order (a-z). However if you wish to reverse this sort, use DESC after declaring the column name:

And that's all there really is to it! You've just created your first database query. However, what if you need to take data related to a user found from another table? The next lesson will focus on joining information from other tables to further customise a result set.