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

FROM departments d

JOIN employees e

ON d. department_id = e. department_id

JOIN job_grades j

ON e. salary

BETWEEN j. lowest_sal AND j. highest_sal;

LAST_NAME JOB_ID DEPARTMENT_NAME SALARY GRA

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

King AD_PRES Executive 24000 E

Kochhar AD_VP Executive 17000 E

De Haan AD_VP Executive 17000 E

Russell SA_MAN Sales 14000 D

Partners SA_MAN Sales 13500 D

Hartstein MK_MAN Marketing 13000 D

LAST_NAME	JOB_ID	DEPARTMENT_NAME	SALARY	GRA
Matos	ST_CLERK	Shipping	2600	A
Vargas	ST_CLERK	Shipping	2500	A
Lorentz	IT_PROG	IT	4200	B
Mourgos	ST_MAN	Shipping	5800	B
Rajs	ST_CLERK	Shipping	3500	B
Davies	ST_CLERK	Shipping	3100	B
Whalen	AD_ASST	Administration	4400	B

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

SELECT e. last_name, TO_CHAR (e. hire_date, 'fmDD-MON-YYYY', 'NLS_Date_Language = american') AS hire_date

FROM employees e

CROSS JOIN employees s

WHERE s. last_name = 'Davies'

AND s. hire_date < e. hire_date;

Или

SELECT e. last_name, TO_CHAR (e. hire_date, 'fmDD-MON-YYYY', 'NLS_Date_Language = american') AS hire_date

FROM employees e

JOIN employees s

ON (s. last_name = 'Davies')

WHERE s. hire_date < e. hire_date;

LAST_NAME HIRE_DATE

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

OConnell 21-JUN-2007

Grant 13-JAN-2008

Fay 17-AUG-2005

Kochhar 21-SEP-2005

Hunold 3-JAN-2006

Ernst 21-MAY-2007

LAST_NAME	HIRE_DATE
Lorentz	07-FEB-99
Mourgos	16-NOV-99
Matos	15_MAR-98
Vargas	09-JUL-98
Zlotkey	29-JAN-00
Taylor	24-MAR-98
Grant	24-MAY-99
Fay	17-AUG-97

11.  По всем служащим, нанятым раньше своих менеджеров, выведите фамилии и даты найма самих служащих, а также фамилии и даты найма их менеджеров. Назовите столбцы Employee, Emp Hired, Manager и Manager Hired.

SELECT e. last_name "Employee", TO_CHAR (e. hire_date, 'DD-MON-YYYY', 'NLS_Date_Language = american') "Emp Hired",

m. last_name "Manager", TO_CHAR (m. hire_date, 'DD-MON-YYYY', 'NLS_Date_Language = american') "Mgr hired"

FROM employees e

CROSS JOIN employees m

WHERE e. manager_id = m. employee_id

AND m. hire_date > e. hire_date;

Или

SELECT e. last_name "Employee", TO_CHAR (e. hire_date, 'fmDD-MON-YYYY', 'NLS_Date_Language = american') "Emp Hired",

m. last_name "Manager", TO_CHAR (m. hire_date, 'fmDD-MON-YYYY', 'NLS_Date_Language = american') "Mgr hired"

FROM employees e

JOIN employees m

ON (e. manager_id = m. employee_id)

WHERE m. hire_date > e. hire_date;

Employee Emp Hired Manager Mgr hired

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

Kaufling 1-MAY-2003 King 17-JUN-2003

Raphaely 7-DEC-2002 King 17-JUN-2003

De Haan 13-JAN-2001 King 17-JUN-2003

Greenberg 17-AUG-2002 Kochhar 21-SEP-2005

Higgins 7-JUN-2002 Kochhar 21-SEP-2005

Baer 7-JUN-2002 Kochhar 21-SEP-2005

Mavris 7-JUN-2002 Kochhar 21-SEP-2005

Whalen 17-SEP-2003 Kochhar 21-SEP-2005

Austin 25-JUN-2005 Hunold 3-JAN-2006

Faviet 16-AUG-2002 Greenberg 17-AUG-2002

Bull 20-FEB-2005 Fripp 10-APR-2005

Sarchand 27-JAN-2004 Fripp 10-APR-2005

Marlow 16-FEB-2005 Fripp 10-APR-2005

