Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	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:

SQL names are case-insensitive: REGIONE and regione refers the same column.
SQL names cannot contain forbidden characters (such as spaces, brackets, colons, hyphens and so on).
And they cannot correspond to any reserved keyword (i.e. you cannot define a column named e.g. SELECT or FROM, because they shadow SQL commands)
anyway you can explicitly mask any forbidden name, so to make it become fully legal.
In order to apply such masking, you simply have to enclose the full name within double quotes.
you can obviously indiscriminately double quote every SQL name: this is completely harmless, but not strictly required.
in the (extremely rare, but not impossible) case your forbidden name already contains one or more double quote characters, you must insert an extra double quote for each one of them.
e.g.: A"Bc"D has to be correctly masked as: "A""Bc""D"

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.

Ascending order means A-Z for text values: and from lesser to greater for numeric values.
Descending order means Z-A for text values: and from greater to lesser for numeric values.

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:

include any County in Central Italy (Regions: Tuscany, Umbria, Marche and Lazio)
excluding the Livorno and Pisa Counties
then filtering Counties by population:
- including the ones within the range 300,000 to 500,000
- including as well the ones exceeding 750,000 peoples

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

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

	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.