Mega Code Archive
Group by cube
<![CDATA[
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> create table class (
2 class_id number primary key,
3 class_desc varchar2(35),
4 credit_hrs number(2) );
Table created.
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> 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> 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> 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> select c.class_desc as class, s.student_name as student, avg(g.grade_received) as grade_avg,
2 grouping(c.class_desc) as class_ind, grouping(s.student_name) as stud_ind
3 from class c, student s, grades g
4 where c.class_id = g.class_id
5 and s.student_id = g.student_id
6 group by cube (c.class_desc, s.student_name) ;
CLASS STUDENT GRADE_AVG CLASS_IND STUD_IND
------------------------- ------------------------------ --------- ---------- ----------
88.25 1 1
Tom 90.33 1 0
Sill 86.33 1 0
Jason 88.00 1 0
Public Speaking 101 88.25 0 1
Public Speaking 101 Tom 90.33 0 0
Public Speaking 101 Sill 86.33 0 0
Public Speaking 101 Jason 88.00 0 0
8 rows selected.
SQL>
SQL>
SQL> drop table class;
Table dropped.
SQL> drop table student;
Table dropped.
SQL> drop table grades;
Table dropped.
]]>