Практические занятия по курсу введение в ORACLE 10g: SQL (Часть 1) (стр. 8 )

SELECT e. last_name, d. department_name, d. location_id, l. city

FROM (employees e

LEFT JOIN departments d

ON e. department_id = d. department_id)

LEFT JOIN locations l

ON d. location_id = l. location_id

WHERE e. salary IS NOT NULL;

LAST_NAME DEPARTMENT_NAME LOCATION_ID CITY

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

Hunold IT 1400 Southlake

Ernst IT 1400 Southlake

Austin IT 1400 Southlake

Pataballa IT 1400 Southlake

Lorentz IT 1400 Southlake

OConnell Shipping 1500 South San Francisco

Grant Shipping 1500 South San Francisco

LAST_NAME	DEPARTMENT_NAME	LOCATION_ID	CITY
Zlotkey	Sales	2500	Oxford
Abel	Sales	2500	Oxford
Taylor	Sales	2500	Oxford

Практическое занятие 6

·  Создание подзапросов для выборки данных по неизвестным критериям

·  Использование подзапросов для выявления значений, существующих в одном наборе данных и отсутствующих в другом

1. 

Создайте запрос для вывода фамилии и даты найма каждого служащего, работающего в одном отделе с Zlotkey. Исключите Zlotkey из выходных данных.

SELECT last_name, TO_CHAR (hire_date, 'fmDD-MON-YY', 'NLS_Date_Language = american') hire_date

FROM employees

WHERE department_id =

(SELECT department_id

FROM employees

WHERE last_name = 'Zlotkey')

AND last_name <> 'Zlotkey';

LAST_NAME HIRE_DATE

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

Russell 1-OCT-4

Partners 5-JAN-5

Errazuriz 10-MAR-5

Cambrault 15-OCT-7

Tucker 30-JAN-5

Bernstein 24-MAR-5

Hall 20-AUG-5

Olsen 30-MAR-6

Cambrault 9-DEC-6

ИЛИ

SELECT last_name, TO_CHAR (hire_date, 'DD-MON-YY', 'NLS_Date_Language = american') hire_date

FROM employees

WHERE department_id =

(SELECT department_id

FROM employees

WHERE last_name = 'Zlotkey')

AND last_name <> 'Zlotkey';

LAST_NAME HIRE_DATE

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

Russell 01-OCT-04

Partners 05-JAN-05

Errazuriz 10-MAR-05

Cambrault 15-OCT-07

Tucker 30-JAN-05

2. 

Создайте запрос для вывода номеров и фамилий всех служащих, оклад которых выше среднего. Отсортируйте данные в порядке увеличения окладов.

SELECT employee_id, last_name, salary

FROM employees

WHERE salary >

(SELECT AVG(salary)

FROM employees)

ORDER BY salary ASC;

EMPLOYEE_ID LAST_NAME SALARY

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

203 Mavris 6500

123 Vollman 6500

165 Lee 6800

113 Popp 6900

155 Tuvault 7000

178 Grant 7000

161 Sewall 7000

164 Marvins 7200

172 Bates 7300

171 Smith 7400

154 Cambrault 7500

3. 

Создайте запрос для вывода номеров и фамилий всех служащих, работающих в одном отделе с любым служащим, фамилия которого содержит букву «u». Сохраните свой запрос в тестовом файле lab6_3.sql. Выполните запрос.

SELECT employee_id, last_name

FROM employees

WHERE department_id IN

(SELECT department_id

FROM employees

WHERE last_name LIKE '%u%');

EMPLOYEE_ID LAST_NAME

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

107 Lorentz

106 Pataballa

105 Austin

104 Ernst

103 Hunold

197 Feeney

196 Walsh

195 Jones

194 McCain

4. 

Создайте запрос для вывода фамилии, номера отдела и должности каждого служащего, идентификатор местоположения отдела которого равен 1700.

SELECT last_name, department_id, job_id

FROM employees

WHERE department_id IN

(SELECT department_id

FROM departments

WHERE location_id = 1700);

LAST_NAME DEPARTMENT_ID JOB_ID

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

Whalen 10 AD_ASST

Raphaely 30 PU_MAN

Colmenares 30 PU_CLERK

Khoo 30 PU_CLERK

Baida 30 PU_CLERK

Tobias 30 PU_CLERK

Himuro 30 PU_CLERK

5.  Получите список фамилий и окладов всех служащих, подчинённых Кингу.

SELECT last_name, salary

FROM employees

WHERE manager_id IN

(SELECT employee_id

FROM employees

WHERE last_name = 'King');

