--查询Student表中的所有记录的Sname、Ssex和Class列。select sname,ssex,class from student;--查询教师所有的单位即不重复的Depart列。selectdistinct depart from teacher;select depart from teacher groupby depart;--查询Student表的所有记录。select*from student;--查询Score表中成绩在60到80之间的所有记录。select*from score where degree between60and80;--查询Score表中成绩为85,86或88的记录。select*from score where degree in(85,86,88);--查询Student表中“95031”班或性别为“女”的同学记录。select*from student where class='95031'or ssex='女';--以Class降序查询Student表的所有记录。select*from student orderby class desc;--以Cno升序、Degree降序查询Score表的所有记录。select*from score orderby cno,degree desc;--查询“95031”班的学生人数。selectcount(1)from student where CLASS='95031';selectcount(1)from student groupby class having class ='95031';--查询Score表中的最高分的学生学号和课程号。select sno,cno from score where degree =(selectmax(degree)from score
);--查询‘3-105’号课程的平均分。selectavg(degree)from score where cno='3-105';--查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select cno,avg(degree)from score
groupby cno
havingcount(*)>=5and cno like'3%'--查询学生的所有选课程分数中最低分大于70,最高分小于90的学生编号Sno列。select sno from score groupby sno havingmin(degree)>70andmax(degree)<90;--查询所有学生的Sname、Cno和Degree列。select sname, cno, degree
from student stu, score sco
where stu.sno = sco.sno ;select sname,cno,degree from student stu join score sco on stu.sno = sco.sno;--查询所有学生的Sno、Cname和Degree列。select sno, cname, degree
from course cou, score sco
where sco.cno= cou.cno;--查询所有学生的Sname、Cname和Degree列。select sname, cname, degree
from student stu, course cou, score sco
where stu.sno = sco.sno
and sco.cno= cou.cno;select sname, cname, degree
from student stu
join score sco
on stu.sno = sco.sno
join course cou
on sco.cno = cou.cno;--查询“95033”班所选课程的平均分selectavg(degree)from student stu, score sco
where stu.sno = sco.sno
and class ='95033';--查询95033班级学生的学生编号select sno from student where class='95033';selectavg(degree)from score
where sno in(select sno from student where class ='95033');select*from STUDENT;select*from TEACHER;select*from COURSE;select*from GRADE;select*from SCORE;--现查询所有同学的Sno、Cno和rank列。select sno,cno,rank from grade g,score s where s.degree between low and upp;--表连接条件--查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。select*from score
where cno ='3-105'and degree >(select degree
from score
where sno ='109'and cno ='3-105');select*from score
where cno ='3-105'and degree >(select degree
from score
where sno ='109'and cno ='3-105');--查询到所有选择3-105课程的学生select*from score where cno ='3-105';--查询到109号学生的3-105课程的分数select degree from score where sno='109'and cno='3-105';--查询score中选学一门以上课程的同学中分数为非所选科目最高分成绩的记录。select*from score s1 where sno in(select sno from score groupby sno havingcount(1)>1)and degree <(所在科目的最高分);select*from score s1
where sno in(select sno from score groupby sno havingcount(1)>1)and degree <(selectmax(degree)from score s2 where s2.cno = s1.cno);--求某一个科目的最高分selectmax(degree)from score s2 where s2.cno = s1.cno;select*from score
where sno in(select sno
from score
groupby sno
havingcount(1)>1)and degree <>(selectmax(degree)from score);--查询score中选学一门以上课程的同学select sno from score groupby sno havingcount(1)>1;--查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。select*from score
where degree >(select degree
from score
where sno ='109'and cno ='3-105');--查询和学号为109的同学同年出生的所有学生的Sno、Sname和Sbirthday列。select sno, sname, sbirthday
from student
where to_char(sbirthday,'yyyy')=--每一个学生的生日的字符串(select to_char(sbirthday,'yyyy')from student where sno ='109');--109学生的生日的字符串select Sbirthday from student where sno ='109';--查询“张旭“教师任课的学生成绩。select s.*from score s, teacher t, course c
where s.cno = c.cno
and t.tno = c.tno
and t.tname ='张旭';--子查询select*from score
where cno =(select cno
from course
where tno =(select tno from teacher where tname ='张旭'));--张旭老师教授的教师编号,根据教师编号找到教授的课程编号select tno from teacher where tname='张旭';select cno from course where tno ='856';select*from score where cno ='6-166';--查询选修某课程的同学人数多于5人的教师姓名。select tname
from teacher t, course c
where t.tno = c.tno
and cno in(select cno from score
groupby cno having(count(1))>5);--分数表 中的数据根据课程进行分组,找到每个课程的所选人数>5人课程编号select cno from score groupby cno havingcount(1)>5;--根据课程编号可以查询教师编号select tno from course where cno in(select cno from score groupby cno havingcount(1)>5);--根据教师编号查询教师姓名select tname
from teacher
where tno in(select tno
from course
where cno in(select cno from score groupby cno havingcount(1)>5));--分数表select*from score;select*from teacher;--查询95033班和95031班全体学生的记录select*from student where class in('95033','95031');--查询存在有85分以上成绩的课程Cno.selectdistinct cno from score where degree >85;--查询出“计算机系“教师所教课程的成绩表。select*from score where cno in(select cno from course c,teacher t where c.tno=t.tno
and t.depart='计算机系');--1)查询计算机系的教师编号select tno from teacher where depart ='计算机系';--2)在课程表中根据教师编号查询课程编号select cno from course where tno in(select tno from teacher where depart ='计算机系');--3)在分数表中根据课程编号查询分数select*from score where cno in(select cno from course where tno in(select tno from teacher where depart ='计算机系'));--查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的任何一个同学的成绩的Cno、Sno和Degree,并按Degree从高到低次序排序。select cno, sno, degree
from score
where cno ='3-105'and degree >(selectmin(degree)from score where cno ='3-245')orderby degree desc;--查询选修编号为“3-105“课程且成绩至少高于自己的选修编号为“3-245”的的Cno、Sno和Degree,并按Degree从高到低次序排序。select cno, sno, degree
from score s1
where cno ='3-105'and degree >(select degree from score s2 where s2.sno = s1.sno and s2.cno ='3-245')orderby degree desc;--