Oracal sql语句练习
- sql语句练习
- 初始化表与数据
- 练习
- 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号
- 2、查询平均成绩大于60 分的同学的学号和平均成绩
- 3、查询所有同学的学号、姓名、选课数、总成绩
- 4、查询姓“刘”的老师的个数;
- 5、查询没学过“谌燕”老师课的同学的学号、姓名;
- 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
- 7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名
- 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名
- 9、查询所有课程成绩小于60 分的同学的学号、姓名
- 10、查询没有学全所有课的同学的学号、姓名
- 11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名
- 12、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩
- 13、删除学习“谌燕”老师课的SC 表记录;
- 14、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩;
- 15、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
- 16、按各科平均成绩从低到高和及格率的百分数从高到低顺序
- 17、查询不同老师所教不同课程平均分从高到低显示
- 18、查询每门课程被选修的学生数
- 19、查询出只选修了一门课程的全部学生的学号和姓名
- 20、查询男生、女生人数
- 21、查询姓“张”的学生名单
- 22、查询同名同性学生名单,并统计同名人数
- 23、1981 年出生的学生名单(注:Student 表中Sage 列的类型是number)
- 24、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
- 25、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
- 26、查询课程名称为“数据库”,且分数低于60 的学生姓名和分数
- 27、查询所有学生的选课情况;
- 28、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数;
- 29、查询不及格的课程,并按课程号从大到小排列
- 30、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名;
- 31、求选了课程的学生人数
- 32、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩
- 33、查询各个课程及相应的选修人数
- 34、查询不同课程成绩相同的学生的学号、课程号、学生成绩
- 35、统计每门课程的学生选修人数(超过10 人的课程才统计)。
- 36、检索至少选修两门课程的学生学号
- 37、查询全部学生都选修的课程的课程号和课程名
- 38、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
- 39、查询两门以上不及格课程的同学的学号及其平均成绩
- 40、检索“c004”课程分数小于60,按分数降序排列的同学学号
- 41、删除“s002”同学的“c001”课程的成绩
sql语句练习
初始化表与数据
1.建表
create table student(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
ssex varchar2(5)
);
create table teacher(
tno varchar2(10) primary key,
tname varchar2(20)
);
create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
);
2.初始化
/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
commit;
/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
commit;
/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
commit;
/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
commit;
练习
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号
SELECT A.SNO 学号, A.SCORE C001成绩, B.SCORE C002成绩
FROM (SELECT SNO, SCORE FROM SC WHERE CNO = 'c001') A
JOIN (SELECT SNO, SCORE FROM SC WHERE CNO = 'c002') B
ON A.SNO = B.SNO
WHERE A.SCORE > B.SCORE;
2、查询平均成绩大于60 分的同学的学号和平均成绩
SELECT SNO, AVG(SCORE) FROM SC GROUP BY SNO HAVING AVG(SCORE) > 60;
3、查询所有同学的学号、姓名、选课数、总成绩
SELECT A.SNO, SNAME, CLAZZNUM, SUMSCORE
FROM STUDENT A
LEFT JOIN (SELECT SNO, COUNT(SCORE) AS CLAZZNUM, SUM(SCORE) AS SUMSCORE
FROM SC
GROUP BY SNO) B
ON A.SNO = B.SNO;
4、查询姓“刘”的老师的个数;
SELECT COUNT(TNAME) FROM TEACHER WHERE TNAME LIKE '刘%';
5、查询没学过“谌燕”老师课的同学的学号、姓名;
SELECT SNO, SNAME
FROM STUDENT
WHERE SNO NOT IN
(SELECT SNO
FROM SC
WHERE CNO IN
(SELECT CNO
FROM COURSE
WHERE TNO IN (SELECT TNO FROM TEACHER WHERE TNAME = '谌燕'))
GROUP BY SNO);
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
SELECT SNO, SNAME
FROM STUDENT
WHERE SNO IN (SELECT A.SNO
FROM (SELECT SNO, SCORE FROM SC WHERE CNO = 'c001') A
JOIN (SELECT SNO, SCORE FROM SC WHERE CNO = 'c002') B
ON A.SNO = B.SNO);
7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名
SELECT A.SNO, SNAME
FROM STUDENT A
JOIN (SELECT SNO, COUNT(CNO) AS COUNTCON -- 上过谌燕课的门数
FROM SC
WHERE CNO IN -- 谌燕的所有课
(SELECT CNO
FROM COURSE
WHERE TNO IN (SELECT TNO FROM TEACHER WHERE TNAME = '谌燕'))
GROUP BY SNO) B
ON A.SNO = B.SNO
WHERE COUNTCON =
(SELECT COUNT(CNO)
FROM COURSE
WHERE TNO IN (SELECT TNO FROM TEACHER WHERE TNAME = '谌燕'));
8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名
SELECT SNO, SNAME
FROM STUDENT
WHERE SNO IN (SELECT A.SNO
FROM (SELECT SNO, SCORE FROM SC WHERE CNO = 'c001') A
JOIN (SELECT SNO, SCORE FROM SC WHERE CNO = 'c002') B
ON A.SNO = B.SNO
WHERE A.SCORE > B.SCORE);
9、查询所有课程成绩小于60 分的同学的学号、姓名
SELECT SNO, SNAME
FROM STUDENT
WHERE SNO IN (SELECT A.SNO
FROM (SELECT SNO, COUNT(CNO) AS N1
FROM SC
WHERE SCORE < 60
GROUP BY SNO) A
JOIN (SELECT SNO, COUNT(CNO) AS N2 FROM SC GROUP BY SNO) B
ON A.SNO = B.SNO
WHERE N1 = N2);
10、查询没有学全所有课的同学的学号、姓名
SELECT A.SNO, SNAME
FROM STUDENT A
LEFT JOIN (SELECT SNO, COUNT(CNO) AS COUNTCNO
FROM SC
GROUP BY SNO
HAVING COUNT(CNO) != (SELECT COUNT(CNO) FROM COURSE)) B
ON A.SNO = B.SNO;
11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名
SELECT SNO, SNAME
FROM STUDENT
WHERE SNO IN (SELECT SNO
FROM SC
WHERE CNO IN (SELECT CNO FROM SC WHERE SNO = 's001')
AND SNO != 's001');
12、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩
UPDATE SC U
SET U.SCORE =
(SELECT AVG_SCORE
FROM (SELECT R.CNO, ROUND(AVG(R.SCORE), 2) AVG_SCORE
FROM SC R
INNER JOIN COURSE S
ON S.CNO = R.CNO
INNER JOIN TEACHER T
ON T.TNO = S.TNO
WHERE T.TNAME = '谌燕'
GROUP BY R.CNO) V
WHERE V.CNO = U.CNO)
WHERE U.CNO IN
(SELECT CNO
FROM COURSE
WHERE TNO IN (SELECT TNO FROM TEACHER WHERE TNAME = '谌燕'));
13、删除学习“谌燕”老师课的SC 表记录;
DELETE FROM SC
WHERE CNO IN
(SELECT CNO
FROM COURSE
WHERE TNO IN (SELECT TNO FROM TEACHER WHERE TNAME = '谌燕'));
14、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩;
insert into sc values ('s005','c002',78.9);
insert into sc values ('s006','c004',80.9);
insert into sc values ('s007','c005',81.9);
insert into sc values ('s004','c004',60.9);
insert into sc values ('s005','c005',82.9);
insert into sc values ('s004','c002',72.9);
insert into sc values ('s009','c005',81.9);
insert into sc values ('s008','c002',59);
insert into sc values ('s005','c003',99.5);
insert into sc values ('s006','c005',99.5);
insert into sc values ('s007','c002',99.5);
insert into sc values ('s004','c001',99.5);
insert into sc values ('s005','c007',99.5);
insert into sc values ('s005','c004',99.5);
insert into sc values ('s009','c003',99.5);
insert into sc values ('s008','c004',99.5);
commit;
15、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT CNO 课程ID, MAX(SCORE) 最高分, MIN(SCORE) 最低分
FROM SC
GROUP BY CNO;
16、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT 课程ID, 平均成绩, 及格率
FROM (SELECT CNO 课程ID, AVG(SCORE) 平均成绩 FROM SC GROUP BY CNO) S1
JOIN (SELECT A.CNO, NVL(N2, 0) / N1 及格率
FROM (SELECT CNO, COUNT(SCORE) AS N1 FROM SC GROUP BY CNO) A
LEFT JOIN (SELECT CNO, COUNT(SCORE) AS N2
FROM SC
WHERE SCORE > 60
GROUP BY CNO) B
ON A.CNO = B.CNO) BB
ON S1.课程ID = BB.CNO
ORDER BY 平均成绩, 及格率 DESC;
17、查询不同老师所教不同课程平均分从高到低显示
SELECT B.TNAME 老师, 课程, 平均分
FROM (SELECT A.CNAME 课程, A.TNO, 平均分
FROM COURSE A
JOIN (SELECT CNO, AVG(SCORE) 平均分 FROM SC GROUP BY CNO) B
ON A.CNO = B.CNO) A
JOIN TEACHER B
ON A.TNO = B.TNO
ORDER BY 平均分 DESC;
18、查询每门课程被选修的学生数
SELECT A.CNAME, NVL(学生数, 0)
FROM COURSE A
LEFT JOIN (SELECT CNO, COUNT(SNO) 学生数 FROM SC GROUP BY CNO) B
ON A.CNO = B.CNO;
19、查询出只选修了一门课程的全部学生的学号和姓名
SELECT SNO, SNAME
FROM STUDENT
WHERE SNO IN (SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(CNO) = 1);
20、查询男生、女生人数
SELECT SSEX,COUNT(SNO) FROM STUDENT GROUP BY SSEX;
21、查询姓“张”的学生名单
SELECT * FROM STUDENT WHERE SNAME LIKE '张%';
22、查询同名同性学生名单,并统计同名人数
INSERT INTO student values ('s011','张三',23,'男');
INSERT INTO student values ('s012','张三',23,'女');
-- 同名同性学生名单
SELECT A.SNAME
FROM STUDENT A
JOIN STUDENT B
ON A.SNAME = B.SNAME
WHERE A.SNO != B.SNO
AND A.SSEX = B.SSEX
GROUP BY A.SNAME
-- 统计同名人数
SELECT SNAME, COUNT(SNO)
FROM STUDENT
WHERE SNAME IN (SELECT A.SNAME
FROM STUDENT A
JOIN STUDENT B
ON A.SNAME = B.SNAME
WHERE A.SNO != B.SNO
GROUP BY A.SNAME)
GROUP BY SNAME
23、1981 年出生的学生名单(注:Student 表中Sage 列的类型是number)
SELECT *
FROM STUDENT
WHERE SAGE >
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE('1981-12-31', 'yyyy-mm-dd')) / 12)
AND SAGE <
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE('1981-01-01', 'yyyy-mm-dd')) / 12);
24、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
SELECT A.CNAME 课程, A.CNO, 平均分
FROM COURSE A
JOIN (SELECT CNO, AVG(SCORE) 平均分 FROM SC GROUP BY CNO) B
ON A.CNO = B.CNO
ORDER BY 平均分, A.CNO DESC;
25、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
SELECT A.SNO, SNAME, AVGSCORE
FROM STUDENT A
JOIN (SELECT SNO, AVG(SCORE) AS AVGSCORE
FROM SC
GROUP BY SNO
HAVING AVG(SCORE) > 85) B
ON A.SNO = B.SNO;
26、查询课程名称为“数据库”,且分数低于60 的学生姓名和分数
SELECT SNAME, CNO, SCORE
FROM STUDENT A
JOIN (SELECT CNO, SNO, SCORE
FROM SC
WHERE CNO IN (SELECT CNO
FROM COURSE
WHERE CNAME IN ('Oracle', 'SQL SERVER 2005'))
AND SCORE < 60) B
ON A.SNO = B.SNO;
27、查询所有学生的选课情况;
SELECT SNAME, CNAME
FROM STUDENT AA
LEFT JOIN (SELECT SNO, CNAME
FROM (SELECT SNO, CNO FROM SC ORDER BY SNO) A
JOIN COURSE B
ON A.CNO = B.CNO) BB
ON AA.SNO = BB.SNO;
28、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数;
SELECT B.SNAME, C.CNAME, A.SCORE
FROM SC A
JOIN STUDENT B
ON A.SNO = B.SNO
JOIN COURSE C
ON A.CNO = C.CNO
WHERE A.SCORE > 70;
29、查询不及格的课程,并按课程号从大到小排列
INSERT INTO SC VALUES ('s010', 'c004', 57);
SELECT SNO, CNO, SCORE FROM SC WHERE SCORE < 60 ORDER BY CNO DESC;
30、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名;
SELECT A.SNO, A.SNAME
FROM STUDENT A
JOIN SC B
ON A.SNO = B.SNO
WHERE B.CNO = 'c001'
AND B.SCORE > 80;
31、求选了课程的学生人数
SELECT COUNT(DISTINCT SNO) FROM SC;
32、查询选修“谌燕”老师所授课程的学生中,成绩最高的学生姓名及其成绩
SELECT CNO, SNAME, MAXSCORE
FROM STUDENT AA
JOIN (SELECT A.CNO, SNO, MAXSCORE
FROM SC A
JOIN (SELECT CNO, MAX(SCORE) MAXSCORE
FROM SC
WHERE CNO IN
(SELECT CNO
FROM COURSE
WHERE TNO IN
(SELECT TNO FROM TEACHER WHERE TNAME = '谌燕'))
GROUP BY CNO) B
ON A.CNO = B.CNO
WHERE A.SCORE = B.MAXSCORE) BB
ON AA.SNO = BB.SNO;
33、查询各个课程及相应的选修人数
SELECT CNAME, CCNO
FROM COURSE A
LEFT JOIN (SELECT CNO, COUNT(SNO) AS CCNO FROM SC GROUP BY CNO) B
ON A.CNO = B.CNO;
34、查询不同课程成绩相同的学生的学号、课程号、学生成绩
SELECT A.SNO, B.SNO, A.CNO, A.SCORE
FROM SC A
JOIN SC B
ON A.SCORE = B.SCORE
WHERE A.SNO != B.SNO
AND A.CNO != B.CNO
ORDER BY A.SCORE;
35、统计每门课程的学生选修人数(超过10 人的课程才统计)。
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
-- 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT CNO, COUNT(SNO) 学生数
FROM SC
GROUP BY CNO
HAVING COUNT(SNO) > 10
ORDER BY COUNT(SNO), CNO;
36、检索至少选修两门课程的学生学号
SELECT SNO FROM SC GROUP BY SNO HAVING COUNT(CNO) >= 2;
37、查询全部学生都选修的课程的课程号和课程名
SELECT CNO, CNAME
FROM COURSE
WHERE CNO IN (SELECT CNO
FROM SC
GROUP BY CNO
HAVING COUNT(SNO) = (SELECT COUNT(SNO) FROM STUDENT));
38、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
SELECT SNO, SNAME
FROM STUDENT
WHERE SNO NOT IN
(SELECT SNO
FROM SC
WHERE CNO IN
(SELECT CNO
FROM COURSE
WHERE TNO IN (SELECT TNO FROM TEACHER WHERE TNAME = '谌燕'))
GROUP BY SNO);
39、查询两门以上不及格课程的同学的学号及其平均成绩
SELECT SNO, AVG(SCORE)
FROM SC
WHERE SNO IN (SELECT SNO
FROM SC
WHERE SCORE < 60
GROUP BY SNO
HAVING COUNT(SCORE) > 2)
GROUP BY SNO;
40、检索“c004”课程分数小于60,按分数降序排列的同学学号
SELECT SNO, SCORE
FROM SC
WHERE CNO = 'c004'
AND SCORE < 60
ORDER BY SCORE, SNO;
41、删除“s002”同学的“c001”课程的成绩
DELETE FROM SC
WHERE SNO = 's002'
AND CNO = 'c001';
写稿不易,感谢支持!