chaxungengxin.txt

至少选修刘老师所授课程中一门课程的女学生姓名 2)检索王同学不学的课程的课程号 3)检索全部学生都选修的课程的课程号与课程名。 4)检索选修课程包含刘老师所授课的学生学号。 5)求选修课程号为

1.
create database学生课程数据库
go

use学生课程数据库
go
create table student(
sno int not null primary key,
sname varchar(10) not null,
ssex char(2) not null,
sage int,
sdept varchar(10) )
go
insert into student (sno,sname,ssex,sage,sdept) values (95001,'李敏勇','男',20,'CS')
insert into student values (95002,'刘晨','女',19,'IS')
insert student values (95003,'王敏','女',18,'MA')
insert student values (95004,'张立','男',18,'IS')
go
create table course(
cno int not null ,
cname varchar(20) not null,
cpno varchar(20),
credit int not null, 
Teacher char（8）null,
primary key(cno))
go
insert course values (1,'数据库',5,4, '王芳')--可以把5赋给varchar型
insert course (cno,cname,credit, Teacher) values (2,'数学',2 , '刘新')
insert course values (3,'信息系统',1,'4', '刘新')--可以把‘4’引起来赋给int型
insert course values (4,'操作系统','6',3, '高升')
insert course values (5,'数据结构',7,4, '宋明')
insert course values (6,'数据处理',null,2, '张彬')
insert course values (7,'Pascal语言',6,4, '李磊')
go

create table sc(
sno int not null,
cno int not null,
grade int not null,
primary key(sno,cno))
go
insert sc values (95001,1,92)
insert sc values (95001,2,85)
insert sc values (95001,3,88)
insert sc values (95002,2,90)
insert sc values (95003,2,55)
insert sc values (95004,2,70)
go

2.
--查询与“刘晨”在同一个系学习的学生(嵌套查询IN)
select sname,sdept 
from student 
where sdept in(select sdept 
               from student 
               where sname='刘晨')
--或者
select s1.sname,s2.sdept 
from student s1,student s2 
where s1.sdept=s2.sdept and s2.sname='刘晨'
--查询选修了课程名为“数学”的学生学号和姓名（嵌套查询IN 或 多层嵌套）
select sno,sname 
from student 
where sno in(select sno 
             from sc 
             where cno in (select cno 
                           from course 
                           where cname='数学'))
--或者
select student.sno,sname 
from student,sc,course 
where student.sno=sc.sno and sc.cno=course.cno and course.cname='数学'
--查询其他系中比信息系中某一学生年龄小的学生姓名和年龄（嵌套查询any）
select sname, sage 
from student 
where sage 
                from student 
                where sdept='IS') 
      and sdept<>'IS' 
--或者
select sname, sage 
from student 
where sage<(select max(sage) 
           from student 
           where sdept='IS') 
      and sdept<>'IS'
--查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄（嵌套查询all）
select sname, sage 
from student 
where sage <
               select sage
from student 
               where sdept='CS') 
      and sdept<>'CS'
--查询所有选修了2号课程的学生姓名（嵌套查询 exists）
select sname 
from student 
where exists (select * 
              from sc 
              where sno=student.sno and cno=2)
--或者
select sname 
from student 
where sno in(select sno 
             from sc 
             where cno=2)
--查询没有选修1号课程的学生姓名（嵌套查询 not exists）
select sname 
from student 
where not exists(select * 
                 from sc 
                 where sno=student.sno and cno=1)
--或者
select sname 
from student 
where sno not in(select sno 
                 from sc 
                 where cno=1)
--注意：
--1.使用group by分组查询时，按照group by后的字段进行分组，值相等的记录分为一组，在分组基础上再查询。group by后的字段不支持对列分配的假名，不支持使用聚合函数。在select后的列（除了聚合函数中的），都要在group by子句中。
--2.Where和Having的区别：where的作用对象是表，从表中选出满足条件的记录；Having的作用对象是组，从组中选出满足条件的记录，所以Having一定是跟在group by后面的。
--3.多张表之间做内连接：注意在where或on中指明它们彼此的连接关系（好好体会一下！）
--4.在嵌套查询前面尽量少用“＝”，除非非常明确嵌套查询只会返回一行结果。

3 题的答案:
1)至少选修刘老师所授课程中一门课程的女学生姓名
select sname 
from s where sex='女' and sno in(select sno 
                                 from sc 
                                 where cno in (select cno 
                                               from c
                                               where tname like '刘%'))
NOTICE：有多种写法，比如联接查询写法：
select sname 
from s,sc,c 
where sex='女' and sc.sno=s.sno and sc.cno=c.cno and tname like '刘%'
但上一种写法更好一些
 
2)检索王同学不学的课程的课程号
select cno 
from c 
where cno not in (select cno 
                  from sc 
                  where sno in (select sno
                                from s 
                                where sname like '王%'))
 
3)检索全部学生都选修的课程的课程号与课程名。
select cno,cname 
from c 
where cno in(select cno
             from sc 
             group by cno 
             having count(sno)=(select count(sno)
                                from s))
 
4)检索选修课程包含刘老师所授课的学生学号。
select distinct sno 
from sc 
where cno in(select cno 
             from c 
             where tname like '刘%')
 
5)求选修课程号为2的学生的平均年龄。
select avg(age)
from s 
where sno in(select sno 
             from sc 
             where cno=2)
或者
select avg(age)
from s,sc 
where s.sno=sc.sno and cno=2
 
6)求刘老师所授课程的每门课程的学生平均成绩。
select sc.cno, avg(grade)
from sc,c 
where sc.cno=c.cno and tname like '刘%'
group by sc.cno
 
7)检索学号比刘同学大，而年龄比他小的学生姓名。
select sname 
from s 
where sno>all( select sno 
               from s 
              where sname like '刘%')
     and age<all( select age 
                  from s 
                  where sname like '刘%')
或者
select x.sname 
from s as x,s as y 
where y.sname like '刘%' and  x.sno>y.sno  and  x.age<y.age
 
8)求年龄大于女同学平均年龄的男同学姓名和年龄。
select x.sname,x.age 
from s as x 
where x.age > (select avg(age) 
               from s as y 
               where y.sex='女')
 
9)求年龄大于所有女同学年龄的男学生姓名和年龄。
select x.sname,x.age 
from s as x 
where x.sex='男' and x.age>all(select age 
                               from s as y 
                               where y.sex='女') 
 
10)在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表Student（SNO,SNAME,SEX）。
方法一：
insert into student
(sno,sname,sex)
select sno,sname,sex 
from s 
where not exists( select * 
                  from sc 
                  where grade<80 and sc.sno=s.sno)
方法二：
insert into student
(sno,sname,sex)
select s.sno,sname,sex 
from s,sc 
where s.sno=sc.sno 
group by s.sno,sname,sex 
having min(grade)>=80
 
11)把选课数学不及格的成绩全改为空值。

update sc 
set grade = NULL 
where grade<60 and cno in(select cno 
                          from c 
                          where cname='数学')