java面试题网

普通会员

15

帖子

1

回复

33

积分

楼主
发表于 2018-06-06 18:04:30 | 查看: 404| 回复: 0

1853000135a852d4497c.jpg

题目:

  1. 分别统计每位员工的应发工资总和,并且只显示工资总和在5000元以上的信息

  2. 统计worker表中各部门党员的人数,并且显示党员人数在2个人以上的相关信息

  3. 查询职工的职工号/姓名/部门名

  4. 查询2011年2月份各部门职工的工资。

  5. 求出各个部门党员的人数,要求显示部门名和党员人数

  6. 显示所有平均工资高于2600的部门名和对应的平均工资

  7. 查询worker与study表的左外连接、右外连接、完全外连接、交叉连接

  8. 统计salary表的工资总和

  9. 利用公用表表达式,查询出‘人事处’职工的职工号、职工姓名、部门名

  10. 利用公用表表达式,查询出‘人事处’职工的职工号、职工姓名、部门名、发工资日期、实发工资

答案:

1. 查询如下:

select wid as 职工, SUM(totalsalary) as 工资总和 from salary group by wid having SUM(totalsalary) >= 5000

2. 查询如下:

select depid as 部门, count() as 党员人数 from worker where wparty = '是' group by depid having count() >= 1

3. 方法1:在where中指定内部连接条件

select worker.wid 职工号, worker.wname 姓名, depart.dname 部门名 from worker, depart where worker.depid = depart.did order by worker.wid desc

方法2:在from中指定内部连接条件

select wid 职工号, wname 姓名, dname 部门名 from worker inner join depart on worker.depid = depart.did

4. 方法1:

select worker.wid as 职工号, worker.wname as 姓名, depart.dname as 部门名, salary.actualsalary as '2011年2月份工资'

from worker, depart, salary

where worker.depid = depart.did and worker.wid = salary.wid and year(salary.sdate) = 2011 and MONTH(salary.sdate) = 2 order by depart.dname

方法2:

select worker.wid 职工号, worker.wname 姓名, depart.dname 部门名, salary.actualsalary '2011年2月份工资'

from worker

inner join depart on worker.depid = depart.did

inner join salary on worker.wid = salary.wid

where YEAR(salary.sdate) = 2011 and MONTH(salary.sdate) = 2

5. select depart.dname 部门名, COUNT(*) 党员人数 from worker inner join depart on depart.did = worker.depid where worker.wparty = '是' group by depart.dname

6. select depart.dname 部门名, AVG(salary.actualsalary) 平均工资 from worker inner join depart on depart.did = worker.depid inner join salary on worker.wid = salary.wid group by depart.dname having AVG(salary.actualsalary) > 2600

7. 左外连接

select * from worker left outer join study on worker.wid = study.wid

右外连接

select * from worker right outer join study on worker.wid = study.wid

完全外连接

select * from worker full outer join study on worker.wid=study.wid

交叉连接

select * from worker cross join study

8. select * from salary union select '小计', NULL, SUM(totalsalary), SUM(actualsalary) from salary

9. with result(wid, wname, dname) as

( select wid, wname, dname from worker

inner join depart on worker.depid = depart.did

where depart.dname = '人事处' )

select * from result

10.with result(wid, wname, dname) as

( select wid, wname, dname from worker

inner join depart on worker.depid = depart.did

where depart.dname = '人事处' )

select result.wid 职工号, wname 职工名, dname 部门名, salary.sdate 发工资日期, salary.actualsalary 实发工资

from result inner join salary on result.wid = salary.wid



您需要登录后才可以回帖 登录 | 立即注册

java面试题网无聊看看网与java建站系统提供技术支持V2.1 网站地图 © 2016-2018