oracle之SQL语句查询

--创建学生表CREATE TABLE STUDENT(SNO NUMBER(15) NOT NULL ENABLE, SNAME VARCHAR2(20 BYTE) NOT NULL ENABLE, SSEX VARCHAR2(20 BYTE) NOT NULL ENABLE, SBIRTHDAY DATE, CLASS VARCHAR2(20 BYTE), CONSTRAINT STUDENT_PK PRIMARY KEY (SNO));--创建学生表课程表CREATE TABLE COURSE (CNO NUMBER(15) NOT NULL ENABLE, CNAME VARCHAR2(20 BYTE) NOT NULL ENABLE, TNO NUMBER(15) NOT NULL ENABLE, CONSTRAINT COURSE_PK PRIMARY KEY (CNO));--创建成绩表CREATE TABLE SCORE(SNO NUMBER(15), CNO NUMBER(15), DEGREES NUMBER(4,1), CONSTRAINT SC_PK PRIMARY KEY (SNO,CNO),FOREIGN KEY (SNO) REFERENCES STUDENT(SNO) ENABLE, FOREIGN KEY (CNO) REFERENCES COURSE(CNO) ENABLE);--创建教师表CREATE TABLE TEACHER(TNO NUMBER(15) NOT NULL ENABLE, TNAME VARCHAR2(20 BYTE) NOT NULL ENABLE, TSEX VARCHAR2(20 BYTE) NOT NULL ENABLE, TBIRTHDAY DATE, PROF VARCHAR2(20 BYTE), DEPART VARCHAR2(20 BYTE), CONSTRAINT TEACHER_PK PRIMARY KEY (TNO));--插入数据Insert into STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) values (105,'匡明','男',to_date('1975-10-02','YYYY-MM-DD'),95031);Insert into STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) values (108,'曾华','男',to_date('1977-10-02','YYYY-MM-DD'),95033);Insert into STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) values (107,'王丽','女',to_date('1976-01-23','YYYY-MM-DD'),95033);Insert into STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) values (101,'李军','男',to_date('1976-02-20','YYYY-MM-DD'),95033);Insert into STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) values (109,'王芳','女',to_date('1975-02-10','YYYY-MM-DD'),95031);Insert into STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) values (103,'陆君','男',to_date('1974-06-03','YYYY-MM-DD'),95031);Insert into COURSE (CNO,CNAME,TNO) values (3105,'计算机导论',825);Insert into COURSE (CNO,CNAME,TNO) values (3245,'操作系统',804);Insert into COURSE (CNO,CNAME,TNO) values (6166,'数字电路',856);Insert into COURSE (CNO,CNAME,TNO) values (9888,'高等数学',831);Insert into SCORE (SNO,CNO,DEGREES) values (103,3245,86);Insert into SCORE (SNO,CNO,DEGREES) values (105,3245,75);Insert into SCORE (SNO,CNO,DEGREES) values (109,3245,68);Insert into SCORE (SNO,CNO,DEGREES) values (103,3105,92);Insert into SCORE (SNO,CNO,DEGREES) values (105,3105,88);Insert into SCORE (SNO,CNO,DEGREES) values (109,3105,76);Insert into SCORE (SNO,CNO,DEGREES) values (101,3105,64);Insert into SCORE (SNO,CNO,DEGREES) values (107,3105,91);Insert into SCORE (SNO,CNO,DEGREES) values (108,3105,78);Insert into SCORE (SNO,CNO,DEGREES) values (101,6166,85);Insert into SCORE (SNO,CNO,DEGREES) values (107,6166,79);Insert into SCORE (SNO,CNO,DEGREES) values (108,6166,81);Insert into TEACHER (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) values (804,'李诚','男',to_date('1974-06-03','YYYY-MM-DD'),'副教授','计算机系');Insert into TEACHER (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) values (856,'张旭','男',to_date('1984-06-03','YYYY-MM-DD'),'讲师','电子工程系');Insert into TEACHER (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) values (825,'王萍','女',to_date('1994-06-03','YYYY-MM-DD'),'助教','计算机系');Insert into TEACHER (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) values (831,'刘冰','女',to_date('1995-06-03','YYYY-MM-DD'),'助教','电子工程系');查询student表中所有的记录的Sname, ssex, class列select Sname, ssex, class from student查询教师所有的单位即不重复的depart列select DISTINCT depart from teacher; --//数据多时效率较低select  depart from teacher group by depart; --//另一种select depart from teacher where tno in   (select min(tno) from teacher where depart in       (select depart from teacher GROUP by depart HAVING count(1)>=1)group by depart);查询student表中所有记录select * from student查询score表中成绩为85 86 88 的记录select * from score where degrees in(85,86,88);select * from score where degrees=85 or degrees=86 or degrees=88查询score表中成绩为60---80 的记录select * from score where degrees BETWEEN 60 and 80select * from score where degrees>=60 and degrees<=80查询student表中‘95031’班女的记录select * from student where class=95031 and ssex='女'以class降序查询student表中所有记录select * from student  order by sno;以Cno升序degree降序查询score表中所有记录select * from score  order by cno asc, degrees desc;查询95031班学生人数select  count(*) from student where class=95033查询sorce表中的最高分的学生学号和课程（子查询或者排序）select sno, cno from score where degrees=(select max(degrees) from score)查询3105号课程的平均分select AVG(degrees) from score where cno=3105查询sorce表汇总至少有5名学生选修的并以3开头的课程的平均分数select cno,AVG(degrees) from score where cno like '3%' GROUP by cno HAVING count(sno)>5查询最低分大于70，最高分小于90的sno列select sno from score where degrees > 70 and degrees <90查询所有学生的sname cno degrees 列select sname,cno, degrees from score join student on score.sno=student.sno查询所有学生的sno cname degrees 列select sno,cname, degrees from score join course on score.cno=course.cno查询所有学生的sname cname degrees 列select cname,sname, degrees from score join student on student.sno=score.sno join course  on score.cno=course.cno查询95033班所选课程的平均分select AVG(degrees) from score where sno in(select sno from student where class=95033)假设使用如下命令建立了一个grade表create table grade(low number(3),upp number(3),rank varchar2(2));insert into grade values(90,100,'A');insert into grade values(80,89,'B');insert into grade values(70,79,'C');insert into grade values(60,69,'D');insert into grade values(0,59,'E');查询所有同学的sno cno rankselect sno,cno,rank from score,grade where degrees between low and upp;select t.* from (select r.sno sno,r.cno cno, r.degrees,  case    when g.low >=90 and g.upp <= 100 and r.degrees >= g.low and r.degrees <= g.upp then 'A'     when g.low >=80 and g.upp <= 89 and r.degrees >= g.low and r.degrees <= g.upp then 'B'     when g.low >=70 and g.upp <= 79 and r.degrees >= g.low and r.degrees <= g.upp then 'C'     when g.low >=60 and g.upp <= 69 and r.degrees >= g.low and r.degrees <= g.upp then 'D'     when g.low >= 0 and g.upp <= 59 and r.degrees >= g.low and r.degrees <= g.upp then 'E'   end grank  from score r,grade g) twhere t.grank is not null;查询选修了3015课程的成绩高于109号的同学的成绩的所有同学的记录。select * from score where cno=3105 and degrees >(select degrees from score where sno=109 and cno=3105);select degrees from score where cno=3105 and degrees >(select max(degrees) from score where sno=109)查询score中选学了多名课程的同学中分数为非最高分成绩的记录select sno, degrees from score s1 where sno in (select sno from score GROUP BY sno HAVING COUNT(*)>1) and (select max(degrees) from score)> degrees;查询1975年以后出生的学生的所学的课程以成绩select cname,sname, degrees from score join student on student.sno=score.sno join course on score.cno=course.cnowheresbirthday > to_date('1975-01-01', 'YYYY-MM-DD')查询和学号为108的同学同年出生的所有学生的sno,sname,和sbirthdayselect sno,sname,sbirthday from student where  to_char(sbirthday,'YYYY')=  to_char((select sbirthday from student where sno=107),'YYYY');查询 张旭 教师任课的学生成绩select degrees from score where cno in (select cno from course where tno in (select tno from teacher where tname='张旭'))查询选修某课程的同学人数多于5人的教师姓名select tname from teacher where tno in (select tno from course where cno in (select cno from score GROUP by cno HAVING count(*)>=5))查询存在95033班和95031班全体学生的记录select * from student where class=95033 or class=95031查询存在有85分以上的成绩的课程cnoselect cno from score where degrees>85查询出 计算机系 教师所教的课程成绩表select degrees from score where cno in (select cno from course where tno in (select tno from teacher where depart='计算机系'))查询计算机系与电子工程系不同职称的教师的tname和profselect tname,prof from teacher where depart='计算机系' and prof not in (SELECT prof from teacher where depart='电子工程系')查询选修编号为3105课程且成绩至少高于选修编号为3245的同学的cno sno degrees并按degrees从高到底次序排序select cno,sno,degrees from score where cno=3105 and degrees >any(select degrees from score where cno=3245)查询选修编号为3105 且成绩高于选修编号为3245课程的同学cno sno degreesselect cno,sno,degrees from score where cno=3105 and degrees >(select max(degrees)from score where cno=3245)查询所有教师和同学的name sex birthdayselect sname,ssex,sbirthday from studentUNIONselect tname,tsex,tbirthday from teacher查询所有女教师和女同学的name  sex birthdayselect  sname,ssex,sbirthday from student where ssex='女'unionselect  tname,tsex,tbirthday from teacher where tsex='女'查询成绩比该课程平均成绩低的同学的成绩表select degrees from score a where degrees <(select avg(degrees) from score b where a.cno=b.cno)查询所有任课教师的tname 和departselect tname,depart from teacher where tno in (select tno from course where cno in(select cno from score GROUP BY cno))select tname,depart from teacher where tno in (select tno from course where cno in(select distinct cno from score))查询所有的未讲课的教师的tname和departselect tname,depart from teacher where tno in (select tno from course where cno not in(select cno from score))查询至少有俩名男生的班号select class from student where ssex='男' group by class HAVING count(*)>=2查询student表中不姓王的同学记录select sname from student where sname not like '王%'查询student表中每个学生的姓名和年龄select sname,to_char(sysdate,'YYYY')-to_char(sbirthday,'YYYY') from student查询student表中最大和最小的sbirthday日期值select max(to_char(sysdate,'YYYY')-to_char(sbirthday,'YYYY')) from studentUNIONselect min(to_char(sysdate,'YYYY')-to_char(sbirthday,'YYYY')) from student以班号和年龄从大到小的顺序查询student表中的全部记录select class,sbirthday from student order by class desc ,sbirthday asc查询男教师及其所上的课程select tname,cname from course teacher where course.tno and teacher tsex='男'select cname from course  where tno in(select tno from teacher where tsex='男')select tname ,cname from course, teacher where course.tno=teacher.tno and teacher.tsex='男'查询最高分同学的sno cno和degrees列select * from score where degrees=(select max(degrees) from score)select * from (select * from score order by degrees) where rownum <= 1 查询和李军同性别的所有同学的snameselect sname from student where ssex=(select ssex from student where sname='李军')查询和李军同学同性别并且同班的同学snameselect sname from student where ssex=(select ssex from student where sname='李军')andclass=(select class from student where sname='李军')查询所有选修课程号位3245和6166的课程的学生学号与姓名select sno,sname from student where sno in(select DISTINCT sno from score where sno  in (select sno from score where cno=3245 and sno in (select sno from score where cno=3105)))in 返回集合=返回单行数据or 并集and 交集查询出没有选修课程号位3245和6166的课程的学生学号与姓名select sno,sname from student where sno in(select DISTINCT sno from score where sno not in (select sno from score where cno=3245 and sno in (select sno from score where cno=3105)))