数据库上机作业

2011年11月21日上机作业
1利用T-SQL语句，对教学库完成下列查询。

（1）检索所有姓王的学生的姓名和年龄。

（2）检索成绩为空值的学生学号和课程号。

（3）统计有学生选修的课程门数。

（4）统计每门课程的学生选修人数，超过3人的课程才统计，要求输出课程号和选修人数。

（5）检索选修2门以上课程的学生平均成绩(不统计不及格的课程)。

6、利用T-SQL语句，对仓库数据库完成下列查询。

（1）查询青岛海尔生产的商品信息。

（2）查询001号仓库储存的商品的编号和数量。

（3）查询所有商品的种类名称。

|
（4）查询商品的单价在2000到3000之间的商品信息。

（5）查询所有商品的信息，其中单价打八折显示。

（6）查询青岛海尔和青岛海信生产的商品的信息。

（7）查询不是青岛生产的商品的信息。

--第一题
(1)
USE 教学库
SELECT 姓名,年龄
FROM 学生
WHERE 姓名 like '王%'
：
(2)
USE 教学库
SELECT 学生号,课程号
FROM 选课
WHERE 成绩 IS NULL
(3)
USE 教学库
SELECT COUNT (DISTINCT 课程号) AS 课程门数
FROM 选课
（
(4)
USE 教学库
SELECT 课程号, COUNT (课程号)选修人数
FROM 选课
GROUP BY 课程号
HAVING COUNT (课程号)>=3
(5)
USE 教学库
SELECT 学生号,COUNT (学生号)所选课程数,AVG(成绩) AS 平均成绩
FROM 选课
~
WHERE 成绩>=60
GROUP BY 学生号
HAVING COUNT (学生号)>=2
--第六题
(1)
USE 仓库库存
SELECT *
FROM 商品
WHERE 生产商='青岛海尔'
"
(2)
USE 仓库库存
SELECT 商品编号,数量
FROM 库存情况
WHERE 仓库编号='001'
(3)
USE 仓库库存SELECT DISTINCT 商品名称
FROM 商品
、
(4)
USE 仓库库存
SELECT *
FROM 商品
WHERE 单价>=2000 AND 单价<=3000
(5)
USE 仓库库存
SELECT 商品编号,商品名称,单价=单价*,生产商
FROM 商品
~
(6)
USE 仓库库存
SELECT *
FROM 商品
WHERE 生产商='青岛海尔' or 生产商='青岛海信'
(7)
USE 仓库库存
SELECT *
FROM 商品
WHERE 生产商 not like '青岛%'
【
11月24日作业
2011年11月24日上机作业
1．针对“教学库”中的三个表，用T-SQL的查询语句实现下列查询：
（1）求选修C4课程的学生的平均年龄。

（2）求学分为3的每门课程的平均成绩。

（3）检索学生姓名及其所选修课程的课程号和成绩。

（4）求年龄大于女同学平均年龄的男学生姓名和年龄。

（5）检索选修2门及以上课程的学生平均成绩只取前五名。

2．针对“仓库库存数据库”中的三个表，实现下列查询：
（1）查询所有商品的生产厂家的信息（去掉重复行）。

（2）统计平均价格高于3000的各种商品（相同商品名的为一种）的平均价格。

~
（3）查询南京熊猫生产的商品的库存情况。

（4）查询每种商品的库存总量，要求显示商品编号、商品名称和库存总量。

（5）查询青岛生产的商品及库存信息，包括没有库存的商品。

（6）查询各商品的详细信息及每种商品的平均价格。

--第一题
(1)
use 教学库
SELECT AVG(年龄)AS 平均年龄
FROM 学生,选课
WHERE 课程号='C004'
】
(2)
use 教学库
SELECT AVG(成绩)AS 平均成绩
FROM 选课,课程
WHERE 学分=3
(3)
use 教学库
SELECT 学生.姓名,选课.课程号,选课.成绩
FROM 学生, 选课
{
WHERE 学生.学生号=选课.学生号
(4)
use 教学库
SELECT 姓名,年龄
FROM 学生
WHERE 性别='男'AND 年龄>
(SELECT AVG(年龄)
FROM 学生
WHERE 性别='女')
-
(5)
use 教学库
SELECT top 5 学生号,avg(成绩)as 平均成绩
FROM 选课
GROUP BY 学生号
HAVING count(*)>=2
ORDER BY 平均成绩 desc
--第二题
(1)
USE 仓库库存
>
SELECT DISTINCT 生产商
FROM 商品
(2)
USE 仓库库存
SELECT 商品名称,AVG(单价) AS 平均单价
FROM 商品
GROUP BY 商品名称
HAVING AVG(单价)>3000
(3)
…
USE 仓库库存
SELECT 库存情况.*
FROM 库存情况 full join 商品
on 商品.商品编号=库存情况.商品编号
WHERE 生产商='南京熊猫'
(4)
USE 仓库库存
SELECT 商品.商品编号,商品.商品名称,库存情况.数量
FROM 库存情况 full join 商品
on 商品.商品编号=库存情况.商品编号
~
ORDER BY 商品名称
COMPUTE SUM(数量) BY 商品名称
(5)
USE 仓库库存
SELECT 商品.*,库存情况.*
FROM 库存情况 full join 商品
on 商品.商品编号=库存情况.商品编号
WHERE 生产商 LIKE '青岛%'
(6)
【
USE 仓库库存
SELECT 商品编号,商品名称,单价,生产商
FROM 商品
ORDER BY 商品名称
COMPUTE AVG(单价) BY 商品名称
--11月28号作业
（1）检索选修课程C004的学生中成绩最高的学生的学号。

（2）查询没有学生选修的课程的信息。

（3）检索学号比张强同学大，而年龄比他小的学生姓名。

（4）查询每个学生的总学分。

…
（5）查询库存总量最少的仓库的编号。

（6）查询各生产厂家的商品库存总量，并存入”库存总量”表。

（7）删除四川长虹的产品的库存信息。

(1)
use 教学库
select top 1 学生号
from 选课
where 课程号='c004'
order by 成绩 desc
:
(2)
use 教学库
select *
from 课程
where not exists
(select 课程号
from 选课
where 课程.课程号=选课.课程号)
(3)
use 教学库
:
select 姓名
from 学生
where 学生号>all
(select 学生号
from 学生
where 姓名='张强')
intersect
select 姓名
from 学生
where 年龄<all
(select 年龄
,
from 学生
where 姓名='张强')
(4)
use 教学库
select 学生号,SUM(学分) 总学分
FROM 课程,选课
where 课程.课程号=选课.课程号
GROUP BY 学生号
/
(5)
use 仓库库存
select TOP 1 仓库编号
FROM 库存情况
GROUP BY 仓库编号
ORDER BY SUM(数量) ASC (6)
use 仓库库存
select 生产商,SUM(数量) 库存总量
into 库存总量
FROM 库存情况,商品
where 库存情况.商品编号=商品.商品编号
GROUP BY 生产商
(7)
use 仓库库存
select 库存情况
WHERE 商品编号 IN (SELECT 商品编号
--第十章
--7 是可更新视图
--8 不是可更新视图
use 教学库
create view 学生_平均成绩
as
select 学生.学生号,姓名,avg(成绩)as 平均成绩
from 学生,选课
where 学生.学生号=选课.学生号
group by 学生.学生号,姓名FROM 商品
WHERE 商品名称='四川长虹')。