补充六

1、查询年龄在19至21岁之间的女生的学号，姓名，年龄，按年龄从大到小排列。

    Selectsno,sname,sage from student

    where sagebetween 19 and 21 and ssex='女'

    order by sagedesc

2、查询姓名中有“明”字的学生人数。

    Select count(*)from student

    where snamelike "%明%"

3、查询1001课程没有成绩的学生的学号。

    Select sno fromsc where cno='1001' and grade is null

4、查询JSJ、SX、WL系的学生学号，姓名，结果按系及学号排列。

    Selectsno,sname,sdept from student

    where sdept in( 'JSJ', 'SX', 'WL' )

    order bysdept,sno

5、计算每一门课的总分、平均分，最高分、最低分。

    Selectcno,sum(grade),avg(grade),max(grade),min(grade)

    from sc

    group by cno

6、查询平均分大于90分的男学生学号及平均分。

连接：

    selectsc.sno,avg(grade) from student,sc

    wherestudent.sno=sc.sno and ssex=’男’

    group by sc.sno

    havingavg(grade)>90

嵌套：

    selectsno,avg(grade) from sc

    where sno in (select sno from student where ssex='男')

    group by sno

    havingavg(grade)>90

7、查询选修课程超过2门的学生姓名。

    select snamefrom student,sc

    where student.sno=sc.sno

    group bysc.sno,sname

    havingcount(*)>2

本题也可以用嵌套做

8、查询 JSJ 系的学生选修的课程号。

    Select distinctcno from student,sc

    where  student.sno=sc.sno and sdept='JSJ'

本题也可以用嵌套做

9、查询选修1002课程的学生的学生姓名(用连接和嵌套2种方法)

  连接：Select sname from student,sc

          wherestudent.sno=sc.sno and cno='1002'

    嵌套：Select sname from student where sno in

              (select sno from sc where cno='1002' )

10、查询学生姓名以及他选修课程的课程号及成绩。

    Selectsname,cno,grade from student,sc

    wherestudent.sno=sc.sno

思考本题也可以用嵌套做吗？

3.6　使用SQL查询语句表达下列对教学数据库中三个基本表S、SC、C的查询：

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

