第二三次试验 数据库 简单的select查询.doc

简单SELECT语句 --求计算机系的学生学号和姓名 select sno,sname from department,student where department.spno=student.spno AND spname=计算机 --求选修了课程的学生学号 select sno from student where exists( select * from student_course where student.sno=student_course.sno ) -- 求选修C1 课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列 select student.sno,score from student,student_course,teacher_course where student.sno=student_course.sno and teacher_course.tcid=student_course.tcid and teacher_course.cno=C1 order by score desc,sno asc --求选修课程C1 且成绩在80－90 之间的学生学号和成绩，并将成绩乘以系数0.75 输出 select sno,score*0.75 as realscore from student_course where score80 AND score90 --求计算机系和数学系的姓张的学生的信息 select student.* from student,department where sname like 张%and student.spno=department.spn and department.spname in(计算机,数学) --求缺少了成绩的学生的学号和课程号 select sno,score from student_course,teacher_course where student_course.tcid=teacher_course.tcid and student_course.score is null --查询学生情况和他选修的课程 select student.*,cname from student,student_course where student.sno=student_course.sno --求学生的学号,姓名,选修的课程名及成绩 select student.sno,student.sname,course.cname,student_course.score from student,student_course,course,teacher_course where teacher_course.tcid=student_course.tcid and student.sno=student_course.sno and teacher_course.cno=course.cno --求选修C1 课程且成绩在90 分以上的学生学号、姓名及成绩 select student.sno,student.sname,student_course.score from student,student_course where student.sno=student_course.sno and student_course.tcid=teacher_course.tcid and teacher_course.cno=C1 -- 查询每一门课的间接先行课 select A.cno from teacher_course as A,teacher_course as B where A.tcidB.tcid and A.spno=B.spno and(A.schoolyear*2+A.semester-B.schoolyear*2-B.semester)=2 --求学生的总人数 select count(sno) as cstu from student --求选修了课程的学生人数 select count(sno) as astu from student where exists( select * from student_course where student_course.sno=student.sno ) --求课程的课程号和选修该课程的人数 select course.cno,count(distinct student.sno) as acsno from student,student_course,teacher_course,course w