数据库实验三

计算机与信息学院实验报告

系： 专业： 年级： 姓名： 学号： 22 实验室号 计算机号 22 实验时间： 2013年10月29日 指导教师签字： 成绩： 报告退发 (订正 、 重做)

实验名称：（三）数据库的定义实验

一、 实验目的和要求

（1）掌握SQL Server查询分析器的使用方法，加深对SQL和Transact-SQL语言的查询语句的理解。

（2）熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。 （3）熟练掌握数据查询中的分组、统计、计算和组合的操作方法。

二、 实验内容和原理

1. 基本操作实验

用Transact-SQL语句表示下列操作．在学生-课程库中实现其数据查询操作： （1）求数学系学生的学号和姓名。

（2）求选修了高等数学的学生学号、姓名和成绩。

（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

（4）获选修课程C1且成绩在80～90分之间的学生学号、姓名及成绩，并将成绩乘以系数0.8输出。

（5）求数学系或计算机系姓张的学生的信息。 （6）求缺少了成绩的学生的学号和课程号。

（7）求C1课程的成绩高于张三的学生学号和成绩。 （8）求其他系中比计算机系学生年龄都小的学生。 （9）查询选修了全部课程的学生的姓名。

（10）求至少选修了学生“张三”所选修的全部课程的学生学号和姓名。 （11）查询每一门课的间接先行课（即先行课的先行课）。 在图书-图书库中实现其查询操作。

（1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的2倍。

（2）求机械工业出版社出版的各类图书的平均定价，用GROUP BY表示。 （3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。

（4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。

（5）查询计算机类和机械工业出版社出版的图书。

（6）在图书-借阅库中实现其查询操作：将计算机类的书存入永久的计算机图书表中，将借书日期在1999年以前的借阅记录存入临时的超期借阅表。

用Transact-SQL语句表示，并在学生选课库中实现下列数据连接查询操作：

2. 提高操作实验

（1）按表1的格式，建立职工部门库和职工表、部门，并向表中输入数据。

表1 职工和部门表数据

职工表 部门表 职工号 1010 1011 1012 1013 姓名 李勇 刘晨 王敏 张立 性别 男 女 女 男 年龄 20 19 22 21 部门 11 部门号 11 12 13 14 部门名称 生产科 计划科 一车间 科研所 电话 566 578 467 12 13 （2）用Transact-SQL语句表示职工和部门之间的内连接、左外部连接和右外部连接，在职工部门库中实现其数据内连接和各种外查询操作。

三、 实验环境

硬件：计算机

软件：Windows 2000和SQL Server 2000

四、 算法描述及实验步骤 五、 调试过程 六、 实验结果 七、 总结

Sql语句： /*

用Transact-SQL语句表示下列操作．在学生-课程库中实现其数据查询操作： （1）求数学系学生的学号和姓名。

（2）求选修了高等数学的学生学号、姓名和成绩。

（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

（4）获选修课程C1且成绩在～分之间的学生学号、姓名及成绩，并将成绩乘以系数.8输出。

（5）求数学系或计算机系姓张的学生的信息。 （6）求缺少了成绩的学生的学号和课程号。

（7）求C1课程的成绩高于张三的学生学号和成绩。 （8）求其他系中比计算机系学生年龄都小的学生。 （9）查询选修了全部课程的学生的姓名。

（10）求至少选修了学生“张三”所选修的全部课程的学生学号和姓名。 （11）查询每一门课的间接先行课（即先行课的先行课）。 */

insert into student values('0007','abc7',21,'女','数学系');

insert into student values('0008','abc8',24,'男','计算机系'); insert into student values('0009','abc9',22,'男','数学系'); insert into student values('0010','abc10',21,'女','计算机系');

select * from student;

--（1）求数学系学生的学号和姓名。

select sno,sname from student where sdept='数学系'; --（2）求选修了高等数学的学生学号、姓名和成绩。 select * from student; select * from course; select * from sc;

