数据库笔试题

1. 新建学生-课程数据库的三个表:
学生表:Student(Sno,Sname,Ssex,Sage,Sdept) Sno为主码;
课程表:Course(Cno,Cname,Cpno,Credeit) Cno为主码;
学生选修表:SC(Sno,Cno,Grade) Sno,Cno,为主码;

 

Student

 

学号（Sno）	姓名 Sname	性别 Ssex	年龄 Sage	所在系 Sdept
95001	李勇	男	20	CS
95002	刘晨	女	19	IS
95003	王敏	女	18	MA
95004	张立	男	19	IS

Course:

 

课程号 Cno	课程名 Cname	先行课 Cpno	学分 Credit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	Pascal语言	6	4

SC:

学号 Sno	课程号 Cno	成绩 Grade
95001	1	92
95001	2	85
95001	3	88
95002	2	90
95002	3	80

 

 

数据库生成语句：

 

create database stu_course

use stu_course

create table student(
    sno varchar(32),
    sname varchar(32),
    ssex varchar(32),
    sage int,
    sdept varchar(32)
)

create table Course(
    Cno varchar(32),
    Cname varchar(32),
    Cpno varchar(32),
    credit int
)

create table SC(
    Sno varchar(32),
    Cno varchar(32),
    Grade int
)

 

一:查询表中的列和行

1:查询全体学生的学号与姓名

select sno,sname from student

2:查询全体学生的姓名、学号、所在系。

 

select sno,sname,sdept from student

 

3:查询全体学生的详细记录

 

select * from student

 

4:查询全体学生的姓名及出生年份

 

select sname,DATEPART(yy, GETDATE()) - sage + 1 from student （SQLServer）

5:查询全体学生的姓名,出生年份及所在系,要用小写字母表示系名

 

select sname,DATEPART(yy, GETDATE()) - sage + 1,lower(sdept) from student

6:查询选修了课程的学生学号
select sno,cno from sc

7:查询选修了课程的学生姓名

 

select distinct sname from student,sc where student.sno=sc.sno

二:条件查询:

 

常用的查询条件

查询条件谓词
比较=,<,>,>=,<=,!=,<>,!>,!<;
not+上述比较运算符
确定范围Between and,Not between And,
确定集合IN,not IN
字符匹配Like,Not Like
空值IsNull,ISNOTNULL
多重条件AND,OR

1:查询计算机系全体学生的姓名

 

select sname from student where sdept=”CS”

 

2:查询所有年龄在20岁以下的学生姓名及其年龄

 

select sname,sage from student where sage<20

 

3:查询考试成绩有不及格的学生的学号

select sno from sc where grade<60

4:查询年龄在20到23间的学生的姓名,系别及年龄

select sname,sdept,sage from student where sage between 20 and 23

5: 查询年龄不在20到23间的学生的姓名,系别及年龄

select sname,sdept,sage from student where sage not between 20 and 23

6:查询信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别

select sname,ssex from student where sdept in("IS","MA","CS")

7:查询不是信息系(IS),数学系(MA)和计算机系(CS)学生的姓名和性别

select sname,ssex from student where sdept not in("IS","MA","CS")

8:查询学号为”95001”的学生详细情况

select * from student where sno=95001

9:查询所有姓刘的学生的姓名,学号和性别(where name like ‘刘%’)

select sname,sno,ssex from student where sname like '刘%'

 

10:查询姓”欧阳”且命名为三个汉字的学生的姓名

 

select sname from student where sname like '欧阳_'

11:查询名字中第2个字为”阳”字的学生姓名和学号(where sname like '_阳%')

select sname,sno from student where sname like '_阳%'

12:查询所有不姓刘的学生姓名

 

select sname from student where sname not like '刘%'

 

13:查询DB_Design课程的课程号和学分(where cname like 'Db\_Design' Escape'\')

select cno,gredit from course where cname like 'Db\_Design' Escape '\'

 

14:查询以”DB_”开头,且倒数第3个字符为i的课程的详细情况(where cname like ‘DB\_%i__’escape’\’)
‘DB\_%i__’escape’\’)

 

select cno,gredit from course where cname like ‘Db\_%i__’escape’\’

15:查询缺少成绩的学生的学号和相应的课程号(where grade is not null)

 

select sno,cno from sc where grade is null

 

16:查询有成绩的学生学号和课程号

select sno,cno from sc where grade is not null

17:查询计算机系年龄在20岁以下的学生姓名

select sname from student where sdept=”CS” and sage<20

 

18:查询选修了3号课程的学生的学号及其成绩,分数降序排列

 

select student.sno,grade from student,sc

where student.sno=sc.sno and sc.cno=3 order by grade desc

 

19:查询全体学生情况,结果按所在系的号升序排列,同一系中的学生按年龄降序

 

select * from student order by sdept,sage desc

 

三:使用集函数

count,sum,avg,max,min

 

1:查询学生的总人数

 

select count(sno) from student

2:查询选修了课程的学生人数(select count(distinct sno))

 

select count(distinct sno) from SC

3:计算1号课程的学生平均成绩

 

select avg(grade) from SC where cno='1'

