数据库上机作业

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 年龄

(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