1 简单查询操作
此部分查询包括投影、选择条件表达、数据排序、使用临时表等。
对EDUC(shiyan6)数据库实现以下查询:
(1)求信电学院的学生学号和姓名;
(2)求选修了课程的学生学号;
(3)求选修C1 课程的学生学号和成绩,并要求对查询结果按成绩的降序排列,如果成绩相同则按学号的升序排列;
(4)求选修课程C1 且成绩在80-90 之间的学生学号和成绩,并将成绩乘以系数0.75 输出;
(5)求计算机系和数学系的姓张的学生的信息;
(6)求缺少了成绩的学生的学号和课程号。
(1)select sno,sname
from student
where sdept=’IS’;
(2)select sno
from student_course
where cno is not null;
(3)select sno,score
from student_couse
where cno=’101’
order by score desc,sno;
(4)select sno,score*0.75
from student_couse
where cno=’101’and score>80 and score<90;
(5)select *
from student
where sdept=’MS’and sdept=’IS’and sname like ‘张%’;
(6)select sno,cno
frome student_course
where score is null;
2 连接查询操作
对EDUC(shiyan6)数据库实现以下查询:
(1)查询每个学生的情况以及他(她)所选修的课程;
(2)求学生的学号、姓名、选修的课程名及成绩;
(3)求选修C1 课程且成绩在90 分以上的学生学号、姓名及成绩;
(4)查询每一门课的间接先行课。
(1)select student.*,student_course.*
from student,student_course
where student.sno=student_course.sno;
(2)select student.sno,student.sname,https://www.wendangku.net/doc/405303475.html,ame,student_course.score
from student,course,student_course
where
student.sno=student_course.sno,https://www.wendangku.net/doc/405303475.html,o=student_https://www.wendangku.net/doc/405303475.html,o;
(3)select student.sno,student.sname,student_course.score
from student,student_course
where cno=’101’ and score>90 and student.sno=student_course.sno;
(4)select https://www.wendangku.net/doc/405303475.html,o,second.spno
from course first, course second
where first.spno= https://www.wendangku.net/doc/405303475.html,o and second.spno is not null;
3.子查询操作,在数据库EDUC(shiyan6)中实现查询:
(1)求选修了高等数学的学生学号和姓名;
(2)求C1 课程的成绩高于张三的学生学号和成绩;
(3)求其他系中比计算机系某一学生年龄小的学生信息(即求其它系中年龄小于计算机系年龄最大者的学生);
(4)求其他系中比计算机系学生年龄都小的学生信息;
(5)求选修了C2 课程的学生姓名;
(6)求没有选修C2 课程的学生姓名;
(7)查询选修了全部课程的学生的姓名;
(8)求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。
(1)select sno,sname
from student
where sno in
(select sno
from student_course
where cno in
( select cno
from course
where cname=’高等数学’));
(2)select sno,score
from student_course
where cno = ‘101’and score > some
(select score
from student_course
where cno ='101' and sno in
(select sno
from student
where sname=’张三’));
(3)select *
from student
where sdept != ‘CS'’and sage From student where sdept=’CS’); (4)select * from student where sdept != 'CS' and sage from student where sdept=’CS’); (5)select sname from student where sno in (select sno from student_course where cno='102'); (6)select sname from student where sno not in (select sno From student_course where cno='102'); (7)select sname from student where sno in (select sno from student_course where cno is not null); (8)select sno,sname from student where sno in (select sno from student_course where cno in (select cno from student_course where sno= ‘s2’ )); 4.使用子句的查询操作,在数据库EDUC(shiyan6)中实现查询:(1)求学生的总人数。 (2)求选修了课程的学生人数。 (3)求课程的课程号和选修该课程的人数。(4)求选修课超过3 门课的学生学号 (1)select count(sno) from student;