Basic SQL Commands

Linklist

1. CREATE TABLE - create a table in the database
2. DELETE FROM - delete records from a database table
3. DROP TABLE - delete a table from a database
4. INSERT - insert new records into a database table
5. SELECT - select data from a database
6. CASE - keyword
7. UPDATE - update the values in the records of a database table

CREATE TABLE - create a database table

Syntax

CREATE TABLE Tablename
( { Fieldname Datatype [ (Size) ] Fieldtype [...] } [,...] ) ;

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:

CHARAKTER - textfields
CHAR - same as CHARAKTER


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:

[decimals,] whole size

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 < 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.

DELETE FROM - delete records from a database table

Syntax

DELETE FROM Tablename [ WHERE logical construct ];

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'.

DROP TABLE - delete a table from a database

Syntax

DROP TABLE Tablename;

Description

With DROP TABLE you can delete an empty table from a database.

Example
DROP TABLE tkunden

Here is the empty customer table tkunden deleted.

INSERT - insert new records into a database table

Syntax

INSERT INTO Tablename [ ( Fieldname [,...] ) ]
{ VALUES ( Valueliste [,...] ) } | SELECT-Command ;

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.

SELECT - select or get data from a database

Syntax

SELECT * | { [ DISTINCT | ALL ] { fieldlname | CASE-Anweisung } [,...] }
FROM { Tablename [ Alias ] } [,...]
[ WHERE logical construct ]
[ GROUP BY { Fieldname | Integer } [,...] ]
[ HAVING logical construct ]
[ { UNION | INTERSECT | EXCEPT [ALL] SELECT-Command } ] [...]
[ ORDER BY { Fieldname | Integer } [,...] ]
[ INTO TEMP Tablename ] ;

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 [ 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 } [,...]

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.

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.

CASE - keyword

Informix

Syntax

CASE
{ WHEN logical construct
THEN { Fieldname | Formel | CASE-Anweisung } } [...]
ELSE { Fieldname | Formel | CASE-Anweisung }
END

Description

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

Example:
SELECT knum, kname,
CASE stadt <> 'Berlin'
THEN LEFT('Das ist ein Nichtberliner, der sehr schlecht ist...',40)
ELSE stadt
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.

UPDATE - update values in the records of a database table

Syntax

UPDATE Tablename
SET { Fieldname=Value } [,...]
[ { WHERE logical construct } | { WHERE CURRENT OF CursorName } ] ;

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 <=
( 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.