第三章 综合练习
1.创建一个数据库，数据库名字edu，数据文件逻辑名字edu_data，初始大小10M，最大
为1024M，增长比例为每次1M;日志文件逻辑名字为edu_log,初始大小5M，最大为100M,按10%比例增长，这些文件都存储到E盘根目录下，并且物理文件名与逻辑文件名全部相同。 CREATE DATABASE edu ON PRIMARY ( NAME = N'educ_data', FILENAME = N'e:\\educ_data.mdf' , SIZE = 10240KB , MAXSIZE = 1024M , FILEGROWTH = 1M) LOG ON
( NAME = N'educ_log', FILENAME = N'e:\\educ_log.ldf' , SIZE = 5M , MAXSIZE = 100M , FILEGROWTH = 10% )
2.在edu数据库里创建一个架构，架构名字为自己名字的汉语拼音。 create schema zhangsan; --自己名字的汉语拼音 3.在创建的架构中，创建如下表：
表1 student表（学生信息表）
字段名称 sno sname sex native birthday pno dno classno entime home tel 类 型 char char char char datetime char char char datetime varchar varchar 宽 度 8 8 2 20 4 6 4 40 40 允许空值 NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 主 键 是 说 明 学生学号 学生姓名 学生性别 籍贯 学生出生日期 专业号 学生所在院系（外键） 班级号 学生入校时间 学生家庭住址 学生联系电话 表2 course表（课程信息表） 字段名称 cno cname cpno experiment lecture semester 类 型 char char char tinyint tinyint tinyint 宽 度 10 20 10 允许空值 NOT NULL NOT NULL NULL NULL NULL NULL 主 键 是 说 明 课程编号 课程名称 先修课程（外键） 实验时数 授课学时 开课学期 credit tinyint NULL 课程学分 表3 student_course表（学生选课成绩表）
字段名称 sno cno score 类 型 char char tinyint 宽 度 8 10 允许空值 NOT NULL NOT NULL NULL 主 键 是 是 说 明 学生学号 课程编号 学生成绩,0-100之间 表4 teacher表（教师信息表）
字段名称 tno tname sex birthday dno pno home zipcode tel email 类 型 char char char datetime char varchar varchar char varchar varchar 宽 度 8 8 2 6 20 40 6 40 40 允许空值 NOT NULL NOT NULL NULL NULL NULL NULL NULL NULL NULL NULL 主 键 是 说 明 教师编号 教师姓名 教师性别 教师出生日期 教师所在院系（外键） 教师职务 教师家庭住址 邮政编码 联系电话 电子邮件 表5 teacher_course表（教师上课课表） 字段名称 tcid tno classno cno semester schoolyear classtime classroom weektime 类 型 smallint char char char char char varchar varchar tinyint 8 4 10 6 10 40 40 宽 度 允许空值 NOT NULL NULL NULL NOT NULL NULL NULL NULL NULL NULL
表6 department表（院系息表）
字段名称 dno dname dhome dzipcode dtel 类 型 char char varchar char varchar 宽 度 6 8 40 6 40 允许空值 NOT NULL NOT NULL NULL NULL NULL 主 键 是 说 明 学院编号 学院名称 学院地址 学院邮政编码 学院联系电话 主 键 是 说 明 上课编号 教师编号（外键） 班级号 课程编号（外键） 学期 学年 上课时间 上课地点 每周课时数 创建表的时候必须建立主、外键关系。 create table zhangsan.department(
dno char(6) primary key, dname char(8) not null, dhome varchar(40), dzipcode char(6), dtel varchar(40)
)
create table zhangsan.student(
sno char(8) not null primary key, sname char(8) not null, sex char(2),
[native] char(20), birthday datetime, pno char(4), dno char(6), classno char(4), entime datetime, home varchar(40), tel varchar(40)
foreign key(dno) references department(dno) )
create table zhangsan.course( cno char(10) primary key, cname char(20) not null, cpno char(10), experiment tinyint, lecture tinyint, semester tinyint, credit tinyint,
foreign key (cpno) references course(cno) )
create table zhangsan.student_course( sno char(8), cno char(10), score tinyint,
primary key(sno,cno),
foreign key(sno) references student(sno), foreign key(cno) references course(cno) )
create table zhangsan.teacher( tno char(8) primary key, tname char(8) not null, sex char(2),
birthday datetime, dno char(6),
pno tinyint,
home varchar(40), zipcode char(6), tel varchar(40), email varchar(40),
foreign key(dno) references department(dno) )
create table zhangsan.teacher_course( tcid smallint primary key, tno char(8), classno char(4),
cno char(10) not null, semester char(6), schoolyear char(10), classtime varchar(40), classroom varchar(40), weektime tinyint,
foreign key(tno) references teacher(tno), foreign key(cno) references course(cno) )
4.建立、删除索引
（1）在student表的sname列建立唯一降序索引；
create unique index IX_STUDENT_SNAME on zhangsan.student(sname desc)
（2）在course表的credit列上建立升序索引；
create index IX_COURSE_CREDIT on zhangsan.course(credit)
（3）在student_course表的sno列上建立聚集索引。
create clustered index IX_STUDENT_COURSE_SNO on zhangsan.student_course(sno)
（4）在student_course表上，以学号升序，学号相同按课程号降序建立索引；
create index IX_STUDENT_COURSE_SNOCNO on zhangsan.student_course(sno,cno desc)
（5）在teacher表的tno列上建立聚集升序索引。
create clustered index IX_TEACHER_TNO ON teacher(tno )
（6）将student_course表的sno列上的聚集索引删掉。 drop index IX_STUDENT_COURSE_SNO
5.将给出的数据插入到相应的表里，必须使用insert语句。 student表 学号 姓名 性别 男 男 男 籍贯 出生日期 专业号 2 2 1 院系编号 1 1 2 班级号 2 2 4 入学日期 家庭住址 NULL 13145322345 NULL 电话 20101001 张三 20101002 张强 20101003 张小兵 河北沧州 1989-03-15 河北邢台 1990-05-01 山东济南 1991-05-02 2010-9-1 河北省沧州市 2010-9-1 河北省邢台市 2010-9-1 河北省唐山市 20101004 李燕 20101005 李晓 20101006 上官青 20101007 李晓芳 20101008 上官文宏 20101009 上官文宏 20101010 张倩 20101011 刘英伟 20101012 刘伟 20101013 翟南 20101014 窦士厝 女 女 女 女 女 女 女 男 男 男 男 山东济南 1991-05-02 山东德州 1991-05-02 陕西西安 1993-05-02 陕西西安 1993-05-02 北京市 1988-05-02 河北保定 1988-05-02 河北保定 1988-11-02 河北保定 1988-11-02 河北保定 1989-11-02 上海市 1989-11-21 云南昆明 1988-10-02 NULL 1 3 3 NULL 3 1 NULL 1 3 3 2 2 3 1 3 3 2 3 2 4 4 1 4 1 1 3 1 3 2 2 3 1 2010-9-1 山东省济南市 2010-9-1 山东省德州市 2010-9-1 陕西省西安市 2010-9-1 陕西省西安市 2010-9-1 北京市 2010-9-1 山东省青岛市 2010-9-1 河北省保定市 2010-9-1 河北省保定市 2010-9-1 河北省保定市 2010-9-1 上海市 2010-9-1 云南省昆明市 15194598762 15394598762 NULL 15867598762 15897598762 15897598762 13897595671 15197595671 15397595671 137093837332 131311948930
course表 课程编号 1 2 3 4 5 6 7 课程名称 先修课程 实验时数 授课学时 开课学期 课程学分 数据库系统概论 3 100%掌握财务报表 7 数据结构 5 c++程序设计 5 c语言程序设计 NULL WEB开发 NULL 计算机导论 NULL 20 30 30 15 18 28 10 72 54 72 72 72 54 32 1 1 2 2 2 1 1 3 2 4 4 3 3 1
student_course表 课学生学号 程编号 学生成绩 20101001 20101001 20101001 20101001 20101001 20101001 20101001 20101002 20101002 20101002 20101003 20101003 20101003 20101004 1 2 3 4 5 6 7 2 4 6 2 3 4 2 90 70 95 90 88 NULL NULL 65 89 NULL 90 89 90 65 20101004 4 87 20101004 5 90 20101004 6 NULL 20101005 20101005 20101007 20101007 20101007 20101009 20101010 20101010 20101009 20101004 20101003 4 6 4 5 6 2 3 5 7 7 7 92 NULL 90 88 NULL 80 75 88 80 70 86
teacher表 教师编号 姓名 性别 出生日期 1975-04-26 1975-04-27 1975-04-28 1975-04-29 1975-04-30 1975-05-01 1975-05-02 1975-05-03 1975-05-04 1975-05-05 1975-05-06 院系编号 1 1 1 2 2 3 3 2 3 3 2 职务 讲师 副教授 讲师 副教授 教授 副教授 讲师 教授 讲师 讲师 讲师 家庭住址 河北省石家庄市 河北省石秦皇岛市 河北省石家庄市 河北省石家庄市 北京市 河北省石家庄市 河北省唐山市 河北省唐山市 河北省石家庄市 山东省济南市 新疆自治区乌鲁木齐市 邮政编码 联系电话 email dong@gmail.com liliang@gmail.com lixl@gmail.com xy23@gmail.com yugong@gmail.com chanjuan@163.com ranran@sina.com zw1965@sina.com lxl@sina.com fn@163.com xby@163.com 836001 董青 男 836002 李亮 男 836003 李晓亮 男 836004 项天 男 836005 余孝天 男 836006 鲁婵娟 女 836007 李冉冉 女 836008 庄文 男 836009 李晓璐 女 836010 范娜 女 836011 萧不语 男 50000 13798789765 66000 15198789765 50000 13898789765 50000 13138789765 100000 13845649765 50000 15145249765 63000 15145649765 63000 13845649765 50000 15875649765 250000 13693020392 830000 13098333290
teacher_course表 上课编号 教师编号 班级号 课程编号 期 学每周学年 上课时间 上课地点 课时数 1 2 3 4 836001 836001 836002 836002 1 1 2 2 1 2 2 3 1 2010 1 2010 1 2010 1 2010 8:00-11:00 8:00-11:00 8:00-11:00 8:00-10:00 公教楼305 公教楼305 c301 c302 3 3 3 2 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 836003 836003 836003 836004 836005 836006 836007 836007 836007 836006 836006 836006 836005 836004 836003 836003 2 1 3 2 2 2 2 3 3 3 3 3 3 3 3 2 1 3 4 3 3 3 3 4 5 4 5 6 5 4 5 5 1 1 1 3 2 1 2 2 2 8 9 8 7 6 5 4 2010 2010 2010 2010 2010 2010 2010 2010 2010 2010 2010 2010 2010 2010 2010 2010 8:00-10:00 14:00-16:00 14:00-16:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 14:00-17:00 c302 c302 c304 c204 c204 c301 c201 c204 c201 c201 c201 c201 c302 c201 c201 c201 2 2 2 3 3 3 3 3 3 3 3 3 3 3 3 3
department表 学院编号 学院名称 学院地址 学院联系电话 邮政编码 50024 50024 50024 50024 1 2 3 4 法政学院 教育学院 软件学院 数信学院 河北省石家庄市裕华区南二环东路20号 河北省石家庄市裕华区南二环东路20号 河北省石家庄市裕华区南二环东路20号 河北省石家庄市裕华区南二环东路20号 80788100 80788101 80788102 80788102
注意插入数据时注意满足参照关系，否则插入数据会出错。 例子：往学生表插入一条记录
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES ('108' ,'曾华' ,'男' ,'1977-09-01',95033);
向教师表插入一条记录 insert into teacher
values('101', '赵旭东', , , '副教授', '数学系') 6.查询
--简单查询部分练习
1)查询全体学生的学号、姓名、籍贯 select sno,sname,native from student
2)查询所有课程的名称及学分 select cname,credit from course
3)查询教师的姓名、性别、联系电话 select tname,sex,tel from teacher
4)查询每门课程的课程编号、课程名称及理论学时 select cno,cname,lecture-experiment 理论课时 from course
5)查询全体学生的姓名、联系电话，并在前面加上字符串‘联系方式’ select sname,'联系方式',tel from student
6)查询全体教师的人数 select count(tno) 教师人数 from teacher
7)查询全体教师的姓名、家庭住址、邮政编码（使用列别名） select tname '姓名',home '家庭住址',zipcode '邮政编码' from teacher
8)查询全体学生的姓名 select sname from student
9)查询本学期有课的教师编号 select distinct tno 有课的教师编号 from teacher_course where semester = '1'
10)查询所有选课的学生学号 select distinct sno from student_course
11)查询籍贯为河北保定的学生的学号和姓名 select sno,sname from student
where native='河北保定'
12)查询课程学分小于3分的课程名称 select cname
from course where credit<3
13)查询在c201教室上课的教师编号 select distinct tno from teacher_course where classroom='c201'
14)查询软件学院的办公电话 select dtel
from department
where dname='软件学院'
15)查询学生成绩在60-90分的学生的学号和课程号 select sno,cno
from student_course
where score between 60 and 90
16)查询学分为3分的课程信息 select * from course where credit=3
17)查询在教学楼C座上课的教师的编号 select distinct tno from teacher_course
where classroom like 'c%'
18)查询有先行课的课程编号及课程名 select cno,cname from course
where cpno is not null
19)查询籍贯为山东省的学生基本信息 select *
from student
where native like '山东%'
20)查询姓名为3个字，前两个字为‘上官’的学生学号和姓名 select sno,sname from student
where sname like '上官_'
21)查询专业号为3的女学生的姓名、生日和家庭住址
select sname,birthday,home from student
where pno='3' and sex='女'
22)查询学号为‘20101001’的学生选修的课程号及成绩，结果按成绩升序排列 select cno,score from student_course where sno='20101001' order by score asc
23)查询所有课程的基本信息，结果按授课学时降序排列，学时相同按学分升序排列 select * from course
order by lecture desc,credit asc
24)查询所有男教师的姓名和出生日期，结果年龄从小到大排序 select tname,birthday from teacher where sex='男'
order by birthday desc
25)查询选修2号课程学生成绩的最大值和最小值 select max(score) 最高分,min(score) 最低分 from student_course where cno='2'
26)查询所有教师的人数 select count(*) from teacher
27)查询所有专业号为1的学生人数 select count(sno) 人数 from student where pno='1'
28)查询选课人数超过5人的课程编号 select cno
from student_course group by cno
having count(*)>5
29)查询所有学生的基本信息以及所在学院的名称和学院联系电话 select student.*,department.dname,department.dtel
from student,department
where student.dno=department.dno
30)查询教师李亮的上课时间、上课地点和每周学时数 select classtime,classroom,weektime from teacher,teacher_course
where teacher.tno=teacher_course.tno and tname='李亮'
31)查询选修了数据库系统概论或数据结构的学生的学号和姓名 select distinct student.sno,sname from student,course,student_course
where student.sno=student_course.sno and course.cno=student_course.cno and
course.cname in ('数据库系统概论','数据结构')
32-67要求至少使用两种方式查询（对复杂查询，可以建立视图） 32)查询至多有两名男生的班级 方法1
select classno --男生人数少于3人得班级 from student where sex = '男' group by classno having count(sno) <3 union
select distinct classno --没有男生的班级 from student
where classno not in( select distinct classno from student where sex = '男' )
方法2
select distinct classno --所有的班级号 from student except
select classno --男生人数大于等于3人得班级 from student where sex = '男' group by classno,sex having count(sno) >=3
33）查询至少有一名教授的学院的信息
方法1 select *
from department where dno in( select dno from teacher where pno = '教授' group by dno having count(tno)>=1 )
方法2 select *
from department where dno not in ( select distinct dno from department except
select distinct dno from teacher where pno = '教授' )
34).查询出每个老师及其教课情况 方法1 select *
from teacher , teacher_course
where teacher.tno = teacher_course.tno
35).查询每门课程及其被选修的人数 方法1
select course.cno,cname,count(*) 选修人数 from student_course,course
where student_course.cno = course.cno group by course.cno,cname
方法2
select course.cno,cname,选课人数 from course,(
select cno,count(*) 选课人数 from student_course group by cno ) tmp
where course.cno = tmp.cno
36).查询籍贯是河北省的教师所教的课程信息 方法1
select distinct course.*
from course,teacher,teacher_course
where course.cno = teacher_course.cno and teacher.tno = teacher_course.tno and home like '河北%'
方法2 select * from course where cno in ( select cno
from teacher_course where tno in( select tno from teacher where home like '河北%' ) )
37).查询软件学院学生情况 方法1 select *
from student where dno in ( select dno from department where dname = '软件学院' )
方法2
select student.*
from student,department
where department.dno = student.dno and dname = '软件学院'
38).查询班级人数最多的班的学生情况 方法1 select *
from student
where classno in (
select classno from student group by classno having count(*)>=all(select count(*) from student group by classno) )
方法2
select student.*,num from student,( select classno,count(*) num from student group by classno) class_num
where student.classno = class_num.classno and num = ( select max(num) from (select classno,count(*) num from student group by classno) class_num )
方法3
create view class_num as
select classno,count(*) num from student group by classno select A.*
from student A,class_num B
where A.classno = B.classno and num = (select max(num) from class_num) drop view class_num
39).查询张姓学生选修的课程号、课程名 方法1
select distinct course.cno,cname from course,student,student_course
where course.cno = student_course.cno and student.sno = student_course.sno and sname like '张%'
方法2
select cno,cname from course where cno in ( select cno
from student_course where sno in ( select sno from student where sname like '张%' ) )
40).查询男学生选修课程的平均分 方法1
select avg(score)
from student,student_course
where student.sno = student_course.sno and sex = '男'
方法2
select avg(score) from student_course where sno in ( select sno from student where sex = '男' )
41).查询哪些学生选修了授课学时为54的课程 方法1 select *
from student where sno in (
select distinct sno from student_course where cno in ( select cno from course where lecture = 54 ) )
方法2
select distinct student.*
from student,student_course B,course where student.sno = b.sno and course.cno = b.cno and lecture = 54
42).查询比软件学院学生年龄都小的其他系学生的信息。 方法1 select *
from student
where birthday > ( select max(birthday) from student where dno = ( select dno from department where dname = '软件学院' ) ) and dno <> ( select dno from department where dname = '软件学院' )--其他系
方法2
select student.*
from student,department
where student.dno = department.dno and dname <>'软件学院' and birthday >( select max(birthday) from student,department where student.dno = department.dno and dname = '软件学院' )
方法3
create view software_student as
select A.*
from student A,department B where A.dno = b.dno and dname = '软件学院' select *
from student A ,software_student B where A.sno <> B.sno and A.birthday > (
select max(birthday) from software_student )
drop view software_student
43).查询比数信学院学院学生年龄大的教育学院学生信息。 方法1
select student.*
from student,department
where student.dno = department.dno and dname = '教育学院' and birthday < ( select max(birthday) from student,department where student.dno = department.dno and dname = '数信学院' )
方法2
select distinct A.*
from (select distinct student.* from student,department where student.dno = department.dno and dname = '教育学院') A, (select distinct student.* from student,department where student.dno = department.dno and dname = '数信学院') B where A.birthday < B.birthday
44).查询班级号为1的班的学生c语言程序设计成绩的平均成绩 方法1
select avg(score)
from student_course A where sno in ( select sno from student where classno = '1') and cno = ( select cno from course where cname = 'c语言程序设计')
方法2
select avg(score)
from student A,student_course B,course C where a.sno =b.sno and c.cno = b.cno and classno = '1' and cname = 'c语言程序设计'
45).查询计算机导论平均成绩最高的班级。 方法1
create view class_avg as
select classno,avg(score) pingjun
from student A,student_course B,course C where a.sno =b.sno and c.cno = b.cno and cname = '计算机导论' group by classno
select classno from class_avg where pingjun = ( select max(pingjun) from class_avg )
drop view class_avg
方法2
select classno
from student A,student_course B,course C where a.sno =b.sno and c.cno = b.cno and cname = '计算机导论' group by classno
having avg(score)>=all( select avg(score) from student A,student_course B,course C where a.sno =b.sno and c.cno = b.cno and cname = '计算机导论' group by classno )
46).查询选修人数最多的课程是哪个老师教的，显示教师号 ，教师姓名
select tno,tname from teacher where tno in ( select tno
from teacher_course where cno in ( select cno from student_course group by cno having count(sno) >=all ( select count(*) aver from student_course group by cno ) ) )
47).查询余孝天老师所教各门课程的平均成绩 方法1
select cno,avg(score) from student_course
where sno in ( --余孝天老师所教学生 select A.sno from student A,teacher B,teacher_course C where A.classno = C.classno and B.tno = C.tno and tname = '余孝天') and
cno in( --余孝天老师所教课程 select cno from teacher B,teacher_course C where B.tno = C.tno and tname = '余孝天') group by cno
方法2
select A.cno,avg(score)
from student_course A,teacher_course B,teacher C where tname = '余孝天' and A.cno = B.cno and B.tno = C.tno and A.sno in ( select sno from student where classno in(
select distinct classno from teacher_course B,teacher C where B.tno = C.tno and tname = '余孝天') )
group by A.cno
48).查询鲁婵娟老师所教课程的各个班级平均成绩 方法1
select classno,avg(score)
from student_course A ,student B where A.sno = B.sno and A.cno in ( select cno from teacher_course where tno in ( select tno from teacher where tname= '鲁婵娟')) and B.classno in ( select distinct classno from teacher_course A,teacher B where A.tno = B.tno and tname= '鲁婵娟') group by classno
方法2
select A.classno,avg(score)
from student A,student_course B,teacher_course C,teacher D where A.sno = B.sno and B.cno = C.cno and C.tno = D.tno and tname = '鲁婵娟' group by A.classno having A.classno in (
select distinct classno from teacher_course A,teacher B where A.tno = B.tno and tname = '鲁婵娟' )
49).查询鲁婵娟老师所教课程的学生的成绩 方法1 select *
from student_course where cno in ( select distinct cno from teacher_course A,teacher B
where A.tno = B.tno and tname = '鲁婵娟') and sno in ( select sno from student where classno in ( select distinct classno from teacher_course A,teacher B where A.tno = B.tno and tname = '鲁婵娟' ) )
50).查询有多少人选修了《数据结构》课程的先修课。 方法1
select count(*)
from student_course where cno = ( select cpno from course where cname = '数据结构')
方法2
select count(*)
from student_course A,course B where A.cno = B.cpno and cname = '数据结构'
51).查询软件学院教师所教课程信息 select distinct A.*
from course A,teacher_course B where A.cno = B.cno and tno in ( select tno from teacher,department where teacher.dno = department.dno and dname = '软件学院' )
52).查询软件学院教师所教课程的成绩，将结果按 课程号降序排列。
select distinct cno,score from student_course where cno in(
select distinct cno from teacher_course
where tno in( select tno from teacher where dno=( select dno from department where dname='软件学院')) ) and sno in ( select distinct A.sno from student A,teacher_course B,teacher C,department D where A.classno = B.classno and B.tno = C.tno and C.dno = D.dno and dname = '软件学院' )
order by cno desc
53).查询未授课教师的姓名和系 select tname,dname
from teacher A,department B where A.dno = B.dno and tno not in ( select distinct tno from teacher_course)
54).按职称显示软件学院的教师人数。 select pno,count(tno)
from teacher A,department B where A.dno = B.dno and dname = '软件学院' group by pno
55).查询成绩高于《数据结构》平均成绩的学生信息。 create view course_avg --各门课程的平均成绩 as
select cno,avg(score) grade from student_course group by cno
select sno
from student_course A,course_avg B where A.cno = B.cno and B.cno = ( select cno from course
where cname = '数据结构' ) and A.score>B.grade drop view course_avg
56).查询学生选修课程的情况，显示学号、姓名、 教师姓名、课程名称、成绩。
select s.sno,sname,s.cno,cname,score,c.tname
from ( select A.sno,A.classno,sname,cname,score,B.cno from student A,student_course B,course C where A.sno = B.sno and B.cno = C.cno )s left outer join ( select D.tno,tname,E.cno,classno from teacher D,teacher_course E where D.tno = E.tno ) c
on s.cno = c.cno and s.classno = C.classno
57).查询法政学院教师第一学期所带班级 select distinct classno
from teacher A,teacher_course B,department C where A.tno = B.tno and A.dno = C.dno and dname = '法政学院' and semester = '1'
58).查询第2学期哪些教师在公教楼上课。 select tname
from teacher A,teacher_course B where A.tno = B.tno and semester = '2' and classroom like '公教楼%'
59).查询数信学院的学生各科平均成绩，显示课程名、 平均成绩。
select cname,avg(score)
from student A,student_course B,course C ,department D where A.sno = B.sno and B.cno =C.cno and A.dno = D.dno and dname = '数信学院' group by B.cno,cname
60).查询选修了全部课程的学生的信息。 select *
from student A where not exists ( select * from course B where not exists ( select * from student_course C where c.sno = A.sno and B.cno = C.cno ) )
select * from student where not exists( select * from course c where cno not in( select cno
from student_course sc where sc.sno=student.sno))
61).查询至少选修了鲁婵娟所教的课程的学生信息 select *
from student A where not exists ( select * from teacher_course B where tno = (select tno from teacher where tname='鲁婵娟') and not exists ( select * from student_course C where C.sno = A.sno and B.cno = C.cno ) )
select * from student where not exists( select cno
from teacher_course tc where tno=(
select tno from teacher
where tname='鲁婵娟') and tc.cno not in( select cno from student_course sc where sc.sno=student.sno ) )
62).查询和张小兵同一个班级的其他学生的信息 select *
from student where classno = ( select classno from student where sname = '张小兵' ) and
sname <> '张小兵'
63).查询和刘英伟同年出生的学生信息（参考sql日期 操作函数） select *
from student
where year(birthday) = ( select year(birthday) from student where sname = '刘英伟' )
64).查询选修了3门以上课程学生信息 select *
from student where sno in ( select sno from student_course group by sno having count(cno)>=3 )
65).查询至少有2名女生的班级的学生信息 select *
from student
where classno in ( select classno from student where sex ='女'
group by classno having count(sno)>=2 )
66).查询软件学院年龄小于25岁的老师的信息 select A.*
from teacher A,department B where A.dno = B.dno and dname = '软件学院' and
(year(getdate())-year(birthday))<25
67).查询低于该门课程平均分的学生的成绩信息。 select *
from student_course B where B.score <( select avg(score) from student_course C where B.cno =C.cno )
select * from student_course sc1 where exists(
select * from student_course sc2 where sc1.cno=sc2.cno group by sc2.cno
having sc1.score --集合查询 68).年龄小于23岁的女老师和女学生的信息 select sno '编号',sname '姓名' from student where year(getdate())-year(birthday)<23 and sex='女' union select tno,tname from teacher where sex='女' and year(getdate())-year(birthday)<23 69).查询不教课的老师信息。 select * from teacher except select * from teacher where tno in(select tno from teacher_course) 70).查询班级号为3,且性别为女的学生的信息 select * from student where classno='3' intersect select * from student where sex='女' 7.更新 1)将年龄小于18岁的男同学的年龄都增大1岁 update student set birthday=dateadd(year,-1,birthday) where sno in ( select sno from student where year(getdate())-year(birthday)<18 and sex='男' ) 2)将王英老师的联系电话改为83421236。 update teacher set tel='83421236' where tname='王英' 3)将“数据结构”课程的上课地点改为“D403”。 update teacher_course set classroom='D403' where cno= ( select cno from course where cname='数据结构' ) 4)将“数据库原理”课程成绩在70分以下的加5分。 update student_course set score=score+5 where cno= ( select cno from course where cname='数据库原理') and score <70 5)将所有计算机系学生的“高等数学”课的成绩加10分。 update student_course set score=score+10 where cno=( select cno from course where cname='高等数学') and sno in( select sno from student A,department B where A.dno = B.dno and ) 6)将所有数学系学生的联系电话删除。 update student set tel=null where dno= ( select dno from department where dname='数学系' ) 7)删除没有成绩的选课记录。 delete from student_course where score is null 8)删除学生中姓名有“强”的记录。 delete from student where sname like '%强%' 9)删除数学系女同学的记录。 dname='计算机系' delete from student where sex='女' and dno= ( select dno from department where dname='数学系' ) 10)将2000年9月1日以前入学的学生记录删除。 delete from student where entime<'2000-09-01' 11)删除计算机系所有教师的上课记录。 delete from teacher_course where dno= ( select dno from department where dname='计算机系' ) 12)将平均成绩最高的课程学分加1。 update course set credit=credit+1 where cno in ( select cno from student_course group by cno having avg(score)>=all ( select avg(score) from student_course group by cno ) ) 13)将实验时数为36的课程的成绩加上10% update student_course set score=score*1.1 where cno in ( select cno from course where experiment=36 ) 14)将所有没有上课的教师记录删除。 delete from teacher where tno not in ( select distinct tno from teacher_course ) 8.视图 1)将女教师的基本情况定义为视图。 create view view_female_teacher as select * from teacher where sex='女' 2)将数学系班级号为2008的学生的学号、姓名、性别和专业号 定义为视图。 create view view_math_class2008(sno,sname,sex,pno) as select sno,sname,sex,pno from student where dno=( select dno from department where dname='数学系') and classno = '2' 3)将各个学院的学生人数定义为视图，包括学院编号、人数。 create view view_dept_num(dno,dnum) as select dno,count(*) from student group by dno 4)将平均成绩大于90分的课程定义为视图，包括课程号、 课程名和平均成绩。 create view c_g(cno,cname,avggrade) as select A.cno,cname,avg(score) from student_course A,course B where A.cno=B.cno group by A.cno,cname having avg(score)>90 5)将各个教师所教课程的选课人数定义为视图，包括教师编号、 选课人数。 create view view_teacher_course(tno,snum) as select tno,count(*) from student_course A, (select distinct sno,tno,student.classno,cno from student,teacher_course where student.classno = teacher_course.classno) B where A.cno = B.cno and A.sno = B.sno group by tno,A.cno 6)查询人数超过500人的学院名称。 select dname from department where dno in ( select dno from view_dept_num where dnum>500 ) 7)查询选修王强老师所教课程的人数。 select snum from view_teacher_course where tno= ( select tno from teacher where tname='王强' )