SQL基本CRUD

  1. --已知Oracle的Scott用户中提供了三个测试数据库表
  2. --名称分别为dept,emp,salgrade。使用SQL语言完成一下操作
  3. --1,查询20号部门的所有员工信息:
  4. SELECT *
  5. FROM EMP E
  6. WHERE E.DEPTNO = 20;
  7. --2,查询奖金(COMM)高于工资(SAL)的员工信息
  8. SELECT *
  9. FROM EMP
  10. WHERE COMM > SAL;
  11. --3,查询奖金高于工资20%的员工信息
  12. SELECT *
  13. FROM EMP
  14. WHERE COMM > SAL*0.2;
  15. --4,查询10号部门中工种为MANAGER和20号部门中
  16. --工种为CLERK的员工的信息
  17. SELECT *
  18. FROM EMP E
  19. WHERE (E.DEPTNO = 10 AND E.JOB = 'MANAGER')
  20. OR (E.DEPTNO = 20 AND E.JOB = 'CLERK');
  21. --5,查询所有工种不是MANAGER和CLERK,且工资大于
  22. --或等于2000的员工的详细信息
  23. SELECT * FROM EMP
  24. WHERE JOB NOT IN ('MANAGER','CLERK')
  25. AND SAL >= 2000;
  26. --6,查询没有奖学金低于100的员工信息
  27. SELECT *
  28. FROM EMP
  29. WHERE COMM IS NULL
  30. OR COMM < 100;
  31. --7,查询员工工龄大于或等于10年的员工信息
  32. SELECT *
  33. FROM EMP
  34. WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)/12 > 10;
  35. --8,查询员工信息,要求以首字母大写的方式显示
  36. --所有员工的姓名
  37. SELECT INITCAP(ENAME)
  38. FROM EMP;
  39. --
  40. SELECT UPPER(SUBSTR(ENAME,1,1))||
  41. LOWER(SUBSTR(ENAME,2))
  42. FROM EMP;
  43. --9,查询在2月份入职的所有员工信息
  44. SELECT *
  45. FROM EMP
  46. WHERE TO_CHAR(HIREDATE,'MM') = '02';
  47. --10,显示所有员工的姓名、入职的年份和月份
  48. --按入职日期所在的月份排序,若月份相同则按
  49. --入职的年份排序
  50. SELECT ENAME,TO_CHAR(HIREDATE,'YYYY') YEAR,
  51. TO_CHAR(HIREDATE,'MM') MONTH
  52. FROM EMP
  53. ORDER BY MONTH,YEAR
  54. --11,查询‘JONES’员工及所有其直接、
  55. --间接下属员工的信息(递归算法)
  56. SELECT E.*
  57. FROM EMP E
  58. START WITH ENAME = 'JONES'
  59. CONNECT BY PRIOR EMPNO = MGR;
  60. --12,查询SCOTT员工及其直接、间接上级员工的信息
  61. SELECT E.*
  62. FROM EMP E
  63. START WITH ENAME = 'SCOTT'
  64. CONNECT BY PRIOR MGR = EMPNO;
  65. --13,查询从事同一种工作但不属于同一部门的员工信息
  66. SELECT A.ENAME,A.JOB,A.DEPTNO,
  67. B.ENAME,B.JOB,B.DEPTNO
  68. FROM EMP A,EMP B
  69. WHERE A.JOB = B.JOB AND A.DEPTNO != B.DEPTNO;
  70. --14,查询各个部门的详细信息以及部门人数、部门
  71. --平均工资
  72. SELECT D.DEPTNO,COUNT(E.EMPNO),AVG(E.SAL),
  73. D.DNAME,D.LOC
  74. FROM EMP E,DEPT D
  75. WHERE E.DEPTNO = D.DEPTNO
  76. GROUP BY D.DEPTNO,D.DNAME,D.LOC;
  77. --15,查询10号部门员工以及领导的信息
  78. SELECT *
  79. FROM EMP
  80. WHERE EMPNO IN
  81. (
  82. SELECT MGR
  83. FROM EMP
  84. WHERE DEPTNO = 10
  85. )
  86. OR DEPTNO = 10;
  87. --16,查询工资为某个部门平均工资的员工信息
  88. SELECT *
  89. FROM EMP
  90. WHERE SAL IN
  91. (
  92. SELECT AVG(SAL)
  93. FROM EMP
  94. GROUP BY DEPTNO
  95. );
  96. --17,查询工资高于本部门平均工资的员工的信息
  97. SELECT *
  98. FROM EMP E1
  99. WHERE SAL >
  100. (
  101. SELECT AVG(SAL)
  102. FROM EMP E2
  103. WHERE E2.DEPTNO = E1.DEPTNO
  104. );
  105. --18,查询工资高于本部们平均工资的员工的信息
  106. SELECT E.*,A.AVGSAL
  107. FROM EMP E,
  108. (
  109. SELECT DEPTNO,AVG(SAL) AS AVGSAL
  110. FROM EMP
  111. GROUP BY DEPTNO
  112. )A
  113. WHERE A.DEPTNO = E.DEPTNO
  114. AND E.SAL > A.AVGSAL;
  115. --19,统计各个工种的人数与平均工资
  116. SELECT COUNT(*),E.JOB,AVG(E.SAL)
  117. FROM EMP E
  118. GROUP BY E.JOB;
  119. --20,查询所有员工入职以来的工作期限,用
  120. --“**年**月**日”的形式表示
  121. SELECT ENAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)||
  122. '年'||TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,HIREDATE),12))||
  123. '月'||TRUNC(SYSDATE - ADD_MONTHS(HIREDATE,
  124. MONTHS_BETWEEN(SYSDATE, HIREDATE)))||'日'
  125. FROM EMP;
  126. --21,查询人数最多的部门信息
  127. SELECT *
  128. FROM DEPT
  129. WHERE DEPTNO IN
  130. (
  131. SELECT DEPTNO
  132. FROM
  133. (
  134. SELECT COUNT(*) COUNT,DEPTNO
  135. FROM EMP
  136. GROUP BY DEPTNO
  137. )
  138. WHERE COUNT IN
  139. (
  140. SELECT MAX(COUNT)
  141. FROM
  142. (
  143. SELECT COUNT(*) COUNT,DEPTNO
  144. FROM EMP
  145. GROUP BY DEPTNO
  146. )
  147. )
  148. );
  149. --22,以树状结构查询所有员工与领导
  150. --之间的层次关系
  151. SELECT SUBSTR(SYS_CONNECT_BY_PATH(ENAME,'->'),3),
  152. LEVEL
  153. FROM EMP
  154. START WITH MGR IS NULL
  155. CONNECT BY PRIOR EMPNO = MGR;
  156. --23,部门平均薪水最高的部门编号
  157. SELECT *
  158. FROM
  159. (
  160. SELECT *
  161. AVG(SAL) AVGSAL,DEPTNO
  162. FROM EMP
  163. GROUP BY DEPTNO
  164. ORDER BY AVGSAL DESC
  165. )
  166. WHERE ROWNUM = 1;
  167. --
  168. SELECT DEPTNO, AVG(SAL)
  169. FROM EMP
  170. GROUP BY DEPTNO
  171. HAVING AVG(SAL) =
  172. (
  173. SELECT MAX(AVG(SAL)) AVGSAL
  174. FROM EMP
  175. GROUP BY DEPTNO
  176. );
  177. --24,部门平均薪水最高的部门名称
  178. SELECT D.*
  179. FROM  DEPT D
  180. WHERE DEPTNO IN
  181. (
  182. SELECT DEPTNO
  183. FROM EMP
  184. GROUP BY DEPTNO
  185. HAVING AVG(SAL)=
  186. (
  187. SELECT MAX(AVG(SAL)) AVGSLA
  188. FROM EMP
  189. GROUP BY DEPTNO
  190. )
  191. );
  192. --25,平均薪水最低的部门名称
  193. SELECT D.DNAME
  194. FROM  DEPT D
  195. WHERE DEPTNO IN
  196. (
  197. SELECT DEPTNO
  198. FROM EMP
  199. GROUP BY DEPTNO
  200. HAVING AVG(SAL)=
  201. (
  202. SELECT MIN(AVG(SAL)) AVGSLA
  203. FROM EMP
  204. GROUP BY DEPTNO
  205. )
  206. );
  207. --26,平均薪水等级最低的部门的部门名称
  208. SELECT D.DNAME
  209. FROM DEPT D
  210. WHERE D.DEPTNO IN
  211. (
  212. SELECT A.DEPTNO
  213. FROM
  214. (
  215. SELECT E.DEPTNO
  216. FROM EMP E, SALGRADE S
  217. GROUP BY E.DEPTNO
  218. ORDER BY AVG(S.GRADE)
  219. )A
  220. WHERE ROWNUM = 1
  221. );
  222. --27,部门经理人中,薪水最低的部门名称
  223. SELECT DNAME
  224. FROM DEPT
  225. WHERE DEPTNO =
  226. (
  227. SELECT DEPTNO
  228. FROM
  229. (
  230. SELECT DEPTNO
  231. FORM EMP
  232. WHERE JOB = 'MANAGER'
  233. GROUP BY DEPTNO
  234. ORDER BY MIN(SAL)
  235. )
  236. WHERE ROWNUM = 1
  237. );
  238. --28,比普通员工的最高薪水还要高的经理人名称
  239. SELECT ENAME
  240. FROM EMP
  241. WHERE SAL >
  242. (
  243. SELECT MAX(SAL)
  244. FROM EMP
  245. WHERE JOB NOT IN
  246. ('MANAGER','PRESIDENT')
  247. )
  248. AND JOB = 'MANAGER'
  249. OR JOB = 'PRESIDENT';
  250. --29,查询所有员工工资大于1000的部门的信息
  251. SELECT *
  252. FROM DEPT
  253. WHERE
  254. DEPTNO IN
  255. (
  256. SELECT DEPTNO
  257. FROM EMP
  258. WHERE DEPTNO NOT IN
  259. (
  260. SELECT DISTINCT DEPTNO
  261. FROM EMP
  262. WHERE SAL < 1000
  263. )
  264. );
  265. --30,查询所有员工工资都大于1000的部门的信息
  266. --以及员工信息
  267. SELECT *
  268. FROM EMP E JOIN DEPT D
  269. ON D.DEPTNO = E.DEPTNO
  270. AND D.DEPTNO IN
  271. (
  272. SELECT DEPTNO
  273. FROM EMP
  274. WHERE DEPTNO NOT IN
  275. (
  276. SELECT DISTINCT DEPTNO
  277. FROM EMP
  278. WHERE SAL < 1000
  279. )
  280. );
  281. --31,查询所有工资都在900-3000之间的员工
  282. --所在部门的员工信息
  283. SELECT *
  284. FROM DEPT
  285. WHERE DEPTNO NOT IN
  286. (
  287. SELECT DEPTNO
  288. FROM EMP
  289. WHERE SAL NOT BETWEEN 900 AND 30000
  290. );
  291. --32,查询所有工资都在900-3000之间的
  292. --员工所在部门的员工信息
  293. SELECT *
  294. FROM EMP A
  295. WHERE A.DEPTNO IN
  296. (
  297. SELECT DISTINCT E.DEPTNO
  298. FROM EMP E
  299. WHERE E.SAL BETWEEN 900 AND 3000
  300. );
  301. --33,查询每个员工的领导所在部门的信息
  302. SELECT D.*
  303. FROM DEPT D
  304. WHERE D.DEPTNO IN
  305. (
  306. SELECT DISTINCT E2.DEPTNO
  307. FROM EMP E1,EMP E2
  308. WHERE E1.EMPNO = E2.MGR
  309. );
  310. --34,查询30号部门中工资排序前3名的员工信息
  311. SELECT *
  312. FROM
  313. (
  314. SELECT SAL
  315. FROM EMP
  316. WHERE DEPTNO = 30
  317. ORDER BY SAL DESC
  318. )E
  319. WHERE ROWNUM < 4;
  320. --35,查询工作等级为2级,1985年以后入职的工作
  321. --地点为DALLAS的员工编号、姓名和工资
  322. SELECT E.ENAME,E.EMPNO,E.SAL
  323. FROM EMP E,SALGRADE S,DEPT D
  324. WHERE (E.SAL BETWEEN S.LOSAL AND S.HISAL)
  325. AND S.GRADE = 2
  326. AND TO_CHAR(E.HIREDATE,'YYYY') > 1985
  327. AND E.DEPTNO = D.DEPTNO
  328. AND D.LOC = 'DALLAS';
  329. --36,将各部门员工的工资修改为该员工所在部门
  330. --平均工资加1000
  331. UPDATE EMP E
  332. SET SAL = 1000 +
  333. (
  334. SELECT AVG(SAL)
  335. FROM EMP
  336. WHERE DEPTNO = E.DEPTNO
  337. );
  338. --37,删除重复部门,只留下一项
  339. DELECT FROM DEPT D
  340. WHERE ROWID !=
  341. (
  342. SELECT MIN(ROWID)
  343. FROM DEPT
  344. WHERE DNAME = D.DNAME
  345. AND LOC = D.LOC
  346. );
  347. --38,更新员工工资为它的主管工资,奖金
  348. UPDATE EMP E
  349. SET SAL =
  350. (
  351. SELECT SAL
  352. FROM EMP
  353. WHERE EMPNO = E.MGR
  354. ),
  355. COMM =
  356. (
  357. SELECT COMM
  358. FORM EMP
  359. WHERE
  360. EMPNO = E.MGR
  361. );
  362. --
  363. UPDATE EMP E
  364. SET (SAL,COMM) =
  365. (
  366. SELECT SAL, COMM
  367. FROM EMP
  368. WHERE EMPNO = E.MGR
  369. );
  370. --某大学图书馆为了更好管理图书,使用Oracle
  371. --数据库建立了三个表:
  372. --CARD 借书卡表:CNO(卡号),NAME(姓名),
  373. --CLASS(班级)
  374. --BOOKS图书表:BNO(书号),BNAME(书名),
  375. --AUTHOR(作者),PRICE(单价),QUANTITY(库存册数)
  376. --BORROW结束记录表:CNO(借书卡号),BNO(书号),
  377. --RDATE(还书日期)
  378. --备注:限定每人每种书只能借一本:库存册数
  379. --随借书、还书而改变
  380. --39,写出建立BORROW表的SQL语句,要求定义
  381. --主码完整性约束
  382. CREATE TABLE BORROW
  383. (
  384. CNO NUMBER REFERENCES CARD(CNO),
  385. BNO NUMBER REFERENCES BOOKS(BNO),
  386. RDATE DATE,
  387. PRIMARY KEY(CNO,BNO)
  388. );
  389. --40,假定在建BOOKS表时没有定义主码,写出
  390. --BOOKS表追加定义主码的语句
  391. ALTER TABLE BOOKS
  392. ADD PRIMARY KEY(BNO);
  393. --41,将CARD表的NAME最大列宽增加到10个字符
  394. --(假定原为6个字符)
  395. ALTER TABLE CARD
  396. MODIFY NAME VARCHAR2(10);
  397. --42,为表增加一列NAME(系名),可变长,
  398. --最大20个字符
  399. ALTER TABLE CARD
  400. ADD 系名 VARCHAR2(20);
  401. --43,找出借书超过5本的读者,输出借书
  402. --卡号所借图书册数
  403. SELECT CNO,COUNT(*)
  404. FROM BORROW
  405. GROUP BY CNO
  406. HAVING COUNT(*) > 5;
  407. --44,查询借阅了“水浒”一书的读者,输出姓名班级
  408. SELECT NAME, CLASS
  409. FROM CARD
  410. WHERE CNO IN
  411. (
  412. SELECT CNO
  413. FROM BORROW BW, BOOKS BK
  414. WHERE BW.BNO = BK.BNO
  415. AND BK.NAME = '水浒'
  416. );
  417. --45,查询过期未还图书,输出借阅者(卡号)
  418. --书号及还书日期
  419. SELECT *
  420. FROM BORROW
  421. WHERE RDATE < SYSDATE;
  422. --46,查询书名包括’网络‘关键词的图书,输出
  423. --书号、书名、作者
  424. SELECT BNO, BNAME,AUTHOR
  425. FORM BOOKS
  426. WHERE BNAME LIKE '%网络%';
  427. --47,查询现有图书中价格最高的图书,输出
  428. --书名及作者
  429. SELECT BNAME,AUTHOR
  430. FROM BOOKS
  431. WHERE PRICE =
  432. (
  433. SELECT MAX(PRICE)
  434. FROM BOOKS
  435. );
  436. --48,查询当前借了“计算方法”但没有借“计算方法
  437. --习题集“的作者,输出其借书卡号,并按卡号
  438. --降序排序输出
  439. SELECT A.CNO
  440. FROM BORROW A, BOOKS B
  441. WHERE A.BNO = B.BNO
  442. AND B.BNAME = '计算方法'
  443. AND A.CNO NOT IN
  444. (
  445. SELECT AA.CNO
  446. FORM BORROW AA,BOOKS BB
  447. WHERE AA.BNO = BB.BNO
  448. AND BB.BNAME = '计算方法习题集'
  449. )
  450. ORDER BY A.CNO DESC;
  451. --49,查询当前同时借有”计算方法“和组合”组合数学“
  452. --两本数的作者,输出其借书卡号,并按卡号升序排列输出
  453. SELECT DISTINCT A.CNO
  454. FROM BORROW A,BOOKS B
  455. WHERE A.BNO = B.BNO
  456. AND B.BNAME IN
  457. ('计算方法','组合数学')
  458. ORDER BY A.CNO;
  459. --50,将”c01“班同学所借图书的还期都延长一周
  460. UPDATE BORROW
  461. SET RDATE = RDATE + 7
  462. WHERE CNO IN
  463. (
  464. SELECT DISTINCT CNO
  465. FROM CARD
  466. WHERE CLASS = 'C01'
  467. );
  468. --51,从BOOKS表中删除当前无人借阅图书记录
  469. DELETE FROM BOOKS
  470. BNO NOT IN
  471. (
  472. SELECT DISTINCT BK, BNO
  473. FROM BORROW BR,BOOKS BK
  474. WHERE BR.BNO = BK.BNO
  475. );
  476. --52,如果经常按书名查询图书信息,
  477. --请建立合适的索引
  478. CREATE INDEX INX_BOOKS_BNAME
  479. ON BOOKS(BNAME);
  480. --52,在BORROW表上建立一个触发器,完成如下功能:
  481. --如果读者借阅的书名是”数据库技术应用“
  482. --就将该读者的借阅记录保存在BORROW_SAVE表中
  483. --(注:ORROW_SAVE表结构同BORROW表)
  484. CREATE TRIGGER TR_SAVE
  485. ON BORROWFOR INSERT,UPDATE AS IF
  486. @@ROW COUNT > 0
  487. INSERT BORROW_SAVE
  488. SELECT I.*
  489. FROM INSERTED I,BOOKS
  490. WHERE I.BNO = B.BNO
  491. AND B.BNAME = N'数据库应用技术';
  492. --53,建立一个视图,显示”01班学生的借书信息:
  493. --(只要求显示姓名和书名)
  494. CREATE VIEW V_VIEWASSELECT
  495. A.NAME,B.BNAME
  496. FROM BORROW AB,CARD A, BOOKS B
  497. WHERE AB.CNO = A.CNO
  498. AND AB.BNO = B.BNO
  499. AND A.CLASS = N'01班'
 
 
上一篇:Permutations [LeetCode]


下一篇:写一个简单的插入排序