首先创建3个表,具体表结构如下:(因为各个数据库的脚本都不一样,所以在此就不贴出建表的脚本了)
(以下的sql语句可能有多种写法,'--'后为一种写法)
表1:学生表(student):如下图
--select Sno ,Cno from sc where Grade is null
2.查询选修了3号课程的学生的学号级成绩,查询结果按成绩降序排列
--select Sno, Grade from sc where Cno=3 order by Grade desc
3.查询选修了3号课程的学生的学号级成绩,查询结果按成绩升序排列
--select Sno, Grade from sc where Cno=3 order by Grade asc
4.查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄升序排列
--select * from student order by Sdept asc ,Sage asc
5.查询学生总人数
--select count(*) from student
6.查询学生表里的总年龄
--select sum(Sage) from student
7.查询选了课程的学生人数
--select count(distinct Sno) from sc
8.统计选了3门及以上课程的学生人数#
--select count(distinct Sno) from sc group by Sno having count(cno)>=3
9.计算2号课程的学生的平均成绩
--select AVG(Grade) AS avg_grade from sc where cno=2
10.查询1号课程的最高分
--select max(grade)as max_grade from sc where cno=1
11.查各个课程号及相应选课人数#
--select count(Sno)as renshu,cno from sc group by cno
12.查询选修两门及以上课程的学生姓名#
--select Sname from student right join sc on student.sno=sc.sno group by student.Sname having count(sc.cno)>=3
--select Sname from student inner join (select sc.sno from sc group by sc.sno having count(sc.cno)>=3) as studentNo on student.sno = studentNo.sno
13.查询每一门课程的间接选修课#
--select c1.cname ,c2.cname from course as c1, course as c2 where c2.cpno=c1.cno
--select c1.cname ,c2.cname from course as c1 inner join course as c2 on c2.cpno=c1.cno
14.查询与刘晨在同一个系的学生
--select Sname from Student where Sdept=(select Sdept from student where Sname='刘晨') (非相关子查询)
--select Sname from Student stu1 where exists (select sdept from student stu2 where stu2.sname='刘晨' and stu1.sdept = stu2.sdept) (相关子查询)
15.查询每个学生超过他选修课程平均成绩的课程号
--select s1.sno,s1.cno from sc s1 where s1.grade > (select avg(s2.grade) from sc s2 where s1.sno=s2.sno)
16.查询其他系中比计算机科学系(CS)某一学生年龄小的学生姓名和年龄
--select s1.Sname, s1.Sage from student s1 where s1.sage<(select max(s2.Sage) from student s2 where s2.Sdept='CS') and s1.sdept <> 'cs'
--select s1.Sname, s1.Sage from student s1 where s1.sage< any (select s2.Sage from student s2 where s2.Sdept='CS') and s1.sdept <> 'cs'
17.查询没有选修1号课程的学生姓名
--select distinct Sname from student ,sc where sc.cno<>1 and student.sno=sc.sno
--select distinct Sname from student right join sc on student.sno=sc.sno where sc.cno<>1
--select Sname from student where exists (select * from sc where sc.cno<>1 and student.sno=sc.sno)
18.查询选修了全部课程的学生姓名(没有一门课程是他不选修的)
--select Sname from student where not exists (select * from course where not exists (select * from sc where sno=student.sno and cno=course.cno))
19.将相同表结构的表导入数据(向student1里导入student表里的数据)
--insert into student1 (sno) select sno from student
20.删除student1表里的所有数据
--delete from student1
21.查询计算机系(cs)的学生及年龄不大于19岁的学生
--select * from student where Sdept='CS' or Sage<=19
--select * from student where Sdept='CS' union select * from student where Sage<=19
(用union如果前后结果集中有相同的记录它只显示1条,如果union后面加all则是将前后两个结果集直接相加,不区分是否有相同数据)
22.查询计算机系(cs)的学生及年龄不大于19岁的学生的交集
--select * from student where Sdept='CS' and Sage<=19
--select * from student where Sdept='CS' intersect select * from student where Sage<=19 (intersect关键字是求前后结果集的交集)
23.查询计算机系(cs)的学生及年龄不大于19岁的学生的差集
--select * from student where Sdept='CS' and Sage>19
--select * from student where Sdept='CS' except select * from stuc.sno) = 'CS' (相关子查询)
24.将学生200215121的年龄改为22岁
--update student set Sage=22 where Sno=200215121
25.将所有学生的年龄增加1岁
--update student set sage=sage+1
26.查询计算机系全体学生的成绩
--select sc.grade,student.sdept from sc left join student on sc.sno = student.sno where student.sdept = 'cs' (外连接)
--select c.grade from student s,sc c where s.sno=c.sno and s.sdept='cs' (自然连接)
--select sc.grade from sc where sc.sno in (select student.sno from student where student.sdept='cs') (非相关子查询)
--select grade from sc where (select sdept from student where student.sno = sc.sno) = 'CS' (相关子查询)
27.将计算机系全体学生的成绩归零
--update sc set grade=0 where sc.sno in (select student.sno from student where student.sdept='cs') (非相关子查询)
--update sc set grade=100 where (select sdept from student where student.sno = sc.sno) = 'CS' (相关子查询)
28.删除学号为200215121的记录
--delete from student where sno=200215121
29.删除计算机系所有学生的选课记录
--delete from sc where sno in (select sno from student where sdept='cs')
--delete from sc,student where sc.sno=student.sno and student.sdept='cs'
--delete from sc where (select sdept from student where student.sno = sc.sno)='cs'