java面试精典SQL语句：测测你会多少

create table WCEmploy (
   id int AUTO_INCREMENT PRIMARY KEY,
   work_no int(255),
   name varchar(55),
   department varchar(55),
   type varchar(20),
   gz double
)

insert into WCEmploy values(null,'1','张三','教学','老师','100');

（1）、请用一个SQL语句查询每个部门的总人数

select count(id) from wcemploy group by department;

（2）、请用一个SQL语句查询出不同部门的担任“钳工”的职工平均工资

select department , avg(gz) from wcemploy where type='钳工' group by department;

（3）、请用一个SQL语句查询出不同部门的担任“钳工”的职工平均工资高于2000的部门

select department ,avg(gz) from wcemploy where type='钳工' group by department having  avg(gz) > 2000;

（4）、请用一个SQL语句查询每个部门低于平均工资的员工信息

select department,gz from wcemploy w,(select department,avg(gz) as avgGz from wcemploy group by department) t on w.department=t.department where w.gz<t.avgGz ;

        student(sno,sname,sage,ssex)学生表
        course(cno,cname,tno) 课程表
        sc(sno,cno,score) 成绩表
        teacher(tno,tname) 教师表

  select sno from (select score ,sno from sc where cno = 1)a,(select score,sno from sc where cno = 2)b where a.score > b.score and a.sno = b.sno

(1)select sno,avg(score) as avgScore from sc group by sno having avg(score) > 60
(2)
select a.sno as "学号", avg(a.score) as "平均成绩" 
from
(select sno,score from sc) a 
group by sno having avg(a.score)>60

select a.sno,a.sname,count(b.cno) as 选课数，sum(b.score) from student a, sc b where a.sno = b.sno  group b.sno ,a.sname

select count(distinct tname) as 个数 from teacher where tname like '张%';

select sno,sname from student where sno in (select sc.sno from sc,course,teacher where sc.cno = course.cno and course.tno = teacher,tno and teacher.tname != '张三');

select sno,sname from student where sno in (select sno from sc where cno = 1) and sno in  (select sno from sc where cno = 2)

select a.sno, a.sname from student a, sc b
where a.sno = b.sno and b.cno in
(select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四')；

select a.sno, a.sname from student a,
(select sno, score from sc where cno = 1) b,
(select sno, score from sc where cno = 2) c
where b.score > c.score and b.sno = c.sno and a.sno = b.sno;

select sno,sname from student where sno in (select distinct sno from sc where score < 60);

select distinct a.sno, a.sname
from student a, sc b
where a.sno <> 1 and a.sno=b.sno and
b.cno in (select cno from sc where sno = 1);