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 


Измените 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

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

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. Альтернативные методы соединения.
Используя оператор 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


Используя оператор 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


Используя оператор 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

SELECT job_id, department_id, 1 a_dummy

FROM employees

WHERE department_id = 10

UNION

SELECT job_id, department_id, 3 a_dummy

FROM employees

WHERE department_id = 20

UNION

SELECT job_id, department_id, 2 a_dummy

FROM employees

WHERE department_id = 50

ORDER BY 3;

(будет доп вопрос про SET и подзапрос!)

JOB_ID  DEPARTMENT_ID 

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

AD_ASST  10 

ST_CLERK  50 

SH_CLERK  50 

SH_CLERK  50 

SH_CLERK  50 

ST_MAN  50 

ST_MAN  50 

ST_MAN  50 

ST_MAN  50 

ST_MAN  50 

ST_CLERK  50 

48 rows selected


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

SELECT employee_id, job_id

FROM employees

INTERSECT

SELECT employee_id, job_id

FROM job_history;

EMPLOYEE_ID JOB_ID 

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

  176 SA_REP 

  200 AD_ASST


Напишите составной запрос, который выводит следующее:
    Номера и отделы всех сотрудников из таблицы EMPLOYEES, независимо от того, относятся ли они к какому-то отделу или нет; Номера и наименования всех отделов из таблицы DEPARTMENTS, независимо от того, есть ли в них сотрудники или нет.

SELECT employee_id, department_id, TO_CHAR (NULL) department_name

FROM employees

UNION

SELECT TO_NUMBER (NULL), department_id, department_name

FROM departments;

EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME 

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

  100  90 

  101  90 

  102  90 

  103  60 

  104  60 

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

Вставка строк в таблицы Обновление строк в таблице Удаление строк из таблицы Управление транзакциями

Вставка данных в таблицу.

Выполните скрипт lab8_1.sql для создания таблицы MY_EMPLOYEE, которая будет использоваться для упражнений. Выведите структуру таблицы MY_EMPLOYEE для выяснения имён столбцов.

DESCRIBE my_employee

Name  Null  Type 

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

ID  NOT NULL NUMBER(4) 

LAST_NAME  VARCHAR2(25)

FIRST_NAME  VARCHAR2(25)

USERID  VARCHAR2(8) 

SALARY  NUMBER(9,2)

Вставьте в таблицу MY_EMPLOYEE первую строку из нижеприведённых образцов. Не указывайте столбцы в предложении INSERT.

INSERT INTO my_employee

VALUES (1, 'Patel', 'Ralph', 'rpatel', 895);

1 rows inserted.

ID

LAST_NAME

FIRST_NAME

USERID

SALARY

1

Patel

Ralph

rpatel

895

2

Dancs

Betty

bdancs

860

3

Biri

Ben

bbiri

1100

4

Newman

Chad

cnewman

750

5

Ropeburn

Andrey

Aropebur

1550
Вставьте в таблицу MY_EMPLOYEE вторую строку из вышеуказанных образцов. На этот раз укажите столбцы явно в предложении INSERT.

INSERT INTO my_employee(id, last_name, first_name, userid, salary)

VALUES (2, 'Dancs', 'Betty', 'bdancs', 860);

1 rows inserted.


Убедитесь в том, что данные вставлены в таблицу.

SELECT *

FROM my_employee;

  ID LAST_NAME  FIRST_NAME  USERID  SALARY

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

  1 Patel  Ralph  rpatel  895

  2 Dancs  Betty  bdancs  860


Создайте скрипт-файл loademp. sql для интерактивной загрузки строк в таблицу MY_EMPLOYEE. Выдайте приглашение пользователю ввести номер, фамилию имя и оклад служащего. Соедините первую букву имени с семью первыми буквами фамилии для получения идентификатора пользователя данного служащего.

SET VERIFY OFF

INSERT INTO my_employee(id, last_name, first_name, userid, salary)

VALUES ('&write_id', INITCAP ('&&write_last_name'), INITCAP ('&&write_first_name'),

LOWER(SUBSTR ('&write_first_name', 1, 1) || SUBSTR ('&write_last_name', 1, 7)),

Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 7 8 9 10 11