Приведем синтаксис команды Select языкаSQL (Structure Query Language)

Раздел 1 Утилита sql*Plus, простые запросы выборки столбцов и строк

Q1_1

Содержимое таблицы EMP

SQL> select * from EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

1.1.Переменные связи в среде SQL*Plus

Q1_2

Создание, обращение к и вывод переменной связи

SQL> variable ret_val number

SQL> begin

:ret_val:=1957;

end;

PL/SQL procedure successfully completed.

SQL> print ret_val

RET_VAL

---------

1957

1.2. Переменные подстановки в среде SQL*Plus

Q1_3

Использование переменной подстановки

SQL> select * from &table_name where DEPTNO=&n_dept;

Enter value for table_name: EMP

Enter value for n_dept: 10

old 1: select * from &table_name where DEPTNO=&n_dept

new 1: select * from EMP where DEPTNO=10

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7934 MILLER CLERK 7782 23-JAN-82 1300 10

Q1_4

[e-mail]> select EMPno,ename,sal from EMP

where job='&1';

Enter value for 1: CLERK

old 2: where job='&1'

new 2: where job='CLERK'

EMPNO ENAME SAL

---------- ---------- ----------

7900 JAMES 950

7369 SMITH 800

7876 ADAMS 1100

7934 MILLER 1300

[e-mail]> save ex1_4

Created file ex1_4.sql

[e-mail]> @ex1_4 CLERK

old 2: where job='&1'

new 2: where job='CLERK'

EMPNO ENAME SAL

---------- ---------- ----------

7900 JAMES 950

7369 SMITH 800

7876 ADAMS 1100

7934 MILLER 1300

Q1_4a

[e-mail]> Insert into dept (deptno, Dname, loc)

Values(&department_number,'&department_nmae','&locacion');

Enter value for department_number: 88

Enter value for department_nmae: home

Enter value for locacion: Kashirka

old 2: Values(&department_number,'&department_nmae','&locacion')

new 2: Values(88,'home','Kashirka')

Q1_4b

[e-mail]> select * from &&table_name;

Enter value for table_name: dept –здесь введено значение для table_name

old 1: select * from &&table_name

new 1: select * from dept

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 ttt fff

70 sss www

88 home Kashirka

7 rows selected.

[e-mail]>select*from&&table_name; --здесь уже отсутствует

--приглашение для ввода значения table_name.

--Отсутствие такого приглашения обеспечивает двойная &

old 1: select * from &&table_name

new 1: select * from dept

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 ttt fff

70 sss www

88 home Kashirka

7 rows selected.

--как изменить значение для «table_name»

Q1_4c

[e-mail]> def table_name –def – сокращение от definition

DEFINE TABLE_NAME = "dept" (CHAR)

[e-mail]> DEFINE TABLE_NAME = "SALGRADE" (CHAR)

[e-mail]> select * from &&table_name;

old 1: select * from &&table_name

new 1: select * from SALGRADE

GRADE LOSAL HISAL

---------- ---------- ----------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

1.3. Выборка столбцов

Q1_5

Выборка всех столбцов из таблицы

SQL> select EMP.* from EMP;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

Q1_6

Использование арифметических выражений

Зарплата за 12 месяцев

SQL> select DEPTNO,sal*12,comm from EMP;

DEPTNO SAL*12 COMM

---------- --------- ---------

10 60000

30 34200

10 29400

20 35700

30 15000 1400

30 19200 300

30 18000 0

30 11400

30 15000 500

20 36000

20 9600

20 36000

20 13200

10 15600

14 rows selected.

Q1_7

Интерпретация NULL-значений столбца (NULL- значение в таблицахOracleпонимается, как отсутствие значения)COMM, как числа "0"

Вывод годового дохода

SQL> select ename name, sal*12+NVL(comm,0) year_sal from EMP;

NAME YEAR_SAL

---------- ---------

KING 60000

BLAKE 34200

CLARK 29400

JONES 35700

MARTIN 16400

ALLEN 19500

TURNER 18000

JAMES 11400

WARD 15500

FORD 36000

SMITH 9600

SCOTT 36000

ADAMS 13200

MILLER 15600

14 rows selected.

Q1_8

Сцепление столбцов DEPTNO и ENAME

SQL> select DEPTNO||ename,mgr from EMP;

DEPTNO||ENAME MGR

-------------------------------------------------- ---------

10KING

30BLAKE 7839

10CLARK 7839

20JONES 7839

30MARTIN 7698

30ALLEN 7698

30TURNER 7698

30JAMES 7698

30WARD 7698

20FORD 7566

20SMITH 7902

20SCOTT 7566

20ADAMS 7788

10MILLER 7782

14 rows selected.

Q1_9

Использование литералов (набора символов) для формирования столбца

SQL> select ename,'Work in dept',DEPTNO from EMP;

ENAME 'WORKINDEPT' DEPTNO

---------- ------------ ----------

KING Work in dept 10

BLAKE Work in dept 30

