《数据库系统概论》课程学习(20)——小测验_综合

小测验1

1、新建Teacher表，要求如下：

Tno	Tname	Tsex	Tage	Tdept
char(4)	char(8）	char(2)	smallint	char(20)
主码	非空	{男、女}	大于20

create table teacher
(tno char(4) primary key,tname char(8) not null,tsex char(2) constraint ck_tsex check(tsex in ('男','女')),tage smallint constraint ck_tage check(tage > 20),tdept char(20))

2、在Teacher表中针对Tdept列建立索引；

create index idx_tdept on teacher(tdept)

3、将（1，Tom，男，34，CS）插入到Teacher表。

insert into teacher values('1', 'Tom', '男', 34, 'CS')

4、将1号教师的年龄改为27。

update teacher set tage = 27 where tno = '1'

5、建立CS系教师的视图。

create view cs_teacher(tno, tname, tsex, tage)
as
select tno, tname, tsex, tage
from teacher
where tdept = 'cs'

6、查询CS系中年龄大于40的教师姓名和性别。

select tname, tsex
from cs_teacher
where tage > 40

7、查询CS系教师总人数。

select count(*)
from cs_teacher

8、查询哪些系的教师总人数不到10人。

select tdept
from teacher
group by tdept
having count(*) < 10

9、在Teacher表中删除CS系全体教师的数据。

delete from teacher where tdept = 'cs'

10、取消Teacher表中对教师年龄的约束。

alter table teacher drop constraint ck_tage

小测验2

student(sno, sname, ssex, sage, sdept)

course(cno, cname, cpno, credit)

sc(sno, cno, grade)

1、若学校规定修满190学分即可毕业，查询哪些学生符合了毕业条件，要求输出学号、姓名和所在系，且按所在系排序，同一个系的按学号排序。

select sno,sname,sdept
from student
where 190 <= (select sum(credit)from course,scwhere student.sno=sc.sno and course.cno=sc.cno and grade>=60)
order by sdept, sno

2、求年龄最小的学生姓名及其年龄。

select sname,sage
from student
where sage = (select min(sage) from student)

select top 1 with ties sname,sage
from student
order by sage

3、哪个系的学生成绩平均分最低。

select top 1 with ties sdept, avg(grade) Davg
from student, sc
where student.sno = sc.sno
group by sdept
order by Davg

select sdept, Davg
from (select sdept, avg(grade) Davgfrom student, scwhere student.sno = sc.snogroup by sdept) T
where Davg =(select top 1 avg(grade) Davgfrom student, scwhere student.sno = sc.snogroup by sdeptorder by Davg)

select sdept, avg(grade) Davg
into #tmpT
from student, sc
where student.sno = sc.sno
group by sdeptselect *
from #tmpT
where Davg =(select min(Davg) from #tmpT)drop table #tmpT

4、哪个系的学生不及格和缺考的人数最多。

select top 1 sdept, count(*) Dcou
from student, sc
where student.sno = sc.sno and (grade < 60 or grade is null)
group by sdept
order by Dcou desc

以上是同一个学生多门课不及格时计为多人次的结果。

select top 1 with ties sdept, count(distinct sc.sno) Dcou
from student, sc
where student.sno = sc.sno and (grade < 60 or grade is null)
group by sdept
order by Dcou desc

以上是同一个学生多门课不及格时人数计为1次的结果。

5、选修数据库课程的哪个系平均分最高。

select top 1 with ties sdept, avg(grade) Davg
from student, sc, course
where student.sno = sc.sno and course.cno = sc.cno and cname = '数据库'
group by sdept
order by Davg desc

6、数据库课程的先修课都有哪些。

create  function  CpCourse(@c_name char(20))
returns  @CpTable table(cno char(4) primary key,Cpname char(20))
as begindeclare @tmpCno char(4),  @tmpCpno char(4),  @tmpCname char(20)select @tmpCpno = cpno from course where cname = @c_namewhile @tmpCpno is not nullbeginselect @tmpCno = cno, @tmpCname = cname, @tmpCpno = cpnofrom course where cno = @tmpCpnoinsert into @CpTable values(@tmpCno,  @tmpCname)endreturn
endselect * from dbo.CpCourse('数据库')

7、哪些学生选修了数据库，但至少有一门数据库先修课没选。

select sname
from student
where sno in(select sc.snofrom sc, coursewhere sc.cno = course.cno and cname = '数据库'exceptselect sc.snofrom sc, dbo.CpCourse('数据库')  tmpCpCoursewhere sc.cno = tmpCpCourse.cnogroup by sc.snohaving count(*) = (select count(*) from dbo.CpCourse('数据库')))