SQL练习

一、建表插入数据（基于Oracle）

STUDENT 表
INSERT INTO "HR"."STUDENT" VALUES ('5', '小花', '20', '女', TO_DATE('1999-01-14 17:13:50', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "HR"."STUDENT" VALUES ('6', '小兰', '23', '男', TO_DATE('2000-06-14 17:14:10', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "HR"."STUDENT" VALUES ('7', '小兰', '20', ' 女', TO_DATE('1995-06-14 17:14:10', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "HR"."STUDENT" VALUES ('1', '小白', '13', '男', TO_DATE('1993-05-14 17:14:10', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "HR"."STUDENT" VALUES ('2', '小黑', '16', '女', TO_DATE('1991-06-25 17:14:10', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "HR"."STUDENT" VALUES ('3', '小红', '21', '男', TO_DATE('1992-06-25 17:14:10', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "HR"."STUDENT" VALUES ('4', '小绿', '22', '女', TO_DATE('2017-06-24 17:14:10', 'YYYY-MM-DD HH24:MI:SS'));
COURSE 表
INSERT INTO "HR"."COURSE" VALUES ('1', '语文', '3');
INSERT INTO "HR"."COURSE" VALUES ('2', '数学', '2');
INSERT INTO "HR"."COURSE" VALUES ('3', '英语', '1');
INSERT INTO "HR"."COURSE" VALUES ('4', 'Java', '4');
INSERT INTO "HR"."COURSE" VALUES ('5', 'IOS', '5');
Teacher表：
INSERT INTO "HR"."TEACHER" VALUES ('1', '李老师');
INSERT INTO "HR"."TEACHER" VALUES ('2', '王老师');
INSERT INTO "HR"."TEACHER" VALUES ('3', '范老师');
INSERT INTO "HR"."TEACHER" VALUES ('4', '董老师');
INSERT INTO "HR"."TEACHER" VALUES ('5', '何老师');
SC表：
INSERT INTO "HR"."SC" VALUES ('1', '1', '77');
INSERT INTO "HR"."SC" VALUES ('2', '2', '66');
INSERT INTO "HR"."SC" VALUES ('1', '5', '86');
INSERT INTO "HR"."SC" VALUES ('1', '3', '90');
INSERT INTO "HR"."SC" VALUES ('1', '4', '78');
INSERT INTO "HR"."SC" VALUES ('2', '1', '99');
INSERT INTO "HR"."SC" VALUES ('2', '3', '88');
INSERT INTO "HR"."SC" VALUES ('2', '4', '73');
INSERT INTO "HR"."SC" VALUES ('3', '1', '57');
INSERT INTO "HR"."SC" VALUES ('3', '2', '63');
INSERT INTO "HR"."SC" VALUES ('3', '3', '50');
INSERT INTO "HR"."SC" VALUES ('3', '4', '59');
INSERT INTO "HR"."SC" VALUES ('4', '1', '55');
INSERT INTO "HR"."SC" VALUES ('4', '2', '58');
INSERT INTO "HR"."SC" VALUES ('4', '3', '59');
INSERT INTO "HR"."SC" VALUES ('4', '4', '68');
INSERT INTO "HR"."SC" VALUES ('4', '5', '80');
INSERT INTO "HR"."SC" VALUES ('3', '5', '87');
INSERT INTO "HR"."SC" VALUES ('5', '1', '86');
INSERT INTO "HR"."SC" VALUES ('5', '5', '77');
INSERT INTO "HR"."SC" VALUES ('6', '3', '80');
INSERT INTO "HR"."SC" VALUES ('6', '2', '92');

二、SQL语句

--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select stu.*,s1."score" 课程一,s2."score" 课程二
from SC s1,SC s2,STUDENT stu
where s1."c_id"=1 and s2."c_id"=2 and s1."s_id"=s2."s_id" and s1."s_id"=stu."s_id" and s1."score">s2."score";
--2、查询平均成绩大于等于80分的同学的学生编号和学生姓名和平均成绩
select stu."s_id",stu."s_name",avg(s."score")
from STUDENT stu,SC s
where stu."s_id"=s."s_id"
group by stu."s_id",stu."s_name"
having avg(s."score")>=80;
--3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select STU."s_id",STU."s_name",count(s."score") 选课总数,sum(s."score") 总成绩
from STUDENT stu ,SC s,COURSE c
where STU."s_id"=s."s_id" and s."c_id"=c."c_id"
group by STU."s_id",STU."s_name";
--4、查询学过"王老师"老师授课的同学的信息                                     
select STU.*
from teacher t,COURSE c,STUDENT stu,SC s
where t."t_id"=c."teacher_id" and c."c_id"=s."c_id" and STU."s_id"=s."s_id" and t."t_name" like '王老师';
--5、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息                                      exists()  表示如果查询出结果返回true 没有结果返回false
select STUDENT.* from STUDENT , SC where STUDENT."s_id" = SC."s_id" and SC."c_id"= '01'
and exists (select 1 from SC SC_2 where SC_2."s_id" = SC."s_id" and SC_2."c_id" = '02')
order by STUDENT."s_id";
--6、查询没有学全所有课程的同学的信息
SELECT STU."s_id",STU."s_name"
from STUDENT stu ,SC s,COURSE c
where STU."s_id"=s."s_id" and s."c_id"=c."c_id"
group by STU."s_id",STU."s_name"
having count(s."c_id")<(select count(COU."c_id") from COURSE cou);
--7、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct STU.* from STUDENT stu,SC s
where STU."s_id"=s."s_id"
and s."c_id" in
(SELECT s."c_id"
from STUDENT stu,SC s
where STU."s_id"=s."s_id" and s."s_id"=1)
and STU."s_id"!=1;
--8、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select STU."s_id",STU."s_name"
FROM STUDENT stu ,SC s
where STU."s_id"=s."s_id"
and s."c_id" in
(SELECT s."c_id"
from STUDENT stu ,SC s
where STU."s_id"=s."s_id"
and STU."s_id"=1)
and STU."s_id"!=1
group by STU."s_id",STU."s_name"
having count(1)=(select count(*) from SC where "s_id"=1);
--9、查询没学过"何老师"老师讲授的任一门课程的学生姓名
select * from STUDENT STU
where stu."s_id" not in(
SELECT STU."s_id" from TEACHER t,STUDENT stu ,COURSE c,SC s
where t."t_id"=c."teacher_id" and s."s_id"=STU."s_id" and c."c_id"=s."c_id" and t."t_name" like '%何老师%');
--10、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT STU."s_id",STU."s_name",cast(avg(s."score") as DECIMAL (4,2))
from STUDENT stu,SC s
where STU."s_id"=s."s_id" and s."score"<60
group by STU."s_id",STU."s_name"
having count(1)>=2;
--11、检索"01"课程分数小于60，按分数降序排列的学生信息
select stu.* , s."c_id" , s."score" from STUDENT stu, SC s
where STU."s_id"=s."s_id" and s."score" < 60 and s."c_id"= '1'
order by s."score" desc;
--12、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT STUDENT."s_id",STUDENT."s_name",avg(sc."score") 平均成绩,sum(sc."score") 总成绩
from STUDENT,SC
where STUDENT."s_id"=SC."s_id"
group by STUDENT."s_id",STUDENT."s_name"
order by avg(sc."score") desc;
--13、查询学生的总成绩并进行排名
select ST."s_id",ST."s_name",sum(s."score")
from SC s,STUDENT st
where s."s_id"=ST."s_id"
group by ST."s_id",ST."s_name"
order by sum(s."score") desc;
--14、查询不同老师所教不同课程的平均分，查询结果从高到低显示
SELECT t."t_id",t."t_name",c."c_name",avg(s."score")
from TEACHER t,COURSE c,SC s
where t."t_id"=c."teacher_id" and c."c_id"=s."c_id"
group by t."t_id",t."t_name",c."c_name"
order by avg(s."score") desc;
--15、查询选修"王老师"老师所授课程的学生中，成绩最高的学生信息及其成绩（最高分可能不止一个）
SELECT STU.*,s."score"
from STUDENT stu,SC s
where STU."s_id"=s."s_id"
and s."score"=
(select max(s."score")
from TEACHER t,SC s,COURSE c
where t."t_id"=c."teacher_id" and s."c_id"=c."c_id" and t."t_name" like '%王老师%');
--16、查询选修了全部课程的学生信息
select STU."s_id",STU."s_name"
from SC s,COURSE c,STUDENT stu
where s."c_id"=c."c_id" and s."s_id"=STU."s_id"
group by STU."s_id",STU."s_name"
having count(s."score")=(select count(*) from COURSE);
--17、查询每门课程被选修的学生数
select c."c_id",c."c_name",count(*)
from COURSE c,SC s
where c."c_id"=s."c_id"
group by c."c_id",c."c_name";
--18、查询出只选修了两门课程的全部学生的学号和姓名
select STU."s_id",STU."s_name"
from STUDENT stu,SC s
where stu."s_id"=s."s_id"
group by STU."s_id",STU."s_name"
having  count(1)=2;
--19、查询名字中含有"风"字的学生信息
SELECT * from STUDENT stu
where STU."s_name" like '%黑%';
--20、查询同名同性学生名单，并统计人数,显示的结果为：学生姓名、性别、人数
SELECT STU .* ,s.CN from
(select STU."s_name",count(STU."s_name") cn
from STUDENT STU
group by STU."s_name"
having count(STU."s_name")>1) s,STUDENT STU
where s."s_name"=STU."s_name";
--21、查询每门课R的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c."c_id",c."c_name",cast( avg(s."score") as decimal(8,2))
from SC s,COURSE c
where s."c_id"=c."c_id"
group by c."c_id",c."c_name"
order by avg(s."score") desc,c."c_id" asc;
--22、查询平均成绩大于等于85分的所有学生的学号、姓名和平均成绩
SELECT STU."s_id",STU."s_name",avg(s."score")
from STUDENT stu ,SC s
where STU."s_id"=s."s_id"
group by STU."s_id",STU."s_name"
having avg(s."score")>=85;
--23、查询课程名称为"数学"，且分数低于60分的学生姓名和分数
select STU."s_id",STU."s_name",s."score"
from STUDENT stu,COURSE c,SC s
where STU."s_id"=s."s_id" and c."c_id"=s."c_id"
and c."c_name"='数学' and s."score"<60;
--24、查询所有学生的课程及分数情况
select *
from STUDENT stu,SC s,COURSE c
where STU."s_id"=s."s_id" and c."c_id"=s."c_id"
order by STU."s_id",STU."s_name";
--25、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select STU."s_id",STU."s_name",c."c_name",s."score"
from STUDENT stu,SC s,COURSE c
where STU."s_id"=s."s_id" and c."c_id"=s."c_id"
and s."score">70;
--26、查询不及格的学生信息
select STU."s_id",STU."s_name",s."score"
from STUDENT stu,SC s
where STU."s_id"=s."s_id"
and s."score"<60;
--27、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select STU."s_id",STU."s_name"
from STUDENT stu,SC s
where STU."s_id"=s."s_id"
and s."c_id"=1 and s."score">80;
--28、求每门课程的学生人数
select c."c_id",c."c_name",count(*)
from COURSE c,SC s
where c."c_id"=s."c_id"
group by c."c_id",c."c_name";
--29、查询各学生的年龄
select s."s_id",s."s_name",trunc((sysdate-s."s_birthday")/365)+1
from STUDENT s;
--30、查询本周过生日的学生
select * FROM
STUDENT stu where to_char(STU."s_birthday",'iw')=to_char(sysdate ,'iw');
--31、查询本月过生日的学生
SELECT * FROM STUDENT s
where to_char(s."s_birthday",'mm')=to_char(sysdate,'mm');