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



New paste | Download | Show/Hide line no. | Copy text to clipboard
  1. SQL> create table emp_457(eid number(4),ename varchar(20),sal number(7),comm num
  2. ber(5),job varchar(20),dno number(4));
  3.  
  4. Table created.
  5.  
  6. SQL> insert into emp_457 values(1,'A',60000,500,'Manager',20);
  7.  
  8. 1 row created.
  9.  
  10. SQL> insert into emp_457 values(2,'B',56000,300,'Analyst',10);
  11.  
  12. 1 row created.
  13.  
  14. SQL> insert into emp_457 values(3,'C',36000,200,'Salesman',30);
  15.  
  16. 1 row created.
  17.  
  18. SQL> insert into emp_457 values(4,'D',76000,700,'Team-Leader',10);
  19.  
  20. 1 row created.
  21.  
  22. SQL> insert into emp_457 values(5,'E',6000,100,'Clerk',20);
  23.  
  24. 1 row created.
  25.  
  26. SQL> select eid,ename from emp_457 where dno=20 and job!='Clerk' , job!='Analyst
  27. ',job!=Salesman';
  28. ERROR:
  29. ORA-01756: quoted string not properly terminated
  30.  
  31.  
  32. SQL> select ename,job,salary from emp_457 order by job asc;
  33. select ename,job,salary from emp_456 order by job asc
  34.                  *
  35. ERROR at line 1:
  36. ORA-00904: "SALARY": invalid identifier
  37.  
  38.  
  39. SQL> select ename,job,sal from emp_457 order by job asc;
  40.  
  41. ENAME                JOB                         SAL
  42. -------------------- -------------------- ----------
  43. B                    Analyst                   56000
  44. E                    Clerk                      6000
  45. A                    Manager                   60000
  46. C                    Salesman                  36000
  47. D                    Team-Leader               76000
  48.  
  49. SQL> select eid,ename from emp_457 where job='Manager';
  50.  
  51.        EID ENAME
  52. ---------- --------------------
  53.          1 A
  54.  
  55. SQL> select * from emp_457 where comm>sal;
  56.  
  57. no rows selected
  58.  
  59. SQL> select sum(sal) from emp_457;
  60.  
  61.   SUM(SAL)
  62. ----------
  63.     234000
  64.  
  65. SQL> select ename,sal from emp_457 where sal>(select sal from emp_456 where job=
  66. 'Manager');
  67.  
  68. ENAME                       SAL
  69. -------------------- ----------
  70. D                         76000
  71.  
  72. SQL> select ename,sal from emp_457 where sal<(select sal from emp_456 where job=
  73. 'Manager');
  74.  
  75. ENAME                       SAL
  76. -------------------- ----------
  77. B                         56000
  78. C                         36000
  79. E                          6000
  80.  
  81. SQL> select min(sal) from emp_457 where job='Manager' orf=der by dno;
  82. select min(sal) from emp_456 where job='Manager' orf=der by dno
  83.                                                  *
  84. ERROR at line 1:
  85. ORA-00933: SQL command not properly ended
  86.  
  87.  
  88. SQL> select min(sal) from emp_457 where job='Manager' order by dno;
  89.  
  90.   MIN(SAL)
  91. ----------
  92.      60000
  93.  
  94. SQL> select sal,sal*12 from emp_457;
  95.  
  96.        SAL     SAL*12
  97. ---------- ----------
  98.      60000     720000
  99.      56000     672000
  100.      36000     432000
  101.      76000     912000
  102.       6000      72000
  103.  
  104. SQL> select * from emp_457 where sal between 10000 and 20000;
  105.  
  106. no rows selected
  107.  
  108. SQL> select * from emp_457 where sal between 20000 and 50000;
  109.  
  110.        EID ENAME                       SAL       COMM JOB
  111. ---------- -------------------- ---------- ---------- --------------------
  112.        DNO
  113. ----------
  114.          3 C                         36000        200 Salesman
  115.         30
  116.  
  117.  
  118. SQL> select min(sal) from emp_457 where job='Manager',sal>2500;
  119. select min(sal) from emp_457 where job='Manager',sal>2500
  120.                                                 *
  121. ERROR at line 1:
  122. ORA-00933: SQL command not properly ended
  123.  
  124.  
  125. SQL> select min(sal) from emp_457 where job='Manager' and sal>2500;
  126.  
  127.   MIN(SAL)
  128. ----------
  129.      60000
  130.  
  131. SQL> select min(sal) from emp_457 where job='Manager' and sal>2500 order by dno;
  132.  
  133.  
  134.   MIN(SAL)
  135. ----------
  136.      60000
  137.  
  138. SQL> select min(sal) from emp_457 where job='Manager' and sal>2500 group by dno;
  139.  
  140.  
  141.   MIN(SAL)
  142. ----------
  143.      60000
  144.  
  145. SQL> select eid,ename,comm,sal*12 from emp_457 order by sal*12;;
  146. select eid,ename,comm,sal*12 from emp_457 order by sal*12;
  147.                                                          *
  148. ERROR at line 1:
  149. ORA-00911: invalid character
  150.  
  151.  
  152. SQL> select eid,ename,comm,sal*12 from emp_457 order by sal*12;
  153.  
  154.        EID ENAME                      COMM     SAL*12
  155. ---------- -------------------- ---------- ----------
  156.          5 E                           100      72000
  157.          3 C                           200     432000
  158.          2 B                           300     672000
  159.          1 A                           500     720000
  160.          4 D                           700     912000
  161.  
  162. SQL> select eid,ename,job,comm,sal*12 from emp_457 order by sal*12;
  163.  
  164.        EID ENAME                JOB                        COMM     SAL*12
  165. ---------- -------------------- -------------------- ---------- ----------
  166.          5 E                    Clerk                       100      72000
  167.          3 C                    Salesman                    200     432000
  168.          2 B                    Analyst                     300     672000
  169.          1 A                    Manager                     500     720000
  170.          4 D                    Team-Leader                 700     912000
  171.  
  172. SQL> select eid,ename from emp_456 where dno=10 0r dno=20 group by job;
  173. select eid,ename from emp_457 where dno=10 0r dno=20 group by job
  174.                                            *
  175. ERROR at line 1:
  176. ORA-00933: SQL command not properly ended
  177.  
  178.  
  179. SQL> select eid,ename from emp_457 where dno=10 or dno=20 group by job;
  180. select eid,ename from emp_457 where dno=10 or dno=20 group by job
  181.        *
  182. ERROR at line 1:
  183. ORA-00979: not a GROUP BY expression
  184.  
  185.  
  186. SQL> select ename from emp_457 where dno=10 or dno!=20;
  187.  
  188. ENAME
  189. --------------------
  190. B
  191. C
  192. D
  193.  
  194. SQL> select * from emp_457 where dno=20 and job!='Clerk' and job!='Analyst' and
  195. job='Salesman';
  196.  
  197. no rows selected
  198.  
  199. SQL>



  • Recent Pastes