Querying using Database OFFICE

Organisations keep their data in a database made up of several related tables — for example, an OFFICE database might hold an EMPLOYEE table, a DEPARTMENT table, and others besides. Every employee belongs to a department, and that department's number (DeptId) is stored as a foreign key inside EMPLOYEE, linking the two tables together. …

The most basic use of SELECT is to retrieve only the columns you need, instead of every column in the table.

To display just the employee numbers of all employees:

mysql> SELECT EmpNo FROM EMPLOYEE;

This returns all ten EmpNo values, one per row.

To display more than one column, list the column names separated by commas. The following query selects the employee number and employee name of all the employees:

mysql> SELECT EmpNo, Ename FROM EMPLOYEE;
``` …

In case we want to rename any column while displaying the output, it can be done by using the alias AS. The following query selects Employee name as Name in the output for all the employees:

mysql> SELECT EName as Name FROM EMPLOYEE;

Example 9.3 Select names of all employees along with their annual income (calculated as Salary*12). While displaying the query result, rename the column EName as Name:

mysql> SELECT EName as Name, Salary*12 FROM EMPLOYEE;

Observe that in the output, Salary*12 is displayed as the column name for the Annual Income column. In the output table, we can use alias to rename that column as Annual Income:

mysql> SELECT Ename AS Name, Salary*12 AS 'Annual Income'
    -> FROM EMPLOYEE;
``` …

By default, SQL shows all the data retrieved through a query as output. However, there can be duplicate values. The SELECT statement when combined with the DISTINCT clause, returns records without repetition (distinct records). For example, while retrieving a department number from the employee relation, there can be duplicate values as many employees are assigned to the same department. To select the unique department number for all the employees, we use DISTINCT as shown below:

mysql> SELECT DISTINCT DeptId FROM EMPLOYEE;
``` …

The WHERE clause is used to retrieve data that meet some specified conditions. In the OFFICE database, more than one employee can have the same salary. The following query gives distinct salaries of the employees working in the department number D01:

mysql> SELECT DISTINCT Salary
    -> FROM EMPLOYEE
    -> WHERE Deptid='D01';

As the column DeptId is of string type, its values are enclosed in quotes ('D01').

In the above example, the = operator is used in the WHERE clause. Other relational operators (<, <=, >, >=, !=) can be used to specify such conditions. The logical operators AND, OR, and NOT are used to combine multiple conditions.

Example 9.4 Display all the details of those employees of D04 department who earn more than 5000:

mysql> SELECT * FROM EMPLOYEE
    -> WHERE Salary > 5000 AND DeptId = 'D04';
``` …

Example 9.7 The following query selects details of all the employees who work in the departments having deptid D01, D02 or D04:

mysql> SELECT * FROM EMPLOYEE
    -> WHERE DeptId = 'D01' OR DeptId = 'D02' OR DeptId = 'D04';

The IN operator compares a value with a set of values and returns true if the value belongs to that set. The above query can be rewritten using the IN operator as shown below:

mysql> SELECT * FROM EMPLOYEE
    -> WHERE DeptId IN ('D01', 'D02', 'D04');

Both queries return the same seven employees.

Example 9.8 The following query selects details of all the employees except those working in department number D01 or D02:

mysql> SELECT * FROM EMPLOYEE …

ORDER BY clause is used to display data in an ordered form with respect to a specified column. By default, ORDER BY displays records in ascending order of the specified column's values. To display the records in descending order, the DESC (means descending) keyword needs to be written with that column.

Example 9.9 The following query selects details of all the employees in ascending order of their salaries:

mysql> SELECT * FROM EMPLOYEE
    -> ORDER BY Salary;

Example 9.10 Select details of all the employees in descending order of their salaries:

mysql> SELECT * FROM EMPLOYEE
    -> ORDER BY Salary DESC;
``` …

SQL supports a special value called NULL to represent a missing or unknown value. It is important to note that NULL is different from 0 (zero). Also, any arithmetic operation performed with a NULL value gives NULL. For example: 5 + NULL = NULL because NULL is unknown, hence the result is also unknown. In order to check for a NULL value in a column, we use the IS NULL operator.

Example 9.11 The following query selects details of all those employees who have not been given a bonus. This implies that the bonus column will be blank:

mysql> SELECT * FROM EMPLOYEE
    -> WHERE Bonus IS NULL;
``` …

Many a times we come across situations where we do not want to query by matching exact text or value. Rather, we are interested to find matching of only a few characters or values in column values. For example, to find out names starting with "T" or to find out pin codes starting with '60'. This is called substring pattern matching. We cannot match such patterns using the = operator as we are not looking for an exact match. SQL provides a LIKE operator that can be used with the WHERE clause to search for a specified pattern in a column.

The LIKE operator makes use of the following two wild card characters:

• % (per cent) — used to represent zero, one, or multiple characters
• _ (underscore) — used to represent exactly a single character

Example 9.13 The following query selects details of all those employees whose name starts with 'K':

mysql> SELECT * FROM EMPLOYEE
    -> WHERE Ename like 'K%';

Example 9.14 The following query selects details of all those employees whose name ends with 'a', and gets a salary more than 45000:

mysql> SELECT * FROM EMPLOYEE
    -> WHERE Ename like '%a'
    -> AND Salary > 45000;

Example 9.15 The following query selects details of all those employees whose name consists of exactly 5 letters and starts with any letter but has 'ANYA' after that:

mysql> SELECT * FROM EMPLOYEE
    -> WHERE Ename like '_ANYA';
``` …