PostgreSQL学习（一）

这学期开始学习数据库，使用的是软件是PostgreSQL14.2，配套教材是中文版数据库系统概念第七版，简单记录一下。

第一次作业：

1. Find the titles of courses in the Comp.Sci. department that have 3 credits.
2. Find the IDs of all students who were taught by an instructor whose name is Einstein; make sure there are no duplicates in the result.
3. Find the highest salary of any instructor.
4. Find all instructors earning the highest salary (there may be more than one with the same salary).
5. Find the enrollment of each section that was offered in Fall 2009.
6. Find the maximum enrollment, across all sections, in Fall 2009.
7. Find the sections that had the maximum enrollment in Fall 2009.

首先，创建需要的几张表。创建方法是把下面的老师提供的代码复制粘贴到记事本中，另存为sql格式，在cmd中打开数据库直接用\i sql文件的路径执行整个文件，一次性创建多张表。

create table instructor
(  
	ID char(5),
	name varchar(20),
	dept_name varchar(20),
	salary numeric(8,2),

	primary key(ID)
);

create table course
(
	course_id char(8),
	title varchar(50),
	dept_name varchar(20),
	credits int,
	
	primary key(course_id)
);

create table section
(
	course_id char(8),
	sec_id char(2),
	semester char(8),
	year int,
	building varchar(20),
	room_no char(5),
	time_slot_id char(5),

	primary key(course_id, sec_id, semester, year),
	foreign key(course_id) references course
);

create table teaches
(
	ID char(5),
	course_id char(8),
	sec_id char(2),
	semester char(8),
	year int,

	primary key(ID, course_id, sec_id, semester, year),
	foreign key(ID) references instructor,
	foreign key(course_id, sec_id, semester, year) references section
);

create table student
(  
	ID char(5),
	name varchar(20),
	dept_name varchar(20),
	tot_cred int,

	primary key(ID)
);

create table takes
(
	ID char(5),
	course_id char(8),
	sec_id char(2),
	semester char(8),
	year int,
	grade int,

	primary key(ID, course_id, sec_id, semester, year),
	foreign key(ID) references student,
	foreign key(course_id, sec_id, semester, year) references section
);

之后插入记录，下面是老师提供的示例文件：

insert into instructor(ID, name, dept_name, salary) values('10101', 'Srinivasan', 'Comp.Sci.', 65000);
insert into instructor(ID, name, dept_name, salary) values('12121', 'Wu', 'Finance', 95000);
insert into instructor(ID, name, dept_name, salary) values('15151', 'Mozart', 'Music', 40000);
insert into instructor(ID, name, dept_name, salary) values('22222', 'Einstein', 'Physics', 95000);
insert into instructor(ID, name, dept_name, salary) values('32343', 'El Said', 'History', 60000);
insert into instructor(ID, name, dept_name, salary) values('33456', 'Gold', 'Physics', 87000);
insert into instructor(ID, name, dept_name, salary) values('45565', 'Kats', 'Comp.Sci.', 75000);
insert into instructor(ID, name, dept_name, salary) values('58583', 'Califieri', 'History', 62000);
insert into instructor(ID, name, dept_name, salary) values('76543', 'Singh', 'Finance', 80000);
insert into instructor(ID, name, dept_name, salary) values('76766', 'Crick', 'Biology', 72000);
insert into instructor(ID, name, dept_name, salary) values('83821', 'Brandt', 'Comp.Sci.', 92000);
insert into instructor(ID, name, dept_name, salary) values('98345', 'Kim', 'Elec.eng.', 80000);

insert into course(course_id, title, dept_name, credits) values('CS-101', 'C++', 'Comp.Sci.', 3);
insert into course(course_id, title, dept_name, credits) values('CS-315', 'Database Concepts', 'Comp.Sci.', 3);
insert into course(course_id, title, dept_name, credits) values('CS-347', 'Network', 'Comp.Sci.', 4);
insert into course(course_id, title, dept_name, credits) values('CS-356', 'Design pattern', 'Comp.Sci.', 3);
insert into course(course_id, title, dept_name, credits) values('FIN-201', 'Introdution to Finance', 'Finance', 2);
insert into course(course_id, title, dept_name, credits) values('MU-199', 'Music history', 'Music', 2);
insert into course(course_id, title, dept_name, credits) values('PHY-101', 'Genery physics', 'Physics', 3);

insert into section(course_id, sec_id, semester, year) values('CS-101', '1', 'Fall', 2009);
insert into section(course_id, sec_id, semester, year) values('CS-315', '1', 'Spring', 2010);
insert into section(course_id, sec_id, semester, year) values('CS-315', '2', 'Spring', 2010);
insert into section(course_id, sec_id, semester, year) values('CS-347', '1', 'Fall', 2009);
insert into section(course_id, sec_id, semester, year) values('FIN-201', '1', 'Spring', 2010);
insert into section(course_id, sec_id, semester, year) values('MU-199', '1', 'Spring', 2010);
insert into section(course_id, sec_id, semester, year) values('PHY-101', '1', 'Fall', 2009);

