SQL keywords for simple queries

Data is retrieved using a query statement. Queries (et al) use a basic building block known as a query specification that contain the following clauses:

• SELECT <select list>
• FROM <table list>
• WHERE <search condition>
• GROUP BY <grouping column list>
• HAVING <search condition>

Using these clauses enable simple queries. Each query must contain at least a SELECT and a FROM clause.

SELECT *                 //* (asterisk) means all
FROM city

SELECT name, country, population                 //returns the same result as the asterisk
FROM city

Lists all data about cities in the city table. All valid SQL queries result in a table. Even a result returning no data is seen as an empty table with no rows or columns.

Selecting columns from a specified table is done by:

SELECT  country, name                
FROM city

However the columns in the resultant table will be in the order specified in the query and not the order from the original table. If a table column has duplicate values using the DISTINCT keyword will pare away the duplicated data from the final table:

SELECT DISTINCT country                 
FROM city

Data types

Data values in a column must be of the same data type. The main data types are:

• character string
• numeric
• bit string
• date-time

SQL data types

Character string data types
char(len)	Fixed length character string
varchar	Variable-length character string
Numeric data types
integer	Integer Numbers
smallint	Small integer Numbers
decimal (p, scale)	Decimal numbers of precision at least p with scale digits after decimal point
numeric (p, scale)	Decimal numbers of precision exactly p with scale digits after decimal point
float (p)	Floating point numbers of precision p
Bit string data types
bit (len)	Fixed length bit string of length len
bit varying (len)	Variable-length bit string of length len
Date-time data types
date	Calendar date
time (p)	Clock time of precision p
timestamp (p)	Date and time of precision p
interval	Time interval

Value expressions

SQL can manipulate the data before displaying the data. This is done via functions involving column names and the numeric operators (+,/,*,-) and string concatenation (||). This is achieved through a value expression included in the SELECT clause, the result of which is a final table of results acquired from the operations/functions applied to the column(s).

Numeric operators can only be applied to columns containg numeric data types. String operations require the data to be of type character string.

Comments, suggestions, ideas to
Stuart Banner