SQL server练习

1、本题用到下面三个关系表：                            

CARD（CNO，NAME，CLASS）   

说明：card:借书卡 cno:卡号 name:姓名class: 班级

BOOKS(BNO, BNAME, AUTHOR, PRICE, QUANTITY)   

说明：books:图书  bno:书号  bname:书名  author:作者  price:单价  quantity:库存册数

BORROW(CNO, BNO, RDATE)  

说明：borrow:借书记录  cno:借书卡号  bno:书号  rdate: 还书日期

备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

按要求查询数据：

(1)    找出借书超过5本的读者，输出借书卡号及所借图书册数。

select cno,count(bno) as 册数 from BORROW group by cno having count(bno)>5        

(2)查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

select cno as 卡号,bno as 书号,rdate as 还书日期 from borrow where borrow.rdate<GETDATE()      

(2)    查询书名包括“网络”关键词的图书，输出书号、书名、作者。

select bno,bname,author from books where bname like '%网络%'

 (4)查询现有图书中价格最高的图书，输出书名及作者。

select bname,author from books where price=(select MAX(price) from books)     

2、有一个[学生课程]数据库，数据库中包括三个表：

学生表:Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为：Student(Sno,Sname,Ssex,Sage,Sdept)，Sno 为关键字。

课程表:Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)、学分(Ccredit)四个属性组成，记为： Course(Cno,Cname,Cpno,Ccredit) Cno为关键字。

成绩表:SG由学号(Sno)、课程号(Cno)、成绩(Grade)三个属性组成，记为： SG(Sno,Cno,Grade) (SNO, CNO) 为关键字。

用SQL语言实现下列功能：

(1)    建立学生表[Student]，其中学号属性不能为空，并且其值是唯一的。

create table Student

(

Sno char(5) primary key,

Sname char(10),

Ssex  char(2) default '男' check (Ssex in ('男','女')),

Sage int,

Sdept varchar(10)

)

(2)    查询考试成绩有不及格的学生的学号。

select distinct Sno from SG where Grade<60

(3)    将学号为05001学生的年龄改为22岁。

update T_Student set Sage=22 where Sno='05001'

(4)    计算1号课程的学生平均成绩。   

select AVG(Grade) from SG group by Cno having Cno=1 

(5)    创建存储过程[getDetailByName]，通过输入参数学生姓名(如 “张三”)，筛选出该学生的基本信息，对不存在此学生姓名的输入值，打印信息“不存在此学生”。

create proc getDetailByName

@name char(10)

as

if exists(select * from Student where Sname=@name)

  begin

      select * from Student where Sname=@name

  end

else

begin

        print '不存在此学生'

    end

exec getDetailByName 'xxx'

3、现有关系数据库如下：

数据库名：教师数据库

教师表(编号，姓名，性别，民族，职称，身份证号)

课程表(课号，名称)

任课表(ID，教师编号,课号，课时数)

用SQL语言实现下列功能：

(1) 创建上述三张表,   要求使用：主键(教师表.编号，课程表.课号)、外键(任课表.教师编号，任课表.课号)、默认(民族)、非空(民族，姓名)、唯一(身份证号)、检查(性别、课时数),自动编号(ID)

create table 教师表

(

    编号 char(10) primary key,

    姓名 char(10) not null,

    性别 char(2) default '男' check (性别 in ('男','女')),

    民族 char(10) default '汉' not null,

    职称 char(10),

    身份证号 char(20) unique

)

create table 课程表

(

    课号 char(10) primary key,

    名称 char(20) not null

)

create table 任课表

(

    ID int identity(1,1),

    教师编号 char(10) references 教师表(编号),

    课号 char(10) references 课程表(课号),

    课时数 int not null check(课时数 between 0 and 200)

)

 (2) 将下列课程信息添加到课程表的代码

        课号      课程名称

        100001    SQL Server数据库

        100002    数据结构

        100003    VB程序设计

SET IDENTITY_INSERT 课程表 ON

insert into 课程表(课号,名称) values(100001,'SQL Server数据库')

insert into 课程表(课号,名称) values(100002,'数据结构')

insert into 课程表(课号,名称) values(100003,'VB程序设计')

(3)    创建任课表视图，查询教师编号，姓名，课号，课程名称与课时数信息。创建内嵌表值函数，检索所有上“SQL Server数据库”这门课程的老师姓名；

create view V_TeacherInfo

as

select

t.编号,t.姓名,course.课号,course.名称,class.课时数

from 任课表 as class

join 课程表 as course on class.课号=course.课号

join 教师表 as t on class.教师编号=t.编号

create function fn_CourseTeacher

(@course char(200))

returns table

as

return

  (select 课程表.名称,教师表.姓名 from 任课表

   join 教师表 on 任课表.教师编号=教师表.编号

   join 课程表 on 课程表.课号=任课表.课号

   where 课程表.名称=@course

  )

go

select * from dbo.fn_CourseTeacher('SQL Server数据库')

(4) 创建存储过程，计算某教师代课总课时，并将值返回。

执行：计算“郭老师”的总课时。

create proc proc_TeachTime

@name char(10),@time int output

as

select @time=sum(任课表.课时数) from 任课表

join 教师表 on 任课表.教师编号=@name

go

declare @time int

exec proc_TeachTime '郭老师',@time output

print @time