【SQL Server 上机实验题 】

上机实验题1

创建数据库

create database Library 
on (
  name=a,
  filename='D:\DB\library.mdf',
  size=10MB,
  maxsize=10MB,
  filegrowth=10MB
)
Log on(
   name=la,
  filename='D:\DB\library.ldf',
  size=10MB,
  maxsize=10MB,
  filegrowth=10MB
)

创建四张表

-- depart 表
create table depart(
  班号 char(10)  primary key,
  系名 char(10)
)
-- student 表
-- constraint 为约束  class_num 为约束名  references 来自
create table student(
 学号 int primary key,
 姓名 char(20),
 性别 char(10),
 出生日期 datetime,
 班号 char(10) constraint class_num  foreign key references depart(班号)
)
-- book 表
create table book(
  图书编号 int primary key,
  图书名 char(10),
  作者 char(10),
  定价 int ,
  出版社 char(100)
)
-- borrow 表
create table borrow(
  学号 int constraint fk_study_num foreign key references  student(学号),
  图书编号 int  constraint fk_lb_num foreign key references book(图书编号),
  primary key(学号,图书编号),
  借书日期 datetime
)

插入四张表的数据

-- depart 插入数据
insert into depart values('0501','计算机系');
insert into depart values('0502','计算机系');
insert into depart values('0801','电子工程系');
insert into depart values('0802','电子工程系');

--student 插入数据
insert into student values(1,'张任','男','1995-01-02','0501');
insert into student values(2,'程华','男','1996-01-10','0501');
insert into student values(3,'张丽','女','1995-06-07','0502');
insert into student values(4,'王英','女','1994-12-10','0502');
insert into student values(5,'李静','男','1995-04-05','0502');
insert into student values(10,'许兵','男','1995-08-10','0801');
insert into student values(11,'张功','男','1995-06-02','0801');
insert into student values(12,'李华','男','1994-10-03','0801');
insert into student values(13,'马超','男','1996-02-03','0802');
insert into student values(14,'曾英','女','1994-03-06','0802');

--book表 插入数据
insert into book values(10011,'C程序设计','李洪',24,'清华大学出版社');
insert into book values(10012,'C程序设计','李洪',24,'清华大学出版社');
insert into book values(10013,'C习题解答','李洪',12,'清华大学出版社');
insert into book values(10014,'C习题解答','李洪',12,'清华大学出版社');
insert into book values(10020,'数据结构','徐华',29,'人民邮电出版社');
insert into book values(10021,'数据结构','徐华',29,'清华大学出版社');
insert into book values(10023,'高等数学','王涛',30,'高等教育出版社');
insert into book values(10034,'软件工程','张明',34,'机械工业出版社');
insert into book values(20025,'信息学','张港',35,'清华大学出版社');
insert into book values(20026,'信息学','张港',35,'清华大学出版社');
insert into book values(20042,'电工学','王民',30,'人民邮电出版社');
insert into book values(20056,'操作系统','曾平',26,'清华大学出版社');
insert into book values(20057,'操作系统','曾平',26,'清华大学出版社');
insert into book values(20058,'操作系统','曾平',26,'清华大学出版社');
insert into book values(20067,'数字电路','徐汉',26,'高等教育出版社');
insert into book values(20140,'数据库原理','陈曼',32,'高等教育出版社');
insert into book values(20090,'网络工程','黄军',38,'高等教育出版社');


--borrow 表插入数据
select * from borrow;
insert into borrow values(1,10020,'2013-12-05');
insert into borrow values(1,20025,'2013-11-08');
-- 因为上面book表没有主键为20059，所以这里应该修改为上面表有的符合
insert into borrow values(1,20057,'2014-04-11');
insert into borrow values(2,10011,'2013-10-02');
insert into borrow values(2,10013,'2014-04-03');
insert into borrow values(3,10034,'2014-04-10');
insert into borrow values(3,20058,'2014-04-11');
insert into borrow values(4,10012,'2014-04-06');
insert into borrow values(5,10023,'2014-02-03');
insert into borrow values(10,20056,'2014-02-05');
insert into borrow values(12,20067,'2014-03-06');

