[VIP专享]数据库期末复习资料

1、查询全体学生姓名，学号，性别

SELECT 学号,姓名,性别

FROM XSQK

2、查询学生全部信息

SELECT *

FROM XSQK

3、查询选修了课程的学生的学号

SELECT 学号

FROM XS_KC

GROUP BY 学号

4、查询全体学生的姓名(将姓名改为NAME),学号,性别

SELECT DISTINCT 姓名NAME,学号,性别

FROM XSQK1

二、条件查询

1、查询选修课程为101的学生的学号和成绩

SELECT 学号,成绩

FROM XS_CK

WHERE 课程号=101

用课本作业做：

2、查询分数在70分以上的学生选课信息，并按成绩降序排列SELECT *

FROM SC

WHERE GR>70

ORDER BY GR DESC

3、在SC表中按学号分组汇总学生的平均分，并按平均分的降序排列。

SELECT S#,平均分=AVG(GR)

FROM SC

GROUP BY S#

ORDER BY 平均分 DESC

或

SELECT S#,AVG(GR)AS 平均分

FROM SC

GROUP BY S#

ORDER BY 平均分DESC

4、在SC表中查询选修了3门以上课程的学生学号。SELECT S#

FROM SC

GROUP BY S#

HAVING COUNT(*)>3

5、按学号对不及格的成绩记录进行明细汇总。SELECT S#,C#,GR

FROM SC

WHERE GR<60

ORDER BY S#

COMPUTE COUNT(GR)

BY S#

6、分别用子查询和连接查询，求107号课程不及格的学生信息。子查询：

SELECT 学号,姓名,联系电话

FROM XSQK

WHERE 学号IN(

SELECT 学号

FROM XS_KC

WHERE 课程号='107' AND 成绩<60)

连接查询：

SELECT XSQK.学号,姓名,联系电话

FROM XSQK

JOIN XS_KC ON XSQK.学号=XS_KC.学号

WHERE 课程号='107'AND 成绩<60

7、用连接查询在XSQK表中查询住在同一寝室的学生，即其联系电话相同SELECT A.学号,A.姓名,A.联系电话

FROM XSQK A

JOIN XSQK B ON A.联系电话=B.联系电话

WHERE A.学号<>B.学号

8、查询XSQK表中所有系名

SELECT DISTINCT 所在系

FROM XSQK

9、查询有多少同学选修了课程。

SELECT COUNT(*)AS 选修了课程的人数

FROM XSQK

WHERE 学号 IN(

SELECT 学号

FROM XS_KC)

10、查询有多少同学没有选课。

SELECT COUNT(*)AS 没有选修课程的人数

FROM XSQK

WHERE 学号 NOT IN(

SELECT 学号

FROM XS_KC)

11、查询与杨颖同一个系的同学姓名。

SELECT 姓名

FROM XSQK

WHERE 所在系=(

SELECT 所在系

FROM XSQK

WHERE 姓名='杨颖')

12、查询选修了课程的学生的姓名、课程名与成绩。

SELECT 姓名,课程名,成绩

FROM XS_KC,XSQK,KC

WHERE (XS_KC.学号=XSQK.学号) AND (XS_KC.课程号=KC.课程号) AND

XSQK.学号=ANY(

SELECT 学号

FROM XS_KC)

13、统计每门课程的选课人数和最高分。

SELECT 课程名,COUNT(*)AS 选此课人数,MAX(成绩)AS 最高分

FROM XS_KC,KC

WHERE XS_KC.课程号=KC.课程号

GROUP BY 课程名

14、统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。错误程序：

SELECT XSQK.学号,姓名,COUNT(*)AS 选课门数,SUM(成绩)AS 总成绩

FROM XS_CK,XSQK

WHERE XS_CK.学号=XSQK.学号

GROUP BY 姓名

ORDER BY 选课门数 DESC

正确程序：

SELECT 学号,姓名,COUNT(*)AS 选课门数,SUM(成绩)AS 总成绩

FROM XS_CK,XSQK

WHERE XS_CK.学号=XSQK.学号

GROUP BY 姓名

ORDER BY 选课门数 DESC

15、 WHERE子句与HAVING子句有何不同？

答：二者的根本区别在于作用对象不同，WHERE自居作用于基本表或视图，从中选择满足条件的元祖；HAVING子句作用于组，选择面租条件的组，必须用在GROUP BY子句之后，但GROUP BY子句可没有HAVING子句。

建立和使用视图：

1、创建一个简单视图，查询“计算机系”学生的信息。

CREATE VIEW 计算机系学生信息

AS SELECT*

FROM XSQK

WHERE 所在系='计算机'

2、创建一个简单视图，统计每门课程的选课人数和最高分。

CREATE VIEW 每门课人数和最高分

