Задание 1.7. Занесите в таблицу assigments_n следующие данные:

PROJID	1	1	2
EMPNO	7369	7902	7844
A_START_DATE	02-JAN-88	02-JAN-88	02-JAN-88
A_END_DATE	02-JAN-88	02-JAN-88	02-JAN-88
BULL_RATE	50.00	55.00	45.50
ASSIGN_TYPE	WR	WR	PF
HOURS	15	20	30

SQL> insert into assigments_5 values

2 (1, 7369, '02.01.88', '02.01.88', 50.00, 'WR', 15)

3 /

1 row created.

Commit complete.

SQL> insert into assigments_5 values

2 (1, 7902, '02.01.88', '02.01.88', 55.00, 'WR', 20)

3 /

1 row created.

Commit complete.

SQL> insert into assigments_5 values

2 (2, 7844, '02.01.88', '02.01.88', 45.50, 'PF', 30)

3 /

1 row created.

Commit complete.

Задание 1.8. Создайте представление для наложения следующих ограничений на таблицу ASSIGMENTS_N: 1.1. Значение поля PROJID должно быть меньше 2000. 1.2. Дата завершения проекта (A_END_DATE) должна быть позже его начала (A_START_DATE). 1.3. Допустимые типы назначения (ASSIGN_TYPE) - это PF, WT, ED. 1.4. Значение поля BULL_RATE должно быть меньше 50.00 при типе назначения PF, меньше 60.00 при типе назначения WT и меньше 70.00 при типе назначения ED. 1.5. Служебный номер (EMPNO) должен соответствовать таблице ЕМР. Не забудьте предложения WITH CHECK OPTION.

SQL> create view ogr_assigm as

2 select projid, a_start_date, a_end_date, asign_type, bull_rate,

3 empno

4 from assigments_5

5 where projid<2000

6 and a_end_date>a_start_date

7 and asign_type in ('PF', 'WT', 'ED')

8 end (bull_rate<50.00 and asign_type='PF')

9 or (bull_rate<60.00 and asign_type='WT')

10 or (bull_rate<70.00 and asign_type='ED'))

11 and empno in (select empno from emp)

12 with check option

13

SQL> /

View created.

Задание 1.9. Занесите несколько строк в таблицу ASSIGMENTS_N через созданное представление

SQL> insert into ogr_assigm_5

2 values (2, 7934, '08.03.89', '15.03.89', 60.00, 'ED', 20)

3 /

1 row created.

Commit complete.

SQL> insert into ogr_assigm_5

2 values (1, 7521, '04.04.90', '12.04.90', 48.00, 'WT', 32)

3 /

1 row created.

Commit complete.

SQL> insert into ogr_assigm_5

2 values (1, 7499, '18.04.90', '20.04.90', 65.00, 'ED', 25)

3 /

1 row created.

Commit complete.

Задание 1.10. Создайте представление, содержащее следующие сведения: номер отдела, среднюю зарплату, максимальную зарплату, минимальную зарплату, суммарную зарплату в отделе, количество людей, получающих зарплату, количество людей, получающих премию. Выполните запрос к представлению для просмотра его содержания. Имя представления View_N.

SQL> create view view_5 as

2 select deptno,

3 avg(sal) avg_salary,

4 max(sal) max_salary,

5 min(sal) min_salary,

6 sum(sal) sum_salary,

7 count(sal) num_emp,

8 count(comm) num_comm

9 from emp group by deptno

10

SQL> /

View created.

Задание 1.11. Воспользуйтесь созданным представлением для получения такой строки - EMPNO, ENAME, JOB, SAL, HIREDATE, минимальная, максимальная, средняя зарплата в отделе сотрудника, номер которого должен вводиться с клавиатуры.

SQL> select e.empno, e.ename, e.job, e.sal, e.hiredate,

2 v.max_salary, v.min_salary, v.avg_salary

3 from emp e, view_5 v

4 where e.deptno=v.deptno

5 and e.empno=&empno

6

SQL> /

Enter value for empno: 7782

old 5: and e.empno=&empno

new 5: and e.empno=7782

EMPNO ENAME JOB SAL HIREDATE MAX_SALARY MIN_SALARY

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

AVG_SALARY

----------

7782 CLARK MANAGER 2450 09.06.81 5000 1300

2916,66667

Задание 1.12. 1. Создать таблицу SOTRUD_N, где N - номер бригады (CREATE TABLE). 2. Просмотреть структуру таблицы (DESC SOTRUD_N). 3. Добавить в таблицу новый столбец (ALTER TABLE). 4. Модифицировать столбец. 5. Занести несколько новых строк в таблицу (INSERT). 6. Скопировать строки из таблицы SOTRUD_N другой бригады. 7. Исправить строку (UPDATE). 8. Удалить строку (DELETE). 9. Создать таблицу OTDEL_N, содержащий табельный номер, имя, должность и оклад сотрудников N-го отдела (CREATE TABLE ... AS SELECT). 10. Переименовать таблицу OTDEL_N в OTD_N (RENAME). 11. Удалить эту таблицу (DROP TABLE).

SQL> create table sotrud_5

