Your First Steps To Mastering SQL (and spatial queries too)

If you feel overwhelmed by the amount of SQL resources out there, this simple tutorial is for you. I will show how to easily play around with SQL without installing anything.  You will learn how to avoid common errors and keep your queries nice and clean. I will show how SQL relates to a spatial data so you can have a good understanding before you dive into it.  After following this tutorial you will know the SQL basics and will be ready to go deeper.

Let’s first have a look at our database. You don’t need to install anything. Just visit this site and you’re set. Done.  Forget about “big” databases that need a lot of time to install and setup. Right now, this is all we need.

The list of tables is on the right. Click on Customers.

Now, a table just come up under Result. All the data from Customers table is there. Have a look at “SQL Statement”:

SELECT * FROM [Customers];

This is probably the most heavily used query in the world.

To choose only some columns, replace a star symbol with a list of columns separated by a comma:

SELECT CustomerName FROM [Customers];
or
SELECT CustomerName,Country FROM [Customers];

Put the first example into “SQL Statement” and click Run SQL. Now, do the same with the second example. Got it? Our Results table has changed. First we’ve got one column and then two. You can put more columns, but remember to put a comma between them.

SELECT * FROM [Customers] WHERE Country = 'Mexico';

This will show you only rows with the specific value in the column.

If you want to be even more specific you can look for more that one value in more than one column:

SELECT * FROM [Customers] WHERE Country = 'Mexico' AND PostalCode = '05033';

Here we will get only rows with the specific values for Country and PostalCode columns. Both values have to match the exact values to show up in the results.
And here is the next example:

SELECT * FROM [Customers] WHERE Country = 'Mexico' OR Country ='USA';

In this example, only one of the value from the columns have to match. If both does not match we will get nothing.

Remember that you can use more than two columns and values with AND and OR. You can also put both in the same query.

For now, just keep in mind that AND is really useful when you want to target rows with a specifc values in each column. On the other hand with OR you can easily get rows that have a number of alternative values.

What if you don’t know exactly what to look for? Let’s say that you’ve talked to ‘Isabel’ on the phone, but forgot her surname. You can use the percent sign to get every contact name that starts with her name:

SELECT * FROM [Customers] WHERE ContactName LIKE 'Isabel%';

This is called a wildcard and you can put it anywhere inside a value. For example to look for Isabel’s potential relatives you can use something like this:

SELECT * FROM [Customers] WHERE ContactName LIKE '%Castro%';

You can mix LIKE and = as much as you need to using OR and AND.

It’s time to play around with these examples. Run each, see if you understand them. Change column names and try some different values.

The Style Appreciation
You can write SQL in many ways, but to make your live easier follow those few simple rules:

• Always put square brackets around a table name. Table names can contain spaces or words reserved for commands like “select” and if you use them without square brackets your query will fial
• Same goes for putting apostrophes around column’s values. Always use them to
  save yourself from “stupid” mistakes
• End your SQL statements with a semicolon. Its not always required, keep it there just in case.
• Format your SQL statements like our examples. It will help you understand what goes where and later on you will find it easier to read them.

Style is important when you want to share your queries with other people. If you follow these guidelines your queries will be easier to understand. And if you use your queries on a different system you will need to tweak it a lot less to make them work.

What?! My SQL won’t work everywhere?
Yes, it sometimes won’t work the way you intended. Let me explain.
There are many database systems that uses SQL. And while SQL is an ISO standard each system might use a different way to implement a specific command.

All queries that you’ve seen so far will work everywhere. Assuming you have the same table and the same column names. One useful “extension” to SELECT won’t. SELECT TOP will show you only a specific number of rows. This will work on our database:

SELECT TOP 10 * FROM [Customers];

This will work on some systems but on other a slightly different one will work instead:

SELECT * FROM [Customers] LIMIT 10;

and again there are some systems where the following will work:

SELECT * FROM [Customers] WHERE ROWNUM <= 10;

And if you try the last one on our database you will get an error.
That’s pretty bad, right? We have three versions of the same command and some of them work and other doesn’t. Not really.

If you know that you can do something like that with SQL. And you understand how SELECT works. Finding the right version for your specific system is quick as wink.

For now, focus on understanding the most important concepts. Let the search engine take care of the rest.

The same goes for working with a spatial data. You will use the same commands
to work with it and the same basic concepts. But you have to know a bit more to get to the spatial part.

In a minute I will talk about data types and functions. Data types define what can you store in a table.Functions do work on your data for you. You need to understand both to work a spatial data.

Now let’s get back for a minute to our LIMIT examples. Remember that the last version,the one with ROWNUM after WHERE? This statement will fail on our system. You will see an error message on your screen after hitting Run SQL. It’s telling you that it can’t find ROWNUM column. Right. There is no such column in Customers table.

A specific error message can be different from system to system. One that you will see a lot when experimenting is a “syntax error”. It usually means that you’ve misspelled a command or forget some part of it. Look again at the SELECT examples. They have a certain structure,right? After a while you will get a feel of it. You’ll able to spot most of those error straight away.

Also, keep in mind tips from “The Style Appreciation” section. They eliminate some “stupid” errors and can save you a lot of learning time.

Ok, now back to data types. Data types control what you can put into each column.
They also control how long a data in each column can be. That way you always know what’s inside a table.

Some common data types in use are VARCHAR and INTEGER. Fist, can store a bunch of characters, second a number. Both have a maximum length. So you can’t go beyond that.
We won’t go deep into data types right know. Keep in mind that every column has one.

Data types are important for a spatial data too. To store a geometry we use data types like POINT, LINE and POLYGON. You will see in a spatial part how does this work exactly.

You can fully realize the power of geometries only when you consider what you can do with them.

This is where functions comes in. First of all, they let you do something with a data and get back changed results.

For example you can use LCASE function to change all the characters in a given column to lowercases:

SELECT LCASE(CustomerName) FROM Customers;

Look at the name of the column. Its pretty random. A better way to do this is to choose
something more readable for our new column:

SELECT LCASE(CustomerName) AS Customer FROM Customers;

AS let you create an alternative name for a column.You can also use it with a plain column to make it more readable:

SELECT CustomerName AS Customer FROM Customers;

So in the same way you can work with a spatial data. For example functions like LENGHT and AREA will measure a geometry. Pretty simple right? But consider doing it on a table that has one milion places. Databases are optimized to this kind of stuff quickly. Very quickly.