--学生表
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