insert into teaches(ID, course_id, sec_id, semester, year) values('10101', 'CS-101', '1', 'Fall', 2009);
insert into teaches(ID, course_id, sec_id, semester, year) values('10101', 'CS-315', '1', 'Spring', 2010);
insert into teaches(ID, course_id, sec_id, semester, year) values('10101', 'CS-347', '1', 'Fall', 2009);
insert into teaches(ID, course_id, sec_id, semester, year) values('12121', 'FIN-201', '1', 'Spring', 2010);
insert into teaches(ID, course_id, sec_id, semester, year) values('15151', 'MU-199', '1', 'Spring', 2010);
insert into teaches(ID, course_id, sec_id, semester, year) values('22222', 'CS-347', '1', 'Fall', 2009);
insert into teaches(ID, course_id, sec_id, semester, year) values('22222', 'CS-315', '2', 'Spring', 2010);

insert into student(ID, name, dept_name) values ('12345', 'John Smith', 'Comp.Sci.');
insert into student(ID, name, dept_name) values ('11234', 'Chavez', 'Comp.Sci.');
insert into student(ID, name, dept_name) values ('23333', 'Mark', 'Finance');
insert into student(ID, name, dept_name) values ('13345', 'Merk', 'Comp.Sci.');
insert into student(ID, name, dept_name) values ('14345', 'Mfrk', 'Comp.Sci.');
insert into student(ID, name, dept_name) values ('15345', 'Msrk', 'Comp.Sci.');

insert into takes(ID, course_id, sec_id, semester, year)values('12345','CS-347', '1', 'Fall', 2009);
insert into takes(ID, course_id, sec_id, semester, year)values('11234','CS-347', '1', 'Fall', 2009);
insert into takes(ID, course_id, sec_id, semester, year)values('12345','CS-315', '2', 'Spring', 2010);
insert into takes(ID, course_id, sec_id, semester, year)values('13345','CS-347', '1', 'Fall', 2009);
insert into takes(ID, course_id, sec_id, semester, year)values('14345','CS-347', '1', 'Fall', 2009);
insert into takes(ID, course_id, sec_id, semester, year)values('15234','CS-347', '2', 'Fall', 2009);

每个表中可以插入的元组数似乎没有限制，可以自己增加元组用于验证自己的代码。但是需要注意因为创建表时定义了一些foreign key限制，比如创建表takes的时候有一条命令是

foreign key(ID) references student,

这就意味着在takes中增加元组时，每一个takes表中的ID需要在student表中存在。从逻辑上来说，就是这名学生选了某门课程，这名学生的信息必须存在。第一次增加元组时没有注意这些，导致报错。

1. Find the titles of courses in the Comp.Sci. department that have 3 credits.

select title
from course
where dept_name='Comp.Sci.' 
	  and credits=3;

2. Find the IDs of all students who were taught by an instructor whose name is Einstein; make sure there are no duplicates in the result.

select distinct student.ID
from student,takes,course,instructor,teaches 
where student.ID=takes.ID
	  and takes.course_id=course.course_id
	  and course.course_id=teaches.course_id
	  and instructor.ID=teaches.ID
	  and instructor.name='Einstein';

3. Find the highest salary of any instructor.

select max(salary)
from instructor;

4. Find all instructors earning the highest salary (there may be more than one with the same salary).

select name
from instructor
where salary>=all(select salary 
			     from instructor);

5. Find the enrollment of each section that was offered in Fall 2009.

select section.course_id, section.sec_id,count(takes.ID) as enrollment
from section,takes
where section.course_id=takes.course_id
	  and section.sec_id=takes.sec_id
	  and section.semester='Fall'	
	  and section.year=2009
group by section.course_id, section.sec_id;

这道题其实题干比较迷惑。我按照中国大学的情况把sec_id理解成课序号。由于同一门课程可以有多个课序号，我在分组计数时用课程号+课序号进行分组。

6. Find the maximum enrollment, across all sections, in Fall 2009.

with max_enrollment(enrollment)as
	(select count(takes.ID)
	from takes,section
	where section.course_id=takes.course_id
		and section.sec_id=takes.sec_id
		and section.semester='Fall'	
		and section.year=2009
	group by section.course_id, section.sec_id)
select max(enrollment)
from max_enrollment;

第六第七题都是在第五题上的延申。第五题单纯打印，就是单纯"select"出来就好，第六第七题就是在第五题"select"出的表的基础上的再次“select”，所以直接用with伪定义一张新表，对新表操作。实际上按照教材的解释，with语句是定义了临时关系。意思就是with定义的是一张临时表，不会存储在数据库中，在这里用完就没了。

7. Find the sections that had the maximum enrollment in Fall 2009.

with max_enrollment(enrollment,course_id,sec_id)as
	(select count(takes.ID),section.course_id,section.sec_id
	from takes,section
	where section.course_id=takes.course_id
		and section.sec_id=takes.sec_id
		and section.semester='Fall'	
		and section.year=2009
	group by section.course_id, section.sec_id)
select course_id,sec_id
from max_enrollment
where enrollment>=all(select enrollment
				from max_enrollment);

第六第七题应该也可以用嵌套语句完成，但会复杂一些。

初学数据库，很多还不熟练，对题目的理解也不知道正不正确。如果存在什么问题，恳请各位大佬不吝赐教。