SHARE
TWEET

RBD_SQL

a guest Apr 18th, 2019 92 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE EMP;
  2. DROP TABLE DEPT;
  3. DROP TABLE SALGRADE;
  4.  
  5. CREATE TABLE EMP
  6.        (EMPNO INT NOT NULL,
  7.         ENAME VARCHAR(10),
  8.         JOB VARCHAR(9),
  9.         MGR INT,
  10.         HIREDATE DATETIME,
  11.         SAL INT,
  12.         COMM INT,
  13.         DEPTNO INT);
  14.  
  15. INSERT INTO EMP VALUES
  16.         (7369, 'SMITH',  'CLERK',     7902,
  17.         CONVERT(DATETIME,'17-DEC-1980'),  800, NULL, 20);
  18. INSERT INTO EMP VALUES
  19.         (7499, 'ALLEN',  'SALESMAN',  7698,
  20.         CONVERT(DATETIME,'20-FEB-1981'), 1600,  300, 30);
  21. INSERT INTO EMP VALUES
  22.         (7521, 'WARD',   'SALESMAN',  7698,
  23.         CONVERT(DATETIME,'22-FEB-1981'), 1250,  500, 30);
  24. INSERT INTO EMP VALUES
  25.         (7566, 'JONES',  'MANAGER',   7839,
  26.         CONVERT(DATETIME,'2-APR-1981'),  2975, NULL, 20);
  27. INSERT INTO EMP VALUES
  28.         (7654, 'MARTIN', 'SALESMAN',  7698,
  29.         CONVERT(DATETIME,'28-SEP-1981'), 1250, 1400, 30);
  30. INSERT INTO EMP VALUES
  31.         (7698, 'BLAKE',  'MANAGER',   7839,
  32.         CONVERT(DATETIME,'1-MAY-1981'),  2850, NULL, 30);
  33. INSERT INTO EMP VALUES
  34.         (7782, 'CLARK',  'MANAGER',   7839,
  35.         CONVERT(DATETIME,'9-JUN-1981'),  2450, NULL, 10);
  36. INSERT INTO EMP VALUES
  37.         (7788, 'SCOTT',  'ANALYST',   7566,
  38.         CONVERT(DATETIME,'09-DEC-1982'), 3000, NULL, 20);
  39. INSERT INTO EMP VALUES
  40.         (7839, 'KING',   'PRESIDENT', NULL,
  41.         CONVERT(DATETIME,'17-NOV-1981'), 5000, NULL, 10);
  42. INSERT INTO EMP VALUES
  43.         (7844, 'TURNER', 'SALESMAN',  7698,
  44.         CONVERT(DATETIME,'8-SEP-1981'),  1500,    0, 30);
  45. INSERT INTO EMP VALUES
  46.         (7876, 'ADAMS',  'CLERK',     7788,
  47.         CONVERT(DATETIME,'12-JAN-1983'), 1100, NULL, 20);
  48. INSERT INTO EMP VALUES
  49.         (7900, 'JAMES',  'CLERK',     7698,
  50.         CONVERT(DATETIME,'3-DEC-1981'),   950, NULL, 30);
  51. INSERT INTO EMP VALUES
  52.         (7902, 'FORD',   'ANALYST',   7566,
  53.         CONVERT(DATETIME,'3-DEC-1981'),  3000, NULL, 20);
  54. INSERT INTO EMP VALUES
  55.         (7934, 'MILLER', 'CLERK',     7782,
  56.         CONVERT(DATETIME,'23-JAN-1982'), 1300, NULL, 10);
  57.  
  58. CREATE TABLE DEPT
  59.        (DEPTNO INT,
  60.         DNAME VARCHAR(14),
  61.         LOC VARCHAR(13) );
  62.  
  63. INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
  64. INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAdS');
  65. INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
  66. INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
  67.  
  68. CREATE TABLE SALGRADE
  69.         (GRADE INT,
  70.          LOSAL INT,
  71.          HISAL INT);
  72.  
  73. INSERT INTO SALGRADE VALUES (1,  700, 1200);
  74. INSERT INTO SALGRADE VALUES (2, 1201, 1400);
  75. INSERT INTO SALGRADE VALUES (3, 1401, 2000);
  76. INSERT INTO SALGRADE VALUES (4, 2001, 3000);
  77. INSERT INTO SALGRADE VALUES (5, 3001, 9999);
  78.  
  79. --1.
  80. SELECT DEPTNO, ENAME, EMPNO FROM EMP;
  81.  
  82. --2.
  83. SELECT * FROM EMP;
  84.  
  85. --3.
  86. SELECT SAL*12 FROM EMP;
  87.  
  88. --4.
  89. SELECT (SAL+250)*12 FROM EMP;
  90.  
  91. --5.
  92. SELECT SAL*12 ROCZNA FROM EMP;
  93.  
  94. --6.
  95. SELECT SAL*12 "R PENSJA" FROM EMP;
  96.  
  97. --7.
  98. SELECT CONCAT(EMPNO, ' ' , ENAME) AS EMPLOYEE FROM EMP;
  99.  
  100. --8.
  101. SELECT CONCAT(ENAME, ' pracuje w dziale ', DEPTNO) AS EMPLOYEE FROM EMP
  102.  
  103. --9.
  104. SELECT SAL*12+ISNULL (COMM,0) ROCZNAPENSJA FROM EMP
  105.  
  106.  
  107. --10/11.
  108. SELECT DISTINCT DEPTNO FROM EMP;
  109.  
  110.  
  111. --12.
  112. SELECT DISTINCT DEPTNO, JOB FROM EMP;
  113.  
  114.  
  115. --13.
  116. SELECT * FROM EMP
  117. ORDER BY ENAME;
  118.  
  119. --14.
  120. SELECT * FROM EMP
  121. ORDER BY HIREDATE;
  122.  
  123. --15.
  124. SELECT * FROM EMP
  125. ORDER BY DEPTNO ASC, SAL DESC;
  126.  
  127. --16.
  128. SELECT ENAME, EMPNO, JOB, DEPTNO FROM EMP
  129. WHERE JOB='CLERK'
  130. ORDER BY DEPTNO DESC;
  131.  
  132. --17.
  133. SELECT JOB, DEPTNO FROM EMP
  134. WHERE DEPTNO>=20;
  135.  
  136. --18.
  137. SELECT CONCAT(ENAME, ' o numerze ', EMPNO) AS PRACOWNIK, COMM, SAL FROM EMP
  138. WHERE COMM>SAL;
  139.  
  140. --19.
  141. SELECT ENAME, EMPNO, SAL FROM EMP
  142. --WHERE SAL+ISNULL (COMM, 0) <2000 AND SAL+ISNULL (COMM, 0) >1000;
  143. WHERE SAL BETWEEN 1000 AND 2000;
  144.  
  145. --20.
  146. SELECT ENAME, EMPNO, MGR FROM EMP
  147. WHERE MGR=7902 OR MGR=7566 OR MGR=7788;
  148.  
  149. --21.
  150. SELECT ENAME FROM EMP
  151. WHERE ENAME LIKE 'S%';
  152.  
  153. --22.
  154. SELECT ENAME FROM EMP
  155. WHERE LEN(ENAME) = 4;
  156.  
  157. --23.
  158. SELECT ENAME, EMPNO, MGR FROM EMP
  159. WHERE MGR IS NULL;
  160.  
  161. --24.
  162. SELECT ENAME, EMPNO, SAL FROM EMP
  163. WHERE SAL NOT BETWEEN 1000 AND 2000;
  164.  
  165. --25.
  166. SELECT ENAME FROM EMP
  167. WHERE ENAME NOT LIKE 'M%';  
  168.  
  169. --26.
  170. SELECT ENAME, EMPNO, MGR FROM EMP
  171. WHERE MGR IS NOT NULL;
  172.  
  173. --27.
  174. SELECT EMPNO, ENAME, JOB, SAL FROM EMP
  175. WHERE JOB='CLERK' AND SAL BETWEEN 1000 AND 2000;
  176.  
  177. --28.
  178. SELECT EMPNO, ENAME, JOB, SAL FROM EMP
  179. WHERE JOB='CLERK' OR SAL BETWEEN 1000 AND 2000;
  180.  
  181. --29.
  182. SELECT EMPNO, ENAME, JOB, SAL FROM EMP
  183. WHERE (JOB='MANAGER' AND SAL>1500) OR JOB='SALESMAN';
  184.  
  185. --30.
  186. SELECT EMPNO, ENAME, SAL, JOB FROM EMP
  187. WHERE JOB='MANAGER' OR (JOB='SALESMAN' AND SAL>1500);
  188.  
  189. --31.
  190. SELECT EMPNO, ENAME, JOB, DEPTNO FROM EMP
  191. WHERE JOB='MANAGER' OR (JOB='MANAGER' AND DEPTNO=10);
  192.  
  193. --32.
  194. SELECT * FROM SALGRADE;
  195.  
  196. --33.
  197. SELECT * FROM DEPT;
  198.  
  199. --34.
  200. SELECT DEPTNO, DNAME FROM DEPT
  201. ORDER BY DEPTNO;
  202.  
  203. --35.
  204. SELECT DISTINCT JOB FROM EMP;
  205.  
  206. --36.
  207. SELECT EMPNO, ENAME, DEPTNO FROM EMP
  208. WHERE DEPTNO=10 OR DEPTNO=20
  209. ORDER BY ENAME;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top