一、java面试笔试SQL语句：测测你会多少（单表）
题目：系统中有一个表WCEmploy(职工号，姓名，部门名，工种，工资)
1、请写出建表语句
create table WCEmploy (
id int AUTO_INCREMENT PRIMARY KEY,
work_no int(255),
name varchar(55),
department varchar(55),
type varchar(20),
gz double
)
2、插入语句
insert into WCEmploy values(null,'1','张三','教学','老师','100');
3、查询语句
（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) 教师表
1，查询课程1的成绩比课程2的成绩高的所有学生的学号
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
2，查询平均成绩大于60分的同学的学号和平均成绩
(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
3，查询所有同学的学号、姓名、选课数、总成绩
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
4，查询姓“张”的老师的个数
select count(distinct tname) as 个数 from teacher where tname like '张%';
5，查询没学过“张三”老师课的同学的学号、姓名
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 != '张三');
6，查询同时学过课程1和课程2的同学的学号、姓名
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)
7，查询学过“李四”老师所教所有课程的所有同学的学号、姓名
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 = '李四')；
8，查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名
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;
9，查询所有课程成绩小于60分的同学的学号、姓名
select sno,sname from student where sno in (select distinct sno from sc where score < 60);
10，查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名
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);