上机实验题2

use Library;
-- 上机实验二
-- 1. 查询图书品种的总数目
select count( distinct 图书名) as 图书品种数目 from book;

-- 2. 查询每种图书品种数目
select 图书名,count( 图书名) as 图书数目 from book group by 图书名;

--3. 查询各班人数
select 班号,count(班号) as 人数 from student group by 班号;

--4.查询各系人数
select d.系名,count(s.班号) as 人数 from student s join depart  d on s.班号=d.班号 group by d.系名;

--5.查询借阅图书学生的学号、姓名、书名、借书日期
select s.学号,s.姓名,bo.图书名,b.借书日期 from student s join  borrow b on b.学号=s.学号 join book bo on bo.图书编号=b.图书编号; 

--6.查询借有图书的学生的学号和姓名
select distinct s.学号,s.姓名 from student s join borrow b on s.学号=b.学号;

--7.查询每个学生借书的总数
select s.姓名,count(b.学号) 借书总数 from student s join borrow b on s.学号=b.学号 group by s.姓名;

--8.查找借书超过两本的学生的学号、姓名、和借书册数
select s.学号,s.姓名,count(b.学号) as 借书册数 from student s join borrow b on s.学号=b.学号 
group by s.学号,s.姓名 having count(b.学号)>=2;

--9.查询借阅了 操作系统 一书的学生，输出学号，姓名，班号
select s.学号,s.姓名,s.班号 from student s join borrow b on s.学号=b.学号 
join book bo on bo.图书编号=b.图书编号 where bo.图书名='操作系统' ;

--10.查询每个班的借书总数
select s.班号,count(s.学号) 数目 from student s join  depart d on s.班号=d.班号 join borrow b on s.学号=b.学号 group by s.班号;

--11.若图书编号以前3为数字进行分类，查询每类图书的平均价
-- Cast函数转换类型  Cast(字段名 as 转换的类型 )
select SUBSTRING(CAST(图书编号 as varchar(1000)),1,3) as 图书类别, cast(AVG(定价) as decimal(4,1)) as 平均价
from book group by SUBSTRING(CAST(图书编号 as varchar(1000)),1,3 );

--12.查询平均价高于30的图书类别
select 图书编号,avg(定价) 平均价 from book group by 图书编号 having avg(定价)>=30

--13.查询图书类别的平均价，最高价
select 图书编号,avg(定价) as 平均价  ,max(定价) as 最高价 from book group by 图书编号;

--14.假设借书期限为45天，查询过期未还的图书的编号。书名，和借书人的学号和姓名
-- datediff(datepart,startdate,enddate)  datepart 可以是 day year month 等
select bo.图书编号,bo.图书名,s.学号,s.姓名 from student s join borrow b on s.学号=b.学号 join book bo on bo.图书编号
=b.图书编号 WHERE datediff(day,b.借书日期,'2014-1-20')>45;

--15.查询书名包括‘工程’关键词的图书，输出书号 书名 作者
select 图书编号,图书名,作者 from book  where 图书名 like '%工程%';

--16.查询现有图书中价格最高的图书，输出书名和作者
select 图书名,作者 from book  where 定价=(select max(定价) as 价格最高 from book);

--17.查询所有借阅 ‘C程序设计’ 一书的学生学号，姓名，
--再查询所有借阅‘C程序设计’但是没有借‘C习题解答的学生’的学号和姓名
select s.学号,s.姓名 from book b join borrow bo on b.图书编号=bo.图书编号 join student s on s.学号
=bo.学号  where 图书名='C程序设计' and not exists(select s.学号,s.姓名 from book b join borrow bo on b.图书编号=bo.图书编号 join student s on s.学号
=bo.学号  where 图书名!='C习题解答');

