SQL语句学习(2)

找出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;