drop table PRODUCT cascade constraints;
create table PRODUCT
(
id NUMBER not null, --主键
pcode VARCHAR2(20) not null, --商品编号
pname VARCHAR2(20), --商品名称
inprice NUMBER(7,2), --进价
outprice NUMBER(7,2), --售价
toma VARCHAR2(20), --管理员名称
lastcou NUMBER, --剩余数量
ptype VARCHAR2(5), --商品类型
mark NUMBER, --有效标志
createtime DATE --生产日期
);
alter table PRODUCT
ADD CONSTRAINT pk_product primary key (ID);
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (1, 'f-qq45', '香蕉', 2.8, 4, '张三', 12, 'a', 1, to_date('05-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (2, 'f-56tt', '苹果', 3.4, 5, '张三', 33, 'a', 1, to_date('16-06-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (3, 'f-332', '梨', 2.7, 4, '张三', 76, 'a', 1, to_date('23-09-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (4, 'f-20', '猪肉', 14, 16, '张三', 56, 'b', 1, to_date('31-12-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (5, 'f-qq46', '圆珠笔', 3, 5, '王五', 55, 'e', 1, to_date('14-09-2016', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (6, 'f-57tt', '樱桃', 8, null, '王五', 12, 'a', 1, to_date('22-05-2006', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (7, 'f-292', '三文鱼', 5, 8, '王五', 23, 'b', 1, to_date('31-07-2001', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (8, 'f-604', '螺丝刀', 12, 15, '王五', 72, 'c', 1, to_date('24-02-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (9, 'f-qq47', '扳手', 14, 19, '王五', 90, 'c', 1, to_date('14-05-2015', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (10, 'f-58tt', '钳子', 10, 13, '张三', 97, 'c', 1, to_date('28-03-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (11, 'f-916', '矿泉水', 2.5, 3, '李四', 44, 'd', 1, to_date('21-07-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (12, 'f-1228', '杏仁露', 4, 5, '李四', 38, 'd', 1, to_date('22-02-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (13, 'f-59tt', '钢笔', null, 43, '李四', 22, 'e', 1, to_date('30-09-2013', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (14, 'f-1540', '铅笔', .5, .8, '赵六', 72, 'e', 1, to_date('14-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (15, 'f-1852', '大马哈鱼', 1.5, 2, '赵六', 76, 'b', 1, to_date('17-05-2012', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (16, 'f-2164', '鸡蛋', 2.7, 4, '赵六', 56, 'b', 1, to_date('14-09-2010', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (17, 'p-123h', '雪碧', 2, 3, '王五', 50, 'd', 1, to_date('01-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (18, 'p-111h', '冰红茶', 2, 3, '王五', 40, 'd', 1, to_date('18-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (19, 'p-18-h', '可乐', 2, 3, '王五', 30, 'd', 1, to_date('09-05-2017', 'dd-mm-yyyy'));
insert into PRODUCT (id, pcode, pname, inprice, outprice, toma, lastcou, ptype, mark, createtime)
values (20, 'f-qq46', '圆珠笔', 3, 5, '王五', 55, 'e', 1, to_date('14-09-2016', 'dd-mm-yyyy'));
commit;
-- 为商品名称添加一个非空约束, 为剩余数量添加一个检查约束(剩余数量 >= 5)
-- 使用insert into任意添加三条数据(数据信息自定)加到这个表中
-- 删除'猪肉'的那条记录
-- 将'扳手'和'钳子'的商品名称分别改为'16号扳手'和'小号钳子', 并将这两个商品的管理人员都改为'赵六'
-- 查询商品类型为'a'的所有商品, 并按照售价由大到小排序
-- 查询王五负责的c类商品
-- 查询剩余数量小于50的所有商品
-- 查询剩余数量在60-80之间的所有商品
-- 查询商品名称带'笔'的和带'鱼'的记录
-- 查询商品编号中带'tt'的记录
-- 查询商品类型为a, d, c的所有商品
-- !查询商品编号重复的记录, 并将他们删除
-- 查询整个表中每一类商品的剩余数量, 并按照剩余数由大到小排序
-- 查询所有'e'类型商品, 并按照剩余库存数量排序
-- 查询管理商品种类数量少于4的管理员名称和管理的商品种类数量
-- 查询所有的管理员名称和其手上所管理的所有商品种类数量
-- .查询所有的管理员名称和其手上所管理的所有'a'类商品种类数量, 数量为0的直接显示为0
-- 查询所有商品中剩余数量最少的一个
-- 查询生产日期在2015-5-31之前的数据 //棒棒哒~~
-- !假设所有商品的有效期是一年(按照365天计算), 截止到2017-06-01为标准, 查询所有商品的信息, 并且加一列标注是否过期
-- !将所有过期的商品的有效标志改为0
-- 查询每个管理员所管理的所有商品的平均进价价格和售价价格, 并四舍五入保留2位小数
-- !!查询假设每个管理员把所有商品售完后各自的利润总和(不计进价和售价为空的商品 用nvl)
省市区三表合一建新表: CREATE TABLE ssq AS (SELECT c.cityid ID,c.city NAME,c.provinceid parentids FROM city c UNION SELECT a.areaid,a.area,a.cityid FROM area a UNION SELECT p.provinceid,p.province,'0' FROM province p)
往数据库某个表中,循环添加400条有序数据,主要是格式别忘了
BEGIN
FOR cnum IN 100..500
LOOP
INSERT INTO USER_ t VALUES(sq_user_.nextval,'realname'||cnum,'1',
1323232|| cnum,'user'||cnum,'123'||
cnum,sysdatedbms_random.value(300,500),
//在范围内随机的一个时间
'1','4','4',sq_user_info.nextval);
END LOOP;
END;
select t.*, t.rowid from PRODUCT t; --添加三条数据 INSERT INTO product t VALUES(21,'xg','西瓜',1.99,3.00,' 张三',50,'d',1,to_date(20170908,'yyyymmdd')); INSERT INTO product t VALUES(22,'lb','萝卜',1.22,2.00,' 李四',34,'e',1,SYSDATE); INSERT INTO product t VALUES(23,'dg','冬瓜',1.5,2.10,' 赵六',68,'c',1,SYSDATE); COMMIT; --删除猪肉那条数据 DELETE product WHERE ID =4; COMMIT; --将'扳手'和'钳子'的商品名称分别改为'16号扳手'和'小号钳子', 并将这两个商品的管理人员都改为'赵六' UPDATE product t SET pname = '16号扳手' ,toma ='赵六' WHERE t.id = 9; UPDATE product t SET pname = '小号钳子',toma ='赵六' WHERE t.id = 10; COMMIT; --查询商品类型为'a'的所有商品, 并按照售价由大到小排序 SELECT t.* FROM product t WHERE t.ptype = 'a' ORDER BY t.outprice desc; -- 查询王五负责的c类商品 SELECT t.* FROM product t WHERE t.toma = '王五' AND t.ptype = 'c'; --查询剩余数量小于50的所有商品 SELECT t.* FROM product t WHERE t.lastcou < 50; --查询剩余数量在60-80之间的所有商品 SELECT t.* FROM product t WHERE t.lastcou > 60 AND t.lastcou < 80 ; --查询商品名称带'笔'的和带'鱼'的记录 SELECT t.* FROM product t where t.pname LIKE '%笔%' OR t.pname LIKE '%鱼%' --查询商品编号中带'tt'的记录 SELECT t.* FROM product t WHERE t.pcode LIKE '%tt%' --查询商品类型为a, d, c的所有商品 select t.* from PRODUCT t WHERE t.ptype = 'a' OR t.ptype = 'b' OR t.ptype = 'c'
--查询商品编号重复的记录, 并将他们删除 DELETE FROM product WHERE pcode =(SELECT t.pcode from PRODUCT t GROUP BY t.pcode HAVING COUNT(1)>1); COMMIT; -- 查询整个表中每一类商品的剩余数量, 并按照剩余数由大到小排序 SELECT t.* FROM product t ORDER BY t.lastcou DESC -- 查询所有'e'类型商品, 并按照剩余库存数量排序 SELECT t.* FROM product t WHERE t.ptype = 'e' ORDER BY t.lastcou -- 查询管理商品种类数量少于4的管理员名称和管理的商品种类数量 SELECT t.toma,COUNT(1) FROM product t GROUP BY t.toma HAVING COUNT(1) < 4; -- 查询所有的管理员名称和其手上所管理的所有商品种类数量 SELECT t.toma,COUNT(1) FROM product t GROUP BY t.toma --?!查询所有的管理员名称和其手上所管理的所有'a'类商品种类数量, 数量为0的直接显示为0 SELECT t.toma 管理员,nvl(count(t.ptype),0) 种类 FROM product t
WHERE t.ptype = 'a' GROUP BY t.toma SELECT t.toma,COUNT(1) FROM product t
WHERE t.ptype = 'a' GROUP BY t.toma -- 查询所有商品中剩余数量最少的一个 --SELECT * FROM product t WHERE t.lastcou <= ALL(SELECT t2.lastcou FROM product t2 ) SELECT * FROM product t WHERE t.lastcou = (SELECT MIN(t.lastcou) FROM product t ); -- 查询生产日期在2015-5-31之前的数据 SELECT t.* FROM product t WHERE t.createtime > (to_date(20150531,'yyyymmdd')) -- !!假设所有商品的有效期是一年(按照365天计算), 截止到2017-06-01为标准, 查询所有商品的信息, 并且加一列标注是否过期 SELECT t.pname 商品名,CASE WHEN to_date(20170601,'yyyymmdd') -t.createtime > 365 THEN '过期' ELSE '保质期内' END 是否过期 FROM product t -- !将所有过期的商品的有效标志改为0 UPDATE product t SET t.mark = 0 WHERE to_date(20170601,'yyyymmdd') -t.createtime > 365; COMMIT; -- 查询每个管理员所管理的所有商品的平均进价价格和售价价格, 并四舍五入保留2位小数 SELECT t.toma,ROUND (AVG (t.inprice),2) 平均进价 , ROUND (AVG (t.outprice),2) 平均售价 from PRODUCT t GROUP BY t.toma; -- !查询假设每个管理员把所有商品售完后各自的利润总和(不计进价和售价为空的商品) SELECT t.toma 管理员, SUM ((nvl(t.outprice,0) - nvl(t.inprice,0))*t.lastcou) 利润总和 from PRODUCT t GROUP BY t.toma
第二波:
表结构：
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列。
2、 查询教师所有的单位即不重复的Depart列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、查询“95033”班所选课程的平均分。
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列。
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。(所有分数)
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
23、查询“张旭“教师任课的学生成绩。
24、查询选修某课程的同学人数多于5人的教师姓名。
25、查询95033班和95031班全体学生的记录。
26、查询存在有85分以上成绩的课程Cno.
27、查询出“计算机系“教师所教课程的成绩表。
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
29、查询选修编号为“3-105“且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”的同学的Cno、Sno和Degree.
31、查询所有教师和同学的name、sex和birthday.
32、查询所有“女”教师和“女”同学的name、sex和birthday.
33、查询成绩比该课程平均成绩低的同学的成绩表。
34、查询所有任课教师的Tname和Depart.
35、查询所有未讲课的教师的Tname和Depart.
36、查询至少有2名男生的班号。
37、查询Student表中不姓“王”的同学记录。
38、查询Student表中每个学生的姓名和年龄。
39、查询Student表中最大和最小的Sbirthday日期值。
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
41、查询“男”教师及其所上的课程。
42、查询最高分同学的Sno、Cno和Degree列。
43、查询和“李军”同性别的所有同学的Sname.
44、查询和“李军”同性别并同班的同学Sname.
45、查询所有选修“计算机导论”课程的“男”同学的成绩表
--SELECT s.* FROM student s
--SELECT c.* FROM course c
--SELECT sc.* FROM score sc
--SELECT t.* FROM teacher t
--SELECT g.* FROM grade g
题目：
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT s.class , s.sname, s.ssex FROM student s
2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT t.depart FROM teacher t
3、 查询Student表的所有记录。
SELECT s.* FROM student s
4、 查询Score表中成绩在60到80之间的所有记录。
SELECT sc.* FROM score sc WHERE sc.degree < 80 AND sc.degree>60
5、 查询Score表中成绩为85，86或88的记录。
SELECT sc.* FROM score sc WHERE sc.degree =85 OR sc.degree = 86 OR sc.degree = 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 sc.* FROM score sc ORDER BY sc.cno ASC,sc.degree DESC;
9、 查询“95031”班的学生人数。
SELECT count(s.sno) FROM student s WHERE s.class ='95031'
10、查询Score表中的最高分的学生学号和课程号。
SELECT sc.cno,sc.sno,sc.degree FROM score sc WHERE sc.degree = (SELECT MAX(sc.degree) FROM score sc)
11、查询‘3-105’号课程的平均分。
SELECT AVG(sc.degree) 平均分 FROM score sc WHERE sc.cno = '3-105';
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT sc.cno 课程编号,COUNT(sc.sno) 选修人数,AVG(sc.degree) 平均分 FROM score sc WHERE sc.cno LIKE '3%' GROUP BY sc.cno
HAVING COUNT(1)>=5;
13、查询最低分大于70，最高分小于90的Sno列。
SELECT sc.sno FROM score sc
group by sc.sno HAVING MIN (sc.degree) >70 AND MAX (sc.degree) <90
14、查询所有学生的Sname、Cno和Degree列。
SELECT s.sname 姓名,sc.cno 课程名,sc.degree 成绩
FROM student s, score sc
WHERE s.sno = sc.sno
--查询所有学生的Sname、Cno和Degree列。再以课程,成绩排个序嘻嘻
SELECT s.sname 姓名,sc.cno 课程,sc.degree 成绩 FROM student s LEFT JOIN score sc ON sc.sno = s.sno ORDER BY sc.cno,sc.degree DESC
15、查询所有学生的Sno、Cname和Degree列。
SELECT sc.sno 学生编号, c.cname 课程名, sc.degree 成绩 FROM score sc LEFT JOIN course c ON c.cno = sc.cno
//on的后面对应相同的列,使两个表对应起来
16、查询所有学生的Sname、Cname和Degree列。
SELECT s.sname,c.cname,sc.degree FROM score sc,course c,student s WHERE sc.cno = c.cno AND s.sno = sc.sno;//多表查询
SELECT s.sname 姓名, d.cname 课程名,d.degree 成绩
FROM student s
LEFT JOIN (SELECT sc.sno,c.cname,sc.degree FROM course c LEFT JOIN score sc ON c.cno = sc.cno) d
ON s.sno = d.sno //多行子查询外链接
17、查询“95033”班所选课程的平均分。
SELECT sc.cno,AVG(sc.degree) FROM score sc LEFT JOIN student s ON s.sno = sc.sno WHERE s.class = '95033' GROUP BY sc.cno
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 sc.sno,sc.cno,g.rank
FROM grade g JOIN score sc ON sc.degree BETWEEN g.low AND g.upp
19、!!!查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from student s right join(SELECT *
FROM score sc WHERE sc.cno = '3-105' AND sc.degree > (
SELECT sc.degree FROM score sc WHERE sc.sno = '109' AND sc.cno = '3-105')) r on s.sno = r.sno
20、查询(score中选学一门以上课程)的同学中(分数为非最高分成绩)的记录。(所有分数)
SELECT * FROM score cc
WHERE cc.degree < (SELECT MAX(scc.degree) FROM score scc WHERE scc.cno = cc.cno ) AND
cc.sno IN (SELECT sc.sno
FROM score sc GROUP BY sc.sno HAVING COUNT(sc.cno)>1) ;
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
SELECT *FROM score cc WHERE cc.degree >(SELECT sc.degree FROM score sc WHERE sc.cno = '3-105' AND sc.sno = '109')
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT st.sno,st.sname,st.sbirthday FROM student st WHERE to_char(st.sbirthday,'yyyy') =(
SELECT to_char(s.sbirthday,'yyyy') FROM student s WHERE s.sno = '108')
23、!查询“张旭“教师任课的学生成绩。
SELECT sc.sno,sc.degree FROM score sc WHERE sc.cno = (
SELECT c.cno FROM course c RIGHT JOIN teacher t ON t.tno = c.tno WHERE t.tname = '张旭')
24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT t.tname
FROM teacher t WHERE t.tno =
(SELECT c.tno FROM course c WHERE c.cno =
(SELECT sc.cno
FROM score sc GROUP BY sc.cno HAVING COUNT(sc.sno)>5) )
25、查询95033班和95031班全体学生的记录。
SELECT * FROM student s WHERE s.class = '95033' OR s.class = '95031'
26、查询存在有85分以上成绩的课程Cno.
SELECT sc.cno FROM score sc WHERE sc.degree >'85'
27、查询出“计算机系“教师所教课程的成绩表。
SELECT sc.cno 课程,sc.degree 成绩
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.depart = '计算机系'))
28、?! 查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT t.tname,t.prof
FROM teacher t WHERE t.prof NOT IN (SELECT te.prof FROM teacher te WHERE t.depart NOT IN te.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 sc.degree FROM score sc WHERE sc.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 sc.degree FROM score sc WHERE sc.cno ='3-245').
31、查询所有教师和同学的name、sex和birthday.
SELECT s.sname,s.ssex,s.sbirthday
FROM student s
UNION
SELECT t.tname,t.tsex,t.tbirthday
FROM teacher t
32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT s.sname,s.ssex,s.sbirthday
FROM student s WHERE s.ssex ='女'
UNION
SELECT t.tname,t.tsex,t.tbirthday
FROM teacher t WHERE t.tsex = '女'
33、!查询成绩比该课程平均成绩低的同学的成绩表。
SELECT s.sno ,s.cno,s.degree,(SELECT AVG(sc.degree)
FROM score sc WHERE sc.sno = s.sno) 平均分
FROM score s WHERE s.degree
< (SELECT AVG(sc.degree)
FROM score sc WHERE sc.sno = s.sno)
34、查询所有任课教师的Tname和Depart.
SELECT t.tname,t.depart
FROM teacher t
35、查询所有未讲课的教师的Tname和Depart.
SELECT t.tname,t.depart
FROM teacher t WHERE t.tno NOT IN (
SELECT c.tno
FROM course c )
36、查询至少有2名男生的班号。
SELECT s.class
FROM student s
WHERE s.ssex = '男'
GROUP BY s.class
HAVING COUNT(s.ssex) >=2
37、查询Student表中不姓“王”的同学记录。
SELECT *
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 *
FROM student s ORDER BY s.class DESC ,s.sbirthday DESC
41、查询“男”教师及其所上的课程。
SELECT c.tno,c.cname
FROM course c WHERE c.tno IN
(SELECT t.tno
FROM teacher t
WHERE t.tsex = '男')
42、查询最高分同学的Sno、Cno和Degree列。
SELECT sc.sno,sc.cno,sc.degree
FROM score sc WHERE sc.degree =
(SELECT MAX(c.degree) FROM score c )
43、查询和“李军”同性别的所有同学的Sname.
SELECT s.sname
FROM student s WHERE s.ssex =(
SELECT st.ssex FROM student st WHERE st.sname = '李军')
44、查询和“李军”同性别并同班的同学Sname.
SELECT s.sname
FROM student s WHERE s.ssex =(
SELECT st.ssex FROM student st WHERE st.sname = '李军' ) AND
s.class = (SELECT st.class FROM student st WHERE st.sname = '李军' )
45、查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT *
FROM score sc
WHERE sc.sno IN
(SELECT s.sno
FROM student s WHERE s.ssex = '男') AND sc.cno =
(SELECT c.cno
FROM course c WHERE c.cname = '计算机导论')
第三波:
drop table COURSE cascade constraints;
drop table STUDENT cascade constraints;
drop table STU_COUR cascade constraints;
create table COURSE
(
id NUMBER not null,
cour_code VARCHAR2(20),
cour_name VARCHAR2(30),
p_cour_code VARCHAR2(20)
);
comment on column COURSE.cour_code
is '课程代码';
comment on column COURSE.cour_name
is '课程名称';
comment on column COURSE.p_cour_code
is '父级课程代码';
alter table COURSE
add constraint PK_COURSE primary key (ID);
alter table COURSE
add constraint UK_COURSE unique (COUR_CODE);
create table STUDENT
(
id NUMBER not null,
name VARCHAR2(20),
code VARCHAR2(20),
sex CHAR(1),
birthday DATE,
major VARCHAR2(20),
note VARCHAR2(300)
);
comment on column STUDENT.name
is '学生姓名';
comment on column STUDENT.code
is '学生学号';
comment on column STUDENT.sex
is '性别';
comment on column STUDENT.birthday
is '生日';
comment on column STUDENT.major
is '专业';
comment on column STUDENT.note
is '备注';
alter table STUDENT
add constraint PK_STUDENT primary key (ID);
create table STU_COUR
(
id NUMBER not null,
stu_code VARCHAR2(20),
cour_code VARCHAR2(20),
degree NUMBER(4,1)
);
comment on column STU_COUR.stu_code
is '学生学号';
comment on column STU_COUR.cour_code
is '课程代码';
comment on column STU_COUR.degree
is '课程分数';
alter table STU_COUR
add constraint PK_STU_COURT primary key (ID);
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (1, 'LAU-100', '汉语言文学专业', null);
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (2, 'C-LAU-101', '语言学概论', 'LAU-001');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (3, 'C-LAU-102', '现代汉语', 'LAU-001');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (4, 'C-LAU-103', '中国当代文学史', 'LAU-001');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (5, 'C-LAU-104', '大学英语', 'LAU-001');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (6, 'NEWS-100', '国际新闻专业', null);
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (7, 'C-NEWS-101', '新闻采访', 'NEWS-100');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (8, 'C-NEWS-102', '报纸编辑', 'NEWS-100');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (9, 'C-NEWS-103', '电视新闻', 'NEWS-100');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (10, 'HIS-121', '历史学专业', null);
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (11, 'C-HIS-335', '中国古代史', 'HIS-121');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (12, 'C-HIS-336', '世界古代史', 'HIS-121');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (13, 'C-HIS-337', '中国近代史', 'HIS-121');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (14, 'ADV-609', '广告学专业', null);
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (15, 'C-ADV-239', '广告文案写作', 'ADV-609');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (16, 'C-ADV-240', '基础美术', 'ADV-609');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (17, 'C-ADV-241', '平面广告设计与制作', 'ADV-609');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (18, 'C-ADV-242', '市场营销学', 'ADV-609');
insert into COURSE (id, cour_code, cour_name, p_cour_code)
values (19, 'C-ADV-243', '大众传播学', 'ADV-609');
commit;
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (1, '陈迪', 'stu-1011', '1', to_date('14-04-1993', 'dd-mm-yyyy'), 'LAU-100', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (2, '肖东菁', 'stu-1014', '1', to_date('15-02-1992', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (3, '汪佳丽', 'stu-1017', '2', to_date('16-08-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (19, '车晓', 'stu-1042', '1', to_date('18-03-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (5, '王聪', 'stu-1023', '1', to_date('18-03-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (6, '李璇', 'stu-1026', '2', to_date('19-05-1991', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (7, '马舒滟', 'stu-1029', '1', to_date('20-01-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (20, '张光北', 'stu-1018', '1', to_date('15-02-1992', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (9, '徐丹', 'stu-1035', '2', null, 'NEWS-100', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (11, '田野', 'stu-1041', '1', null, 'ADV-609', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (12, '彭亚光', 'stu-1044', '2', to_date('25-11-1990', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (14, '黄欢', 'stu-1050', '1', to_date('27-06-1990', 'dd-mm-yyyy'), 'ADV-609', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (15, '庞琳', 'stu-1053', '1', to_date('28-05-1989', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (16, '张子腾', 'stu-1056', '2', to_date('18-03-1990', 'dd-mm-yyyy'), 'LAU-100', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (17, '姜春阳', 'stu-1059', '2', to_date('30-05-1988', 'dd-mm-yyyy'), 'HIS-121', '1');
insert into STUDENT (id, name, code, sex, birthday, major, note)
values (18, '陈冰若', 'stu-1062', '1', to_date('31-10-1990', 'dd-mm-yyyy'), 'NEWS-100', '1');
commit;
insert into STU_COUR (id, stu_code, cour_code, degree)
values (1, 'stu-1011', 'C-LAU-101', 35);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (2, 'stu-1011', 'C-LAU-102', 65);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (3, 'stu-1011', 'C-LAU-103', 25);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (4, 'stu-1011', 'C-LAU-104', 97);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (5, 'stu-1014', 'C-HIS-335', 53);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (6, 'stu-1014', 'C-HIS-336', 35);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (7, 'stu-1014', 'C-HIS-337', 65);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (8, 'stu-1017', 'C-NEWS-101', 25);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (9, 'stu-1017', 'C-NEWS-102', 65);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (10, 'stu-1017', 'C-NEWS-103', 25);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (11, 'stu-1023', 'C-ADV-239', 33);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (12, 'stu-1023', 'C-ADV-240', 42);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (13, 'stu-1023', 'C-ADV-241', 66);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (14, 'stu-1023', 'C-ADV-242', 69);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (15, 'stu-1023', 'C-ADV-243', 82);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (16, 'stu-1026', 'C-HIS-335', 37);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (17, 'stu-1026', 'C-HIS-336', 77);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (18, 'stu-1026', 'C-HIS-337', 34);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (19, 'stu-1029', 'C-NEWS-101', 35);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (20, 'stu-1029', 'C-NEWS-102', 75);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (21, 'stu-1029', 'C-NEWS-103', 32);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (22, 'stu-1035', 'C-NEWS-101', 19);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (23, 'stu-1035', 'C-NEWS-102', 11);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (24, 'stu-1035', 'C-NEWS-103', 93);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (25, 'stu-1041', 'C-ADV-239', 99);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (26, 'stu-1041', 'C-ADV-240', 88);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (27, 'stu-1041', 'C-ADV-241', 89);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (28, 'stu-1041', 'C-ADV-242', 63);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (29, 'stu-1041', 'C-ADV-243', 44);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (30, 'stu-1044', 'C-HIS-335', 73);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (31, 'stu-1044', 'C-HIS-336', 65);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (32, 'stu-1044', 'C-HIS-337', 25);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (33, 'stu-1050', 'C-ADV-239', 33);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (34, 'stu-1050', 'C-ADV-240', 42);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (35, 'stu-1050', 'C-ADV-241', 25);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (36, 'stu-1050', 'C-ADV-242', 33);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (37, 'stu-1050', 'C-ADV-243', 42);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (38, 'stu-1053', 'C-HIS-335', 66);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (39, 'stu-1053', 'C-HIS-336', 69);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (40, 'stu-1053', 'C-HIS-337', 35);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (41, 'stu-1056', 'C-LAU-101', 65);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (42, 'stu-1056', 'C-LAU-102', 25);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (43, 'stu-1056', 'C-LAU-103', 97);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (44, 'stu-1056', 'C-LAU-104', 53);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (45, 'stu-1059', 'C-HIS-335', 35);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (46, 'stu-1059', 'C-HIS-336', 25);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (47, 'stu-1059', 'C-HIS-337', 97);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (48, 'stu-1062', 'C-NEWS-101', 32);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (49, 'stu-1062', 'C-NEWS-102', 19);
insert into STU_COUR (id, stu_code, cour_code, degree)
values (50, 'stu-1062', 'C-NEWS-103', 11);
commit;
--题目
--查询学生表中的姓名，专业
--查询学生表, 按照学号由小到大排序, 前五个同学的姓名，专业
--查询专业为国际新闻的学生的所有信息
--查询1991年出生的学生姓名和专业
--查询历史, 广告, 国际新闻专业的所有学生信息
--查询姓名是两个字的姓王, 姓陈, 姓李的所有学生信息
--查询没有学分的学生信息
--查询课程表中不是专业的记录
--查询计算机专业的没有记录生日的学生信息
--查询按照专业降序，学号升序排列所有学生信息
--查询表中前三个的学生的姓名，专业，到现在的年龄并按照年龄降序排列
--查询学生表中专业, 并显示为文字
--查询所有的课程代码和每个课程的平均成绩并按照课程号排序
--查询出每个课程代码的最高分和最低分
--查询学号为stu-1023的学生的各课成绩
--查询各专业各有多少人
--查询出历史学专业有多少人
--查询出各专业里男女生各有多少人
--查询出学生所有课程的平均分在50分以上的学生学号
--查询每个学生有几门课成绩高于80分
--查询所有学生的学号，姓名，专业课程号，成绩
--查询课程号为C-HIS-336的学生的姓名和成绩
--查询选修广告学专业的基础美术这门课程的学生学号，姓名，成绩
--查询选修课程号为C-NEWS-101这门课的所有学生信息和成绩
--查询生日是同一天的学生信息
--查询各课的课程名，课程号，每门课所有学生的平均成绩
--查询所有学生的平均成绩
--查询每个专业的每个课程的平均分是多少
--查询平均分大于40分的国际新闻专业的每个学生姓名，学号和各课的平均分
--查询平均分大于等于课程号为C-ADV-239的课程号和平均分
--查询历史学专业下的课程的及格率(课程得分在50以上的人数除以总人数)
--查询没有选修C-NEWS-101这门课程的学生信息和课程信息
--查询没有课程成绩的学生信息
--假设在一个页面上要显示成绩表, 按照成绩降序排列, 分页显示信息, 每页显示5条, 查询出第2页的信息
--查询学生表中的姓名，专业
SELECT s.name,s.major
FROM student s
--!伪列|查询学生表, 按照学号由小到大排序, 前五
个同学的姓名，专业
SELECT ROWNUM , d.name,d.major
FROM ( SELECT * FROM student s ORDER BY
s.id) d
WHERE ROWNUM <6
--查询专业为国际新闻的学生的所有信息
SELECT *
FROM student s WHERE s.major IN
(SELECT c.cour_code
FROM course c
WHERE c.cour_name LIKE '%国际新闻%')
--查询1991年出生的学生姓名和专业
SELECT st.name,(SELECT c.cour_name
FROM course c WHERE c.cour_code = st.major) 专
业
FROM student st
WHERE to_char(st.birthday,'yyyy') = '1991'
--查询历史, 广告, 国际新闻专业的所有学生信息
SELECT *
FROM student s
WHERE s.major IN (
SELECT c.cour_code FROM course c
WHERE c.cour_name LIKE '%历史%' OR
c.cour_name LIKE '%广告%' OR c.cour_name LIKE
'%国际新闻%' )
--查询姓名是两个字的姓王, 姓陈, 姓李的所有学生
信息
s.name LIKE '王_' OR
--查询没有学分/成绩的学生信息
SELECT *
FROM student s
WHERE s.code NOT IN
(SELECT st.stu_code FROM stu_cour st
GROUP BY st.stu_code )
--查询课程表中不是专业的记录
SELECT c.*
FROM course c WHERE c.p_cour_code IS NOT NULL
--查询国际新闻专业的没有记录生日的学生信息
SELECT *
FROM student s WHERE s.major =(
SELECT c.cour_code FROM course c WHERE
c.cour_name = '国际新闻专业')
AND s.birthday IS NULL
--查询按照专业降序，学号升序排列所有学生信息
SELECT * FROM student s ORDER BY
s.major DESC,s.code ASC
--查询表中前三个的学生的姓名，专业，到现在的年
龄并按照年龄降序排列
select *
from (SELECT s.name , s.major ,
(to_char(SYSDATE,'yyyy') -to_char
(s.birthday,'yyyy')) 年龄
FROM student s where s.birthday id not null
ORDER BY 年龄 DESC)r
where rownum <=3
--查询学生表中专业, 并显示为文字
SELECT (SELECT c.cour_name FROM course
c WHERE c.cour_code = s.major) FROM student s
--查询所有的课程代码和每个课程的平均成绩并按照
课程号排序
SELECT SC.COUR_CODE, AVG(SC.DEGREE)
FROM STU_COUR SC
GROUP BY SC.COUR_CODE
ORDER BY SC.COUR_CODE
--查询出每个课程代码的最高分和最低分
SELECT SC.COUR_CODE, MIN(SC.DEGREE),
MAX(SC.DEGREE)
FROM STU_COUR SC
GROUP BY SC.COUR_CODE
ORDER BY SC.COUR_CODE
--查询学号为stu-1023的学生的各课成绩
SELECT * FROM stu_cour sc WHERE
sc.stu_code='stu-1023'
--查询各专业各有多少人
SELECT s.major, COUNT(s.major) FROM
student s GROUP BY s.major
--查询出历史学专业有多少人
SELECT S.MAJOR, COUNT(S.MAJOR)
FROM STUDENT S
WHERE S.major = (SELECT C.cour_CODE FROM
COURSE C WHERE C.COUR_NAME LIKE '%历史%')
GROUP BY S.MAJOR
--查询出各专业里男女生各有多少人
SELECT S.MAJOR,s.sex, COUNT(s.sex)
FROM STUDENT S
GROUP BY S.MAJOR, s.sex
--查询出学生所有课程的平均分在50分以上的学生学
号
SELECT sc.stu_code, AVG(sc.degree) FROM
stu_cour sc GROUP BY sc.stu_code HAVING AVG
(sc.degree) > 50
--查询每个学生有几门课成绩高于80分
SELECT SC.STU_CODE, COUNT(SC.DEGREE)
FROM (SELECT * FROM STU_COUR WHERE DEGREE >
80) SC
GROUP BY SC.STU_CODE
--查询所有学生的学号，姓名，专业,课程号，成绩
SELECT S.CODE, S.NAME, S.MAJOR,
SC.COUR_CODE, SC.DEGREE
FROM STUDENT S
LEFT JOIN STU_COUR SC
ON S.CODE = SC.STU_CODE
--查询课程号为C-HIS-336的学生的姓名和成绩
--查询选修广告学专业的基础美术这门课程的学生学
号，姓名，成绩
SELECT c.cour_code FROM course c WHERE
c.cour_name ='基础美术'
--查询选修课程号为C-NEWS-101这门课的所有学生信
息和成绩
--查询生日是同一天的学生信息
SELECT R.BIRTHDAY, WM_CONCAT(DISTINCT
R.NAME)
FROM (SELECT S1.NAME, S1.BIRTHDAY
FROM STUDENT S1, STUDENT S2
WHERE S1.BIRTHDAY = S2.BIRTHDAY
AND S1.CODE != S2.CODE) R
GROUP BY R.BIRTHDAY
--查询各课的课程名，课程号，每门课所有学生的平
均成绩
SELECT SC.COUR_CODE,
(SELECT C.COUR_NAME FROM COURSE C WHERE
C.COUR_CODE = SC.COUR_CODE),
AVG(SC.DEGREE)
FROM STU_COUR SC
GROUP BY SC.COUR_CODE
--查询所有学生的平均成绩
--查询每个专业的每个课程的平均分是多少
--查询平均分大于40分的国际新闻专业的每个学生姓
名，学号和各课的平均分
SELECT SC.STU_CODE, AVG(SC.DEGREE)
FROM STU_COUR SC
WHERE SC.COUR_CODE IN
(SELECT C.COUR_CODE
FROM COURSE C
WHERE C.P_COUR_CODE =
(SELECT C.COUR_CODE
FROM COURSE C
WHERE C.COUR_NAME LIKE '%国际
新闻%'))
GROUP BY SC.STU_CODE
HAVING AVG(SC.DEGREE) > 40
--查询平均分大于等于课程号为C-ADV-239的课程号和
平均分
SELECT SC.cour_code, AVG(SC.DEGREE)
FROM STU_COUR SC
GROUP BY SC.COUR_CODE
HAVING AVG(SC.DEGREE) > (SELECT AVG(SC.DEGREE)
FROM STU_COUR SC
WHERE SC.COUR_CODE =
'C-ADV-239'
GROUP BY
SC.COUR_CODE)
--查询历史学专业下的课程的及格率(课程得分在50以
上的人数除以总人数)
SELECT SC.COUR_CODE,
(SUM(CASE
WHEN SC.DEGREE > 50 THEN
1
ELSE
0
END) / COUNT(SC.DEGREE))*100||'%' 及
格率
FROM STU_COUR SC
WHERE SC.COUR_CODE IN
(SELECT C.COUR_CODE
FROM COURSE C
WHERE C.P_COUR_CODE IS NOT NULL
CONNECT BY PRIOR C.COUR_CODE =
C.P_COUR_CODE
START WITH C.COUR_NAME LIKE '%历史
学%')
GROUP BY SC.COUR_CODE
--查询没有选修C-NEWS-101这门课程的学生信息和课
程信息
SELECT * FROM stu_cour sc WHERE
sc.cour_code <> 'C-NEWS-101'
--假设在一个页面上要显示成绩表, 按照成绩降序排
列, 分页显示信息, 每页显示5条, 查询出第2页的信
息
SELECT *
FROM (SELECT R.*, ROWNUM RM
FROM (SELECT * FROM STU_COUR SC ORDER
BY SC.DEGREE DESC) R
WHERE ROWNUM <= 10) R1
WHERE R1.RM > 5