May 2009

May 2009
Let Me Google That For You

A SQL query walks into a bar. What does it say?
Click here for the answer. Funny.
Aggregation Disambiguation 

This month’s question comes to us from a reader:

“What is the difference between aggregate functions and analytic functions?”

Senior Staff Consultant Rich Headrick responds:

In the context of relational databases, to aggregate means to summarize. Aggregate functions operate on groups of rows and summarize them. The number of rows in the result is therefore less than the number of rows that you started with. A GROUP BY clause is used in conjunction with aggregate functions. The aggregate function produces one row of data per group specified by the GROUP BY clause. Typical aggregate functions are SUM, COUNT, MIN, MAX, and AVG. Here is an example.

  SELECT department_id,
AVG (salary)
AS avg

    FROM employees
WHERE department_id = 30
GROUP BY department_id;
————- ———-
30       4150
Analytic functions are often confused with aggregate functions because they also operate on groups of rows; SUM, COUNT, MIN, MAX, and AVG are also analytic functions. However, analytic functions do not reduce the number of rows and do not use a GROUP BY clause. However, they need a partitioning clause or a windowing clause, as in the following example.

SELECT department_id,
AVG (salary) OVER (PARTITION BY department_id) AS AVG

  FROM employees
WHERE department_id = 30;
————- ———-
30       4150
30       4150
30       4150
30       4150
30       4150
30       4150
Now let’s suppose that you want to find out how employee salaries deviate from the average salary. You can do this very easily with analytic functions.
SELECT department_id,
salary – AVG (salary) OVER (PARTITION BY department_id) AS deviation
FROM employees
WHERE department_id = 30;
————- ———– ———- ———-
30         114      11000       6850
30         115       3100      -1050
30         116       2900      -1250
30         117       2800      -1350
30         118       2600      -1550
30         119       2500      -1650
We’ve only barely scratched the surface of analytic functions but there’s a whole chapter devoted to them in the Oracle Database 10g Data Warehousing Guide which you can consult for more information.
I hope this answer helps you. Best of luck.
Comic Book on Relational Databases
There is a book for every need and for every audience. There is even a comic book to explain relational databases to kids: The Manga Guide to Databases.
Manga Guide to Databases
Manga” are Japanese-style comic books. In this book, a fairy named Tico teaches Princess Ruruna how to manage the kingdom of Kod’s fruit-selling business in the absence of the king and queen. They design a relational database together and the fairy teaches SQL to the princess. They even discuss advanced topics such as query optimization, distributed databases, and replication!
Brian Hitchcock reviewed this book for the NoCOUG Journal and observed that “what [database professionals] do all day has become relevant to a much wider audience than we realize … databases really are a part of everyday life and are becoming more important all the time.” We’re glad to hear it and give the book five stars.
P.S. You can look inside the book at
Database Specialists is mentioned in the April 8, 2009 edition of eWeek in an article titled Sizing Up the Costs of Remote Database Administration Services.  The article discusses the various remote DBA options and compares several providers of these services. Database Specialists is also mentioned in a follow-up article titled Inside the Outsourced World of Remote Database Services.
Unless you’ve been living under a rock, you know that Oracle has just acquired Sun Microsystems for billions of dollars. But do you know how many companies Oracle has acquired in the last four years? Here’s the complete list. Can you spot the four database technology companies in the list?

360Commerce                  Retail                     Jan 06
AdminServer                  Insurance                  May 08
Advanced Visual Technology   Retail                     Oct 08
Agile                        Manufacturing              May 07
AppForge                     Business Applications      Apr 07
BEA                          Enterprise Technology      Jan 08
Bharosa                      Identity Management        Jul 07
Bridgestream                 Identity Management        Sep 07
Captovation                  Content Management         Jan 08
ClearApp                     Systems Management         Sep 08
Context Media                Content Management         Jul 05
Demantra                     Manufacturing              Jun 06
e-Test                       Systems Management         Mar 08
Global Knowledge Software    Business Applications      Jul 08
G-Log                        Manufacturing              Sep 05
Haley                        Business Applications      Oct 08
HotSip                       Communications             Feb 06
Hyperion                     Performance Management     Mar 07
i-flex                       Banking                    Aug 05
Innobase                     Database                   Oct 05
Interlace Systems            Performance Management     Oct 07
LODESTAR                     Utilities                  Apr 07
LogicalApps                  Business Applications      Oct 07
MetaSolv Software            Communications             Oct 06
Moniforce                    Systems Management         Dec 07
Net4Call                     Communications             Apr 06
Netsure Telecom Limited      Communications             Sep 07
Oblix                        Identity Management        Mar 05
OctetString                  Identity Management        Nov 05
PeopleSoft                   Enterprise Applications    Jan 05
Portal Software              Communications             Apr 06
Primavera                    Project Management         Oct 08
ProfitLogic                  Retail                     Jul 05
Relsys                       Health Sciences            Mar 09
Retek                        Retail                     Apr 05
Siebel                       Enterprise Applications    Jan 06
Sigma Dynamics               Business Applications      Aug 06
Skywire Software             Insurance                  Jun 08
Sleepycat                    Database                   Feb 06
SPL WorldGroup               Utilities                  Nov 06
Stellent                     Content Management         Nov 06
Sun Microsystems             Enterprise Technology      Apr 09
Sunopsis                     Middleware Management      Oct 06
Tacit Software               Content Management         Nov 08
Tangosol                     Middleware Management      Mar 07
Telephony@Work               Business Applications      Jun 06
TempoSoft                    Business Applications      Dec 05
Thor Technologies            Identity Management        Nov 05
TimesTen                     Database                   Jun 05
TripleHop                    Database                   Jun 05

Call Database Specialists when you need remote DBA services or onsite support for your mission-critical Oracle database systems. Arrange a free consultation with a senior Database Specialists team member to find out how we can help increase your uptime, improve performance, minimize risk, and reduce costs. Visit our website for no-cost resources, white papers, conference presentations and handy scripts.

David Wolff
CEO, Database Specialists, Inc.
(415) 344-0500 x48

Leave a Reply

Your email address will not be published. Required fields are marked *