SQL*Plus: Release 8.0.6.0.0 - Production on Ndz Kwi 5 15:40:14 2009 (c) Copyright 1999 Oracle Corporation. All rights reserved. ERROR: ORA-12514: Message 12514 not found; product=RDBMS80; facility=ORA Połączony z: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM --------- ---------- --------- --------- -------- --------- --------- --- 7839 KING PRESIDENT 81/11/17 5000 7698 BLAKE MANAGER 7839 81/05/01 2850 7782 CLARK MANAGER 7839 81/06/09 2450 7566 JONES MANAGER 7839 81/04/02 2975 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 7844 TURNER SALESMAN 7698 81/09/08 1500 0 7900 JAMES CLERK 7698 81/12/03 950 7521 WARD SALESMAN 7698 81/02/22 1250 500 7902 FORD ANALYST 7566 81/12/03 3000 7369 SMITH CLERK 7902 80/12/17 800 7788 SCOTT ANALYST 7566 82/12/09 3000 7876 ADAMS CLERK 7788 83/01/12 1100 7934 MILLER CLERK 7782 82/01/23 1300 14 wierszy zostało wybranych. SQL> select ename, job 2 from emp 3 where job=(select job 4 from emp 5 where ename='blake'); nie wybrano żadnych wierszy SQL> select ename, job 2 from emp 3 where job=(select job 4 from emp 5 where ename='BLAKE'); ENAME JOB ---------- --------- BLAKE MANAGER CLARK MANAGER JONES MANAGER SQL> select * from dept; DEPTNO DNAME LOC --------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select ename, sal 2 from emp 3 where sal=(select sal 4 from emp 5 where 6 SQL> SQL> select ename, sal 2 form emp 3 where sal=(select min(sal) 4 from emp 5 group by deptno); form emp * ERROR w linii 2: ORA-00923: FROM keyword not found where expected SQL> select ename, sal 2 from emp 3 where sal=(select min(sal) 4 from emp 5 group by deptno); where sal=(select min(sal) * ERROR w linii 3: ORA-01427: single-row subquery returns more than one row SQL> select ename, sal 2 from emp 3 where sal in (select min(sal) 4 from emp 5 group by deptno); ENAME SAL ---------- --------- JAMES 950 SMITH 800 MILLER 1300 SQL> select e.name, e.sal, d.dname 2 form emp e, dept d 3 where e.sal in (select min(sal) 4 from emp 5 group by deptno); form emp e, dept d * ERROR w linii 2: ORA-00923: FROM keyword not found where expected SQL> select e.name, e.sal, d.dname 2 fo 3 SQL> select e.ename, e.sla, d.name 2 from emp e, dept d 3 where e.sal in (select min(sal) 4 from emp 5 group by deptno); select e.ename, e.sla, d.name * ERROR w linii 1: ORA-00904: "D"."NAME": invalid identifier SQL> select e.ename, e.sal, d.dname 2 from emp e, dept d 3 where e.sal in (select min(sal) 4 from emp 5 group by deptno); ENAME SAL DNAME ---------- --------- -------------- JAMES 950 ACCOUNTING JAMES 950 RESEARCH JAMES 950 SALES JAMES 950 OPERATIONS SMITH 800 ACCOUNTING SMITH 800 RESEARCH SMITH 800 SALES SMITH 800 OPERATIONS MILLER 1300 ACCOUNTING MILLER 1300 RESEARCH MILLER 1300 SALES MILLER 1300 OPERATIONS 12 wierszy zostało wybranych. SQL> select e.ename, e.sal, d.dname 2 from emp e, dept d 3 where e.deptno=d.deptno and 4 SQL> e.sal in (select min(sal) nieznane polecenie początek "e.sal in ..." - reszta linii pomijana. SQL> select e.ename, e.sal, d.dname 2 from emp e, dept d 3 where e.deptno=d.deptno and 4 (select min(sal) 5 from emp 6 group by deptno); group by deptno) * ERROR w linii 6: ORA-00936: missing expression SQL> select e.ename, e.sal, d.dname 2 from emp e, dept d 3 where e.deptno=d.deptno and 4 e.sal in (select min(sal) 5 from emp 6 group by deptno); ENAME SAL DNAME ---------- --------- -------------- JAMES 950 SALES SMITH 800 RESEARCH MILLER 1300 ACCOUNTING SQL> select avg(e.sal), d.dname 2 from emp e, dept d 3 where e.deptno = d.deptno 4 group by d.dname 5 having avg(e.sal)>2500; AVG(E.SAL) DNAME ---------- -------------- 2916,6667 ACCOUNTING SQL> select job, avg(sal) 2 from emp 3 group by job 4 having avg(sal)=(select min(avg(sal)) 5 from emp 6 group by job); JOB AVG(SAL) --------- --------- CLERK 1037,5 SQL> select ename, sal 2 from (select ename, sal 3 from emp 4 order by sal desc) 5 where rownum <=3; ENAME SAL ---------- --------- KING 5000 FORD 3000 SCOTT 3000 SQL> select e.ename, e.sal, d.dname 2 from emp e, dept d 3 where e.deptno=d.deptno and 4 e.sal=(select min(sal) 5 from emp 6 where deptno=e.deptno); ENAME SAL DNAME ---------- --------- -------------- JAMES 950 SALES SMITH 800 RESEARCH MILLER 1300 ACCOUNTING SQL> select e.ename, e.sal, d.dname, (select min(sal) from emp where deptno=e.deptno) 2 from emp e, dept d 3 where e.deptno=d.deptno and 4 e.sal<(select avg(sal) 5 from emp 6 where deptno=e.deptno); ENAME SAL DNAME (SELECTMIN(SAL)FROMEMPWHEREDEPTNO=E.DEPTNO) ---------- --------- -------------- ------------------------------------------- CLARK 2450 ACCOUNTING 1300 MARTIN 1250 SALES 950 TURNER 1500 SALES 950 JAMES 950 SALES 950 WARD 1250 SALES 950 SMITH 800 RESEARCH 800 ADAMS 1100 RESEARCH 800 MILLER 1300 ACCOUNTING 1300 8 wierszy zostało wybranych. SQL> SQL> select e.ename, e.sal, d.dname, d.sr 2 from emp e, (select d1.dname, d1.deptno, avg(e1.sal) sr 3 from emp e1, dept d1 4 where e1.deptno=d1.deptno 5 group by d1.dname, d1.deptno) d 6 where e.deptno=d.deptno and 7 e.sal