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

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

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Question 1:

SQL Query :

select cname,count(grade) from course,Reg
where
course.cno=Reg.cno
group by cname
order by cname,count(grade);

Explanation :Given query will join two tables course and Reg and will return the course name and number of students got the letter grade.

******************************

Question 2:

SQL Query :

select sid,sname,datediff(yy,convert(datetime, DOB),getdate()) as 'Age' FROM student
order by age;

Explanation :This query will return the age of the student.

******************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.