一个postgresSQL数据库的面试题

原题：
   为记录某校各班级学生的各科目期末考试成绩,要求建立合理的数据结构并给出关联关系,主要包括字段有姓名、性别、班级、科目、成绩等。
写出你了解的数据库有哪些，选择哪种数据库来记录相关数据，再按要求完成下面试题。
1. 写出创建数据库表结构的SQL语句。
2. 查询：总成绩前10名学生姓名和成绩顺序显示。查询结果显示如下
姓名    总成绩
张三    470
……
3. 查询：全校各科目平均成绩,一行显示。查询结果显示如下
语文    数学    英语    物理    化学
78      85     80      82      85
4. 查询：全校学生总成绩前100名。查询结果显示如下
姓名   班级    语文    数学    英语    物理    化学   总成绩
张三   一班    98     95      90     92      95     470
李四   三班    90     92      94     96      94     466
……
5. 查询: 各班级男、女学生语文科目的平均成绩。查询结果显示如下
班级    男生     女生
一班    75        82
二班    85        81  
……
答案：
--1.写出创建数据库表结构的SQL语句。
CREATE TABLE student(
stud_id int primary key not null,
class_id int not null references class_info(class_id),
stud_name varchar(20),
sex char(1)
)
CREATE TABLE class_info(
class_id int primary key not null,
class_name varchar(20) not null
)
CREATE TABLE achieve(
subject_id int  not null references subject(subject_id),
stud_id int not null references student(stud_id),
achievements int
)
CREATE TABLE subject(
subject_id INT primary key not null,
subject_name varchar(20)
)
--2.查询：总成绩前10名学生姓名和成绩顺序显示
select stud_name, sum(achievements) as 总成绩 from student a,achieve b
where a.stud_id=b.stud_id
group by stud_name
order by 总成绩 desc  limit 10
--3.查询：全校各科目平均成绩,一行显示。
select
round(avg(case when f.subject_id = 1 then f.achievements else 0 end )*5,2) as 语文,
round(avg(case when f.subject_id = 2 then f.achievements else 0 end )*5,2) as 数学,
round(avg(case when f.subject_id = 3 then f.achievements else 0 end )*5,2) as 英语,
round(avg(case when f.subject_id = 4 then f.achievements else 0 end )*5,2) as 物理,
round(avg(case when f.subject_id = 5 then f.achievements else 0 end )*5,2) as 化学
from (select b.stud_id from class_info a left join student b on (a.class_id=b.class_id))t
left join achieve f
on (t.stud_id=f.stud_id)
--4.查询：全校学生总成绩前100名。
select a.stud_name,b.class_name,
sum(case when f.subject_id = 1 then f.achievements else 0 end) as 语文,
sum(case when f.subject_id = 2 then f.achievements else 0 end) as 数学,
sum(case when f.subject_id = 3 then f.achievements else 0 end) as 英语,
sum(case when f.subject_id = 4 then f.achievements else 0 end) as 物理,
sum(case when f.subject_id = 5 then f.achievements else 0 end) as 化学,
(sum (achievements)) as 总成绩
from class_info b, student a, achieve f
where a.class_id=b.class_id and a.stud_id=f.stud_id
group by a.stud_name, b.class_name
order by 总成绩 desc limit 100
--5.查询: 各班级男、女学生语文科目的平均成绩。
select b.class_name,
case when sum(case when a.sex= '1' then 1 else 0 end ) != 0 then sum(case when a.sex='1' then f.achievements else 0 end) / sum(case when a.sex= '1' then 1 else 0 end ) else 0 end as 男生,
case when sum(case when a.sex= '0' then 1 else 0 end ) != 0 then sum(case when a.sex='0' then f.achievements else 0 end) / sum(case when a.sex= '0' then 1 else 0 end ) else 0 end as 女生
from class_info b,student a,achieve f
where a.class_id=b.class_id and a.stud_id=f.stud_id and f.subject_id = 1
group by b.class_name