数据库第五次作业

前言

1.1 和 2.3 是我请教的一个同学，其他是教材课后练习题的答案

1.1

Write the following queries in SQL, using the university schema.

Display a list of all instructors, showing their ID, name, and the ID of sections that they have taught. Make sure to show instructors who have not taught any section.

select I.ID, I.name, T.course_id, T.sec_id, T.semester, T.year
from instructor I
         left outer join teaches T on I.ID = T.ID;

1.2

Display a list of all instructors, showing their ID, name, and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outerjoin, and should not use scalar subqueries.

select ID, count(sec_id)
   from instructor natural left join teaches
   group by ID;

1.3

Write the same query as above, but using a scalar subquery, without outerjoin.

SELECT
	ID,
	(SELECT count(sec_id) FROM teaches
		WHERE teaches.ID = instructor.ID)
FROM instructor;

2.1

Write the following queries in SQL, using the university schema.

Find all course sections offered in Spring 2020, along with the names of the instructors teaching the section. Display course ID, section id, instructor’s ID and name. If a section has more than one instructor, it should appear as many times in the result as it has instructors. If it does not have any instructor, it should still appear in the result with the instructor name set to “—”.

select
    year,
    semester,
    sec_id,
    case
        when ID is null then "--"
        ELSE ID
    end,
    case
        when name is null then "--"
        else name
    end
from
    section natural left join teaches natural left join instructor
where
    year = 2020
    and semester = "Spring";

2.2

Display the name of all departments, with the total number of instructors in each department, without using scalar subqueries. Make sure to correctly handle departments with no instructors.

insert into department values("tmp", "tmp", 666);
select dept_name, count(ID)
from department natural left join instructor
group by dept_name;

2.3

Write the same query as above, but using a scalar subquery, without outerjoin.

with department_and_its_teacher as (select instructor.dept_name dept_name, instructor.name inst_name
                                    from department,
                                         instructor
                                    where department.dept_name = instructor.dept_name)
select dept_name, count(*)
from department_and_its_teacher
group by dept_name;

3.1

Suppose you are given a relation grade_points(grade, points), which provides a conversion from letter grades in the takes relation to numeric scores; for example an “A” grade could be specified to correspond to 4 points, an “A-” to 3.7 points, a “B+” to 3.3 points, a “B” to 3 points, and so on. The grade points earned by a student for a course offering (section) is defined as the number of credits for the course multiplied by the numeric points for the grade that the student received.

Given the above relation, and our university schema, write each of the following queries in SQL. You can assume for simplicity that no takes tuple has the null value for grade.

Find the total grade-points earned by the student with ID 12345, across all courses taken by the student.

注意，第一份代码是创建关系，第二份代码才是应该填入的答案，因为提交答案的代码框有代码量限制，这两份代码无法同时放在一份答案里面

create table grade_points(
    grade varchar(2) primary key,
    points float(2)
);
insert into grade_points values ("A", 4.0);
insert into grade_points values ("A-", 3.7);
insert into grade_points values ("B+", 3.3);
insert into grade_points values ("B", 3.0);
insert into grade_points values ("B-", 2.7);
insert into grade_points values ("C+", 2.3);
insert into grade_points values ("C", 2.0);
insert into grade_points values ("C-", 1.7);
insert into grade_points values ("F", 1.0);

select sum(points*credits)
from (takes natural join grade_points) join course using(course_id)
where ID = "12345";

3.2

Find the grade-point average (GPA) for the above student, that is, the total grade-points divided by the total credits for the associated courses.

select sum(points*credits)/sum(credits)
from (takes natural join grade_points) join course using(course_id)
where ID = "12345";

3.3

Find the ID and the grade-point average of every student.

select ID, sum(points*credits)/sum(credits) as gpa
from (takes natural join grade_points) join course using(course_id)
group by ID
order by gpa;