--18.查询所有没有借书的学生的学号和姓名
select 学号,姓名 from student where 学号 not in( select distinct 学号 from borrow);

--19.查询每个系所借图书的总数
select  d.系名,count(b.学号) as 借阅图书数目 from  student s join borrow b on s.学号=b.学号 join depart d on s.班号=d.班号
group by d.系名

--20 查询各出版社的图书总数
select 出版社,count(图书编号) as 图书总数 from book group by 出版社;

--21.查询各出版社的图书占图书总书的百分比(四舍五入到一位数)
-- numeric(5,1) 5位数字1位小数
select 出版社,cast(
round(
count(图书编号)*100.0/(select count(*) from book),
1)
as  numeric(5,1)
) as 百分比
from book group by 出版社;

--22.查询各出版社的图书被借的数目
select b.出版社,count(bor.图书编号) as 借书数目 from 
book b join borrow bor on bor.图书编号=b.图书编号 group by b.出版社;

上机实验题4

--上机实验4
 -- 1.如果经常按照书名查询图书信息，在书名上建立非聚集索引，并输出book表中记
 --看输出的次序是否按书名排序
 use Library;
 select * from book;
 if exists(select name from sysindexes where name='idx_bookname')
 drop index book.idx_bookname
 go
 create  index idx_bookname on book(图书名)
 go

--输出book表
go
select * from book order by 图书名
go

-- 2.在borrow表的学号和图书编号列上建立非聚集索引，并输出该表中的记录，看输出记录的次序如何
select * from borrow ;
if exists(select name from sysindexes where name='idx_idbh')
drop index borrow.idx_idbh
go
create index idx_idbh on borrow(学号,图书编号)
go
go
select * from borrow;
go

--3.建立一个视图，显示’0502‘班学生的借书信息(只要求显示姓名和书名)
use Library;
create view v$_bor_book as
 select s.姓名,bok.图书名 from depart  d join student s on d.班号=s.班号  join borrow bor on s.学号=bor.学号 join
 book bok on bok.图书编号=bor.图书编号;

 --4. 建立一个视图，显示所有学生的借书数目(只要求显示学号、姓名、和数目)
 create view v$_bor_ as 
 select  b.学号,s.姓名,count(b.学号) as 借书数目 from student s join borrow b on s.学号=b.学号 group by b.学号,s.姓名;

 --5.删除前面创建的索引和视图
 --删除索引
  drop index book.idx_bookname
  drop index borrow.idx_idbh
  --删除视图
  drop view v$_bor_
  drop view v$_bor_book

上机实验题5

--实验五
--1.将student表中的性别列设置为只能取男女值
select  * from student;
go
create rule sex as @性别 in('男','女')
go 
exec sp_bindrule 'sex','student.性别';
insert into student values(16,'张仁','男',2020/11/10,'0502') ;  

--2.将student表中的性别列默认改为男
go
create default sex_man as '男'
go
exec sp_bindefault 'sex_man','student.性别';
--3.修改student将班号列作为depart表的班号的外键
alter table student add constraint FK_student_depart foreign key (班号) references depart(班号);
go
--4.将borrow表中的学号和图书编号定义为主键
select * from borrow;
go
alter table borrow  drop constraint  PK__borrow;
alter table borrow add constraint pk_xh_tsbh primary key (学号,图书编号);
--5.删除之前的约束
exec sp_unbindrule 'student.性别'
drop rule sex

exec sp_unbindefault'student.性别'
drop default sex_man

alter table student drop constraint FK_student_depart;
alter table borrow drop constraint pk_xh_tsbh;

上机实验题7

use Library;
--上机实验7
--1. 在Library数据库中创建一个标量值函数Sum(n)，求1+2+...+n之和，并用相关数据进行测试。
GO 
IF EXISTS(SELECT * FROM sysobjects  
WHERE name='Sum' AND type='FN') --如果存在这样的函数则删除之
   DROP FUNCTION Sum 
