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

SELECT d. department_name "Name", d. location_id "Location", COUNT (employee_id) "Number of People", ROUND (AVG (salary), 0) "Salary"

FROM departments d

JOIN employees e

USING (department_id)

GROUP BY d. department_name, d. location_id;

Name Location Number of People Salary

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

Administration 1700 1 4400

Marketing 1800 2 9500

Sales 2500 34 8956

Purchasing 1700 6 4150

Finance 1700 6 8600

IT 1400 5 5760

Executive 1700 3 19333

Name	Location	Number of People	Salary
Accounting	1700	2	10150
Administration	1700	1	4400
Executive	1700	3	19333.33
IT	1400	3	6400
Marketing	1800	2	9500
Sales	2500	3	10033.33
Shipping	1500	5	3500

2.  Напишите запрос для вывода фамилии, номера отдела и названия отдела для всех служащих.

SELECT e. last_name, department_id, d. department_name

FROM departments d

RIGHT OUTER JOIN employees e

USING (department_id);

107 rows selected

LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME

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

Whalen 10 Administration

Fay 20 Marketing

Hartstein 20 Marketing

Colmenares 30 Purchasing

Himuro 30 Purchasing

Tobias 30 Purchasing

LAST_NAME	DEPARTMENT_ID	DEPARTMENT_NAME
Whalen	10	Administration
Hartstein	20	Marketing
Fay	20	Marketing
Mourgos	50	Shipping
Rajs	50	Shipping
Davies	50	Shipping
Matos	50	Shipping
Vargas	50	Shipping

……………..

19 rows selected

3.  Выведите список всех должностей в отделе 80 (должности в списке не должны повторяться) и местоположение отдела. (в примере правильное выполнение запроса – 2 должности должно быть!)

SELECT e. job_id, d. location_id

FROM departments d

JOIN employees e

USING (department_id)

WHERE department_id = 80

GROUP BY e. job_id, d. location_id;

JOB_ID LOCATION_ID

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

SA_REP 2500

SA_MAN 2500

JOB_ID	LOCATION_ID
SA_MAN	2500
SA_REP	2500

4.  Выведите фамилии всех служащих, содержащих букву «а» (в строчном регистре), с названиями отделов. Сохраните свою команду SQL в файле lab4_4.sql.

SELECT e. last_name, d. department_name

FROM departments d

JOIN employees e

USING (department_id)

WHERE e. last_name LIKE '%a%';

LAST_NAME DEPARTMENT_NAME

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

Whalen Administration

Fay Marketing

Hartstein Marketing

Baida Purchasing

Raphaely Purchasing

Tobias Purchasing

Colmenares Purchasing

Mavris Human Resources

Davies Shipping

Cabrio Shipping

LAST_NAME	DEPARTMENT_NAME
Whalen	Administration
Hartstein	Marketing
Fay	Marketing
Rajs	Shipping
Davies	Shipping
Matos	Shipping
Vargas	Shipping
Taylor	Sales
Kochhar	Executive
De Haan	Executive

10  rows selected.

5.  Напишите запрос для вывода фамилии, должности, номера отдела и названия отдела всех служащих, работающих в городе Toronto.

SELECT e. last_name, e. job_id, d. department_id, d. department_name

FROM employees e

JOIN departments d

ON e. department_id = d. department_id

JOIN locations l

ON d. location_id = l. location_id

WHERE l. city IN 'Toronto';

LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME

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

Hartstein MK_MAN 20 Marketing

Fay MK_REP 20 Marketing

LAST_NAME	JOB_ID	DEPARTMENT_ID	DEPARTMENT_NAME
Hartstein	MK_MAN	20	Marketing
Fay	MK_REP	20	Marketing

6.  Выведите фамилии и номера всех служащих вместе с фамилиями и номерами их менеджеров. Назовите столбцы EMPLOYEE, Emp#, Manager и Mgr#. Сохраните свою команду SQL в файле lab4_6.sql.

SELECT e. last_name "Employee", e. employee_id "Emp#", m. last_name "Manager", m. manager_id "Mgr#"

FROM employees e

JOIN employees m

ON (e. manager_id = m. employee_id);

Employee Emp# Manager Mgr#

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

Fay 202 Hartstein 100

Gietz 206 Higgins 101

Zlotkey 149 King

Cambrault 148 King

Errazuriz 147 King

Employee	Emp#	Manager	Mgr#
Kochhar	101	King	100
De Haan	102	King	100
Mourgos	124	King	100
Zlotkey	149	King	100

…………….

7.  Измените файл lab4_6.sql так, чтобы получить фамилии всех служащих, включая Кинга, который не имеют менеджера. Упорядочьте результат по возрастанию номера служащего. Сохраните изменённый запрос в текстовом файле lab4_7.sql. Выполните запрос.

SELECT e. last_name "Employee", e. employee_id "Emp#", m. last_name "Manager", m. manager_id "Mgr#"

FROM employees e

LEFT OUTER JOIN employees m

ON (e. manager_id = m. employee_id)

ORDER BY "Emp#";

Employee Emp# Manager Mgr#

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

King 100

Kochhar 101 King

De Haan 102 King

Hunold 103 De Haan 100

Ernst 104 Hunold 102

Austin 105 Hunold 102

Pataballa 106 Hunold 102

Employee	Emp#	Manager	Mgr#
King	100
Kochhar	101	King	100
De Haan	102	King	100
Hunolds	103	De Haan	102
Ernst	104	Hunold	103
Lorentz	107	Hunold	103

…………………………….

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

SELECT e. department_id AS department, e. last_name AS employee, col. last_name AS colleague

FROM employees col JOIN employees e

ON (e. department_id = col. department_id)

ORDER BY e. department_id, e. last_name, col. last_name;

DEPARTMENT EMPLOYEE COLLEAGUE

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

10 Whalen Whalen

20 Fay Fay

20 Fay Hartstein

20 Hartstein Fay

20 Hartstein Hartstein

30 Baida Baida

30 Baida Colmenares

30 Baida Himuro

30 Baida Khoo

30 Baida Raphaely

30 Baida Tobias

30 Colmenares Baida

DEPARTMENT	EMPLOYEE	COLLEAGUE
20	Fay	Hartstein
20	Hartstein	Fay
50	Davies	Matos
50	Davies	Mourgos
50	Davies	Rajs
50	Davies	Vargas

…………………

9.  Покажите структуру таблицы JOB_GRADES. Создайте запрос для вывода фамилии, должности, названия отдела, оклада и категории (GRADE_LEVEL) всех служащих.

DESCRIBE job_grades

Name Null Type

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

GRADE_LEVEL VARCHAR2(3)

LOWEST_SAL NUMBER

HIGHEST_SAL NUMBER

Name	Null?	Type
GRADE_LEVEL		VARCHAR2(3)
LOWEST_SAL		NUMBER
HIGHEST_SAL		NUMBER

SELECT e. last_name, e. job_id, d. department_name, e. salary, j. grade_level AS gra