Sqlformatting

SQL & SQLPLUS
SQLPLUS & Building Reports
Dr. James Dullea james.dullea@boeing.com professor@comcast.net

PROBLEM
CREATE A REPORT THAT SHOWS THE DEPARTMENT NAMES, EACH EMPLOYEE BY FIRST AND LASTNAME IN THE DEPARTMENT IN ALPHABETIC ORDER,AND THEIR SALARIES. SHOW THE TOTAL OF DEPARTMENT SALARIES UNDER EACH DEPARTMENT THE TITLE OF THE REPORT IS "EMPLOYEES & SALARIES BY DEPARTMENT" PUT TODAY'SDATE IN THE RIGHT HAND CORNER

1

SQL FORMATTING
SELECT first_name||' '|| last_name AS "EMPLOYEE" FROM employees; embedding CHR(n) into the projection (SELECT) line

SQLPLUS FORMATTING
SET SETSET SET SET HEADING OFF/ON FEEDBACK OFF/ON AUTOCOMMIT OFF/ON/n PAGESIZE 66 NUMFORMAT B9,999,990.00

SPOOL C:/SQLSPO0L/FILENAME.LST SPOOL OFF

2

FORMATTING
--clears all breaks and computesCLEAR BREAKS CLEAR COMPUTES --turns top and bottom titles off BTITLE OFF TTITLE OFF

PROBLEM
CREATE A REPORT THAT SHOWS THE DEPARTMENT NAMES, EACH EMPLOYEE BY FIRST AND LAST NAME IN THE DEPARTMENT INALPHABETIC ORDER,AND THEIR SALARIES. SHOW THE TOTAL OF DEPARTMENT SALARIES UNDER EACH DEPARTMENT THE TITLE OF THE REPORT IS "EMPLOYEES & SALARIES BY DEPARTMENT" PUT TODAY'S DATE IN THE RIGHT HANDCORNER

3

BASIC SQL STATEMENT
SELECT d.name AS "DEPARTMENT", e.first_name||' '||e.last_name AS "EMPLOYEE", e.salary AS "SALARY", e.last_name, TO_CHAR(sysdate, 'Month dd,yyyy') as TODAY employeese, departments d e.department_id = d.department_id

FROM WHERE

ORDER BY d.name, e.last_name;

FORMATTING
COLUMN last_name noprint COLUMN Today NEW_VALUE TodaysDate noprint format a1 truncCOLUMN "DEPARTMENT" format a13 COLUMN "EMPLOYEE" format a18 COLUMN "SALARY" format B99,999.99

4

FORMATTING
TTITLE RIGHT TodaysDate SKIP 1 CENTER "DULLEA SPORTING GOODS COMPANY" SKIP 2 SET PAGESIZE60

FORMATTING
BREAK ON "DEPARTMENT" SKIP 2 COMPUTE SUM OF "SALARY" ON "DEPARTMENT"

5

FORMATTING
BREAK ON REPORT ON "DEPARTMENT" SKIP 1 COMPUTE SUM OF "SALARY" ON REPORT

Now Execute...