SpatiaLite logo

Basics about SQL queries

2011 January 28

Previous Slide Table of Contents Next Slide

The following SQL queries are so elementary simple that you can directly verify the results by yourself.
Simply follow any example executing the corresponding SQL statement (using copy&paste).

SELECT *
FROM reg2001_s;
This one really is the pons asinorum of SQL: all columns for each row of the selected table will be dumped following a random order.

SELECT pop2001, regione
FROM reg2001_s;
You aren't necessarily obliged to retrieve every column: you can explicitly choose which columns have to be included into the result-set, establishing their relative order.

SELECT Cod_rEg AS code, REGIONE AS name,
  pop2001 AS "population (2001)"
FROM reg2001_s;
You can set a most appropriate and intelligible name for each column, if you feel this is appropriate.
this example shows two important aspects to be absolutely noticed:

SELECT COD_REG, REGIONE, POP2001
FROM reg2001_s
ORDER BY regione;
SQL allows you to order rows into the result-set in the most convenient way for your purposes.

SELECT COD_REG, REGIONE, POP2001
FROM reg2001_s
ORDER BY POP2001 DESC;
You can order in ASCending or DESCending order at your will: the ASC qualifier is usually omitted, simply because this one is the default ordering.

SELECT COD_PRO, PROVINCIA, SIGLA
FROM prov2001_s
WHERE COD_REG = 9;
Using the WHERE clause you can restrict the range: only rows satisfying the WHERE clause will be placed into the result-set: in this case, the list of Counties belonging to Tuscany Region will be extracted.

SELECT PRO_COM, NOME_COM, POP2001
FROM com2001_s
WHERE COD_PRO = 48;
Same as above: this time the list of Local Councils belonging to the Florence County will be extracted.

SELECT PRO_COM, NOME_COM, POP2001
FROM com2001_s
WHERE COD_REG = 9 AND POP2001 > 50000
ORDER BY POP2001 DESC;
You can combine more conditions under the same WHERE clause: this time the list of the most populated Local Councils (> 50,000 peoples) belonging to Tuscany Region will be extracted.
And the result-set will be ordered accordingly to decreasing population.

SELECT PRO_COM, NOME_COM, POP2001
FROM com2001_s
WHERE nome_com = 'ROMA';
You can obviously use text strings as comparison values: in pure SQL any text string has to be enclosed within single quotes.
[SQLite is smart enough to recognize double quoted text strings as well, but I strongly discourage you to adopt this bad style as your preferred one].
Please note well: string values comparisons for SQLite are always case-sensitive.

SELECT PRO_COM, NOME_COM, POP2001
FROM com2001_s
WHERE nome_com = 'L''AQUILA';
When some text string contains an apostrophe, you have to apply masking.
An extra single quote is required to mask every apostrophe withing the text string: e.g.: REGGIO NELL'EMILIA has to be correctly masked as: 'REGGIO NELL''EMILIA'

SELECT PRO_COM, NOME_COM, POP2001
FROM com2001_s
WHERE nome_com LIKE 'roma';
You can use the approximate evaluation operator LIKE to make text comparisons to become case-insensitive.

SELECT PRO_COM, NOME_COM, POP2001
FROM com2001_s
WHERE nome_com LIKE '%maria%';
And you can use the operator LIKE so to apply partial text comparison, using % as a wild-card: this query will extract any Local Council containing the sub-string 'maria' within its name.

SELECT PRO_COM, NOME_COM, POP2001
FROM com2001_s
WHERE nome_com IN ('ROMA', 'MILANO', 'NAPOLI');
Sometimes may be useful using a list of values, as the one shown above.

SELECT PRO_COM, NOME_COM, POP2001
FROM com2001_s
WHERE POP2001 BETWEEN 1990 AND 2010;
Another not often used, but sometimes useful comparison criterion is the one to set a range of values to be checked.

SELECT PROVINCIA, SIGLA, POP2001
FROM prov2001_s
WHERE COD_REG IN (9, 10, 11, 12)
  AND SIGLA NOT IN ('LI', 'PI')
  AND (POP2001 BETWEEN 300000 AND 500000
    OR POP2001 > 750000);
Using SQL you can set any kind of complex WHERE clause: there are no imposed limits.
And this one is a really fantastic feature, disclosing potentially infinite scenarios.
Just a short explanation: the previous query will:
SELECT PROVINCIA, SIGLA, POP2001
FROM prov2001_s
WHERE COD_REG IN (9, 10, 11, 12)
  AND SIGLA NOT IN ('LI', 'PI')
  AND POP2001 BETWEEN 300000 AND 500000
  OR POP2001 > 750000;
Please note well: in SQL the logical connector OR has a very low priority.
Check by yourself: omitting to properly enclose the OR-clause within brackets produces very different results, isn't ?

SELECT *
FROM com2001_s
LIMIT 10;
There is a last sometimes useful SELECT clause to explain: using LIMIT you can set the maximum number of rows to be extracted into the result-set
(very often you aren't actually interested into reading a very densely populated table as a whole: a shorted preview will easily be enough in many cases).

SELECT *
FROM com2001_s
LIMIT 10 OFFSET 1000;
And that's not all: SQL doesn't constraints you to read a limited row-set necessarily starting from the beginning: you can place the start-point at your will, simply using OFFSET combined with LIMIT.

Learning SQL isn't so difficult after all.
There are very few keywords, language syntax is notably regular and predictable, and query statements are designed to resemble plain English (as much as possible ...).
Now you are supposed to be able to attempt writing (simple) SQL query statements by yourself.

Previous Slide Table of Contents Next Slide

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.