- •Задание 1.1. Подключиться к бд с логином / паролем studXx, где хх - номер группы, и запустить на выполнение скрипт scott_x.Sql. Что будет выполнено в результате его выполнения?
- •Задание 1.7. Занесите в таблицу assigments_n следующие данные:
- •Задание 1.16. Проверить или создать ограничения целостности, чтобы гарантировать, что каждый сотрудник в таблице emp значится в одном из отделов, перечисленных в таблице dept.
- •Задание 1.17. Определить и дать объяснение ограничения целостности в команде create table:
- •Задание 2.6. Составить команду sql для выборки данных о сотрудниках 20 и 30 отделов по алфавиту по их именам.
- •Задание 2.7. Составить команду sql для выборки имен служащих и их окладов для всех менеджеров в 10 и 20 отделах.
- •Задание 2.8. Составить команду sql для нахождения всех имен сотрудников содержащие комбинации символов "th" или "ar".
- •Задание 2.9. Составить команду sql для выдачи имен служащих, их должностей и окладов имеющих менеджера.
- •Задание № 3.7. Выберите имя, должность, оклад, категорию оклада, наименование отдела для всех сотрудников компании, кроме реализаторов. Организуйте строки по зарплате в порядке убывания.
- •Задание № 3.8. Выдайте следующую информацию о служащих, получивших доход от 2000 до 3000, а также по всем реализаторами.
- •Задание № 3.11. Модифицируйте запрос 10 так, чтобы в таблице результатов появился king, который не имеет менеджера.
- •Заполнение таблицы Услуги:
- •Создание запросов:
Задание 2.9. Составить команду sql для выдачи имен служащих, их должностей и окладов имеющих менеджера.
SQL> select ename, job, sal from emp where mgr is not null;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
TURNER SALESMAN 1500
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
11 rows selected.
Задание 2.10. Составить команду SQL для получения имен и годового дохода всех служащих с учетом премии.
SQL> select ename, nvl(comm,0), (sal+comm)*12 dohod from emp;
ENAME NVL(COMM,0) DOHOD
---------- ----------- ----------
SMITH 0
ALLEN 300 22800
WARD 500 21000
JONES 0
MARTIN 1400 31800
BLAKE 0
CLARK 0
KING 0
TURNER 0 18000
JAMES 0
FORD 0
ENAME NVL(COMM,0) DOHOD
---------- ----------- ----------
MILLER 0
12 rows selected.
Задание 2.11. Составить команду SQL для получения данных о всех сотрудников, зачисленных на работу летом 1981 года.
SQL> select * from emp where to_char(hiredate,'YYYY') = 1981 and to_char(hiredate,'MM') between '06' and '08';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ------
7782 CLARK MANAGER 7839 09.06.81 2450 10
Задание 2.12. Составить команду SQL для выборки имени, годовой зарплаты и премий для всех реализаторов, у которых месячное жалованье превосходит премию. Отсортировать строки по значению оклада в порядке убывания. Если несколько служащих получают одинаковую зарплату, то в пределах строк с одинаковой зарплатой упорядочение их по именам сотрудников.
SQL> select ename, sal*12 godovay_zarplata, comm from emp
where sal>comm order by sal desc, ename;
ENAME GODOVAY_ZARPLATA COMM
---------- ---------------- ----------
ALLEN 19200 300
TURNER 18000 0
WARD 15000 500
Задание 2.13. Составить команду SQL для получения таблицы результатов, как показано ниже:
Кто, где и когда
-------------------------------------------------- -----------------------------------
SMITH работает как CLERK в 20 отделе с 17.12.1998
SQL> select ename, 'works as ', job, ' in ', deptno, ' department since ', hiredate from emp;
ENAME 'WORKSAS' JOB 'IN' DEPTNO 'DEPARTMENTSINCE' HIREDATE
---------- --------- --------- ---- ---------- ------------------ --------
SMITH works as CLERK in 20 department since 17.12.80
ALLEN works as SALESMAN in 30 department since 20.02.81
WARD works as SALESMAN in 30 department since 22.02.81
JONES works as MANAGER in 20 department since 02.04.81
MARTIN works as SALESMAN in 30 department since 28.09.81
BLAKE works as MANAGER in 30 department since 01.05.81
CLARK works as MANAGER in 10 department since 09.06.81
KING works as PRESIDENT in 10 department since 17.11.81
TURNER works as SALESMAN in 30 department since 08.09.81
JAMES works as CLERK in 30 department since 03.12.81
FORD works as ANALYST in 20 department since 03.12.81
MILLER works as CLERK in 10 department since 23.01.82
12 rows selected.
Задание 2.14. Составить команду SQL для внесения информации о двух новых сотрудников отдела № 40 с номерами "xx1", "хх2" и должностями "программист", где "хх"- номер варианта. Зафиксировать транзакцию и просмотреть результаты.
SQL> insert into emp(empno,job,deptno) values(261, 'PROGRAMST', 40);
1 row created.
Commit complete.
SQL> insert into emp(empno,job,deptno) values(262, 'PROGRAMST', 40);
1 row created.
Commit complete.
SQL> select * from 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
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
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
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
261 PROGRAMST 40
262 PROGRAMST
14 rows selected.
Задание 2.15. После выполнения Задание 14 составить команду SQL для восстановления строк в таблице EMP, заменив должности "программист" на "администратор " для сотрудников с номерами "хх", где "хх"- номер варианта. Зафиксировать транзакцию и просмотреть результаты.
SQL> update emp set job='admin' where empno like '26%';
2 rows updated.
Commit complete.
Задание 16
После выполнения задачи 15 составить команду SQL которая создает таблицу EMP_COPY на основе всех данных таблицы EMP и таблицу JOBS из двух полей "EMPNO" и "JOB"на основе данных таблицы EMP. Зафиксировать транзакции и просмотреть результаты.
SQL> create table emp_copy as select * from emp;
Table created.
SQL> select * from emp_copy;
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
261 admin
40
262 admin
40
SQL> create table jobs as select empno, job from emp;
Table created.
SQL> select * from jobs;
EMPNO JOB
---------- ---------
7369 CLERK
7499 SALESMAN
7521 SALESMAN
7566 MANAGER
7654 SALESMAN
7698 MANAGER
7782 MANAGER
7839 PRESIDENT
7844 SALESMAN
7900 CLERK
7902 ANALYST
EMPNO JOB
---------- ---------
7934 CLERK
261 admin
262 admin
Задание 17
После выполнения задачи 16 составить команду SQL для удаления из таблицы "EMP_COPY" все строки, в которых в полях с номером отдела и номером служащего значится идентификатор вида "хх", где хх - номер варианта. Зафиксировать транзакцию и просмотреть результаты.
SQL> delete from emp_copy where empno like '%07%'
2 or deptno like '%07%';
0 rows deleted.Commit complete.
Задание № 3.1. Найдите имена всех служащих и их отделов. Отсортируйте результаты по наименованию отделов.
SQL> select e.ename, d.dname from emp e, dept
2 where e.deptno=d.deptno order by d.dname;
ENAME DNAME
---------- --------------
CLARK ACCOUNTING
MILLER ACCOUNTING
KING ACCOUNTING
OPERATIONS
OPERATIONS
FORD RESEARCH
JONES RESEARCH
SMITH RESEARCH
ALLEN SALES
WARD SALES
JAMES SALES
ENAME DNAME
---------- --------------
BLAKE SALES
MARTIN SALES
TURNER SALES
14 rows selected.
Задание № 3.2. Выберите имена всех служащих, номера и наименования их отделов.
SQL> select e.ename, d.dname, d.dept
2 from emp e, dept d
3 where e.deptno=d.deptno;
ENAME DNAME DEPTNO
---------- -------------- ----------
SMITH RESEARCH 20
ALLEN SALES 30
WARD SALES 30
JONES RESEARCH 20
MARTIN SALES 30
BLAKE SALES 30
CLARK ACCOUNTING 10
KING ACCOUNTING 10
TURNER SALES 30
JAMES SALES 30
FORD RESEARCH 20
ENAME DNAME DEPTNO
---------- -------------- ----------
MILLER ACCOUNTING 10
OPERATIONS 40
OPERATIONS 40
14 rows selected.
Задание № 3.3. Выдайте имя служащего, расположение и наименование его отдела для всех сотрудников, у которых зарплата превышает 1500.
SQL> select e.ename, d.loc, d.dname
2 from emp e, dept d
3 where e.deptno=d.deptno
4 and e.sal>1500;
ENAME LOC DNAME
---------- ------------- ----------
ALLEN CHICAGO SALES
JONES DALLAS RESEARCH
BLAKE CHICAGO SALES
CLARK NEW YORK ACCOUNTING
KING NEW YORK ACCOUNTING
FORD DALLAS RESEARCH
6 rows selected.
Задание № 3.4. Сформируйте таблицу, отражающую градацию служащих по уровню их зарплаты.
SQL> select e.ename, e.sal, s.grade, s.losal, s.hisal from emp e, salgrade s
where e.sal between s.losal and s.hisal;
ENAME SAL GRADE LOSAL HISAL
---------- ---------- ---------- ---------- ----------
SMITH 800 1 700 1200
JAMES 950 1 700 1200
WARD 1250 2 1201 1400
MARTIN 1250 2 1201 1400
MILLER 1300 2 1201 1400
ALLEN 1600 3 1401 2000
TURNER 1500 3 1401 2000
JONES 2975 4 2001 3000
BLAKE 2850 4 2001 3000
CLARK 2450 4 2001 3000
FORD 3000 4 2001 3000
KING 5000 5 3001 9999
12 rows selected.
Задание № 3.5. Выдайте только служащих третьей категории оплаты.
SQL> select e.ename, e.sal, s.grade from emp e, salgrade s
2 where e.sal between s.losal and s.hisal
3 and s.grade = 3;
ENAME SAL GRADE
---------- ---------- ----------
ALLEN 1600 3
TURNER 1500 3
Задание № 3.6. Найдите всех, кто работает в Далласе.
SQL> select e.*, d.loc from emp e, dept d where e.deptno=d.deptno and d.loc='DALLAS';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO LOC
---------- ---------- ------ ---------- -------- ---------- ------ --
7369 SMITH CLERK 7902 17.12.80 800 20 DALLAS
7566 JONES MANAGER 7839 02.04.81 2975 20 DALLAS 7902 FORD ANALYST 7566 03.12.81 3000 20 DALLAS