LAST_NAME SALARY

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

Hartstein 13000

Kochhar 17000

De Haan 17000

Raphaely 11000

Weiss 8000

6. 

Получите номер отдела, фамилию и должность для каждого служащего, работающего в администрации (department_name = ‘Executive’).

SELECT department_id, last_name, job_id

FROM employees

WHERE department_id IN

(SELECT department_id

FROM departments

WHERE department_name = 'Executive');

DEPARTMENT_ID LAST_NAME JOB_ID

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

90 King AD_PRES

90 Kochhar AD_VP

90 De Haan AD_VP

7.  Измените lab6_3.sql для вывода номеров, фамилий и окладов всех служащих, которые работают в одном отделе с любым служащим с буквой «u» в фамилии и оклады которых превышают средний. Сохраните новый запрос в файле lab6_7.sql. Выполните его.

SELECT employee_id, last_name, salary

FROM employees

WHERE department_id IN

(SELECT department_id

FROM employees

WHERE last_name LIKE '%u%')

AND salary > (SELECT AVG(salary)

FROM employees);

Или

SELECT employee_id, last_name, salary

FROM employees

WHERE department_id IN

(SELECT department_id

FROM employees

WHERE last_name LIKE '%u%')

AND salary > ALL (SELECT AVG(salary)

FROM employees);

EMPLOYEE_ID LAST_NAME SALARY

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

103 Hunold 9000

123 Vollman 6500

122 Kaufling 7900

121 Fripp 8200

120 Weiss 8000

177 Livingston 8400

176 Taylor 8600

175 Hutton 8800

174 Abel 11000

8. 

Покажите номер отдела с наивысшей средней заработной платой и наименьший оклад работающего в нём сотрудника.

SELECT department_id, MIN(salary)

FROM employees

WHERE department_id IN

(SELECT department_id

FROM employees

WHERE salary =

(SELECT MAX(AVG(salary))

FROM employees

GROUP BY salary))

GROUP BY department_id;

DEPARTMENT_ID MIN(SALARY)

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

90 17000

9. Выведите номера, наименования и местоположения отделов, в которых не работают торговые представители (job_id = ‘SA_REP’).

SELECT department_id, department_name, manager_id, location_id

FROM departments

WHERE department_id <> ALL

(SELECT NVL(department_id, 0)

FROM employees

WHERE job_id = 'SA_REP');

.

(а если в отделе работает SA_REP и еще кто-то)

1.Вычисляем, какие отделы с job_id SA_REP, 2. Составляем список тех значений отделов, с которыми будем проверять равенство 3. Сравниваем номер любого департамента со всеми полученными в подзапросе значениями.

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

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

10 Administration 200 1700

20 Marketing 201 1800

30 Purchasing 114 1700

40 Human Resources 203 2400

140 Control And Credit 1700

150 Shareholder Services 1700

160 Benefits 1700

170 Manufacturing 1700

180 Construction 1700

26 rows selected

Практическое занятие 7

·  Создание запросов, в которых используются операторы SET.

·  Альтернативные методы соединения.

1.  Используя оператор SET, выведите номера отделов (department_id), в которых нет служащих с идентификатором должности (job_id) ST_CLERK.

SELECT department_id

FROM departments

MINUS

SELECT department_id

FROM employees

WHERE job_id LIKE 'ST_CLERK';

DEPARTMENT_ID

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

10

20

30

40

60

70

2.  Используя оператор SET, выведите идентификаторы и наименования стран, в которых не располагаются отделы компании. (учесть отделы!)

SELECT country_id "CO", country_name

FROM countries

MINUS

SELECT l. country_id, con. country_name

FROM locations l JOIN countries con

ON l. country_id = con. country_id

JOIN departments d

ON d. location_id = l. location_id;

Чтобы не забыть: 1. Нахожу локайшнс, где есть отделы 2. Вычитаю их из общего списка

21 строчка должна выводиться!

CO COUNTRY_NAME

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

AR Argentina

AU Australia

BE Belgium

BR Brazil

CH Switzerland

21 rows selected

3.  Используя оператор SET, выведите список должностей отделов 10, 50 и 20 в таком же порядке отделов. Выведите столбцы job_id и department_id.

SELECT job_id, department_id

FROM (SELECT job_id, department_id

FROM employees

WHERE department_id IN (10, 20, 50)

INTERSECT

SELECT job_id, department_id

FROM employees)

ORDER BY decode (department_id, 10,1, 20,3, 50,2);

COLUMN a_dummy NOPRINT