CLARK Work in dept 10

JONES Work in dept 20

MARTIN Work in dept 30

ALLEN Work in dept 30

TURNER Work in dept 30

JAMES Work in dept 30

WARD Work in dept 30

FORD Work in dept 20

SMITH Work in dept 20

SCOTT Work in dept 20

ADAMS Work in dept 20

MILLER Work in dept 10

14 rowsselected.

Q1_10

Исключение повторяющихся строк с использованием классификатора DISTINCT

SQL> select distinct DEPTNO,job from EMP;

DEPTNO JOB

---------- ---------

10 CLERK

10 MANAGER

10 PRESIDENT

20 ANALYST

20 CLERK

20 MANAGER

30 CLERK

30 MANAGER

30 SALESMAN

9 rows selected.

Q1_11

Использование псевдонимов столбцов

SQL> select DEPTNO,sal OKLAD,comm as KOMMIS from EMP;

DEPTNO OKLAD KOMMIS

---------- --------- ---------

10 5000

30 2850

10 2450

20 2975

30 1250 1400

30 1600 300

30 1500 0

30 950

30 1250 500

20 3000

20 800

20 3000

20 1100

10 1300

14 rows selected.

Q1_12

Упорядочение строк результата запроса с использованием ORDERBY

SQL> select ename NAME,sal*12+nvl(comm,0) YEAR_SAL from EMP order by ename;

NAME YEAR_SAL

---------- ---------

ADAMS 13200

ALLEN 19500

BLAKE 34200

CLARK 29400

FORD 36000

JAMES 11400

JONES 35700

KING 60000

MARTIN 16400

MILLER 15600

SCOTT 36000

SMITH 9600

TURNER 18000

WARD 15500

14 rows selected.

Q1_13

Сортировка в порядке убывания

SQL> select ename NAME,sal*12+nvl(comm,0) YEAR_SAL from EMP order by ename desc;

NAME YEAR_SAL

---------- ---------

WARD 15500

TURNER 18000

SMITH 9600

SCOTT 36000

MILLER 15600

MARTIN 16400

KING 60000

JONES 35700

JAMES 11400

FORD 36000

CLARK 29400

BLAKE 34200

ALLEN 19500

ADAMS 13200

14 rows selected.

Q1_14

Сортировка по нескольким столбцам

SQL> select ename,sal*12+nvl(comm,0) as total

from EMP

order by total desc,ename;

ENAME TOTAL

---------- ---------

KING 60000

FORD 36000

SCOTT 36000

JONES 35700

BLAKE 34200

CLARK 29400

ALLEN 19500

TURNER 18000

MARTIN 16400

MILLER 15600

WARD 15500

ADAMS 13200

JAMES 11400

SMITH9600

14 rowsselected.

1.4. Выборка строк таблицы

Q1_15

Условия выборки во фразе WHERE

Выборка всех строк , где DEPTNOне равен 30

SQL> select * from EMP

where DEPTNO != 30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- --------- --------- --------- ----------

7839 KING PRESIDENT 17-NOV-81 5000 10

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

8 rows selected.

Q1_16

Выбор сотрудников клерков

SQL> select ename NAME,EMPNO NUM,job

from EMP where job='CLERK';

NAME NUM JOB

---------- --------- ---------

JAMES 7900 CLERK

SMITH 7369 CLERK

ADAMS 7876 CLERK

MILLER 7934 CLERK

Q1_17

Выбор сотрудников у которых комиссионные больше оклада

SQL> select ename name,EMPNO num,job from EMP

where comm > sal;

NAME NUM JOB

---------- --------- ---------

MARTIN7654SALESMAN

Q1_18

Выбор сотрудников у которых оклад меньше или равен 2000 и больше или равен 1000

SQL> select ename name,sal from EMP

where sal between 1000 and 2000;

NAME SAL

---------- ---------

MARTIN 1250

ALLEN 1600

TURNER 1500

WARD 1250

ADAMS 1100

MILLER1300

6 rowsselected.

Q1_19

Выбор сотрудников , номер руководителя которых 7902 или 7566

SQL> select ename name,sal,mgr from EMP

where mgr in (7902,7566);

NAME SAL MGR

---------- --------- ---------

FORD3000 7566

SMITH800 7902

SCOTT3000 7566

Q1_20

Выбор сотрудников у которых имя начинается с S

SQL> select ename name,sal,job from EMP

where ename like 'S%';

NAME SAL JOB

---------- --------- ---------

SMITH 800 CLERK

SCOTT 3000 ANALYST

Q1_21

Выбор сотрудников у которых нет руководителя (поле MGRне определено)

SQL> select ename name, sal, mgr from EMP

where mgr is null;

NAME SAL MGR

---------- --------- ---------

KING5000

Q1_22

Выбор сотрудников у которых оклад не меньше 3000

SQL> select ename name,sal from EMP

where not sal<3000;

NAME SAL

---------- ---------

KING5000

FORD3000

