sql - 处理平均绩点的查询

Question

我在尝试使此 sql 语句工作时遇到问题。应该这样计算差距。专业 GPA 的计算方式与 GPA 相同，只是计算中仅包括学生专业的类(class)。例如，假设一名 CS 学生在 CS 类(class)中获得 A，在数学类(class)中获得 B，则该学生的主修 GPA 将为 4.0。

List the students and their major GPA. The results do not need to include the students who have not taken any classes in their major.

到目前为止，我有以下查询，它没有完全返回我想要的。

select sum(value*units)/sum(units) as GPA ,f.name from faculty f
inner join sections s on s.instructor_id = f.id
inner join courses c on c.id=s.id
inner join grades g on g.value =c.units
group by f.name

我得到的表是

name    gpa
Art     4.00
Bob     4.00
Pat     3.30
Amy     3.30
Kim     3.30
Sue     3.30
Joe     2.70
Lee     2.70
Max    2.70

这几乎是我想要的，但它显示了甚至没有上过任何课的学生的 gpa。

数据库看起来像

   create table departments (
    id      integer primary key,
    name    varchar(255)
);

insert into departments (id, name) values (10, 'Computer Science');
insert into departments (id, name) values (20, 'Math');
insert into departments (id, name) values (30, 'Drama');

 create table students (
    id              integer primary key,
    name            varchar(255),
    graduation_date date,
    major_id        integer references departments(id)
);

insert into students (id, name, graduation_date, major_id) values
    (1, 'Joe', null, 10);
insert into students (id, name, graduation_date, major_id) values
    (2, 'Amy', '2009-04-22', 20);
insert into students (id, name, graduation_date, major_id) values
    (3, 'Max', null, 10);
insert into students (id, name, graduation_date, major_id) values
    (4, 'Sue', '2009-01-10', 20);
insert into students (id, name, graduation_date, major_id) values
    (5, 'Bob', '2009-03-05', 30);
insert into students (id, name, graduation_date, major_id) values
    (6, 'Kim', null, 20);
insert into students (id, name, graduation_date, major_id) values
    (7, 'Art', null, 30);
insert into students (id, name, graduation_date, major_id) values
    (8, 'Pat', '2005-07-11', 20);
insert into students (id, name, graduation_date, major_id) values
    (9, 'Lee', null, 10);

create table grades (
    id          integer primary key,
    letter      varchar(255) not null unique,
    value       real
);

insert into grades (id, letter, value) values (1, 'A', 4.0);
insert into grades (id, letter, value) values (2, 'A-', 3.7);
insert into grades (id, letter, value) values (3, 'B+', 3.3);
insert into grades (id, letter, value) values (4, 'B', 3.0);
insert into grades (id, letter, value) values (5, 'B-', 2.7);
insert into grades (id, letter, value) values (6, 'C+', 2.3);
insert into grades (id, letter, value) values (7, 'C', 2.0);
insert into grades (id, letter, value) values (8, 'C-', 1.7);
insert into grades (id, letter, value) values (9, 'D+', 1.3);
insert into grades (id, letter, value) values (10, 'D', 1.0);
insert into grades (id, letter, value) values (11, 'D-', 0.7);
insert into grades (id, letter, value) values (12, 'F', 0.0);
insert into grades (id, letter) values (13, 'CR');
insert into grades (id, letter) values (14, 'NC');

create table courses (
    id              integer primary key,
    title           varchar(255),
    units           integer,
    department_id   integer references departments(id)
);

insert into courses (id, title, units, department_id) values
    (12, 'Databases', 4, 10);
insert into courses (id, title, units, department_id) values
    (22, 'Compilers', 4, 10);
insert into courses (id, title, units, department_id) values
    (32, 'Calculus 1', 4, 20);
insert into courses (id, title, units, department_id) values
    (42, 'Algebra', 4, 20);
insert into courses (id, title, units, department_id) values
    (52, 'Acting', 4, 30);
insert into courses (id, title, units, department_id) values
    (62, 'Elocution', 2, 30);
insert into courses (id, title, units, department_id) values
    (72, 'Calculus 2', 4, 20);
insert into courses (id, title, units, department_id) values
    (82, 'Topology', 4, 20);

create table sections (
    id              integer primary key,
    course_id       integer not null references courses(id),
    instructor_id   integer references faculty(id),
    year            integer
);

insert into sections (id, course_id, instructor_id, year) values
    (12, 12, 6, 2007);
insert into sections (id, course_id, instructor_id, year) values
    (13, 12, 1, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (14, 22, 1, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (23, 12, 6, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (24, 22, 1, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (32, 32, 2, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (33, 32, 2, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (34, 82, 2, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (43, 32, 3, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (51, 62, 4, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (52, 52, 4, 2008);
insert into sections (id, course_id, instructor_id, year) values
    (53, 62, 4, 2009);
insert into sections (id, course_id, instructor_id, year) values
    (54, 52, 4, 2009);

create table enrollment (
    id          integer primary key,
    student_id  integer not null references students(id),
    section_id  integer not null references sections(id),
    grade_id    integer references grades(id)
);

insert into enrollment (id, student_id, section_id, grade_id) values
    (14, 1, 12, 8);
insert into enrollment (id, student_id, section_id, grade_id) values
    (15, 1, 13, 3);
insert into enrollment (id, student_id, section_id, grade_id) values
    (16, 1, 14, 5);
insert into enrollment (id, student_id, section_id, grade_id) values
    (17, 1, 32, 1);
insert into enrollment (id, student_id, section_id, grade_id) values
    (18, 1, 34, 2);
insert into enrollment (id, student_id, section_id, grade_id) values
    (19, 1, 53, 13);
insert into enrollment (id, student_id, section_id, grade_id) values
    (24, 3, 12, 2);
insert into enrollment (id, student_id, section_id, grade_id) values
    (25, 3, 14, 5);
insert into enrollment (id, student_id, section_id, grade_id) values
    (26, 3, 32, 1);
insert into enrollment (id, student_id, section_id, grade_id) values
    (27, 3, 34, 2);
insert into enrollment (id, student_id, section_id, grade_id) values
    (28, 3, 54, 7);
insert into enrollment (id, student_id, section_id, grade_id) values
    (34, 2, 43, 3);
insert into enrollment (id, student_id, section_id, grade_id) values
    (44, 4, 33, 4);
insert into enrollment (id, student_id, section_id, grade_id) values
    (54, 4, 53, 1);
insert into enrollment (id, student_id, section_id, grade_id) values
    (64, 6, 53, 1)

Answer 1

我试过你的样本，但无法得到与你相同的结果。

我试过这个:

select st.name, sum(value*units)/sum(units) as GPA 
from sections s
inner join enrollment e on e.section_id = s.id
inner join students st on st.id = e.student_id
inner join courses c on c.id=s.id
inner join grades g on g.value =c.units
group by st.name

我在学生和入学表上过滤，这意味着如果学生没有类(class)，他将不会出现在结果中

它适合你吗？