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

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

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

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

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

…………………

Покажите структуру таблицы 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

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

Создайте запрос для вывода фамилий и дат найма всех служащих, нанятых после 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

По всем служащим, нанятым раньше своих менеджеров, выведите фамилии и даты найма самих служащих, а также фамилии и даты найма их менеджеров. Назовите столбцы 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

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

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