The first time anyone looks at SQL can be overwhelming.  Mostly because it is like learning a foreign language.  You need to learn the vocabulary and the syntax to get what you want.  The first step to learning should be how to navigate information.  In my examples, I will be using examples from Microsoft Dynamics GP 2015’s database.  I will also be using the table PM00200 (Vendor Master) for all Select queries for my examples.

The simplest concept within SQL querying is the select statements.  Once you understand the concept you would be able to apply the select statements to all types of databases.  It is important to note that a select statement does not change data in any shape or form.  Select statements are simply a method to pulls data in a way that is simpler to analyze.

The vocabulary that we will be using in this post are:

SELECT – Pulls rows and columns from the database
FROM – Specifies the specific table or view.  Always used in conjunction with a SELECT, DELETE, and UPDATE statement.
WHERE – Specifies additional conditions for a SELECT, DELETE, or UPDATE statement.
GROUP BY – Specifies which columns you want to have information grouped by.
ASC – Is used with GROUP BY to place items in an ascending order.
DESC – Is used with GROUP BY to place items in a descending order.

Let’s start with the basics.  A simple select statement.  All select statements have similar components.  If you have questions about navigating the SQL query windows please see my post Understanding SQL Query Window.

• They all start with the word SELECT.
• Then they specify the columns. If you want all columns in a table to appear then you would select an asterisk (*).  If you want specific columns, then you would select the column name and separate then with a comma.
• Then you would use the word FROM to specify where the columns are located.
• Then you will type the column name.

Using my example for this you would type the following to return all columns

SELECT * FROM PM00200

This returns all the columns.  Let’s say we only want the vendor ID, Vendor Name and their state.  We would then take the column names and replace the asterisk.

SELECT VENDORID, VENDNAME, STATE FROM PM00200

Now let’s say we only want to see the vendors for the state of Illinois.  To accomplish this we would add in the WHERE clause.

SELECT VENDORID, VENDNAME, STATE FROM PM00200 WHERE STATE=’IL’

Please note that the single ‘ sometimes does not copy and paste correctly into the query screen and you will need to delete and retype this into the query window.  Adding ASC or DESC to the end will change how the order goes.

SELECT VENDORID, VENDNAME, STATE FROM PM00200 ORDER BY STATE ASC
SELECT VENDORID, VENDNAME, STATE FROM PM00200 ORDER BY STATE DESC

ASC is the abbreviation for ascending.  DESC is for descending.  Meaning ASC will place the items in alphabetical (ascending) order and DESC will place the items in reverse alphabetical (descending) order.