Chapter 4 : Formatting and alias

This chapter explains how data has to be formatted as well as how to make use of alias in SQL queries.

Figure 4.1 : Database schema

Each table attribute has a data type. Let’s take the table Employee as an example. Last_name and First_name are of type String, Start and End are of type Date, ID and Kids_number are of type Integer and finally Salary is of type Float.

In SQL queries the formatting of data depends on its data type.

1. Date

Attributes which are of type Date have to be formatted in this way : '2014-01-22'. Let’s assume as an example we’d like to get data about all employees who have worked all along the period from January 1st, 2008 to December 31th, 2013 inclusive.

SELECT *
FROM Employee
WHERE Start >= '2008-01-01' AND End <= '2013-12-31'

We get the following table.

2. Number (integer and float)

Attributes which are of type Number have to be formatted in this way : 3. Let’s assume as an example we’d like to get data about all employees whose number of kids is not equal to 3.

SELECT *
FROM Employee
WHERE Kids_number <> 3

We get the following table.

3. String

Attributes which are of type String have to be formatted in the following manner : 'Marketing'. Double quotes can also be used, especially when a word contains one single quote in it such as follows : "O'Connor".

Search in a string can be done with the LIKE clause. For instance, assume we’d like to get data about all employees whose first name contains the substring Anne.

... WHERE First_name LIKE '%Anne%'

This code is going to hit employees whose first name is for example Marie-Anne, Annette or just Anne.

4. NULL value

Testing if an attribute has a NULL value can be done as follows : IS NULL. Let’s assume we’d like to get data about all employees who are no longer part of the company.

SELECT *
FROM Employee
WHERE End IS NOT NULL

We get the following table.

5. Alias

As its name suggests, an alias is a label assigned to an attribute or a table. An alias is set by using the keyword AS.

One example where this is useful is in the header renaming in the result set. Suppose we’d like to rename the column title of both First_name and Last_name in German in the result set.

SELECT First_name AS Vorname, Last_name AS Name
FROM Employee
WHERE Last_name = 'Sasaki' AND First_name = 'Ryoko'

We get the following table.

Another example of use is for having a more convenient name of tables to deal with. When the needed data comes from many different tables (see join), table’s name has to be prefixed before attributes (e.g. Employee.First_name). The use of alias allows to have a shorter table name. For example,

SELECT emp.First_name, emp.Last_name, dep.Name
FROM Employee AS emp, Department AS dep

is more convenient than

SELECT Employee.First_name, Employee.Last_name, Department.Name
FROM Employee, Department