Oracle版SQL语句练习

--学生表
Create table t_Student
(
Sno int PRIMARY KEY,    
Sname char(20)
)  
insert into t_Student values (1,'张三'); 
insert into t_Student values (2,'张a'); 
insert into t_Student values (3,'张b'); 
insert into t_Student values (4,'张c'); 
insert into t_Student values (5,'张d'); 
--课程表
Create table t_class
(
Cno int PRIMARY KEY,    
Cname varchar(20),
cteacher varchar(20)
)  

insert into t_class values (1,'语文','张老师'); 
insert into t_class values (2,'数学','李红'); 
insert into t_class values (3,'英语','李大大'); 
insert into t_class values (4,'Java','徐奖'); 
insert into t_class values (5,'科学','张三丰'); 
--成绩表
 Create table t_grade
(
sno int ,    
cno int ,
score number
)  

insert into t_grade values (1,1,80);
insert into t_grade values (1,2,70);
insert into t_grade values (1,3,60);
insert into t_grade values (1,4,80);
insert into t_grade values (1,5,99);
insert into t_grade values (2,1,50);
insert into t_grade values (2,2,57.4);
insert into t_grade values (3,1,89.8);
insert into t_grade values (3,2,40);
insert into t_grade values (3,3,50);
insert into t_grade values (3,4,71);
insert into t_grade values (4,4,81);
insert into t_grade values (5,3,84);
insert into t_grade values (5,4,81.3);

1.没有选‘’张老师‘’的课的学生信息

select s.sname from t_Student s where sno not in (
select g.sno from t_grade g where cno = (select cno from t_class where cteacher ='张老师'));  --第三步

select g.sno from t_grade g where g.cno =(
select c.cno from t_class c where c.cteacher='张老师'  --第二步，找到张老师编号cno的sno
);
select c.cno from t_class c where c.cteacher='张老师'; --第一步，先找‘张老师’对应的编号cno

2.列出每个学生的总分

select s.sname,sum(g.score) from t_student s join t_grade g on s.sno = g.sno
group by s.sname;

3.列出每个学生的平均分

select s.sname,avg(score) from t_student s join t_grade g on s.sno = g.sno 
group by s.sname;

4.列出每个学生的不及格门数

select s.sname,count(s.sname)as 不及格门数 from t_student s join t_grade g on s.sno = g.sno 
where g.score <60 
group by s.sname

只要select要查的有聚合函数，那就一定要group by

group by 后面有的字段，select后面才能跟，不然报错

5.列出两个以上学科不及格的学生名字和挂科学科数量

select s.sname,count(s.sname)as 不及格门数 from t_student s join t_grade g on s.sno = g.sno 
where g.score <60 
group by s.sname
having count(s.sname)>2

where子句中不能使用聚合函数，可写在having 子句中

sql语句的执行顺序为

 from子句

where 子句

group by 子句

having 子句

order by 子句

select 子句

6.查询语文成绩高于数学成绩的学生信息

select * from t_Student,
(select * from t_grade where cno = (select cno from t_class where cname ='语文')) ch,
(select * from t_grade where cno = (select cno from t_class where cname ='数学')) ma
where t_Student.Sno=ch.sno and t_Student.Sno=ma.sno
and ch.score >ma.score 

7.查询平均成绩大于等于70分的同学的学生编号和学生姓名和平均成绩

select s.sno,s.sname,avg(g.score) 平均成绩 from t_grade g join t_student s on g.sno = s.sno
group by s.sno,s.sname
having avg(g.score)>=70;

在select中出现的列必须全部出现在group by的后面

8.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select s.sno,s.sname,count(cno) 选课总数 ,sum(score)总分 from t_student s join t_grade g on s.sno=g.sno
group by s.sno,s.sname

9.查询"李"姓老师的数量

select count(cteacher) from t_class where cteacher like '李%'

10.查询参加了"张三丰"老师这门课的考试的学生信息

select s.sname,c.cname,c.cteacher,g.* from t_student s join t_grade g on s.sno=g.sno join t_class c on c.cno = g.cno
where c.cteacher='张三丰'

11.查询学过编号为"1"并且也学过编号为"4"的课程的同学的信息

select * from t_student s ,(select * from t_grade where cno=1) o,(select * from t_grade where cno=4) p
where s.sno=o.sno and s.sno=p.sno;

12.查询学过编号为"1"但是没有学过编号为"4"的课程的同学的信息

select * from t_student s
where s.sno not in (select sno from t_grade g where cno =4)
and sno in (select sno from t_grade g where cno =1) 

13.查询没有参加全所有科目的考试的学生信息（找出缺考的同学）

我想的是，一个学生如果五门考试都参加了就会有成绩，对应到t_score表，就是一个sno出现了5次就代表全部参加了考试，<5次的就代表你缺考

select s.sname,g.sno,count(g.sno) from t_grade g join t_student s on g.sno = s.sno
group by g.sno,s.sname
having count(g.sno)<5;

列出两门以上（含两门）不及格课程的学生姓名及其平均成绩

对题目进行拆解

1.列出学生的姓名和平均成绩

select s.sname,avg(g.score) from t_student s 
join t_grade g on s.sno = g.sno 
group by s.sname

2.列出学生的学号和不及格门数

select g.sno,count(g.sno) a from t_grade g where score<60
group by g.sno

3.列出学生学号和不及格门数大于等于2的

select g.sno,count(g.sno) a from t_grade g where score<60
group by g.sno
having count(g.sno)>=2

4.将1和3进行组合

select s.sname,avg(g.score) from t_student s 
join t_grade g on s.sno = g.sno 
where s.sno in (
select g.sno from t_grade g where score<60
group by g.sno
having count(g.sno)>=2
)
group by s.sname

列出既学过课程名为‘1’又学过课程名为‘2’的所有学生姓名

--1.找到对应课程名字的编号
select cno from t_class where cname='语文'
select cno from t_class where cname='Java'

--2.关键点在于一个表join两次
select s.sname from t_student s join t_grade g1 on s.sno = g1.sno join t_grade g2 on g2.sno = s.sno
where g1.cno = (
select cno from t_class where cname='语文'
)
and g2.cno = (
select cno from t_class where cname='Java'
)

列出“1”号课成绩比“2”号课成绩高的学生学号以及“1”“2”号课的成绩

--1.列出‘1’号课的编号对应的成绩和列出‘2’号课的编号对应的成绩
select score from t_grade where cno = (select cno from t_class where cname='语文');
select score from t_grade where cno = (select cno from t_class where cname='Java')

--2.联表查询
select distinct(g.sno),s2.score java,s1.score chinese 
from t_grade g 
join (select sno,score from t_grade where cno = (select cno from t_class where cname='语文')) s1
on g.sno = s1.sno 
join (select sno ,score from t_grade where cno = (select cno from t_class where cname='Java'))s2 
on g.sno=s2.sno
where s2.score >s1.score