Category:SQL Examples

From Manipedia

chioracroe

[edit] Introduction

On August 18th, 2006 Adam suggested that we begin a GeoReference thread devoted to small SQL examples (thread below). This MS Word XP (2002) document is an attempt to organize various SQL examples posted to GeoReference and Manifold-L. It is hoped that the Table of Contents will allow people to navigate quickly to their area of interest.

Initially, I kept all the related postings and there was considerably more information provided then just the basic SQL. I have stripped some of that out in the following summary; however, there are many entries that are rather long and contain a lot of the back and forth commentary which I found helpful. L. Ketch – Oct 2, 2006

[edit] Spatial SQL tutorial video

Art Lembo developed a comprehensive Spatial SQL tutorial video, which is freely available. Highly recommended for any new starters.

[edit] How to approach SQL and understand queries

For most people, SQL hurts the brain at first. Then it starts to click. You just need to keep pushing at the boundary, and eating high-quality protein so your brain can rewire itself while you sleep.

The difficulty for anyone whose programming experience has been a bit casual (as mine had been, and I dare say yours has been too) is a natural tendency to think in procedural terms. This then this then this. SQL isn't procedural (which is why a query is not a script), but relational. It works on sets, not on individual pieces of data. (That's why it's so fast.) Put this set (or table) beside this set, then do this to every row; call the resulting set this. In a sense, getting your head around the procedural—relational difference is the whole of the "secret". There are probably shortcuts to grasping it, but I bet they differ quite a bit from person to person, and that they are often only "useful" after you've covered the distance the long way.

Above, when you write for example:

"For example, I can read from the query above that you set the component coordinate system... Then it's a select query, etc.... is there any way to look at it, so that when I see SELECT, I can go to the help file and say OK, what parameters are available for SELECT."

I think you are reading more or less procedurally. Instead, try starting to read any query by beginning with the innermost FROM. (The FROM clause of the innermost subquery, or of the main query if there is only one.) This is what the engine itself does. For example, the bit that I pulled out above to explain what [N] does. Note the set or sets taken as input, what is done, and the resulting set passed as output. Then read out a layer (the layer the result is passed to), and so on, to the outermost SELECT (which is the interface with the outside world). Then try reading in the same sequence, only in reverse.

SQL and programming have to be approached from a 'doing' perspective - reading will get you started but doing is essential. There is really no such thing as cut and paste solutions. One turns to SQL or programming because it can't be handled (easily) at the GUI level, the procedure has to be done over and over or there is some level of uniqueness in the problem. The unique aspect implies that some part of the solution will always be specific to the problem of the moment and requires special handling. It can be painful but jumping in over your head seems to be a prerequisite :) Source: Concentric Buffers Thread

<source lang="sql">
Select * from [Table]
where [ID] = 123;
</source>