SQL题：sql语句查询(三)

 

第一题：创建如下三个基本表，表结构如下：
borrower：
借书证号　　姓名　　系名　　 班级
03001        李垒    信息系   03-1     

03002        赵      信息     03-1     

03003        钱      计算机   03-2     

03004        孙      计算机   03-3     
......
loans:
借书证号　　图书登记号　　借书日期
03001        t01           2004     

03001        t02           2004     

03001        t03           2004     

03002        t01           2005     

03002        t02           2005     

03003        t03           2006     

03003        t04           2006     

03004        t05           2007     

.......
BOOKS:
索书号　　书名　　　　作者　　　图书登记号　　出版社　　价格
TP311.13　数据结构　　李卫　　 T01　　　　科学　　　19.00
TP311.13　数据结构　　李卫　　 T02　　　　科学　　　19.00
TP.065　　数据结构导论李卫　   T03　　　　北航　　　16.50
TP.1599　数据通信　　　杨志　　 T04　　　　清华　　　28.50

(1)、检索借了3本书以下的学生的借书证号，姓名，系名和借书数量。

---正解如下------------

select *

from borrower B,(select count(*) as 借书数量,借书证号 as 借书证号

                    from loans

                    group by 借书证号

                    having count(*)<3) A

where B.借书证号=A.借书证号

(2)、检索借书和赵垒同学所借书中的任意一本相同的学生的姓名，系名，书名，借书日期。

---正解如下------------

select姓名,系名,图书登记号,借书日期

from borrower bb,loans ll

where bb.借书证号=ll.借书证号 and 姓名<>'李垒'

and图书登记号 in (select 图书登记号

                    from borrower b,loans l

                    where b.借书证号=l.借书证号 and b.姓名='李垒')

(3)、建立信管系学生借书的视图SB，该视图的属性列由借书证号，姓名，班级，图书登记号，书号，出版社和借书日期组成

 

第二题：

现有一个学生选修课程的数据库，其中存放以下三个表：

学生（学号，姓名，性别，年龄，系别）
课程（课程号，课程名，任课教师）；

选修（学号，课程号，分数）；

请用SQL完成以下功能：
（1）、建表，在定义中要求声明：
　A、每个表的主外码。
　B、学生的年龄介于16到30。
　C、学生的姓名和课程名不能为空。
　D、选课成绩要么为空值，要么取0到100的整数。
（2）、插入如下数据：
　学生（101，张三，男，16，数学
　      102，李四，男，18，计算机
　      103，王玲，女，17，中文
　      105，李飞，男，19，计算机
　      109，赵四，女，18，历史
　      110，李平，男，20，化学）
课程（203，操作系统，程羽
　　　279，高等数学，王备
　　　210，现代文学，王林
　　　243，有机化学，沈同
　　　204，数据结构，张青)
选修（101,203,82
　　　105,203,59
　　　102,279,90
　　　101,279,88
　　　105,279,82
　　　110,279,68
　　　109,210,72
　　　103,210,90
　　　110,243,92
　　　101,204,85
　　　105,204,91
　　　102,204,56

101,210,77

101,243,88）
（3）、用SQL语然完成下列查询：
1、列出张三同学选修的所有课程的名称和成绩

------正解如下----------------

select课程名,分数

from学生,选修,课程

where学生.学号=选修.学号and选修.课程号=课程.课程号and姓名='张三'

2、列出所有课程都及格了的同学的名字

------正解如下----------------

select t1.姓名

from学生t1,(select 学号

           from 选修

           where 分数>=60

           group by 学号

           having count(*)=(select count(*) from (select distinct 课程号from课程)A)) t2

where t1.学号=t2.学号

3、列出在选修张青老师所教授课程的学生中，成绩最高的学生姓名和成绩

------正解如下----------------

select top 1 姓名,分数

from学生,选修

where学生.学号=选修.学号 and 学生.学号 in (select 学号 from 课程,选修 where 选修.课程号=课程.课程号 and 任课教师='张青')

order by 分数 desc

（4）、删除所有成绩不及格的选课记录

delete from 选修where分数<60

（5）、将105号同学选修203号课程的成绩改为该门课程的平均成绩

  

update选修

set分数=(select avg(分数) from 选修where课程号='203')

from选修

where学号='105' and 课程号='203'