找出Computer Science系的老师的平均工资:
select avg(salary) from instructor where dept_name='Comp. Sci.';
可以通过更名运算符给属性赋予一个有意义的名字:
select avg(salary) as avg_salary from instructor where dept_name='Comp. Sci.';
找出在2010年春季学期讲授一门课程的教师总数。
select count (distinct ID) from teaches where semester='Spring' and year=2010;
我们经常使用count函数求一个关系的元组总数:
select count (*) from teaches where semester='Spring' and year=2010;
有时候我们想将聚集函数作用于一组元组集上,这时候需要使用group by子句
找出每个系的平均工资
select dept_name ,avg(salary) as avg_salary from instructor group by dept_name;
找出每个系在2010年春季讲授一门课程的教师人数
select dept_name ,count(distinct ID) from instructor natural join teaches where teaches.semester='Spring' and teaches.year =2010 group by dept_name;
任何没有出现在group by中的属性如果出现在select中的话,它只能出现在聚集函数内部。
找出教师平均工资超过42000美元的系
select dept_name ,avg(salary) as avg_salary from instructor group by dept_name having avg(salary)>42000;
与select子句的情况类似,任何出现在having子句中,但没有被聚集的属性必须出现在group by子句中,否则查询被当做是错误的。
找出所有在2009年秋季和2010年春季的课程
select distinct course_id from section where semester ='Fall' and year = 2009 and course_id in (
select course_id from section where semester='Spring' and year=2010);
in和not in 也能用于枚举集合,比如,找出既不叫Mozart也不叫Einstein的教师
select name from instructor where name not in('Mozart','Einstein');
找出不同的学生总数,他们选修了ID为10101的教师讲授的课程段
select count(distinct ID) from takes where (course_id ,sec_id,semester,year)in
(select course_id,sec_id,semester,year from teaches where teaches.ID='10101');
找出满足下列条件的所有教师的姓名,他们的工资至少比一个Biology系的教师的工资高
select name from instructor where salary > some
(select salary from instructor where dept_name='Biology');
找出下列条件的所有教师的姓名,他们的工资值比Biology系每个教师的工资都要高
select name from instructor where salary > all
(select salary from instructor where dept_name='Biologgy');
找出所有系中平均工资最高的系
select dept_name from instructor group by dept_name having avg(salary)>=all(select avg(salary) from instructor group by dept_name);
找出在2009年秋季和2010年春季同时开的课程
select course_id from section as S where semester='Fall' and year=2009 and exists
(select * from section as T where semester='Spring' and year=2010 and S.course_id=T.course_id);
找出选修了Biology系开设的所有课程的学生
select S.ID,S.name from student as S where not exists ((select course_id from course where dept_name ='Biology')
except (select T.course_id from takes as T where S.ID=T.ID));
找出所有在2009年最多开设一次的课程
select T.course_id from course as T where unique (select R.course_id from section as R where T.course_id=R.course_id and R.year=2009);
postgres没有实现unique谓词.
一种等价的表达方法是:
select T.course_id from course as T where 1>=(select count(R.course_id) from section as R where T.course_id=R.course_id and R.year=2009);
找出系平均工资超过42000美元的那些系中教师的平均工资:
select T.dept_name ,T.avg_salary from (select dept_name,avg(salary) as avg_salary from instructor group by dept_name) as T where T.avg_salary>42000;
也可以写成
select dept_name,avg_salary from (select dept_name,avg(salary) from instructor group by dept_name) as dept_avg(dept_name,avg_salary) where dept_avg.avg_salary>42000;
找出所有系工资总额最大的系
select max(T.max_salary) from (select dept_name ,sum(salary) from instructor group by dept_name) as T(dept_name,max_salary);
找出具有最大预算值的系
with max_budget(value) as (select max(budget) from department)
select dept_name,budget from department ,max_budget where department.budget=max_budget.value;
找出所有工资总额大于所有系平均工资总额的系
with dept_total(dept_name,value) as (select dept_name ,sum(salary) from instructor group by dept_name),dept_total_avg(value) as (select avg(value) from dept_total) select dept_name from dept_total,dept_total_avg where dept_total.value>= dept_total_avg.value;