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 |
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 |
Покажите сотрудников, менеджеры которых имеют оклад выше $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 |
Напишите запрос для вывода фамилий, названия отдела, идентификатора местоположения отдела и города, в котором он находится, для всех служащих, зарабатывающих комиссионные.
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
- Создание подзапросов для выборки данных по неизвестным критериям Использование подзапросов для выявления значений, существующих в одном наборе данных и отсутствующих в другом
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
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
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
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
Получите список фамилий и окладов всех служащих, подчинённых Кингу.
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
SELECT department_id, last_name, job_id
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 5 6 7 8 9 10 11 |
