我无法弄清楚如何为学生运行查询 [英] ICannot figure out how to run queries for student

问题描述

这是我为每个表创建的代码，不确定如何连接表来计算 gpa??

Here is my code that I created for each of the tables, not sure how to join the tables to calculated the gpa's??

CREATE TABLE Student(SSN NUMBER(4) NOT NULL,
SName VARCHAR(20) NOT NULL,
Major VARCHAR(4),

推荐答案

因为grade和SSN在成绩表中，所以不需要加入就可以得到平均成绩如下:

Because grade and SSN are in the grades table, you dont need to join and can get the average grade like this:

这给出了每个学生的平均成绩，按成绩排序:

This gives the average grade per student, ordered by the grade:

SELECT AVG(g.Grade), g.SSN
FROM Grade g
group by g.SSN
order by AVG(g.Grade)

如果您希望每门课程的平均成绩如下所示:

If you wanted average grade per course it would look like this:

SELECT AVG(g.Grade), g.cno
FROM Grade g
group by g.cno
order by AVG(g.Grade)

但是，如果您需要来自学生的更多信息，则需要加入学生表:

However, if you need more information from student, you'd need to join to the student table:

select *
from (
   SELECT AVG(g.Grade) as average_grade, g.SSN
   FROM Grade g
   group by g.SSN) a
inner join Student s on a.ssn = s.ssn

这篇关于我无法弄清楚如何为学生运行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！