SQL - Tutorial and Reference (EN)

This is an attempt to introduce you to SQL and to explain what relational databases are, what the relational datamodell is and how you can extract data from databases with SQL. -- english, deutsch, russian --
Categories: 

Preface

The author has written this SQL-help in his free time because a quick and permanently online SQL-tutorial was needed. This is only a part of the whole SQL-standart, for more information about its implementations on different database systems please contact the software manyfacturers. The "Standard-SQL", first developed by IBM, was improved by software firms that develop relational database management systems (RDBMS). But all of this improvements can not be discussed in an brief help file. The small table after the command/keyword indicates in some cases, in wich system this command/keyword is implemented.

Warranty
This document is not an official publication and is provided "as is". This document is not a full technical documentation and not completely error free. There is no warranty of any kind for errors in comercial or public projects, which result from wrong or wrong understanded descriptions in this pages.

Categories: 

Databases

What are databases? How can i create databases? How can i delete databases? How can i modify databases ?
Categories: 

CREATE DATABASE

Syntax:


    CREATE DATABASE dbname
    [ IN dbspace ]
    [ { WITH [ LOCATION = 'dbpath' ] [ TEMPLATE = template ] [ ENCODING = encoding ] }
    | { WITH [ BUFFERED ] LOG }
    | { WITH LOG MODE ANSI } ]

Description:

    Creates a new database in the database management system. Attention, the syntax and the options vary from database system to database system.

    Example (common):

CREATE DATABASE example;

Creates a new database with name example.

Example (Postgresql):

CREATE DATABASE expamle WITH LOCATION = '/var/lib/postgresql/data' ENCODING='utf-8'

Creates a new database examle, which uses utf-8 as character encoding and is phisically stored in
'/var/lib/postgresql/data'.

Example (Informix):

CREATE DATABASE expamle IN dbsl01 WITH BUFFERED LOG;

Creates in DB-Slice dbsl01 a new database example with buffered logging turned on.

Categories: 

DROP DATABASE

Syntax:

Description:

    Deletes the specifyed database.

    Example:

DROP DATABASE example;

Deletes the database 'example'.

Categories: 

Tables

What are tables in relational databases? How can i crate tables? How can i delete tables? How can i change the structure of relational database tables?
Categories: 

CREATE TABLE - create a database table

Syntax

Description:

    CREATE TABLE creates a new empty table in the selectted database from the sructure description in the command.

Datatype

    Datatype is here a type of values known by the system which would be filled into the specifiyed database table column.

    Text data types:

      CHARACTER - textfields
      CHAR - same as CHARACTER

    Numeric datatypes (exact numeric):

      DECIMAL - a number with decimals
      DEC - same as DECIMAL
      NUMERIC - same as DECIMAL, but the number of decimals is ...
      INTEGER - number without decimals (plain numbers), is used without specifying a fieldsize
      because it is defined by the database system
      INT - same as INTEGER
      SMALLINT - number without decimals (plain numbers), is used without specifying a fieldsize
      because it is defined by the database system and uses probably less space than INTEGER, because is designed for small numbers. The format depends on DBMS.

    Numeric datatypes (approximate numeric):

      FLOAT - numbers in exponential form
      REAL - such as FLOAT, but is used without specifying the fieldsize, because it is defined by the database system.
      DOUBLE PRECISION - such as REAL, but probably with doppelten precision
      DOUBLE - same as DOUBLE PRECISION

    Other datatypes, not defined in the standart SQL :

      DATE - datevalue, im most european implementations in the following format: dd.mm.yyyy
      TIME - timevalue, im most european implementations in the following format: hh-mm-ss
      VARCHAR - string with variable length, depending on the impementation maximal 254 to 2048 characters.
      LONG VARCHAR - sting with variable length, depending on the impementation maximal 16 kilobytes.

    Example:

 CREATE TABLE torder (
 onum INTEGER,
 anz  INTEGER );

Creates an empty table torder with records, which have two integer fields
onum and anz.

Size

    Syntax: whole size [,decimals]

    Description:

    Decimals are allowed only with DECIMAL and NUMERIC declarations. The size declaration is allowed only with variable length datatypes. When the size declaration is not given for the variable length fields, then the default value 1 would be used by the system. Exception: for CHARAKTER a size must always be specifyed (no default values).

    Example:

CREATE TABLE tverkauf (
 vnum  INTEGER,
 vname CHAR(30),
 stadt CHAR(20),
 prov  DECIMAL);

Here is an empty table tverkauf with four records (vnum - number, vname - 30 characters, stadt - 20 characters and prov - number) created.

Fieldtype

    Syntax:

    Fieldtype [...]

    Fieldtype is here the type of the table column to be created.

    NOT NULL

      Is needed when, no NULL-Values in the columns are allowed.

    UNIQUE

      Allows only unique Values in the database table column.

    CHECK( logical construct )

      Is used if an error check is needed before new values are saved into the field.

    DEFAULT = Value

      Defines a default value for the column. Empty fields would have the specifyed value and would not be NULL.

    PRIMARY KEY

      Specifyes the primary key for the database table.

    Example:

CREATE TABLE tverkauf (
 vnum  INTEGER NOT NULL UNIQUE PRIMARY KEY,
 vname CHAR(30) NOT NULL UNIQUE,
 stadt CHAR(20) DEFAULT='Berlin',
 prov  DECIMAL CHECK(comm &lt 1) );

Creates a new empty table tverkauf with records with four fields :
vnum - unique, not null number as primary key;
vname - 30 character not null and unique textfield;
stadt - 20 character textfield with default value 'Berlin';
prov - number, which is always smaller than 1.

Categories: 

DROP TABLE - delete a table from the database

Syntax

Description

    With DROP TABLE you can delete a table from a database. The table must not be explicitly empty. It depends on the database system.

    Example

DROP TABLE tkunden;

Here is the customer table tkunden deleted.

Categories: 

ALTER TABLE - modify tables

Syntax

Description

    With ALTER TABLE you can add, remove or modify table columns. depending on the database management system, the table should not be empty.

    Example:

ALTER TABLE tkunden ADD COLUMN new_number INTEGER NOT NULL;

Adds the new INTEGER column new_number to existing table tkunden and sets a NOT NULL contraint.

Example:

ALTER TABLE TABLE tkunden DROP COLUMN new_number;

Drops the column new_number from the table tkunden.

Categories: 

GRANT - grant table privileges to users

Syntax

    GRANT { SELECT | INSERT | UPDATE | DELETE | ALL }
    ON tablename
    TO
    { User | {GROUP|ROLE} groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ];

