Question

Having below 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));

questions (Oracle live SQL)

1. For each course, display the number of students who got each grade, ordered by the course and the letter grade.
2. List the current age in years for each student, ordered from youngest to oldest.
3. Lets do a simple analytics query - 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.

Answer 1

1) SELECT grade, cno, count(distinct sid) FROM Reg GROUP BY cno, grade ORDER BY cno, grade;

2) SELECT months_between(TRUNC(sysdate), to_date(dob, 'DD-MON-YYYY') )/12 as age FROM Student ORDER BY age;

3) SELECT major, count(distinct sid), round(min(gpa), 2), round(max(gpa), 2), round(avg(gpa), 2), min(months_between(TRUNC(sysdate), to_date(dob, 'DD-MON-YYYY') )/12), max(months_between(TRUNC(sysdate), to_date(dob, 'DD-MON-YYYY') )/12), avg(months_between(TRUNC(sysdate), to_date(dob, 'DD-MON-YYYY') )/12) FROM Student;