1. 使用SCHOOL数据库,在SQL SERVER 2000 的查询分析器中使用SQL语言完成特定功能
## 1)查询年级为2001的所有学生的名称,按编号顺序排列:##
select sname
from students
where grade='2001'orderby sid asc;
## 2)查询所有课程名称中含有data的课程编号##
select cid
from courses
where cname like'%data%';
## 3)统计所有老师的平均工资 ##
selectavg(salary) 平均工资
from teachers;
## 4)查询至少选了3门课的学生编号 ##
selectdistinct sid
from choices
groupby sid
havingcount(*)>=3;
## 5)查询学号为800009026的学生的姓名、所选课名及成绩##
select s.sname,co.cname,ch.score
from students s join choices ch on s.sid=ch.sid join courses co on ch.cid=co.cid
where s.sid='800009026';
## 6)查询没有学生选的课程编号##
select cid
from courses
where cid notin
(selectdistinct cid
from choices
);
##7 )查询既选了C++又选了Java课程的学生编号 ##
select sid
from choices ,courses
where choices.cid=courses.cid and cname='java'and sid in
(
SELECT sid
from choices ,courses
where choices.cid=courses.cid and cname='C++'
)
## 8)查询选了C++但没选Java课程的学生编号; ##
select sid
from choices ,courses
where choices.cid=courses.cid and cname='C++'and sid notin
(
SELECT sid
from choices ,courses
where choices.cid=courses.cid and cname='JAVA'
)