QUERYING using Database OFFICE

Every organisation keeps its data in databases organised as related tables. The running example here is a database named OFFICE, which holds several connected tables such as EMPLOYEE and DEPARTMENT. Each employee is assigned to a department, and that department's number (DeptId) is stored inside the EMPLOYEE table as a foreign key — the column that links an employee's record back to the DEPARTMENT table. All the queries that follow are applied to the EMPLOYEE data of Table 8.8:

Three employees — Vergese, Nachaobi and Daribha — have no bonus recorded, so their Bonus is NULL. Keep this table in view: every result below can be checked against it by hand. It is also worth pausing to think of examples from daily life — contact lists, library catalogues, school records — where storing data in a database and querying it like this would be helpful.

(A) Retrieving selected columns

Name only the columns you want after SELECT. To display the employee numbers of all the employees:

SELECT EmpNo
FROM EMPLOYEE;

This returns a single-column result holding all ten employee numbers. To display both the employee number and the name, list the two columns separated by a comma:

SELECT EmpNo, Ename
FROM EMPLOYEE;

(B) Renaming columns in the output — the alias AS

Sometimes the stored column name is not the heading you want in the output. The keyword AS gives a column an alias — a display name used only in that query's result. To show employee names under the heading Name:

SELECT EName AS Name
FROM EMPLOYEE;

Aliases become genuinely useful with computed columns. To display each employee's name along with the annual salary — the monthly Salary multiplied by 12 — while renaming EName as Name:

SELECT EName AS Name, Salary*12
FROM EMPLOYEE;

The computation works (Aaliya shows 120000, Kritika 720000, and so on), but the output column is headed literally Salary*12. An alias fixes the heading too:

SELECT Ename AS Name, Salary*12 AS 'Annual Salary'
FROM EMPLOYEE;

(C) The DISTINCT clause

By default SQL shows all the data a query retrieves, and that can include duplicate values. Since many employees are assigned to the same department, retrieving DeptId for everyone repeats department numbers. Combining SELECT with the DISTINCT clause returns records without repetition:

SELECT DISTINCT DeptId
FROM EMPLOYEE;

The result is the five distinct department numbers — D02, D01, D04, D03, D05 — instead of ten repeating values.

(D) The WHERE clause

WHERE retrieves only the data meeting specified condition(s). To display the distinct salaries of employees working in department D01:

SELECT DISTINCT Salary
FROM EMPLOYEE
WHERE DeptId = 'D01';

The answer is 60000, 45000 and 15000. Because DeptId is a string-type column, its value is enclosed in quotes ('D01'); numeric values are written bare.

The = used above is only one of the relational operators available in conditions — <, <=, >, >= and != work as well. Conditions can also be combined using the logical operators AND, OR and NOT:

• AND — both conditions must hold. Employees earning more than 5000 who work in department D04:

SELECT *
FROM EMPLOYEE
WHERE Salary > 5000 AND DeptId = 'D04';

This returns two rows — Gurpreet and Daribha. (Try replacing the AND with OR and compare the outputs: OR needs only one of the conditions to hold, so it selects far more rows. That contrast is exactly the difference between the two operators.)

• NOT — negates a condition. Records of all employees except Aaliya:

SELECT *
FROM EMPLOYEE
WHERE NOT Ename = 'Aaliya';

Nine rows come back — everyone but Aaliya. A worthwhile experiment: rewrite 'Aaliya' as 'AALIYA', 'aaliya' or 'AaLIYA' and observe whether the query produces the same output or an error.

• A range with AND — names and department numbers of employees earning between 20000 and 50000, both values inclusive:

SELECT Ename, DeptId
FROM EMPLOYEE
WHERE Salary >= 20000 AND Salary <= 50000;

Six employees qualify: Shabbir, Joseph, Sanya, Nachaobi, Daribha and Tanya.

The same range can be expressed with the comparison operator BETWEEN, which produces exactly the same six rows:

SELECT Ename, DeptId
FROM EMPLOYEE
WHERE Salary BETWEEN 20000 AND 50000;

(E) The membership operator IN

To pick rows whose column value is any one of several alternatives, OR conditions can be chained. Details of employees working in D01, D02 or D04:

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

The IN operator says the same thing compactly: it compares a value with a set of values and returns true if the value belongs to that set.

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

Both forms return the same seven rows. Combining NOT with IN inverts the test — details of all employees except those working in D01 or D02:

SELECT *
FROM EMPLOYEE
WHERE DeptId NOT IN ('D01', 'D02');

This returns the five employees of departments D03, D04 and D05. NOT is needed here because the requirement is to retrieve every record except those with the listed department numbers.

(F) The ORDER BY clause

ORDER BY displays data in an ordered (arranged) form with respect to a specified column. By default the arrangement is ascending; to get descending order, the keyword DESC is written after the column name.

Details of all employees in ascending order of salary:

SELECT *
FROM EMPLOYEE
ORDER BY Salary;

The listing runs from Aaliya (10000) up to Kritika (60000). In descending order of salary:

SELECT *
FROM EMPLOYEE
ORDER BY Salary DESC;

Now Kritika (60000) heads the list and Aaliya (10000) closes it. Two columns can also be specified in ORDER BY — for instance ORDER BY Salary, Bonus; or ORDER BY Salary, Bonus DESC; — and running both is an instructive exercise in how the second column arranges rows that the first column leaves tied.

(G) Handling NULL values

SQL supports a special value, NULL, to represent a missing or unknown value. For example, a village column in an address table would have no value for people living in cities — NULL stands in for such unknowns.

Because NULL is not an ordinary value, it is not tested with =; the test is IS NULL (and its opposite, IS NOT NULL). Details of all employees who have not been given a bonus — that is, whose bonus column is blank:

SELECT *
FROM EMPLOYEE
WHERE Bonus IS NULL;

This returns Vergese, Nachaobi and Daribha. Names of all employees who have been given a bonus:

SELECT EName
FROM EMPLOYEE
WHERE Bonus IS NOT NULL;

This returns the other seven — Aaliya, Kritika, Shabbir, Gurpreet, Joseph, Sanya and Tanya.

(H) Substring pattern matching — the LIKE operator …