挺基础的。只不过,我荒废了好长时间都忘得差不多了大伙给做一下。帮我复习sql了。
Department表
deptno name
101 commerce
102 ITEmployee表
empno salary
1 2000
2 5000
3 1000
4 6000dept_emp表
deptno empno
101 10
101 30
102 20
102 401 选出列表 empNo, salary, deptName
2 选出每个部门的最低工资
3 选出每个部门的工资总和
4 选出工资总和大于4000的部门
Department表
deptno name
101 commerce
102 ITEmployee表
empno salary
1 2000
2 5000
3 1000
4 6000dept_emp表
deptno empno
101 10
101 30
102 20
102 401 选出列表 empNo, salary, deptName
2 选出每个部门的最低工资
3 选出每个部门的工资总和
4 选出工资总和大于4000的部门
解决方案 »
- java ftp 访问总是走不下去,怎么回事哦?
- 一个JavaSE的题,大家进来看看
- java 的 string.value() to.String() (String) 这3种强转方式有什么区别啊??
- 谁又封装好的类似MySQL中时间选择列表的封装类,高分求!
- 提一个面向对象开发的百思不得其解的问题
- 怎样用java程序打开WinXP的拨号连接对话框?
- 如何在servlet中取得当前webapp 根目录url的
- 这个卖票程序在统计各个站点时为什么会出错?
- 请问?这是什么错误,在jbuilder7的gridcontrol中想要写数据时出现的
- 我想用Java开发类似与CGI,ASP的服务器端程序,用什么好呢?
- 如何删除二维数组的一行?
- android setAdapter出错
2 select min(b.salary) ,a.name deptName from Department a ,Employee b ,dept_emp c where a.deptno=c.deptno and b.empno=c.empno group by a.name
3 select sum(b.salary) ,a.name deptName from Department a ,Employee b ,dept_emp c where a.deptno=c.deptno and b.empno=c.empno group by a.name
4 select sum(b.salary) ,a.name deptName from Department a ,Employee b ,dept_emp c where a.deptno=c.deptno and b.empno=c.empno group by a.name having sum(b.salary)>4000
--选出列表 empNo, salary, deptName
select b.empno as empNo,b.salary,c.name as deptName from dept_emp a left join Employee b on a.empno=b.empno left join Department c on a.deptno=c.deptno--选出每个部门的最低工资
select deptName,min(salary) as salary from (select b.empno as empNo,b.salary,c.name as deptName from dept_emp a left join Employee b on a.empno=b.empno left join Department c on a.deptno=c.deptno) b group by deptName--选出每个部门的工资总和
select deptName,sum(salary) as salary from (select b.empno as empNo,b.salary,c.name as deptName from dept_emp a left join Employee b on a.empno=b.empno left join Department c on a.deptno=c.deptno) b group by deptName--选出工资总和大于4000的部门
select deptName from (select b.empno as empNo,b.salary,c.name as deptName from dept_emp a left join Employee b on a.empno=b.empno left join Department c on a.deptno=c.deptno) b group by deptName having sum(salary) > 4000