| Team Fly | |
Robbinette Farmington
11167.65
9 rows selected.
The following steps correspond to the set commands in the code listing that follows them. The original SQL query will also be executed a second time with much nicer formatting results.
1. Set the page size to 15. (You'll probably never have such a small page size, but we're doing this to illustrate multiple pages with this small result set.)
2. Set the line size to 70.
3. Add a title at the top of the page with ''Customer Sales Report" and "Utah Region" in the first and second lines, respectively.
4. Add a footer with "CONFIDENTIAL REPORT" displayed.
5. Format the last name to be exactly 12 characters long and with a title "Last Name" listed on two separate lines.
6. Format the city with "City" as the title and the data fixed at 15 characters long.
7. Format the summed amount sold with a two-line title "Total Sales." Format the data to include a dollar sign, two digits following the decimal point, and a comma to denote thousands.
SQL> set pagesize 15
SQL> set linesize 70
SQL> ttitle 'Customer Sales Report | Utah Region'
SQL> btitle 'CONFIDENTIAL REPORT'
SQL> column cust_last_name format a12 wrap heading 'Last | Name'
SQL> column cust_city format a15 heading 'City'
SQL> column sum(amount_sold) format $999,999.99 wrap heading 'Total | Sales'
SQL> select cust_last_name, cust_city, sum(amount_sold)
2 from customers natural join sales
3 where cust_state_province = 'UT'
4 group by cust_last_name, cust_city;
Mon Jan 12 page 1
Customer Sales Report
Utah Region
Last Total
Name City Sales
------------ --------------- ------------
Bane Farmington $62,605.42
Desai Farmington $240.95
Eubank Farmington $21,297.49
Wilbur Farmington $190.96
Kuehler Farmington $23,258.40
| Team Fly | |