Mega Code Archive

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.