The text below is selected, press Ctrl+C to copy to your clipboard. (⌘+C on Mac) No line numbers will be copied.
Guest
Dh
By Guest on 11th February 2019 08:24:08 AM | Syntax: TEXT | Views: 12



New paste | Download | Show/Hide line no. | Copy text to clipboard
  1. SQL> create table emptable466 (eid number(2),enmae varchar
  2.  
  3. (7),sal number(5),comm
  4.  number(5),job varchar(10),dno number(2));
  5.  
  6. Table created.
  7.  
  8. SQL> insert into emptable466 values
  9.  
  10. (11,'Pujan',50000,5000,'Engineer',10);
  11.  
  12. 1 row created.
  13.  
  14. SQL> insert into emptable466 values
  15.  
  16. (12,'Kartik',60000,5000,'Manager',20);
  17.  
  18. 1 row created.
  19.  
  20. SQL> insert into emptable466 values
  21.  
  22. (13,'Shubh',65000,6000,'CA',30);
  23.  
  24. 1 row created.
  25.  
  26. SQL> insert into emptable466 values
  27.  
  28. (14,'Jinesh',55000,9000,'Engineer',10);
  29.  
  30. 1 row created.
  31.  
  32. SQL> insert into emptable466 values
  33.  
  34. (15,'Ashutosh',65000,9000,'Engineer',10);
  35. insert into emptable466 values
  36.  
  37. (15,'Ashutosh',65000,9000,'Engineer',10)
  38.                                   *
  39. ERROR at line 1:
  40. ORA-12899: value too large for column
  41.  
  42. "SYSTEM"."EMPTABLE466"."ENMAE" (actual:
  43. 8, maximum: 7)
  44.  
  45.  
  46. SQL> insert into emptable466 values
  47.  
  48. (15,'Ashu',65000,9000,'Engineer',10);
  49.  
  50. 1 row created.
  51.  
  52. SQL>
  53. SQL>
  54. SQL> insert into emptable466 values
  55.  
  56. (16,'Palli',60000,9000,'Engineer',10);
  57.  
  58. 1 row created.
  59.  
  60. SQL> insert into emptable466 values
  61.  
  62. (17,'Raju',15000,1000,'Clerk',40);
  63.  
  64. 1 row created.
  65.  
  66. SQL> insert into emptable466 values
  67.  
  68. (18,'Babu',12500,1000,'Clerk',40);
  69.  
  70. 1 row created.
  71.  
  72. SQL> insert into emptable466 values
  73.  
  74. (19,'Shyaam',14000,1000,'Clerk',40);
  75.  
  76. 1 row created.
  77.  
  78. SQL>
  79. SQL>
  80. SQL>
  81. SQL> select * from emptable466;
  82.  
  83.        EID ENMAE          SAL       COMM JOB               DNO
  84. ---------- ------- ---------- ---------- ---------- ----------
  85.         11 Pujan        50000       5000 Engineer           10
  86.         12 Kartik       60000       5000 Manager            20
  87.         13 Shubh        65000       6000 CA                 30
  88.         14 Jinesh       55000       9000 Engineer           10
  89.         15 Ashu         65000       9000 Engineer           10
  90.         16 Palli        60000       9000 Engineer           10
  91.         17 Raju         15000       1000 Clerk              40
  92.         18 Babu         12500       1000 Clerk              40
  93.         19 Shyaam       14000       1000 Clerk              40
  94.  
  95. 9 rows selected.
  96.  
  97. SQL> commit
  98.   2
  99. SQL> commit;
  100.  
  101. Commit complete.
  102.  
  103. SQL>
  104. SQL>
  105. SQL>
  106. SQL> select enmae,job,sal from emptable466 order by job;
  107.  
  108. ENMAE   JOB               SAL
  109. ------- ---------- ----------
  110. Shubh   CA              65000
  111. Raju    Clerk           15000
  112. Shyaam  Clerk           14000
  113. Babu    Clerk           12500
  114. Palli   Engineer        60000
  115. Jinesh  Engineer        55000
  116. Pujan   Engineer        50000
  117. Ashu    Engineer        65000
  118. Kartik  Manager         60000
  119.  
  120. 9 rows selected.
  121.  
  122. SQL> select eid,enmae from emptable466 where job='Manager';
  123.  
  124.        EID ENMAE
  125. ---------- -------
  126.         12 Kartik
  127.  
  128. SQL>
  129. SQL>
  130. SQL>
  131. SQL>
  132. SQL>
  133. SQL> select * from emptable466 where comm>sal;
  134.  
  135. no rows selected
  136.  
  137. SQL>
  138. SQL>
  139. SQL>
  140. SQL>
  141. SQL>
  142. SQL>
  143. SQL> select sum(sal) from emptable466;
  144.  
  145.   SUM(SAL)
  146. ----------
  147.     396500
  148.  
  149. SQL>
  150. SQL>
  151. SQL>
  152. SQL> select enmae,sal from emptable466 where (select sal from
  153.  
  154. emptable466 where
  155. job='Manager')<(select sal from emptable466 where job!
  156.  
  157. ='Manager');
  158. select enmae,sal from emptable466 where (select sal from
  159.  
  160. emptable466 where job='
  161. Manager')<(select sal from emptable466 where job!='Manager')
  162.  
  163.            *
  164. ERROR at line 1:
  165. ORA-01427: single-row subquery returns more than one row
  166.  
  167.  
  168. SQL> select enmae,sal from emptable466 where (select sal from
  169.  
  170. emptable466 where
  171. job='Manager')<(select sal from emptable466);
  172. select enmae,sal from emptable466 where (select sal from
  173.  
  174. emptable466 where job='
  175. Manager')<(select sal from emptable466)
  176.  
  177.            *
  178. ERROR at line 1:
  179. ORA-01427: single-row subquery returns more than one row
  180.  
  181.  
  182. SQL>
  183. SQL>
  184. SQL>
  185. SQL> select enmae,sal from emptable466 where (select sal from
  186.  
  187. emptable466 where
  188. job='Manager')<(select sal from emptable466);
  189. select enmae,sal from emptable466 where (select sal from
  190.  
  191. emptable466 where job='
  192. Manager')<(select sal from emptable466)
  193.  
  194.            *
  195. ERROR at line 1:
  196. ORA-01427: single-row subquery returns more than one row
  197.  
  198.  
  199. SQL>
  200. SQL>
  201. SQL>
  202. SQL>
  203. SQL>
  204. SQL>
  205. SQL> select enmae,sal from emptable466 where sal>(select sal from
  206.  
  207. emptable466 wh
  208. ere job='Manager');
  209.  
  210. ENMAE          SAL
  211. ------- ----------
  212. Shubh        65000
  213. Ashu         65000
  214.  
  215. SQL>
  216. SQL>
  217. SQL>
  218. SQL> select enmae,sal from emptable466 where sal<(select sal from
  219.  
  220. emptable466 wh
  221. ere job='Manager');
  222.  
  223. ENMAE          SAL
  224. ------- ----------
  225. Pujan        50000
  226. Jinesh       55000
  227. Raju         15000
  228. Babu         12500
  229. Shyaam       14000
  230.  
  231. SQL>
  232. SQL>
  233. SQL>
  234. SQL>
  235. SQL>
  236. SQL> select min(sal) from emptable466 group by dno;
  237.  
  238.   MIN(SAL)
  239. ----------
  240.      65000
  241.      60000
  242.      12500
  243.      50000
  244.  
  245. SQL> select min(sal) from emptable466 group by dno;
  246.  
  247.   MIN(SAL)
  248. ----------
  249.      65000
  250.      60000
  251.      12500
  252.      50000
  253.  
  254. SQL>
  255. SQL>
  256. SQL>
  257. SQL>
  258. SQL>
  259. SQL>
  260. SQL> select enmae,sal,sal*12 from emptable466;
  261.  
  262. ENMAE          SAL     SAL*12
  263. ------- ---------- ----------
  264. Pujan        50000     600000
  265. Kartik       60000     720000
  266. Shubh        65000     780000
  267. Jinesh       55000     660000
  268. Ashu         65000     780000
  269. Palli        60000     720000
  270. Raju         15000     180000
  271. Babu         12500     150000
  272. Shyaam       14000     168000
  273.  
  274. 9 rows selected.
  275.  
  276. SQL> select enmae,sal,sal*12 as AnnualIncome from emptable466;
  277.  
  278. ENMAE          SAL ANNUALINCOME
  279. ------- ---------- ------------
  280. Pujan        50000       600000
  281. Kartik       60000       720000
  282. Shubh        65000       780000
  283. Jinesh       55000       660000
  284. Ashu         65000       780000
  285. Palli        60000       720000
  286. Raju         15000       180000
  287. Babu         12500       150000
  288. Shyaam       14000       168000
  289.  
  290. 9 rows selected.
  291.  
  292. SQL>
  293. SQL>
  294. SQL>
  295. SQL>
  296. SQL>
  297. SQL>
  298. SQL> select * from emptable466 where sal>=20000 and sal<60000;
  299.  
  300.        EID ENMAE          SAL       COMM JOB               DNO
  301. ---------- ------- ---------- ---------- ---------- ----------
  302.         11 Pujan        50000       5000 Engineer           10
  303.         14 Jinesh       55000       9000 Engineer           10
  304.  
  305. SQL> select * from emptable466 where sal>=20000 and sal<=60000;
  306.  
  307.        EID ENMAE          SAL       COMM JOB               DNO
  308. ---------- ------- ---------- ---------- ---------- ----------
  309.         11 Pujan        50000       5000 Engineer           10
  310.         12 Kartik       60000       5000 Manager            20
  311.         14 Jinesh       55000       9000 Engineer           10
  312.         16 Palli        60000       9000 Engineer           10
  313.  
  314. SQL> select *,sal*12 as AnnInc from emptable466 order by sal;
  315. select *,sal*12 as AnnInc from emptable466 order by sal
  316.         *
  317. ERROR at line 1:
  318. ORA-00923: FROM keyword not found where expected
  319.  
  320.  
  321. SQL>
  322. SQL>
  323. SQL>
  324. SQL> select * as AnnInc from emptable466 order by sal;
  325. select * as AnnInc from emptable466 order by sal
  326.          *
  327. ERROR at line 1:
  328. ORA-00923: FROM keyword not found where expected
  329.  
  330.  
  331. SQL> select * from emptable466 order by sal;
  332.  
  333.        EID ENMAE          SAL       COMM JOB               DNO
  334. ---------- ------- ---------- ---------- ---------- ----------
  335.         18 Babu         12500       1000 Clerk              40
  336.         19 Shyaam       14000       1000 Clerk              40
  337.         17 Raju         15000       1000 Clerk              40
  338.         11 Pujan        50000       5000 Engineer           10
  339.         14 Jinesh       55000       9000 Engineer           10
  340.         12 Kartik       60000       5000 Manager            20
  341.         16 Palli        60000       9000 Engineer           10
  342.         13 Shubh        65000       6000 CA                 30
  343.         15 Ashu         65000       9000 Engineer           10
  344.  
  345. 9 rows selected.
  346.  
  347. SQL>
  348. SQL>
  349. SQL>
  350. SQL> select eid,enmae,sal,comm,job,dno,sal*12 as annInc from
  351.  
  352. emptable466 order b
  353. y sal;
  354.  
  355.        EID ENMAE          SAL       COMM JOB               DNO     ANNINC
  356. ---------- ------- ---------- ---------- ---------- ---------- ----------
  357.         18 Babu         12500       1000 Clerk              40     150000
  358.         19 Shyaam       14000       1000 Clerk              40     168000
  359.         17 Raju         15000       1000 Clerk              40     180000
  360.         11 Pujan        50000       5000 Engineer           10     600000
  361.         14 Jinesh       55000       9000 Engineer           10     660000
  362.         12 Kartik       60000       5000 Manager            20     720000
  363.         16 Palli        60000       9000 Engineer           10     720000
  364.         13 Shubh        65000       6000 CA                 30     780000
  365.         15 Ashu         65000       9000 Engineer           10     780000
  366.  
  367. 9 rows selected.



  • Recent Pastes