- •Раздел 1 Утилита sql*Plus, простые запросы выборки столбцов и строк
- •Раздел 2 Функции Oracle sql
- •Раздел 3 Выборка данных из нескольких таблиц
- •Раздел 4 Другие команды языка манипулирования данными dмl и обработка транзакций
- •Раздел 5 Создание и изменение таблиц средствами ddl
- •Раздел 6 Другие объекты базы данных
Раздел 3 Выборка данных из нескольких таблиц
3.1. Подзапросы
Q3_1
Использование подзапросов
Найти служащих , получающих минимальную зарплату
SQL> select ename name,sal from EMP
where sal=(select min(sal) from EMP);
NAMESAL
---------- ---------
SMITH800
Q3_2
Сотрудники отдела продаж (SALES) , занимающих ту же должность , что и служащийSMITH
SQL> select EMPNO,ename,job,sal
from EMP
where job=(select job from EMP
where ename='SMITH')
and DEPTNO=(select DEPTNO from DEPT
where dname='SALES');
EMPNO ENAME JOB SAL
--------- ---------- --------- ---------
7900 JAMESCLERK950
Q3_3
Служащие получающие минимальный оклад в своем отделе
SQL> select DEPTNO dept,ename name,sal from EMP
where (sal,DEPTNO) in (select min(sal),DEPTNO
from EMP
group by DEPTNO);
DEPTNO NAME SAL
--------- ---------- ---------
20 SMITH 800
30 JAMES 950
10 MILLER1300
Q3_4
Список сотрудников отдела 30, получающих оклад больше минимального в отделе 20
SQL> select ename,sal,job,DEPTNO dept
from EMP
where DEPTNO=30 and
sal > any(select distinct sal from EMP
where DEPTNO=20)
order by sal,ename;
ENAME SAL JOB DEPTNO
---------- --------- --------- ---------
JAMES 950 CLERK 30
MARTIN 1250 SALESMAN 30
WARD 1250 SALESMAN 30
TURNER 1500 SALESMAN 30
ALLEN 1600 SALESMAN 30
BLAKE 2850 MANAGER 30
6 rows selected.
Q3_5
Список сотрудников , получающих оклад больше любого, работающего в отделе 30
SQL> select ename,sal,job,DEPTNO dept from EMP
where sal > all(select distinct sal
from EMP where DEPTNO=30)
order by sal,ename;
ENAME SAL JOB DEPTNO
---------- --------- --------- ---------
JONES 2975 MANAGER 20
FORD 3000 ANALYST 20
SCOTT 3000 ANALYST 20
KING 5000 PRESIDENT 10
Q3_6
Отделы, где число сотрудников больше 3 и средний оклад больше среднего оклада отдела 30
SQL> select DEPTNO dept,avg(sal) from EMP
group by DEPTNO
having count(EMPNO) > 3
and avg(sal) > (select avg(sal) from EMP
where DEPTNO = 30);
DEPTNO AVG(SAL)
--------- ---------
20 2175
Q3_7
Год когда в компанию было зачислено наибольшее количество служащих
SQL> select to_char(hiredate,'YYYY') year,count(EMPNO) num
from EMP
group by to_char(hiredate,'YYYY')
having count(EMPNO) = (select max(count(EMPNO))
from EMP
group by to_char(hiredate,'YYYY'));
YEARNUM
---- ---------
1981 10
3.2 Коррелированные подзапросы
Q3_8
Список сотрудников, получающих зарплату больше средней по отделу, где работают
SQL> select EMPNO,ename name,sal,DEPTNO
from EMP x
where sal > (select avg(sal) from EMP
where DEPTNO = x.DEPTNO)
order by DEPTNO,sal desc;
EMPNO NAME SAL DEPTNO
--------- ---------- --------- ----------
7839 KING 5000 10
7902 FORD 3000 20
7788 SCOTT 3000 20
7566 JONES 2975 20
7698 BLAKE 2850 30
7499 ALLEN 1600 30
6 rows selected.
Q3_9
Сотрудники, имеющие хотя бы одного подчиненного
SQL> select EMPNO,ename,job,DEPTNO
from EMP x
where exists (select 1 from EMP
where EMP.mgr = x.EMPNO)
order by DEPTNO;
EMPNO ENAME JOB DEPTNO
--------- ---------- --------- ----------
7839 KING PRESIDENT 10
7782 CLARK MANAGER 10
7566 JONES MANAGER 20
7788 SCOTT ANALYST 20
7902 FORD ANALYST 20
7698 BLAKE MANAGER 30
6 rowsselected.
Q3_10 Сотрудники, не имеющие подчиненных.
NOTINничего не выбирает, так как естьNULLзначение
SQL> select EMPno,ename,job,DEPTno
from EMP
where EMPno not in (select mgr from EMP);
norowsselected
Правильно так:
SQL> select EMPno,ename,job,DEPTno
from EMP
where EMPno not in (select nvl(mgr,0) from EMP);
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7654 MARTIN SALESMAN 30
7499 ALLEN SALESMAN 30
7844 TURNER SALESMAN 30
7900 JAMES CLERK 30
7521 WARD SALESMAN 30
7369 SMITH CLERK 20
7876 ADAMS CLERK 20
7934 MILLER CLERK 10
8 rows selected.
Q3_11
Список сотрудников , не имеющих подчиненных
SQL> select EMPNO,ename,job,DEPTNO
from EMP x
where not exists (select mgr from EMP
where mgr = x.EMPNO)
order by DEPTNO;
EMPNO ENAME JOB DEPTNO
--------- ---------- --------- ----------
7934 MILLER CLERK 10
7369 SMITH CLERK 20
7876 ADAMS CLERK 20
7654 MARTIN SALESMAN 30
7499 ALLEN SALESMAN 30
7844 TURNER SALESMAN 30
7900 JAMES CLERK 30
7521 WARD SALESMAN 30
8 rows selected.
Q3_11a
Коррелированный подзапрос при использовании оператора UPDATE.
Создадим таблицу emp1:
create table emp1 as select * from emp;
Изменим ее:
alter table emp1 add(dname varchar2(14);
А теперь заполним значениями новую колонку:
scott@8i> update emp1 x
set dname=(select dname from dept
where deptno=x.deptno);
scott@8i> select * from emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 RESEARCH
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 SALES
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 SALES
7566 JONES MANAGER 7839 02-APR-81 2975 20 RESEARCH
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 SALES
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 SALES
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 ACCOUNTING
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 RESEARCH
7839 KING PRESIDENT 17-NOV-81 5000 10 ACCOUNTING
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 SALES
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 RESEARCH
7900 JAMES CLERK 7698 03-DEC-81 950 30 SALES
7902 FORD ANALYST 7566 03-DEC-81 3000 20 RESEARCH
7934 MILLER CLERK 7782 23-JAN-82 1300 10 ACCOUNTING
14 rows selected.
3.3. Использование подзапросов во фразе FROM
Q3_12
Использование подзапросов во фразе FROM. В нижеприводимом примереby_deptиtotal- псевдонимы подзапросов. В отличие от псевдонимов столбцов использованиеASдля подзапроса во фразеFROMнедопустимо.
Процент служащих и зарплаты по отделам
SQL> select DEPTNO "Dept",
round(100*by_dept.dept_emp/total.total_emp,0) "% Serves",
round(100*by_dept.dept_sal/total.total_sal,0) "% Sal"
from (select count(EMPNO) dept_emp,
sum(sal) dept_sal,
DEPTNO
from EMP group by DEPTNO) by_dept,
(select count(EMPNO) total_emp,
sum(sal) total_sal
from EMP) total;
DEPTNO % Serves % Sal
--------- --------- ---------
10 21 30
20 36 37
30 43 32
3.4. Операторы над множествами
Q3_13
Пересечение множеств INTERSECT
Должности, которые имеются как в отделе 10, так и в отделе 30
SQL>selectjobfromEMP
where DEPTNO=10
intersect
select job from EMP
where DEPTNO=30;
JOB
---------
CLERK
MANAGER
Q3_14
Объединение множеств UNION
Должности, которые есть в оделах 10 и 30
SQL> select job from EMP
where DEPTNO=10
union
select job from EMP
where DEPTNO=30;
JOB
---------
CLERK
MANAGER
PRESIDENT
SALESMAN
Q3_15
UNION ALL дубликаты не исключаются
SQL> select job from EMP
where DEPTNO=10
union all
select job from EMP
where DEPTNO=30;
JOB
---------
PRESIDENT
MANAGER
CLERK
MANAGER
SALESMAN
SALESMAN
SALESMAN
CLERK
SALESMAN
9 rowsselected.
Q3_16
Разность множеств MINUS
Должности сотрудников отдела 10, которых нет в отделе 30
SQL> select job from EMP
where DEPTNO=10
minus
select job from EMP
where DEPTNO=30;
JOB
---------
PRESIDENT
Q3_17
ORDERBYв операторах над множествами
Служащие отделов 10 и 30, упорядочивание по имени
SQL> select job,sal,ename from EMP
where DEPTNO=10
union
select job,sal,ename from EMP
where DEPTNO=30
order by 3;
JOB SAL ENAME
--------- --------- ----------
SALESMAN 1600 ALLEN
MANAGER 2850 BLAKE
MANAGER 2450 CLARK
CLERK 950 JAMES
PRESIDENT 5000 KING
SALESMAN 1250 MARTIN
CLERK 1300 MILLER
SALESMAN 1500 TURNER
SALESMAN 1250 WARD
9 rows selected.
3.5. Соединения
Q3_18
Соединение по равенству таблиц EMP,DEPTпо полюDEPTNO
SQL> select ename,job,dname
from EMP,DEPT
where EMP.DEPTNO=DEPT.DEPTNO;
ENAME JOB DNAME
---------- --------- --------------
KING PRESIDENT ACCOUNTING
BLAKE MANAGER SALES
CLARK MANAGER ACCOUNTING
JONES MANAGER RESEARCH
MARTIN SALESMAN SALES
ALLEN SALESMAN SALES
TURNER SALESMAN SALES
JAMES CLERK SALES
WARD SALESMAN SALES
FORD ANALYST RESEARCH
SMITH CLERK RESEARCH
SCOTT ANALYST RESEARCH
ADAMS CLERK RESEARCH
MILLER CLERK ACCOUNTING
14 rows selected.
Q3_19
Использование псевдонимов имен таблиц
SQL> select d.DEPTNO,ename,job,dname
from EMP e,DEPT d
where e.DEPTNO=d.DEPTNO
order by d.DEPTNO;
DEPTNO ENAME JOB DNAME
---------- ---------- --------- --------------
10 KING PRESIDENT ACCOUNTING
10 CLARK MANAGER ACCOUNTING
10 MILLER CLERK ACCOUNTING
20 JONES MANAGER RESEARCH
20 SCOTT ANALYST RESEARCH
20 ADAMS CLERK RESEARCH
20 SMITH CLERK RESEARCH
20 FORD ANALYST RESEARCH
30 BLAKE MANAGER SALES
30 MARTIN SALESMAN SALES
30 ALLEN SALESMAN SALES
30 TURNER SALESMAN SALES
30 JAMES CLERK SALES
30 WARD SALESMAN SALES
14 rowsselected.
Q3_20
Соединения не по равенству
Категория оплаты каждого сотрудника компании
SQL>selecte.ename,e.sal,s.grade
from EMP e,SALGRADE s
where e.sal between s.losal and s.hisal;
ENAME SAL GRADE
---------- --------- ---------
JAMES 950 1
SMITH 800 1
ADAMS 1100 1
MARTIN 1250 2
WARD 1250 2
MILLER 1300 2
ALLEN 1600 3
TURNER 1500 3
BLAKE 2850 4
CLARK 2450 4
JONES 2975 4
FORD 3000 4
SCOTT 3000 4
KING 5000 5
14 rows selected.
Q3_21
Соединение таблицы сомой с собой
Список служащих, зачисленных на работу раньше своих руководителей
SQL> select e.ename name,e.hiredate dat,e.job job,
m.ename boss,m.hiredate dat
from EMP e,EMP m
where e.mgr=m.EMPNO
and e.hiredate < m.hiredate;
NAME DAT JOB BOSS DAT
---------- --------- --------- ---------- ---------
BLAKE 01-MAY-81 MANAGER KING 17-NOV-81
CLARK 09-JUN-81 MANAGER KING 17-NOV-81
JONES 02-APR-81 MANAGER KING 17-NOV-81
ALLEN 20-FEB-81 SALESMAN BLAKE 01-MAY-81
WARD 22-FEB-81 SALESMAN BLAKE 01-MAY-81
SMITH 17-DEC-80 CLERK FORD 03-DEC-81
6 rowsselected.
Q3_22
Служащие , имеющие оклад больше оклада своего руководителя
SQL> select e.ename name,e.sal sal,
m.ename boss,m.sal sal
from EMP e,EMP m
where e.mgr=m.EMPNO
and e.sal > m.sal;
NAME SAL BOSS SAL
---------- --------- ---------- ---------
FORD 3000 JONES 2975
SCOTT3000JONES2975
3.6. Внешние соединения
Q3_23
Использование внешнего соединения для таблицы EMP. Внешнее соединение задается опреатором (+) (символ плюс, заключенный в круглые скобки) и позволяет выбрать строки одной таблицы, для которых в другой таблице нет строк, соответствующих условию соединения. Оператор (+) помещается на той стороне соединяющего условия, которая соответствует таблице с отсутствующими данными. Он предписываетOracleв случае отсутствия строк, удовлетворяющих условию соединения, возвращатьNULL(неопределенное значение) для всех выражений списка выборки, которые содержат имена столбцов таблицы с отсутствующими данными.
SQL> select d.DEPTNO,ename,job,dname
from EMP e,DEPT d
where e.DEPTNO(+)=d.DEPTNO
order by d.DEPTNO;
DEPTNO ENAME JOB DNAME
---------- ---------- --------- --------------
10 KING PRESIDENT ACCOUNTING
10 CLARK MANAGER ACCOUNTING
10 MILLER CLERK ACCOUNTING
20 JONES MANAGER RESEARCH
20 SCOTT ANALYST RESEARCH
20 ADAMS CLERK RESEARCH
20 SMITH CLERK RESEARCH
20 FORD ANALYST RESEARCH
30 BLAKE MANAGER SALES
30 MARTIN SALESMAN SALES
30 ALLEN SALESMAN SALES
30 TURNER SALESMAN SALES
30 JAMES CLERK SALES
30 WARD SALESMAN SALES
40 OPERATIONS
15 rowsselected.
3.7. Комплексный пример использования соединений
Q3_24
Комплексный пример использования соединений
SQL>selecte.enamename,e.jobjob,m.enameboss,e.salsal,s.gradegrade,d.dname dept
from EMP e,EMP m,SALGRADE s,DEPT d
where e.mgr = m.EMPNO(+)
and e.sal between s.losal and s.hisal
and e.DEPTNO = d.DEPTNO
order by s.grade desc,e.sal desc,d.DEPTNO,e.ename;
NAME JOB BOSS SAL GRADE DEPTNO
---------- --------- ---------- --------- --------- --------------
KING PRESIDENT 5000 5 ACCOUNTING
FORD ANALYST JONES 3000 4 RESEARCH
SCOTT ANALYST JONES 3000 4 RESEARCH
JONES MANAGER KING 2975 4 RESEARCH
BLAKE MANAGER KING 2850 4 SALES
CLARK MANAGER KING 2450 4 ACCOUNTING
ALLEN SALESMAN BLAKE 1600 3 SALES
TURNER SALESMAN BLAKE 1500 3 SALES
MILLER CLERK CLARK 1300 2 ACCOUNTING
MARTIN SALESMAN BLAKE 1250 2 SALES
WARD SALESMAN BLAKE 1250 2 SALES
ADAMS CLERK SCOTT 1100 1 RESEARCH
JAMES CLERK BLAKE 950 1 SALES
SMITH CLERK FORD 800 1 RESEARCH
14 rowsselected.
3.8. Иерархические запросы
Q3_25
Древовидная структура таблицы EMP, отражающая подчиненность сотрудников фирмы
SQL> select level,DEPTNO,EMPNO,ename,job,sal
from EMP
connect by prior EMPNO=mgr
start with mgr is null;
LEVEL DEPTNO EMPNO ENAME JOB SAL
--------- ---------- --------- ---------- --------- ---------
1 10 7839 KING PRESIDENT 5000
2 30 7698 BLAKE MANAGER 2850
3 30 7654 MARTIN SALESMAN 1250
3 30 7499 ALLEN SALESMAN 1600
3 30 7844 TURNER SALESMAN 1500
3 30 7900 JAMES CLERK 950
3 30 7521 WARD SALESMAN 1250
2 10 7782 CLARK MANAGER 2450
3 10 7934 MILLER CLERK 1300
2 20 7566 JONES MANAGER 2975
3 20 7902 FORD ANALYST 3000
4 20 7369 SMITH CLERK 800
3 20 7788 SCOTT ANALYST 3000
4 20 7876 ADAMS CLERK 1100
14 rows selected.
где:
CONNECT BY (соединить по) - задает зависимость между родительскими и дочерними вершинами (строками) иерархии. Если в заданной иерархии обнаруживается петля, то Oracle возвращает сообщение об ошибке.
PRIOR (предшествующий) - определяет направление обхода дерева. В примере задано направление от родителя к потомку. Направлению от потомка к родителю соответствовало бы условие empno = PRIOR mgr или PRIOR mgr = empno.
START WITH (начать с) - если задано, то позволяет указать, с какой вершины (строки) или вершин (строк) начать построение дерева. То есть задает корневую (вые) вершины (строки).
Используя информацию, заданную в этих предложениях, Oracleформируетиерархию следующим образом:
1. Выбирает корневую строку (строки) иерархии в соответствии с условиями заданными во фразе START WITH. Если эта фраза опущена, то Oracle? будет использовать все строки таблицы в качестве корневых (попробуйте). Условие в START WITH может содержать подзапрос.
2. Для каждой корневой строки выбираются дочерние строки, каждая из которых должна удовлетворять условию фразы CONNECT BY по отношению к одной из корневых строк.
3. Oracle? выбирает следующие одно за другим поколения дочерних строк. Сначала выбираются потомки строк, полученных на этапе 2, затем потомки этих потомков и так далее. Oracle? всегда выбирает дочерние строки на основании условия CONNECT BY по отношению к текущей родительской строке.
4. Если запрос содержит фразу WHERE, то Oracle? исключает из иерархии все строки, которые не удовлетворяют условию, заданному в этой фразе. Поскольку проверка условия выполняется индивидуально для каждой строки, это не приводит к исключению строк, подчиненных не удовлетворяющим данному условию.
5. Oracle? возвращает результат запроса, в котором выбранные строки расположены в иерархическом порядке, то есть потомки выводятся после своих родителей.
Q3_26
Используя функцию LPADи псевдостолбецLEVELможно украсить
выводимый результат:
SQL> select lpad(' ',(level-1)*3)||level||' '||ename as tree,
EMPNO,job,mgr from EMP
connect by prior EMPNO=mgr
start with ename='KING';
TREE EMPNO JOB MGR
---------------------------------------- --------- --------- ---------
1 KING 7839 PRESIDENT
2 BLAKE 7698 MANAGER 7839
3 MARTIN 7654 SALESMAN 7698
3 ALLEN 7499 SALESMAN 7698
3 TURNER 7844 SALESMAN 7698
3 JAMES 7900 CLERK 7698
3 WARD 7521 SALESMAN 7698
2 CLARK 7782 MANAGER 7839
3 MILLER 7934 CLERK 7782
2 JONES 7566 MANAGER 7839
3 FORD 7902 ANALYST 7566
4 SMITH 7369 CLERK 7902
3 SCOTT 7788 ANALYST 7566
4 ADAMS 7876 CLERK 7788
14 rows selected.
Q3_27
JONES и его подчиненные
SQL> select lpad(' ',(level-1)*3)||level||' '||ename as tree,
EMPNO,job,mgr from EMP
connect by prior EMPNO=mgr
start with ename='JONES';
TREE EMPNO JOB MGR
---------------------------------------- --------- --------- ---------
1 JONES 7566 MANAGER 7839
2 FORD 7902 ANALYST 7566
3 SMITH 7369 CLERK 7902
2 SCOTT 7788 ANALYST 7566
3 ADAMS 7876 CLERK 7788
Q3_28
SMITH и его начальники
SQL> select lpad(' ',(level-1)*3)||level||' '||ename as tree,
EMPNO,job,mgr from EMP
connect by EMPNO = prior mgr
start with ename='SMITH';
TREE EMPNO JOB MGR
---------------------------------------- --------- --------- ---------
1 SMITH 7369 CLERK 7902
2 FORD 7902 ANALYST 7566
3 JONES 7566 MANAGER 7839
4 KING7839PRESIDENT
Q3_29
Исключение вершины SCOTTи ветвиBLAKE
SQL> select lpad(' ',(level-1)*3)||level||' '||ename as tree,
EMPNO,job,mgr
from EMP
where ename <> 'SCOTT'
connect by prior EMPNO = mgr and ename <> 'BLAKE'
start with sal=5000;
TREE EMPNO JOB MGR
---------------------------------------- --------- --------- ---------
1 KING 7839 PRESIDENT
2 CLARK 7782 MANAGER 7839
3 MILLER 7934 CLERK 7782
2 JONES 7566 MANAGER 7839
3 FORD 7902 ANALYST 7566
4 SMITH 7369 CLERK 7902
4 ADAMS 7876 CLERK 7788
7 rows selected.
Q3_30
Упорядочивание с помощью ORDER BY
SQL> select lpad(' ',(level-1)*3)||level||' '||ename as tree,
EMPNO,job,mgr
from EMP
connect by prior EMPNO = mgr
start with ename='JONES'
order by tree;
TREE EMPNO JOB MGR
---------------------------------------- --------- --------- ---------
3 ADAMS 7876 CLERK 7788
3 SMITH 7369 CLERK 7902
2 FORD 7902 ANALYST 7566
2 SCOTT 7788 ANALYST 7566
1 JONES7566MANAGER7839