Description

    With GRANT you can give following rights or privileges to database users or roles (groups of users):
    SELECT - revtrieve or query data
    INSERT - insert new records into a table
    UPDATE - modify datafields in a table
    DELETE - delete records from a table
    ALL - means all rights on a table

    When PUBLIC is used instead of a list of concrete users or roles, then the specifyed priviledges will be given to all users of the database system (depending on the used RDMBS the rights are given only to the users woth connect permission to the current database).

    WITH GRANT OPTION allows users to delegate their table priviledges to other users.

    Example:

GRANT SELECT ON tkunden TO my_user;

The user my_user becames the priviledge to query, to retrieve data from the table tkunden.

Categories: 

REVOKE - remove table priviledges from users

Syntax

    REVOKE { SELECT | INSERT | UPDATE | DELETE | ALL }
    ON tablename
    FROM public | {user|role [,..]} ;

Description

    REVOKE removes the followong priviledges from the specifyed users or roles (user groups):
    SELECT - query or retrieve data
    INSERT - insert new records into a table
    UPDATE - modify existing records in a table
    DELETE - delete records from a table
    ALL - means all priviledges
    When PUBLIC is used, the specifyed priviledges will be removed from all users in the database (depending on the concrete RDMBS, the rights will be removed only from the users or the current database).

    Example:

REVOKE UPDATE ON tkunden TO my_user;

Here the user my_user looses the right to modify existing records in the table tkunden.

Categories: 

Data and Records

What are database records? How can i store, modify, delete, update, change records of data in relational database tables?

SELECT - retrieve data

-- english, deutsch, russian --

Syntax

Description:

    SELECT-commands get data from the tables of one or, in special cases, more than one database. SELECT returns a result-data-table, which is created from the data in the specifyed tables of the current database. You can get with the SELECT-command only some records from one database table, if you want, or sum them up, or you can create a new result table from different database tables or even databases on the same system.

DISTINCT

    DISTINCT tells the DBMS to select only unique records. Redundant data would not be selectted in this case (the same record would not be included more than once).

    Example:

SELECT DISTINCT vnum
 FROM torder;

Selects all unique values of the column vnum from the table torder.

FROM { Tablename [ [AS] Alias ] } [,...]

    FROM tells the DBMS from which tables the data must be retrieved.

    Example:

SELECT vnum
 FROM torder;

Selects all values of the whole column vnum from the table torder..

WHERE logical construct

    With WHERE you can decrease the selected amount of data or you can specify in which way multiple tables are joined.

    Example:

SELECT tkunden.knum, tverkauf.vnum, tverkauf.prov
 FROM tkunden, tverkauf
 WHERE tkunden.vnum=tverkauf.vnum;

Here is an resulting table with the column knum from the table tkunden and the columns vnum and prov from table tverkauf created, where the logical construct of the WHERE-clause specifyes the way in which the correct record from second table is selected.

GROUP BY { Fieldname | Integer } [,...]

    GROUP BY is always used with data aggregation functions to let them work only on specifyed groups of records. Instead of explicit using of an fieldname the number of the field in the column list can be used.

    Example:

SELECT vnum, MAX(preis)
 FROM torder
 GROUP BY vnum;

Selects only the maxumum values of the field preis from each record group with the same vnum.

HAVING logical construct

    HAVING is used only with GROUP when the data aggregation functions shall work only in groups of records, which fullfill the logical construct.

    Example:

SELECT vnum, MAX(preis)
 FROM torder
 GROUP BY vnum
 HAVING AVG(preis) > 10

Selects only the maximum values of the field preis from each record group with the same vnum, where the average of preis is greater than 10.

ORDER BY { Fieldname | Integer [ ASC|DESC ] } [,...]

    ORDER BY sorts the records of the resulting table. When ORDER BY is used in the GROUP BY statement, then the records in the record groups will be sorted too. Instead of explicit using of an fieldname the number of the field in the column list can be used. ASC sorts the records in ascending order, DESC in descending.

    Example:

UNION [ALL] SELECT-Command

    UNION is used to merge the results of two or more SELECT commands into one resulting table. The structure of all tables (the fields in the records) must be compatible in this case. When ALL is not used then only unique records will build the result. With UNION ALL all records would be included into the result.

    Example:

SELECT vnum, vname
 FROM tverkauf
 WHERE stadt='Berlin'
 UNION
SELECT knum, kname
 FROM tkunden
 WHERE stadt='Berlin'
ORDER BY 1;

Here are all salesman (from the table tverkauf) and cutomers (from the table tkunden) living in Berlin selected. Only unique records are included in the resulting table, which is sorted in ascending order by the first column.

INTERSECT [ALL] SELECT-Command

    ANSI DB2 Informix Oracle SQL Server Interbase
    no yes yes yes ? ?

    INTERSECT is used to merge the results of two or more SELECT commands into one resulting table. The structure of all tables (the fields in the records) must be compatible in this case. When ALL is not used then only unique records will build the result. With INTERSECT only records find in all results of the SELECT commands are included into the resulting table.

    Example:

SELECT vnum, vname
 FROM tverkauf
 WHERE stadt='Berlin'
INTERSECT
SELECT knum, kname
 FROM tkunden
 WHERE stadt='Berlin'
ORDER BY 1;

Here are all salesman selected, that are customers too. The result is sorted by the first column. Only unique records are selected.

EXCEPT [ALL] SELECT-Command

    whichtig: in einigen SQL-Dialekten wird anstatt EXCEPT - MINUS oder DIFFERENCE verwendet, die Syntax bleibt aber gleich.

    ANSI DB2 Informix Oracle SQL Server Interbase
    no yes ? ? ? ?

    EXCEPT is used to merge the results of two SELECT commands into one resulting table. The structure of all tables (the fields in the records) must be compatible in this case. When ALL is not used then only unique records will build the result. With EXCEPT are only the records included into the resulting table, which are in the first SELECT and not in the second SELECT.

    Example:

SELECT vnum, vname
 FROM tverkauf
 WHERE stadt='Berlin'
EXCEPT
SELECT knum, kname
 FROM tkunden
 WHERE stadt='Berlin'
ORDER BY 1;

Here are all salesman secelted, which are not customers too. The result is sorted by the first column. Only unique records are selected.

INTO TEMP Tablename

    ANSI DB2 Informix Oracle SQL Server Interbase
    no yes yes ? ? ?

    INTO TEMP is used to create temporary database tables, which are deleted after the SQL-session. Is often used to optimize SQL queries.

SELECT - step by step

