内容发布更新时间 : 2024/11/7 23:58:14星期一 下面是文章的全部内容请认真阅读。

（7）查询各个系的学生的“跳高”项目比赛的平均成绩 (不要求输出比赛项目的计分单位) 。

SELECT szx,AVG(cj) FROM Student, SS, Sports

WHERE Student.xh=SS.xh and SS.xmh=Sports.xmh AND xmm='跳高' GROUP BY szx

（8）统计各个系的总成绩情况，并根据总成绩按降序排序。

SELECT szx,sum(cj) FROM Student, SS, Sports

WHERE Student.xh=SS.xh and SS.xmh=Sports.xmh GROUP BY szx

Order by sum(cj) desc

（9）建立“计算机”系所有男学生的信息视图JSJ_M_Student。

CREATE VIEW JSJ_M_Student AS

SELECT * FROM Student

WHERE szxt='计算机' AND xb='男'

（10）回收用户“李明”对Sports表的查询权限。

REVOKE SELECT ON TABLE Sports FROM 李明

28

综合练习第3套

有一个“学生选课”数据库，数据库中包括三个表，其关系模式分别为： Student(xh,xm,xb,nl,szx) Course(kch,kcm,xxk,xf) SG(xh,kch,cj)

其中：Student是学生表，xh表示学号、xm表示姓名、sb表示性别、nl表示年龄、szx表示所在系。xh为主码。

Course是课程表，kch表示课程号、kcm表示课程名、xxk表示先修课号、xf表示学分。kch为主码。 SG是选课表，xh表示学号，参照学生表的学号xh、kch表示课程号，参照课程表的课程号kch、cj表示成绩。(xh,kch)为主码。 请用SQL语言实现下列功能：

1.建立选课表SG，要求实现主键约束和外键约束，且成绩不能为空。 CREATE TABLE SG( Sno CHAR(5), Cno CHAR(3),

Ccredit tinyint not null, PRIMARY KEY (Sno,Cno),

FOREIGN KEY (Sno) REFERENCES Student(Sno),

CONSTRAINT C3 FOREIGN KEY (Cno) REFERENCES Course(Cno)) 2.查询选修课程的成绩小于60分的人数。 SELECT COUNT(DISTINCT Sno) FROM SG

WHERE Grade < 60

3.查询选修了C3号课程的学生的学号及其成绩，查询结果按分数的降序排列。要求查询结果的标题显示为汉字。

SELECT Sno 学号,Grade 成绩 FROM SG WHERE Cno='C3' ORDER BY Grade DESC

4.查询选修了“数据库应用”课程且成绩在90分以上的学生的姓名和所在系。 SELECT Sname,Sdept FROM Student,SG,Course

WHERE Student.Sno=SG.Sno and SG.Cno=Course.Cno and Cname='数据库应用' AND Grade>=90 或

SELECT Sname,Sdept

FROM Student JOIN SG ON Student.Sno=SG.Sno JOIN Course ON SG.Cno=Course.Cno WHERE Cname='数据库应用' AND Grade>=90

5.向Student表中插入一条记录，学号为“05020”，姓名为“丁莉”，性别为“女”，年龄为“17”，所在系为“计算机系”。 INSERT INTO Student

29

VALUES ('05020', '丁莉', '女', 17, '计算机系') 6.将计算机系全体学生的成绩置零。 UPDATE SG SET Grade=0

WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept='计算机系')

7.创建一个“学生成绩”视图，包括选修了课程的学生的学号、姓名、选修课程的课程号、课程名以及成绩。

CREATE VIEW 学生成绩AS

SELECT Sno, Sname, Course.Cno, Cname, Grade FROM Student, SG, Student

Where Student.Sno=SG.Sno and Course.Cno=SG.Cno 或

CREATE VIEW 学生成绩AS

SELECT Sno, Sname, Course.Cno, Cname, Grade FROM Student JOIN SG ON Student.Sno=SG.Sno JOIN Course ON Course.Cno=SG.Cno

8.为Student表建立一个按学号升序排列的唯一索引Stusno_IDX。 CREATE UNIQUE INDEX Stusno_IDX ON Student(Sno) 9.求各课程的选修人数及平均成绩。 SELECT Cno,COUNT(Sno),AVG(Grage) FROM SG GROUP BY Cno

10.查询选修了课程编号为’14001’和’14002’课程的学生的学号和姓名。 SELECT Student.Sno,Sname FROM Student,SG

WHERE Student.Sno=SG.Sno AND Cno=’14001’ AND Sno IN(SELECT Sno FROM SG WHERE Cno=’14002’)

30

综合练习第4套

设“职工_社团”数据库有3个基本表：

职工：zg (zgh，xm，nl，xb，gz) //职工号、姓名、年龄、性别、工资 社会团体：shtt(bh，mc，fzr，dd)//编号、名称、负责人、地点 参加：cj(zgh，bh，rq)//职工号、编号、日期

1.定义参加表，在语句中要求定义表中的主码和外码约束；(说明：表中属性的类型根据实际情况定义。) create table cj( zgh char(8), bh char(8),

primary key(zgh,bh),

foreign key(zgh) references zg(zgh), foreign key(cno) references shtt(bh)) 2.查询每个社会团体的参加人数 Select count(*) From cj Group by bh

3.检索所有比“王华”年龄大的职工的姓名、年龄和性别 Select xm,nl,xb From zg

Where nl>(select nl from zg where xm='王华') 4.查找参加了歌唱队或篮球队的职工号和姓名 Select zg.zgh,xm From zg,shtt,cj

Where zg.zgh=cj.zgh and shtt.bh=cj.bh and mc in ('歌唱队','篮球队') 5.没有参加任何社会团体的职工信息 Select * From zg

Where zgh not in (select distinct zgh from cj)

6. 将所有参加编号为“10001”的社会团体的职工的工资增加10% Update zg Set gz=1.1*gz

Where zgh in (select zgh from cj where bh='10001') 7.查询年龄最大的职工的职工号和姓名 Select zgh,xm From zg

Where nl=(select max(nl) from zg)

8. 查询各社会团体的编号以及其负责人的姓名 Select zgh,xm from zg,shtt

where zg.zgh=shtt.fzr

9. 删除职工号为’402’的职工参加所有社会团体的记录

31