AS SELECT 课程名,COUNT(*)AS 选此课人数,MAX(成绩)AS 最高分

FROM XS_KC,KC

WHERE XS_KC.课程号=KC.课程号

GROUP BY 课程名

3、创建一个复杂视图，查询与“俞奇军”住在同一寝室的学生信息，即其联系电话相同。

CREATE VIEW 与俞奇军同学住同一寝室的学生信息

AS SELECT*

FROM XSQK

WHERE 联系电话=(

SELECT 联系电话

FROM XSQK

WHERE 姓名='俞奇军')

4、创建一个复杂视图，查询选修了课程的同学的姓名，课程名及成绩。CREATE VIEW 选课的同学姓名课程名及成绩

AS SELECT 姓名,课程名,成绩

FROM XS_KC,XSQK,KC

WHERE (XS_KC.学号=XSQK.学号) AND (XS_KC.课程号=KC.课程号) AND

XSQK.学号=ANY(

SELECT 学号

FROM XS_KC)

课本上第135页四、5答案

（1）用SQL的DDL语言创建S表，S#为主键，SN不能为空。

CREATE TABLE S

(S# CHAR(10) CONSTRAINT S_PRIM PRIMARY KEY,

SN CHAR(10) NULL,

AGE INT,

DEPT VARCHAR(20))

（2）创建计算机系学生的视图，该视图的属性列由学号、姓名、课程号和任课教师号组成。

CREATE VIEW C_STUDENT(S#,SN,C#,T#)

AS SELECT S.S#,SN,C.C#,T#

FROM S,SC,C,T

WHERE S.S#=SC.S# AND C.C#=SC.C# AND T.C#=SC.C#

（3）检索计算机系年龄在20岁以上的学生学号。

SELECT S#

FROM S

WHERE(DEPT='计算机')AND(AGE>=20)

(4) 检索姓王的教师所讲课程的课程号及课程名称。

错误程序：

SELECT C.C#,CN

FROM T,C

WHERE(T.C#=C.C#)AND(TN='王%')

正确程序：

SELECT C.C#,CN

FROM T,C

WHERE(T.C#=C.C#)AND(TN LIKE ‘王%’)

错误分析:

此处‘王%’为模糊查询，而‘=’为精确查询，故应该用‘LIKE’语句查询。

（5）检索张三同学课程的成绩，列出SN,C#,GR。

SELECT SN,C#,GR

FROM S,SC

WHERE (S.S#=SC.S#)AND(SN=’张三’)

（6）检索选修总收入超过1000元的教师所讲课程的学生姓名、课程号和成绩。

SELECT SN,C#,GR

FROM S,T,SC

WHERE(S.S#=SC.S#)AND(T.C#=SC.C#)AND(SAL+COMM>=1000)

（7）检索没有选修C1课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列。

SELECT SN,AVG(GR) AS AVGGR

FROM S,SC

WHERE NOT EXISTS

(SELECT*

FROM SC

WHERE S#=S.S# AND C#='C1')

GROUP BY SC.S#,S.SN

HAVING(COUNT(*)=2)

ORDER BY 2 DESC

（8）检索选修和张三同学所选课程中任意一门相同的学生姓名，课程名。

SELECT SN,CN

FROM S,SC,C

WHERE S.S#=SC.S# AND C.C#=SC.C# AND SC.C#=ANY(SELECT C#

FROM S,SC

WHERE S.S#=SC.S# AND SN='张三')

（9）S1同学选修了C3，将此信息插入SC表中。

INSERT INTO SC(S#,C#)

VALUES('S1','C3')

（10）删除S表中没有选修任何课程的学生记录。

DELETE

FROM S

WHERE S.S# NOT IN

(SELECT SC.S#

FROM SC)

（1）建立存书表和销售表；

CREATE TABLE 存书

(书号VARCHAR(10) PRIMARY KEY,

书名VARCHAR(20),

出版社CHAR(20),

版次INT,

出版日期DATETIME,

作者CHAR(10),

书价NUMERIC(4,1),

进价NUMERIC(4,1),

数量INT )

CREATE TABLE 销售

(日期DATETIME,

书号VARCHAR(10) PRIMARY KEY,

数量INT,

金额NUMERIC(4,1) )

(2)

SELECT 书名,数量,进价*数量AS 余额

FROM 存书

(3)

SELECT SUM(数量*金额)AS 总销售额

FROM 销售

（4）

SELECT 日期,书名,存书.数量,销售.数量*金额AS 合计金额

FROM 存书,销售

WHERE 存书.书号=销售.书号

GROUP BY 书名,日期,存书.数量,销售.数量*金额

（5）

SELECT 书名,销售.数量

FROM 存书,销售

WHERE 存书.书号= 销售.书号AND 销售.数量>100 AND 日期+30<( SELECT MAX(日期)

FROM 销售)