Команда select содержит 4 ошибки - Oshibki.top - большая энциклопедия ошибок и их решений

Добавил:

Upload

Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Предмет:

Файл:

blablabla.docx

Скачиваний:

Добавлен:

07.12.2018

Размер:

324.98 Кб

Скачать

Выборка
данных из разных таблиц
Описание
структуры таблиц
Выполнение
арифметических вычислений и задание
имён столбцов
Использование
iSQL*Plus

вызовите
iSQL*Plus
http://pks0.pks:5560/isqlplus
Команды
iSQL*Plus
помогают запрашивать данные?
Нет
Будет
ли успешна эта команда SELECT?
SELECT
*
FROM job_grades;
домашнпя
Да
Команда
SELECT содержит 4 ошибки.
Укажите их.
SELECT employee_id,
last_name,

salary *
12 ANNUAL_SALARY

FROM employees;

Name	Null?	Type
DEPARTMENT_ID	NOT NULL	NUMBER(4)
DEPARTMENT_NAME	NOT NULL	VARCHAR(2)
MANAGER_ID		NUMBER(6)
LOCATION_ID		NUMBER(4)

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
10	Administration	200	1700
20	Marketing	201	1800
50	Shipping	124	1500
60	IT	103	1400
80	Sales	149	2500
90	Executive	100	1700
110	Accounting	205	1700
190	Contracting		1700

Покажите структуру таблицы
DEPARTMENS. Выберите данные
из неё.
desc Departments
8 row
selected

Select
DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID

From departments

Where DEPARTMENT_ID in (10,20,50,60,80,90,110,190)

Покажите
структуру таблицы EMPLOYEES.

desc Employees

Name	Null?	Type
EMPLOYEE_ID	NOT NULL	NUMBER(6)
FIRST_NAME		VARCHAR2(20)
LAST_NAME	NOT NULL	VARCHAR2(25)
EMAIL	NOT NULL	VARCHAR2(25)
PHONE_NUMBER		VARCHAR2(20)
HIRE_DATE	NOT NULL	DATE
JOB_ID	NOT NULL	VARCHAR2(10)
SALARY		NUMBER(8,2)
COMISSION_PCT		NUMBER(2,2)
MANAGER_ID		NUMBER(6)
DEPARTMENT_ID		NUMBER(4)

EMPLOYEE_ID	LAST_NAME	JOB_ID	StartDate
100	King	AD_PRES	17-JUN-87
101	Kochhar	AD_VP	21-SEP-89
102	De Haan	AD_VP	13-JAN-93
103	Hunold	IT_PROG	03-JAN-90
104	Ernst	IT_PROG	21-MAY-91
107	Lorentz	IT_PROG	07-FEB-99
124	Mourgos	ST_MAN	16-NOV-99
141	Rajs	ST_CLERK	17-OCT-95
142	Davies	ST_CLERK	90-JAN-97
143	Matos	ST_CLERK	15-MAR-98
144	Vargas	ST_CLERK	09-JUL-98

Составьте запрос для вывода имени
каждого служащего, должности, даты
найма и номера.

Номер служащего должен быть первым.
Сохраните запрос
с именем
lab1_7.sql.
select employee_id, last_name, job_id, hire_date as
«StartDate» from employees;

JOB_ID

AC_ACCOUNT

AC_MGR

AD_ASST

AD_PRES

AD_VP

IT_PROG

MK_MAN

MK_REP

SA_MAN

SA_REP

ST_CLERK

ST_MAN

Составьте
запрос для вывода неповторяющихся
должностей из таблицы
EMPLOYEES.

select
distinct job_id from EMPLOYEES

Employee and Title

King, AD_PRES

Kochhar, AD_VP

De Haan, AD_VP

Hunold, IT_PROG

Ernst, IT_PROG

Lorentz, IT_PROG

Mourgos, ST_MAN

Rajs, ST_CLERK

Davies, ST_CLERK

Matos, ST_CLERK

Vargas, ST_CLERK

Выведите на экран имя, соединённое
с идентификатором должности через
запятую и пробел. Назовите
новый столбец
Employee and Title
select FIRST_NAME||’, ‘||JOB_ID as «Employee
and Title» from EMPLOYEES

Создайте
запрос для вывода всех данных из таблицы
EMPLOYEES. Разделите столбцы
запятыми. Назовите столбец THE_OUTPUT
select
EMPLOYEE_ID||’,’|| FIRST_NAME ||’,’|| LAST_NAME ||’,’|| EMAIL
||’,’|| PHONE_NUMBER ||’,’|| HIRE_DATE ||’,’|| JOB_ID ||’,’||
SALARY ||’,’|| COMMISSION_PCT ||’,’|| MANAGER_ID ||’,’||
DEPARTMENT_ID as «THE_OUTPUT» from EMPLOYEES

THE_OUTPUT

100,Steven,King,SKING,515.123.4567,AD_PRES,17-JUN-87,24000,,90

101,Neena,Kochhar,NKOCHHAR,515.123.4568,AD_VP,100,21-SEP-89,17000,,90

102,Lex,De
Haan,LDEHAAN,515.123.4569,AD_VP,100,13-JAN-93,17000,,90

103,Alexander,Hunold,AHUNOLD,590.423.4567,IT_PROG,102,03-JAN-90,9000,,60

104,Bruce,Ernst,BERNST,590.423.4568,IT_PROG,103,21-MAY-91,6000,,60

107,Diana,LorentzDLORENTZ,590.423.5567,IT_PROG,103,07-FEB-99,4200,,60

…..

Лабораторная работа 2

Выборка
данных и изменение последовательности
вывода строк
Ограничение
количества возвращаемых строк с помощью
предложения WHERE
Сортировка
строк с помощью предложения ORDER
BY

Создайте запрос
для вывода имени и заработной платы
служащих, зарабатывающих более $12000.
Сохраните команду
SQL в файле
lab2_1.sql. выполните
запрос.
SELECT first_name, salary
FROM employees where salary>12000

LAST_NAME	SALARY
King	24000
Kochhar	17000
De Haan	17000
Hartstein	13000

Создайте запрос
для вывода фамилии и номера отдела
служащего под номером 176

SELECT
last_name, department_id FROM employees where employee_id=176

LAST_NAME	DEPARTMENT_ID
Taylor	80

Измените файл
lab2_1.sql и
выведите фамилии и оклады всех служащих,
чей оклад не входит в диапазон от $5000
до $12000. сохраните команду SQL
в файле lab2_3.sql.
SELECT
first_name,
salary FROM
employees where
salary>12000 or
salary<5000

LAST_NAME	SALARY
King	24000
Kochhar	17000
De Haan	17000
Lorentz	4200
Rajs	3500
Davies	3100
Matos	2600
Vargas	2500
Whalen	4400
Hartstein	13000

LAST_NAME	JOB_ID	HIRE_DATE
Matos	ST_CLERK	15-MAR-98
Taylor	SA_REP	24-MAR-98

Выведите фамилию,
идентификатор должности и дату начала
работы всех служащих, нанятых в период
с 20 февраля 1998 по 1 мая 1998 г. Отсортируйте
данные в порядке возрастания даты
найма.
SELECT last_name, job_id,
hire_date FROM employees where hire_date>=’20-FEB-1998′ and
hire_date<=’1-MAY-1998′ Order by hire_date

Выведите фамилию
и номер отдела всех служащих из отделов
20 и 50. Отсортируйте данные
по фамилиям
в алфавитном
порядке.
SELECT LAST_NAME,
DEPARTMENT_ID FROM employees where department_id in (20,50) Order
by LAST_NAME

LAST_NAME	DEPARTMENT_ID
Davies	50
Fay	20
Hartstein	20
Matos	50
Mourgos	50
Rajs	50
Vargas	50

7
row selected

Измените файл
lab2_3.sql для
вывода фамилий и окладов служащих
отделов 20 и 50, зарабатывающих от $5000 до
$12000. Назовите столбцы Employee
и Monthly Salary,
соответственно. Вновь сохраните команду
SQL в файле lab2_6.sql.
Выполните запрос.
SELECT
first_name as «Employee», salary as «Monthly Salary»
FROM employees where salary>5000 and salary<12000 and
department_id in (20,50)

EMPLOYEE	Monthly Salary
Mourgos	5800
Fay	6000

Выведите
фамилию и
дату найма
всех служащих,
нанятых в
1994 г.
SELECT last_name, hire_date
FROM employees where hire_date>’31-DEC-93′ and
hire_date<‘1-JAN-95’

LAST_NAME	HIRE_DATE
Higgins	07-JUN-94
Gietz	07-JUN-94

Выведите фамилии
и должности всех служащих, не имеющих
менеджера.
SELECT last_name, job_id
FROM employees where manager_id is null

LAST_NAME	JOB_ID
King	AD_PRES

Выведите фамилию,
и комиссионные всех служащих,
зарабатывающих комиссионные. Отсортируйте
данные в
порядке убывания
окладов и
комиссионных.
SELECT
LAST_NAME, SALARY, COMMISSION_PCT FROM employees where
commission_pct is not null Order by commission_pct desc

LAST_NAME	SALARY	COMMISSION_PCT
Abel	11000	.3
Zlotkey	10500	.2
Taylor	8600	.2
Grant	7000	.15

Выведите
все фамилии
служащих, в
которых третья
буква – а.
SELECT
LAST_NAME FROM employees where last_name like ‘__a%’

LAST_NAME

Grant

Whalen

Выведите фамилии
всех служащих, в которых есть буквы
«а» и «е».
SELECT
LAST_NAME FROM employees where last_name like ‘%a%’ and last_name
like ‘%e%’

LAST_NAME

De Haan

Davies

Whalen

Hartstein
запросите фамилии,
должности и оклады всех служащих,
работающих торговыми представителями
(SA_REP) или
клерками на складе (ST_CLERK)
и с окладом, не равным $2500, $3500 и
$7000.
SELECT LAST_NAME, JOB_ID, SALARY FROM
employees where (job_id = ‘ST_CLERK’ or job_id = ‘SA_REP’) and
salary not in (2500,3500,7000)

LAST_NAME

JOB_ID

SALARY

Davies

ST_CLERK

3100

Matos

ST_CLERK

2600

Abel

SA_REP

11000

Taylor

SA_REP

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

SELECT
LAST_NAME as «Employee», SALARY as «Monthly Salary»,
COMMISSION_PCT FROM employees where commission_pct is not null and
commission_pct>.2 Order by commission_pct desc

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

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник

ПРАКТИЧЕСКИЕ ЗАНЯТИЯ ПО КУРСУ
ВВЕДЕНИЕ В ORACLE 10g: SQL (Часть 1)
Без ORDER BY как (в каком порядке) выводятся значения в таблице? (см. книгу)
Практическое занятие 1
• Выборка данных из разных таблиц
• Описание структуры таблиц
• Выполнение арифметических вычислений и задание имён столбцов
• Использование iSQL*Plus
1. Будет ли успешна эта команда SELECT?
SELECT last_name, job_id, salary As sal
FROM employees;
Да/Нет
2. Будет ли успешна эта команда SELECT?
SELECT *
FROM job_grades;
Да/Нет
3. Команда SELECT содержит 4 ошибки. Укажите их.
SELECT employee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;
Верно:
SELECT employee_id, last_name, (salary*12) «ANNUAL SALARY»
FROM employees;
EMPLOYEE_ID LAST_NAME ANNUAL SALARY
———————- ————————- ———————-
198 OConnell 31200
199 Grant 31200
200 Whalen 52800
201 Hartstein 156000
4. Покажите структуру таблицы DEPARTMENTS. Выберите данные из неё.
DESCRIBE departments
SELECT *
FROM departments;
Name Null Type
—————————— ——— ———————————————————————————————————————————————————————————————
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
———————- —————————— ———————- ———————-
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR(2)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
10 Administration 200 1700
20 Marketing 201 1800
50 Shipping 124 1500
60 IT 103 1400
80 Sales 149 2500
90 Executive 100 1700
110 Accounting 205 1700
190 Contracting 1700
8 row selected
5. Покажите структуру таблицы EMPLOYEES.
Составьте запрос для вывода имени каждого служащего, должности, даты найма и номера. Номер служащего должен быть первым. Столбец Hire_date должен отображаться под именем StartDate Сохраните запрос с именем lab1_7.sql.
DESCRIBE EMPLOYEES
SELECT employee_id, last_name, job_id, hire_date AS «StartDate»
FROM employees;
DESCRIBE EMPLOYEES
Name Null Type
—————————— ——— ———————————————————————————————————————————————————————————————
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
EMPLOYEE_ID LAST_NAME JOB_ID StartDate
———————- ————————- ———- ————————-
198 OConnell SH_CLERK 21.06.07
199 Grant SH_CLERK 13.01.08
200 Whalen AD_ASST 17.09.03
201 Hartstein MK_MAN 17.02.04
202 Fay MK_REP 17.08.05
203 Mavris HR_REP 07.06.02
Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
EMPLOYEE_ID LAST_NAME JOB_ID StartDate
100 King AD_PRES 17-JUN-87
101 Kochhar AD_VP 21-SEP-89
102 De Haan AD_VP 13-JAN-93
103 Hunold IT_PROG 03-JAN-90
104 Ernst IT_PROG 21-MAY-91
107 Lorentz IT_PROG 07-FEB-99
124 Mourgos ST_MAN 16-NOV-99
141 Rajs ST_CLERK 17-OCT-95
142 Davies ST_CLERK 90-JAN-97
143 Matos ST_CLERK 15-MAR-98
144 Vargas ST_CLERK 09-JUL-98
8. Проверьте выполнение запроса из файла lab1_7.sql, чтобы убедиться, что он выполняется правильно.
9. Составьте запрос для вывода неповторяющихся должностей из таблицы EMPLOYEES.
SELECT DISTINCT job_id
FROM employees;
JOB_ID
———-
AC_ACCOUNT
AC_MGR
AD_ASST
AD_PRES
AD_VP
FI_ACCOUNT
FI_MGR
JOB_ID
AC_ACCOUNT
AC_MGR
AD_ASST
AD_PRES
AD_VP
IT_PROG
MK_MAN
MK_REP
SA_MAN
SA_REP
ST_CLERK
ST_MAN
10. Скопируйте команды из файла lab1_7.sql в окно редактирования iSQL*Plus. Назовите столбцы Emp #, Employee, Job, Hire Date. Выполните запрос ещё раз.
SELECT employee_id AS «Emp#», last_name AS «Employee», job_id AS «Job», hire_date AS «Hire Date»
FROM employees;
Emp# Employee Job Hire Date
———————- ————————- ———- ————————-
198 OConnell SH_CLERK 21.06.07
199 Grant SH_CLERK 13.01.08
200 Whalen AD_ASST 17.09.03
201 Hartstein MK_MAN 17.02.04
202 Fay MK_REP 17.08.05
203 Mavris HR_REP 07.06.02
Emp # Employee Job Hire Date
100 King AD_PRES 17-JUN-87
101 Kochhar AD_VP 21-SEP-89
102 De Haan AD_VP 13-JAN-93
103 Hunold IT_PROG 03-JAN-90
104 Ernst IT_PROG 21-MAY-91
107 Lorentz IT_PROG 07-FEB-99
124 Mourgos ST_MAN 16-NOV-99
141 Rajs ST_CLERK 17-OCT-95
142 Davies ST_CLERK 90-JAN-97
143 Matos ST_CLERK 15-MAR-98
144 Vargas ST_CLERK 09-JUL-98
11. Выведите на экран имя, соединённое с идентификатором должности через запятую и пробел. Назовите новый столбец Employee and Title
SELECT last_name ||’, ‘|| job_id
AS «Employee and Title»
FROM employees;
Employee and Title
————————————
Abel,SA_REP
Ande,SA_REP
Atkinson,ST_CLERK
Austin,IT_PROG
Baer,PR_REP
12. Создайте запрос для вывода всех данных из таблицы EMPLOYEES. Разделите столбцы запятыми. Назовите столбец THE_OUTPUT
SELECT employee_id ||’,’|| first_name ||’,’|| last_name ||’,’|| email ||’,’|| phone_number ||’,’|| hire_date ||’,’|| job_id ||’,’|| salary ||’,’|| commission_pct ||’,’|| manager_id ||’,’|| department_id
AS the_output
FROM employees;
THE_OUTPUT
——————————————————————————————————————————————————————————————————————————————————————————————————————————
198,Donald,OConnell,DOCONNEL,650.507.9833,21.06.07,SH_CLERK,2600,,124,50
199,Douglas,Grant,DGRANT,650.507.9844,13.01.08,SH_CLERK,2600,,124,50
200,Jennifer,Whalen,JWHALEN,515.123.4444,17.09.03,AD_ASST,4400,,101,10
201,Michael,Hartstein,MHARTSTE,515.123.5555,17.02.04,MK_MAN,13000,,100,20
202,Pat,Fay,PFAY,603.123.6666,17.08.05,MK_REP,6000,,201,20
Практическое занятие 2
• Выборка данных и изменение последовательности вывода строк
• Ограничение количества возвращаемых строк с помощью предложения WHERE
• Сортировка строк с помощью предложения ORDER BY
1. Создайте запрос для вывода имени и заработной платы служащих, зарабатывающих более $12000. Сохраните команду SQL в файле lab2_1.sql. выполните запрос.
SELECT last_name, salary
FROM employees
WHERE salary > 12000;
LAST_NAME SALARY
————————- ———————-
Hartstein 13000
Higgins 12008
King 24000
LAST_NAME SALARY
King 24000
Kochhar 17000
De Haan 17000
Hartstein 13000
2. Создайте запрос для вывода фамилии и номера отдела служащего под номером 176
SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;
LAST_NAME DEPARTMENT_ID
————————- ———————-
Taylor 80
LAST_NAME DEPARTMENT_ID
Taylor 80
3. Измените файл lab2_1.sql и выведите фамилии и оклады всех служащих, чей оклад не входит в диапазон от $5000 до $12000. сохраните команду SQL в файле lab2_3.sql.
SELECT last_name, salary
FROM employees
WHERE salary
NOT BETWEEN 5000 AND 12000;
LAST_NAME SALARY
————————- ———————-
OConnell 2600
Grant 2600
Whalen 4400
Hartstein 13000
Higgins 12008
King 24000
LAST_NAME SALARY
King 24000
Kochhar 17000
De Haan 17000
Lorentz 4200
Rajs 3500
Davies 3100
Matos 2600
Vargas 2500
Whalen 4400
Hartstein 13000
LAST_NAME JOB_ID HIRE_DATE
Matos ST_CLERK 15-MAR-98
Taylor SA_REP 24-MAR-98
4. Выведите фамилию, идентификатор должности и дату начала работы всех служащих, нанятых в период с 20 февраля 2008 по 1 мая 2008 г. Отсортируйте данные в порядке возрастания даты найма.
SELECT last_name, job_id, hire_date
FROM employees
WHERE hire_date >= ‘20.02.2008’
AND hire_date <= ‘01.05.2008’
ORDER BY hire_date;
LAST_NAME JOB_ID HIRE_DATE
————————- ———- ————————-
Lee SA_REP 23.02.08
Markle ST_CLERK 08.03.08
Ande SA_REP 24.03.08
Banda SA_REP 21.04.08
Kumar SA_REP 21.04.08
5. Выведите фамилию и номер отдела всех служащих из отделов 20 и 50. Отсортируйте данные по фамилиям в алфавитном порядке.
SELECT last_name, department_id
FROM employees
WHERE department_id = 20
OR department_id = 50
ORDER BY last_name;
LAST_NAME DEPARTMENT_ID
————————- ———————-
Atkinson 50
Bell 50
Bissot 50
Bull 50
Cabrio 50
Chung 50
Davies 50
Dellinger 50
Dilly 50
Everett 50
Fay 20
Feeney 50
LAST_NAME DEPARTMENT_ID
Davies 50
Fay 20
Hartstein 20
Matos 50
Mourgos 50
Rajs 50
Vargas 50
7 row selected
6. Измените файл lab2_3.sql для вывода фамилий и окладов служащих отделов 20 и 50, зарабатывающих от $5000 до $12000. Назовите столбцы Employee и Monthly Salary, соответственно. Вновь сохраните команду SQL в файле lab2_6.sql. Выполните запрос.
SELECT last_name AS employee, salary AS «Monthly Salary»
FROM employees
WHERE department_id IN (20, 50)
AND salary BETWEEN 5000
AND 12000;
EMPLOYEE Monthly Salary
————————- ———————-
Fay 6000
Weiss 8000
Fripp 8200
Kaufling 7900
Vollman 6500
Mourgos 5800
EMPLOYEE Monthly Salary
Mourgos 5800
Fay 6000
7. Выведите фамилию и дату найма всех служащих, нанятых в 2004 г.
SELECT last_name, hire_date
FROM employees
WHERE hire_date LIKE ‘%04’;
LAST_NAME HIRE_DATE
————————- ————————-
Hartstein 17.02.04
Weiss 18.07.04
Mallin 14.06.04
Russell 01.10.04
King 30.01.04
LAST_NAME HIRE_DATE
Higgins 07-JUN-94
Gietz 07-JUN-94
8. Выведите фамилии и должности всех служащих, не имеющих менеджера.
SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;
LAST_NAME JOB_ID
————————- ———-
King AD_PRES
LAST_NAME JOB_ID
King AD_PRES
9. Выведите фамилию, оклады и комиссионные всех служащих, зарабатывающих комиссионные. Отсортируйте данные в порядке убывания окладов и комиссионных.
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC, commission_pct DESC;
LAST_NAME SALARY COMMISSION_PCT
————————- ———————- ———————-
Russell 14000 0,4
Partners 13500 0,3
Errazuriz 12000 0,3
Ozer 11500 0,25
Cambrault 11000 0,3
Abel 11000 0,3
Vishney 10500 0,25
LAST_NAME SALARY COMMISSION_PCT
Abel 11000 .3
Zlotkey 10500 .2
Taylor 8600 .2
Grant 7000 .15
10. Создайте запрос для вывода фамилии и зарплаты сотрудников, у которых зарплата превышает некоторое заданное значение, которое вводится при выполнении запроса. Сохраните этот запрос в файле lab2_10.sql
SELECT last_name, salary
FROM employees
WHERE salary > &salary_num;
FROM employees
WHERE salary > 5000
LAST_NAME SALARY
————————- ———————-
Hartstein 13000
Fay 6000
Mavris 6500
Baer 10000
Higgins 12008
Gietz 8300
King 24000
Kochhar 17000
De Haan 17000
11. Создайте запрос для вывода номеров сотрудников, фамилий сотрудников, зарплаты и отдела. Номер руководителя сотрудников должен вводиться при выполнении запроса
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE manager_id = &manager_num;
WHERE manager_id = 103
EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID
———————- ————————- ———————- ———————-
104 Ernst 6000 60
105 Austin 4800 60
106 Pataballa 4800 60
12. Выведите все фамилии служащих, в которых третья буква – а.
SELECT last_name
FROM employees
WHERE last_name LIKE ‘__a%’;
LAST_NAME
————————-
Grant
Grant
Whalen
LAST_NAME
Grant
Whalen
13. Выведите фамилии всех служащих, в которых есть буквы «а» и «е».
SELECT last_name
FROM employees
WHERE last_name LIKE ‘%a%’
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
3. Выполните запрос из файла 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
6. Перепишите запрос таким образом, чтобы пользователь мог при выполнении запроса вводить букву, с которой начинается фамилия сотрудника.
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
7. Создайте запрос для определения срока работы сотрудников в месяцах до текущей даты. Назовите столбец 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
Mavris earns $6,500.00 monthly but wants $19,500.00
Baer earns $10,000.00 monthly but wants $30,000.00
Higgins earns $12,008.00 monthly but wants $36,024.00
Gietz earns $8,300.00 monthly but wants $24,900.00
King earns $24,000.00 monthly but wants $72,000.00
Dream Salaries
King earns $24,000.00 monthly but wants $72,000.00
Kochhar earns $17,000.00 monthly but wants $51,000.00
De Haan earns $17,000.00 monthly but wants $51,000.00
Hunold earns $9,000.00 monthly but wants $27,000.00
Ernst earns $6,000.00 monthly but wants $18,000.00
Lorentz earns $4,200.00 monthly but wants $12,600.00
Mourgos earns $5,800.00 monthly but wants $17,400.00
Rajs earns $3,500.00 monthly but wants $10,500.00
Davies earns $3,100.00 monthly but wants $9,300.00
……………
20 rows selected
2. Напишите запрос для вывода фамилий и окладов всех служащих. Назовите выходной столбец SALARY. Длина столбца SALARY – 15 символов с заполнением символом $.
SELECT last_name, LPAD (salary, 15, ‘$’) salary
FROM employees;
LAST_NAME SALARY
————————- —————
OConnell $$$$$$$$$$$2600
Grant $$$$$$$$$$$2600
Whalen $$$$$$$$$$$4400
Hartstein $$$$$$$$$$13000
Fay $$$$$$$$$$$6000
Mavris $$$$$$$$$$$6500
Baer $$$$$$$$$$10000
LAST_NAME SALARY
King $$$$$$$$$$24000
Kochhar $$$$$$$$$$17000
De Haan $$$$$$$$$$17000
Hunold $$$$$$$$$$$9000
Ernst $$$$$$$$$$$6000
Lorentz $$$$$$$$$$$4200
Mourgos $$$$$$$$$$$5800
Rajs $$$$$$$$$$$3500
Davies $$$$$$$$$$$3100
Matos $$$$$$$$$$$2600
………………
20 rows selected
3. Для каждого служащего выведите фамилию, дату найма и дату пересмотра зарплаты, которая приходится на первый понедельник после 6 месяцев работы. Назовите столбец REVIEW. Формат даты при выводе имеет вид: “Monday, the Thirty-First of MM, YYYY”.
SELECT last_name,
TO_CHAR (hire_date, ‘DD-MON-YY’, ‘NLS_DATE_LANGUAGE=american’) hire_date,
TO_CHAR (NEXT_DAY (ADD_MONTHS (hire_date, 6), ‘Понедельник’), ‘fmDay, «the» Ddspth «of» Month, YYYY’, ‘NLS_DATE_LANGUAGE=american’) review
FROM employees;
LAST_NAME HIRE_DATE REVIEW
————————- ——— ————————————————
OConnell 21-JUN-07 Monday, the Twenty-Fourth of December, 2007
Grant 13-JAN-08 Monday, the Fourteenth of July, 2008
Whalen 17-SEP-03 Monday, the Twenty-Second of March, 2004
Hartstein 17-FEB-04 Monday, the Twenty-Third of August, 2004
Fay 17-AUG-05 Monday, the Twentieth of February, 2006
Mavris 07-JUN-02 Monday, the Ninth of December, 2002
Baer 07-JUN-02 Monday, the Ninth of December, 2002
LAST_NAME HIRE_DATE REVIEW
King 17-JUN-87 Monday, the Thirty-First of December, 1987
Kochhar 21-SEP-89 Monday, the Thirty-Sixth of March, 1990
De Haan 13-JAN-93 Monday, the Nineteenth of July, 1993
Hunold 03-JAN-90 Monday, the Ninth of July, 1990
20 rows selected
4. По каждому служащему выведите фамилию, дату найма и день недели, когда он был нанят на работу. Назовите последний столбец DAY. Отсортируйте результаты по дням недели.
SELECT last_name,
TO_CHAR (hire_date, ‘DD-MON-YY’, ‘NLS_DATE_LANGUAGE=american’) «HIRE_DАTE»,
TO_CHAR (hire_date, ‘fmDay’, ‘NLS_DATE_LANGUAGE=american’) day
FROM employees
ORDER BY TO_CHAR (hire_date, ‘D’);
LAST_NAME HIRE_DАTE DAY
————————- ——— ———
Mallin 14-JUN-04 Monday
Banda 21-APR-08 Monday
Ladwig 14-JUL-03 Monday
Vollman 10-OCT-05 Monday
Walsh 24-APR-06 Monday
Kumar 21-APR-08 Monday
Ernst 21-MAY-07 Monday
Ande 24-MAR-08 Monday
Greene 19-MAR-07 Monday
Cambrault 15-OCT-07 Monday
Olson 10-APR-07 Tuesday
Urman 07-MAR-06 Tuesday
Hunold 03-JAN-06 Tuesday
LAST_NAME HIRE_DATE DAY
Grant 24-MAY-99 MONDAY
Ernst 21-MAY-91 TUESDAY
Mourgos 16-NOV-99 TUESDAY
Taylor 24-MAR-98 TUESDAY
Rajs 17-OCT-95 TUESDAY
Gietz 07-JUN-94 TUESDAY
Higgins 07-JUN-94 TUESDAY
King 17-JUN-87 WEDNESDAY
De Haan 13-JAN-93 WEDNESDAY
………………
20 rows selected
5. Напишите запрос для вывода фамилии и суммы комиссионных каждого служащего. Если служащий не зарабатывает комиссионных, укажите в столбце «No Commission». Назовите столбец COMM.
SELECT last_name, NVL (TO_CHAR (commission_pct), ‘No Commission’) COMM
FROM employees;
LAST_NAME COMM
————————- —————————————-
OConnell No Commission
Grant No Commission
Whalen No Commission
Vargas No Commission
Russell ,4
Partners ,3
Errazuriz ,3
LAST_NAME COMM
King No Commission
Kochhar No Commission
De Haan No Commission
Hunold No Commission
Ernst No Commission
Lorentz No Commission
Mourgos No Commission
Rajs No Commission
Davies No Commission
Matos No Commission
Vargas No Commission
Zlotkey .2
Abel .3
…………….
20 rows selected
6. Напишите запрос для отображения фамилий сотрудников и их заработной платы в виде гистограммы, состоящей из звёздочек. Каждая звёздочка означает 1000$. Строки должны быть отсортированы по заработной плате в убывающем порядке. Результат должен быть выведен одним столбцом, озаглавленным как EMPLOYEES AND THEIR SALARIES.
SELECT last_name || TRIM (‘ ‘ FROM RPAD (‘ ‘, salary/1000+1, ‘*’))
«EMPLOYEES AND THERE SALARIES»
FROM employees
ORDER BY salary DESC;
———————————————————————————————————————————
Hartstein*************
EMPLOYEES AND THEIR SALARIES
King************************
Kochhar*****************
De Haan*****************
Hartstein*************
Higgins************
Abel***********
………………….
20 rows selected
7. Используя функцию DECODE, напишите запрос для отображения должности сотрудника и её разряда (grade). Разряд каждого типа должности JOB_ID приведён в таблице.
SELECT job_id,
DECODE (job_id, ‘AD_PRES’, ‘A’,
‘ST_MAN’, ‘B’,
‘IT_PROG’, ‘C’,
‘SA_REP’, ‘D’,
‘ST_CLERK’, ‘E’,
0)
G
FROM employees;
JOB_ID G
———- —
AC_ACCOUNT 0
AC_MGR 0
AD_ASST 0
AD_PRES A
AD_VP 0
AD_VP 0
FI_ACCOUNT 0
FI_ACCOUNT 0
FI_ACCOUNT 0
FI_ACCOUNT 0
FI_ACCOUNT 0
FI_MGR 0
HR_REP 0
IT_PROG C
IT_PROG C
IT_PROG C
IT_PROG C
Должность Разряд
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
Другая 0
JOB_ID G
AD_PRES A
AD_VP 0
AD_VP 0
IT_PROG C
IT_PROG C
IT_PROG C
ST_MAN B
ST_CLERK E
…………………..
20 rows selected
8. Перепишите команду из предыдущего задания, используя синтаксис выражения CASE.
SELECT job_id,
CASE job_id WHEN ‘AD_PRES’ THEN ‘A’
WHEN ‘ST_MAN’ THEN ‘B’
WHEN ‘IT_PROG’ THEN ‘C’
WHEN ‘SA_REP’ THEN ‘D’
WHEN ‘ST_CLERK’ THEN ‘E’
ELSE ‘0’
END G
FROM employees;
JOB_ID G
———- —
AC_ACCOUNT 0
AC_MGR 0
AD_ASST 0
AD_PRES A
AD_VP 0
AD_VP 0
FI_ACCOUNT 0
FI_ACCOUNT 0
FI_ACCOUNT 0
Практическое занятие 4
• Использование групповых функций
• Вывод данных по группам с помощью предложения GROUP BY
• Включение и исключение групп с помощью предложения HAVING
1. Напишите запрос для вывода самого высокого, самого низкого и среднего оклада по всем служащим, а также суммы всех окладов. Назовите столбцы Maximum, Minimum, Average и Sum. Округлите средний оклад до ближайшего целого значения. Сохраните свой запрос в файле lab5_6.sql.
SELECT MAX (salary) «Maximum», MIN (salary) «Minimum», SUM (salary) «Sum», ROUND (AVG (salary), 0) «Average»
FROM employees;
Maximum Minimum Sum Average
———- ———- ———- ———-
24000 2100 691408 6462
Maximum Minimum Sum Average
24000 2500 175500 8775
2. Измените запрос в lab5_6.sql. так, чтобы получить самый низкий, самый высокий и средний оклады, а также сумму окладов по каждой должности. Сохраните изменённый запрос в файле lab5_5.sql. Выполните запрос из lab5_5.sql.
SELECT job_id, MAX (salary) «Maximum», MIN (salary) «Minimum», SUM (salary) «Sum», ROUND (AVG (salary), 0) «Average»
FROM employees
GROUP BY job_id;
JOB_ID Maximum Minimum Sum Average
———- ———- ———- ———- ———-
AC_ACCOUNT 8300 8300 8300 8300
AC_MGR 12008 12008 12008 12008
AD_ASST 4400 4400 4400 4400
AD_PRES 24000 24000 24000 24000
AD_VP 17000 17000 34000 17000
FI_ACCOUNT 9000 6900 39600 7920
JOB_ID Maximum Minimum Sum Average
AC_ACCOUNT 8300 8300 8300 8300
AC_MGR 12000 12000 12000 12000
AD_ASST 4400 4400 4400 4400
AD_PRES 24000 24000 24000 24000
AD_VP 17000 17000 34000 17000
…………….
3. Напишите запрос для вывода должности и количества служащих, занимающих каждую должность.
4.
SELECT job_id, COUNT (*) cnt
FROM employees
GROUP BY job_id;
JOB_ID CNT
———- ———-
AC_ACCOUNT 1
AC_MGR 1
AD_ASST 1
AD_PRES 1
AD_VP 2
FI_ACCOUNT 5
JOB_ID CNT
AD_ACCOUNT 1
AC_MGR 1
AD_ASST 1
AD_PRES 1
AD_VP 2
IT_PROG 3
MK_MAN 1
MK_REP 1
SA_MAN 1
SA_REP 3
ST_CLERK 4
ST_MAN 1
5. Получите количество служащих, имеющих подчинённых, без их перечисления. Назовите столбец Number of Managers. Используйте столбец MANAGER_ID для определения числа менеджеров.
SELECT COUNT (DISTINCT manager_id) «Number of Managers»
FROM employees;
Number of Managers
——————
18
Number of Managers
8
6. Напишите запрос для вывода разности между самым высоким и самым низким окладами. Назовите столбец DIFFERENCE
SELECT (MAX (salary) — MIN (salary)) DIFFERENCE
FROM employees;
DIFFERENCE
———-
21900
DIFFERENCE
21500
7. Напишите запрос для вывода номера каждого менеджера, имеющего подчинённых, и заработную плату самого низкооплачиваемого из его подчинённых. Исключите менеджеров для которых неизвестны их менеджеры. Исключите все группы, где минимальный оклад составляет менее $6000. Отсортируйте выходные строки в порядке убывания оклада.
SELECT manager_id, MIN (salary) min_salary
FROM employees
WHERE manager_id IS NOT NULL
HAVING MIN (salary) >= 6000
GROUP BY manager_id
ORDER BY min_salary DESC;
MANAGER_ID MIN_SALARY
———- ———-
102 9000
205 8300
146 7000
145 7000
MANAGER_ID MIN_SALARY
102 9000
205 8300
149 7000
8. Напишите запрос для вывода общего количества служащих и количества служащих, нанятых в 2005, 2006, 2007 и 2008 годах. Дайте соответствующие заголовки столбцам.
SELECT COUNT (*) total,
SUM (DECODE (TO_CHAR (hire_date, ‘YYYY’), 05, 1)) «2005»,
SUM (DECODE (TO_CHAR (hire_date, ‘YY’), 06, 1)) «2006»,
SUM (DECODE (TO_CHAR (hire_date, ‘YY’), 07, 1)) «2007»,
SUM (DECODE (TO_CHAR (hire_date, ‘YY’), 08, 1)) «2008»
FROM employees;
TOTAL 2005 2006 2007 2008
———- ———- ———- ———- ———-
107 29 24 19 11
TOTAL 2005 2006 2007 2008
20 1 2 2 3
9 Напишите матричный запрос для вывода всех должностей и суммы заработной платы служащих, работающих в этой должности в отделах 20, 50, 80 и 90. Последний столбец должен содержать сумму заработной платы служащих этих отделов, занимающих каждую конкретную должность. Дайте столбцам соответствующие заголовки.
SELECT job_id «Job»,
SUM (DECODE (department_id, 20, salary)) «Dept20»,
SUM (DECODE (department_id, 50, salary)) «Dept50»,
SUM (DECODE (department_id, 80, salary)) «Dept80»,
SUM (DECODE (department_id, 90, salary)) «Dept90»,
SUM (salary) «Total»
FROM employees
GROUP BY job_id;
Job Dept20 Dept50 Dept80 Dept90 Total
———- ———- ———- ———- ———- ———-
AC_ACCOUNT 8300
AC_MGR 12008
AD_ASST 4400
AD_PRES 24000 24000
AD_VP 34000 34000
FI_ACCOUNT 39600
FI_MGR 12000
Job Dept20 Dept50 Dept80 Dept90 Total
AC_ACCOUNT 4200 4100 8300
AC_MGR 7800 4200 12000
AD_ASST 4400 4400
AD_PRES 24000 24000
AD_VP 34000 34000
IT_PROG 14700 4500 19200
……………………………
ДОПОЛНИТЕЛЬНО (надо было переделать):
SELECT job_id «Job»,
SUM (DECODE (department_id, 20, salary)) «Dept20»,
SUM (DECODE (department_id, 50, salary)) «Dept50»,
SUM (DECODE (department_id, 80, salary)) «Dept80»,
SUM (DECODE (department_id, 90, salary)) «Dept90»,
SUM (DECODE (department_id, 20, salary,
50, salary,
80, salary,
90, salary)) «Total»
FROM employees
GROUP BY job_id;
Job Dept20 Dept50 Dept80 Dept90 Total
———- ———- ———- ———- ———- ———-
AC_ACCOUNT
AC_MGR
AD_ASST
AD_PRES 24000 24000
AD_VP 34000 34000
FI_ACCOUNT
FI_MGR
HR_REP
IT_PROG
MK_MAN 13000 13000
MK_REP 6000 6000
PR_REP
PU_CLERK
PU_MAN
SA_MAN 61000 61000
SA_REP 243500 243500
SH_CLERK 64300 64300
ST_CLERK 55700 55700
ST_MAN 36400 36400
19 rows selected
Доп задача 1.11
Написать команду, которая позволит выводить фамилию сотрудника через запятую столько раз, сколько букв в его фамилии.
SELECT LPAD (last_name, LENGTH(last_name)*LENGTH(last_name)+(2*(LENGTH(last_name)-1)), last_name ||’, ‘) «LAST_NAME»
FROM employees;
Или
SELECT RPAD (last_name, LENGTH(last_name)*LENGTH(last_name)+(2*(LENGTH(last_name)-1)), ‘, ‘||last_name) «LAST_NAME»
FROM employees;
———————————————————————————————————————————
Banda, Banda, Banda, Banda, Banda
Практическое занятие 5
• Соединение таблиц с использованием эквисоединения.
• Выполнение внешних соединений и соединений таблицы с собой
• Включение дополнительных условий.
1. Напишите запрос для вывода названия отдела, местоположения отдела, количества служащих и среднего оклада по этому отделу. Назовите столбцы Name, Location, Number of People и Salary. Округлите средний оклад до ближайшего целого значения.
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
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. Напишите запрос для вывода фамилий, названия отдела, идентификатора местоположения отдела и города, в котором он находится, для всех служащих, зарабатывающих комиссионные.
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
• Создание подзапросов для выборки данных по неизвестным критериям
• Использование подзапросов для выявления значений, существующих в одном наборе данных и отсутствующих в другом
1. Создайте запрос для вывода фамилии и даты найма каждого служащего, работающего в одном отделе с Zlotkey. Исключите Zlotkey из выходных данных.
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
2. Создайте запрос для вывода номеров и фамилий всех служащих, оклад которых выше среднего. Отсортируйте данные в порядке увеличения окладов.
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
3. Создайте запрос для вывода номеров и фамилий всех служащих, работающих в одном отделе с любым служащим, фамилия которого содержит букву «u». Сохраните свой запрос в тестовом файле lab6_3.sql. Выполните запрос.
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
4. Создайте запрос для вывода фамилии, номера отдела и должности каждого служащего, идентификатор местоположения отдела которого равен 1700.
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
5. Получите список фамилий и окладов всех служащих, подчинённых Кингу.
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
6. Получите номер отдела, фамилию и должность для каждого служащего, работающего в администрации (department_name = ‘Executive’).
SELECT department_id, last_name, job_id
FROM employees
WHERE department_id IN
(SELECT department_id
FROM departments
WHERE department_name = ‘Executive’);
DEPARTMENT_ID LAST_NAME JOB_ID
————- ————————- ———-
90 King AD_PRES
90 Kochhar AD_VP
90 De Haan AD_VP
7. Измените lab6_3.sql для вывода номеров, фамилий и окладов всех служащих, которые работают в одном отделе с любым служащим с буквой «u» в фамилии и оклады которых превышают средний. Сохраните новый запрос в файле lab6_7.sql. Выполните его.
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN
(SELECT department_id
FROM employees
WHERE last_name LIKE ‘%u%’)
AND salary > (SELECT AVG(salary)
FROM employees);
Или
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN
(SELECT department_id
FROM employees
WHERE last_name LIKE ‘%u%’)
AND salary > ALL (SELECT AVG(salary)
FROM employees);
EMPLOYEE_ID LAST_NAME SALARY
———— ————————- ———-
103 Hunold 9000
123 Vollman 6500
122 Kaufling 7900
121 Fripp 8200
120 Weiss 8000
177 Livingston 8400
176 Taylor 8600
175 Hutton 8800
174 Abel 11000
8. Покажите номер отдела с наивысшей средней заработной платой и наименьший оклад работающего в нём сотрудника.
SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IN
(SELECT department_id
FROM employees
WHERE salary =
(SELECT MAX(AVG(salary))
FROM employees
GROUP BY salary))
GROUP BY department_id;
DEPARTMENT_ID MIN(SALARY)
————- ————
90 17000
9. Выведите номера, наименования и местоположения отделов, в которых не работают торговые представители (job_id = ‘SA_REP’).
SELECT department_id, department_name, manager_id, location_id
FROM departments
WHERE department_id <> ALL
(SELECT NVL(department_id, 0)
FROM employees
WHERE job_id = ‘SA_REP’);
.
(а если в отделе работает SA_REP и еще кто-то)
1.Вычисляем, какие отделы с job_id SA_REP, 2. Составляем список тех значений отделов, с которыми будем проверять равенство 3. Сравниваем номер любого департамента со всеми полученными в подзапросе значениями.
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
————- —————————— ———- ————
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
26 rows selected
Практическое занятие 7
• Создание запросов, в которых используются операторы SET.
• Альтернативные методы соединения.
1. Используя оператор SET, выведите номера отделов (department_id), в которых нет служащих с идентификатором должности (job_id) ST_CLERK.
SELECT department_id
FROM departments
MINUS
SELECT department_id
FROM employees
WHERE job_id LIKE ‘ST_CLERK’;
DEPARTMENT_ID
————-
10
20
30
40
60
70
2. Используя оператор SET, выведите идентификаторы и наименования стран, в которых не располагаются отделы компании. (учесть отделы!)
SELECT country_id «CO», country_name
FROM countries
MINUS
SELECT l.country_id, con.country_name
FROM locations l JOIN countries con
ON l.country_id = con.country_id
JOIN departments d
ON d.location_id = l.location_id;
Чтобы не забыть: 1. Нахожу локайшнс, где есть отделы 2. Вычитаю их из общего списка
21 строчка должна выводиться!
CO COUNTRY_NAME
— —————————————-
AR Argentina
AU Australia
BE Belgium
BR Brazil
CH Switzerland
21 rows selected
3. Используя оператор SET, выведите список должностей отделов 10, 50 и 20 в таком же порядке отделов. Выведите столбцы job_id и department_id.
SELECT job_id, department_id
FROM (SELECT job_id, department_id
FROM employees
WHERE department_id IN (10, 20, 50)
INTERSECT
SELECT job_id, department_id
FROM employees)
ORDER BY decode (department_id, 10,1, 20,3, 50,2);
COLUMN a_dummy NOPRINT
SELECT job_id, department_id, 1 a_dummy
FROM employees
WHERE department_id = 10
UNION
SELECT job_id, department_id, 3 a_dummy
FROM employees
WHERE department_id = 20
UNION
SELECT job_id, department_id, 2 a_dummy
FROM employees
WHERE department_id = 50
ORDER BY 3;
(будет доп вопрос про SET и подзапрос!)
JOB_ID DEPARTMENT_ID
———- ———————-
AD_ASST 10
ST_CLERK 50
SH_CLERK 50
SH_CLERK 50
SH_CLERK 50
ST_MAN 50
ST_MAN 50
ST_MAN 50
ST_MAN 50
ST_MAN 50
ST_CLERK 50
48 rows selected
4. Выведите номер сотрудника и идентификатор его должности, если его текущая должность совпадает с той, которую он уже занимал, работая в компании.
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
EMPLOYEE_ID JOB_ID
———— ———-
176 SA_REP
200 AD_ASST
5. Напишите составной запрос, который выводит следующее:
• Номера и отделы всех сотрудников из таблицы EMPLOYEES, независимо от того, относятся ли они к какому-то отделу или нет;
• Номера и наименования всех отделов из таблицы DEPARTMENTS, независимо от того, есть ли в них сотрудники или нет.
SELECT employee_id, department_id, TO_CHAR (NULL) department_name
FROM employees
UNION
SELECT TO_NUMBER (NULL), department_id, department_name
FROM departments;
EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
———— ————- ——————————
100 90
101 90
102 90
103 60
104 60
Практическое занятие 8
11 Вставка строк в таблицы
12 Обновление строк в таблице
13 Удаление строк из таблицы
14 Управление транзакциями
Вставка данных в таблицу.
1. Выполните скрипт lab8_1.sql для создания таблицы MY_EMPLOYEE, которая будет использоваться для упражнений.
2. Выведите структуру таблицы MY_EMPLOYEE для выяснения имён столбцов.
DESCRIBE my_employee
Name Null Type
———- ——— ————
ID NOT NULL NUMBER(4)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
USERID VARCHAR2(8)
SALARY NUMBER(9,2)
3. Вставьте в таблицу MY_EMPLOYEE первую строку из нижеприведённых образцов. Не указывайте столбцы в предложении INSERT.
INSERT INTO my_employee
VALUES (1, ‘Patel’, ‘Ralph’, ‘rpatel’, 895);
1 rows inserted.
ID LAST_NAME FIRST_NAME USERID SALARY
1 Patel Ralph rpatel 895
2 Dancs Betty bdancs 860
3 Biri Ben bbiri 1100
4 Newman Chad cnewman 750
5 Ropeburn Andrey Aropebur 1550
4. Вставьте в таблицу MY_EMPLOYEE вторую строку из вышеуказанных образцов. На этот раз укажите столбцы явно в предложении INSERT.
INSERT INTO my_employee(id, last_name, first_name, userid, salary)
VALUES (2, ‘Dancs’, ‘Betty’, ‘bdancs’, 860);
1 rows inserted.
5. Убедитесь в том, что данные вставлены в таблицу.
SELECT *
FROM my_employee;
ID LAST_NAME FIRST_NAME USERID SALARY
———- ————————- ————————- ——— ———-
1 Patel Ralph rpatel 895
2 Dancs Betty bdancs 860
6. Создайте скрипт-файл loademp.sql для интерактивной загрузки строк в таблицу MY_EMPLOYEE. Выдайте приглашение пользователю ввести номер, фамилию имя и оклад служащего. Соедините первую букву имени с семью первыми буквами фамилии для получения идентификатора пользователя данного служащего.
SET VERIFY OFF
INSERT INTO my_employee(id, last_name, first_name, userid, salary)
VALUES (‘&write_id’, INITCAP (‘&&write_last_name’), INITCAP (‘&&write_first_name’),
LOWER(SUBSTR (‘&write_first_name’, 1, 1) || SUBSTR (‘&write_last_name’, 1, 7)),
‘&write_salary’);
UNDEFINE write_last_name
UNDEFINE write_first_name
1 rows inserted.
1 rows inserted.
7. С помощью созданного скрипта вставьте в таблицу две следующие строки данных из приведённых образцов.
8. Убедитесь в том, что строки вставлены в таблицу.
SELECT *
FROM my_employee;
ID LAST_NAME FIRST_NAME USERID SALARY
———- ————————- ————————- ——— ———-
1 Patel Ralph rpatel 895
2 Dancs Betty bdancs 860
3 Biri Ben bbiri 1100
4 Newman Chad cnewman 750
9. Сделайте изменения постоянными.
COMMIT;
committed.
Обновление и удаление данных из таблицы MY_EMPLOYEE.
10. Смените фамилию служащего номер 3 на «Drexler».
UPDATE my_employee
SET last_name = ‘Drexler’
WHERE id = 3;
1 rows updated.
11. Сделайте оклад равным 1000 для всех служащих, имеющих оклад ниже 900.
UPDATE my_employee
SET salary = 1000
WHERE salary < 900;
3 rows updated.
12. Проверьте изменения в таблице.
SELECT *
FROM my_employee;
ID LAST_NAME FIRST_NAME USERID SALARY
———- ————————- ————————- ——— ———-
1 Patel Ralph rpatel 1000
2 Dancs Betty bdancs 1000
3 Drexler Ben bbiri 1100
4 Newman Chad cnewman 1000
13. Удалите из таблицы MY_EMPLOYEE строку для служащей Betty Dancs.
DELETE my_employee
WHERE last_name = ‘Dancs’
AND first_name = ‘Betty’;
1 rows deleted.
14. Проверьте изменения в таблице.
SELECT *
FROM my_employee;
ID LAST_NAME FIRST_NAME USERID SALARY
———- ————————- ————————- ——— ———-
1 Patel Ralph rpatel 1000
3 Drexler Ben bbiri 1100
4 Newman Chad cnewman 1000
15. Зафиксируйте все незафиксированные изменения.
COMMIT;
committed.
Управление данными при помощи транзакций
16. Вставьте в таблицу последнюю строку из образца данных путём выполнения скрипта, созданного в пункте 6.
1 rows inserted.
17. Проверьте изменения в таблице.
SELECT *
FROM my_employee;
ID LAST_NAME FIRST_NAME USERID SALARY
———- ————————- ————————- ——— ———-
1 Patel Ralph rpatel 1000
3 Drexler Ben bbiri 1100
4 Newman Chad cnewman 1000
5 Ropeburn Audrey aropebur 1550
18. Создайте точку сохранения в ходе транзакции.
SAVEPOINT A;
19. Удалите все данные из таблицы.
DELETE FROM my_employee;
4 rows deleted.
20. Убедитесь в том, что таблица пуста.
SELECT *
FROM my_employee;
no rows selected
21. Отмените последнюю операцию DELETE, не отменяя предыдущую операцию INSERT.
ROLLBACK to SAVEPOINT A;
rollback complete.
22. Убедитесь в том, что вставленная строка присутствует в таблице.
SELECT *
FROM my_employee;
ID LAST_NAME FIRST_NAME USERID SALARY
———- ————————- ————————- ——— ———-
1 Patel Ralph rpatel 1000
3 Drexler Ben bbiri 1100
4 Newman Chad cnewman 1000
5 Ropeburn Audrey aropebur 1550
23. Сделайте добавление данных постоянным.
COMMIT;
committed.
Практическое занятие 9
15 Создание новых таблиц
16 Создание новой таблицы с помощью синтаксиса CREATE TABLE AS
17 Проверка существования таблицы
18 Удаление таблиц
1. Создайте таблицу DEPT на основе приведенного ниже описания. Сохраните команду в файле lab9_1.sql, а затем выполните файл для создания таблицы. Убедитесь в том, что таблица создана.
CREATE TABLE DEPT
(id number(7),
name varchar2(25));
Имя столбца ID Name
Тип ключа
Nulls/Unique
Таблица FK
Столбец FK
Тип данных NUMBER VARCHAR2
Длина 7 25
2. Заполните таблицу DEPT данными из таблицы Departments. Включите только нужные столбцы.
INSERT INTO dept
SELECT department_id, department_name
FROM departments;
SELECT *
FROM dept;
ID NAME
———- ————————-
10 Administration
20 Marketing
30 Purchasing
40 Human Resources
50 Shipping
60 IT
70 Public Relations
80 Sales
27 rows selected
3. Создайте таблицу EMP на основе приведенного ниже описания. Сохраните команду в скрипт-файле lab9_3.sql, а затем выполните его, чтобы создать таблицу. Убедитесь в том, что таблица создана.
CREATE TABLE emp
(id NUMBER(7),
last_name VARCHAR2(25),
first_name VARCHAR2(25),
dept_id NUMBER(7));
table EMP created.
DESCRIBE emp
Name Null Type
———- —- ————
ID NUMBER(7)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPT_ID NUMBER(7)
Имя столбца ID Last_name FIRST_NAME DEPT_ID
Тип ключа
Nulls/Unique
Таблица FK
Столбец FK
Тип данных NUMBER VARCHAR2 VARCHAR2 NUMBER
Длина 7 25 25 7
4. Создайте таблицу EMPLOYEES2, основанную на структуре таблицы EMPLOYEES, включив только столбцы EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY и DEPARTMENT_ID. Имена колонок в новой таблице должны быть ID, FIRST_NAME, LAST_NAME, SALARY и DEPT_ID соответственно.
CREATE TABLE employees2 AS
SELECT employee_id id, first_name, last_name, salary, department_id dept_id
FROM employees;
table EMPLOYEES2 created.
5. Удалите таблицу EMP.
DROP TABLE emp;
table EMP dropped.
Практическое занятие 10, Часть 1
• Создание простого представления
• Создание сложного представления
• Создание представления с ограничением CHECK
• Попытки изменить данные в представлении
• Вывод определений представлений
• Удаление представлений
1. Создайте представление EMPLOYEES_VU. Включите номер служащего, фамилию служащего и номер отдела из таблицы EMPLOYEES. Смените заголовок столбца с фамилией служащего на EMPLOYEE.
CREATE VIEW employees_vu AS
SELECT employee_id, last_name employee, department_id
FROM employees;
view EMPLOYEES_VU created.
2. Выведите содержимое представления EMPLOYEE_VU.
SELECT *
FROM employees_vu;
EMPLOYEE_ID EMPLOYEE DEPARTMENT_ID
———— ————————- ————-
198 OConnell 50
199 Grant 50
200 Whalen 10
201 Hartstein 20
202 Fay 20
203 Mavris 40
107 rows selected
3. Используя своё представление EMPLOYEES_VU, создайте запрос для вывода всех фамилий и номеров отделов служащих.
SELECT employee, department_id
FROM employees_vu;
EMPLOYEE DEPARTMENT_ID
————————- ————-
OConnell 50
Grant 50
Whalen 10
Hartstein 20
107 rows selected
……
4. Создайте представление DEPT50, содержащее номер служащего и номер отдела для всех служащих отдела 50. Назовите столбцы представления EMPNO, EMPLOYEE и DEPTNO. Запретите операцию перевода служащего в другой отдел через представление.
DROP VIEW dept50;
CREATE VIEW dept50 AS
SELECT employee_id empno, last_name employee, department_id deptno
FROM employees
WHERE department_id = 50
WITH CHECK OPTION;
Какое имя будет. БУДЕТ ИМЯ ТАКОЕ: SYS_C00162842
view DEPT50 created.
5. Выведите содержимое представления DEPT50.
SELECT *
FROM dept50_1;
EMPNO EMPLOYEE DEPTNO
———- ————————- ———-
198 OConnell 50
199 Grant 50
120 Weiss 50
121 Fripp 50
44 rows selected
6. Попробуйте сменить номер отдела служащего по фамилии Matos на 80.
UPDATE dept50
SET deptno = 80
WHERE employee = ‘Matos’;
QL Error: ORA-01402: представление WITH CHECK OPTION не соответствует фразе WHERE
01402. 00000 — «view WITH CHECK OPTION where-clause violation»
*Cause:
*Action:
Дополнительное задание
7. Создайте представление SALARY_VU, включающее фамилию служащего, название отдела, оклад и категорию оклада для всех служащих. Используйте таблицы EMPLOYEES, DEPARTMENTS и JOB_GRADES. Соответственно назовите столбцы Employee, Department, Salary и Grade.
CREATE VIEW salary_vu AS
SELECT d.department_name, e.salary, j.grade_level
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;
DROP VIEW salary_vu;
CREATE VIEW salary_vu AS
SELECT d.department_name, e.salary, j.grade_level
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;
DEPARTMENT_NAME SALARY GRADE_LEVEL
—————————— ———- ————
Executive 24000 E
Executive 17000 E
107 rows selected
Практическое занятие 10, Часть 2
• Создание последовательностей
• Использование последовательностей
• Создание неуникальных индексов
1. Создайте последовательность для столбца главного ключа таблицы DEPT. Последовательность должна начинаться с 200 и иметь максимальное значение 1000. шаг приращения значений – 10. Назовите последовательность DEPT_ID_SEQ.
CREATE SEQUENCE dept_id_seq
START WITH 200
MAXVALUE 1000
INCREMENT BY 10;
sequence DEPT_ID_SEQ_1 created.
2. Создайте скрипт lab12_3.sql для вставки строки в таблицу DEPT. Обязательно используйте последовательность, созданную Вами для столбца ID. Добавьте в таблицу два отдела: Education и Administration. Выполните файл, проверьте успешное выполнение вставки.
INSERT INTO dept
VALUES (dept_id_seq.nextval, ‘Education’);
INSERT INTO dept
VALUES (dept_id_seq.nextval, ‘Administration’);
1 rows inserted.
1 rows inserted.
ID NAME
———- ————————-
10 Administration
20 Marketing
30 Purchasing
40 Human Resources
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
400 Education
410 Administration
29 rows selected
3. Создайте в таблице EMP неуникальный индекс для столбца таблицы DEPT_ID, имеющего ограничение FOREIGN KEY.
УДАЛИЛИ Ж ТАБЛИЦУ!
CREATE TABLE emp
(id NUMBER(7),
last_name VARCHAR2(25),
first_name VARCHAR2(25),
dept_id NUMBER(7)
CONSTRAINT emp_dept_id_fk REFERENCES departments (department_id));
CREATE INDEX dept_name_index ON emp (dept_id);
index DEPT_NAME_INDEX created.
DROP TABLE emp;
4. Создайте синоним для таблицы EMPLOYEES. Назовите его EMP.
CREATE SYNONYM emp FOR employees;
Практическое занятие 11
• Создайте скрипт, который позволит получать информацию об именах колонок, типах данных, допустимых объемах ячеек, а также о допустимости пустых ячеек. Пользователь должен иметь возможность вводить имя таблицы. Задайте псевдонимы для столбцов DATA_PECISION и DATA_SCALE. Сохраните скрипт в файле.
SELECT column_name, data_type, data_length, data_precision «PRECISION», data_scale «SCALE», nullable
FROM user_tab_columns
WHERE table_name = UPPER (‘&table_name’);
COLUMN_NAME DATA_TYPE DATA_LENGTH PRECISION SCALE NULLABLE
—————————— ———————————————————————————————————- ———— ———- ———- ———
DEPARTMENT_ID NUMBER 22 4 0 N
DEPARTMENT_NAME VARCHAR2 30 N
MANAGER_ID NUMBER 22 6 0 Y
LOCATION_ID NUMBER 22 4 0 Y
Например, если пользователь ввел DEPARTMENTS, он должен получить следующий результат:
COLUMN_NAME DATA_TYPE DATA_LENGTH PRECISION SCALE N
DEPARTMENT_ID NUMBER 22 4 0 N
DEPARTMENT_NAME VARCHAR2 30 N
MANAGER_ID NUMBER 22 6 0 Y
LOCATION_ID NUMBER 22 4 0 Y
• Создайте скрипт для получения информации об именах колонок, именах ограничений, условиях поиска и статусе таблицы. Для получения необходимой информации Вы должны соединить таблицы USER_CONSTRAINTS и USER_CONS_COLUMNS. Пользователь должен иметь возможность вводить имя таблицы. Сохраните скрипт в файле.
SELECT ucol.column_name, ucol.constraint_name,
uconstr.constraint_type «C», uconstr.search_condition, uconstr.status
FROM user_cons_columns ucol
JOIN user_constraints uconstr
ON ucol.constraint_name = uconstr.constraint_name
AND ucol.table_name = UPPER (‘&write_table_name’);
CONSTRAINT_NAME C SEARCH_CONDITION STATUS
———————————————————————————————————————————- —————————— — ——————————————————————————— ———
SYS_C00140066 C «DEPARTMENT_NAME» IS NOT NULL ENABLED
DEPT_ID_PK P ENABLED
DEPT_MGR_FK R ENABLED
DEPT_LOC_FK R ENABLED
Например, если пользователь ввел DEPARTMENTS, он должен получить следующий результат:
COLUMN_NAME CONSTRAINT_NAME C SEARCH_CONDITION STATUS
DEPARTMENT_ID DEPT_ID_PK P ENABLED
LOCATION_ID DEPT_LOC_FK R ENABLED
MANAGER_ID DEPT_MGR_FK R ENABLED
DEPARTMENT_NAME DEPT_NAME_NN C «DEPARTMENT_NAME» IS NOT NULL ENABLED
• Добавьте комментарий к таблице DEPARTMENTS. Затем обратитесь с запросом к представлению USER_TAB_COMMENTS, для проверки факта создания комментария.
COMMENT ON TABLE departments IS ‘Bla-bla-bla’;
SELECT comments
FROM user_tab_comments
WHERE table_name = ‘DEPARTMENTS’;
———————————————————————————————————————-
COMMENTS
Company department information including name, code and location
• Найдите имена всех синонимов в вашей схеме
SELECT *
FROM user_synonyms;
SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
————— —————— ————————————————————————
EMP ANNLUS EMPLOYEES
SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
EMP HR EMPLOYEES
• Из представления словаря данных USER_VIEWS выберите столбцы VIEW_NAME и TEXT.
Примечание: для просмотра содержимого столбца типа LONG выполните команду SET LONG n, где n – число выводимых запросом символов из столбца типа LONG.
SET LONG 50
SELECT view_name, text
FROM user_views;
VIEW_NAME TEXT
—————————— —————————————————
СПЕЦ SELECT Врач.ФИО_Врача, Список_спец.Специальность,
ПРЕДСТАВЛЕНИЕ SELECT Автор.ФИО, Книга.Назв_е, Изд_во.Назв_изд
БОЛЬН_УЧАСТК SELECT Пациент.ФИО, Врач.ФИО_Врача, Расписание_на_
SALARY_VU SELECT e.last_name, d.department_name, e.salary, j
EMPLOYEES_VU SELECT employee_id, last_name employee, department
DEPT50 SELECT employee_id empno, last_name employee, depa
6 rows selected
5. Найдите имена всех ваших последовательностей. Напишите запрос для получения следующей информации о ваших последовательностях: имя последовательности, максимальное значение, величина приращения, последнее значение. Сохраните скрипт в файле. Выполните скрипт.
SELECT sequence_name, max_value, increment_by, last_number
FROM user_sequences;
SEQUENCE_NAME MAX_VALUE INCREMENT_BY LAST_NUMBER
—————————— ———- ———— ————
DEPT_ID_SEQ 1000 10 600
SEQUENCE_NAME MAX_VALUE INCREMENT_BY LAST_NUMBER
DEPARTMENTS_SEQ 9990 10 280
EMPLOYEES_SEQ 1,0000E+27 1 207
LOCATIONS_SEQ 9900 100 3300
ОГЛАВЛЕНИЕ
Практическое занятие 1 1
Практическое занятие 2 5
Практическое занятие 3, часть 1 8
Практическое занятие 3, часть 2 10
Практическое занятие 4 13
Практическое занятие 5 15
Практическое занятие 6 19
Практическое занятие 7 22
Практическое занятие 8 23
Практическое занятие 9 25
Практическое занятие 10, Часть 1 26
Практическое занятие 10, Часть 2 27
Практическое занятие 11 28

Источник

Автор: • Май 28, 2019 • Анализ учебного пособия • 124,246 Слов (497 Страниц) • 493 Просмотры

Страница 1 из 497

ПРАКТИЧЕСКИЕ ЗАНЯТИЯ ПО КУРСУ

ВВЕДЕНИЕ В ORACLE 10g: SQL (Часть 1)

Без ORDER BY как (в каком порядке) выводятся значения в таблице? (см. книгу)

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

Выборка данных из разных таблиц
Описание структуры таблиц
Выполнение арифметических вычислений и задание имён столбцов
Использование iSQL*Plus

Будет ли успешна эта команда SELECT?
SELECT last_name, job_id, salary As sal

FROM employees;

Да/Нет

Будет ли успешна эта команда SELECT?
SELECT *
FROM job_grades;Да/Нет
Команда SELECT содержит 4 ошибки. Укажите их.
SELECT employee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;

Верно:

SELECT employee_id, last_name, (salary*12) «ANNUAL SALARY»

FROM employees;

EMPLOYEE_ID LAST_NAME ANNUAL SALARY

———————- ————————- ———————-

198 OConnell 31200

199 Grant 31200

200 Whalen 52800

201 Hartstein 156000

Покажите структуру таблицы DEPARTMENTS. Выберите данные из неё.

DESCRIBE departments

SELECT *

FROM departments;

Name Null Type

—————————— ——— ———————————————————————————————————————————————————————————————

DEPARTMENT_ID NOT NULL NUMBER(4)

DEPARTMENT_NAME NOT NULL VARCHAR2(30)

MANAGER_ID NUMBER(6)

LOCATION_ID NUMBER(4)

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

———————- —————————— ———————- ———————-

10 Administration 200 1700

20 Marketing 201 1800

30 Purchasing 114 1700

40 Human Resources 203 2400

50 Shipping 121 1500

60 IT 103 1400

Name	Null?	Type
DEPARTMENT_ID	NOT NULL	NUMBER(4)
DEPARTMENT_NAME	NOT NULL	VARCHAR(2)
MANAGER_ID		NUMBER(6)
LOCATION_ID		NUMBER(4)

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
10	Administration	200	1700
20	Marketing	201	1800
50	Shipping	124	1500
60	IT	103	1400
80	Sales	149	2500
90	Executive	100	1700
110	Accounting	205	1700
190	Contracting		1700

8 row selected

…

Доступно только на Essays.club

Источник

Скачай Конспект решений курса SQL Базы данных ч.1 и еще Конспекты лекций в формате PDF Базы данных только на Docsity! ПРАКТИЧЕСКИЕ ЗАНЯТИЯ ПО КУРСУ ПРОГРАММИРОВАНИЕ БАЗ ДАННЫХ: SQL (Часть 1) ОГЛАВЛЕНИЕ Практическое занятие 1. Выбор данных с использованием команды SELECT……………………………….2 Практическое занятие 2. Ограничение и сортировка выходных данных……………………………………..10 Практическое занятие 3. Использование однострочных функций (часть 1)……………………………….18 Практическое занятие 3. Использование однострочных функций (часть 2)……………………………….22 Практическое занятие 4. Агрегирование данных с помощью групповых функций………………………..29 Практическое занятие 5. Выборка данных из нескольких таблиц……………………………………………….34 Практическое занятие 6. Использование некоррелированных подзапросов………………………………….45 SELECT DISTINCT……………………………………………………………………………………………………………………..56 NVL(TO_CHAR(employee_id), ‘ ‘) AS employee_id,……………………………………………………………………….56 NVL(TO_CHAR(department_id), ‘ ‘) AS department_id,………………………………………………………………….56 ‘ ‘ AS department_name……………………………………………………………………………………………………………….56 FROM employees………………………………………………………………………………………………………………………..56 UNION ALL………………………………………………………………………………………………………………………………..56 SELECT DISTINCT…………………………………………………………………………………………………………………….56 ‘ ‘,……………………………………………………………………………………………………………………………………………..56 TO_CHAR(department_id),………………………………………………………………………………………………………….56 department_name……………………………………………………………………………………………………………………….56 FROM departments;……………………………………………………………………………………………………………………56 Практическое занятие 9. Использование команд DDL для создания и изменения таблиц…………….61 Практическое занятие 10. Создание других объектов схем (часть 1)………………………………………..63 Практическое занятие 10. Создание других объектов схем (часть 2)…………………………………………67 Практическое занятие 11. Управление объектами с помощью представлений словаря данных…..69 1 Практическое занятие 1. Выбор данных с использованием команды SELECT  Выборка данных из разных таблиц  Описание структуры таблиц  Выполнение арифметических вычислений и задание имён столбцов  Использование оператора конкатенации символьных значений. 1. Будет ли успешна эта команда SELECT? [ДА] SELECT last_name, job_id, salary As sal FROM employees; ` Да/Нет 2. Будет ли успешна эта команда SELECT? [ДА] SELECT * FROM job_grades; Да/Нет 3. Команда SELECT содержит 4 ошибки. Укажите их. SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROM employees; Нет запятой после last_name, sal вместо salary, x вместо *, пропущены кавычки в псевдониме ANNUAL SALARY. Правильный вариант выглядит следующим образом: SELECT employee_id, last_name, salary * 12 “ANNUAL SALARY” FROM employees; 4. Покажите структуру таблицы DEPARTMENTS. Выберите данные из неё. Name Null? Type DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR(2) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID 10 Administration 200 1700 20 Marketing 201 1800 50 Shipping 124 1500 60 IT 103 1400 80 Sales 149 2500 90 Executive 100 1700 110 Accounting 205 1700 190 Contracting 1700 8 row selected 2 ЕМРТОЗЕЕ ТО МОТ МОТ, МОМВЕВ (6)

ЕТАЗТ МАМЕ УАИСНАР? (20)

ТАЗТ_ МАМЕ НОТ МОТ, УАВСНАЕ2 {25}

ЕМАТЬ НОТ МЛ. УАВСНАЕ2 (25)

РНОНЕ_МОМВЕВ УАВСНАЮ2 (20)

НТВЕ_РАТЕ НОТ МОЛ, РАТЕ

вто МОТ НЫ. УАВСНАВ2 {10}

ЗАТАВУ ВПМВЕВ (8,2)

СОММТ55ТОН_РСТ МОМВЕВ (2,2)

МАНАСЕВ_ТР ЗМВЕВ (5)

РЕРАВТМЕНТ_ТО МОМВЕВ (4)

ЕМРТОТЕЕ ТО ГАЗТ_МАМЕ зов то Этаетрасе
100 Ета АР РВЕЗ — 17-Л71-03
101 Коспваг АО УР 21-ЗЕР-05
102 ре Наап АО УР 13-ТАМ-01
103 Нипола ТТ_РРОб — 03-ТАН-06
104 Етпзе ТТ_РВОб 21-НМАУ-07
105 АизЕза ТТ_2Р806 — 25-Л-05
106 Ратаьа11а ТТ_Р80б — 05-РЕВ-06
107 Тогерех ТТ_РРОб — 07-РЕВ-07
108 бхеельего ЕТ МСК 17-106-02
109 Еаулев ЕТ АССОПМТ 16-А06-02
110 Сев ЕТ АССОПМТ 28-5ЕР-05

107 гонз зе1естеа.

6. Проверьте выполнение запроса из файла 1аБ1_5.54, чтобы убедиться, что он выполняется
правильно.
7. Составьте запрос для вывода неповторяющихся идентификаторов должностей из таблицы EMPLOYEES. JOB_ID AC_ACCOUNT AC_MGR AD_ASST AD_PRES AD_VP IT_PROG MK_MAN MK_REP SA_MAN SA_REP ST_CLERK ST_MAN SELECT DISTINCT job_id FROM employees; 8. Скопируйте команды из файла lab1_5.sql в окно редактирования SQL*Developer. Назовите столбцы Emp #, Employee, Job, Hire Date. Выполните запрос ещё раз. Emp # Employee Job Hire Date 100 King AD_PRES 17-JUN-87 101 Kochhar AD_VP 21-SEP-89 102 De Haan AD_VP 13-JAN-93 103 Hunold IT_PROG 03-JAN-90 104 Ernst IT_PROG 21-MAY-91 107 Lorentz IT_PROG 07-FEB-99 6 124 Mourgos ST_MAN 16-NOV-99 141 Rajs ST_CLERK 17-OCT-95 142 Davies ST_CLERK 90-JAN-97 143 Matos ST_CLERK 15-MAR-98 144 Vargas ST_CLERK 09-JUL-98 SELECT employee_id AS «Emp #», last_name AS «Employee», job_id AS «Job», TO_CHAR(hire_date, ‘DD-MON-YY’, ‘NLS_Date_Language=English’) AS «Hire Date» FROM employees; 9. Выведите на экран фамилию сотрудника, соединённую с идентификатором его должности через запятую и пробел. Назовите новый столбец Employee and Title. Employee and Title King, AD_PRES Kochhar, AD_VP De Haan, AD_VP Hunold, IT_PROG Ernst, IT_PROG Lorentz, IT_PROG Mourgos, ST_MAN Rajs, ST_CLERK Davies, ST_CLERK Matos, ST_CLERK SELECT last_name ||’, ‘||job_id as «Employee and Title» FROM employees; 7 Практическое занятие 2. Ограничение и сортировка выходных данных  Выборка данных и изменение последовательности вывода строк  Ограничение количества возвращаемых строк с помощью предложения WHERE  Сортировка строк с помощью предложения ORDER BY  Использование при составлении запросов подстановочных переменных 1. Создайте запрос для вывода фамилии и заработной платы служащих, зарабатывающих более $12000. Сохраните команду SQL в файле lab2_1.sql. Выполните запрос. LAST_NAME SALARY King 24000 Kochhar 17000 De Haan 17000 Hartstein 13000 SELECT last_name, salary FROM employees WHERE salary > 12000; 2. Создайте запрос для вывода фамилии и номера отдела служащего под номером 176. LAST_NAME DEPARTMENT_ID Taylor 80 SELECT last_name, department_id FROM employees WHERE employee_id = 176; 3. Измените файл lab2_1.sql и выведите фамилии и оклады всех служащих, чей оклад не входит в диапазон от $5000 до $12000. Сохраните команду SQL в файле lab2_3.sql. LAST_NAME SALARY King 24000 Kochhar 17000 10 De Haan 17000 Lorentz 4200 Rajs 3500 Davies 3100 Matos 2600 Vargas 2500 Whalen 4400 Hartstein 13000 SELECT last_name, salary FROM employees WHERE salary < 5000 OR salary > 12000; 4. Выведите фамилию, идентификатор должности и дату начала работы всех служащих, нанятых в период с 20 февраля 2008 по 1 мая 2008 г. Отсортируйте данные в порядке возрастания даты найма. LAST_NAME JOB_ID HIRE_DATE Matos ST_CLERK 15-MAR-98 Taylor SA_REP 24-MAR-98 SELECT last_name, job_id, TO_CHAR(hire_date, ‘DD-MON-YY’, ‘NLS_Date_Language=English’) AS «HIRE_DATE» FROM employees WHERE hire_date > ‘20.02.2008’ AND hire_date < ‘1.05.2008’ ORDER BY hire_date; 11 5. Выведите фамилию и номер отдела всех служащих из отделов 20 и 50. Отсортируйте данные по фамилиям в алфавитном порядке. 7 row selected SELECT last_name, department_id FROM employees WHERE department_id = 20 OR department_id = 50 ORDER BY last_name; 6. Измените файл lab2_3.sql для вывода фамилий и окладов служащих отделов 20 и 50, зарабатывающих от $5000 до $12000. Назовите столбцы Employee и Monthly Salary, соответственно. Вновь сохраните команду SQL в файле lab2_6.sql. Выполните запрос. Employee Monthly Salary Mourgos 5800 Fay 6000 SELECT last_name AS Employee, salary AS «Monthly Salary» FROM employees WHERE salary > 5000 AND salary < 12000 AND (department_id = 20 OR department_id = 50); 7. Выведите фамилию и дату найма всех служащих, нанятых в 2004 г. LAST_NAME HIRE_DATE 12 12. Выведите все фамилии служащих, в которых третья буква – а. LAST_NAME Grant Whalen SELECT last_name FROM employees WHERE SUBSTR(last_name, 3, 1) = ‘a’; 13. Выведите фамилии всех служащих, в которых есть буквы «а» и «е». LAST_NAME De Haan Davies Whalen Hartstein SELECT last_name FROM employees WHERE last_name LIKE ‘%a%’ AND last_name LIKE ‘%e%’; 15 14. Запросите фамилии, должности и оклады всех служащих, работающих торговыми представителями (SA_REP) или клерками на складе (ST_CLERK) и с окладом, не равным $2500, $3500 и $7000. LAST_NAME JOB_ID SALARY Davies ST_CLERK 3100 Matos ST_CLERK 2600 Abel SA_REP 11000 Taylor SA_REP 8600 SELECT last_name, job_id, salary FROM employees WHERE (job_id = ‘SA_REP’ OR job_id = ‘ST_CLERK’) AND (salary != ANY(2500, 3500, 7000)); 15. Измените lab2_6.sql так, чтобы получить фамилии оклады и комиссионные всех служащих, у которых сумма комиссионных составляет 20%. Выполните запрос ещё раз. Сохраните запрос в файле lab2_15.sql. Employee Monthly Salary COMMISSION_PCT 16 Zlotkey 10500 .2 Taylor 8600 .2 SELECT last_name AS Employee, salary AS «Monthly Salary», commission_pct FROM employees WHERE commission_pct = 0.2; 17 6. Перепишите запрос таким образом, чтобы пользователь мог при выполнении запроса вводить букву, с которой начинается фамилия сотрудника. При вводе пользователем нескольких букв выбор фамилий должен осуществляться по первой букве. SELECT UPPER(SUBSTR(last_name, 1, 1))||LOWER(SUBSTR(last_name, 2)) AS «Name», LENGTH(last_name) AS «Length» FROM employees WHERE UPPER(SUBSTR(last_name, 1, 1)) = UPPER(SUBSTR(‘&init’, 1, 1)); 7. Создайте запрос для определения срока работы сотрудников в месяцах до текущей даты. Назовите столбец MONTHS_WORKED. Отсортируйте результат в порядке возрастания числа проработанных месяцев. Округлите результат до целого числа. LAST_NAME MONTH_WORKED Zlotkey 13 Mourgos 16 Grant 22 Lorentz 25 Vargas 32 Taylor 36 Matos 36 Fay 43 …………….. 20 rows selected SELECT last_name, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date)) AS “MONTH WORKED” FROM employees ORDER BY 2; Практическое занятие 3. Использование однострочных функций (часть 2)  Составление запросов, требующих использования числовых, символьных функций и функций для работы с датами.  Составление запросов, нечувствительных к регистру символов.  Использование функций NVL, LPAD, RPAD.  Составление запросов с функцией DECODE и выражением CASE. 1. Получите по каждому служащему отчёт в следующем виде: <фамилия> earns <оклад с указанием символа валюты, дробная часть должна содержать два разряда и быть отделена от целой части точкой, группы разрядов целой части должны быть разделены запятыми> monthly, but wants <утроенный оклад в том же формате>. Между словами должно быть ровно по одному пробелу. Назовите столбец Dream Salaries. Dream Salaries King earns $24,000.00 monthly, but wants $72,000.00 Kochhar earns $17,000.00 monthly, but wants $51,000.00 De Haan earns $17,000.00 monthly, but wants $51,000.00 Hunold earns $9,000.00 monthly, but wants $27,000.00 Ernst earns $6,000.00 monthly, but wants $18,000.00 Lorentz earns $4,200.00 monthly, but wants $12,600.00 Mourgos earns $5,800.00 monthly, but wants $17,400.00 Rajs earns $3,500.00 monthly, but wants $10,500.00 Davies earns $3,100.00 monthly, but wants $9,300.00 …………… 20 rows selected SELECT last_name || ‘ earns ‘ || TRIM(LEADING ‘ ‘ FROM TO_CHAR(salary,’$99,999.00’)) || ‘ monthly, but wants ‘ || TRIM(LEADING ‘ ‘ FROM TO_CHAR(salary * 3,’$99,999.00′)) AS «Dream Salaries» FROM employees; 4. По каждому служащему выведите фамилию, дату найма и день недели, когда он был нанят на работу. Назовите последний столбец DAY. Отсортируйте результаты по дням недели. LAST_NAME HIRE_DATE DAY Grant 24-MAY-99 MONDAY Ernst 21-MAY-91 TUESDAY Mourgos 16-NOV-99 TUESDAY Taylor 24-MAR-98 TUESDAY Rajs 17-OCT-95 TUESDAY Gietz 07-JUN-94 TUESDAY Higgins 07-JUN-94 TUESDAY King 17-JUN-87 WEDNESDAY De Haan 13-JAN-93 WEDNESDAY ……………… 20 rows selected SELECT last_name, TO_CHAR(hire_date,’DD-MON-YY’,’NLS_Date_Language=English’) AS HIRE_DATE, TO_CHAR(hire_date,’DAY’,’NLS_Date_Language=English’) AS DAY FROM employees ORDER BY TO_CHAR(employees.hire_date,’D’); 5. Напишите запрос для вывода фамилии и суммы комиссионных каждого служащего. Если служащий не зарабатывает комиссионных, укажите в столбце «No Commission». Назовите столбец COMM. LAST_NAME COMM King No Commission Kochhar No Commission De Haan No Commission Hunold No Commission Ernst No Commission Lorentz No Commission Mourgos No Commission Rajs No Commission Davies No Commission Matos No Commission Vargas No Commission Zlotkey .2 Abel .3 ……………. 20 rows selected SELECT last_name, NVL(TO_CHAR(commission_pct, ‘.9′),’No Commission’) AS COMM FROM employees; 6. Напишите запрос для отображения фамилий сотрудников и их заработной платы в виде гистограммы, состоящей из звёздочек. Каждая звёздочка означает 1000$. Строки должны быть отсортированы по заработной плате в убывающем порядке. Результат должен быть выведен одним столбцом, озаглавленным как EMPLOYEES AND THEIR SALARIES. EMPLOYEES AND THEIR SALARIES King************************ Kochhar***************** De Haan***************** Hartstein************* Higgins************ Abel*********** …………………. 20 rows selected 7. Используя функцию DECODE, напишите запрос для отображения должности сотрудника и её разряда (grade). Разряд каждого типа должности JOB_ID приведён в таблице. Должность Разряд AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E Другая 0 JOB_ID G AD_PRES A AD_VP 0 AD_VP 0 IT_PROG C IT_PROG C IT_PROG C ST_MAN B ST_CLERK E ………………….. 20 rows selected SELECT DISTINCT job_id, DECODE(job_id, ‘AD_PRES’, ‘A’, ‘ST_MAN’, ‘B’, ‘IT_PROG’, ‘C’, ‘SA_REP’, ‘D’, ‘ST_CLERK’, ‘E’, ‘0’) AS G FROM employees; ……………. 3. Напишите запрос для вывода должности и количества служащих, занимающих каждую должность. JOB_ID CNT AD_ACCOUNT 1 AC_MGR 1 AD_ASST 1 AD_PRES 1 AD_VP 2 IT_PROG 3 MK_MAN 1 MK_REP 1 SA_MAN 1 SA_REP 3 ST_CLERK 4 ST_MAN 1 SELECT job_id, COUNT(employee_id) AS CNT FROM employees GROUP BY job_id; 4. Получите количество служащих, имеющих подчинённых, без их перечисления. Назовите столбец Number of Managers. Используйте столбец MANAGER_ID для определения числа менеджеров. Number of Managers 8 SELECT COUNT(DISTINCT manager_id) AS «Number of Managers» FROM employees; 5. Напишите запрос для вывода разности между самым высоким и самым низким окладами. Назовите столбец DIFFERENCE DIFFERENCE 21500 SELECT MAX(salary) — MIN(salary) AS DIFFERENCE FROM employees; 6. Напишите запрос для вывода номера каждого менеджера, имеющего подчинённых, и заработную плату самого низкооплачиваемого из его подчинённых. Исключите сотрудников, для которых неизвестны их менеджеры. Исключите все группы, где минимальный оклад составляет менее $6000. Отсортируйте выходные строки в порядке убывания минимального оклада. MANAGER_ID MIN_SALARY 102 9000 205 8300 149 7000 SELECT DISTINCT manager_id, MIN(salary) AS MIN_SALARY FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) >= 6000 ORDER BY MIN_SALARY DESC; 7. Напишите запрос для вывода общего количества служащих и количества служащих, нанятых в 2005, 2006, 2007 и 2008 годах. Дайте соответствующие заголовки столбцам. TOTAL 1995 1996 1997 1998 20 1 2 2 3 SELECT COUNT(employee_id) AS TOTAL, COUNT(DECODE(TO_CHAR(hire_date,’YYYY’), ‘2005’, 1, null)) AS «2005», COUNT(DECODE(TO_CHAR(hire_date,’YYYY’), ‘2006’, 1, null)) AS «2006», COUNT(DECODE(TO_CHAR(hire_date,’YYYY’), ‘2007’, 1, null)) AS «2007», COUNT(DECODE(TO_CHAR(hire_date,’YYYY’), ‘2008’, 1, null)) AS «2008» FROM employees; 8. Напишите матричный запрос для вывода всех должностей и суммы заработной платы служащих, работающих в этой должности в отделах 20, 50, 80 и 90. Последний столбец должен содержать сумму заработной платы служащих этих отделов, занимающих каждую конкретную должность. Дайте столбцам соответствующие заголовки. Job Dept20 Dept50 Dept80 Dept90 Total AC_ACCOUNT 8300 8300 AC_MGR 7000 5000 12000 AD_ASST 4400 4400 AD_PRES 24000 24000 AD_VP 34000 34000 IT_PROG 14000 5200 19200 SELECT DISTINCT job_id AS JOB, NVL(TO_CHAR(SUM(DECODE( department_id, 20, salary, null))), ‘ ‘) AS «Dept20», 2. Напишите запрос для вывода фамилии, номера отдела и названия отдела для всех служащих. 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 Задачу решить в синтаксисе ANSI SQL-99 и в специфической для Oracle форме записи. ANSI: SELECT e.last_name, department_id, d.department_name FROM employees e LEFT JOIN departments d USING(department_id); ORACLE: SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+); 3. Выведите список всех должностей в отделе 80 (должности в списке не должны повторяться) и местоположение отдела. JOB_ID LOCATION_ID SA_MAN 2500 SA_REP 2500 SELECT DISTINCT e.job_id, d.location_id FROM employees e LEFT JOIN departments d USING(department_id) WHERE department_id=80; 4. Выведите фамилии всех служащих, содержащих букву «а» (в строчном регистре), с названиями отделов. Сохраните свою команду SQL в файле lab5_4.sql. 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. Задачу решить в синтаксисе ANSI SQL-99 и в специфической для Oracle форме записи. ANSI: SELECT e.last_name, d.department_name FROM employees e LEFT JOIN departments d USING(department_id) WHERE e.last_name LIKE ‘%a%’; Oracle: SELECT e.last_name, d.department_name FROM employees e, departments d WHERE (e.last_name LIKE ‘%a%’) AND (e.department_id = d.department_id(+)); 5. Напишите запрос для вывода фамилии, должности, номера отдела и названия отдела всех служащих, работающих в городе Toronto. LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME Hartstein MK_MAN 20 Marketing Fay MK_REP 20 Marketing SELECT e.last_name, e.job_id, department_id, d.department_name FROM employees e JOIN departments d USING(department_id) JOIN locations l USING (location_id) WHERE INITCAP(l.city) = ‘Toronto’; 6. Выведите фамилии и номера всех служащих вместе с фамилиями и номерами их менеджеров. Назовите столбцы Employee, Emp#, Manager и Mgr#. Сохраните свою команду SQL в файле lab5_6.sql. Employee Emp# Manager Mgr# Kochhar 101 King 100 De Haan 102 King 100 HIGHEST_SAL NUMBER DESCRIBE JOB_GRADES; 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 SELECT last_name, job_id, department_name, salary, grade_level AS «GRA» FROM employees e JOIN departments d USING(department_id) JOIN job_grades jg ON(e.salary <= jg.highest_sal AND e.salary >= jg.lowest_sal); 10. Создайте запрос для вывода фамилий и дат найма всех служащих, нанятых после Davies. 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 SELECT e.last_name, TO_CHAR(e.hire_date, ‘DD-MON-YY’, ‘NLS_Date_Language=English’) FROM employees e JOIN employees davies ON(e.hire_date > davies.hire_date AND davies.last_name = ‘Davies’); 11. По всем служащим, нанятым раньше своих менеджеров, выведите фамилии и даты найма самих служащих, а также фамилии и даты найма их менеджеров. Назовите столбцы Employee, Emp Hired, Manager и Manager Hired. 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 e.last_name AS «Employee», TO_CHAR(e.hire_date, ‘DD-MON-YY’, ‘NLS_Date_Language=English’) AS «Emp Hired», m.last_name AS «Manager», TO_CHAR(m.hire_date, ‘DD-MON-YY’, ‘NLS_Date_Language=English’) AS «Mgr Hired» FROM employees e JOIN employees m ON(e.manager_id = m.employee_id AND e.hire_date < m.hire_date); 12. Выведите номера, наименования и местоположение всех отделов, а также количество работающих в них сотрудников. Обеспечьте вывод отделов, в которых нет сотрудников. DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID COUNT 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 Задачу решить в синтаксисе ANSI SQL-99 и в специфической для Oracle форме записи. ANSI: SELECT department_id, department_name, location_id, COUNT(employee_id) AS «COUNT» FROM departments LEFT JOIN employees USING (department_id) GROUP BY department_id, department_name, location_id; Oracle: SELECT d.department_id, department_name, location_id, COUNT(e.employee_id) AS «COUNT» FROM departments d, employees e WHERE d.department_id = e.department_id(+) GROUP BY d.department_id, department_name, location_id; Практическое занятие 6. Использование некоррелированных подзапросов  Создание некоррелированных подзапросов для выборки данных по неизвестным критериям  Использование некоррелированных подзапросов для выявления значений, существующих в одном наборе данных и отсутствующих в другом 1. Создайте запрос для вывода фамилии и даты найма каждого служащего, работающего в одном отделе с Zlotkey. Исключите Zlotkey из выходных данных. SELECT last_name, TO_CHAR(hire_date, ‘DD-MON-YY’, ‘NLS_Date_Language=English’) AS HIRE_DATE FROM employees WHERE last_name != ‘Zlotkey’ AND department_id IN ( SELECT department_id FROM employees WHERE last_name = ‘Zlotkey’ ); 2. Создайте запрос для вывода номеров и фамилий всех служащих, оклад которых выше среднего. Отсортируйте данные в порядке увеличения окладов. SELECT employee_id, last_name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees ) ORDER BY salary; 3. Создайте запрос для вывода номеров и фамилий всех служащих, работающих в одном отделе с любым служащим, фамилия которого содержит букву «u». Сохраните свой запрос в тестовом файле lab6_3.sql. Выполните запрос. SELECT employee_id, last_name FROM employees WHERE department_id IN ( SELECT department_id FROM ( SELECT department_id FROM employees WHERE last_name LIKE ‘%u%’ ) d JOIN employees e USING (department_id) GROUP BY department_id HAVING COUNT(e.employee_id) > 1 ); 4. Создайте запрос для вывода фамилии, номера отдела и должности каждого служащего, идентификатор местоположения отдела которого равен 1700. SELECT last_name, department_id, job_id FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id = 1700 ); 120 Ме1 33 8000
121 Ег1рр 8200
123 Уо1 пап 5500
125 Мауег 3200
126 №1111 пел: 2700
127 Гапагу 2400
128 Магк1е 2200
129 813305 3300
130 АсЕ1пзоп 2800
131 Маг1ом 2500
132 01301 2100

8. Покажите номер отдела с наивысшей средней заработной платой и наименьший оклад
работающего в нём сотрудника.

| РЕРАВТМЕНТ 10 [ МИМАГАВУ)
[ 90 || 17000

SELECT department_id, MIN(SALARY) FROM employees WHERE department_id IN ( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)=( SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id ) ) GROUP BY department_id; 9. Выведите номера, наименования и местоположения отделов, в которых не работают торговые представители (job_id = ‘SA_REP’). ). SELECT department_id, department_name, manager_id, location_id FROM departments WHERE manager_id NOT IN ( SELECT employee_id FROM employees WHERE job_id = ‘SA_REP’ ); Практическое занятие 7. Использование операторов над множествами  Создание запросов с использованием операторов UNION, MINUS, INTERSECT.  Управление порядком вывода строк. 1. Используя оператор SET, выведите номера отделов (department_id), в которых нет служащих с идентификатором должности (job_id) ST_CLERK. SELECT department_id FROM departments MINUS SELECT department_id FROM employees WHERE job_id = ‘ST_CLERK’; 2. Используя оператор SET, выведите идентификаторы и наименования стран, в которых не располагаются отделы компании. SELECT country_id AS «CO», country_name FROM countries MINUS SELECT country_id AS «CO», c.country_name FROM countries c JOIN locations l USING(country_id) JOIN departments USING(location_id); SELECT job_id, department_id FROM employees INTERSECT SELECT job_id, department_id FROM employees WHERE department_id = 20 ); 4. Выведите номер сотрудника и идентификатор его должности, если его текущая должность совпадает с той, которую он уже занимал, работая в компании. SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history; 5. Напишите составной запрос, который выводит следующее:  Номера и отделы всех сотрудников из таблицы EMPLOYEES, независимо от того, относятся ли они к какому-то отделу или нет;  Номера и наименования всех отделов из таблицы DEPARTMENTS, независимо от того, есть ли в них сотрудники или нет. EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME 100 90 101 90 102 90 103 60 104 60 205 110 206 110 10 Administration 20 Marketing 30 Purchasing 40 Human Resources 50 Shipping SELECT DISTINCT NVL(TO_CHAR(employee_id), ‘ ‘) AS employee_id, NVL(TO_CHAR(department_id), ‘ ‘) AS department_id, ‘ ‘ AS department_name FROM employees UNION ALL SELECT DISTINCT ‘ ‘, TO_CHAR(department_id), department_name FROM departments; Практическое занятие 8. Манипулирование данными  Вставка строк в таблицы  Обновление строк в таблице  Удаление строк из таблицы  Управление транзакциями Вставка данных в таблицу 1. Выполните скрипт lab8_1.sql для создания таблицы MY_EMPLOYEE, которая будет использоваться для упражнений. CREATE TABLE my_employee ( ID NUMBER(4) CONSTRAINT MY_EMPLOYEE_ID_NN NOT NULL, LAST_NAME VARCHAR2(25), FIRST_NAME VARCHAR2(25), USERID VARCHAR2(8), SALARY NUMBER(9,2) ); 2. Выведите структуру таблицы MY_EMPLOYEE для выяснения имён столбцов. 3. Вставьте в таблицу MY_EMPLOYEE первую строку из нижеприведённых образцов. Не указывайте столбцы в предложении INSERT. ID LAST_NAME FIRST_NAME USERID SALARY 1 Patel Ralph rpatel 895 2 Dancs Betty bdancs 860 3 Biri Ben bbiri 1100 4 Newman Chad cnewman 750 5 Ropeburn Andrey Aropebur 1550 INSERT INTO MY_EMPLOYEE VALUES(1, ‘Patel’, ‘Ralph’, ‘rpatel’, 895); 4. Вставьте в таблицу MY_EMPLOYEE вторую строку из вышеуказанных образцов. На этот раз укажите столбцы явно в предложении INSERT. INSERT INTO MY_EMPLOYEE(ID, LAST_NAME, FIRST_NAME, USERID, SALARY) VALUES(2, ‘Dancs’, ‘Betty’, ‘bdancs’, 860); 18. Создайте точку сохранения в ходе транзакции. SAVEPOINT save; 19. Удалите все данные из таблицы. DELETE FROM my_employee; 20. Убедитесь в том, что таблица пуста. 21. Отмените последнюю операцию DELETE, не отменяя предыдущую операцию INSERT. ROLLBACK TO SAVEPOINT save; 22. Убедитесь в том, что вставленная строка присутствует в таблице. 23. Сделайте добавление данных постоянным. Практическое занятие 9. Использование команд DDL для создания и изменения таблиц  Создание новых таблиц  Создание новой таблицы с помощью синтаксиса CREATE TABLE AS  Проверка существования таблицы  Удаление таблиц 1. Создайте таблицу DEPT на основе приведенного ниже описания. Сохраните команду в файле lab9_1.sql, а затем выполните файл для создания таблицы. Убедитесь в том, что таблица создана. Имя столбца ID Name Тип ключа Primary Key Nulls/Unique Таблица FK Столбец FK Тип данных NUMBER VARCHAR2 Длина 7 25 CREATE TABLE DEPT ( ID NUMBER(7) CONSTRAINT DEPT_ID_PRIMARY PRIMARY KEY, NAME VARCHAR2(25) ); 2. Заполните таблицу DEPT данными из таблицы Departments. Включите только нужные столбцы. INSERT INTO DEPT ( SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS ); 3. Создайте таблицу EMP на основе приведенного ниже описания. Сохраните команду в скрипт- файле lab9_3.sql, а затем выполните его, чтобы создать таблицу. Убедитесь в том, что таблица создана. Имя столбца ID Last_name FIRST_NAME DEPT_ID Тип ключа Primary Key Foreign Key Nulls/Unique Таблица PK DEPT Столбец PK ID Тип данных NUMBER VARCHAR2 VARCHAR2 NUMBER Длина 7 25 25 7 CREATE TABLE EMP ( ID NUMBER(7) CONSTRAINT EMP_ID_PRIMARY PRIMARY KEY, LAST_NAME VARCHAR2(25), FIRST_NAME VARCHAR2(25), DEPT_ID NUMBER(7), CONSTRAINT EMP_DEPT_FOREIGN FOREIGN KEY (DEPT_ID) REFERENCES DEPT(ID) ); 4. Создайте таблицу EMPLOYEES2, на основе структуры таблицы EMPLOYEES, включив только столбцы EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY и DEPARTMENT_ID. Имена колонок в новой таблице должны быть ID, FIRST_NAME, LAST_NAME, SALARY и DEPT_ID соответственно. CREATE TABLE EMPLOYEES2 AS SELECT EMPLOYEE_ID AS «ID», FIRST_NAME, LAST_NAME, SALARY, DEPARTMENT_ID AS «DEPT_ID» FROM EMPLOYEES; 5. Удалите таблицу EMP. DROP TABLE EMP; 6. Попробуйте сменить номер отдела служащего по фамилии Matos на 80. UPDATE DEPT50 SET DEPTNO = 80 WHERE EMPLOYEE = ‘Matos’; 7. Создайте представление SALARY_VU, включающее фамилию служащего, название отдела, оклад и категорию оклада для всех служащих. Используйте таблицы EMPLOYEES, DEPARTMENTS и JOB_GRADES. Соответственно назовите столбцы Employee, Department, Salary и Grade. CREATE VIEW SALARY_VU AS SELECT E.LAST_NAME AS «EMPLOYEE», D.DEPARTMENT_NAME AS «DEPARTMENT», E.SALARY AS «SALARY», JG.GRADE_LEVEL AS «GRADE» FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID JOIN JOB_GRADES JG ON E.SALARY >= JG.LOWEST_SAL AND E.SALARY <= HIGHEST_SAL; 8. Покажите структуру представления SALARY_VU. DESCRIBE SALARY_VU; 9. Удалите представление SALARY_VU DROP VIEW SALARY_VU; 10. Восстановите представление SALARY_VU, воспользовавшись опцией SQL History. 11. Проверьте работоспособность представления. SELECT * FROM SALARY_VU; Практическое занятие 10. Создание других объектов схем (часть 2)  Создание последовательностей  Использование последовательностей  Создание неуникальных индексов и синонимов 1. Создайте последовательность для столбца главного ключа таблицы DEPT. Последовательность должна начинаться с 200 и иметь максимальное значение 1000. Шаг приращения значений – 10. Назовите последовательность DEPT_ID_SEQ. CREATE SEQUENCE DEPT_ID_SEQ INCREMENT BY 10 START WITH 200 MAXVALUE 1000; 2. Создайте скрипт lab10_2_2.sql для вставки строки в таблицу DEPT. Обязательно используйте последовательность, созданную Вами для столбца ID. Добавьте в таблицу два отдела: Education и Administration. Выполните файл, проверьте успешное выполнение вставки. INSERT INTO DEPT VALUES (DEPT_ID_SEQ.NEXTVAL, ‘&DEPT_NAME’); 3. Измените созданную Вами последовательность, установив шаг приращения 20. Добавьте отдел Publication с использованием последовательности. Проверьте выполнение вставки. ALTER SEQUENCE DEPT_ID_SEQ INCREMENT BY 20; 4. Измените созданную Вами последовательность, установив начальное значение, равным 300.. Добавьте отдел Distribution с использованием последовательности. Проверьте выполнение вставки. DROP SEQUENCE DEPT_ID_SEQ; CREATE SEQUENCE DEPT_ID_SEQ INCREMENT BY 10 START WITH 300 MAXVALUE 1000; 5. Создайте последовательность SEQ_CYCLE. Последовательность должна генерировать числа: 0 1 -1 0 1 -1…….. Проверьте правильность работы последовательности. COMMENT ON TABLE DEPARTMENTS IS ‘Company department information including name, code and location’; SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME = ‘DEPARTMENTS’; 4. Добавьте комментарий к столбцу LAST_NAME таблицы EMPLOYEES. Проверьте факт создания комментария к столбцу. COMMENT ON COLUMN EMPLOYEES.LAST_NAME IS ‘LAST_NAME COMMENT’; SELECT COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME = ‘EMPLOYEES’ AND COLUMN_NAME = ‘LAST_NAME’; 5. Найдите имена всех синонимов в вашей схеме SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK EMP HR EMPLOYEES SELECT SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, NVL(DB_LINK, ‘ ‘) AS «DB_LINK» FROM USER_SYNONYMS; 6. Из представления словаря данных USER_VIEWS выберите столбцы VIEW_NAME и TEXT. SELECT VIEW_NAME, TEXT FROM USER_VIEWS; 7. Найдите имена всех ваших последовательностей. Напишите запрос для получения следующей информации о ваших последовательностях: имя последовательности, максимальное значение, величина приращения, последнее значение. Сохраните скрипт в файле lab11_7.sql. Выполните скрипт. SEQUENCE_NAME MAX_VALUE INCREMENT_BY LAST_NUMBER DEPARTMENTS_SEQ 9990 10 280 EMPLOYEES_SEQ 1,0000E+27 1 207 LOCATIONS_SEQ 9900 100 3300 SELECT SEQUENCE_NAME, CASE WHEN MAX_VALUE > POWER(10,25) THEN TO_CHAR(MAX_VALUE, ‘9D9999EEEE’) ELSE TO_CHAR(MAX_VALUE) END AS «MAX_VALUE», INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES;

Источник

I appologize if something like this has already been posted or is just a simple fix but I’ve been looking around all over and can’t seem to find a solution to this problem. I’m using vba: ms access, and I’m trying to comapre two identical tables in diffrent databases by running a query. I’ve been trying to make the script so it can be run on diffrent databases requiring only user input. However throught this process I seem to keep getting an error that reads:

The select statement includes a reserved word or an argument name that
is misspelled or missing.

The LinkUserTable function basically creates a linked table from another DB based off user input. Below is the subroutine:

Sub LinkTables()
        Dim sTable1 As String
        Dim sTable2 As String
    sTable1 = LinkUserTable
    sTable2 = LinkUserTable

    vars_sql = "SELECT " & sTable1 & ".[f_ptid]," & sTable1 & ".[f_flag]," & sTable1 & ".[f_user]," & sTable1 & ".[f_brief]," & sTable1 & ".[f_gid]," & sTable1 & ".[f_vco]," & sTable1 & ".[f_dtype]," & sTable1 & ".[f_precs]," & sTable1 & ".[f_addr]," & sTable1 & ".[f_ndim]," & sTable1 & ".[f_sys]," & sTable1 & ".[f_unit]," & sTable1 & ".[f_value]," & sTable1 & ".[f_pred]," & sTable1 & ".[f_bflag]," & sTable1 & ".[f_dim1]," & sTable1 & ".[f_dim2]," & sTable1 & ".[f_dim3]," & sTable1 & ".[f_bound]," & sTable1 & ".[f_size]," & sTable1 & ".[f_updat]," & sTable1 & ".[f_time]," & sTable1 & ".[f_ldes]," & sTable1 & ".[f_sflag]," & sTable1 & ".[f_tflag]," & sTable1 & ".[rid]" + _
                    "FROM" + sTable1 + " AS LEFT JOIN " + sTable2 + " AS ON sTable1.[f_ptid] = sTable2.[f_ptid]" + _
                    "WHERE (((" & sTable2 & ".[f_ptid]) Is Null))" 


If CheckQuery("new_old") = "Yes" Then
            DoCmd.DeleteObject acQuery, "new_old"
        End If

Set qdf = CurrentDb.CreateQueryDef("new_old", vars_sql)

End Sub

I was just wondering based off of this what the possible problems could be that would cause this error? All of the fields are in both databases, all with the same properties(just diffrent data). I appologize if there is a lack of information I can supply more if needed.

Источник

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

Возможно, вам также будет интересно: