ORACLE百例试炼五

xglys

　　Oracle系列《五》：SQL综合练习
　　
　　【1】列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
　　select  job,count(*) from emp group by job having min(sal)>1500
　　【2】列出在部门'SALES'工作的员工姓名
　　先查询SALES的部门编号
　　SQL> SELECT deptno FROM dept WHERE dname='SALES';
　　SELECT ename FROM emp
　　WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');
　　【3】列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级
　　求出公司平均薪金
　　SQL> SELECT AVG(sal) FROM emp;
　　列出薪金高于平均薪金的所有员工
　　SQL> SELECT * FROM emp
　　WHERE sal>(SELECT AVG(sal) FROM emp);
　　查询所在部门信息
　　SQL> SELECT e.*,d.dname,d.loc FROM emp e,dept d
　　WHERE sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno;
　　查询上级领导
　　SQL>
　　SQL>SELECT e.empno,e.ename,m.empno,m.ename,d.deptno,d.dname,d.loc
　　FROM emp e,dept d,emp m
　　WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr = m.empno(+);
　　求出工资的工资等级
　　SQL> SELECT
　　e.empno,e.ename,s.grade,m.empno,m.ename,d.deptno,d.dname,d.loc
　　FROM emp e,dept d,emp m,salgrade s
　　WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+)
　　AND e.sal BETWEEN s.losal AND s.hisal;
　　【4】列出与"SCOTT"从事相同工作的所有员工及部门名称
　　找出与SCOTT相同工作的雇员，但不能包括自己
　　SQL> SELECT empno,ename,job FROM emp
　　WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';
　　与部门表关联，查询部门名称
　　SQL> SELECT e.empno,e.ename,e.job,d.dname FROM emp e,dept d
　　WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT';
　　AND e.deptno=d.deptno;
　　【5】列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金 （本题无结果，薪金都和30号部门不一样）
　　列出部门30员工的薪金
　　SQL> SELECT sal FROM emp WHERE deptno=30;
　　上述条件作为子查询，这里注意上述结果是返回多条记录的，所以要用IN
　　SQL> SELECT ename,sal FROM emp
　　WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND deptno!=30;
　　【6】列出在每个部门工作的员工数量、平均工资和平均服务期限
　　每个部门的员工数量：可求出部门名称
　　SQL> SELECT d.dname,COUNT(e.empno)
　　FROM emp e,dept d
　　WHERE e.deptno=d.deptno  GROUP BY d.dname;
　　求平均工资和服务年限
　　SQL> SELECT d.dname,COUNT(e.empno),AVG(e.sal),AVG(MONTH_BETWEENS(sysdate,hiredate)/12) year
　　FROM emp e,dept d
　　WHERE e.deptno=d.deptno GROUP BY d.dname;
　　【7】列出所有部门的详细信息和部门人数
　　列出所有部门人数
　　SQL> SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno;
　　将以上查询当做一张临时表
　　SQL> SELECT d.*,ed.cou
　　FROM dept d,(SELECT deptno,COUNT(empno) cou   from emp GROUP BY deptno) ed
　　WHERE d.deptno = ed.deptno;
　　但是以上没有40部门的信息，则应该使用NVL和左连接操作
　　SQL> SELECT d.*,NVL(ed.cou,0)
　　FROM dept d,(SELECT deptno,COUNT(empno) cou FROM emp   GROUP BY deptno) ed
　　WHERE d.deptno = ed.deptno(+);
　　【8】列出各种工作的最低工资及从事该工作的雇员姓名
　　按照工作分组，使用MIN函数求出最低工资
　　SQL> SELECT job,MIN(sal) FROM emp GROUP BY job;
　　按照工资查询雇员信息
　　SQL> SELECT * FROM emp
　　WHERE sal IN (SELECT MIN(sal) FROM emp   GROUP BY job);
　　【9】列出各个部门 MANAGER的最低薪金
　　SQL> SELECT deptno,MIN(sal) FROM emp
　　WHERE job='MANAGER' GROUP BY deptno;
　　【10】列出所有员工的年工资，按年薪从低到高排序
　　SQL> SELECT ename,(sal+NVL(comm,0))*12 income
　　FROM emp ORDERY BY income;
　　【11】求出部门名称中，带'S'字符的部门员工，工资合计，部门人数
　　使用模糊查询，获得部门编号
　　SQL> SELECT deptno FROM dept WHERE dname LIKE '%S%';
　　上面作为子查询
　　SQL> SELECT deptno,SUM(sal),COUNT(empno) FROM emp
　　WHERE deptno IN(SELECT deptno FROM dept WHERE dname LIKE '%S%') GROUP BY deptno;
　　【12】给任职10年以上的人加薪10%
　　SQL> UPDATE emp SET sal=sal+(sal*0.1)
　　WHERE MONTH_BETWEENS(sysdate,hiredate)/12>10;
　　【综合题】有个学生运动会比赛信息的数据库，需要建立如下的表，结构如下 运动员sporter: (运动员编号 sporterid,运动员姓名 name,运动员性别 sex,所属系号 department)
　　项目item: (项目编号itemid,项目名称itemname,项目比赛地点 location)
　　成绩grade: (运动员编号 sporterid,项目编号itemid,积分mark)
　　1、建表要求
　　定义各个表的主外键约束
　　运动员姓名和所属系别不能为空
　　积分要么控制，要么为6,4,2,0,
　　CREATE TABLE sporter(
　　sporterid  NUMBER(4) PRIMARY KEY,
　　name    VARCHAR2(20) NOT NULL,
　　sex    VARCHAR2(2) NOT NULL,
　　department VARCHAR2(20) NOT NULL,
　　CONSTRAINT sporter_sex_CK CHECK(sex IN('M','F'))
　　);
　　CREATE TABLE item(
　　itemid VARCHAR2(4)PRIMARY KEY,
　　itemname VARCHAR2(20) NOT NULL,
　　location VARCHAR2(20) NOT NULL );
　　CREATE TABLE grade(
　　sporterid NUMBER(4),
　　itemid VARCHAR2(20),
　　mark NUMBER(2),
　　CONSTRAINT sporter_grade_fk FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,
　　CONSTRAINT item_grade_fk FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,
　　CONSTRAINT grade_mark_CK CHECK(mark IN(6,4,2,0))
　　);
　　记录可自己视情况插入，完成以上的查询语句
　　1、求出目前总积分最高的系名，及其积分
　　SQL> SELECT s.department,SUM(g.mark) sum FROM sporter s,grade g
　　WHERE s.sporterid=g.sporterid GROUP BY s.department ORDER BY sum DESC;
　　当然上述查出来的结果是排序的多条记录，使用ROWNUM最为简便
　　SQL> SELECT * FROM (
　　SELECT s.department,SUM(g.mark) sum  FROM sporter s,grade g
　　where s.sporterid = g.sporterid  GROUP BY s.department  ORDER BY sum DESC)
　　WHERE ROWNUM=1;
　　2、找出场地为'S1'，进行比赛的各项目名称及其冠军的姓名
　　首先确定一操场中的全部项目和每个项目的最高成绩
　　SQL> SELECT i.itemname,s.name,g.mark FROM item i,grade g,sporter s
　　WHERE i.location='S1' AND i.itemid = g.itemid AND s.sporterid = g.sporterid;
　　根据上述结果求出最高分
　　SQL> SELECT i.item,s.name,g.mark FROM item i,grade g,sporter s
　　WHERE i.location='S1' AND i.itemid = g.itemid AND s.sporterid AND g.mark=6;
　　3、找出参加了wilson所参加过的项目的其他同学的姓名
　　找到wilson参加过的项目编号
　　SQL> SELECT g.itemid FROM sporter s,grade g
　　WHERE s.sporterid=g.sporterid AND s.name='wislon';
　　SELECT DISTINCT s.name FROM sporter s, grade g
　　WHERE s.sporterid=g.sporterid AND s.name'wilson';
　　AND g.itemid IN
　　(SELECT g.itemid FROM sporter s,grade g WHERE s.sporterid=g.sporterid AND s.name='wilson');
　　4、wilson使用了违禁药物，成绩记为0
　　SQL> UPDATE grade SET makr=0
　　WHERE sporterid=(SELECT sporterid FROM sporter WHERE name='wilson');
　　5、删除S2项目
　　SQL> DELETE FROM item WHERE itemid='S2'
　　到此ORACLE基础语法等训练结束了，后续我们还将进行ORACLE体系结构的学习