GO 
CREATE FUNCTION Sum(@n int) --输入参数
RETURNS int 
AS 
BEGIN 
   DECLARE @s int,@i int 
   SET @s=0 
   SET @i=1 
   WHILE @i<=@n 
   BEGIN 
      SET @s=@s+@i 
      SET @i=@i+1 
   END 
   RETURN(@s) 
END 
GO 
PRINT '1+2+、、、+60='+CAST(dbo.Sum(60) AS char(10)) 
GO

---在Library数据库中创建一个内联表值函数nbook，返回指定系的学号、姓名、班号、所借图书名和借书日期，
--并用相关数据进行测试。
USE Library 
GO 
IF EXISTS(SELECT * FROM sysobjects        --如果存在这样的函数则删除之
     WHERE name='nbook' AND (type='IF' OR type ='TF')) 
   DROP FUNCTION nbook 
GO 
CREATE FUNCTION nbook(@dname char(16))--建立函数nbook
RETURNS TABLE  --返回表,没有指定表结构,这就是内联表值函数的特征
AS 
RETURN  
( 
  SELECT s.学号,s.姓名,s.班号,b.图书名,bor.借书日期
  FROM student s,depart sc,borrow bor,book b 
  WHERE s.班号=sc.班号 AND s.学号=bor.学号 AND b.图书编号=bor.图书编号 AND 
sc.系名=@dname 
) 
GO 
SELECT *  
FROM nbook('计算机系') 
ORDER BY 学号
GO
--3.在Library数据库中创建一个多语句表值函数pbook，返回系名和该系所有学生所借图书的平均价格
--并用相关数据进行测试
USE Library 
GO 
IF EXISTS(SELECT * FROM sysobjects        --如果存在这样的函数则删除之
     WHERE name='pbook' AND (type='IF' OR type ='TF')) 
   DROP FUNCTION pbook 
GO 
CREATE FUNCTION pbook()   --建立函数pbook 
  RETURNS @st TABLE 
  (  
系名 char(16), 
平均价格 decimal(4,1) 
  ) 
AS 
  BEGIN 
    INSERT @st --向@st中插入满足条件的记录
      SELECT sc.系名 AS '系名',AVG(b.定价) AS '平均价格' 
      FROM depart sc,student s,book b,borrow bor 
      WHERE s.班号=sc.班号 AND s.学号=bor.学号 AND b.图书编号=bor.图书编号
      GROUP BY sc.系名
    RETURN 
  END 
GO 
SELECT * FROM pbook() 
GO
--4.设计一个存储过程，查询每种图书品种的数目，并用相关数据进行测试。
USE Library 
GO 
--若存在存储过程proc1,则删除之
IF EXISTS(SELECT * FROM sysobjects WHERE name='proc1'  
     AND type='P') 
   DROP PROCEDURE proc1 
GO 
--创建存储过程proc1 
CREATE PROCEDURE proc1 
AS 
  SELECT 图书名 AS '书名',COUNT(*) AS '数目'  
  FROM book 
  GROUP BY 图书名
GO
--5.设计一个存储过程，采用模糊查询方式查找借阅指定书名的学生，输出学号、姓名
--班号和书名，并用相关数据进行测试。
USE Library 
GO 
--若存在存储过程proc2,则删除之
IF EXISTS(SELECT * FROM sysobjects WHERE name='proc2'  
     AND type='P') 
   DROP PROCEDURE proc2 
GO 
--创建存储过程proc2 
CREATE PROCEDURE proc2(@bn char(16)) 
AS 
 DECLARE @pt char(16) 
 SET @pt='%'+RTRIM(@bn)+'%' 
 SELECT s.学号,s.姓名,s.班号,b.图书名
 FROM student s,book b,borrow bor 
 WHERE s.学号=bor.学号 AND b.图书编号=bor.图书编号
     AND b.图书名 LIKE @pt 
ORDER BY s.班号
GO 
EXEC proc2 '数'
GO