update course set cname='高等数学' where cno='c004';

select student.sno,sname,crade from student,course,sc where course.cno=sc.cno and student.sno=sc.sno and course.cname='高等数学';

--（3）求选修C1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

insert into course values('c1','C++','c001',4,78);

insert into sc values('0008','c001',92); insert into sc values('0008','c1',95); insert into sc values('0010','c001',90); insert into sc values('0010','c1',83);

select sno,crade from sc where cno='c1' order by crade desc,sno asc;

--（4）获选修课程C1且成绩在～分之间的学生学号、姓名及成绩，并将成绩乘以系数.8输出。

select student.sno,sname,crade*0.8 as 成绩from student,sc where student.sno=sc.sno and cno='c1' and (crade between 80 and 90);

--（5）求数学系或计算机系姓张的学生的信息。

insert into student values('1000','张三',22,'男','数学系');

insert into student values('1001','张小小',21,'女','计算机系'); insert into student values('1002','张敏',21,'女','计算机系');

--select * from student where sname like '张%' and sdept='数学系' or sdept='计算机系';

select * from student where sname like '张%' and sdept in ('数学系','计算机系');

--（6）求缺少了成绩的学生的学号和课程号。

insert into sc values('1002','c001',null);

select sno,cno from sc where crade is null;

--（7）求C1课程的成绩高于张三的学生学号和成绩。 insert into sc values('1000','c1',85);

select sno,crade from sc where cno='c1' and crade >(select crade from sc,student where sc.sno=student.sno and sname='张三' and cno='c1') ;

--（8）求其他系中比计算机系学生年龄都小的学生。

select * from sc;

select * from student; select * from course;

insert into student values('2001','王玲',19,'女','生物技术系'); insert into student values('2002','李莉',20,'女','城市规划系'); insert into student values('5001','黄成',20,'男','土木工程系');

select * from student where sage < (select min(sage) from student where sdept='计算机系') and sdept not in('计算机系');

--（9）查询选修了全部课程的学生的姓名。 insert into sc values('1001','c001',83); insert into sc values('1001','c004',93); insert into sc values('1001','c002',97); insert into sc values('1001','c005',80); insert into sc values('1001','c006',77); insert into sc values('1001','c007',81); insert into sc values('1001','c1',89);

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

--（10）求至少选修了学生“张三”所选修的全部课程的学生学号和姓名。

select student.sno,sname from student where sno in ( select distinct sno from sc s1 where not exists ( select * from sc s2 where sno=(select sno from student where sname='张三') and not exists( select * from sc s3 where s3.sno=s1.sno and s3.cno=s2.cno ) ) );

--（11）查询每一门课的间接先行课（即先行课的先行课）。 select * from course;

select first.cno,second.cpno from course first,course second where first.cpno = second.cno; /*

在图书-图书库中实现其查询操作。

（1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的倍。

（2）求机械工业出版社出版的各类图书的平均定价，用GROUP BY表示。 （3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。

（4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册数和总价格。

（5）查询计算机类和机械工业出版社出版的图书。

（6）在图书-借阅库中实现其查询操作：将计算机类的书存入永久的计算机图书表中， 将借书日期在年以前的借阅记录存入临时的超期借阅表。 */

use DB_BookAndReader go

--（1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书平均定价的倍。 select * from book;

insert into book values('1001','语言','清华大学','HF','汉语的由来',77);

insert into book values('1002','科技','华中科技大学','李四','未来的移动终端形式',89); insert into book values('1003','计算机','清华大学','FZX','C++程序设计',57); insert into book values('1004','数学','清华大学','FS','线性代数',45); insert into book values('1005','经济','清华大学','GM','香港地产',119); insert into book values('2003','计算机','清华大学','FZX','oracle',300);

--select max(pricing),type from book group by type;

本文来源：https://www.bwwdw.com/article/46px.html