SQL之CRUD综合训练
来源：互联网 发布：为什么还原网络设置 编辑：程序博客网 时间：2024/05/02 06:44
--已知Oracle的Scott用户中提供了三个测试数据库表--名称分别为dept，emp，salgrade。使用SQL语言完成一下操作--1，查询20号部门的所有员工信息：SELECT * FROM EMP EWHERE E.DEPTNO = 20; --2，查询奖金（COMM）高于工资（SAL）的员工信息SELECT *FROM EMPWHERE COMM > SAL;--3，查询奖金高于工资20%的员工信息SELECT *FROM EMPWHERE COMM > SAL*0.2;--4，查询10号部门中工种为MANAGER和20号部门中--工种为CLERK的员工的信息SELECT * FROM EMP EWHERE (E.DEPTNO = 10 AND E.JOB = 'MANAGER')OR (E.DEPTNO = 20 AND E.JOB = 'CLERK');--5，查询所有工种不是MANAGER和CLERK,且工资大于--或等于2000的员工的详细信息SELECT * FROM EMPWHERE JOB NOT IN ('MANAGER','CLERK')AND SAL >= 2000;--6，查询没有奖学金低于100的员工信息SELECT * FROM EMP WHERE COMM IS NULL OR COMM < 100;--7，查询员工工龄大于或等于10年的员工信息SELECT * FROM EMPWHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12 > 10;--8，查询员工信息，要求以首字母大写的方式显示--所有员工的姓名SELECT INITCAP(ENAME) FROM EMP;--SELECT UPPER(SUBSTR(ENAME,1,1))||LOWER(SUBSTR(ENAME,2)) FROM EMP;--9，查询在2月份入职的所有员工信息SELECT * FROM EMPWHERE TO_CHAR(HIREDATE,'MM') = '02';--10，显示所有员工的姓名、入职的年份和月份--按入职日期所在的月份排序，若月份相同则按--入职的年份排序SELECT ENAME,TO_CHAR(HIREDATE,'YYYY') YEAR,TO_CHAR(HIREDATE,'MM') MONTHFROM EMPORDER BY MONTH,YEAR--11，查询‘JONES’员工及所有其直接、--间接下属员工的信息(递归算法)SELECT E.* FROM EMP ESTART WITH ENAME = 'JONES'CONNECT BY PRIOR EMPNO = MGR;--12，查询SCOTT员工及其直接、间接上级员工的信息SELECT E.*FROM EMP ESTART WITH ENAME = 'SCOTT'CONNECT BY PRIOR MGR = EMPNO;--13，查询从事同一种工作但不属于同一部门的员工信息SELECT A.ENAME,A.JOB,A.DEPTNO,B.ENAME,B.JOB,B.DEPTNOFROM EMP A,EMP BWHERE A.JOB = B.JOB AND A.DEPTNO != B.DEPTNO;--14，查询各个部门的详细信息以及部门人数、部门--平均工资SELECT D.DEPTNO,COUNT(E.EMPNO),AVG(E.SAL),D.DNAME,D.LOCFROM EMP E,DEPT DWHERE E.DEPTNO = D.DEPTNOGROUP BY D.DEPTNO,D.DNAME,D.LOC;--15，查询10号部门员工以及领导的信息SELECT *FROM EMPWHERE EMPNO IN( SELECT MGR FROM EMP WHERE DEPTNO = 10)OR DEPTNO = 10;--16，查询工资为某个部门平均工资的员工信息SELECT *FROM EMPWHERE SAL IN( SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO);--17，查询工资高于本部门平均工资的员工的信息SELECT * FROM EMP E1WHERE SAL > ( SELECT AVG(SAL) FROM EMP E2 WHERE E2.DEPTNO = E1.DEPTNO);--18，查询工资高于本部们平均工资的员工的信息SELECT E.*,A.AVGSALFROM EMP E,( SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO)AWHERE A.DEPTNO = E.DEPTNO AND E.SAL > A.AVGSAL;--19，统计各个工种的人数与平均工资SELECT COUNT(*),E.JOB,AVG(E.SAL)FROM EMP EGROUP BY E.JOB;--20，查询所有员工入职以来的工作期限，用--“**年**月**日”的形式表示SELECT ENAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)||'年'||TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,HIREDATE),12))||'月'||TRUNC(SYSDATE - ADD_MONTHS(HIREDATE,MONTHS_BETWEEN(SYSDATE, HIREDATE)))||'日'FROM EMP;--21,查询人数最多的部门信息SELECT * FROM DEPTWHERE DEPTNO IN( SELECT DEPTNO FROM ( SELECT COUNT(*) COUNT,DEPTNO FROM EMP GROUP BY DEPTNO ) WHERE COUNT IN ( SELECT MAX(COUNT) FROM ( SELECT COUNT(*) COUNT,DEPTNO FROM EMP GROUP BY DEPTNO ) ));--22，以树状结构查询所有员工与领导--之间的层次关系SELECT SUBSTR(SYS_CONNECT_BY_PATH(ENAME,'->'),3),LEVELFROM EMPSTART WITH MGR IS NULLCONNECT BY PRIOR EMPNO = MGR;--23，部门平均薪水最高的部门编号SELECT * FROM( SELECT * AVG(SAL) AVGSAL,DEPTNO FROM EMP GROUP BY DEPTNO ORDER BY AVGSAL DESC)WHERE ROWNUM = 1;--SELECT DEPTNO, AVG(SAL)FROM EMP GROUP BY DEPTNOHAVING AVG(SAL) =( SELECT MAX(AVG(SAL)) AVGSAL FROM EMP GROUP BY DEPTNO);--24，部门平均薪水最高的部门名称SELECT D.* FROM DEPT DWHERE DEPTNO IN( SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)= ( SELECT MAX(AVG(SAL)) AVGSLA FROM EMP GROUP BY DEPTNO ));--25，平均薪水最低的部门名称SELECT D.DNAME FROM DEPT DWHERE DEPTNO IN( SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)= ( SELECT MIN(AVG(SAL)) AVGSLA FROM EMP GROUP BY DEPTNO ));--26，平均薪水等级最低的部门的部门名称SELECT D.DNAMEFROM DEPT DWHERE D.DEPTNO IN( SELECT A.DEPTNO FROM ( SELECT E.DEPTNO FROM EMP E, SALGRADE S GROUP BY E.DEPTNO ORDER BY AVG(S.GRADE) )A WHERE ROWNUM = 1);--27，部门经理人中，薪水最低的部门名称SELECT DNAME FROM DEPTWHERE DEPTNO =( SELECT DEPTNO FROM ( SELECT DEPTNO FORM EMP WHERE JOB = 'MANAGER' GROUP BY DEPTNO ORDER BY MIN(SAL) ) WHERE ROWNUM = 1);--28，比普通员工的最高薪水还要高的经理人名称SELECT ENAME FROM EMP WHERE SAL >( SELECT MAX(SAL) FROM EMP WHERE JOB NOT IN ('MANAGER','PRESIDENT'))AND JOB = 'MANAGER'OR JOB = 'PRESIDENT';--29，查询所有员工工资大于1000的部门的信息SELECT * FROM DEPTWHEREDEPTNO IN( SELECT DEPTNO FROM EMP WHERE DEPTNO NOT IN ( SELECT DISTINCT DEPTNO FROM EMP WHERE SAL < 1000 ));--30，查询所有员工工资都大于1000的部门的信息--以及员工信息SELECT * FROM EMP E JOIN DEPT DON D.DEPTNO = E.DEPTNOAND D.DEPTNO IN( SELECT DEPTNO FROM EMP WHERE DEPTNO NOT IN ( SELECT DISTINCT DEPTNO FROM EMP WHERE SAL < 1000 ) );--31,查询所有工资都在900-3000之间的员工--所在部门的员工信息SELECT *FROM DEPTWHERE DEPTNO NOT IN( SELECT DEPTNO FROM EMP WHERE SAL NOT BETWEEN 900 AND 30000);--32，查询所有工资都在900-3000之间的--员工所在部门的员工信息SELECT * FROM EMP AWHERE A.DEPTNO IN( SELECT DISTINCT E.DEPTNO FROM EMP E WHERE E.SAL BETWEEN 900 AND 3000);--33，查询每个员工的领导所在部门的信息SELECT D.*FROM DEPT DWHERE D.DEPTNO IN( SELECT DISTINCT E2.DEPTNO FROM EMP E1，EMP E2 WHERE E1.EMPNO = E2.MGR);--34，查询30号部门中工资排序前3名的员工信息SELECT * FROM ( SELECT SAL FROM EMP WHERE DEPTNO = 30 ORDER BY SAL DESC)EWHERE ROWNUM < 4;--35，查询工作等级为2级，1985年以后入职的工作--地点为DALLAS的员工编号、姓名和工资SELECT E.ENAME,E.EMPNO,E.SALFROM EMP E,SALGRADE S,DEPT DWHERE (E.SAL BETWEEN S.LOSAL AND S.HISAL)AND S.GRADE = 2AND TO_CHAR(E.HIREDATE,'YYYY') > 1985AND E.DEPTNO = D.DEPTNOAND D.LOC = 'DALLAS';--36，将各部门员工的工资修改为该员工所在部门--平均工资加1000UPDATE EMP E SET SAL = 1000 + ( SELECT AVG(SAL) FROM EMP WHERE DEPTNO = E.DEPTNO);--37，删除重复部门，只留下一项DELECT FROM DEPT DWHERE ROWID !=( SELECT MIN(ROWID) FROM DEPT WHERE DNAME = D.DNAME AND LOC = D.LOC);--38，更新员工工资为它的主管工资，奖金UPDATE EMP ESET SAL = ( SELECT SAL FROM EMP WHERE EMPNO = E.MGR),COMM =( SELECT COMM FORM EMP WHERE EMPNO = E.MGR);--UPDATE EMP ESET (SAL,COMM) = ( SELECT SAL, COMM FROM EMP WHERE EMPNO = E.MGR);--某大学图书馆为了更好管理图书，使用Oracle--数据库建立了三个表：--CARD 借书卡表：CNO(卡号)，NAME（姓名），--CLASS(班级)--BOOKS图书表：BNO(书号),BNAME(书名),--AUTHOR(作者),PRICE(单价)，QUANTITY(库存册数)--BORROW结束记录表：CNO(借书卡号)，BNO(书号)，--RDATE(还书日期)--备注：限定每人每种书只能借一本：库存册数--随借书、还书而改变--39，写出建立BORROW表的SQL语句，要求定义--主码完整性约束CREATE TABLE BORROW( CNO NUMBER REFERENCES CARD(CNO), BNO NUMBER REFERENCES BOOKS(BNO), RDATE DATE, PRIMARY KEY(CNO,BNO));--40，假定在建BOOKS表时没有定义主码，写出--BOOKS表追加定义主码的语句ALTER TABLE BOOKS ADD PRIMARY KEY(BNO);--41，将CARD表的NAME最大列宽增加到10个字符--（假定原为6个字符）ALTER TABLE CARD MODIFY NAME VARCHAR2(10);--42，为表增加一列NAME（系名），可变长，--最大20个字符ALTER TABLE CARD ADD 系名 VARCHAR2(20);--43，找出借书超过5本的读者，输出借书--卡号所借图书册数SELECT CNO,COUNT(*)FROM BORROWGROUP BY CNO HAVING COUNT(*) > 5;--44，查询借阅了“水浒”一书的读者，输出姓名班级SELECT NAME, CLASS FROM CARDWHERE CNO IN( SELECT CNO FROM BORROW BW, BOOKS BK WHERE BW.BNO = BK.BNO AND BK.NAME = '水浒');--45，查询过期未还图书，输出借阅者（卡号）--书号及还书日期SELECT * FROM BORROW WHERE RDATE < SYSDATE;--46，查询书名包括’网络‘关键词的图书，输出--书号、书名、作者SELECT BNO, BNAME,AUTHORFORM BOOKSWHERE BNAME LIKE '%网络%';--47，查询现有图书中价格最高的图书，输出--书名及作者SELECT BNAME,AUTHOR FROM BOOKSWHERE PRICE =( SELECT MAX(PRICE) FROM BOOKS);--48，查询当前借了“计算方法”但没有借“计算方法--习题集“的作者，输出其借书卡号，并按卡号--降序排序输出SELECT A.CNOFROM BORROW A, BOOKS BWHERE A.BNO = B.BNO AND B.BNAME = '计算方法'AND A.CNO NOT IN( SELECT AA.CNO FORM BORROW AA,BOOKS BB WHERE AA.BNO = BB.BNO AND BB.BNAME = '计算方法习题集')ORDER BY A.CNO DESC;--49，查询当前同时借有”计算方法“和组合”组合数学“--两本数的作者，输出其借书卡号，并按卡号升序排列输出SELECT DISTINCT A.CNO FROM BORROW A,BOOKS BWHERE A.BNO = B.BNOAND B.BNAME IN('计算方法','组合数学')ORDER BY A.CNO;--50，将”c01“班同学所借图书的还期都延长一周UPDATE BORROW SET RDATE = RDATE + 7WHERE CNO IN( SELECT DISTINCT CNO FROM CARD WHERE CLASS = 'C01');--51，从BOOKS表中删除当前无人借阅图书记录DELETE FROM BOOKSBNO NOT IN( SELECT DISTINCT BK, BNO FROM BORROW BR,BOOKS BK WHERE BR.BNO = BK.BNO);--52，如果经常按书名查询图书信息，--请建立合适的索引CREATE INDEX INX_BOOKS_BNAME ON BOOKS(BNAME); --52，在BORROW表上建立一个触发器，完成如下功能：--如果读者借阅的书名是”数据库技术应用“--就将该读者的借阅记录保存在BORROW_SAVE表中--（注：ORROW_SAVE表结构同BORROW表）CREATE TRIGGER TR_SAVE ON BORROWFOR INSERT,UPDATE AS IF@@ROW COUNT > 0INSERT BORROW_SAVE SELECT I.* FROM INSERTED I,BOOKSWHERE I.BNO = B.BNO AND B.BNAME = N'数据库应用技术';--53，建立一个视图，显示”01班学生的借书信息：--（只要求显示姓名和书名）CREATE VIEW V_VIEWASSELECT A.NAME,B.BNAMEFROM BORROW AB,CARD A, BOOKS BWHERE AB.CNO = A.CNOAND AB.BNO = B.BNO AND A.CLASS = N'01班' 0 0
- SQL之CRUD综合训练
- SQL综合训练
- Struts2之Crud综合实例
- SQL常用函数之综合
- SQL常用函数之综合
- Linux程序设计综合训练之简易Web服务器
- java文件综合训练
- 结构体综合训练
- java基础 - 综合训练
- Layout组件综合训练
- PL/SQL 综合复习题之答案(1)
- PL/SQL 综合复习题之答案(2)
- 数据库系统开发综合训练总结1（关于eclipse连接sql server 2008 R2）
- Open SQL 增刪查改(CRUD)
- SQL基础CRUD语句
- sql 数据库CRUD语句
- CRUD---数据库SQL操作
- SQL的CRUD记录
- UISearchController搜索框禁止向上移动
- json 格式解析 及使用
- Microsoft CRM 2016 IFD配置
- poj1125解题报告【动态规划】【弗洛伊德算法-floyd算法】
- UVA 580
- SQL之CRUD综合训练
- 洛谷 P1378 油滴扩展
- Android进阶之intent传递大数据
- jQuery源码学习---插件接口的设计
- vim 全局替换命令
- shell按行读取文件的3种方法
- IOS runtime简单使用
- vim跳到文件头和文末结尾
- bzoj1612（dfs）
