sql练习

select Dept from emp group by salary having avg(salary)>2000
S left join SC left join C where

---

查询"95031"班的学生人数
select count(1) as stu_num from students where class=‘95031’;

查询每个班的学生人数
select class,count(class) from students group by class;

查询至少有两名男生的班号
select class,count(1) as boy_count from students
where ssex=‘男’ group by class having boy_count>=2;

查询最高分
select max(degree) from scores;
查询最低分
select min(degree) from scores;

查询scores表中的最高分的学生学号和课程号
(1)通过嵌套子查询
select sno,cno from scores
where degree = (select max(degree) from scores);
(2)通过排序取第一条记录
select sno,cno from scores order by degree desc limit 1;

查询’3-105’号课程的平均分
select avg(degree),round(avg(degree),2)
from scores where cno=‘3-105’;

查询各科的平均分
select cno,round(avg(degree),2)
from scores group by cno;

课程号"3-105"的倒数最后3名学员排行
select * from
(select sno,degree from scores s
where sno=‘3-105’ order by degree limit 3) s
order by degree desc;

课程"计算机导论"的前3名学员排行
select sno,degree from scores
where cno=(select cno from courses where cname=‘计算机导论’)
order by degree desc limit 3;

课程号"3-105"的前3名学员排行
select sno,degree from scores where cno=‘3-105’ order by degree desc limit 3;

各科最好成绩
select cno,max(degree) from scores group by cno;

行列转置
select
max(case cno when ‘3-105’ then degree else 0 end) as ‘3-105’;
max(case cno when ‘3-245’ then degree else 0 end) as ‘3-245’;
max(case cno when ‘6-106’ then degree else 0 end) as ‘6-106’;
max(case cno when ‘6-166’ then degree else 0 end) as ‘6-166’;
from scores;

查询课程对应的女老师姓名,职称,所属系
(1)先连接数据后过滤数据,假如数据量很大,中间过程要构建巨大的临时表
select c.cname,t.tname,t.prof,t.depart from
teachers t left join course c
on t.tno = c.tno and t.sex = ‘女’;
(2)先过滤数据,构建的中间结果集自然就变的很小,所占内存,所加工的时间所网络传输的时间都变少了,所以效率高
select c.cno,c.cname,t.tno,t.tname,t.prof,t.depart from
(select * from teachers where tsex=‘女’) t
left join
(select * from courses) c
t.tno = c.tno;

查询课程对应的老师姓名,职称,所属系
select c.cname,t.tname,t.prof,t.depart
from teachers t left join courses c
on t.tno = c.tno;
select c.cname,t.tname,t.prof,t.depart
from (select * from teachers) t left join (select * from courses) c
on t.tno = c.tno;
第一种方式简洁,第二种方式高效

查询各门课程的最高分同学的sno,cno,degree
(1)
select sno,cno,degree from scores
where degree in (select max(degree) from scores group by cno);
(2)
select s.sno,g.cno,s.degree from
(select cno,max(degree) maxd from scores group by cno) g
left join
(select sno,cno,degree from scores) s
on g.cno = s.cno and g.maxd = s.degree;
一般认为数据少时使用in,数据多时使用join

查询男教师及其所上的课程
select teachers.tno,tname,cname from
teachers inner join courses
on teacher.tno = courses.tno and teachers.tsex = ‘男’;

以班级和年龄从大到小的顺序查询student表中的全部记录
select * from students order by class desc,sbirthday desc;
select * from students order by class desc,(year(now())-year(sbirthday)) desc;

查询学生中最大和最小的出生年月
select min(sbirthday),max(sbirthday) from students;

查询学生的姓名和年龄
select sname,year(now())-year(sbirthday) as sage from students;

查询同名的同学记录
select * from students where sname
in (select sname from students group by sname having count(sname)>1);

查询所有未讲课的教师的tname和depart
select tname,depart from teachers
where tno not in (select tno from courses)

查询所有任课老师的tname和depart
select tname,depart from teachers tno in (select distinct tno from courses);

查询所有"女"教师和"女"同学的name,sex和birthday
select tname,tsex,tbirthday from teachers where tsex=‘女’ union select sname,ssex,sbirthday from students where ssex=‘女’;

查询所有教师和同学的name,sex和birthday
select tname as name,tsex as sex,tbirthday as birthday from teachers union
select sname as name,ssex as sex,sbirthday as birthday from students;

查询存在有85分以上成绩的课程cno
select distinct cno from courses where degree>85;