SQL基本CRUD
--已知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班'
