表结构:
- drop table student;
- drop table course;
- drop table score;
- drop table teacher;
- CREATE TABLE STUDENT
- (SNO VARCHAR(30) NOT NULL,
- SNAME VARCHAR(40) NOT NULL,
- SSEX VARCHAR(20) NOT NULL,
- SBIRTHDAY DATE,
- CLASS NUMBER NOT NULL);
- CREATE TABLE COURSE
- (CNO VARCHAR(50) NOT NULL,
- CNAME VARCHAR(100) NOT NULL,
- TNO VARCHAR(100) NOT NULL);
- CREATE TABLE SCORE
- (SNO VARCHAR(30) NOT NULL,
- CNO VARCHAR(50) NOT NULL,
- DEGREE NUMBER NOT NULL);
- CREATE TABLE TEACHER
- (TNO VARCHAR(30) NOT NULL,
- TNAME VARCHAR(40) NOT NULL,
- TSEX VARCHAR(20) NOT NULL,
- TBIRTHDAY DATE NOT NULL,
- PROF VARCHAR(60),
- DEPART VARCHAR(100) NOT NULL);
插入数据:
- INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' ,'男' ,to_date('1977-09-01','yyyy-mm-dd'),95033);
- 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 (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 ('3-105' ,'计算机导论',825);
- INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
- INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
- INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
- INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);
- INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男',to_date('1958-12-02','yyyy-mm-dd'),'副教授','计算机系');
- INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男',to_date('1969-03-12','yyyy-mm-dd'),'讲师','电子工程系');
- INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女',to_date('1972-05-05','yyyy-mm-dd'),'助教','计算机系');
- INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女',to_date('1977-08-14','yyyy-mm-dd'),'助教','电子工程系');
- commit;
题目:
--1, 查询 Student 表中的所有记录的 Sname,Ssex 和 Class 列.
SELECT s.sname,s.ssex,s.class FROM student s ;
--2, 查询教师所有的单位即不重复的 Depart 列.
SELECT DISTINCT(t.depart) FROM teacher t;
--3, 查询 Student 表的所有记录.
SELECT s.* FROM student s;
--4, 查询 Score 表中成绩在 60 到 80 之间的所有记录.
SELECT s.* FROM score s WHERE s.degree BETWEEN 60 AND 80;
--5, 查询 Score 表中成绩为 85,86 或 88 的记录.,
SELECT s.* FROM score s WHERE s.degree IN(85,86,88);
--6, 查询 Student 表中 "95031" 班或性别为 "女" 的同学记录.
SELECT s.* FROM student s WHERE s.class='95031' OR s.ssex='女';
--7, 以 Class 降序查询 Student 表的所有记录.
SELECT s.* FROM student s ORDER BY s.class DESC;
--8, 以 Cno 升序, Degree 降序查询 Score 表的所有记录.
SELECT s.* FROM score s ORDER BY s.cno ASC ,s.degree DESC;
--9, 查询 "95031" 班的学生人数.
SELECT COUNT(1) 学生人数 FROM student s WHERE s.class='95031'
--10, 查询 Score 表中的最高分的学生学号和课程号.
SELECT s.sno,s.cno FROM score s WHERE s.degree=(SELECT MAX(s.degree) FROM score s);
--11, 查询'3-105'号课程的平均分.
SELECT AVG(s.degree) FROM score s WHERE s.cno='3-105';
--12, 查询 Score 表中至少有 5 名学生选修的并以 3 开头的课程的平均分数.
SELECT s.cno,AVG(s.degree) FROM score s WHERE s.cno LIKE('3%') GROUP BY s.cno HAVING COUNT(1)>=5;
--13, 查询最低分大于 70, 最高分小于 90 的 Sno 列.
SELECT s.sno FROM score s WHERE s.degree BETWEEN 70 AND 90;
--14, 查询所有学生的 Sname,Cno 和 Degree 列.
SELECT st.sname,sc.cno,sc.degree FROM student st ,score sc WHERE st.sno=sc.sno;
--15, 查询所有学生的 Sno,Cname 和 Degree 列.
SELECT sc.sno,c.cname,sc.degree FROM score sc,course c WHERE sc.cno=c.cno;
--16, 查询所有学生的 Sname,Cname 和 Degree 列.
SELECT (SELECT st.sname FROM student st WHERE st.sno=sc.sno),c.cname,sc.degree FROM score sc,course c WHERE sc.cno=c.cno;
-- 方法 2
SELECT st.sname,c.cname,sc.degree FROM student st,score sc,course c WHERE st.sno=sc.sno AND c.cno=sc.cno;
--17, 查询 "95033" 班所选课程的平均分.
SELECT st.class,AVG(sc.degree) FROM student st,score sc WHERE st.sno=sc.sno AND st.class='95033' GROUP BY st.class
--18, 假设使用如下命令建立了一个 grade 表:
- create table grade
- (low number,
- upp number,
- rank char(1));
- 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');
- commit;
-- 现查询所有同学的 Sno,Cno 和 rank 列.
SELECT s.sno,s.cno,g.rank FROM score s,GRADE G WHERE s.degree>=g.low AND s.degree<=g.upp;
--19, 查询选修 "3-105" 课程的成绩高于 "109" 号同学成绩的所有同学的记录.
- SELECT st.* FROM student st,score sc WHERE st.sno=sc.sno AND sc.cno='3-105'
- AND sc.degree>(SELECT sc.degree FROM score sc WHERE sc.sno='109' AND sc.cno='3-105' )
--20, 查询 score 中选学一门以上课程的同学中分数为非最高分成绩的记录.(所有分数)
SELECT s.* FROM score s WHERE s.degree NOT IN (SELECT MAX(s.degree) FROM score s GROUP BY s.cno)
AND s.sno IN (SELECT s.sno FROM score s GROUP BY s.sno HAVING COUNT(1)>1)
--21, 查询成绩高于学号为 "109", 课程号为 "3-105" 的成绩的所有记录.
SELECT * FROM score s WHERE s.degree>(SELECT s.degree FROM score s WHERE s.sno='109' AND s.cno='3-105')
--22, 查询和学号为 108 的同学同年出生的所有学生的 Sno,Sname 和 Sbirthday 列.
- SELECT s.sno,s.sname,s.sbirthday FROM student s WHERE to_char(s.sbirthday,'yyyy')=
- (SELECT to_char(s.sbirthday,'yyyy') FROM student s WHERE s.sno='108')
- SELECT * FROM student s1,student s2 WHERE TO_char
--23, 查询 "张旭" 教师任课的学生成绩.
SELECT t.tname, s.degree FROM course c,score s,teacher t WHERE c.tno=t.tno AND c.cno=s.cno AND t.tname='张旭'
-- 方法 2
SELECT sc.* FROM score sc WHERE sc.cno IN
(SELECT c.cno FROM course c WHERE c.tno IN
(SELECT t.tno FROM teacher t WHERE t.tname='张旭'))
--24, 查询选修某课程的同学人数多于 5 人的教师姓名.
SELECT t.tname FROM course c,score s,teacher t WHERE c.tno=t.tno AND c.cno=s.cno GROUP BY t.tname HAVING COUNT(1)>5
-- 方法 2
SELECT t.tname FROM teacher t WHERE t.tno IN
(SELECT c.tno FROM course c WHERE c.cno IN
(SELECT sc.cno FROM score sc GROUP BY sc.cno HAVING COUNT(1)>5))
--25, 查询 95033 班和 95031 班全体学生的记录.
SELECT s.* FROM student s WHERE s.class='95033'OR s.class='95031'
--26, 查询存在有 85 分以上成绩的课程 Cno.
SELECT DISTINCT s.cno FROM score s WHERE s.degree>'85'
--27, 查询出 "计算机系" 教师所教课程的成绩表.
SELECT s.degree FROM score s,course c,teacher t WHERE s.cno=c.cno AND c.tno=t.tno AND t.depart='计算机系'
--28, 查询 "计算机系" 与 "电子工程系" 不同职称的教师的 Tname 和 Prof.
- SELECT t.tname,t.prof FROM teacher t WHERE t.depart='计算机系'
- AND t.prof NOT IN(SELECT t.prof FROM teacher t WHERE t.depart='电子工程系')
--29, 查询选修编号为 "3-105" 且成绩至少高于选修编号为 "3-245" 的同学的 Cno,Sno 和 Degree, 并按 Degree 从高到低次序排序.
- SELECT s.cno,s.sno,s.degree FROM score s WHERE s.cno='3-105' AND
- s.degree>ANY(SELECT s.degree FROM score s WHERE s.cno='3-245') ORDER BY s.degree DESC
--30, 查询选修编号为 "3-105" 且成绩高于选修编号为 "3-245" 的同学的 Cno,Sno 和 Degree.
- SELECT s.cno,s.sno,s.degree FROM score s WHERE s.cno='3-105' AND
- s.degree>ALL(SELECT s.degree FROM score s WHERE s.cno='3-245')
--31, 查询所有教师和同学的 name,sex 和 birthday.
SELECT tname,tsex,tbirthday FROM teacher UNION SELECT sname,ssex,sbirthday FROM student;
--32, 查询所有 "女" 教师和 "女" 同学的 name,sex 和 birthday.
- SELECT tname,tsex,tbirthday FROM teacher WHERE tsex='女'
- UNION SELECT sname,ssex,sbirthday FROM student WHERE ssex='女';
--union: 意思为联合, 使用条件为两个表的列的类型和列数都要相同
--union all 与 union 的区别在于 union all 包含了重复的数据
--33, 查询成绩比该课程平均成绩低的同学的成绩表.
Select a.* from score a where degree<( Select AVG(degree) from score b where a.cno=b.cno GROUP BY b.cno)
--34, 查询所有任课教师的 Tname 和 Depart.
SELECT t.tname,t.depart FROM teacher t WHERE EXISTS(SELECT 1 FROM course c WHERE c.tno=t.tno)
-- 方法 2
SELECT t.tname,t.depart FROM teacher t WHERE t.tno IN(SELECT c.tno FROM course c);
--35, 查询所有未讲课的教师的 Tname 和 Depart.
SELECT t.tname,t.depart FROM teacher t WHERE NOT EXISTS(SELECT 1 FROM course c WHERE c.tno=t.tno )
-- 方法 2
SELECT t.tname,t.depart FROM teacher t WHERE t.tno NOT IN(SELECT c.tno FROM course c);
--36, 查询至少有 2 名男生的班号.
SELECT r.class from (SELECT T.CLASS,SUM(DECODE(t.ssex,'男',1,0)) m FROM STUDENT T GROUP BY T.CLASS) r WHERE r.m>=2
-- 方法 2
SELECT S.CLASS FROM STUDENT S WHERE S.SSEX = '男' GROUP BY S.CLASS HAVING COUNT(1)> 1
--37, 查询 Student 表中不姓 "王" 的同学记录.
SELECT s.* FROM student s WHERE s.sname NOT LIKE('王 %')
--38, 查询 Student 表中每个学生的姓名和年龄.
SELECT s.sname,to_char(SYSDATE,'yyyy')-to_char(s.sbirthday,'yyyy') 年龄 FROM student s
--39, 查询 Student 表中最大和最小的 Sbirthday 日期值
SELECT MAX(s.sbirthday),MIN(s.sbirthday) FROM student s
--40, 以班号和年龄从大到小的顺序查询 Student 表中的全部记录.
SELECT s.* FROM student s ORDER BY s.class,to_char(SYSDATE,'yyyymm')-to_char(s.sbirthday,'yyyymm') DESC
--41, 查询 "男" 教师及其所上的课程.
SELECT c.cname FROM course c WHERE c.tno in(Select t.tno from teacher t where t.tsex='男')
-- 方法 2
SELECT t.tname, c.cname FROM course c,teacher t WHERE c.tno= t.tno AND t.tsex='男'
--42, 查询 (每个课程里) 最高分同学的 Sno,Cno 和 Degree 列.
SELECT s.sno,s.cno,s.degree FROM score s WHERE s.degree IN (SELECT MAX(s.degree) FROM score s GROUP BY s.cno)
-- 方法 2
SELECT s1.sno,s1.cno,s1.degree FROM score s1 WHERE s1.degree =
(SELECT MAX(s2.degree) FROM score s2 WHERE s2.cno=s1.cno GROUP BY s2.cno)
--43, 查询和 "李军" 同性别的所有同学的 Sname.
SELECT s.sname FROM student s WHERE s.ssex IN (SELECT s.ssex FROM student s WHERE s.sname='李军')
--44, 查询和 "李军" 同性别并同班的同学 Sname.
- SELECT s.sname FROM student s WHERE s.ssex IN (SELECT s.ssex FROM student s WHERE s.sname='李军')
- AND s.class IN (SELECT s.class FROM student s WHERE s.sname='李军')
-- 方法 2
SELECT s.sname FROM student s WHERE (s.ssex,s.class) IN(SELECT s.ssex,s.class FROM student s WHERE s.sname='李军')
--45, 查询所有选修 "计算机导论" 课程的 "男" 同学的成绩表
- SELECT sc.* FROM score sc WHERE sc.sno IN(SELECT st.sno FROM student st WHERE st.ssex='男' )
- AND sc.cno IN(SELECT c.cno FROM course c WHERE c.cname='计算机导论' )
-- 方法 2
SELECT sc.* FROM student st,score sc,course c WHERE st.sno=sc.sno AND sc.cno=c.cno AND st.ssex='男' AND c.cname='计算机导论'
来源: http://www.bubuko.com/infodetail-2617871.html