DriveWorks Pro 22

How To: Write Database Queries Using SQL (KB12121044)

Introduction

The document will show you how to write basic queries by using SQL, which will then make it easier to retrieve data for use in your DriveWorks projects.

To make queries against databases such as Microsoft SQL Server, Oracle, MySQL, and so on, you typically write your query in a language called SQL which standards for Structured Query Language. This language is designed to resemble English to make it relatively easy to perform complex queries against a lot of data.

Different database systems have slightly different variations of the SQL language, but the differences are generally minor and well documented. In this document we are going to focus on Microsoft SQL Server specifically.

There is a lot of great information about SQL and databases on the Internet which can be found using your favorite search engine, one such resource is the w3schools SQL tutorial.

Simple Queries

SQL queries can be broadly broken down into 3 basic types: those that retrieve data, those that modify data, and those that modify the database itself.

This tutorial only deals with SQL queries which retrieve data, an example of which is given below:

SELECT * FROM Customers

This is a very simple query which retrieves every customer from the "Customers" table.

If we wanted to be more specific, and only get the first and last name's of our customers, we could instead write something like:

SELECT FirstName,LastName FROM Customers

The query above uses a comma-delimited list of the names of the columns (or fields) to get only the information we want, where as the first query used the special character "*" to get all columns.

We might want to take the example further though, and only retrieve customers that are in a particular state:

SELECT FirstName,LastName FROM Customers WHERE State='Massachusetts'

By using the "WHERE" keyword, we can apply filters to the data to refine the results we receive, in this case, we are filtering the data so that we only get customers who are in Massachusetts. Because the State column in this case contains text, we need to surround the state name with single-quote marks so that the database server doesn't think we are talking about a column or a keyword.

If we wanted to filter the data even further, so that we retrieve customers in Massachusetts who are over 30 years old, we could do something like:

SELECT FirstName,LastName FROM Customers WHERE State='Massachusetts' AND Age > 30

This query uses the "AND" keyword to make sure both filters (or clauses) get applied. Also, you may have noticed that 30 is not surrounded by quotes, this is because the Age column contains numbers which don't need to be surrounded by quotes.

Here is a list of the common types of comparisons that you may use in your queries:

So, let's take a look at a slightly more sophisticated example:

SELECT FirstName,LastName FROM Customers WHERE State='Massachusetts' AND (Age <= 10 OR Age >= 20) AND FirstName LIKE 'Mar%'

Here we're retrieving all customers whose first name begins with "Mar", that live in Massachusetts, and are either younger than 11, or older than 19.

In this case the brackets aren't strictly needed, but you can use them in a similar way to mathematical formulas to group together related filters, and control the order in which they are applied.

Querying across Tables

In some cases, you may want to retrieve and filter data from multiple tables - for example, many ERP and CRM systems store related data in different tables.

Let's take a simple example to start with, let's assume you have the following data you want to query:

"States" Table

"Customers" Table

Unlike the first queries we were doing where the State name was stored in the customer table, now a number is used instead. So how do we look up customers in Orlando if we don't know the state number?

Let's start small:

SELECT FirstName,LastName FROM Customers INNER JOIN States ON Customers.StateNumber=States.StateNumber

Okay, it's a bit more complicated than some of the previous queries, but if you break it down, it's pretty simple.

In English the query would be something like: take the Customers table, and join it with the States table by looking for rows in the States table where the StateNumber column is a match for the StateNumber column in the customers table.

The results of the join are shown below:

From there, it's easy to now query for customers in Orlando by just adding a WHERE filter:

SELECT FirstName,LastName FROM Customers INNER JOIN States ON Customers.StateNumber=States.StateNumber WHERE StateName='Orlando'