Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from Oracle SQL*Plus: The Definitive Guide

Designing a Simple Report

The first part of this chapter will lead you through the five steps involved in generating a simple, columnar report. This report will be complete with page headings, page footings, and column headings. In addition, you will learn about several settings, controlled by the SET command, that are useful when printing and formatting reports.

Step 1: Formulate the Query

The very first step to designing a report is to formulate the underlying SQL query. There is little point in doing anything else until you have done this. The remaining steps all involve formatting and presentation. If you haven’t defined your data, there is no reason to worry about how to format it.

For this chapter, let’s look at developing a report that answers the following questions:

  • To what projects is each employee assigned?

  • How many hours have been charged to each project?

  • What is the cost of those hours?

One way to satisfy those requirements would be to develop a report based on the following query, which summarizes the hours and dollars charged by employee and project:

SELECT E.EMPLOYEE_NAME,
       P.PROJECT_NAME,
       SUM(PH.HOURS_LOGGED) ,
       SUM(PH.DOLLARS_CHARGED)
  FROM EMPLOYEE E,
       PROJECT P,
       PROJECT_HOURS PH
 WHERE E.EMPLOYEE_ID = PH.EMPLOYEE_ID
   AND P.PROJECT_ID = PH.PROJECT_ID
 GROUP BY E.EMPLOYEE_ID, E.EMPLOYEE_NAME,
          P.PROJECT_ID, P.PROJECT_NAME;

If you execute this query using SQL*Plus, here’s what the output will look like:

EMPLOYEE_NAME PROJECT_NAME ---------------------------------------- ------------------------------------ ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata