ORACLE百例试炼五

发布时间:2020-06-02 06:33:28 作者:月幕
来源:网络 阅读:794

Oracle系列《五》:SQL综合练习


【1】列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

select  job,count(*) from emp group by job having min(sal)>1500


【2】列出在部门'SALES'工作的员工姓名

<1>先查询SALES的部门编号 

SQL> SELECT deptno FROM dept WHERE dname='SALES'; 

<2>SELECT ename FROM emp 

WHERE deptno=(SELECT deptno FROM dept WHERE dname='SALES');


【3】列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级 

<1>求出公司平均薪金 

SQL> SELECT AVG(sal) FROM emp;

 

<2>列出薪金高于平均薪金的所有员工 

SQL> SELECT * FROM emp  

WHERE sal>(SELECT AVG(sal) FROM emp); 


<3>查询所在部门信息 

SQL> SELECT e.*,d.dname,d.loc FROM emp e,dept d 

WHERE sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno; 


<4>查询上级领导 

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(+); 


<5>求出工资的工资等级 

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"从事相同工作的所有员工及部门名称 

<1>找出与SCOTT相同工作的雇员,但不能包括自己 

SQL> SELECT empno,ename,job FROM emp 

WHERE job=(SELECT job FROM emp WHERE ename='SCOTT') AND ename!='SCOTT'; 


<2>与部门表关联,查询部门名称 

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号部门不一样

<1>列出部门30员工的薪金 

SQL> SELECT sal FROM emp WHERE deptno=30; 


<2>上述条件作为子查询,这里注意上述结果是返回多条记录的,所以要用IN 

SQL> SELECT ename,sal FROM emp 

WHERE sal IN(SELECT sal FROM emp WHERE deptno=30) AND deptno!=30;


【6】列出在每个部门工作的员工数量、平均工资和平均服务期限 

<1>每个部门的员工数量:可求出部门名称 

SQL> SELECT d.dname,COUNT(e.empno) 

FROM emp e,dept d 

WHERE e.deptno=d.deptno  GROUP BY d.dname;

 

<2>求平均工资和服务年限 

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】列出所有部门的详细信息和部门人数 

<1>列出所有部门人数 

SQL> SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno;

 

<2>将以上查询当做一张临时表 

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】列出各种工作的最低工资及从事该工作的雇员姓名 

<1>按照工作分组,使用MIN函数求出最低工资 

SQL> SELECT job,MIN(sal) FROM emp GROUP BY job;

 

<2>按照工资查询雇员信息 

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'字符的部门员工,工资合计,部门人数 

<1>使用模糊查询,获得部门编号 

SQL> SELECT deptno FROM dept WHERE dname LIKE '%S%'; 


<2>上面作为子查询 

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、建表要求 

<1>定义各个表的主外键约束 

<2>运动员姓名和所属系别不能为空 

<3>积分要么控制,要么为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',进行比赛的各项目名称及其冠军的姓名 

<1>首先确定一操场中的全部项目和每个项目的最高成绩

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;  

<2>根据上述结果求出最高分 

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所参加过的项目的其他同学的姓名 

<1>找到wilson参加过的项目编号 

SQL> SELECT g.itemid FROM sporter s,grade g 

WHERE s.sporterid=g.sporterid AND s.name='wislon'; 

 

<2>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体系结构的学习

推荐阅读:
  1. 【Oracle Database】Oracle RAC(五):RAC Database
  2. Oracle备份与恢复(五)

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle ocp acle

上一篇:分布式框架开发环境部署

下一篇:【Linux】用户组、用户操作

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》