The SQL command for querying data in a database is very powerfull and therefore can be quite complex. To adres sthat problem I provide here a small step by step tutorial how to build a SELECT command. May be it is easier for you to answer the following questions and to build so your custom SELECT command step by step:

  1. Which data do you need? In which data fields is this data stored?
  2. AT first you should know and, when you do not know, search, in which data fields of which database tables (or database views) the needed information is located/stored. In this example we want a relatively simple statistics about the customer count per salesman. The id and name of the salesman (vnum, vname) and the customer id (knum) should be enough for this purpose.

    SELECT vnum, vname, knum
    ;
  3. In which tables are the needed data fields stored?
  4. The name and id of the salesman (vnum, vname) are stored in the salesman table (tverkauf) and for the customer id (knum) the customer table (tkunden) seems to be most suitable.

    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a, tkunden b
    ;
  5. What kind of relationship do the needed tables have to each other, how can they be joined?
  6. We have following knowledge about the two tables needed in this example:
    Not every salesman has customers (may be a new employee), so the data must be joined without loosing any salesman, even he has no corresponding customers: we need all records from the table tverkauf and search matching records from the table tkunden --> LEFT JOIN
    In the customer table (tkunde) we find the salesman id (vnum) and, because this is the primary key of the salesman table (tverkauf), we can use this data field for joining both of the tables together --> a.vnum = b.vnum

    SQL92::
    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    ;
    Standard-SQL::
    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a, OUTER tkunden b
    WHERE a.vnum=b.vnum
    ;

    When you omit LEFT JOIN or the OUTER, then you get only salesman, which have at least one corresponding record in the customer table (tkunden), so we get only salesman with customers.
    When you omit the join criteria a.vnum=b.vnum, then you get a so called cartesian product - the combination of all salesman with all customers, in our case quite a bunch of data vaste.

  7. Do you need all of the data or do you have to filter it?
  8. Let us assume, we are interested only on salesman from Berlin:

    SQL92::
    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    ;
    Standard-SQL::
    SELECT a.vnum, a.vname, b.knum
    FROM tverkauf a, OUTER tkunden b
    WHERE a.vnum=b.vnum
    AND a.stadt='Berlin'
    ;
  9. Do you need the original record fields or is a statistics, an aggregation of the original data needed here?
  10. Because we want to know, how much custimers do each salesman have, we should count the customers per salesman --> COUNT.
    That means, we have to aggregate, to group data --> GROUP BY

    SQL92::
    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    ;
    Standard-SQL::
    SELECT a.vnum, a.vname, COUNT(*) AS anzahl
    FROM tverkauf a, OUTER tkunden b
    WHERE a.vnum=b.vnum
    AND a.stadt='Berlin'
    GROUP BY vnum, vname
    ;
  11. Is all of the resulting aggregated statistics data needed?
  12. Let as futher assume, we are not interested in good salesman with lots of customers. And let us assume, that a good salesman have 100 or more customers. Then we have to choose only salesman with less than 100 customers:

    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) 
    ;
  13. Should the query results be sorted?
  14. In our search for bad salesman we probably want to see salesman with less customers first. So we have to order the results by the customer count ascending:

    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) ORDER BY anzahl ASC
    ;
  15. Should the amount of the resulting data be limited to a specific number of records?
  16. Because we do not want to talk with every laisy employee, we probably want to limit the amount of the resulting data records. Let us set the limit to 10 records:

    SQL92::
    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) LIMIT 10
    ;
    Informix::
    SELECT FIRST 10 a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) 
    
  17. Should the query results be stored in a new or temporary table?
  18. When the resulting data have to be processed in some you sometimes want to have it in a handy (temporary) database table, so you can easily use ot in other, futher database query. So the salesmanager can probably decide by him self, how much employees he want to speak. In this case we need to remove the record limit from our select query and write the records into a new (temporary) table:

    Temporäre Tabelle
    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) INTO TEMP verkauf_score
    ;
    Regular, existing table (must be created before with CREATE TABLE)
    INSERT INTO verkauf_score
    SELECT a.vnum, a.vname, COUNT(DISTINCT b.knum) AS anzahl
    FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
    WHERE a.stadt='Berlin'
    GROUP BY vnum, vname
    HAVING COUNT(DISTINCT b.knum) 
    
Categories: 

INNER JOIN - normal table join

Syntax

    WHERE a.Field1 = b.Field2
    before SQL92 (implicit declaration of table joins)

    FROM Table1 [Alias1] INNER JOIN Table2 [Alias2] ON {Table1|Alias1}.TableKey = {Table2|Alias2}.TableKey
    SQL92 (explicit declaration of table joins)

Description

    The inner or equal join is the "normal" way for joining two tables: the records of two tables are joined, when the join expression build upon the fields from both tables evaluates as true.

    Example:

-- before SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
WHERE a.vnum = b.vnum
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a INNER JOIN tkunden b ON a.vnum=b.vnum
ORDER BY vnum;

Result:

vnum vname   knum kname
1    Mueller 1    Lehmann
1    Mueller 2    Schmidt
1    Mueller 3    Schumacher
2    Meyer   4    Schroeder
2    Meyer   5    Stoiber
3    Schulz  6    Lohmann
3    Schulz  7    Krupp
3    Schulz  8    Stradivari
3    Schulz  9    Schumacher
Categories: 

LEFT JOIN - left outer join

Syntax

    FROM Table1 [Alias1], OUTER Table2 [Alias2]
    WHERE a.Field1 = b.Field2

    Informix, before SQL92 (implicit declaration of table join)

    WHERE (a.Field1 = b.Field2(+))
    Oracle, before SQL92 (implicit declaration of table join)

    FROM Table1 [Alias1] LEFT JOIN Tabelle2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
    SQL92 (explicit join declaration)

Description

    The result of a left join (or left outer join) includes all data from the left/normal table, data from the right /outer table is included, when the specifyed join expression evaluates as true.

    Example:

-- Informix
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, OUTER tkunden b
WHERE a.vnum = b.vnum
ORDER BY vnum;

-- Oracle
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
WHERE (a.vnum = b.vnum(+))
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a LEFT JOIN tkunden b ON a.vnum=b.vnum
ORDER BY vnum;

Result:

vnum vname       knum kname
1    Mueller     1    Lehmann
1    Mueller     2    Schmidt
1    Mueller     3    Schumacher
2    Meyer       4    Schroeder
2    Meyer       5    Stoiber
3    Schulz      6    Lohmann
3    Schulz      7    Krupp
3    Schulz      8    Stradivari
3    Schulz      9    Schumacher
4    Burgmueller 

In addition to a normal or equal or inner join, there are the salesman (values from table tverkauf) with vnum=4 in the result, which has no matching records in the table tkunden.

Categories: 

RIGHT JOIN - right outer join

Syntax

    FROM OUTER Table1 [Alias1], Table2 [Alias2]
    WHERE a.Field1 = b.Field2

    Informix, before SQL92 (implicit join declaration)

    WHERE (a.Field1(+) = b.Field2)
    Oracle, before SQL92 (implicit join declaration)

    FROM Table1 [Alias1] RIGHT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
    SQL92 (explicit join declaration)

Description

    The result of a right join (or right outer join) includes all data from the right/normal table, data from the left/outer table is only included, when the specifyed join expression evaluates as true.

    Beispiel:

-- Informix
SELECT a.vnum, a.vname, b.knum, b.kname
FROM OUTER tverkauf a, tkunden b
WHERE a.stadt = b.stadt
ORDER BY vnum;

-- Oracle
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
WHERE (a.stadt(+) = b.stadt)
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a RIGHT JOIN tkunden b ON a.stadt = b.stadt
ORDER BY vnum;

Result:

vnum vname   knum kname
1    Mueller 1    Lehmann
1    Mueller 2    Schmidt
1    Mueller 4    Schroeder
3    Schulz  5    Stoiber
             3    Schumacher
             6    Lohmann
             7    Krupp
             8    Stradivari
             9    Schumacher
             10   Testcustomer

List of all customers with corresponding salespeople, but only when they live in the same city.

Categories: 

FULL JOIN - two sided outer join

Syntax

    FROM Table1 [Alias1] FULL JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2
    SQL92 (explicit declaration of a full or union join)

    SELECT Fieldlist FROM Table1 [Alias1] LEFT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2 UNION
    SELECT Fieldlist FROM Table1 [Alias1] RIGHT JOIN Table2 [Alias2] ON {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2

    SQL92 (if a direct declaration of a full or union join is not supported by the database system)

    SELECT Fieldlist FROM Table1 [Alias1], OUTER Table2 [Alias2]
    WHERE {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2 UNION
    SELECT Fieldlist FROM OUTER Table1 [Alias1], Table2 [Alias2]
    WHERE {Table1|Alias1}.Field1 = {Table2|Alias2}.Field2

    Informix before SQL92 (implicit join declaration)

    SELECT Fieldlist FROM Table1 [Alias1], Table2 [Alias2]
    WHERE ({Table1|Alias1}.Field1 = {Table2|Alias2}.Field2(+)) UNION
    SELECT Fieldlist FROM Table1 [Alias1], Table2 [Alias2]
    WHERE ({Table1|Alias1}.Field1(+) = {Table2|Alias2}.Field2)

    Oracle before SQL92 (implicit join declaration)

Description

    The full or the full outer join includes all records from both tables in one resulting row, where the specifyed join expression evaluates as true. Additionally all records from the left table, where is no match in the right table, and all records from the right table, where is no match in the left table, are included in the query results.

    Attention, if the database system does not directly support a full join (Syntax 1), all matching records in both tables are selected in the first select statement with the left join and then are selected a second time in the second select statement with the right join. At the and duplicate rows are eliminated with UNION (without ALL, this is importand).

    Example:

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a FULL JOIN tkunden b ON a.vnum = b.vnum
ORDER BY vnum;

Ergebnis:

vnum vname       knum kname
                 10   Testcustomer
1    Mueller     1    Lehmann
1    Mueller     2    Schmidt
1    Mueller     3    Schumacher
2    Meyer       4    Schroeder
2    Meyer       5    Stoiber
3    Schulz      6    Lohmann
3    Schulz      7    Krupp
3    Schulz      8    Stradivari
3    Schulz      9    Schumacher
4    Burgmueller 		

List of all salesman (tverkauf) and customers (tkunde), matching salesman and customers are in the same result record, salesman without customers and customers wothout salesman are uncluded without their counterparts.

Categories: 

CROSS JOIN - kartesian product

Syntax

    SELECT {Table1|Alias1}.Field1 [,...] , {Table2|Alias2}.Field2 [,...]
    FROM Table1 [Alias1], Table2 [Alias2]

    before SQL92 (no join declaration at all)

    FROM Table1 [Alias1] CROSS JOIN Table2 [Alias2]
    SQL92 (explicit declaration of a cross join)

Desciption

    The cross join or the so called kartesian product involves no record match at all, all posible combinations of all records from both tables are included in the query results.

    Example:

-- before SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a, tkunden b
ORDER BY vnum;

-- SQL 92
SELECT a.vnum, a.vname, b.knum, b.kname
FROM tverkauf a CROSS JOIN tkunden b
ORDER BY vnum;

Result:

vnum	vname	knum	kname
1	Mueller 	1	Lehmann
1	Mueller 	2	Schmidt
1	Mueller 	3	Schumacher
1	Mueller 	4	Schroeder
1	Mueller 	5	Stoiber
1	Mueller 	6	Lohmann
1	Mueller 	7	Krupp
1	Mueller 	8	Stradivari
1	Mueller 	9	Schumacher
1	Mueller 	10	Testcustomer
2	Meyer 	1	Lehmann
2	Meyer 	2	Schmidt
2	Meyer 	3	Schumacher
2	Meyer 	4	Schroeder
2	Meyer 	5	Stoiber
2	Meyer 	6	Lohmann
2	Meyer 	7	Krupp
2	Meyer 	8	Stradivari
2	Meyer 	9	Schumacher
2	Meyer 	10	Testcustomer
3	Schulz 	1	Lehmann
3	Schulz 	2	Schmidt
3	Schulz 	3	Schumacher
3	Schulz 	4	Schroeder
3	Schulz 	5	Stoiber
3	Schulz 	6	Lohmann
3	Schulz 	7	Krupp
3	Schulz 	8	Stradivari
3	Schulz 	9	Schumacher
3	Schulz 	10	Testcustomer
4	Burgmueller 	1	Lehmann
4	Burgmueller 	2	Schmidt
4	Burgmueller 	3	Schumacher
4	Burgmueller 	4	Schroeder
4	Burgmueller 	5	Stoiber
4	Burgmueller 	6	Lohmann
4	Burgmueller 	7	Krupp
4	Burgmueller 	8	Stradivari
4	Burgmueller 	9	Schumacher
4	Burgmueller 	10	Testcustomer
Categories: 

INSERT - store data in table

Syntax

Description:

    INSERT INTO inserts new records into the with Tablename specifyed table, which are consiting of fixed Values or are the result of a SELECT-Command.

    Example:

INSERT INTO tberlin
 SELECT *
 FROM tverkauf
 WHERE stadt='Berlin'

Here are all records from the table tverkauf inserted into the table tberlin, where stadt is equal to Berlin.

VALUES ( Value [,...] )

    With the VALUES keyword you can insert a new fixed value record into the database table.

    Example:

INSERT INTO tverkauf (vnum,vname,stadt)
 VALUES ( 1001, 'Müller', 'Berlin' );

Here is a new record, consisting of 1001,'Müller','Berlin',NULL , inserted into the table tverkauf.

DELETE FROM - delete records from a table

Syntax

Description

    With DELETE FROM you can delete records from a database table.

    Example:

DELETE FROM tverkauf
 WHERE stadt='Kleinstadt'

Here all records of table tverkauf are deleted, where stadt is equal to 'Kleinstadt'.

UPDATE - modify data records

Syntax

Description:

    UPDATE is used to update some Values in the fields of a table with other Values.

SET { Fieldname=Value } [,...]

    With SET you scpecify the fields in which the values are replaced with the here specifyed values.

    Example:

UPDATE tverkauf
 SET prov = prov + .01
 WHERE 2 &lt=
 ( SELECT COUNT(knum) FROM tkunden WHERE tkunden.vnum=tverkauf.vnum );

Here is the field prov increased by .01 in all records of the table tverkauf, which have no more than 2 corresponding records in the tkunden table.

CASE - command

Syntax

Description

    With CASE you can proceed data in relation to other data.

    Example:

SELECT knum, kname,
CASE
  WHEN stadt <> 'Berlin'
  THEN LEFT('Das ist ein Nichtberliner, der sehr schlecht ist...',40)
  ELSE stadt
END berlin_ja_nein
FROM tkunden
WHERE rating>1;

Here are selectted all customers, where rating is bigger than 1, and only between curtomers living in Berlin and the others is made a difference.

LOAD - load data from file

Syntax


    LOAD FROM Dateiname [ DELIMITER '*' ]
    INSERT INTO Tablename

Description:


    Is used to insert records from a flat file into a database table. The default delimiter
    in most RDBMS is '|'.

UNLOAD - unload data to a text file

Syntax

Description:

    Writes the results of an SELECT statement into an flat file. Each record is written into a new line and the fields are separated with the specifyed delimiter. The default delimiter in most RDBMS is '|'.

Operators

What are operators? How can i use them?

SQL specific Operators

Here you find SQL-specific operators, which are not or are not so used in other computer languages.

* - All Fields

Syntax

    *

Description:

    Is an alias for all fields of the selected table.

    Example:

SELECT *
 FROM torder

Selects all fields in all records of the table torder.

ALL - All Records

Syntax

Description:

    ALL is used to select all records of an SELECT statement.

    Example:

SELECT *
 FROM tkunden
 WHERE rating &gt ALL ( SELECT rating FROM tkunden WHERE stadt='Berlin' );

Select all customers where rating is biger than the rating of all customers living in Berlin.

ANY - Some of Records

Syntax

Description:

    ANY and SOME are the same. They are used to apply a logical contruct to all records on the right side of the operator.

    Example:

SELECT *
 FROM tkunden
 WHERE rating &gt ANY ( SELECT rating FROM tkunden WHERE stadt='Berlin' );

Select all customers with rating bigger than rating of at least one of living in Berlin.

BETWEEN - Between Two Values

Syntax

Description:

    WW BETWEEN W1 AND W2 tells the DBMS , that WW must be between W1 and W2.

    Example:

SELECT vnum,anz,preis
 FROM torder
 WHERE preis BETWEEN 100 AND 500

Here are the fields vnum and preis from the table torder selected, where the field preis have an value between 100 and 500.

EXISTS - Records Exist

Syntax

Description:

    EXISTS is used with subquerys (sub-SELECTs) and have the value 'TRUE', when the subselect returns data. When no data is returned then this operator have the value 'FALSE'.

    Example:

SELECT DISTINCT knum
 FROM tkunden outer
 WHERE EXISTS
 ( SELECT * FROM tkunden inner
   WHERE inner.vnum=outer.vnum AND inner.knum&lt&gtouter.knum );

Here are all customers selected, which have more than one corresponding salesman.

IN - One of a Set of Values

Syntax

Description:

    WW IN ( Value [,...] ) tells the DBMS , that WW must be one of the values in the specifyed valuelist.

    Example:

SELECT *
 FROM torder
 WHERE preis IN (100,200,300,400,500);

Here are all records from the table torder selected, where preis have one of the values in the specifyed list.

IS NULL - Is an Empty Field

Syntax

Description:

    Returns 'TRUE' if the field is empty and 'TRUE' if the field have a value.

    Example:

SELECT *
 FROM tkunden
 WHERE stadt IS NOT NULL;

Here are all customers selected, where the field stadt is not empty.

LIKE - Search String Matching

Syntax

Description:

    LIKE is used only with CHAR and VARCHAR field types. Folowing rules apply to the format string :
    _ (underline) means all characters are allowed at this place;
    % (percent) means all character combinations (more than one character too) are allowed at this place (wildcard)

    Example:

SELECT *
 FROM tkunden
 WHERE kname LIKE 'G%';

Select all customers whith an beginning 'G' in the name.

MATCHES - Search String Matching

Syntax (Informix)

Beschreibung:

    MATCHES is used only with CHAR and VARCHAR field types. Folowing rules apply to the format string :
    ? (query) means all characters are allowed at this place;
    * (star) means all character combinations (more than one character too) are allowed at this place (wildcard);
    [characterset] the square brackets with the specifyed characterset allow only only a specifyed subset of characters at this place.

    Example:

SELECT *
 FROM tkunden
 WHERE kname MATCHES '?[AaOoUu]*';

Select all customers whith an 'A', 'a', 'O', 'o', 'U' or 'u' as the second character in the field kname.

SOME - Some of Records

Syntax

Description:

    SOME and ANY mean the same. They are used to apply a logicat constuct to all records at the rigth side of the operator. It's true when the logical construct has the value true on at least one record at the right side of the operator.

    Example:

SELECT *
 FROM tkunden
 WHERE rating &gt SOME ( SELECT rating FROM tkunden WHERE stadt='Berlin' );

Select all customers, where rating is bigger than the rating of at least one customer in Berlin.

Logical Operators

Logical contsructs consists of other logical constructs, formulas and values, which are connected through logical operators. For example, comparisons with equal, unequal and so on.

OperatorDescriptionExampleDescripltion
AND Means that the values at both sides of the operator must be TRUE, otherwise this operator returns FALSE. The execution priority is (in SQL) bigger than OR, but smaller than NOT. preis > 10 AND preis < 100 Price is bigger than 10 and smaller then 100.
NOT Logical negation, makes from FALSE an TRUE value and vice versa. Biggest execution priority of all logical operators. NOT preis=0 Price is not zero.
OR Means that at least one of the values at both sides of the operator must be TRUE, otherwise this operator returns FALSE. This operator is executed after NOT and AND. preis > 10 AND preis < 100 OR preis > 1000 Price must be bigger than 10 and smaller than 100 or bigger than 1000.
= Is TRUE when the values at both sides of the operator are equal. The execution priority is bigger than these of NOT, AND and OR. preis = 10 Price is equal to 10.
> Is TRUE when the value at the left side of the operator is greater than the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis > 0 Price is bigger than 0.
>= Is TRUE when the value at the left side of the operator is greater or equal to the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis >= 300 Price is bigger than or equal to 300.
< Is TRUE when the value at the left side of the operator is smaller than the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis < 300 Price is smaller than 300.
<= Is TRUE when the value at the left side of the operator is smaller ot equal to the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis <= 300 Price is smaller than or equal to 300 is.
<> Is TRUE when the value at the left side of the operator is not equal to the value at the right side. The execution priority is bigger than these of NOT, AND and OR. preis <> 0 Price is not equal to 0.

Mathematical Operators

In formulas values and fields may be connected through mathematical operators.

OperatorMeaningExampleDescription
-Negativ sign-0.01Minus 0,01
+Positiv sign (optional)+.9Plus 0,9
*Multiplication2*Field3The content fo the field Field3 twice
/DivisionProzent3/100The content of the field Prozent3 divided through 100
+AdditionFeldA + (-0.9)Sum of FeldA and -0,9
-SubtraktionFeldA - 0.01FeldA minus 0,01

Operator Precedence

All operators are executed in a defined priority:

OperatorDecriptionPriority
+- sing 0
* / multiply, divide 1
+ - add, substact 2
= equal 3
<> not equal 3
> greater 3
< smaller 3
>= greater or equal 3
<= smaller or equal 3
[NOT] BETWEEN ...
AND ...
[not] between ... and 3
IS [NOT] NULL [not] NULL-value 3
[NOT] IN [not] in the list 3
NOT Negation 4
AND logical and 5
OR logical or 6

Functions

What are functions? How can i use them?

Data Aggregation Functions in SQL

Here you find all data aggregation functions, which are used with the GROUP BY statement within the SELECT command. Theese functions aggregate data or get statistics from groups of rows.

AVG - average

Syntax

Description

    AVG calculates the average of all values in the specifyed column.

    Example:

SELECT AVG(preis)
 FROM torder

Calculates the average of all values of preis.

COUNT - count records and values

Syntax

Description

    COUNT counts the number of records with no NULL-values in the specifyed field or the whole number of records in the table if '*' is used instead of specifying a column.

    Example:

SELECT COUNT(DISTINCT preis)
 FROM torder

Counts all unique values of the column preis of the table torder.

SUM - sum up the values

Syntax

Description

    SUM sums up all values in the specifyed column.

    Example:

SELECT SUM(preis)
 FROM torder

Sums up all values of the column preis.

MIN - minimum value

Syntax

Description

    MIN returns the minimum value of the column.

    Example:

SELECT MIN(preis)
 FROM torder

Returns the minimum value of preis.

MAX - maximum value

Syntax

Description

    MAX returns the maximum value of the selected column.

    Example:

SELECT MAX(preis)
 FROM torder

Returns the maximum value of the column preis.

RANGE - range of values (MAX - MIN)

Syntax

Description

    RANGE calculates the difference between the maximum and the minimum values of the column.

    Example:

SELECT PANGE(preis)
 FROM torder;

Calculates the difference between the biggest and the lowest preis.

STDDEV - standard deviation

Syntax

Description

    STDDEV calculates the standart deviation of the values in the specifyed column. Standart deviation is the square root of variance.

    Example:

SELECT STDDEV(preis)
 FROM torder

Calculate the standart variation of all values of preis.

VARIANCE - variance of values in a column

Syntax

Description

    VARIANCE calculates the variance of the values in the column.

    Example:

SELECT VARIANCE(preis)
 FROM torder

Calculate the variance of all values in the column preis.

Mathematical Functions

Most important mathematical and arithmetical functions, which are implemented in most SQL databases.

SyntaxDescription
ABS(Formula) ABS returns the value istself, when positiv, and (-1)*value, when negativ.
CEIL(Formula) CEIL rounds a value (number with decimals) to the next bigger integer.
HEX(Formula) HEX returns the hex-value of the argument.
FLOOR(Formula) FLOOR rounds a value (number with decimals) to the next smaller integer.
MOD(Formula1, Formula2) MOD returns the rest of an division of two arguments.
ROUND( Formula [, Integer ] ) Rounds the argument to the next floating point number with specifyed number of decimals. If the oprional number of decimals in not given, then the result would be an integer.
SQRT(Formula) Returns the square root of the argument.
TAN(Formula) Returns the tangens of the argument.
TRUNC(Formula) TRUNC rounds the argument to the next smaller integer. In some databases a charakter value is converted to a number, before it is rounded.

Text and Strings

Here you find most important text and string functions, which are supported by most of the sql database systems.

SyntaxDescription
LENGTH(Formula) Returns the length of the string in characters
LEFT( Formula1 , Formula2 ) Returns a number of characters, specifyed by Fomula2, from the left side of the argument Formula1.
RIGHT(Formula1, Formula2) Returns a number of characters, specifyed by Fomula2, from the right side of the argument Formula1.
SUBSTR(Formula1, Formula2, Formula3) Returns a substring from the argument Formula1, which starts at position Formula2 and is Formula3 characters long.
TRIM(Formula) TRIM deletes all spaces form the left and right sides of the argument.
VALUE(Formula) DB2. Converts the string into a numeric value.

Date and Time Functions

Here are the most important date and time funtcions, which are supported by most of sql databases.

SyntaxDescription
CURRENT Returns a DATETIME with current date and time.
DATE(Formula) Convert the string argument into date. In some RDBMS in numeric argument is alowed too.
DAY(Formula) Returns the day from a date-value.
EXTEND(Datetime, [ raram1 TO param2 ] ) Sets the precision of date and datetime values. The first argument is the date to be converted, the second argument defines the precision in form of beginning value and the end value desired.
Example:
SELECT EXTEND( datevalue, MONTH TO YEAR) as time, sum(anz) as orders 
FROM torder 
GROUP BY 1 
Return some order statistics.
MDY(Formula1, Formula2, Formula3) Builds a date from ist components : month, day of the month, year.
MONTH(Datum) Extracts and returns the month from a date.
TODAY Returns current date.
WEEKDAY(datevalue) Calculates and returns the weekday of a date.
YEAR(datevalue) Extracts and returns the year from a date.

Other SQL Functions

Here you find some of the most important helper functions, which are supported by most sql databases.

SyntaxDescription
NVL(Field [, Value ] ) Replaces all NULL-values with the value given in the argument.
DBSERVERNAME Returns the name of the database server.
USER Returns the login name of the currrent user.
SITENAME Returns the name of the current database server.

Views

What are views? How can i use views? How can i create and delete views?
Categories: 

CREATE VIEW

Syntax

Description:

    CREATE VIEW creates an speciel view on one or more sql-tables in the database in form of a new virtual (only in the memory created) table. In CREATE VIEW you can use accordingly to the database software only a part of the SELECT-Commands clauses.

    Example:

CREATE VIEW myorders (
  my_onum,
  my_anz ) AS
SELECT
 onum,
 anz
FROM torder
WHERE preis > 0;

Creates a view on the table 'torders', which contains inly the fields 'anum' und 'anz' from records with 'preis' > 0.

DROP VIEW

Syntax

Description

    DROP VIEW deletes an in the database stored view.

    Example:

DROP VIEW myorders;

Here is the view 'myorders' deleted from the database.

Stored Procedures

What are stored procedures? How can i use stored procedures? How can i create and delete stored procedures?

CREATE PROCEDURE

Syntax

Description

    With CREATE PROCEDURE you can create and store in the database an procedure (programm/function), wich you want to you use later in SQL-statements or other programms and procedures.

    Example :

CREATE PROCEDURE table_drop ( )
DROP TABLE proc_test;
END PROCEDURE;

Creates a new stored procedure 'table_drop' , which deletes the table 'proc_test' from the current database, when called.

DBA

    DBA tells the database to create a dba privileged procedure.

    Example :

CREATE DBA PROCEDURE table_create ( )
  CREATE TABLE proc_test (
    fid	 smallint,
    ag	 smallint,
    vsnr integer
  );
END PROCEDURE;

Creates a new dba privileged stored procedure 'table_create' , which creates a new table 'proc_test' with three coplumns 'fid', 'ag' und 'vsnr' in the aktive database when called.

REFERENCES { BYTE | TEXT }

    REFERENCES ...

    Example :

under construction...
   

Creates a new stored procedure '...' in the database.

DEFAULT { Wert | NULL }

    DEFAULT specifyes a default value, that is used when the procedure is called without a parameter.

    Example :

CREATE PROCEDURE square ( par INTEGER DEFAULT 0 )
RETURNING INTEGER;
  DEFINE rez INTEGER;
  LET rez = par * par;
  RETURN rez;
END PROCEDURE;

Creates a new in the database stored procedure 'square'. Here is the parameter 'par' optional optional and zero by default. When the procedure is called without paramenter, then it returns 0*0 , otherwise it returns par*par .

RETURNING { Feldtyp | REFERENCES { BYTE | TEXT } }

    RETURNING tells the DBMS , how many values and of which type an procedure will return.

    Example :

CREATE PROCEDURE table_insert ( )
RETURNING INT;
  DEFINE i INT;
  INSERT INTO aam_proc_test ( fid, ag, vsnr )
  VALUES ( 1, 2, 3 );
  INSERT INTO aam_proc_test ( fid, ag, vsnr )
  VALUES ( 0, 9, 9 );
  LET i = SELECT COUNT(*) FROM proc_test;
  RETURN i;
END PROCEDURE;

Creates a new stored procedure 'table_insert', which inserts two new rows into the table 'proc_test' when called.

Statement

    An SPL-statement or command, such as DEFINE, RETURN, IF, FOR, FOREACH, WHILE, and so on.

    Example :

CREATE PROCEDURE table_run ( )
DEFINE i INT;
  SET ISOLATION TO dirty read;
  SET LOCK MODE to wait 60;
  SET PDQPRIORITY 5;
  CALL table_drop();
  CALL table_create();
  LET i = table_insert();
END PROCEDURE;

Creates a new stored procedure 'table_run', which calls some other stored procedures and proceeds a return value.

Integrated example

    Example :

CREATE PROCEDURE table_show ( )
RETURNING INT, INT, INT;
  DEFINE my_fid, my_ag, my_vsnr INTEGER;
  CALL table_run();
  FOREACH
    SELECT square(fid+1) fid, ag, vsnr
    INTO my_fid, my_ag, my_vsnr
    FROM proc_test
    RETURN my_fid, my_ag, my_vsnr
    WITH RESUME;
  END FOREACH;

END PROCEDURE;

Creates a new stored procedure 'table_show' , which returns all records of the table 'proc_test' created before in the subprocedure 'table_run'.

DROP PROCEDURE

Syntax

Description:

    DROP PROCEDURE deletes a stored procedure form the database.

    Example :

DROP PROCEDURE table_show;

Deletes the stored procedure 'table_show'.

EXECUTE PROCEDURE

Syntax

Description:

    EXECUTE PROCEDURE starts the execution of a stored procedure.

    Example:

EXECUTE PROCEDURE table_show;

Starts the stored procedure 'table_show'.

Transactions

What are transactions, how to begin transactions, hot to end and cancel them?

BEGIN WORK - begin transaction

Syntax

Description:

    Marks the begin of an transaction, a set of SQL commands, that can be undo in error case.

COMMIT WORK - end the transaction

Syntax

Description:

    Marks the end of an transaction, a set of SQL commands, that can be undo in error case.

ROLLBACK WORK - cancel the transaction

Syntax

Description:

    Undo all SQL commands of an transaction.

Other SQL commands

Here are some other SQL commands sometimes needed for optimal query execution.

SET ISOLATION TO - set isolation level

Syntax:

    SET ISOLATION TO { DIRTY READ | COMMITTED READ | CURSOR STABILITY | REPEATABLE READ };

Description:

    SET ISOLATION TO DIRTY READ sets the multiuser isolation so, that an SQL-command does not respect locked records, even when they are locked for UPDATE .

SET PDQPRIORITY - set ressource priority

Syntax

Description:

    Sets the priority of granting ressources on the server to nn. The default Value ist 0 (without priority), when no other special settings are made.

    Example:

SET PDQPRIORITY 5;

Sets the priority to 5 (small).

SET LOCK MODE TO WAIT - set timeouts

Syntax:

Description:

    Set the timeout (the time, in which the DBMS waites until records unlocked) to nn seconds.

    Example:

SET LOCK MODE TO WAIT 60;

Sets the timeout to 60 seconds.

Syntax Definitions

The description of the syntax diagramm elements used in this tutorial.

Syntax elements used in this book

|

    The vertical line | means, that you have to use only one of the language elements on both sides of the line in the program text. This vertical line charakter is not to be used in the program text.

[ ]

    Anything in the quare brackets is optional and must not be excplicitly used. These square brackets are not to be used in the program text too.

{ }

    Anything between this brackets is considered as one language element. These brackets are not to be inserted into the program text too.

( )

    SQL language element used in field size definitions and in logical contsructs or formulas. These round brackets you have to insert into the program text at this place.

TextBigSmall

    Text elements, having big and small charakters together, are used to mark database, table, fields or aliases, or lists of them.

TEXTBIG

    Text elements, having only big charakters, are used to mark SQL keywords.

textsmall

    Text elements, having only small charakters, are used only in examples to mark table- of field- names.

[...]

    Any language elements, that are standing before [...] can be repeated and used more than once.

[,...]

    Any language elements, that are standing before [,...] can be repeated and used more than once in a list. The elements of the list have to be separated with "kommas".

SQL language elements in syntax diagramms

( )

    Round brackets. Are used in field size definitions in logical constucts or formulas.

Alias

    Alias is an alias to the original tablename, which is used instead of the tablename.

    Example:

mytable1

Fieldname

    Fieldname is the name of an databasetable-field or -column in the used database table.

    Example:

 preis 

Feldlist

    Fieldlist is a list of fieldnames of the used database table, separated with commas.

    Example:

 onum, preis, anz 

Value

    Value is here a value that can be stored in the corresponding SQL data type, such as an integer, decimal number or text in "Hochkomma".

    Example:

 'Berlin' 

Valuelist

    Valuelist is a list of values separated with commas (see value).

    Example:

 1001, 'Müller', 'Berlin', .12 

Tablename

    Tablename is a name of an table existing or to be created in the database.

    Example:

 torder 

Logical constuct

 preis &gt 100 AND preis &lt 1000 

Formula

 ( (price * prov) - .10 )

Integer

    Integer ist a number without decimals.

    Example:

 17 

Tables used in examples

Table tverkauf (salesman) :


    CREATE TABLE tverkauf (
    vnum INTEGER NOT NULL UNIQUE PRIMARY KEY,
    vname CHAR(40),
    stadt CHAR(40),
    prov DECIMAL );

Table tkunden (customers) :


    CREATE TABLE tkunden (
    knum INTEGER NOT NULL UNIQUE PRIMARY KEY,
    kname CHAR(40),
    stadt CHAR(40),
    rating INTEGER,
    vnum INTEGER );

Table torder (customer orders) :


    CREATE TABLE torder (
    onum INTEGER NOT NULL UNIQUE PRIMARY KEY,
    preis DECIMAL(2,30),
    anz REAL,
    odatum DATE,
    knum INTEGER
    vnum INTEGER );

Table tposition (order items) :


    CREATE TABLE tposition (
    pnum INTEGER NOT NULL UNIQUE PRIMARY KEY,
    onum INTEGER,
    anum INTEGER,
    menge INTEGER );

Table tartikel (products) :


    CREATE TABLE tartikel (
    anum INTEGER NOT NULL UNIQUE PRIMARY KEY,
    name CHAR(40),
    preis DECIMAL(2,30),
    hnum INTEGER );

SQL standads overview

SQL, the Structured Query Language for relational datanbases, was originally published in 1986 and was standardized in several steps. The following explanations are sorted by standards and its parts/improvements.
  • 1989 - SQL-89, standard SQL, SQL-1
  • 1992 - SQL-92, SQL-2 (ANSI specification X3.135-1992)
      * ANSI X3.135-1992 Database Language SQL (the 1992 standard)
      * ANSI/ISO/IEC 9075-1992 Technical Corrigendum 1 (errata)
      * ANSI/ISO/IEC 9075-3-1995: Part 3: Call-Level Interface (CLI)
    • DDL (Data Description Language): BLOB's, VARCHAR, DATE, TIME, TIMESTAMP, BOOLEAN
    • DML (Data Manipulation Language): OUTER/INNER-Joins, operations on row sets (UNION, CROSS)
    • Transactions: set transaction
    • Cursors:
    • Bindings: Dynamic SQL
    • Domain checks and constraints: DEFAULT, CHECK (beginning of domain concept), basic concepts for refferential integrity (references for primary keys and key candidates)
    • Connections: connect, set connection
    • Catalogs: system tables or system catalog
    • Errors & diagnostics: SQLSTATE, GET DIAGNOSTICS
    • Misc.: cursors, ALTER (altering and dropping), CAST (data type converions)

  • 1999 - SQL-99, SQL-3 (ISO/IEC 9075:1999(E) Information technology - Database languages - SQL)
      * INCITS/ISO/IEC 9075-1 01-Jan-1999 SQL-Part 1: Framework (SQL/Framework)
      * INCITS/ISO/IEC 9075-2 01-Jan-1999 SQL-Part 2: Foundation (SQL/Foundation)
      * INCITS/ISO/IEC 9075-3 01-Oct-1999 SQL-Part 3: Call Level Interface (SQL/CLI)
      * INCITS/ISO/IEC 9075-4 01-Jan-1999 SQL-Part 4: Persistent Stored Modules (SQL/PSM)
      * INCITS/ISO/IEC 9075-5 01-Jan-1999 SQL-Part 5: Host Language Bindings (SQL/Bindings)
    • Framework:
    • DDL (Data Definition Language): Time-Series-Data, User defined types (UDT), roles
    • DML (Data Manipulation Language): recursive queries
      Intermediate Level - CASCADE DELETE
      Full Level - CASCADE UPDATE
    • Transactions:
    • Cursors:
    • Bindings: Precompilers, embedded and dynamic SQL, OLB (Object Language Bindings) with SQLJ for Java
    • Domain checks and constraints: triggers
      Full Level - Subquery in CHECK, Assertions, DEFERRED
    • CLI (Callable Level Interface): ODBC, JDBC, OLE DB
    • PSM (Persistent Storage Modules): stored Procedures
    • Mediums: Management of external data

    • 2003 - SQL-2003
      • DDL (Data Description Language): MULTISET, generated attibutes (derived from other attributes), identity attributes (automatic generation of primary keys)
      • DML (Data Manipulation Language): table functions (table generation in functions), TABLESAMPLE, MERGE command
      • SQL/Schemata: information and definition schemas
      • SQL/XML: XML-related Specifications
      • SQL/MED (Mediums): access to external data

    • Categories: 

      Links to software manyfacturers documentation pages

      Commercial database management systems:

      Open Source:

      Categories: 

      Copyright and Warranty

      Copyright © 2004 by André Müller. This Object is open source. You can redistribute it and/or modify it under the terms of the Universal General Public License (UGPL).

      Here some important parts of the UGPL:

      Distribution Concerns

      You may not copy, modify, sublicense, or distribute the Object except as expressly provided under this License. Any attempt otherwise to copy, modify, sublicense or distribute the object is void, and will automatically terminate your rights under this License. However, parties who have received copies, or rights, from you under this License will not have their licenses terminated so long as such parties remain in full compliance.

      USAGE

      BECAUSE THE OBJECT IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY FOR THE OBJECT, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE OBJECT IS WITH YOU. SHOULD THE OBJECT PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, REPAIR OR CORRECTION.

      DAMAGES AND LOSSES

      IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR REDISTRIBUTE THE OBJECT AS PERMITTED ABOVE, BE LIABLE TO YOU FOR DAMAGES, INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE OBJECT (INCLUDING BUT NOT LIMITED TO LOSSES OF MONEY AND/OR INVESTMENTS OR INPUT BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY YOU OR THIRD PARTIES OR A FAILURE OF THE OBJECT TO OPERATE WITH ANY OTHER OBJECT), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

      The older Version of SQL-Help can be downloaded here: http://aam.ugpl.de/node/1141