back to first page..
back to Work program - Spain
The PostgreSQL System
PostgreSQL is a free relational database system supporting the SQL language available on a variety of hardware platforms.
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.
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.
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.
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.
- The comment syntax is the same as C : /* ...*/, (but comments are not allowed). Also ‘--’ comments out the rest of the line.
- SQL statements end with ‘;’.
Data Definition Statements
CREATE TABLE creates an empty table defining the structure (see the exercise definition).
DROP TABLE destroys a table and all the data it contains (if you have permission).
ALTER TABLE change the definition of a table (column names and types etc.)
Data Manipulation Statements
INSERT add rows or part of rows to a table.
SELECT perform a query to view some existing data.
UPDATE modify existing table entries, selecting which rows are changed.
DELETE remove selected rows from a table.
ROLLBACK requests that a change be undone.
COMMIT requests that the database make a permanent record of a change.
COPY reads or writes data between databases and files.
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:
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:
SELECT ’Unknown’ AS born ,name FROM actors WHERE born IS NULL ;
WHERE
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:
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:
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
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:
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:
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.
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:
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:
SELECT director FROM films WHERE EXISTS ( SELECT name FROM actors WHERE born < ’1-jan-1900’) ;
