数据库复习总结命令.docx

--1.查询学时在以上的课程名称select cnamefrom Coursewhere chour40--2、查询出计算机系工资收入排前三的老师姓名select top 3 tnamefrom Teacherwhere dept=计算机order by sal+comm desc--3、查询讲授了课程的老师姓名select tnamefrom TC,teacherwhere cno=01001 and tc.tno=Teacher.tno --4查询李英老师的收入总和select sal+comm 李英老师总收入from teacherwhere tname=李英--5、统计教授并且工资在元以上的老师人数select COUNT(tno) 老师人数from Teacher where prof=教授and (sal+comm)4000--6、检索至少选修了两门课程的学生学号和姓名select student.sno,snamefrom Student,SCwhere student.sno=sc.snogroup by student.sno,sname having count(*)=2--7、查询姓刘的学生选修的所有课程名与教师名select course.cname,teacher.tname,snamefrom SC,Student,tc,teacher,coursewhere Course.cno=tc.cno and student.sno=sc.sno and TC.cno=sc.cno and Teacher.tno=tc.tno and sname like 刘%--8、STUDENT表中添加一条记录学号为，姓名是张三，性别为男，年龄岁，所在系是计算机insert into Student values(991110,张三,男,20,计算机)--9、STUDENT表中添加学号是，姓名是李四的记录insert into Student (sno,sname)values(991110,李四)--10、把选修了“计算机基础”课程的学生分数置为分update SCset Score=0where sc.cno=(select course.cno from Course where cname=计算机基础)--11、将STUDENT表中所有信息系的年龄在-20之间的男生存入到NEW表中select * into new from Studentwhere dept=信息and age between 18 and 20--12、把张三的名字修改为张山update Studentset Sname=张山where sname=张三--13、把计算机系李四的系修改为信息系update Studentset Dept=信息where sname=李四--14、将不及格的信息系的学生分数修改为分update SCset score=60where score60 and sc.sno in (select sno from student where dept=信息)--15、老师所上的改为老师上update TC set Tno=000009where tno=000001and cno=01001--16、把张彬的学生信息及其选课情况均删除delete from Student where sname=张彬delete from sc where sno=(select Sno from Student where Sname=张彬)--17、将张彬的程序设计成绩改为分UPDATE SCset Score=99where Cno=(select Cno from Course where Cname=计算机基础) and Sno=(select Sno from Student where Sname=张彬)--18、把计算机基础课程的学时修改为学时UPDATE courseset Chour=32where cname=计算机基础--19、将考试成绩在-60分之间的学生分数加分UPDATE SCset Score=score+10where Score between 50 and 60--20、把李雪老师的性别修改为男，系修改为外语系UPDATE Teacherset Sex=男,Dept=外语where Tname=李雪--1、查询所有数学系学生的信息。select * from Studentwhere Dept=计算机 --2、查询李老师所教的课程号、课程名select tc.c