Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP);
- SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME LIKE 'BLAKE');
- SELECT ENAME FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);
- SELECT ENAME FROM EMP WHERE SAL IN (SELECT MIN(SAL);
- SELECT * FROM EMP WHERE sal = (SELECT sal FROM EMP WHERE sal = MIN(SAL);
- SELECT * FROM SALGRADE;
- CREATE TABLE PEOPLE (
- IDP INT NOT NULL,
- SURAME VARCHAR(50) NOT NULL,
- POSITION VARCHAR(50) NOT NULL,
- INCOME INT NOT NULL);
- INSERT INTO PEOPLE (IDP,SURAME,POSITION,INCOME) VALUES (1,'KOWALSKI','EXPERT','3800');
- INSERT INTO PEOPLE SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB LIKE 'ANALYST';
- CREATE TABLE DIVISION (
- DIVNO INT NOT NULL,
- DIVNAME VARCHAR(50) NOT NULL,
- DIVLOC VARCHAR(50) NOT NULL);
- INSERT INTO DIVISION SELECT * FROM DEPT;
- ALTER TABLE DIVISION ADD (HEAD VARCHAR2(20));
- ALTER TABLE DIVISION RENAME TO BRANCH1;
- SELECT * FROM BRANCH1;
- UPDATE BRANCH1 SET DIVLOC = 'WARSAW';
- UPDATE PEOPLE SET SURAME = 'ARENT' WHERE POSITION = 'PRESIDENT';
- DROP TABLE PEOPLE;
- CREATE TABLE GROUPIE( GROUPNO INT PRIMARY KEY, GROUPNAME VARCHAR(50));
- DROP TABLE GROUPIE;
- CREATE TABLE STUDENTE( STUDENTID INT, GROUPNO INT NOT NULL, STUDENTNAME VARCHAR(50), CONSTRAINT g_id FOREIGN KEY (GROUPNO) REFERENCES GROUPIE(GROUPNO));
- select * from emp,dept where emp.deptno=dept.deptno;
- select * from emp;
- select emp.ename,dept.dname,dept.deptno from emp,dept where emp.deptno=dept.deptno;
- select emp.ename,dept.loc,dept.dname,emp.sal from emp,dept where emp.deptno=dept.deptno and sal>1500;
- select * from salgrade;
- select ename from emp where sal>1401 and sal<2000;
- select emp.ename,dept.loc from emp,dept where emp.deptno=dept.deptno and dept.loc='DALLAS';
- select * from emp;
- select A.* from EMP A WHERE A.SAL < (SELECT B.SAL FROM EMP b WHERE A.MGR=B.EMPNO);
- select distinct job from emp where deptno=10 or deptno=30;
- select * from emp order by deptno;
- select distinct job from emp where DEPTNO=10 AND JOB in (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=30);
- SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=10;
- SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=30;
- select distinct job from emp where DEPTNO=30 AND JOB NOT IN (SELECT JOB FROM EMP WHERE DEPTNO=10);
- SELECT * FROM SALGRADE;
- SELECT ENAME,DEPTNO,SAL FROM EMP WHERE SAL > 1000 AND SAL < 2000;
- SELECT DISTINCT JOB FROM EMP;
- SELECT * FROM EMP ORDER BY JOB;
- SELECT * FROM EMP WHERE DEPTNO=20 AND JOB='CLERK';
- SELECT ENAME FROM EMP WHERE ENAME LIKE '%TH%' OR ENAME LIKE '%LL%';
- SELECT * FROM EMP WHERE SAL < 1000 or SAL > 2000 AND ENAME LIKE '_____';
- SELECT * FROM EMP WHERE ENAME NOT LIKE '%L%' AND SAL NOT IN(800,1600,3000);
- SELECT * FROM EMP WHERE MGR IS NOT NULL;
- SELECT ENAME,JOB,((SAL*12)+COMM) AS ANNSAL FROM EMP WHERE JOB = 'SALESMAN';
- SELECT * FROM EMP WHERE JOB='MANAGER' AND DEPTNO=10;
- SELECT * FROM EMP WHERE HIREDATE > '83/01/01' AND HIREDATE < '83/12/12';
- SELECT ENAME,JOB,(SAL*12)ANNSAL,COMM FROM EMP WHERE JOB = 'SALESMAN' AND SAL > COMM ORDER BY SAL DESC;
- SELECT (ENAME||' HAS HELD THE POSITION OF '||JOB||' IN DEPARTMENT '||DEPTNO||' SINCE '||HIREDATE) "WHO, WHAT, WHEN" FROM EMP;
- select ROUND(AVG(SAL),3) from EMP;
- select min(sal) from EMP where job = 'CLERK';
- select count(*) from EMP where deptno = 20;
- select round(avg(sal),3),job from EMP group by job;
- select round(avg(sal),3),job from EMP where job != 'MANAGER' group by job;
- select round(avg(sal),3),job,deptno from EMP group by deptno,job order by deptno;
- select max(sal),job from EMP group by job;
- select deptno,avg(sal) from emp group by deptno having count(*) > 3;
- select count(*) from emp where deptno = 20;
- select job,avg(sal) from emp group by job having avg(sal) >= 3000;
- SELECT JOB,
- CASE
- WHEN JOB = 'SALESMAN'
- THEN AVG((SAL*12)+COMM)
- ELSE AVG(SAL*12)
- END
- FROM EMP GROUP BY JOb;
- SELECT (MAX(SAL)-MIN(SAL)) FROM EMP;
- SELECT DEPTNO,COUNT(*) FROM EMP group by deptno having count(*) > 3;
- SELECT CASE
- WHEN COUNT(DISTINCT EMPNO) = COUNT(*) THEN 'TRUE'
- ELSE 'FALSE'
- END
- FROM EMP;
- SELECT MIN(SAL),MGR,JOB FROM EMP WHERE SAL>1000 and MGR is not null group by mgr,job order by min(sal);
- SELECT COUNT(*) FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DEPT.LOC='DALLAS';
- SELECT * FROM SALGRADE;
- SELECT SAL FROM EMP GROUP BY SAL HAVING COUNT(SAL)>=2;
- SELECT SAL FROM EMP ORDER BY SAL;
- SELECT AVG(SAL) FROM EMP WHERE SAL>1200 AND SAL<1401;
- SELECT MGR,COUNT(*) FROM EMP WHERE JOB != 'MANAGER' AND MGR IS NOT NULL GROUP BY MGR;
- SELECT * FROM EMP ORDER BY MGR;
- SELECT SUM(SAL) FROM EMP WHERE SAL >700 AND SAL<1200;
- SELECT * FROM EMP WHERE SAL = (SELECT MIN(SAL) FROM EMP);
- SELECT MIN(SAL) FROM EMP WHERE JOB = 'CLERK';
- SELECT * FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'BLAKE') AND ENAME != 'BLAKE';
- SELECT * FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY DEPTNO);
- SELECT * FROM EMP WHERE SAL > ANY (SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30);
- SELECT * FROM EMP WHERE SAL > ANY (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30);
- SELECT MAX(AVG(SAL)) FROM EMP WHERE JOB != 'PRESIDENT' GROUP BY SAL;
- SELECT * FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND DNAME ='SALES');
- SELECT MAX(SAL) FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND DNAME ='SALES';
- select * from emp e where sal > (select avg(sal) from emp e2 where e2.deptno = e.deptno);
- select E.* from emp E where EXISTS (SELECT E2.* FROM EMP E2 WHERE E2.MGR = E.EMPNO);
- SELECT * FROM EMP;
- select * from branch;
- SELECT * FROM DEPT;
- select * from branch1;
- select e.* from emp e where e.sal IN (select max(sal) from emp e2 where e.job = e2.job) order by sal desc;
- select max(sal),job from emp group by job;
- select e.* from emp e where sal = (select min(sal) from emp e2 where e.deptno = e2.deptno) order by ename;
- select e.* from emp e where hiredate = (select max(hiredate) from emp e2 where e.deptno = e2.deptno) order by hiredate;
- select e.ename,e.sal,e.deptno from emp e where sal > (select avg(sal) from emp e2 where e2.deptno = e.deptno);
- select ename,sal from ( select ename,sal from emp order by sal desc) where rownum <=3;
- SELECT COUNT(*),DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM EMP GROUP BY DEPTNO);
- SELECT ENAME,SAL,DEPTNO FROM EMP E WHERE E.SAL > (SELECT AVG(SAL) FROM EMP E2 WHERE E.DEPTNO = E2.DEPTNO);
- SELECT EMP.*, CASE WHEN HIREDATE = (select MAX(HIREDATE) from EMP) THEN '*' ELSE' ' END AS MAXDATE FROM EMP;
- SELECT * FROM EMP E1,(
- SELECT E1.MGR,E1.EMPNO,
- CASE WHEN e1.SAL >= 700 AND e1.SAL <=1200 THEN '1'
- WHEN e1.SAL >= 1201 AND e1.SAL <=1400 THEN '2'
- WHEN e1.SAL >= 1401 AND e1.SAL <= 2000 THEN '3'
- WHEN e1.SAL >= 2001 AND e1.SAL <= 3000 THEN '4'
- WHEN e1.SAL >= 3001 AND e1.SAL <= 9999 THEN '5' ELSE '0' END AS salos FROM EMP e1),emp e2
- where E1.MGR = E2.EMPNO AND salos > (
- SELECT CASE
- WHEN e2.SAL >= 700 AND e2.SAL <=1200 THEN '1'
- WHEN e2.SAL >= 1201 AND e2.SAL <=1400 THEN '2'
- WHEN E2.SAL >= 1401 AND e2.SAL <= 2000 THEN '3'
- WHEN e2.SAL >= 2001 AND e2.SAL <= 3000 THEN '4'
- WHEN e2.SAL >= 3001 AND e2.SAL <= 9999 THEN '5' ELSE '0' END AS SALOS2 FROM EMP E2)
- SELECT e1.EMPNO,grade,e1.mgr
- from emp E1,SALGRADE WHERE E1.SAL > SALGRADE.LOSAL AND E1.SAL < SALGRADE.HISAL AND GRADE <
- (SELECT GRADE FROM EMP E2,SALGRADE WHERE E2.SAL>SALGRADE.LOSAL AND E2.SAL<SALGRADE.HISAL and e1.mgr = e2.empno)
- group by e1.empno,grade,e1.mgr;
- select e1.empno,salgrade.grade,e1.mgr
- from emp e1,salgrade where e1.sal > salgrade.losal and e1.sal < salgrade.hisal and grade <
- (select grade from emp e2,salgrade where
- e2.sal>salgrade.losal and e2.sal<salgrade.hisal and e2.mgr=e1.empno) group by e1.empno,grade,e1.mgr;
- select a.empno,grade,a.sal from salgrade,emp a inner join emp b on a.mgr = b.empno
- 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)
- select * from emp order by hiredate;
- SELECT E.DEPTNO,DEPT.LOC, COUNT(E.DEPTNO) FROM DEPT,EMP E WHERE DEPT.DEPTNO = E.DEPTNO GROUP BY DEPT.LOC,E.DEPTNO;
- SELECT COUNT(*) FROM EMP;
- SELECT hiredate from emp group by hiredate having count(hiredate) > 1;
- select ename,hiredate from emp where hiredate = ( select hiredate from emp group by hiredate having count(hiredate) > 1);
- 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);
- select * from emp where job not like 'ANALYST';
- select * FROM EMP E WHERE E.JOB LIKE 'ANALYST';
- SELECT JOB,MAX(GRADE) FROM EMP, SALGRADE WHERE SAL>=LOSAL AND SAL<=HISAL GROUP BY JOB;
- select e1.empno,salgrade.grade,e1.mgr,JOB
- from emp e1,salgrade where e1.sal >= salgrade.losal and e1.sal <= salgrade.hisal ORDER BY JOB;
- SELECT DISTINCT E.JOB FROM DEPT a, EMP E WHERE a.DEPTNO = E.DEPTNO AND a.LOC = 'DALLAS' AND JOB NOT IN
- (SELECT DISTINCT JOB FROM EMP E1,DEPT D WHERE D.DEPTNO = E1.DEPTNO AND d.LOC = 'CHICAGO');
- SELECT DISTINCT JOB FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND DEPT.LOC = 'CHICAGO';
- select sum(case when comm is not null then comm else 55 end) as suma from emp;
- desc emp;
- select dept.loc,(SELECT COUNT(*) from emp,dept where job = 'ANALYST'
- and emp.deptno = dept.deptno group by dept.deptno) from dept group by dept.loc;
- SELECT COUNT(*) AS COUNT, DEPT.LOC FROM DEPT,EMP WHERE DEPT.DEPTNO = EMP.DEPTNO AND EMPNO IN
- (SELECT distinct MGR FROM EMP) GROUP BY DEPT.LOC having avg(sal)>1800;
- select ('Mr '||ENAME||' who has a position of '||job|| 'is supervised by '||(SELECT ENAME FROM EMP E WHERE E.EMPNO = EMP.MGR)) FROM EMP;
- DESC EMP;
- CREATE TABLE PEOPLEz (
- idp int,
- surname varchar(30),
- position varchar(30),
- income int);
- INSERT INTO PEOPLEZ VALUES (1,'KOWALSKI','EXPERT',3800);
- INSERT INTO PEOPLEZ SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB = 'ANALYST';
- CREATE TABLE DIVISION (
- divno,divname,divloc)
- as (select * from dept);
- ALTER TABLE DIVISION RENAME TO BRANCHE;
- SELECT * FROM BRANCHE;
- UPDATE BRANCHE SET DIVLOC = 'WARSAW';
- ALTER TABLE BRANCHE ALTER COLUMN DIVLOC VARCHAR(30) NOT NULL;
- UPDATE PEOPLEZ SET SURNAME = 'ARENT' WHERE POSITION = 'PRESIDENT';
- select * from salgrade;
- select * from emp where sal >= 1000 and sal <= 2000;
- select * from dept order by deptno;
- select distinct job from emp;
- select * from emp where deptno = 10 OR deptno = 20 order by job;
- select * from emp where deptno = 20 and job = 'CLERK';
- select * from emp where ename LIKE '%TH%' OR ename like '%LL%'
- select * from emp where sal <1000 OR sal > 2000 and ename like '_____'
- select * from emp where ename not like '%L%' and sal NOT IN(800,1600,3000);
- select * from emp where mgr is null;
- select ename,((sal*12)+comm) as ANNSAL from emp where job = 'SALESMAN';
- select * from emp where job = 'MANAGER';
- select * from emp where HIREDATE > '1983-01-01'
- SELECT * FROM EMP WHERE SAL>COMM;
- SELECT E.*,D.* FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;
- SELECT E.ENAME,D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;
- SELECT E.ENAME,D.LOC,D.DEPTNO,E.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO;
- SELECT E.ENAME,D.LOC,D.DNAME FROM EMP E,DEPT D WHERE E.SAL>1500 AND E.DEPTNO = D.DEPTNO;
- SELECT E.ENAME,E.SAL,E.JOB,GRADE FROM EMP E,SALGRADE WHERE E.SAL>=LOSAL AND E.SAL<=HISAL
- SELECT E.ENAME,E.SAL,E.JOB,GRADE FROM EMP E,SALGRADE WHERE E.SAL>=LOSAL AND E.SAL<=HISAL and grade = 3;
- select e.ename,d.loc from emp e,dept d where d.loc = 'DALLAS' AND e.deptno = d.deptno;
- SELECT E.SAL,E.ENAME,E.JOB,F.SAL FROM EMP E,EMP F WHERE E.MGR = F.EMPNO AND F.SAL > E.SAL;
- SELECT DISTINCT JOB FROM EMP WHERE DEPTNO IN(10,30);
- SELECT JOB FROM EMP WHERE DEPTNO = 10 AND JOB IN (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 30);
- SELECT JOB FROM EMP WHERE DEPTNO = 10 AND JOB NOT IN (SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 30);
- SELECT ROUND(AVG(SAL),2) FROM EMP;
- SELECT MIN(SAL) FROM EMP WHERE JOB = 'CLERK';
- SELECT COUNT(EMPNO) FROM EMP WHERE DEPTNO = 20;
- SELECT AVG(SAL) FROM EMP WHERE JOB != 'MANAGER';
- SELECT MAX(SAL),JOB FROM EMP GROUP BY JOB;
- SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>3;
- SELECT JOB FROM EMP GROUP BY JOB HAVING AVG(SAL) >= 3000;
- SELECT JOB,CASE WHEN COMM IS NULL THEN AVG(SAL) WHEN COMM IS NOT NULL THEN AVG(SAL+COMM) ELSE 0 END AS AVGMONTH,
- 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
- SELECT (MAX(SAL) - MIN(SAL))AS DIFFERENCE FROM EMP;
- SELECT CASE WHEN COUNT(DISTINCT EMPNO)=COUNT(EMPNO) THEN 'UNIQUE' ELSE 'NOT UNIQUE' END FROM EMP;
- SELECT F.EMPNO,MIN(E.SAL) FROM EMP E,EMP F WHERE E.MGR=F.EMPNO AND E.SAL>1000 GROUP BY F.EMPNO;
- SELECT COUNT(EMPNO) FROM EMP, DEPT WHERE DEPT.LOC='DALLAS' AND EMP.DEPTNO = DEPT.DEPTNO;
- SELECT GRADE,MAX(SAL),LOSAL,HISAL FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<HISAL GROUP BY GRADE,LOSAL,HISAL ORDER BY GRADE;
- SELECT SAL,COUNT(*) FROM EMP GROUP BY SAL HAVING COUNT(*) > 1;
- SELECT GRADE,AVG(SAL),LOSAL,HISAL FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<HISAL AND GRADE = 2 GROUP BY GRADE,LOSAL,HISAL;
- SELECT F.EMPNO,F.ENAME,COUNT(E.ENAME) FROM EMP E,EMP F WHERE E.MGR=F.EMPNO GROUP BY F.EMPNO,F.ENAME;
- SELECT SAL FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<HISAL AND GRADE=1;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement