blablabla


Лабораторная работа 1
Выборка данных из разных таблиц
Описание структуры таблиц
Выполнение арифметических вычислений и задание имён столбцов
Использование 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 Departments8 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 EmployeesName 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 StartDate100 King AD_PRES 17-JUN-87101 KochharAD_VP 21-SEP-89102 De HaanAD_VP 13-JAN-93103 HunoldIT_PROG 03-JAN-90104 Ernst IT_PROG 21-MAY-91107 Lorentz IT_PROG 07-FEB-99124 MourgosST_MAN 16-NOV-99141 RajsST_CLERK 17-OCT-95142 Davies ST_CLERK 90-JAN-97
143 Matos ST_CLERK 15-MAR-98144 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 Titleselect FIRST_NAME||', '||JOB_ID as "Employee and Title" from EMPLOYEES
Создайте запрос для вывода всех данных из таблицы EMPLOYEES. Разделите столбцы запятыми. Назовите столбец THE_OUTPUTselect 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
Kochhar17000
De Haan17000
Hartstein13000
Создайте запрос для вывода фамилии и номера отдела служащего под номером 176
SELECT last_name, department_id FROM employees where employee_id=176
LAST_NAME DEPARTMENT_ID
Taylor80
Измените файл 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
Kochhar17000
De Haan17000
Lorentz 4200
Rajs3500
Davies 3100
Matos 2600
Vargas 2500
Whalen 4400
Hartstein13000
LAST_NAME JOB_ID HIRE_DATE
Matos ST_CLERK 15-MAR-98TaylorSA_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
Hartstein20
Matos 50
Mourgos50
Rajs50
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
Mourgos5800
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-94Gietz07-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 descLAST_NAME SALARY COMMISSION_PCT
Abel 11000 .3
Zlotkey10500 .2
Taylor8600 .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 HaanDavies
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
TaylorSA_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 descEmployee Monthly Salary COMMISSION_PCT
Zlotkey10500 .2
Taylor8600 .2
Лабораторная работа 3
Составление запроса для вывода текущей даты.
Составление запросов, требующих использования числовых, символьных функций и функций для работы с датами.
Вычисление продолжительности работы служащего в месяцах и годах.
Напишите запрос для вывода текущей даты. Назовите столбец Date.
DATE
08-MAR-01select to_char(sysdate, 'DD-MM-YYYY ') as "Current Time" from dual;
Выведите номер служащего, его фамилию, оклад и новый оклад, повышенный на 15% и округлённый до целого. Назовите столбец New Salary. Сохраните команду SQL в текстовом файле lab3_2sql.
Выполните запрос из файла lab3_2sql.
EMPLOYEE_ID LAST_NAME SALARY New Salary
100 King 24000 27600
101 Kochhar17000 19550
102 De Haan17000 19550
103 Hunold9000 10350
104 Ernst 6000 6900
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,round(SALARY + (.15 * SALARY)) as "New Salary" FROM employees
Измените lab3_2.sql. добавьте ещё один столбец, который будет содержать результат вычитания старого оклада из нового. Назовите столбец Increase. Сохраните изменённый запрос в lab3_4.sql. Выполните запрос ещё раз.
EMPLOYEE_ID LAST_NAME SALARY New Salary Increase
100 King 24000 27600 3600
101 Kochhar17000 19550 2550
102 De Haan17000 19550 2550
103 Hunold9000 10350 1350
104 Ernst 6000 6900 900
SELECT EMPLOYEE_ID,LAST_NAME,SALARY,round(SALARY + (.15 * SALARY))as "New Salary",round(.15 * salary) as "Increase" FROM employees
Выведите фамилии служащих (первая буква каждой фамилии должна быть заглавной, а остальные - строчными) и длину каждой фамилии для тех служащих, фамилия которых начинается с символа J, A или М. Присвойте соответствующие заголовки столбцам.Name LenghtAbel 4
Matos 5
Mourgos7
select last_name as "Name",length(last_name)as "Length" from employees
where last_name like 'A%' or last_name like 'J%' or last_name like 'M%'Лабораторная работа 4
Составление запросов, требующих использования числовых, символьных функций и функций для работы с датами.
Использование конкатенации с функциями.
Составление запросов, нечувствительных к регистру символов, для проверки полезности символьных функций.
Вычисление продолжительности работы служащего в месяцах и годах.
Определение даты аттестации служащего.
Для каждого служащего выведите фамилию и вычислите количество месяцев со дня найма до настоящего времени, округлённое до ближайшего целого. Назовите столбец MONTH_WORKED. Результаты отсортируйте по количеству отработанных месяцев. Округлите число до ближайшего целого.
LAST_NAME MONTH_WORKED
Zlotkey13
Mourgos16
Grant 22
Lorentz 25
Vargas 32
Taylor36
Matos 36
Fay 43
select last_name,trunc(months_between(sysdate,hire_date)) as MONTH_WORKED from employees Order by month_worked
Получите по каждому служащему отчёт в следующем виде:<фамилия> зарабатывает <оклад> в месяц, но желает <утроенный оклад>. Назовите столбец 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
select concat(last_name,' earns $' , salary, ' monthly but wants $', salary * 3 ) as "Dream Salaries" from EMPLOYEES
Напишите запрос для вывода фамилий и окладов всех служащих. Назовите выходной столбец SALARY. Длина столбца SALARY – 15 символов с заполнением символом $.
LAST_NAME SALARY
King $$$$$$$$$$24000
Kochhar$$$$$$$$$$17000
De Haan$$$$$$$$$$17000
Hunold$$$$$$$$$$$9000
Ernst $$$$$$$$$$$6000
Lorentz $$$$$$$$$$$4200
Mourgos$$$$$$$$$$$5800
Rajs$$$$$$$$$$$3500
Davies $$$$$$$$$$$3100
Matos $$$$$$$$$$$2600
select last_name , lpad(salary,15,'$') as salary from EMPLOYEES
Для каждого служащего выведите фамилию, дату найма и дату пересмотра зарплаты, которая приходится на первый понедельник после 6 месяцев работы. Назовите столбец REVIEW. Формат даты при выводе имеет вид: “Monday, the Thirty-First of MM, YYYY”.
LAST_NAME HIRE_DATE REVIEW
King 17-JUN-87Monday, the Thirty-First of December, 1987Kochhar21-SEP-89Monday, the Thirty-Sixth of March, 1990
De Haan13-JAN-93Monday, the Nineteenth of July, 1993
Hunold03-JAN-90Monday, the Ninth of July, 1990
select last_name,hire_date, to_char((next_day(add_months(hire_date,6),1)),'Day,"the" DDspth "of" MM,YYYY') as "review" from employees
По каждому служащему выведите фамилию, дату найма и день недели, когда он был нанят на работу. Назовите последний столбец DAY. Отсортируйте результаты по датам.
LAST_NAME HIRE_DATE DAY
Grant 24-MAY-99MONDAY
Ernst 21-MAY-91TUESDAY
Mourgos16-NOV-99TUESDAY
Taylor24-MAR-98TUESDAY
Rajs17-OCT-95TUESDAY
Gietz07-JUN-94TUESDAY
Higgins 07-JUN-94TUESDAY
King 17-JUN-87WEDNESDAY
De Haan13-JAN-93WEDNESDAY
SELECT last_name,hire_date,to_char(hire_date, 'Dy')as 'Day' from employees ORDER BY to_char(hire_date, 'D')
Напишите запрос для вывода фамилии и суммы комиссионных каждого служащего. Если служащий не зарабатывает комиссионных, укажите в столбце «No Commission». Назовите столбец COMM.
LAST_NAME COMM
King No Commission
KochharNo Commission
De HaanNo Commission
HunoldNo Commission
Ernst No Commission
Lorentz No Commission
MourgosNo Commission
RajsNo Commission
Davies No Commission
Matos No Commission
Vargas No Commission
Zlotkey.2
Abel .3
select last_name,replace(nvl(commission_pct,1),1,'No Commission') as "COMM"
from employees
Используя функцию 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
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
order by job_id
Перепешите команду из предыдущего задания, используя синтаксис выражения CASE. SELECT distinct 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 ) as "G"
FROM employees order by job_idЛабораторная работа 5
Соединение таблиц с использованием эквисоединения.
Выполнение внешних соединений и соединений таблицы с собой
Включение дополнительных условий.
Напишите запрос для вывода фамилии, номера отдела и названия отдела дл всех служащих.
LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
Whalen 10 Administration
Hartstein20 Marketing
Fay 20 Marketing
Mourgos50 Shipping
Rajs50 Shipping
Davies 50 Shipping
Matos 50 Shipping
Vargas 50 Shipping
select e.last_name,e.department_id, d.department_name
from employees e, departments d
where e.department_id=d.department_id
Выведите список всех должностей в отдел 80 (должности в списке не должны повторяться) и местоположение отдела.
JOB_ID LOCATION_ID
SA_MAN 2500
SA_REP 2500
select distinct e.job_id, d.location_id from employees e, departments d where d.department_id= '80' order by e.job_idНапишите запрос для вывода фамилий, названия отдела, идентификатора местоположения отдела и города, в котором он находится, для всех служащих, зарабатывающих комиссионные.
LAST_NAME DEPARTMENT_NAME LOCATION_ID CITY
ZlotkeySales 2500 OxfordAbel Sales 2500 OxfordTaylorSales 2500 Oxfordselect e.last_name,d.DEPARTMENT_NAME,d.LOCATION_ID,l.CITY from employees e,departments d,locations l where commission_pct is not null
Выведите фамилии всех служащих, содержащих букву «а» (в строчном регистре), с названиями отделов. Сохраните свою команду SQL в файле lab4_4.sql.
LAST_NAME DEPARTMENT_NAME
Whalen Administration
HartsteinMarketing
Fay Marketing
RajsShipping
Davies Shipping
Matos Shipping
Vargas Shipping
TaylorSales
KochharExecutive
De HaanExecutive
select e.last_name,d.department_name from employees e,departments d where last_name like '_%a%'
Напишите запрос для вывода фамилии, должности, номера отдела и названия отдела всех служащих, работающих в городе Toronto.
LAST_NAME JOB_ID DEPARTMENT_ID DEPARTMENT_NAME
HartsteinMK_MAN 20 Marketing
Fay MK_REP 20 Marketing
select e.last_name,e.job_id,d.department_id,d.department_namefrom employees e, departments d, locations
where city like 'Toronto'
Выведите фамилии и номера всех служащих вместе с фамилиями и номерами их менеджеров. Назовите столбцы EMPLOYEE, Emp#, Manager и Mgr#. Сохраните свою команду SQL в файле lab4_6.sql.
Employee EMP# Manager Mgr#
Kochhar101 King 100
De Haan102 King 100
Mourgos124 King 100
Zlotkey149 King 100
select e.last_name Employee, e.employee_id Emp#,m.last_name Manager, e.manager_id Mgr#
from employees e,employees m where e.manager_id is not null
and e.manager_id = m.employee_id
Измените файл lab4_6.sql так, чтобы получить фамилии всех служащих, включая Кинга, который не имеет менеджера. Упорядочьте результат по возрастанию номера служащего. Сохраните изменённый запрос в текстовом файле lab4_7.sql. Выполните запрос.
Employee EMP# Manager Mgr#
King 100 Kochhar101 King 100
De Haan102 King 100
Hunolds103 De Haan102
Ernst 104 Hunold103
Lorentz 107 Hunold103
select e.last_name Employee, e.employee_id Emp#,m. last_name Manager, e.manager_id Mgr#
from employees e left join employees m on( e.manager_id = m.employee_id)
order by e.employee_idСоздайте запрос для вывода номера отдела, фамилии служащего и фамилий всех служащих, работающих в одном отделе с данным служащим. Дайте столбцам соответствующие имена.
DEPARTMENT EMPLOYEE COLLEAGUE
20 Fay Hartstein20 HartsteinFay
50 Davies Matos
50 Davies Mourgos50 Davies Rajs50 Davies Vargas
select e.department_id department,e.last_name employee,m.last_name colegue
from employees e,employees m
where e.employee_id != m.employee_id and e.department_id = m.department_idПокажите структуру таблицы JOB_GRADES. Создайте запрос для вывода фамилии, должности, названия отдела, оклада и категории (GRADE_LEVEL) всех служащих.
Name Null? Type
GRADE_LEVEL VARCHAR2(3)
LOWEST_SAL NUMBER
HIGHEST_SAL NUMBER

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
MourgosST_MAN Shipping 5800 B
RajsST_CLERK Shipping 3500 B
Davies ST_CLERK Shipping 3100 B
Whalen AD_ASST Administration 4400 B
select e.last_name,e.job_id,d.department_name,e.salary,
j.grade_level gra from departments d,employees e left outer join job_grades j on e.salary>=j.lowest_sal and e.salary<=j.highest_sal
Создайте запрос для вывода фамилий и дат найма всех служащих, нанятых после Davies.
LAST_NAME HIRE_DATE
Lorentz 07-FEB-99Mourgos16-NOV-99Matos 15_MAR-98
Vargas 09-JUL-98Zlotkey29-JAN-00Taylor24-MAR-98Grant 24-MAY-99Fay 17-AUG-97select last_name,hire_date from employees where hire_date > (select hire_date from employees where last_name like 'Davies' )
По всем служащим нанятым раньше своих менеджеров, выведите фамилии и даты найма самих служащих, а также фамилии и даты найма их менеджеров. Назовите столбцы Employee, Emp, Manager и Manager Hired.
Employee Emp Hired Manager Mgr hired
Whalen 17-SEP-87Kochhar21-SEP-89Hunold03-JAN-90De Haan13-JAN-93Rajs17-OCT-95Mourgos16-NOV-99Davies 29-JAN-97Mourgos16-NOV-99Matos 15-MAR-98Mourgos16-NOV-99Vargas 09-JUL-98Mourgos16-NOV-99Abel 11-MAY-96Zlotkey29-JAN-00Taylor24-MAR-98Zlotkey29-JAN-00Grant 24-MAY-99Zlotkey29-JAN-00select e.last_name Employee,e.hire_date "Emp Hired",m.last_name Manager,m.hire_date "Mgr hired"
from employees e left outer join employees m on e.manager_id = m.employee_id where e.manager_id is not null and (e.hire_date) < (select hire_date from employees m where e.manager_id = m.employee_id)
Выведите номера, наименования и местоположение всех отделов, а также количество работающих в них сотрудников. Обеспечьте вывод отделов в которых нет сотрудников.
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
Какие должности имеются в отделах Administration и Executive, а также сколько сотрудников занимают эти должности? Выведите первыми наиболее часто встречаемые должности.
JOB_ID FREQUENCY
AD_VP 2
AD_ASST 1
AD_PRES 1
Покажите сотрудников, менеджеры которых имеют оклад выше $15000. выведите следующие сведения: фамилию сотрудника, фамилию менеджера, его оклад и уровень оклада (GRADE_LEVEL).
LAST_NAME MANAGER SALARY GRA
KochharKing 24000 E
De HaanKing 24000 E
MourgosKing 24000 E
ZlotkeyKing 24000 E
HartsteinKing 24000 E
Whalen Kochhar17000 E
Higgins Kochhar17000 E
HunoldDe Haan17000 E
12) select distinct d.DEPARTMENT_ID,d.DEPARTMENT_NAME,d.LOCATION_ID,COUNT(E.EMPLOYEE_ID)
from departments d left outer join employees e on e.DEPARTMENT_id = d.DEPARTMENT_idgroup by d.DEPARTMENT_ID,d.DEPARTMENT_NAME,d.LOCATION_IDorder by department_id13) select j.job_id,count(h.employee_id) as "Frequency" from departments d,job_history h left outer join job_history j on h.job_id = j.job_idwhere d.department_name like 'Administration' or d.department_name like 'Executive'
group by j.job_idorder by count(h.employee_id) desc14) select distinct e.last_name Last_Name,
m.last_name Manager,
m.salary as Salary,
j.grade_level grafrom job_grades j, employees e left outer join employees m on e.manager_id = m.employee_id
where e.manager_id is not null and m.salary > 15000 and m.salary>=j.lowest_sal and m.salary<=j.highest_sal Лабораторная работа 6
Использование групповых функций
Вывод данных по группам с помощью предложения GROUP BY
Включение и исключение групп с помощью предложения HAVING
Напишите запрос для вывода самого высокого, самого низкого и среднего оклада по всем служащим, а также суммы всех окладов. Назовите столбцы Maximum, Minimum, Average и Sum. Округлите суммы до ближайшего целого значения. Сохраните свой запрос в файле lab5_6.sql.
Maximum Minimum Sum Average
24000 2500 175500 8775
select round(max(salary)),round(min(salary)), round(avg(salary)),round(sum(salary)) from employees
Измените запрос в lab5_6.sql. так, чтобы получить самый низкий, самый высокий и средний оклады, а также сумму окладов по каждой должности. Сохраните изменённый запрос в файле lab5_5.sql. Выполните запрос из lab5_5.sql.
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
select job_id,round(max(salary)),round(min(salary)), round(avg(salary)),round(sum(salary)) from employees group by job_idНапишите запрос для вывода должности и количества служащих, занимающих каждую должность.
JOB_ID COUNT(*)
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(job_id) from employees group by job_idПолучите количество служащих, имеющих подчинённых, без их перечисления. Назовите столбец Number of Managers. Используйте столбец MANAGER_ID для определения числа менеджеров.
Number of Managers
8
select count(distinct manager_id) “number of Managers” from employees
Напишите запрос для вывода разности между самым высоким и самым низким окладами. Назовите столбец DIFFERENCE
DIFFERENCE
21500
select max(max_salary)-min(min_salary) difference from jobs
Напишите запрос для вывода номера каждого менеджера, имеющего подчинённых, и заработную плату самого низкооплачиваемого из его подчинённых. Исключите менеджеров для которых неизвестны их менеджеры. Исключите все группы, где минимальный оклад составляет менее $6000. Отсортируйте выходные строки в порядке убывания оклада.
MANAGER_ID MIN(SALARY)
102 9000
205 8300
149 7000
select distinct e.manager_id,min(j.min_salary) from employees e,jobs j where e.manager_id is not null group by e.manager_id having min(j.min_salary) > 6000 order by min(j.min_salary) descНапишите запрос для вывода названия отдела, местоположения отдела, количества служащих и среднего оклада по этому отделу. Назовите столбцы Name, Location, Number of People и Salary. Округлите средний оклад до ближайшего целого значения.
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
select d.department_name “Name”,d.location_id “Location”,count(employee_id)"number of people",round(avg(e.salary)) “Salary”
from employees e,departments d
where e.department_id = d.department_idgroup by d.department_name,d.location_idНапишите запрос для вывода общего количества служащих и количества служащих, нанятых в 1995, 1996, 1997 и 1998 годах. Дайте соответствующие заголовки столбцам.
TOTAL 1995 1996 1997 1998
20 1 2 2 3
select count(*) total,
sum(decode (to_char(hire_date, 'yyyy'),'1995',1, 0)) "1995"
,sum(decode (to_char(hire_date, 'yyyy'),'1996',1, 0)) "1996"
,sum(decode (to_char(hire_date, 'yyyy'),'1997',1, 0)) "1997"
,sum(decode (to_char(hire_date, 'yyyy'),'1998',1, 0)) "1998"
from employees
Напишите матричный запрос для вывода всех должностей и суммы заработной платы служащих, работающих в этой должности в отделах 20, 50, 80 и 90. Последний столбец должен содержать сумму заработной платы служащих этих отделов, занимающих каждую конкретную должность. Дайте столбцам соответствующие заголовки.
Job Dept20 Dept50 Dept80 Dept90 Total
AC_ACCOUNT 8300
AC_MGR 12000
AD_ASST 4400
AD_PRES 24000 24000
AD_VP 34000 34000
IT_PROG 19200
select job_id,
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Лабораторная работа 7
Создание подзапросов для выборки данных по неизвестным критериям
Использование подзапросов для выявления значений, существующих в одном наборе данных и отсутствующих в другом
228600429260Создайте запрос для вывода фамилии и даты найма каждого служащего, работающего в одном отделе с Zlotkey. Исключите Zlotkey из выходных данных.
select last_name,hire_date from employees where department_id =(select department_id from employees where last_name like 'Zlotkey') and last_name not like 'Zlotkey'
228600468630Создайте запрос для вывода номеров и фамилий всех служащих, оклад которых выше среднего. Отсортируйте данные в порядке увеличения окладов.
select employee_id,last_name,salary from employees group by employee_id,last_name,salary having (select avg(salary) from employees)< salary
228600573405Создайте запрос для вывода номеров и фамилий всех служащих, работающих в одном отделе с любым служащим, фамилия которого содержит букву «u». Сохраните свой запрос в тестовом файле lab6_3.sql. Выполните запрос.
select e.employee_id,e.last_name from employees e
where (e.department_id) in (select m.department_id from employees m where m.last_name like lower('%u%'))2286004572004.Создайте запрос для вывода фамилии, номера отдела и должности каждого служащего, идентификатор местоположения отдела которого равен 1700.
select distinct e.last_name,e.department_id,e.job_id from employees e,departments d where d.location_id = 1700
5.Получите список фамилий и окладов всех служащих, подчинённых Кингу.
select last_name,salary, from employees where manager_id = (select employee_id from employees where manager_id is null) 22860093345
2286004781556.Получите номер отдела, фамилию и должность для каждого служащего, работающего в администрации (department_name = ‘Executive’).
select e.department_id,e.last_name,e.job_id from employees e,departments d where d.department_name like 'Executive'
7.Измените lab6_3.sql для вывода номеров, фамилий и окладов всех служащих, которые работают в одном отделе с любым служащим с буквой «u» в фамилии и оклады которых превышают средний. Сохраните новый запрос в файле lab6_7.sql. Выполните его.
select e.employee_id,e.last_name,salary from employees e
where (e.department_id) in (select m.department_id from employees m where m.last_name like lower('%u%'))
group by e.employee_id,e.last_name,salaryhaving (select avg(salary) from employees)< salary 228600123825
2286004876808.Покажите номер отдела с наивысшей средней заработной платой и наименьший оклад работающегов нём сотрудника.
select department_id,min(e.salary) from employees e group by department_id having (max(select avg(m.salary) from employees m )) > ( avg(e.salary)) //доделать
2286004572009.Выведите номера , наименования и местоположения отделов, в которых не работают торговые представители (job_id = ‘SA_REP’).
select distinct d.department_id,d.department_name,d.manager_id,d.location_id from departments d
where (d.department_id) in (select m.department_id from employees m where m.job_id not like 'SA_REP')
Лабораторная работа 8
Создание запросов, в которых используются операторы SET.
Альтернативные методы соединения.
Используя оператор SET, выведите номера отделов (department_id), в которых нет служащих с идентификатором должности (job_id) ST_CLERK.
select department_id from employees e
where not exists (select job_id from employees where job_id=e.job_id and job_id = 'ST_CLERK')
Используя оператор SET, выведите идентификаторы и наименования стран, в которых не располагаются отделы компании.
select distinct COUNTRY_ID, COUNTRY_NAME FROM COUNTRIES WHERE COUNTRY_ID IN (SELECT COUNTRY_ID FROM COUNTRIES MINUS SELECT COUNTRY_ID FROM LOCATIONS WHERE LOCATION_ID IN (SELECT LOCATION_ID FROM DEPARTMENTS))
Используя оператор SET, выведите список должностей отделов 10, 50 и 20 в таком же порядке отделов. Выведите столбцы job_id и department_id.
select job_id,department_id from
(select 1 as id,job_id,department_id from employees where department_id=10
unionselect 2 as id,job_id,department_id from employees where department_id=50
union
select 3 as id,job_id,department_id from employees where department_id=20)
Выведите номер сотрудника и идентификатор его должности, если его текущая должность совпадает с той, которую он уже занимал, работая в компании.
select e.employee_id,e.job_id from job_history j,employees e where e.job_id=j.job_id and e.employee_id=j.employee_idunionselect j.employee_id,j.job_id from job_history j,employees e where e.job_id=j.job_id and e.employee_id=j.employee_idНапишите составной запрос, который выводит следующее:
Номера и отделы всех сотрудников из таблицы EMPLOYEES, независимо от того, относятся ли они к какому-то отделу или нет;
Номера и наименования всех отделов из таблицы DEPARTMENTS, независимо от того, есть ли в них сотрудники или нет.
select employee_id,department_id,NULL,NULL from employees
unionselect NULL as department_id,NULL as department_name,department_id,department_name from departments
Лабораторная работа 9
Создание многостолбцовых подзапросов
Написание связанных подзапросов
Использование оператора EXIST
Использование скалярных подзапросов
Использование предложения WITH
Создайте запрос для вывода фамилии, номера отдела и оклада всех служащих, чей номер отдела и оклад совпадают с номером отдела и окладом любого служащего, зарабатывающего комиссионные.
select last_name,department_id,salary from employees e
where exists (select * from employees d where e.department_id=d.department_id and e.salary=d.salary and e.commission_pct is not null)
Выведите фамилию, название отдела и оклад всех служащих, чей оклад и комиссионные совпадают с окладом и комиссионными любого служащего, работающего в отделе, местоположение которого (Location_ID).
228600543560Создайте запрос для вывода фамилии, даты найма и оклада всех служащих, которые получают такой же оклад и такие же комиссионные, как Kochhar. Не выводите данные о сотруднике Kochhar.
select e.last_name,e.hire_date,e.salary from employees e where (e.hire_date,e.salary) in
(select h.hire_date,h.salary from employees h where exists
(select d.last_name from employees d where last_name = 'Kochhar' and h.salary=d.salary) and last_name <> 'Kochhar')
Выведите фамилию, должность и оклад всех служащих, оклад которых превышает оклад каждого клерка торгового менеджера (JOB_ID = ‘SA_MAN’). Отсортируйте результаты по убыванию окладов.
Выведите номера, фамилии и отделы служащих, живущих в городах, названия которых начинаются с буквы Т.
4)select last_name,job_id,salary from employees where salary >(select min(salary) from employees where job_id ='SA_MAN' )and job_id <> 'SA_MAN'
5) select employee_id,last_name,department_id from employees e where exists (select city from locations where city like 'T%')
228600741045Напишите запрос для нахождения всех сотрудников, которые зарабатывают больше среднего оклада по их отделу. Выведите фамилию, оклад, номер отдела и средний оклад по отделу. Отсортируйте результаты по средней зарплате. Используйте псевдонимы для выбираемых столбцов.
select e.last_name ename,e.salary ,e.department_id deptno,(select avg(f.salary) from employees f where f.department_id = e.department_id ) dept_avg from employees e
where (select avg(f.salary) from employees f where f.department_id = e.department_id ) < e.salary order by 4
Найдите всех сотрудников, не являющихся руководителями. Выполните это с помощью оператора NOT EXIST.
select e.last_name from employees e where not exists ( select m.last_name from employees m where m.manager_id = e.employee_id )
Может ли это же быть сделано с помощью оператора NOT IN? нет
Выведите фамилии сотрудников, зарабатывающих меньше среднего оклада по их отделу.
select e.last_name from employees e where salary in(select salary from employees where salary < ( select avg(m.salary) from employees m where m.department_id = e.department_id ))
Выведите фамилии сотрудников, у которых есть коллеги по отделу, которые были приняты на работу позже, но имеют более высокий оклад.
select e.last_name from employees e where exists ( select m.last_name from employees m where m.hire_date >e.hire_date and m.salary > e.salary and m.department_id=e.department_id )
Выведите номера, фамилии и наименования отделов всех сотрудников. Используйте скалярный подзапрос в команде SELECT для вывода наименований отделов.
select e.employee_id,e.last_name,(select d.department_name from departments d where d.department_id=e.department_id) department from employees e
Напишите запрос для вывода наименования отделов и фондов заработной платы отделов, размер которых больше 1/8 от общего фонда заработной платы всей компании. Используйте предложение WITH для создания запроса. Назовите запрос SUMMARY.
Лабораторная работа 10
Отличие иерархических запросов от неиерархических
Обход дерева
Получение отчёта с отступом с помощью псевдостолбца LEVEL
Отсечение структур дерева
Сортировка выходных данных
Напишите отчёт, в котором отражена структура отдела, которым руководит Mourgos. Выведите фамилии, оклады и номер отдела сотрудников.
Создайте отчёт, который показывает иерархию менеджеров, которым подчиняется сотрудник Lorentz. Выведите сначала менеджера, перед которым непосредственно отчитывается Lorentz.
Создайте отчёт с отступом, в котором отражается иерархия управления, начиная с сотрудника по фамилии Kochhar. Выведите фамилии, номера менеджеров и номера отделов сотрудников. Назовите столбцы как показано в примере выходных результатов.
Создайте отчёт, отражающий иерархию управления компанией. Начните с сотрудника самого высокого уровня и исключите из выходных данных всех служащих с идентификатором должности IT_PROG, а также сотрудника De Haan и всех, кто перед ним отчитывается.

Приложенные файлы

  • docx 24059430
    Размер файла: 317 kB Загрузок: 3

Добавить комментарий