设有关系EMP(ENO,ENAME,SALARY,DNO),其中各属性的含义依次为职工号、姓名、工资和所在部门号,以及关系DEPT(DNO,DNAME,MANAGER),其中各属性含义依次为部门号、部门名称、部门经理的职工号。试用SQL语句完成以下查询:
a) 列出各部门中工资不低于600元的职工的平均工资。
b) 请用SQL语句将“销售部”的那些工资数额低于600的职工的工资上调10%。
A:
SELECT eno,dno,AVG(salary) //求部门内平均工资
FROM emp
WHERE emp.eno IN (SELECT a.eno FROM emp a WHRER a.salary >= 600 ) //找出工资>600的员工
GROUP BY dno; //按照部门分组
A:
SELECT eno,dno,AVG(salary)
FROM emp
WHERE emp.eno IN (SELECT a.eno FROM emp a WHRER a.salary >= 600 )
GROUP BY dno;
B:
UPDATE emp
SET salary = salary *1.1
WHERE emp.dno = dept.dno AND dept.danme = "销售部" AND
eno IN (SELECT a.eno FROM emp a WHERE a.salary < 600);
a\ select avg(salary) from EMP where salary >= 600
b\ update EMP set EMP.salary = EMP.salary * 1.1 from EMP,DEPT where EMP.DNO = DEP.DNO and DEPT.DNAME = '销售部' and salary < 600
--a>
select sum(salary) / count(eno) from emp where salary > = 600
--b>
select ENO,ENAME,salary * 1.1 ,DNO from emp where salary < 600
and exists (select * from DEPT where emp.DNO = DNO and DNAME = '销售部')