一、java面試筆試SQL語句：測測你會多少（單表）

題目：系統(tǒng)中有一個表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語句查詢每個部門的總人數(shù)

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 ;

二、關聯(lián)查詢

        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，查詢所有同學的學號、姓名、選課數(shù)、總成績

select a.sno,a.sname,count(b.cno) as 選課數(shù)，sum(b.score) from student a, sc b where a.sno = b.sno  group b.sno ,a.sname

4，查詢姓“張”的老師的個數(shù)

select count(distinct tname) as 個數(shù) 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);