Typically, SQL tutorials start with a small sample table and some simple queries, but since we are looking at SQL as a general purpose programming language, we stick to our "Hello World" program.
sample=> select 'Hello World'; ?column? ------------- Hello World (1 row)
Nonetheless, we end up with a select statement. Whenever we want to get some response from SQL, we have to define a query using the select keyword. In the simplest case, the select keyword is followed only by an expression. The expression is evaluated and the resulting value is the result of the select statement.
The result of a query is always a table (as the central concept of the relational model). Hence, we get our single message string in form of a table with the single column named ?column? and a single row containing the actual data. We can help the server to choose a better name for the column using an as clause.
sample=> select 'Hello World' as message; message ------------- Hello World (1 row)
If we can an individual string, what about arithmetical expressions? We can indeed abuse our PostgreSQL client as a calculator.
sample=> select 3+4*5;
?column?
----------
23
(1 row)
sample=> select 1.5e-2 * 5;
?column?
----------
0.075
(1 row)
sample=> select 2 ^ 10;
?column?
----------
1024
(1 row)
We can also call functions as part of the select expression. The following examples use the built-in square root function sqrt, the substring function substr, and the function now returning the current date and time.
sample=> select sqrt(2.0);
sqrt
-----------------
1.4142135623731
(1 row)
sample=> select substr('Hello World', 2, 6);
substr
--------
ello W
(1 row)
sample=> select now();
now
-------------------------------
2003-12-22 12:01:20.401652+01
(1 row)There are hundreds of built-in functions for mathematics, strings, dates, and so forth. To see a complete list, use the \df (describe functions) command. To find out more about a particular function, call \df followed by the name of the function.
sample=> \df sqrt
List of functions
Result data type | Name | Argument data types
------------------+------+---------------------
double precision | sqrt | double precision
numeric | sqrt | numeric
(2 rows)It is about time to introduce our own tables and data so that we can approach SQL's main features. SQL consists of (at least) two languages: a language to define relational data models (DDL - Data Definition Language) and a language to query and manipulate the actual data contained in these tables (DML - Data Manipulation Language).
The relational model is simple and powerful. All data is organized in tables. A table has a fixed number of columns. The data is contained in the rows (or tuples) of the table. A row is uniquely identified by its values (in the table's columns). In more mathematical terms, a table is a subset of the cross product of the column domains, where a column's domain is the set of allowed values in the column. All operations selecting and combining tables can be interpreted as operations on these sets (the "relations").
As our first relation, let's define a table containing first name, last name, and birthday of our friends.
sample=> create table friend ( sample(> firstname char(20), sample(> lastname char(20), sample(> birthday date); CREATE
A table is defined with the create table command followed by the name of the table and the list of column definitions as a comma separated list in parentheses. Each column definition consists of the column's name, its type, and optionally additional flags controlling the behavior of the column. In our example we use two types: char(20) for strings of up to 20 characters and date for dates (just the date, no time).
We can now insert tuples into this table using SQL's insert command.
sample=> insert into friend values ('Homer', 'Simpson', '15/05/1950');
INSERT 16576 1
sample=> insert into friend values ('Bart', 'Simpson', '20/07/1990');
INSERT 16577 1The first number in PostgreSQL's response if the object id supplied by PostgreSQL. This id is a PostgreSQL specific features we can forget for now. The second number is the number of rows inserted into the table.
Now that we have some data available, we can