Structured Query Language Case

Introduction

SQL (Structured Query Language) is a programming language that is widely utilized in accessing and managing relational database systems. It does creation, updating, deletion, insertion, sorting and plenty of other operations to a database, as well as to the tables and views inside the database.

The execution of SQL commands is based on relational database management system (RDBMS), a database management system. Many different versions of SQL are being run on different RDBMS in today's world. Some of the frequently heard RDBMS are MySQL, Microsoft SQL Server, and Oracle Database, etc.

Developed by the PostgreSQL Global Development Group, PostgreSQL is a "powerful, open source object-relational database system" (About Postgres, the PostgreSQL Global Development Group, 1996-2012). As PostgreSQL derives the majority of its syntax from SQL:2008, it largely complies with the ANSI (American National Standards Institute) standards. PgAdmin, an open source GUI (graphical user interface), is the administration tool for PostgreSQL. The version of the PostgreSQL mentioned in this report is 9.0.

The report is based on the premise that the readers possess the fundamental knowledge in SQL, which includes the SQL statements (SELECT * FROM *, INSERT INTO, UPDATE, DELETE), clauses (WHERE, TOP), operators (AND, OR, LIKE), and keywords (JOIN, ORDER BY). Though not required, readers are recommended to have a junior level of relative experience in programming with SQL.

Basic Control Flow in PostgreSQL

The if-then statement should be a familiar statement for computer programmers of all levels. Although the syntax may vary in different programming languages, it is undoubtedly the most basic control flow statement of all.

In PostgreSQL, one of the ways to apply the most basic control flow is:

CASE WHEN (conditional statement) THEN (value returned)

[WHEN (conditional statement) THEN (value returned)]

ELSE (value returned)

END

The conditional statement denotes a condition that returns boolean value. The value returned refers to the resulting value when the conditional statement evaluates to true.

The following example decodes the account status of personal bank accounts, assuming that '0' means 'Active', '1' means 'Inactive', and '2' means 'Temporarily Frozen'.

SELECT Account_Number,

CASE account_status

WHEN 0 THEN 'Active'

WHEN 1 THEN 'Inactive'

WHEN 2 THEN 'Temporarily Frozen'

END

FROM account_table

The output of the above query will be the readable string account status, instead of a mere digit, that corresponds to each bank account number.

Also, a conditional expression can be put inside another conditional expression:

CASE WHEN (conditional statement) THEN (

CASE WHEN (conditional statement) THEN (value returned)

[WHEN (conditional statement) THEN (value returned)]

ELSE (value returned)

END)

[WHEN (conditional statement) THEN (value returned)]

ELSE (value returned)

END

The lines highlighted in shaded color is a complete CASE WHEN statement, which will be executed only if its conditional statement returns true. In that case, the value returned for the highlighted lines will be the ultimate result of the entire statements.

Concatenation in PostgreSQL

Data retrieved by SQL is always presented in a table format, as presented below:

Query:

SELECT Last_Name, First_Name, Income

FROM Employees

Table:

Last_Name First_Name Income

Johnson Peter $100,000

Williams Nicole $120,000

Crisp Adam $98,000

At times, users may find it necessary to merge two fields of data into one. While a more literal function CONCAT() is being used to concatenate multiple fields together in some other versions of SQL languages, PostgreSQL uses the concatenation operator (||) to accomplish the same goal. For example, if a user would like to have the Last_Name and First_Name fields combined into a new field called Full_Name, as shown below:

Full_Name Income

Johnson, Peter $100,000

Williams, Nicole $120,000

Crisp, Adam $98,000

The query should be:

SELECT (Last_Name || ' , ' || First_Name) AS Full_Name, Income

FROM Employees

where the new field, Full_Name, is denoted as (Last_Name || ' , ' || First_Name).

It is worth noticing that the concatenation operator works for both string and non-string data types. With that being said, integers could be formatted in a similar manner. A good example would be a table column that demonstrates the market value of a company v.s. the total market value of the entire industry. The table column may be constructed as:

(Company_Market_Value || ' / ' || Industry_Market_Value).

Truncate a Time to a Specific Precision

On some occasions, developers may be required to group the data by day, month or year. In such cases, they presumably need to remove a portion of time from a timestamp field. For example, if the timestamp field returns '2012-08-03

...