SQL练习

模式图：

a. 找出Comp. Sci. 系开设的具有3个学分的课程。

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

b. 该查询涉及到多个表，并且在合并结果时有两种不同的ID属性，在合并时注意顺序。

select distinct student.name
from instructor join teaches using (ID)
join (student join takes using (ID)) using (course_id,sec_id,semester,year)
where instructor.name='Einstein';

c. 找出教师的最高工资。

select max(salary) from instructor;

d. 找出工资最高的所有教师。

select name
from instructor
where salary=(select max(salary) from instructor);

e. 找出09年秋季开设的每个课程段的选课人数。

select course_id,sec_id,count(ID)
from `section` join takes using (course_id,sec_id,semester,year)
where year=2009 and semester='Fall'
group by course_id,sec_id;

f. 从09年秋季找出最多的选课人数。

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

g. 从09年秋季找出选课人数最多的课程。

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

3.3

update instructor
set salary=salary*1.0
where dept_name='Comp. Sci.';