数据库原理及应用第二版第5章数据操作语句课后习题.pdf
第 5 章 数据操作语句-1查询学生选课表中的全部数据。Select*from sc-2查询计算机系的学生的姓名、年龄。Select sname,sage from student where sdept=计算机系-3查询成绩在分之间的学生的学号、课程号和成绩。Select sno,cno,grade from sc where grade between 70 and 80-4查询计算机系年龄在之间且性别为“男”的学生的姓名、年龄。Select sname,sage from student where sdept=计算机系 and sage between 18 and 20 -5查询课程号为“c01”的课程的最高的分数。Select max(grade)from sc where cno=C01 -6.查询计算机系学生的最大年龄和最小年龄。Select max(sage)最大年龄,min(sage)最小年龄 from student Where sdept=计算机系 -7.统计每个系的学生人数。Select sdept,count(*)人数 from student Group by sdept -8.统计每门课程的选课人数和考试最高分。Select cno,count(*)选课人数,max(grade)最高分 from sc Group by cno -9.统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。Select sno,count(*)选课门数,sum(grade)from sc Group by sno order by count(*)asc -10.查询总成绩超过分的学生，要求列出学号、总成绩。Select sno,sum(grade)总成绩 from sc Group by sno having sum(grade)200 -11.查询选修了“c02”号课程的学生的姓名和所在系。Select sname,sdept from student s join sc on s.sno=sc.sno Where cno=C02 -12.查询成绩分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。Select sname,cno,grade From student s join sc on s.sno=sc.sno Where grade 80 Order by grade desc -13.查询哪些学生没有选课，要求列出学号、姓名和所在系。Select sname from student s left join sc on s.sno=sc.sno Where sc.sno is null -14.查询与 VB 在同一学期开设的课程的课程名和开课学期。Select c2.Cname,c2.Semester from Course c1 JOIN Course c2 on c1.Semester=c2.Semester Where ame=VB and c2.Cname!=VB -15查询与李勇年龄相同的学生的姓名、所在系和年龄。*Select s2.Sname,s2.Sdept,s2.Sage From Student s1 JOIN Student s2 on s1.Sage=s2.Sage Where s1.Sname=李勇 and s2.Sname!=李勇 -16查询计算机系年龄最小的名学生的姓名和年龄。Select top 2 sname,sage from student Where sdept=计算机系 Order by sage asc -17.查询 VB 成绩最高的前名学生的姓名、所在系和 VB 成绩，包括并列的情况。Select TOP 2 WITH TIES Sname,Sdept,Grade FROM Student s JOIN SC ON s.Sno=SC.Sno JOIN Course c ON c.Cno=SC.Cno Where Cname=VB ORDER BY Grade desc -18.查询选课人数最多的前名学生的学号和选课门数，包括并列的情况。Select TOP 2 WITH TIES Sno,Count(*)选课门数 FROM SC GROUP BY Sno ORDER BY Count(*)DESC -19.查询学生人数最多的系，列出系名和人数。Select TOP 1 Sdept,Count(*)系人数 FROM Student GROUP BY Sdept ORDER BY Count(*)DESC -20.用子查询实现如下查询:-（）查询选修了“c01”号课程的学生的姓名和所在系。select sname,sdept from student where sno in(select sno from sc where cno=C01)-（）查询数学系成绩分以上的学生的学号、姓名、课程号和成绩。select sno,sname from student where sno in(select sno from sc where grade 80)and sdept=数学系 -（）查询计算机系考试成绩最高的学生的姓名。select sname from student s join sc on s.sno=sc.sno where sdept=计算机系 and grade=(select max(grade)from sc join student s on s.sno=sc.sno where sdept=计算机系)-（）查询数据结构考试成绩最高的学生的姓名、所在系、性别和成绩。select sname,sdept,ssex,grade from student s join sc on s.sno=sc.sno where s.sno in(select top 1 with ties sno from sc join course c on o=o where cname=数据结构 order by grade desc)-21查询没有选修 VB 课程的学生的姓名和所在系。Select sname,sdept from student where sno not in(Select sno from sc join course c on o=o Where cname=vb)-22查询计算机系没有选课的学生的姓名和性别。Select sname,ssex from student where sdept=计算机系 And sno not in(select sno from sc)-23.查询计算机系考试平均成绩最低的学生的姓名以及所选的课程名。*稍复杂*Select sname,cname from student s join sc on s.sno=sc.sno Join course c on o=o Where s.sno in(select top 1 sno from(Select student.sno,avg(grade)as pingjun from sc join student on student.sno=sc.sno Where sdept=计算机系 group by student.sno)as m order by m.pingjun)-24.查询学期中，选课人数最少的课程的课程名、开课学期和学分。Select cname,semester,credit from course Where cno in(select top 1 with ties o from sc Right Join course c on o=o Where semester between 1 and 5 Group by o Order by count(o)asc)25.创建一个新表，表名为 test_t，其结构为：（COL1,COL 2,COL 3），其中：COL1：整型，允许空值。COL2：字符型，长度为 10，不允许空值。COL3：字符型，长度为 10，允许空值。试写出按行插入如下数据的语句（空白处表示空值）。COL1 COL2 COL3 B1 1 B2 C2 2 B3 Create table test_t(COL1 int,COL2 char(10)not null,COL3 char(10)Insert into test_t(COL2)values(B1)Insert into test_t values(1,B2,C2)Insert into test_t values(2,B3,NULL)-26.删除考试成绩低于分的学生的选课记录。Delete from sc where grade 50 -27删除没有人选的课程记录。Delete from course where cno not in(Select cno from course)28删除计算机系 VB 成绩不及格学生的 VB 选课记录。Delete from sc from sc join student s on s.sno=sc.sno Join course c on o=o Where sdept=计算机系 and cname=VB And grade 60 29.删除 VB 考试成绩最低的学生的 VB 选课记录。Delete from sc from sc Where grade in(select min(grade)from sc join course c on o=o Where cname=VB)-30将第学期开设的所有课程的学分增加分。Update course set credit=credit+2 where semester=2 -31将 VB 课程的学分改为分。Update course set credit=3 where cname=VB -32.将计算机系学生的年龄增加岁。Update student set sage=sage+1 where sdept=计算机系 -33将信息系学生的“计算机文化学”课程的考试成绩加分。Update SC set grade=grade+5 From course c join sc on o=o Join student s on s.sno=sc.sno where sdept=信息系 and cname=计算机文化学 -34将选课人数最少的课程的学分降低分。(未考虑没人选的课程)Update course set credit=credit-1 where cno in(Select top 1 with ties cno from sc Group by cno Order by count(*)asc)