SELECT COUNT(DISTINCT C#) FROM SC

（2）求选修C4课程的学生的平均年龄。

　　 　SELECT AVG(AGE)
　　 　FROM S
　　 　WHERE S# IN
　　 　　(SELECT S#
　　 　　FROM SC
　　 　　WHERE C#='C4')

或者

　　 　SELECT AVG(AGE)
　　 　FROM S，SC
　　 　WHERE S.S#=SC.S# AND C#='004'

（3）求LIU老师所授课程的每门课程的学生平均成绩。

　　　SELECT CNAME,AVG(GRADE)
　　　FROM SC ,C
　　　WHERE SC.C#=C.C# ANDTEACHER='LIU'
　　 GROUP BY C# 　　

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

　　 　SELECT DISTINCT C#,COUNT(S#)
　　 　FROM SC
　　 　GROUP BY C#
　　 　HAVING COUNT(S#)>10
　　 　ORDER BY 2 DESC, C# ASC

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

　　 　SELECT X.SNAME
　　 　FROM S AS X, S AS Y
　　 　WHERE Y.SNAME='WANG' AND X.S#>Y.S# AND X.AGE<Y.AGE

（6）检索姓名以WANG打头的所有学生的姓名和年龄。

　　 　SELECT SNAME,AGE
　　 　FROM S
　　 　WHERE SNAME LIKE 'WANG%'

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

　　 　SELECT S#,C#
　　 　FROM SC
　　 　WHERE GRADE IS NULL

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

　　 　SELECT SNAME,AGE
　　 　FROM S AS X
　　 　WHERE　X.SEX='男' AND X.AGE>(SELECT AVG(AGE)FROM S AS Y WHERE Y.SEX='女')

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

　　 　SELECT SNAME,AGE
　　 　FROM S AS X
　　 　WHERE X.SEX='男' AND X.AGE>ALL (SELECT AGE FROM S AS Y WHERE Y.SEX='女')

除法运算

3.7　使用SQL更新语句表达对教学数据库中三个基本表S、SC、C的各个更新操作：

（1）往基本表S中插入一个学生元组（‘S9’，‘WU’，18）。

　　 　INSERT INTO S(S#,SNAME,AGE) VALUES('59','WU',18)

（2）在基本表S中检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表STUDENT（S＃，SANME，SEX）。

　　 　INSERT INTO STUDENT(S#,SNAME,SEX)
　　 　　SELECT S#,SNAME,SEX
　　 　　FROM S WHERE 　NOT EXISTS
　　　　　　(SELECT * FROM SC WHERE
　　　　　　 GRADE<80 AND S.S#=SC.S#)

（3）在基本表SC中删除尚无成绩的选课元组。

　　 　DELETE FROM SC
　　 　　WHERE GRADE IS NULL

（4）把WANG同学的学习选课和成绩全部删去。

　　 　DELETE FROM SC
　　 　　WHERE S# IN
　　 　　　(SELECT S#
　　 　　　FROM S
　　 　　　WHERE SNAME='WANG')

（5）把选修MATHS课不及格的成绩全改为空值。

　　 　UPDATE SC
　　 　SET GRADE=NULL
　　 　WHERE GRADE<60 AND C# IN
　　 　　　(SELECT C#
　　 　　　FROM C
　　 　　　WHERE CNAME='MATHS')

（6）把低于总平均成绩的女同学成绩提高5%。

　　 　UPDATE SC
　　 　SET GRADE=GRADE*1.05
　　 　WHERE GRADE<(SELECT AVG(GRADE) FROM SC) AND S# IN (SELECT S# FROM SWHERE SEX='F')

（7）在基本表SC中修改C4课程的成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%（用两个UPDATE语句实现）。

　　 　UPDATE SC
　　 　　SET GRADE=GRADE*1.05
　　 　　WHERE C#='C4' AND GRADE<=75
　　 　UPDATE SC
　　 　　SET GRADE=GRADE*1.04
　　 　　WHERE C#='C4' AND GRADE>75

3.8　“仓库管理”关系模型有五个关系模式：

　　零件　PART（P＃，PNAME，COLOR，WEIGHT）

　　项目　PROJECT（J＃，JNAME，DATE）

　　供应商　SUPPLIER（S＃，SNAME，SADDR）

　　供应　P＿P（J＃，P＃，TOTOAL）

　　采购　P＿S（P＃，S＃，QUANTITY）

（1）使用SQLDDL语句定义上述五个基本表，并说明主键和外键。

　　 　CREATE TABLE PART
　　 　(P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,
　　 　COLOR CHAR(10),WEIGHT REAL,
　　 　PRIMARY KEY(P#))
　　 　CREATE TABLE PROJECT
　　 　(J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,
　　 　DATE DATE,
　　 　PRIMARY KEY(J#))
　　 　CREATE TABLE SUPLIER
　　 　(S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),
　　 　PRIMARY KEY(S#))
　　 　CREATE TABLE P_P
　　 　(J# CHAR(4),P# CHAR(4),TOTAL INTEGER,
　　 　PRIMARY KEY(J#,P#),
　　 　FOREIGN KEY(J#) REFERENCE PROJECT(J#),
　　 　FOREIGN KEY(P#) REFERENCE PART(P#))
　　 　CREATE TABLE P_S
　　 　(P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,
　　 　PRIMARY KEY(P#,S#),
　　 　FOREIGN KEY(P#) REFERENCE PART(P#),
　　 　FOREIGN KEY(S#) REFERENCE SUPLIER(S#))

（2）试将PROGECT、P＿P、PART三个基本表的自然联接定义为一个视图VIEW1，PART、P＿S、SUPPLIER 三个基本表的自然联接定义为一个视图VIEW2。

　　 　CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)
　　 　　　　　　　AS SELECT PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTAL
　　 　　　　　　　FROM PROJECT,PART,P_P
　　 　　　　　　　WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J#　
　　 　　
　　 　CREATE VIEW VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)
　　 　　　　　　　AS SELECT PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER