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

AND last_name LIKE '%e%';

LAST_NAME

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

Baer

Bates

Colmenares

Davies

De Haan

Faviet

Fleaur

Gates

Hartstein

Markle

LAST_NAME

De Haan

Davies

Whalen

Hartstein

14.  Запросите фамилии, должности и оклады всех служащих, работающих торговыми представителями (SA_REP) или клерками на складе (ST_CLERK) и с окладом, не равным $2500, $3500 и $7000.

SELECT last_name, job_id, salary

FROM employees

WHERE (job_id IN ('SA_REP', 'ST_CLERK'))

AND (salary NOT IN (2500, 3500, 7000));

LAST_NAME JOB_ID SALARY

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

Tucker SA_REP 10000

Bernstein SA_REP 9500

Hall SA_REP 9000

Olsen SA_REP 8000

Cambrault SA_REP 7500

King SA_REP 10000

Sully SA_REP 9500

McEwen SA_REP 9000

Smith SA_REP 8000

Doran SA_REP 7500

LAST_NAME	JOB_ID	SALARY
Davies	ST_CLERK	3100
Matos	ST_CLERK	2600
Abel	SA_REP	11000
Taylor	SA_REP	8600

15.  Измените lab2_6.sql так, чтобы получить фамилии оклады и комиссионные всех служащих, у которых сумма комиссионных составляет 20%. Выполните запрос ещё раз. Сохраните запрос в файле lab2_15.sql.

DEFINE commission_num = .2

SELECT last_name AS "Employee", salary AS "Monthly Salary", commission_pct

FROM employees

WHERE commission_pct = &commission_num;

UNDEFINE commission_num

или

SELECT last_name AS "Employee", salary AS "Monthly Salary", commission_pct

FROM employees

WHERE commission_pct = .2;

Employee Monthly Salary COMMISSION_PCT

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

Zlotkey 10500 0,2

Olsen 8000 0,2

Cambrault 7500 0,2

Bloom 10000 0,2

Fox 9600 0,2

Taylor 8600 0,2

Livingston 8400 0,2

Employee	Monthly Salary	COMMISSION_PCT
Zlotkey	10500	.2
Taylor	8600	.2

Практическое занятие 3, часть 1

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

1.  Напишите запрос для вывода текущей даты. Назовите столбец Date.

SELECT SYSDATE AS "Date"

FROM DUAL;

DATE

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

08.11.13

DATE

08-MAR-01

2.  Выведите номер служащего, его фамилию, оклад и новый оклад, повышенный на 15.5% и округлённый до целого. Назовите столбец New Salary. Сохраните команду SQL в текстовом файле lab3_2sql.

SELECT employee_id, last_name, salary, ROUND (salary+salary*0.155, 0) "New Salary"

FROM employees;

EMPLOYEE_ID LAST_NAME SALARY New Salary

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

198 OConnell 2600 3000

199 Grant 2600 3000

200 Whalen 4400 5080

201 Hartstein 13000 15020

202 Fay 6000 6930

203 Mavris 6500 7510

204 Baer 10000 11550

205 Higgins 12008 13870

206 Gietz 8300 9590

100 King 24000 27720

101 Kochhar 17000 19640

102 De Haan 17000 19640

103 Hunold 9000 10400

104 Ernst 6000 6930

105 Austin 4800 5540

106 Pataballa 4800 5540

Выполните запрос из файла lab3_2sql.

EMPLOYEE_ID	LAST_NAME	SALARY	New Salary
100	King	24000	27600
101	Kochhar	17000	19550
102	De Haan	17000	19550
103	Hunold	9000	10350
104	Ernst	6000	6900

………….

20 rows selected

4.  Измените lab3_2.sql. добавьте ещё один столбец, который будет содержать результат вычитания старого оклада из нового. Назовите столбец Increase. Сохраните изменённый запрос в lab3_4.sql. Выполните запрос ещё раз.

SELECT employee_id, last_name, salary, ROUND (salary+salary*0.155) "New Salary", ROUND (salary+salary*0.155) - salary "Increase"

FROM employees;

EMPLOYEE_ID LAST_NAME SALARY New Salary Increase

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

198 OConnell 2600 3003 403

199 Grant 2600 3003 403

200 Whalen 4400 5082 682

201 Hartstein 13000 15015 2015

202 Fay 6000 6930 930

EMPLOYEE_ID	LAST_NAME	SALARY	New Salary	Increase
100	King	24000	27600	3600
101	Kochhar	17000	19550	2550
102	De Haan	17000	19550	2550
103	Hunold	9000	10350	1350
104	Ernst	6000	6900	900

…………

20 rows selected

5.  Выведите фамилии служащих (первая буква каждой фамилии должна быть заглавной, а остальные - строчными) и длину каждой фамилии для тех служащих, фамилия которых начинается с символа J, A или М. Присвойте соответствующие заголовки столбцам. Отсортируйте результат по фамилии сотрудников.

SELECT UPPER (LPAD (last_name, 1)) || LOWER(SUBSTR (last_name, 2))

FROM employees;

SELECT UPPER (SUBSTR (last_name, 1, 1)) || LOWER(SUBSTR (last_name, 2)) "Name", LENGTH (last_name) "Length"

FROM employees

WHERE last_name LIKE 'J%'

OR last_name LIKE 'A%'

OR last_name LIKE 'M%'

ORDER BY last_name;

Name Length

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

Abel 4

Ande 4

Atkinson 8

Austin 6

Johnson 7

Jones 5

Name	Length
Abel	4
Matos	5
Mourgos	7

Перепишите запрос таким образом, чтобы пользователь мог при выполнении запроса вводить букву, с которой начинается фамилия сотрудника.

SELECT INITCAP(last_name) "Name", LENGTH (last_name) "Length"

FROM employees

WHERE last_name LIKE UPPER ('&one_letter%')

ORDER BY last_name;

Name Length

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

Kaufling 8

Khoo 4

King 4

Создайте запрос для определения срока работы сотрудников в месяцах до текущей даты. Назовите столбец MONTHS_WORKED. Отсортируйте результат в порядке возрастания числа проработанных месяцев. Округлите результат до целого числа.

SELECT last_name, ROUND(MONTHS_BETWEEN (SYSDATE, hire_date)) month_worked

FROM employees

ORDER BY month_worked;

LAST_NAME MONTH_WORKED

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

Banda 67

Kumar 67

Ande 68

Markle 68

Geoni 69

Philtanker 69

Lee 69

Grant 70

LAST_NAME	MONTH_WORKED
Zlotkey	13
Mourgos	16
Grant	22
Lorentz	25
Vargas	32
Taylor	36
Matos	36
Fay	43

……………..

20 rows selected

Практическое занятие 3, часть 2

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

1.  Получите по каждому служащему отчёт в следующем виде:
<фамилия> зарабатывает <оклад> в месяц, но желает <утроенный оклад>. Назовите столбец Dream Salaries

SELECT Last_name ||' earns'|| TO_CHAR (salary, '$99,999.00') || ' monthly but wants' || TO_CHAR (salary*3, '$999,999.00')

AS "Dream Salaries"

FROM employees;

Dream Salaries

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

OConnell earns $2,600.00 monthly but wants $7,800.00

Grant earns $2,600.00 monthly but wants $7,800.00

Whalen earns $4,400.00 monthly but wants $13,200.00

Hartstein earns $13,000.00 monthly but wants $39,000.00

Fay earns $6,000.00 monthly but wants $18,000.00