Using analytic SQL functions

Analytic SQL is one of those Oracle Database native features that you can start using immediately after installing the database. Of course, you will also need some data stored in the database to play with. However, the data available in the demonstration database schemas can be quite enough to begin with.

Answering simple questions

As stated earlier, there are business questions that can be answered with a simple SQL query issued against the database. Questions starting with "how many" are a good example. Often, to answer such questions, you can use the COUNT SQL function. For example, you might need to know how many employees in your organization have been working for the company for 15 years or more. In this example, you might query the employees table located in the hr/hr demonstration schema, issuing the following statement:

SELECT count(*) FROM employees WHERE (EXTRACT(YEAR FROM (SYSDATE)) - EXTRACT(YEAR FROM (hire_date))) >= 15;

The output should look...