2 (id_sotrud number(2));

Table created.

SQL> desc sotrud_5;

Name Null? Type

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

ID_SOTRUD NUMBER(2)

SQL> alter table sotrud_5 add (name_sotrud varchar2(30));

Table altered.

SQL> alter table sotrud_5 modify (id_sotrud number(4));

Table altered.

SQL> insert into sotrud_5 values (1, 'koval');

1 row created.

Commit complete.

SQL> insert into sotrud_5 values (2, 'nemov');

1 row created.

Commit complete.

SQL> insert into sotrud_5 values (3, 'petrov');

1 row created.

Commit complete.

SQL> insert into sotrud_5 (id_sotrud, name_sotrud)

2 select empno, job

3 from emp;

12 rows created.

Commit complete.

SQL> update sotrud_5 set name_sotrud='volcov' where id_sotrud=1;

1 row updated.

Commit complete.

SQL> delete from sotrud_5 where id_sotrud=2;

1 row deleted.

Commit complete.

SQL> create table otdel_5 (name, job, sal) as select ename, job, sal

2 from emp;

Table created.

SQL> rename otdel_5 to otd_5;

Table renamed.

SQL> drop table otd_5;

Table dropped.

Задание 1.13. Создать представление, соединяющее данные из таблиц EMP и DEPT с условием выборки WHERE emp.deptno IN (10, 30).

create view em_dept as select * from emp join dept on emp.deptno=dept.deptno

where emp.deptno in (10,30)

View created.

Задание 1.14. Создать в базе данных таблицу EMP1 идентичную таблице EMP. Создать последовательность с помощью команды SQL CREATE SEQUENCE для генерации номеров сотрудников для столбца EMPNO таблицы EMP1. Дать объяснение выбора основных параметров команды (INCREMENT BY, START WITH, NOMAXVALUE, NOCYCLE, CACHE 10); Сделайте изменения в последовательности командой SQL ALTER SEQUENCE с параметрами: ALTER SEQUENCE emp_sequence INCREMENT BY 10 MAXVALUE 10000 CYCLE CACHE 20; Как осуществляется обращение к последовательности в предложениях SQL? Как удалить последовательность? С помощью последовательности занести 3 строки в таблицу EMP1. Проверить результат.

SQL> create table emp1

2 (empno number(4) constraint pk_emp1 primary key,

3 ename varchar2(10),

4 job varchar2(10),

5 mgr number(4),

6 hiredate date,

7 sal number(7,2),

8 comm number(7,2),

9 deptno number(2) constraint fk_deptno1 references dept);

Table created.

SQL> create sequence seq_emp1 increment by 1

2 start with 1

3 nomaxvalue

4 /

Sequence created.

SQL> alter sequence seq_emp1 increment by 10 maxvalue 1000 cycle cache 20;

Sequence altered.

SQL> insert into emp1 values

2 (seq_emp1.nextval,

3 'petrov', 'master', 7839,'01.01.2009', 1000, 100, 10);

1 row created.

Commit complete.

SQL> insert into emp1 values

2 (seq_emp1.nextval,'ivanov','salsman',7698,'01.06.2009',1500,250,20);

1 row created.

Commit complete.

SQL> insert into emp1 values

2 (seq_emp1.nextval,'kuzmin','clerk',7902,'01.09.2010',1250,200,30);

1 row created.

Commit complete.

SQL> select * from emp1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

10 petrov master 7839 01.01.09 1000 100

10

20 ivanov salsman 7698 01.06.09 1500 250

20

30 kuzmin clerk 7902 01.09.10 1250 200

30

Задание 1.15. Подключиться к базе с логином scott (connect scott / tiger). Ввести команду grant SELECT on emp to public. Вернуться в свою базу (логин studXX). Создать синоним с помощью команды SQL CREATE SYNONYM с именем PUBLIC_EMP по таблице EMP, содержащейся в схеме пользователя SCOTT. Выберите данные из Public_EMP. Удалить ненужный синоним, используя команду SQL DROP SYNONYM.

SQL> connect scott/tiger

Connected.

SQL> grant select on emp to public;

Grant succeeded.

SQL> connect system/ksen

Connected.

SQL>

SQL> create synonym public_emp for scott.emp;

Synonym created.

SQL> select * from public_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17.12.80 800

20

7499 ALLEN SALESMAN 7698 20.02.81 1600 300

30

7521 WARD SALESMAN 7698 22.02.81 1250 500

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7566 JONES MANAGER 7839 02.04.81 2975

20

7654 MARTIN SALESMAN 7698 28.09.81 1250 1400

30

7698 BLAKE MANAGER 7839 01.05.81 2850

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7782 CLARK MANAGER 7839 09.06.81 2450

10

7839 KING PRESIDENT 17.11.81 5000

10

7844 TURNER SALESMAN 7698 08.09.81 1500 0

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7900 JAMES CLERK 7698 03.12.81 950

30

7902 FORD ANALYST 7566 03.12.81 3000

20

7934 MILLER CLERK 7782 23.01.82 1300

10

12 rows selected.

SQL> drop synonym public_emp;

Synonym dropped.