SCOTT3000

Q1_23

Выбор сотрудников у которых определено поле COMM(неNULL)

SQL> select ename name,sal,comm from EMP

where comm is not null;

NAME SAL COMM

---------- --------- ---------

MARTIN 1250 1400

ALLEN 1600 300

TURNER1500 0

WARD1250 500

Q1_24

Использование сложного критерия поиска с объединением AND

Выбор клерков с окладом между 1000 и 2000

SQL> select ename name,sal from EMP

where sal between 1000 and 2000 and job='CLERK';

NAME SAL

---------- ---------

ADAMS 1100

MILLER 1300

Q1_24aИспользованиеselect-а вselect-е или вложенный подзапрос

в select-е.

Вывести номер, имя и название отдела служащего.

scott@8i> select empno, ename, (select dname from dept

2 where dept.deptno=emp.deptno) ddname from emp;

EMPNO ENAME DDNAME

---------- ---------- --------------

7369 SMITH RESEARCH

7499 ALLEN SALES

7521 WARD SALES

7566 JONES RESEARCH

7654 MARTIN SALES

7698 BLAKE SALES

7782 CLARK ACCOUNTING

7788 SCOTT RESEARCH

7839 KING ACCOUNTING

7844 TURNER SALES

7876 ADAMS RESEARCH

7900 JAMES SALES

7902 FORD RESEARCH

7934 MILLER ACCOUNTING

14 rows selected.

Q1_24bФормирование отчета, в котором содержимое столбца разворачивается в ряд столбцов отчета. Отчет должен содержать по строкам список сорудников (номера и имена служащих), по столбцам наименования отделов, а на пересечении знак ‘+’, если служащий работает в соответствующем отделе.

select empno, ename,

(select decode(deptno,10,' + ',NULL) from dept

where emp.deptno=dept.deptno) accounting,

(select decode(deptno,20,' + ',NULL) from dept

where emp.deptno=dept.deptno) research,

(select decode(deptno,30,' + ',NULL) from dept

where emp.deptno=dept.deptno) SALES,

(select decode(deptno,40,' + ',NULL) from dept

where emp.deptno=dept.deptno) OPERATIONS

from emp;

scott@8i>

EMPNO ENAME ACCOUNTING RESEARCH SALES OPERATIONS

---------- ---------- ----------- ----------- ----------- -----------

7369 SMITH +

7499 ALLEN +

7521 WARD +

7566 JONES +

7654 MARTIN +

7698 BLAKE +

7782 CLARK +

7788 SCOTT +

7839 KING +

7844 TURNER +

7876 ADAMS +

7900 JAMES +

7902 FORD +

7934 MILLER +

14 rows selected.

Q1_24cА теперь усложним вывод отчета. На пересечении строк (сотрудников) и столбцов(названий отдела) надо выставить зарплату сотрудника в этом отделе.

Сначала создадим представление:

create or replace view my_rep1 as

select empno, ename,sal salary,

(select decode(deptno,10,'1',NULL) from dept

where emp.deptno=dept.deptno) accounting,

(select decode(deptno,20,'1',NULL) from dept

where emp.deptno=dept.deptno) research,

(select decode(deptno,30,'1',NULL) from dept

where emp.deptno=dept.deptno) SALES,

(select decode(deptno,40,'1',NULL) from dept

where emp.deptno=dept.deptno) OPERATIONS

from emp;

scott@8i> select * from my_rep1;

EMPNO ENAME SALARY A R S O

---------- ---------- ---------- - - - -

7369 SMITH 800 1

7499 ALLEN 1600 1

7521 WARD 1250 1

7566 JONES 2975 1

7654 MARTIN 1250 1

7698 BLAKE 2850 1

7782 CLARK 2450 1

7788 SCOTT 3000 1

7839 KING 5000 1

7844 TURNER 1500 1

7876 ADAMS 1100 1

7900 JAMES 950 1

7902 FORD 3000 1

7934 MILLER 1300 1

14 rows selected.

Затем на основе представления сформируем отчет:

select EMPNO,ENAME,

SALARY* nvl(to_number(accounting),NULL) accounting,

SALARY* nvl(to_number(research),NULL) research,

SALARY* nvl(to_number(sales),NULL) sales,

SALARY* nvl(to_number(operations),NULL) operations

from my_rep1;

EMPNO ENAME ACCOUNTING RESEARCH SALES OPERATIONS

---------- ---------- ---------- ---------- ---------- ----------

7369 SMITH 800

7499 ALLEN 1600

7521 WARD 1250

7566 JONES 2975

7654 MARTIN 1250

7698 BLAKE 2850

7782 CLARK 2450

7788 SCOTT 3000

7839 KING 5000

7844 TURNER 1500

7876 ADAMS 1100

7900 JAMES 950

7902 FORD 3000

7934 MILLER 1300

14 rows selected.

Здесь использовано то свойство типов данных, что сумма числа и NULLзначения даетNULLзначение.