1. 练习Insert, update, delete语句,对表中的数据进行增加,修改,删除
(1)插入数据
①向Student表中插入2行数据,1行为你的信息,另一行自定。
②向Course表中插入2行数据,1行为本门课程的信息,另一行自定。
③向SC表中插入数据,插入你的这门课程的选课信息。
(1)插入数据
intsert into Student(Sno,Sname,Ssex,Sdept) values(1012,'王凌','男','软件'),(2013,'黎明','男','政法');
insert into Course(Cno,Cname,Cpno,Credit) values(54,'大学英语',1,3),(1016,'java技术',2,4);
insert into SC(Sno,Cno,Grade) values(200215121,54,85);
(2)修改数据
①将姓刘的同学删除。
②将’CS’系同学的选课信息中的成绩置0。
(2)修改数据
delete from Student where ename like '刘%';
update SC set Grade=0 where Student.Sdept='CS' Student.Sno=SC.Sno;
(3)删除数据
删除和’ 刘晨’在同一个系的学生的信息。
(3)删除数据
delete from student where Sdept=(select sdept FROM student where Sname='刘晨');
2.基本查询
- 查询每个选课学生的学生姓名、课程号和分数。(2表连接)
- 求学生的学号、姓名、选修课程的课程名及成绩。(3表连接)
- 求选修课程号为1010或课程号为1012的学生姓名和学号。//去重
- 查询每一门课程的间接先行课的课程号。
- 查询与’刘晨’在同一个系学习的学生。
- 查询选修了课程名为’C语言’的学生学号和姓名。
- 查询平均成绩在80分以上的学生学号和平均成绩。
- 查询选修了1门且平均分在80分以下课程的学生的学号。
- 以学生为主体显示学生的信息及其选课的信息。
- 对学生表和选课表做自然连接,并输出结果。
- 输出学号大于’ 刘晨’的学生的姓名和学号。
- 查询选修2号课程且成绩高于“200215121”号学生成绩(2号课程的成绩)的所有学生姓名。(20070001)
2基本查询
(1)select Sname,Sno,Grade from SC,Student where SC.Sno=Student.Sno;
(2)select Student.Sno,Sname,Cname,grade from Student,SC,Course where Student.Sno=SC.Sno and Course.Cno=SC.Cno;(三表连接查询)
(3) select distinct Sno,Sname from Student,SC where Student.Sno=SC.Sno and Cno in(1010,1012);
(4)select Cpno from course sc1 where Cono=(select Cpno from course sc2 where sc1.Cpno=sc2.Cno);
(5)select Sdept from Student where Sdept=(select Sdept from where Sname='刘晨');
(6)select Student .Sno , Sname from SC , Course , Student where SC.Cno=Course.Cno and SC.Sno=Student.Sno and Cname='C语言';
(7)select SC.Sno,AVG(Grade) from SC where AVG(Grade)>80;
(8)select SC.Sno COUNT(*) from SC group by Sno HAVING COUNT(*)=1 and AVG(Grade)<80
(9)select * from Student,SC;
(10)select * from Student natural join SC;
(11)select Sname,Sno from Student where Sno>(select Sno from Student where Sname='刘晨');
(12)select Sname from Student where Sno in(select Sno from sc where Cno=2 and Grade>(select Grade from SC where SC.Sno=200215121);
3.嵌套查询
- 求选修了’MA’的学号和姓名。
- 查询与刘晨在同一个系学习的学生。
- 求选修1号课程的成绩高于刘晨的成绩(指刘晨选修的所有的课程的成绩)的学生学号及成绩。
- 求其他系中比计算机系某一学生年龄小的学生(即年龄小于计算机系年龄最大者的学生)。
- 求其他系中比计算机系学生年龄都小的学生姓名及年龄。
- 求没有选修3号课程的学生姓名。
- 查询选修了全部课程的学生姓名。
- 求至少选修了学号为“200215121”的学生所选修全部课程的学生学号和姓名。
- 求选修课程超过2门的学生的学号和姓名。
3.嵌套查询
(1)select Student.Sno,Sname from Student where Sno in (select Cno from SC where Cname='MA');
(2)select * from Student where Sdept=(select Sdept from Student where Sname='刘晨');
(3)select Sno,Grade from SC where
Grade>(select SUM(Grade) from SC where Sno=(select Sno from Student where Sname='刘晨')) and Cno=1;
(4)select * from Student where Sdept <> '计算机系' and Sage<(select max(Sage) from Student where Sdept='计算机系');
(5)select Sname,Sage from Student where Sage<(select min(Sage) from Student where Sdept='计算机系');
(6)select Sname from Student where Sno not in (select Sno from Sc where Cno=3);
(7)select Sname from student where not exits(select * from Course where not exits(select * from SC where Sno=Studetn.Sno and Cno=Course.cno));
(8)select Sname ,Student.Sno from Student,Course inner join SC ON SC.Sno=Student.Sno inner join Course ON Course.Cno=SC.Cno where Student.Sno=200215121;
(9)select Sno,Sname from where Sno not ingroup by Sno having count(Cno)>2;