- --已知Oracle的Scott用户中提供了三个测试数据库表
- --名称分别为dept,emp,salgrade。使用SQL语言完成一下操作
- --1,查询20号部门的所有员工信息:
- SELECT *
- FROM EMP E
- WHERE E.DEPTNO = 20;
- --2,查询奖金(COMM)高于工资(SAL)的员工信息
- SELECT *
- FROM EMP
- WHERE COMM > SAL;
- --3,查询奖金高于工资20%的员工信息
- SELECT *
- FROM EMP
- WHERE COMM > SAL*0.2;
- --4,查询10号部门中工种为MANAGER和20号部门中
- --工种为CLERK的员工的信息
- SELECT *
- FROM EMP E
- WHERE (E.DEPTNO = 10 AND E.JOB = 'MANAGER')
- OR (E.DEPTNO = 20 AND E.JOB = 'CLERK');
- --5,查询所有工种不是MANAGER和CLERK,且工资大于
- --或等于2000的员工的详细信息
- SELECT * FROM EMP
- WHERE JOB NOT IN ('MANAGER','CLERK')
- AND SAL >= 2000;
- --6,查询没有奖学金低于100的员工信息
- SELECT *
- FROM EMP
- WHERE COMM IS NULL
- OR COMM < 100;
- --7,查询员工工龄大于或等于10年的员工信息
- SELECT *
- FROM EMP
- WHERE 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 EMP
- WHERE TO_CHAR(HIREDATE,'MM') = '02';
- --10,显示所有员工的姓名、入职的年份和月份
- --按入职日期所在的月份排序,若月份相同则按
- --入职的年份排序
- SELECT ENAME,TO_CHAR(HIREDATE,'YYYY') YEAR,
- TO_CHAR(HIREDATE,'MM') MONTH
- FROM EMP
- ORDER BY MONTH,YEAR
- --11,查询‘JONES’员工及所有其直接、
- --间接下属员工的信息(递归算法)
- SELECT E.*
- FROM EMP E
- START WITH ENAME = 'JONES'
- CONNECT BY PRIOR EMPNO = MGR;
- --12,查询SCOTT员工及其直接、间接上级员工的信息
- SELECT E.*
- FROM EMP E
- START WITH ENAME = 'SCOTT'
- CONNECT BY PRIOR MGR = EMPNO;
- --13,查询从事同一种工作但不属于同一部门的员工信息
- SELECT A.ENAME,A.JOB,A.DEPTNO,
- B.ENAME,B.JOB,B.DEPTNO
- FROM EMP A,EMP B
- WHERE A.JOB = B.JOB AND A.DEPTNO != B.DEPTNO;
- --14,查询各个部门的详细信息以及部门人数、部门
- --平均工资
- SELECT D.DEPTNO,COUNT(E.EMPNO),AVG(E.SAL),
- D.DNAME,D.LOC
- FROM EMP E,DEPT D
- WHERE E.DEPTNO = D.DEPTNO
- GROUP BY D.DEPTNO,D.DNAME,D.LOC;
- --15,查询10号部门员工以及领导的信息
- SELECT *
- FROM EMP
- WHERE EMPNO IN
- (
- SELECT MGR
- FROM EMP
- WHERE DEPTNO = 10
- )
- OR DEPTNO = 10;
- --16,查询工资为某个部门平均工资的员工信息
- SELECT *
- FROM EMP
- WHERE SAL IN
- (
- SELECT AVG(SAL)
- FROM EMP
- GROUP BY DEPTNO
- );
- --17,查询工资高于本部门平均工资的员工的信息
- SELECT *
- FROM EMP E1
- WHERE SAL >
- (
- SELECT AVG(SAL)
- FROM EMP E2
- WHERE E2.DEPTNO = E1.DEPTNO
- );
- --18,查询工资高于本部们平均工资的员工的信息
- SELECT E.*,A.AVGSAL
- FROM EMP E,
- (
- SELECT DEPTNO,AVG(SAL) AS AVGSAL
- FROM EMP
- GROUP BY DEPTNO
- )A
- WHERE A.DEPTNO = E.DEPTNO
- AND E.SAL > A.AVGSAL;
- --19,统计各个工种的人数与平均工资
- SELECT COUNT(*),E.JOB,AVG(E.SAL)
- FROM EMP E
- GROUP 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 DEPT
- WHERE 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),
- LEVEL
- FROM EMP
- START WITH MGR IS NULL
- CONNECT 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 DEPTNO
- HAVING AVG(SAL) =
- (
- SELECT MAX(AVG(SAL)) AVGSAL
- FROM EMP
- GROUP BY DEPTNO
- );
- --24,部门平均薪水最高的部门名称
- SELECT D.*
- FROM DEPT D
- WHERE 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 D
- WHERE 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.DNAME
- FROM DEPT D
- WHERE 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 DEPT
- WHERE 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 DEPT
- WHERE
- DEPTNO 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 D
- ON D.DEPTNO = E.DEPTNO
- AND D.DEPTNO IN
- (
- SELECT DEPTNO
- FROM EMP
- WHERE DEPTNO NOT IN
- (
- SELECT DISTINCT DEPTNO
- FROM EMP
- WHERE SAL < 1000
- )
- );
- --31,查询所有工资都在900-3000之间的员工
- --所在部门的员工信息
- SELECT *
- FROM DEPT
- WHERE DEPTNO NOT IN
- (
- SELECT DEPTNO
- FROM EMP
- WHERE SAL NOT BETWEEN 900 AND 30000
- );
- --32,查询所有工资都在900-3000之间的
- --员工所在部门的员工信息
- SELECT *
- FROM EMP A
- WHERE A.DEPTNO IN
- (
- SELECT DISTINCT E.DEPTNO
- FROM EMP E
- WHERE E.SAL BETWEEN 900 AND 3000
- );
- --33,查询每个员工的领导所在部门的信息
- SELECT D.*
- FROM DEPT D
- WHERE 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
- )E
- WHERE ROWNUM < 4;
- --35,查询工作等级为2级,1985年以后入职的工作
- --地点为DALLAS的员工编号、姓名和工资
- SELECT E.ENAME,E.EMPNO,E.SAL
- FROM EMP E,SALGRADE S,DEPT D
- WHERE (E.SAL BETWEEN S.LOSAL AND S.HISAL)
- AND S.GRADE = 2
- AND TO_CHAR(E.HIREDATE,'YYYY') > 1985
- AND E.DEPTNO = D.DEPTNO
- AND D.LOC = 'DALLAS';
- --36,将各部门员工的工资修改为该员工所在部门
- --平均工资加1000
- UPDATE EMP E
- SET SAL = 1000 +
- (
- SELECT AVG(SAL)
- FROM EMP
- WHERE DEPTNO = E.DEPTNO
- );
- --37,删除重复部门,只留下一项
- DELECT FROM DEPT D
- WHERE ROWID !=
- (
- SELECT MIN(ROWID)
- FROM DEPT
- WHERE DNAME = D.DNAME
- AND LOC = D.LOC
- );
- --38,更新员工工资为它的主管工资,奖金
- UPDATE EMP E
- SET SAL =
- (
- SELECT SAL
- FROM EMP
- WHERE EMPNO = E.MGR
- ),
- COMM =
- (
- SELECT COMM
- FORM EMP
- WHERE
- EMPNO = E.MGR
- );
- --
- UPDATE EMP E
- SET (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 BORROW
- GROUP BY CNO
- HAVING COUNT(*) > 5;
- --44,查询借阅了“水浒”一书的读者,输出姓名班级
- SELECT NAME, CLASS
- FROM CARD
- WHERE 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,AUTHOR
- FORM BOOKS
- WHERE BNAME LIKE '%网络%';
- --47,查询现有图书中价格最高的图书,输出
- --书名及作者
- SELECT BNAME,AUTHOR
- FROM BOOKS
- WHERE PRICE =
- (
- SELECT MAX(PRICE)
- FROM BOOKS
- );
- --48,查询当前借了“计算方法”但没有借“计算方法
- --习题集“的作者,输出其借书卡号,并按卡号
- --降序排序输出
- SELECT A.CNO
- FROM BORROW A, BOOKS B
- WHERE 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 B
- WHERE A.BNO = B.BNO
- AND B.BNAME IN
- ('计算方法','组合数学')
- ORDER BY A.CNO;
- --50,将”c01“班同学所借图书的还期都延长一周
- UPDATE BORROW
- SET RDATE = RDATE + 7
- WHERE CNO IN
- (
- SELECT DISTINCT CNO
- FROM CARD
- WHERE CLASS = 'C01'
- );
- --51,从BOOKS表中删除当前无人借阅图书记录
- DELETE FROM BOOKS
- BNO 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 > 0
- INSERT BORROW_SAVE
- SELECT I.*
- FROM INSERTED I,BOOKS
- WHERE I.BNO = B.BNO
- AND B.BNAME = N'数据库应用技术';
- --53,建立一个视图,显示”01班学生的借书信息:
- --(只要求显示姓名和书名)
- CREATE VIEW V_VIEWASSELECT
- A.NAME,B.BNAME
- FROM BORROW AB,CARD A, BOOKS B
- WHERE AB.CNO = A.CNO
- AND AB.BNO = B.BNO
- AND A.CLASS = N'01班'
相关文章
- 10-13hbase基本介绍
- 10-13Hbase-基本
- 10-13SQL面试题练习
- 10-13信号量的基本同步模式
- 10-13pickle模块的基本使用
- 10-13sql 查询结果中加入空值列
- 10-13SQL游标在递归是的时候提示 "游标" 名称已经存在的问题
- 10-13win7安装SQL2005出现29506错误
- 10-13串口通信协议基本样式
- 10-13F1C100S基本信息