SQL is a powerful tool, but as with any complex software package, it requires knowledge to get the most out of it. Here at Vision Data Insight we use Microsoft SQL Server 2016 on a regular basis and our MCSA certificated personnel have a passion for sharing their knowledge with you. To that end our blog will be routinely updated with ‘how-to’ guides and top tips so that you can learn to get the most out of the world’s number 1 database reading tool. At Vision Data Insight we believe that the best way to learn is hands-on. So in our tutorials (unless otherwise mentioned) we will be using the free sample database Adventure Works, so that you can follow along on your own computer. THE SELECT CLAUSE The select clause is the genesis of anyone’s SQL knowledge. No matter how long, and how complex your SQL know-how becomes, you’ll always be using the humble Select clause. In its simplest form: Select * from person.person
Select means just that, it requests SQL to select whatever follows the command. The asterisks (*) simply means EVERYTHING From allows you to request the table from which you will pull the information from.
So in this instance the user will receive the full person.person table. If you sample this yourself you should receive 19972 rows of data as can be seen in figure #1
Now that is a lot of data! And in practise it is unlikely that you will ever need to see it all at once, so let’s learn how we can narrow it down.
One such of way of doing this is by specifying which columns we wish to see. We do this by replacing the * with a list of column(s) that we desire. For example imagine we wish only to see the First and Last names of everyone in the person.person table.
To do this we would write the following query:
Select firstname, lastname from person.person
We still get 19972 rows of data, but as you can see in figure #2, it immediately looks much tidier and easy to read, if all you need to know is the names of people listed in the table.
THE WHERE CLAUSE
What though if we wish to return only a selected number of rows? Well that is where the where clause comes in. Still using the person.person table, let’s return only people who are not part of the email promotion (where the emailpromotion column = 0)
Select firstname, lastname, emailpromotion from person.person where emailpromotion = 0
As you can see we add the where clause after the table and immediately follow it with the column that we want to apply our restriction too before using the equals (=) operator and then the designated restriction.
Now let’s see how we can return only people whose name is ‘Andy’
Select firstname, lastname from person.person where firstname = ‘Andy’
Aha, we have 26 Andy’s! Note at this point that when we apply a written restriction (as opposed to a numerical restriction) we must enclose it in quote marks.
For added clarity we can have more than one where clause. So for example let’s see how many of our Andy’s have also opted out of the email promotions.
Select firstname, lastname, emailpromotion from person.person where emailpromotion = 0 AND firstname = ‘Andy’
Yes, as figure #3 shows, 18 Andy’s also have no email’s floating in their inbox from the people at Adventure Works!
So hopefully after this brief lesson you’ll be able to write yourself a simple select statement, and narrow the results down either by column choice, or by using a where clause to restrict the number of returned rows.
With this information, you are well on your way to learning the fundamentals of SQL. If you do have any questions or queries regarding what we have discussed though, please do feel free to drop me a line at firstname.lastname@example.org And if you feel that what we have discussed today has been of benefit then keep an eye out for our next lesson in SQL.