back to first page[..][[BR]]
back to ["Work program - Spain"][[BR]]

= The PostgreSQL System =

PostgreSQL is a free relational database system supporting the SQL language available on a variety of hardware platforms.[[BR]]

= PostGIS =
is an extension to the PostgreSQL object-relational database system which allows GIS (Geographic Information Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects.[[BR]]

= SQL =
SQL (Structured Query Language) is the standard relational query language with statements to create, remove, retrieve and maintain data in a relational database. SQL contains three main types of commands, Data Definition language (DDL), Data Manipulation language (DML), and Data Control language (DCL) statements.[[BR]]
The specific version of SQL used by PostgreSQL can be used interactively via the psql front-end, embedded in programming languages like C or called from Java, perl and tcl. PostgreSQL supports a subset of SQL3 with extra types and commands.[[BR]]

= SQL syntax =
 • SQL keywords and names contain alpha-numeric characters (including ‘_’) and must begin with a letter or ‘_’ and are case insensitive. A name containing other characters or which is an SQL keyword must be written in double quotes ‘"’. Column names, table names and database names are separate, so a column can have the same name as the table it is in. Keywords are usually written in uppercase letters. [[BR]]
 • The comment syntax is the same as C : /* ...*/, (but // comments are not allowed). Also ‘--’ comments out the rest of the line.[[BR]]
 • SQL statements end with ‘;’.

== Data Definition Statements ==

'''CREATE TABLE'''   	creates an empty table defining the structure (see the exercise definition). [[BR]]
'''DROP TABLE'''	destroys a table and all the data it contains (if you have permission).[[BR]]
'''ALTER TABLE''' 	change the definition of a table (column names and types etc.) [[BR]]

== Data Manipulation Statements ==

'''INSERT''' 	add rows or part of rows to a table. [[BR]]
'''SELECT''' 	perform a query to view some existing data. [[BR]]
'''UPDATE''' 	modify existing table entries, selecting which rows are changed. [[BR]]
'''DELETE''' 	remove selected rows from a table. [[BR]]
'''ROLLBACK'''	requests that a change be undone. [[BR]]
'''COMMIT'''	requests that the database make a permanent record of a change. [[BR]]
'''COPY'''	reads or writes data between databases and files.[[BR]]

'''SELECT''' : The most used statement
The SELECT statement is the most frequently used statement in SQL, it is used to retrieve data from one or more tables. The names of the tables used in the statement must be listed in the statement. If a column-name appears in more than one table, references to the column must be written as table.column to avoid ambiguity. The syntax for '''SELECT''' is used in '''UPDATE''' and '''DELETE''', for example:[[BR]]
{{{
#!sql
SELECT name,pay FROM payroll WHERE name = ’poor man’ ;
UPDATE payroll SET pay = pay * 10 WHERE name = ’poor man’ ;
DELETE FROM payroll WHERE pay < 0 ;
}}}

== NULL ==

is a special value that represents the fact that a piece of data that is either unknown or undefined. Note that this is not zero, or the empty string. Comparisons with NULL return unknown, use the expression IS NULL or IS NOT NULL for example:[[BR]]
{{{
#!sql
SELECT ’Unknown’ AS born ,name FROM actors WHERE born IS NULL ;
}}}

== WHERE ==
{{{
#!sql
SELECT * FROM Localisation WHERE town = 'Bilbao';
SELECT * FROM Localisation WHERE inhabitant<1000;
SELECT * FROM Localisation WHERE town LIKE'%N%';
SELECT * FROM Localisation WHERE town IN ('Bilbao','Mundaka');
SELECT * FROM Localisation WHERE inhabitant BETWEEN 1000 AND 2000;
SELECT * FROM Localisation WHERE inhabitant<1000 AND LIKE'%N%';
}}}

== ORDER BY ==
The result of a select statement is not sorted unless ORDER BY is used, when the column(s) to be used for sorting are given. Ascending order (ASC) is the default, specify DESC for descending order. For example to list films longest first:[[BR]]
{{{
#!sql
SELECT title,length FROM films ORDER BY length DESC ;
}}}

== DISTINCT ==
Duplicate rows in the result of SELECT are all shown unless DISTINCT is used. For example to remove duplicate
titles:[[BR]]
{{{
#!sql
SELECT DISTINCT title FROM Films;
}}}

== Alias ==
Tables or columns may have an alias to shorten queries or let the same table to be used several times in a single query.
Here A is used as a shorthand for the table actor[[BR]]
{{{
#!sql
SELECT COUNT(A.name) FROM actors A WHERE A.name < ’Zorro’;
}}}

== GROUP BY ==
rearranges the original table into logical partitions, in each partition all rows have the same value in the specified
column(s). For example to group films by director and give the size of each group:[[BR]]
{{{
#!sql
SELECT director,COUNT(director) FROM films GROUP BY director;
Or you can use: '''min''', '''max''', '''avg''' (average), '''sum'''
}}}

== HAVING ==
selects groups from the partitions generated by GROUP BY. For example:
{{{
#!sql
SELECT part FROM casting GROUP BY part HAVING part > "A";
}}}

== A pattern ==
is a string constant that may contain wild-card characters: ‘%’ matches any number of characters, ‘_’ (underscore)
matches a single character for example to select any actors name containing o.
{{{
#!sql
SELECT name FROM actors WHERE name LIKE ’%o%’;
}}}

== A set ==
is either a bracketed list of expressions, or a nested SELECT query. The ability to nest queries gives SELECT much of its power, for example to look for actor-directors:
{{{
#!sql
SELECT director FROM films /* Any actor-directors ? */
WHERE director IN (SELECT name FROM actors );
}}}

== EXISTS ==
is TRUE if the nested query returns any data, for example: [[BR]]
{{{
#!sql
SELECT director FROM films
WHERE EXISTS ( SELECT name FROM actors WHERE born < ’1-jan-1900’) ;
}}}

= Some links =
http://developer.postgresql.org/ [[BR]]