Oracle下SQL基本操作(三)(7)
来源:未知 责任编辑:责任编辑 发表时间:2013-11-26 22:16 点击:次
SELECT EMP.DEPTID, AVG(EMP.SALARY) FROM EMPLOYEE EMP
WHERE EMP.QUALID = '1'
GROUP BY EMP.DEPTID
----统计每种学位的平均工资
SELECT EMP.QUALID,AVG(EMP.SALARY) FROM EMPLOYEE EMP
GROUP BY EMP.QUALID
---统计每个部门的总年薪
SELECT EMP.DEPTID,SUM(COALESCE(EMP.SALARY + EMP.COMMISSION, EMP.SALARY,EMP.COMMISSION,0)) * 12 总年薪 FROM EMPLOYEE EMP
GROUP BY EMP.DEPTID
SELECT * FROM EMPLOYEE;
---统计每个部门 员工资金大于1000 的最小工资 再按
SELECT EMP.DEPTID ,MIN(EMP.SALARY) 最小工资
FROM EMPLOYEE EMP
WHERE EMP.COMMISSION > 1000
GROUP BY EMP.DEPTID
--- ORDER BY 最小工资 desc;
----员工姓名 部门名
SELECT EMP.NAME,DEP.DEPTNAME
FROM EMPLOYEE EMP,DEPARTMENT DEP
WHERE EMP.DEPTID = DEP.DEPTID
----员工姓名 学位名
SELECT EMP.NAME,QUA.QUALNAME
FROM EMPLOYEE EMP,QUAFICATION QUA
WHERE EMP.QUALID = QUA.QUALID
-----SQL 1999新连接标准
SELECT EMP.EMPLOYEEID, DEPT.DEPTID
FROM EMPLOYEE EMP
CROSS JOIN DEPTMENT DEPT;
SELECT EMP.EMPLOYEEID, DEPTID
FROM EMPLOYEE EMP
NATURAL JOIN DEPTMENT DEPT;
SELECT EMP.EMPLOYEEID, DEPTID
FROM EMPLOYEE EMP
JOIN DEPTMENT DEPT USING (DEPTID);
----教学部的员工姓名 ,学位名,工资等级 SELECT * FROM EMPLOYEE;
--- SELECT * FROM EMPLOYEE FOR UPDATE
SELECT EMP.NAME,QUA.QUALNAME, LEV.LEVELNO,QUA.QUALID
FROM EMPLOYEE EMP, QUAFICATION QUA, EMPLEVEL LEV , DEPARTMENT DEP
WHERE DEP.DEPTNAME = '教学部'
AND EMP.QUALID = QUA.QUALID
AND EMP.SALARY BETWEEN LEV.LOWSALARY AND LEV.LOWSALARY;
--不相关子查询
---薪水高于0004号员工的薪水的员工,他的ID
SELECT OUTEMP.EMPLOYEEID
FROM EMPLOYEE OUTEMP
WHERE OUTEMP.SALARY >
(SELECT INEMP.SALARY
FROM EMPLOYEE INEMP
WHERE INEMP.EMPLOYEEID = '0004');
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 更多>>