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 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 `;'.
Every column in an SQL table must have a type describing the data in it. SQL supports integer data: as INT4 (4 bytes) and INT2 (2 bytes), Floating point data is stored as FLOAT8 (8 bytes) or FLOAT (4 bytes). Character data is held as fixed length strings padded with blanks: CHAR(n) where n is the length. Variable length strings with a size limit are declared as VARCHAR(n). String contants are written in single quotes '.
The types DATE,TIME and DATETIME contain date information, time information or both, constants of these types are written as strings, for example:
'5th November 2001'
'2001-11-05 10:20'
'5.11.2001 10:20 gmt'
|
Expressions are type checked, and where possible type conversion is used, for example dates can be written as strings without using the conversion function DATE.
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 ;
|
creates an empty table defining the structure (see the exercise definition).
destroys a table and all the data it contains (if you have permission).
change the definition of a table (column names and types etc.)
add rows or part of rows to a table.
perform a query to view some existing data.
modify existing table entries, selecting which rows are changed.
remove selected rows from a table.
requests that a change be undone.
requests that the database make a permanent record of a change.
reads or writes data between databases and files.