title: Analytical Query in SQL for MIN, MAX, and AVG
body: <p>I am trying to figure out a query for this question: for each major, list the number of students, minimum GPA, maximum GPA, average GPA, minimum age, maximum age, and average age. (Show GPA with 2 decimal points, age with no decimal points. You may find it useful to create a view with one of the previous queries for this one.)</p>
  <p>This is the script to create the table for SQL!</p>
  <pre><code>REM drop all the tables. Note that you need to drop the
  REM dependent table first before dropping the base tables.
  
  drop table Reg;
  drop table Student;
  drop table Course;
  
  REM Now create all the tables.
  
  create table Student
  (
      sid char(10) primary key,
      sname varchar(20) not null, 
      gpa float, 
      major char(10), 
      dob DATE
  );
  
  create table Course
  (
      cno char(10) primary key,
      cname varchar(20) not null, 
      credits int, 
      dept char(10)
  );
  
  create table Reg
  (
      sid references Student(sid) on delete cascade,  
      cno references Course(cno) on delete cascade,  
      grade char(2),  
      primary key (sid, cno)
  );
  
  REM Now insert all the rows.
  
  insert into Student values ('111', 'Joe', 3.5 , 'MIS', '01-AUG-2000');
  insert into Student values ('222', 'Jack', 3.4  , 'MIS', '12-JAN-1999');
  insert into Student values ('333', 'Jill', 3.2 , 'CS', '15-MAY-1998');
  insert into Student values ('444', 'Mary', 3.7 , 'CS', '17-DEC-2001');
  insert into Student values ('555', 'Peter', 3.8 , 'CS', '19-MAR-1999');
  insert into Student values ('666', 'Pat', 3.9,  'Math', '31-MAY-2000');
  insert into Student values ('777', 'Tracy', 4.0,  'Math', '18-JUL-1997');
  
  insert into Course values ('c101', 'intro', 3 , 'CS');
  insert into Course values ('m415', 'database', 4 , 'Bus');
  insert into Course values ('m215', 'programming', 4 , 'Bus');
  insert into Course values ('a444', 'calculus', 3 , 'Math');
  
  insert into Reg values ('111', 'c101', 'A');
  insert into Reg values ('111', 'm215', 'B');
  insert into Reg values ('111', 'm415', 'A');
  insert into Reg values ('222', 'm215', 'A');
  insert into Reg values ('222', 'm415', 'B');
  insert into Reg values ('333', 'c101', 'A');
  insert into Reg values ('444', 'm215', 'C');
  insert into Reg values ('444', 'm415', 'B');
  insert into Reg values ('555', 'c101', 'B');
  insert into Reg values ('555', 'm215', 'A');
  insert into Reg values ('555', 'm415', 'A');
  insert into Reg values ('666', 'c101', 'A'); 
  </code></pre>
  <p>This is what I have so far:</p>
  <pre><code>SELECT major,      
    count(distinct SID) as students,      
    round(min(gpa), 2),      
    round(max(gpa), 2),      
    round(avg(gpa), 2),      
    trunc(min(sysdate - dob)/365) as min_age,      
    trunc(max(sysdate - dob)/365) as max_age,      
    trunc(avg(sysdate - dob)/365) as avg_age, 
  FROM Student 
  GROUP BY MAJOR;
  </code></pre>