4:查询选修1号课程的学生最高分数

 

select max(grade) from SC where cno='1'

5:求各个课程号及相应的选课人数

 

select cno,count (sno) from sc group by cno

6:查询选修了3门以上的课程的学生学号

 

select sno
from sc
group by sno

having count(*)>3

 

四:连接查询:

 

<1>等值与非等值的连接查询
在连接查询中用来连接两个有的条件称为连接条件或连接谓词,,当连接运算符号为”=”时,称为等值连接,使用如,=,<,>,<=,>=,!=连接时称非等值连接

 

1:查询每个学生及其选修课程的情况

 

select student.*,sc.*
from student,sc
where student.sno=sc.sno

 

<2>自身连接

 

连接操作在同一个表中进行连接查询

 

2:查询每一门课的间接先修课(即先修课的先修课)

select first .cno,second.cpno
from course first ,course second
where first.cpno=second.cno

 

五：复合条件连接

 

1:查询选修2号课程且成绩在90分以上的所有学生。

 

Select student,sname
form student, sc
Where student.sno=sc.sno And
Sc.cno=’2’ and sc.grade>90

 

六：嵌套查询

 

1：带有谓词in的子查询

 

<1>查询与“刘晨”在同一个系学习的学生

 

select sno,sname,sdept
from student
where sdept in(
select sdept
from student
where sname='刘晨')

或：

 

select s1.sname,s1.sdept
from student s1,student s2
where s1.dept=s2.dept and s2.name='刘晨'

<2>查询选修了课程名为“信息系统”的学生学号和姓名

 

select sno,sname
from student
where sno in
( select sno
from sc
  where cno in
     (select cno
        from course
          where cname='信息系统')
或：

 

select sno,sname
   from student,sc,course
  where student.sno=sc.sno and
       sc.cno=course.cno and
       course.cname='信息系统')

2:带有Any 或all谓词的子查询

<1>查询其他系中比信息系中某一学生年龄小的学生姓名和年龄

 

select sname, sage
from student
where sage <any(select  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’

 

<2> 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄

 

select sname, sage
from student
where sage<all
        (select sage
        from student
       where sdept=’is’)
    and sdept<>’is’

 

3 带有Exitst谓词的子查询
<1>查询所有选修了1号课程的学生姓名

select sname
from student
where exists
        (select *
         from sc
         where sno=student.sno and cno='1')

<2>查询没有选修1号课程的学生姓名
select sname
form student
where not exists
          (select *
            form sc
             where sno=stuedent.sno and cno=’1’)

 

<2>查询选修所有全部课程的学生姓名

 

select sname
from student
where not exists
        (select *
          from course
            where not exists
                  (select *
                     from sc
                       where sno=student.sno
              and cno=course.cno)

 

<3>查询只选修了学生95002选修的全部课程的一部分的学生号码

 

select distinct sno
from sc scx
where not exists
       ( select *
          from sc scy
           where scy.sno=’95002’ and
              not exists
                  ( select *
                     from sc scz
                      where scz.sno=scx.sno and
                         scz.cno=scy.cno)

       )

二：

 

 

题一：表Ａ数据如下：

FYear FNum
2006  1
2006  2
2006  3
2007  4
2007  5
2007  6

按如下格式显示：

年度  2006  2007
汇总　6     15

方案一：
create table 表名(FID varchar(10), Field1 varchar(100))
go

insert into 表名 select 1,'A'
insert into 表名 select 1,'B'
insert into 表名 select 1,'C'
insert into 表名 select 2,'D'
insert into 表名 select 2,'E'
insert into 表名 select 2,'F'
go

--创建一个合并的函数
create function f_merge(@name varchar(100))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(Field1 as varchar(100)) from 表名 where FID = @name
  set @str = stuff(@str , 1,1,'')
  return(@str)
End
go

--select * from 表名

--调用自定义函数得到结果：
select FID ,dbo.f_merge(FID) as tel from 表名 group by FID

drop table 表名
drop function f_merge


方案二：
select '汇总' as  年度
,[2006],[2007]
from
(select fyear,fnum from T)as sourceTable
pivot
(
sum(fnum)
for fyear in ([2006],[2007])
)
as pivotTable

回头发现可以用SQL2005 pivot 的方法很简单

题二：
表A数据如下：
FID  Field1
1    A
1    B
1    C
2    D
2    E
2    F
要求按如下格式显示：
FID  Field1
1    A,B,C
2    D,E,F 
如何做到?

create table 表名(FID varchar(10), Field1 varchar(100))
go

insert into 表名 select 1,'A'
insert into 表名 select 1,'B'
insert into 表名 select 1,'C'
insert into 表名 select 2,'D'
insert into 表名 select 2,'E'
insert into 表名 select 2,'F'
go

--创建一个合并的函数
create function f_merge(@name varchar(100))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(Field1 as varchar(100)) from 表名 where FID = @name
  set @str = stuff(@str , 1,1,'')
  return(@str)
End
go

--select * from 表名

--调用自定义函数得到结果：
select FID ,dbo.f_merge(FID) as tel from 表名 group by FID

drop table 表名
drop function f_merge