Sql Server数据库查询语句练习题含答案

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表（学生信息表）

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列上建立聚集索引。

（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表

例子：往学生表插入一条记录

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 

 女'

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 

 李亮'

31)查询选修了数据库系统概论或数据结构的学生的学号和姓名

select distinct student.sno,sname

from student,course,student_course

where student.sno=student_course.sno and

 数据库系统概论','数据结构')

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(

 男'

)

方法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(

 教授'

)

方法2

select *

from department

where dno  not in (

 教授'

) 

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,(

 选课人数

) tmp

where course.cno = tmp.cno

36).查询籍贯是河北省的教师所教的课程信息

方法1

select distinct course.*

from course,teacher,teacher_course

where course.cno = teacher_course.cno and

 河北%'

方法2

select *

from course

where cno in (

     select tno

     from teacher

  河北%'

)

37).查询软件学院学生情况

方法1

select *

from student

where dno in (

 软件学院'

)

方法2

select student.*

from student,department

where department.dno = student.dno and

 软件学院'

38).查询班级人数最多的班的学生情况

方法1

select *

from student

where classno in (

      from student 

      group by classno) 

)

方法2

select student.*,num

from student,(

where student.classno = class_num.classno and

)

方法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 

          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

 张%'

方法2

select cno,cname

from course

where cno in (

     select sno

     from student

  张%'

)

40).查询男学生选修课程的平均分

方法1

select avg(score)

from student,student_course

where student.sno = student_course.sno and

 男'

方法2

select avg(score)

from student_course

where sno in (

 男'

)

41).查询哪些学生选修了授课学时为54的课程

方法1

select *

from student

where sno in (

     select cno

     from course

     where lecture = 54

)

方法2

select  distinct student.*

from student,student_course  B,course

where student.sno = b.sno and

42).查询比软件学院学生年龄都小的其他系学生的信息。

方法1

select *

from student

where birthday > (

     select dno 

     from department

  软件学院'    )

     select dno 

     from department

  软件学院' 其他系

方法2

select student.*

from student,department

where student.dno = department.dno and

 软件学院' and 

 软件学院'

)

方法3

create view software_student

as

select A.*

from student A,department B

where A.dno = b.dno and

 软件学院'

select *

from student A ,software_student B 

where A.sno <> B.sno and

       from software_student

drop view software_student

43).查询比数信学院学院学生年龄大的教育学院学生信息。

方法1

select student.*

from student,department

where student.dno = department.dno and

 教育学院' and 

  数信学院'

)

方法2

select distinct A.*

from (select distinct student.*

  教育学院') A,

     from student,department

     where student.dno = department.dno and

  数信学院') B

where A.birthday < B.birthday

44).查询班级号为1的班的学生c语言程序设计成绩的平均成绩

方法1

select avg(score)

from student_course A

where sno in (

     select cno

     from course

  语言程序设计')

方法2

select avg(score)

from student A,student_course B,course C

where a.sno =b.sno and

 语言程序设计'

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

 计算机导论'

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

 计算机导论'

group by classno

having avg(score)>=all(

  计算机导论'

)

46).查询选修人数最多的课程是哪个老师教的，显示教师号

，教师姓名

select tno,tname

from teacher

where tno 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

w余孝天老师所教学生

       B.tno = C.tno and 

  余孝天') and

    cno in(   --余孝天老师所教课程

     select cno

     from teacher B,teacher_course C

     where  B.tno = C.tno and 

  余孝天')

group by cno

方法2

select A.cno,avg(score)

from student_course A,teacher_course B,teacher C

where tname = '余孝天' and 

       select sno

       from student

       where classno in(

       select distinct classno 

       from teacher_course B,teacher C

    余孝天')

)

group by A.cno

48).查询鲁婵娟老师所教课程的各个班级平均成绩

方法1

select classno,avg(score)

from student_course A ,student B

where  A.sno = B.sno and 

     select cno

     from teacher_course

     where tno in (

      select tno

      from teacher

   鲁婵娟')) and 

     select distinct classno 

     from teacher_course A,teacher B

  鲁婵娟')

方法2

select A.classno,avg(score)

from student A,student_course B,teacher_course C,teacher D

where A.sno = B.sno and

 鲁婵娟' 

group by A.classno 

having A.classno in (

 鲁婵娟' )

49).查询鲁婵娟老师所教课程的学生的成绩

方法1

select *

from student_course

where cno in (

     select distinct cno 

     from teacher_course A,teacher B

  鲁婵娟') and 

     select sno

     from student

     where classno in (

       select distinct classno 

      from teacher_course A,teacher B

   鲁婵娟' )

50).查询有多少人选修了《数据结构》课程的先修课。

方法1

select count(*)

from student_course

where cno = (

 数据结构')

方法2

select count(*)

from student_course A,course B

where A.cno = B.cpno and

 数据结构'

51).查询软件学院教师所教课程信息

select distinct A.*

from course A,teacher_course B

where A.cno = B.cno and

  软件学院'

)

52).查询软件学院教师所教课程的成绩，将结果按

课程号降序排列。

select distinct cno,score 

from student_course

where cno in(

     select tno from teacher

     where dno=(

       select dno from department

  软件学院'))

     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

   软件学院'

order by cno desc

53).查询未授课教师的姓名和系

select tname,dname

from teacher A,department B

where A.dno = B.dno and 

     select distinct tno

     from teacher_course)

54).按职称显示软件学院的教师人数。

select pno,count(tno)

from teacher A,department B

where A.dno = B.dno and

 软件学院'

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 

     select cno

     from course

  数据结构'

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

          B.cno = C.cno )s left outer join (

     ) 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

 法政学院' and

58).查询第2学期哪些教师在公教楼上课。

select tname

from teacher A,teacher_course B

where A.tno = B.tno and

 公教楼%'

59).查询数信学院的学生各科平均成绩，显示课程名、

平均成绩。

select cname,avg(score)

from student A,student_course B,course C ,department D

where A.sno = B.sno and

 数信学院'

group by B.cno,cname

60).查询选修了全部课程的学生的信息。

select *

from student A

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(

61).查询至少选修了鲁婵娟所教的课程的学生信息

select *

from student A

where not exists (

     from teacher 

  鲁婵娟') 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=(

 鲁婵娟') and tc.cno not in(

)

62).查询和张小兵同一个班级的其他学生的信息

select *

from student

where classno = (

 张小兵'

) and

sname <> '张小兵'

63).查询和刘英伟同年出生的学生信息（参考sql日期

操作函数）

select *

from student

where year(birthday) = (

 刘英伟'

)

).查询选修了3门以上课程学生信息

select *

from student

where sno in (

)

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

 软件学院' and

67).查询低于该门课程平均分的学生的成绩信息。

     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 

            dname='计算机系'

  ) 

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)删除数学系女同学的记录。

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='王强'

)