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'