数据库基础知识点（系列六）

各种查询，SELECT命令

1．针对“teaching”数据库中的三个表，试用T-SQL的查询语句实现下列查询。

（1）查询学生们有哪些专业，只显示专业列，过滤掉重复行。

答：SELECT DISTINCT specialty FROM student

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

答：SELECT COUNT(DISTINCT cno) FROM sc

（3）求选修C004课程的学生的平均年龄。

答：SELECT AVG(sage) FROM student,sc

WHERE student.sno=sc.sno and cno=’C004’

（4）求学分为3的各门课程的学生平均成绩。

答：SELECT course.cno,AVG(score) FROM course,sc

WHERE course.cno=sc.cno and credit=3

GROUP BY course.cno

（5）统计每门课程的学生选修人数，超过2人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

答：SELECT cno,COUNT(*) FROM sc

GROUP BY cno

HAVING COUNT(*) >2

ORDER BY COUNT(*) DESC, cno

（6）检索姓刘的学生的姓名和年龄。

答：SELECT sname,sage FROM student

WHERE sname LIKE ‘刘%’

（7）在sc表中检索成绩为空值的学生学号和课程号。

答：SELECT sno,cno FROM sc WHERE score IS NULL

（8）查询没有学生选修的课的课程号和课程名。

答：SELECT cno, cname FROM course

    WHERE cno NOT IN (SELECT cno FROM sc)

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

答：SELECT sname,sage FROM student WHERE ssex='女'  

and sage >(SELECT  AVG(sage) FROM student WHERE ssex='男')

（10）求年龄大于所有男同学年龄的女学生姓名和年龄。

答：SELECT sname,sage FROM student  WHERE ssex='女'  

and sage > all (SELECT  sage FROM student WHERE ssex='男')

（11）查询所有与刘宏伟同年级、同专业，但比沈艳年龄大的学生的姓名、年龄和性别。

答：SELECT sname,sage,ssex FROM student WHERE sage >  

(SELECT  sage FROM student  WHERE sname='沈艳')

     AND  grade=(SELECT  grade FROM student WHERE sname='刘宏伟')

     AND specialty=(SELECT  specialty FROM student WHERE sname='刘宏伟')

（12）查询选修课程C001的学生中成绩最高的学生的学号。

答：SELECT sno FROM sc WHERE cno='C001'

AND score=(SELECT MAX(score) FROM sc WHERE cno='C001')

（13）查询学生姓名及其所选修课程的课程号和成绩。

答：SELECT sname, cno, score FROM student,sc

    WHERE student.sno=sc.sno

（14）查询选修2门以上课程的学生平均成绩(不及格的课程不参与统计)，并要求按平均成绩的降序排列出来。

答：SELECT sno,AVG(score) FROM sc WHERE score>=60

GROUP BY sno  HAVING COUNT(*)>=2

ORDER BY AVG(score) DESC

（15）查询每个学生的平均成绩，只取前五名。

答：SELECT TOP 5 sno,AVG(score) FROM sc

GROUP BY sno

ORDER BY AVG(score) DESC

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

答：SELECT sno,SUM(credit) FROM sc,course

WHERE sc.cno=course.cno

GROUP BY sno

（17）查询每门课成绩最低的学生的学号和课程号。

SELECT sno,cno FROM sc a WHERE score=

 (SELECT MIN(score) FROM sc WHERE cno=a.cno )

2．利用T-SQL语句，对“inventory”数据库完成下列查询。

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

答：SELECT * FROM goods WHERE producer= '青岛海尔'

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

答：SELECT gno,number FROM invent WHERE stno= '001'

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

答：SELECT DISTINCT gname FROM goods

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

答：SELECT * FROM goods WHERE price BETWEEN 2000 AND 3000

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

答：SELECT gno,gname,price*0.8 单价八折,producer FROM goods

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

答：SELECT * FROM goods WHERE producer='青岛海尔' OR producer='青岛海信'

（7）查询李明管理的仓库存储的商品信息。

答：SELECT * FROM goods WHERE gno IN

(SELECT gno FROM invent WHERE stno IN

(SELECT stno FROM manager WHERE mname='李明'))

（8）查询2号楼101仓库的管理员的姓名和年龄。

答：SELECT mname,YEAR(GETDATE())-YEAR(birthday) age FROM manager

WHERE stno=(SELECT stno FROM store WHERE address='2号楼101')

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

答：SELECT * FROM goods WHERE producer NOT LIKE '青岛%'

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

答：SELECT TOP 1 stno FROM invent

     GROUP BY stno

     ORDER BY SUM(number)  ASC

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

答：SELECT producer,SUM(number) AS 库存总量 INTO 库存总量

FROM goods,invent

WHERE goods.gno=invent.gno

    GROUP BY producer

（12）将张力管理的仓库的电话改为89000008。

答：UPDATE store SET telephone='89000008' WHERE stno=

(SELECT stno FROM manager WHERE mname='张力')

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

答：DELETE invent WHERE gno IN

(SELECT gno FROM goods WHERE producer='四川长虹')