Advertisement
Guest User

Untitled

a guest
May 22nd, 2019
113
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.16 KB | None | 0 0
  1. SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP);
  2. SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME LIKE 'BLAKE');
  3. SELECT ENAME FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);
  4. SELECT ENAME FROM EMP WHERE SAL IN (SELECT MIN(SAL);
  5. SELECT * FROM EMP WHERE sal = (SELECT sal FROM EMP WHERE sal = MIN(SAL);
  6.  
  7. SELECT * FROM SALGRADE;
  8. CREATE TABLE PEOPLE (
  9. IDP INT NOT NULL,
  10. SURAME VARCHAR(50) NOT NULL,
  11. POSITION VARCHAR(50) NOT NULL,
  12. INCOME INT NOT NULL);
  13.  
  14. INSERT INTO PEOPLE (IDP,SURAME,POSITION,INCOME) VALUES (1,'KOWALSKI','EXPERT','3800');
  15. INSERT INTO PEOPLE SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB LIKE 'ANALYST';
  16. CREATE TABLE DIVISION (
  17. DIVNO INT NOT NULL,
  18. DIVNAME VARCHAR(50) NOT NULL,
  19. DIVLOC VARCHAR(50) NOT NULL);
  20. INSERT INTO DIVISION SELECT * FROM DEPT;
  21.  
  22. ALTER TABLE DIVISION ADD (HEAD VARCHAR2(20));
  23.  
  24. ALTER TABLE DIVISION RENAME TO BRANCH1;
  25.  
  26. SELECT * FROM BRANCH1;
  27. UPDATE BRANCH1 SET DIVLOC = 'WARSAW';
  28. UPDATE PEOPLE SET SURAME = 'ARENT' WHERE POSITION = 'PRESIDENT';
  29. DROP TABLE PEOPLE;
  30.  
  31. CREATE TABLE GROUPIE( GROUPNO INT PRIMARY KEY, GROUPNAME VARCHAR(50));
  32. DROP TABLE GROUPIE;
  33. CREATE TABLE STUDENTE( STUDENTID INT, GROUPNO INT NOT NULL, STUDENTNAME VARCHAR(50), CONSTRAINT g_id FOREIGN KEY (GROUPNO) REFERENCES GROUPIE(GROUPNO));
  34.  
  35. select * from emp,dept where emp.deptno=dept.deptno;
  36. select * from emp;
  37.  
  38. select emp.ename,dept.dname,dept.deptno from emp,dept where emp.deptno=dept.deptno;
  39. select emp.ename,dept.loc,dept.dname,emp.sal from emp,dept where emp.deptno=dept.deptno and sal>1500;
  40. select * from salgrade;
  41. select ename from emp where sal>1401 and sal<2000;
  42. select emp.ename,dept.loc from emp,dept where emp.deptno=dept.deptno and dept.loc='DALLAS';
  43. select * from emp;
  44. select A.* from EMP A WHERE A.SAL < (SELECT B.SAL FROM EMP b WHERE A.MGR=B.EMPNO);
  45. select distinct job from emp where deptno=10 or deptno=30;
  46. select * from emp order by deptno;
  47. select distinct job from emp where DEPTNO=10 AND JOB in (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=30);
  48. SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=10;
  49. SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=30;
  50. select distinct job from emp where DEPTNO=30 AND JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO=10);
  51.  
  52.  
  53.  
  54.  
  55.  
  56.  
  57.  
  58. SELECT * FROM SALGRADE;
  59. SELECT ENAME,DEPTNO,SAL FROM EMP WHERE SAL > 1000 AND SAL < 2000;
  60. SELECT DISTINCT JOB FROM EMP;
  61. SELECT * FROM EMP ORDER BY JOB;
  62. SELECT * FROM EMP WHERE DEPTNO=20 AND JOB='CLERK';
  63. SELECT ENAME FROM EMP WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%LL%';
  64. SELECT * FROM EMP WHERE SAL < 1000 or SAL > 2000 AND ENAME LIKE '_____';
  65. SELECT * FROM EMP WHERE ENAME NOT LIKE '%L%' AND SAL NOT IN(800,1600,3000);
  66. SELECT * FROM EMP WHERE MGR IS NOT NULL;
  67.  
  68. SELECT ENAME,JOB,((SAL*12)+COMM) AS ANNSAL FROM EMP WHERE JOB = 'SALESMAN';
  69.  
  70. SELECT * FROM EMP WHERE JOB='MANAGER' AND DEPTNO=10;
  71. SELECT * FROM EMP WHERE HIREDATE > '83/01/01' AND HIREDATE < '83/12/12';
  72.  
  73. SELECT ENAME,JOB,(SAL*12)ANNSAL,COMM FROM EMP WHERE JOB = 'SALESMAN' AND SAL > COMM ORDER BY SAL DESC;
  74. SELECT (ENAME||' HAS HELD THE POSITION OF '||JOB||' IN DEPARTMENT '||DEPTNO||' SINCE '||HIREDATE) "WHO, WHAT, WHEN" FROM EMP;
  75.  
  76. select ROUND(AVG(SAL),3) from EMP;
  77. select min(sal) from EMP where job = 'CLERK';
  78. select count(*) from EMP where deptno = 20;
  79. select round(avg(sal),3),job from EMP group by job;
  80. select round(avg(sal),3),job from EMP where job != 'MANAGER' group by job;
  81. select round(avg(sal),3),job,deptno from EMP group by deptno,job order by deptno;
  82. select max(sal),job from EMP group by job;
  83. select deptno,avg(sal) from emp group by deptno having count(*) > 3;
  84. select count(*) from emp where deptno = 20;
  85. select job,avg(sal) from emp group by job having avg(sal) >= 3000;
  86. SELECT JOB,
  87. CASE
  88. WHEN JOB = 'SALESMAN'
  89. THEN AVG((SAL*12)+COMM)
  90. ELSE AVG(SAL*12)
  91. END
  92. FROM EMP GROUP BY JOb;
  93.  
  94. SELECT (MAX(SAL)-MIN(SAL)) FROM EMP;
  95.  
  96. SELECT DEPTNO,COUNT(*) FROM EMP group by deptno having count(*) > 3;
  97. SELECT CASE
  98. WHEN COUNT(DISTINCT EMPNO) = COUNT(*) THEN 'TRUE'
  99. ELSE 'FALSE'
  100. END
  101. FROM EMP;
  102.  
  103. SELECT MIN(SAL),MGR,JOB FROM EMP WHERE SAL>1000 and MGR is not null group by mgr,job order by min(sal);
  104.  
  105. SELECT COUNT(*) FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.LOC='DALLAS';
  106.  
  107. SELECT * FROM SALGRADE;
  108.  
  109. SELECT SAL FROM EMP GROUP BY SAL HAVING COUNT(SAL)>=2;
  110. SELECT SAL FROM EMP ORDER BY SAL;
  111.  
  112. SELECT AVG(SAL) FROM EMP WHERE SAL>1200 AND SAL<1401;
  113. SELECT MGR,COUNT(*) FROM EMP WHERE JOB != 'MANAGER' AND MGR IS NOT NULL GROUP BY MGR;
  114. SELECT * FROM EMP ORDER BY MGR;
  115.  
  116. SELECT SUM(SAL) FROM EMP WHERE SAL >700 AND SAL<1200;
  117.  
  118. SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP);
  119.  
  120. SELECT MIN(SAL) FROM EMP WHERE JOB = 'CLERK';
  121.  
  122. SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'BLAKE') AND ENAME != 'BLAKE';
  123.  
  124. SELECT * FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);
  125.  
  126. SELECT * FROM EMP WHERE SAL > ANY (SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30);
  127. SELECT * FROM EMP WHERE SAL > ANY (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);
  128.  
  129. SELECT MAX(AVG(SAL)) FROM EMP WHERE JOB != 'PRESIDENT' GROUP BY SAL;
  130.  
  131. SELECT * FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND DNAME ='SALES');
  132. SELECT MAX(SAL) FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND DNAME ='SALES';
  133.  
  134. select * from emp e where sal > (select avg(sal) from emp e2 where e2.deptno = e.deptno);
  135.  
  136. select E.* from emp E where EXISTS (SELECT E2.* FROM EMP E2 WHERE E2.MGR = E.EMPNO);
  137. SELECT * FROM EMP;
  138. select * from branch;
  139. SELECT * FROM DEPT;
  140. select * from branch1;
  141.  
  142. select e.* from emp e where e.sal IN (select max(sal) from emp e2 where e.job = e2.job) order by sal desc;
  143. select max(sal),job from emp group by job;
  144.  
  145. select e.* from emp e where sal = (select min(sal) from emp e2 where e.deptno = e2.deptno) order by ename;
  146. select e.* from emp e where hiredate = (select max(hiredate) from emp e2 where e.deptno = e2.deptno) order by hiredate;
  147.  
  148. select e.ename,e.sal,e.deptno from emp e where sal > (select avg(sal) from emp e2 where e2.deptno = e.deptno);
  149. select ename,sal from ( select ename,sal from emp order by sal desc) where rownum <=3;
  150.  
  151. SELECT COUNT(*),DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM EMP GROUP BY DEPTNO);
  152.  
  153. SELECT ENAME,SAL,DEPTNO FROM EMP E WHERE E.SAL > (SELECT AVG(SAL) FROM EMP E2 WHERE E.DEPTNO = E2.DEPTNO);
  154.  
  155. SELECT EMP.*, CASE WHEN HIREDATE = (select MAX(HIREDATE) from EMP) THEN '*' ELSE' ' END AS MAXDATE FROM EMP;
  156.  
  157. SELECT * FROM EMP E1,(
  158. SELECT E1.MGR,E1.EMPNO,
  159. CASE WHEN e1.SAL >= 700 AND e1.SAL <=1200 THEN '1'
  160. WHEN e1.SAL >= 1201 AND e1.SAL <=1400 THEN '2'
  161. WHEN e1.SAL >= 1401 AND e1.SAL <= 2000 THEN '3'
  162. WHEN e1.SAL >= 2001 AND e1.SAL <= 3000 THEN '4'
  163. WHEN e1.SAL >= 3001 AND e1.SAL <= 9999 THEN '5' ELSE '0' END AS salos FROM EMP e1),emp e2
  164. where E1.MGR = E2.EMPNO AND salos > (
  165. SELECT CASE
  166. WHEN e2.SAL >= 700 AND e2.SAL <=1200 THEN '1'
  167. WHEN e2.SAL >= 1201 AND e2.SAL <=1400 THEN '2'
  168. WHEN E2.SAL >= 1401 AND e2.SAL <= 2000 THEN '3'
  169. WHEN e2.SAL >= 2001 AND e2.SAL <= 3000 THEN '4'
  170. WHEN e2.SAL >= 3001 AND e2.SAL <= 9999 THEN '5' ELSE '0' END AS SALOS2 FROM EMP E2)
  171.  
  172. SELECT e1.EMPNO,grade,e1.mgr
  173. from emp E1,SALGRADE WHERE E1.SAL > SALGRADE.LOSAL AND E1.SAL < SALGRADE.HISAL AND GRADE <
  174. (SELECT GRADE FROM EMP E2,SALGRADE WHERE E2.SAL>SALGRADE.LOSAL AND E2.SAL<SALGRADE.HISAL and e1.mgr = e2.empno)
  175. group by e1.empno,grade,e1.mgr;
  176.  
  177. select e1.empno,salgrade.grade,e1.mgr
  178. from emp e1,salgrade where e1.sal > salgrade.losal and e1.sal < salgrade.hisal and grade <
  179. (select grade from emp e2,salgrade where
  180. e2.sal>salgrade.losal and e2.sal<salgrade.hisal and e2.mgr=e1.empno) group by e1.empno,grade,e1.mgr;
  181.  
  182. select a.empno,grade,a.sal from salgrade,emp a inner join emp b on a.mgr = b.empno
  183. where a.sal>salgrade.losal and a.sal<salgrade.hisal and grade > (select grade from salgrade,emp b where b.sal>salgrade.losal and b.sal<salgrade.hisal and a.mgr = b.empno)
  184.  
  185.  
  186. select * from emp order by hiredate;
  187.  
  188. SELECT E.DEPTNO,DEPT.LOC, COUNT(E.DEPTNO) FROM DEPT,EMP E WHERE DEPT.DEPTNO = E.DEPTNO GROUP BY DEPT.LOC,E.DEPTNO;
  189. SELECT COUNT(*) FROM EMP;
  190.  
  191. SELECT hiredate from emp group by hiredate having count(hiredate) > 1;
  192. select ename,hiredate from emp where hiredate = ( select hiredate from emp group by hiredate having count(hiredate) > 1);
  193.  
  194. select * from emp where job NOT LIKE 'ANALYST' and sal IN (select sal FROM EMP E WHERE E.JOB LIKE 'ANALYST' AND EMP.EMPNO = E.EMPNO);
  195. select * from emp where job not like 'ANALYST';
  196. select * FROM EMP E WHERE E.JOB LIKE 'ANALYST';
  197.  
  198. SELECT JOB,MAX(GRADE) FROM EMP, SALGRADE WHERE SAL>=LOSAL AND SAL<=HISAL GROUP BY JOB;
  199.  
  200.  
  201. select e1.empno,salgrade.grade,e1.mgr,JOB
  202. from emp e1,salgrade where e1.sal >= salgrade.losal and e1.sal <= salgrade.hisal ORDER BY JOB;
  203.  
  204. SELECT DISTINCT E.JOB FROM DEPT a, EMP E WHERE a.DEPTNO = E.DEPTNO AND a.LOC = 'DALLAS' AND JOB NOT IN
  205. (SELECT DISTINCT JOB FROM EMP E1,DEPT D WHERE D.DEPTNO = E1.DEPTNO AND d.LOC = 'CHICAGO');
  206.  
  207. SELECT DISTINCT JOB FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND DEPT.LOC = 'CHICAGO';
  208.  
  209. select sum(case when comm is not null then comm else 55 end) as suma from emp;
  210. desc emp;
  211.  
  212. select dept.loc,(SELECT COUNT(*) from emp,dept where job = 'ANALYST'
  213. and emp.deptno = dept.deptno group by dept.deptno) from dept group by dept.loc;
  214.  
  215. SELECT COUNT(*) AS COUNT, DEPT.LOC FROM DEPT,EMP WHERE DEPT.DEPTNO = EMP.DEPTNO AND EMPNO IN
  216. (SELECT distinct MGR FROM EMP) GROUP BY DEPT.LOC having avg(sal)>1800;
  217.  
  218. select ('Mr '||ENAME||' who has a position of '||job|| 'is supervised by '||(SELECT ENAME FROM EMP E WHERE E.EMPNO = EMP.MGR)) FROM EMP;
  219.  
  220. DESC EMP;
  221.  
  222. CREATE TABLE PEOPLEz (
  223. idp int,
  224. surname varchar(30),
  225. position varchar(30),
  226. income int);
  227.  
  228. INSERT INTO PEOPLEZ VALUES (1,'KOWALSKI','EXPERT',3800);
  229. INSERT INTO PEOPLEZ SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB = 'ANALYST';
  230. CREATE TABLE DIVISION (
  231. divno,divname,divloc)
  232. as (select * from dept);
  233. ALTER TABLE DIVISION RENAME TO BRANCHE;
  234. SELECT * FROM BRANCHE;
  235.  
  236. UPDATE BRANCHE SET DIVLOC = 'WARSAW';
  237. ALTER TABLE BRANCHE ALTER COLUMN DIVLOC VARCHAR(30) NOT NULL;
  238.  
  239. UPDATE PEOPLEZ SET SURNAME = 'ARENT' WHERE POSITION = 'PRESIDENT';
  240.  
  241. select * from salgrade;
  242. select * from emp where sal >= 1000 and sal <= 2000;
  243. select * from dept order by deptno;
  244. select distinct job from emp;
  245. select * from emp where deptno = 10 OR deptno = 20 order by job;
  246. select * from emp where deptno = 20 and job = 'CLERK';
  247. select * from emp where ename LIKE '%TH%' OR ename like '%LL%'
  248. select * from emp where sal <1000 OR sal > 2000 and ename like '_____'
  249. select * from emp where ename not like '%L%' and sal NOT IN(800,1600,3000);
  250. select * from emp where mgr is null;
  251. select ename,((sal*12)+comm) as ANNSAL from emp where job = 'SALESMAN';
  252. select * from emp where job = 'MANAGER';
  253. select * from emp where HIREDATE > '1983-01-01'
  254. SELECT * FROM EMP WHERE SAL>COMM;
  255. SELECT E.*,D.* FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;
  256. SELECT E.ENAME,D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;
  257. SELECT E.ENAME,D.LOC,D.DEPTNO,E.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;
  258. SELECT E.ENAME,D.LOC,D.DNAME FROM EMP E,DEPT D WHERE E.SAL>1500 AND E.DEPTNO = D.DEPTNO;
  259. SELECT E.ENAME,E.SAL,E.JOB,GRADE FROM EMP E,SALGRADE WHERE E.SAL>=LOSAL AND E.SAL<=HISAL
  260. SELECT E.ENAME,E.SAL,E.JOB,GRADE FROM EMP E,SALGRADE WHERE E.SAL>=LOSAL AND E.SAL<=HISAL and grade = 3;
  261. select e.ename,d.loc from emp e,dept d where d.loc = 'DALLAS' AND e.deptno = d.deptno;
  262. SELECT E.SAL,E.ENAME,E.JOB,F.SAL FROM EMP E,EMP F WHERE E.MGR = F.EMPNO AND F.SAL > E.SAL;
  263. SELECT DISTINCT JOB FROM EMP WHERE DEPTNO IN(10,30);
  264. SELECT JOB FROM EMP WHERE DEPTNO = 10 AND JOB IN (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 30);
  265. SELECT JOB FROM EMP WHERE DEPTNO = 10 AND JOB NOT IN (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 30);
  266. SELECT ROUND(AVG(SAL),2) FROM EMP;
  267. SELECT MIN(SAL) FROM EMP WHERE JOB = 'CLERK';
  268. SELECT COUNT(EMPNO) FROM EMP WHERE DEPTNO = 20;
  269. SELECT AVG(SAL) FROM EMP WHERE JOB != 'MANAGER';
  270. SELECT MAX(SAL),JOB FROM EMP GROUP BY JOB;
  271. SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>3;
  272. SELECT JOB FROM EMP GROUP BY JOB HAVING AVG(SAL) >= 3000;
  273. SELECT JOB,CASE WHEN COMM IS NULL THEN AVG(SAL) WHEN COMM IS NOT NULL THEN AVG(SAL+COMM) ELSE 0 END AS AVGMONTH,
  274. CASE WHEN COMM IS NULL THEN AVG(SAL*12) WHEN COMM IS NOT NULL THEN AVG((SAL*12)+COMM) ELSE 0 END AS AVGYEARz FROM EMP GROUP BY JOB,COMM
  275.  
  276. SELECT (MAX(SAL) - MIN(SAL))AS DIFFERENCE FROM EMP;
  277.  
  278. SELECT CASE WHEN COUNT(DISTINCT EMPNO)=COUNT(EMPNO) THEN 'UNIQUE' ELSE 'NOT UNIQUE' END FROM EMP;
  279.  
  280. SELECT F.EMPNO,MIN(E.SAL) FROM EMP E,EMP F WHERE E.MGR=F.EMPNO AND E.SAL>1000 GROUP BY F.EMPNO;
  281. SELECT COUNT(EMPNO) FROM EMP, DEPT WHERE DEPT.LOC='DALLAS' AND EMP.DEPTNO = DEPT.DEPTNO;
  282.  
  283. SELECT GRADE,MAX(SAL),LOSAL,HISAL FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<HISAL GROUP BY GRADE,LOSAL,HISAL ORDER BY GRADE;
  284.  
  285. SELECT SAL,COUNT(*) FROM EMP GROUP BY SAL HAVING COUNT(*) > 1;
  286. SELECT GRADE,AVG(SAL),LOSAL,HISAL FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<HISAL AND GRADE = 2 GROUP BY GRADE,LOSAL,HISAL;
  287. SELECT F.EMPNO,F.ENAME,COUNT(E.ENAME) FROM EMP E,EMP F WHERE E.MGR=F.EMPNO GROUP BY F.EMPNO,F.ENAME;
  288. SELECT SAL FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<HISAL AND GRADE=1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement