Creating report with SQL: – Report is one of the most important feature of any programming languages including SQL programming languages. It provides formatted output report. We can create or format output data in simple way using SQL. The output report includes title, header, footer, grouping data and doing some computation on grouped data. There are number of statement used in SQL for formatted output. They are –
ttitle: – ttitle stand for top title. We can give title to output using the statement using the statement ttitle. This statement display output at top of the page. We can use title text and preposition at the top of the text. Syntax of the ttitle statement is –
here, preposition may be text of some other option. The options are col, skip, left, right and center.
- Col: – This option skip number of columns of current line. Number is specified by the user. For example, col 5 skip 5 column for next item.
- Skip: – This option skip number of lines specified by the user. If this option is not used by the user, one line skipped by default.
- Tab: – The tab option skip six columns in forward direction. Tab followed by n specify number of tabs. Is tab content 6 columns.
- Left: – This option aligns data or text at the left side.
- Right: – This option aligns data or text at the right side.
- Center: – This option aligns text or data at center position.
For example: –
ttitle left ‘General Report’ right ‘15-April-2017’ skip center ‘Employee List’; select * from employee;
The output will be displayed in the following format –
Formatting the output data: – Formatting is used to specify size of output data. SQL provide three different type of output format. They are called character format, numeric format and date format.
Character format: – The character format consists of letter ‘A’ followed by the size of the column in character. User can also use number of letter instead of number. If a value does not feet with in the width, it will be wrapped or truncated depending on whether SET WRAPED ON or OFF. Suppose we want to specify column with as 5 we use either A5 or AAAAA for character format. For example –
Set wrap on Select ename format:A(5) from employee;
Numeric format: – Numeric format is used in SQL form numeric type data. Size of numeric data fixed with digit 9. A number of 9 or digit 9 followed by size used in parenthesis to specified size of numeric type data. Single line used for one digit. For example, 9(5) or 99999 specify size of numeric data for five digits. Some other symbols are also used for numeric format. Some examples of numeric type format are as follows –
Format:99999 or format:9(5)
This format specifies width of numeric data five digit.
This format displays the leading zeroes in the specified width.
This format displays a dollar sign as prefix. If size of numeric value is less than format, number of blank spaces inserted after the dollar sign.
This format displays blank spaces at beginning instead of zeroes.
This format displays a negative sign (-) before the numeric value.
This format displays comma sign in the value. If size of values is equal to numeric data format, all common signs are printed as well as their position. And if value is less than the numeric data format, remaining comma sign are treated as blank spaces and at the beginning.
This format print the decimal point in the specified position.
This format convert numeric value in scientific notation.
For example: –
Select salary format:99999 from employee;
Date format: – The date format decides how the dates are printed on output. The date formats are constructed using the format elements. Suppose the date format is DDth ‘of’ month yy and the date is jul 17, 1999, then it will be printed as 17th of july 99. Following are the element of data format. they are –
This format is used for printing years in four digits, three digits or two digits respectively.
BC or AD
This format is used as indicator.
This format is used for printing month in two digit, full name of the month or first three letter of the month respectively.
This format printing name of the day in three letters or number of the day in two digits respectively.
This format print name of the day.
Btitle: – Btitle stands for bottom title. The user can add footer in output using the statement btitle. This statement is used to display a tittle at the bottom of each page on the report. Syntax of the btitle statement is –
Btitle <on/off> / <Text>
An example of btitle statement –
Btitle on: – This statement display bottom title if it is already existed.
Btitle off: – This statement hide existing btitle.
Btitle ‘Employee’: – The text employee will be listed at the end of each page during printing report.
Btitle left ‘End’ right ‘Good’;
The above two text will be printed at the left and at the right of each printing report.
Break: – The break statement is used to break a column value until. It is continued and when next value available, it displays only one time. The break statement will be use only one time. When we issue new break statement, it replaces the preceding completely. Syntax of the break statement is –
Break on <column name>;
Order by: – The order by is an option used with select statement to arrange records of selected database in ascending order. If user want to sort existing table in ascending order, an order by option can be use with select statement. Syntax of the select statement with order by clause is –
Select * from employee order by cname;
In case of above example, all column values of table employee are listed in ascending order on cname column.
Another example: –
Break on cname skip 2; Select * from employee order by cname;
In case of above example, all column values are sorted by cname column and one column name displayed only one and first time. After printing one group value, next group automatically skipped two line.