Set column format before doing the query : Column « SQL Plus « Oracle PL / SQL






Set column format before doing the query

    
SQL>
SQL>
SQL> create table student (
  2  student_id  number primary key,
  3  student_name  varchar2(25),
  4  student_year  varchar2(15),
  5  student_major  varchar2(25) );

Table created.

SQL>
SQL> insert into student values (1,'Tom','First','Art');

1 row created.

SQL> insert into student values (2,'Jack','First','Med');

1 row created.

SQL> insert into student values (3,'Peter','First','History');

1 row created.

SQL> insert into student values (4,'Jason','First','Science');

1 row created.

SQL> insert into student values (5,'Joe','Second','Education');

1 row created.

SQL> insert into student values (6,'Cat','Second','Finance');

1 row created.

SQL> insert into student values (7,'Sill','Second','Art');

1 row created.

SQL> insert into student values (8,'Bill','Second','Med');

1 row created.

SQL> insert into student values (9,'Mary','Third','History');

1 row created.

SQL>
SQL>
SQL> create table class (
  2  class_id    number primary key,
  3  class_desc  varchar2(35),
  4  credit_hrs  number(2) );

Table created.

SQL>
SQL> insert into class values (1,'Public Speaking 101',3);

1 row created.

SQL> insert into class values (2,'English 101',3);

1 row created.

SQL> insert into class values (3,'English 201',3);

1 row created.

SQL> insert into class values (4,'English 301',3);

1 row created.

SQL> insert into class values (5,'English 401',3);

1 row created.

SQL> insert into class values (6,'Marketing 101',3);

1 row created.

SQL> insert into class values (7,'Child Development 101',3);

1 row created.

SQL> insert into class values (8,'Golf for Novices',2);

1 row created.

SQL> insert into class values (9,'Biology 101',4);

1 row created.

SQL>
SQL>
SQL>
SQL> create table grades (
  2  student_id  number,
  3  class_id    number,
  4  assignment_desc varchar2(200),
  5  grade_received  number(3) );

Table created.

SQL>
SQL>
SQL> insert into grades values (1,1,'Exam 1',94);

1 row created.

SQL> insert into grades values (7,1,'Exam 1',88);

1 row created.

SQL> insert into grades values (4,1,'Exam 1',85);

1 row created.

SQL> insert into grades values (1,1,'Exam 2',87);

1 row created.

SQL> insert into grades values (7,1,'Exam 2',89);

1 row created.

SQL> insert into grades values (4,1,'Exam 2',91);

1 row created.

SQL> insert into grades values (1,1,'Paper 1',90);

1 row created.

SQL> insert into grades values (7,1,'Paper 1',82);

1 row created.


SQL>
SQL> set lines 90
SQL> set pages 100
SQL> col class format a25
SQL> col student format a30
SQL> col grade_avg format 999.99
SQL>
SQL> select c.class_desc as class, s.student_name as student, avg(g.grade_received) as grade_avg
  2     from class c, student s, grades g
  3    where c.class_id = g.class_id
  4      and s.student_id = g.student_id
  5    group by rollup (c.class_desc, s.student_name) ;

CLASS                     STUDENT                        GRADE_AVG
------------------------- ------------------------------ ---------
Public Speaking 101       Tom                                90.33
Public Speaking 101       Sill                               86.33
Public Speaking 101       Jason                              88.00
Public Speaking 101                                          88.25
                                                             88.25

5 rows selected.

SQL>
SQL> drop table class;

Table dropped.

SQL> drop table student;

Table dropped.

SQL> drop table grades;

Table dropped.

   
    
    
    
  








Related examples in the same category

1.Use 'format a30 heading' to define column name
2.Column format $9,999.99
3.Column heading format a13
4.column localtimestamp format a28
5.Use a13 to set the column length during displaying
6.Aligning decimals
7.Adding a group separator
8.Including a currency symbol
9.Wrapping text
10.JUSTIFY CENTER
11.JUSTIFY RIGHT
12.Disable the column formatting
13.SET string to display when value is NULL
14.COLUMN Salary heading "Current|Salary" format $9999.99
15.COLUMN fname heading "emp|Name" format a10
16.COLUMN id heading "emp|Number" format 9999
17.Copy column format with 'col ... like'
18.column format: ascii type, 26 letter long
19.column number format
20.Column data is aligned by type
21.Word Wrapped column format
22.Set number column format
23.Set column heading with column command
24.Set column separation with colsep