fatdb6

数据库实验报告

实验 6 — Advanced SQL (Part I)

实验内容

1. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D). Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).

F级被任何及格分数（A、B、C、D）覆盖。创建一个视图，列出所有未被覆盖的不及格分数的信息（包含takes中的所有属性）

原数据库中的takes并没有grade为F的数据，也没有D的数据，为了不影响后续操作，将C认为不合格
由于这个版本似乎不支持exits，用了另一种写法。

找到所有takes a，满足其grade是C且不存在另一个takes b，是同一个人选的且科目相同，且成绩不为C。

CREATE VIEW v1 AS
select *
from takes as a
where a.grade like "%C%" and 1<=(
	select count(b.ID) 
	from takes as b
	where b.course_id=a.course_id and b.ID=a.ID 
	and b.grade not like "%C%"
) ;

2. Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F grades.

找到至少挂了两门课，且没有重修成功的学生
题目英文理解不足，考虑只找出所有满足要求的学生
即id在v1视图中出现了两次的学生。

select *
from student as a 
where 2<=(select count(*)
from v1 as b
where a.id=b.id
);

3. Grades are mapped to a grade point as follows: A:10, B:8, C:6, D:4 and F:0. Create a table to store these mappings, and write a query to find the CPI of each student, using this table. Make sure students who have not got a non-null grade in any course are displayed with a CPI of null.

创建一个表来储存这个绩点映射，并通过这个表来输出学生CPI。
将CPI理解成学生的总分，经过验证，原数据库没有学生没有选课

且成绩不只是ABCD。
先插入一个表来作映射
然后在表里插入映射规则
最后将表与takes 内连接。得到的关系再与学生外连接
这样没有选课的学生的CPI即为null

CREATE table IF NOT EXISTS grade_mapping (
grade varchar(2),
points integer
);

INSERT INTO grade_mapping (grade, points) VALUES
 ('A', 10),
 ('A-', 10),
 ('A+', 10),
 ('B', 8),
 ('B-', 8),
 ('B+', 8),
 ('C', 6),
('C-', 6),
     ('C+', 6),
 ('D', 4),
 ('F', 0);

SELECT
	t.id,
	s.name,
	SUM(gm.points) cpi
	FROM  student s  LEFT OUTER JOIN (takes t JOIN grade_mapping gm
	ON t.grade = gm.grade) ON t.id = s.id

group by t.id, s.name;

4. Find all rooms that have been assigned to more than one section at the same time. Display the rooms along with the assigned sections; I suggest you use a with clause or a view to simplify this query.

先为所有时间及地点相同的section 创建视图v3
即所有section ，存在另一个section与它时间地点相同
然后再将v3与 classroom 自然连接，直接输出即可

create view v3 as(
select *
from section a
where 1<(select count(*)
from section b
where a.room_number=b.room_number 
	and a.semester=b.semester 
    and a.building= b.building
    and a.sec_id=b.sec_id
    and a.year=b.year
   --  and a.time_slot_id=b.time_slot_id
    ) 
    )
;