Everett 3-MAR-2005 Vollman 10-OCT-2005

Bell 4-FEB-2004 Vollman 10-OCT-2005

Employee	Emp Hired	Manager	Mgr hired
Whalen	17-SEP-87	Kochhar	21-SEP-89
Hunold	03-JAN-90	De Haan	13-JAN-93
Rajs	17-OCT-95	Mourgos	16-NOV-99
Davies	29-JAN-97	Mourgos	16-NOV-99
Matos	15-MAR-98	Mourgos	16-NOV-99
Vargas	09-JUL-98	Mourgos	16-NOV-99
Abel	11-MAY-96	Zlotkey	29-JAN-00
Taylor	24-MAR-98	Zlotkey	29-JAN-00
Grant	24-MAY-99	Zlotkey	29-JAN-00

12.  Выведите номера, наименования и местоположение всех отделов, а также количество работающих в них сотрудников. Обеспечьте вывод отделов, в которых нет сотрудников.

SELECT d. department_id, d. department_name, d. location_id, COUNT (e. employee_id)

FROM departments d

LEFT JOIN employees e

ON (d. department_id = e. department_id)

GROUP BY d. department_id, d. department_name, d. location_id

ORDER BY 1;

DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID COUNT(E. EMPLOYEE_ID)

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

10 Administration 1700 1

20 Marketing 1800 2

30 Purchasing 1700 6

40 Human Resources 2400 1

50 Shipping 1500 45

60 IT 1400 5

70 Public Relations 2700 1

80 Sales 2500 34

90 Executive 1700 3

100 Finance 1700 6

110 Accounting 1700 2

120 Treasury 1700 0

130 Corporate Tax 1700 0

140 Control And Credit 1700 0

DEPARTMENT_ID	DEPARTMENT_NAME	LOCATION_ID	COUNT(E. EMPLOYEE_ID)
10	Administration	1700	1
20	Marketing	1800	2
50	Shipping	1500	5
60	IT	1400	3
80	Sales	2500	3
90	Executive	1700	3
110	Accounting	1700	2
190	Contracting	1700	0

13.  Какие должности имеются в отделах Administration и Executive, а также сколько сотрудников занимают эти должности? Выведите первыми наиболее часто встречаемые должности.

SELECT e. job_id, COUNT (e. job_id) AS frequency

FROM employees e

LEFT JOIN departments d

ON e. department_id = d. department_id

WHERE d. department_name = 'Administration'

OR d. department_name = 'Executive'

GROUP BY e. job_id

ORDER BY 2 DESC;

JOB_ID FREQUENCY

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

AD_VP 2

AD_PRES 1

AD_ASST 1

JOB_ID	FREQUENCY
AD_VP	2
AD_ASST	1
AD_PRES	1

14.  Покажите сотрудников, менеджеры которых имеют оклад выше $15000. выведите следующие сведения: фамилию сотрудника, фамилию менеджера, его оклад и уровень оклада (GRADE_LEVEL).

SELECT one. last_name, two. last_name AS manager, two. salary, j. grade_level

FROM employees one

CROSS JOIN employees two

JOIN job_grades j

ON two. salary

BETWEEN j. lowest_sal AND j. highest_sal

WHERE one. manager_id = two. employee_id

AND two. salary > 15000;

(убрать CROSS)

SELECT one. last_name, two. last_name AS manager, two. salary, j. grade_level

FROM employees one

JOIN employees two

ON (one. manager_id = two. employee_id)

JOIN job_grades j

ON two. salary

BETWEEN j. lowest_sal AND j. highest_sal

AND two. salary > 15000;

LAST_NAME MANAGER SALARY GRADE_LEVEL

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

Hartstein King 24000 E

Kochhar King 24000 E

Zlotkey King 24000 E

Cambrault King 24000 E

Errazuriz King 24000 E

Partners King 24000 E

Russell King 24000 E

De Haan King 24000 E

20 rows selected

LAST_NAME	MANAGER	SALARY	GRADE_LEVEL
Kochhar	King	24000	E
De Haan	King	24000	E
Mourgos	King	24000	E
Zlotkey	King	24000	E
Hartstein	King	24000	E
Whalen	Kochhar	17000	E
Higgins	Kochhar	17000	E
Hunold	De Haan	17000	E

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