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

Voditeli

Поз. вод	ФИО	№ вод.прав	Тел-н	Год нач. сотр-ва	АТП	Прописка	Год рождения	Паспорт
	Иванов					Постоянная
	Гачик					Временная
	Степанов					Постоянная
	Кравец					Временная
	Шмелёв					нет

Таблица "Voditeli" находится в первой нормальной форме (в ней нет сложных неразбиваемых атрибутов), во второй нормальной форме (нет функциональных зависимостей), в третьей нормальной форме (нет транзитивной зависимости).

Bus

Гос №	Марка	ТО	АТП	Штраф (№ протокола)
р010но	Golden Drakon	11.09.2013
н113ов	ZIL	8.10.2011
м158хр	Golden Drakon	3.09.2012
р256рт	Hern	3.12.2013
о782он	ZIL	31.09.2010

Таблица "Bus" находится в первой нормальной форме (в ней нет сложных неразбиваемых атрибутов), во второй нормальной форме (нет функциональных зависимостей), в третьей нормальной форме (нет транзитивной зависимости).

Marshrut

№ марш	Нач. точка	Конеч. точка	Время в пути (мин)	Кол-во остановок	Гос № авт
	Дербышки	Ленина			О782он
	Халева	Габишева			Р256рт
	Гаврилова	Гаврилова			М158хр
	Максимова	Тинчурина			Н113ов
	РКБ	Кольцо			Р010но

Таблица "Marshrut" находится в первой нормальной форме (в ней нет сложных неразбиваемых атрибутов), во второй нормальной форме (нет функциональных зависимостей), в третьей нормальной форме (нет транзитивной зависимости).

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

Создание пользователя и выделение ему привилегий:

CREATE USER Administrator

IDENTIFIED BY password21;

GRANT all privilege

TO Administrator;

Создание таблиц с ограничениями:

CREATE TABLE Voditeli

(Поз.вод NUMBER(6) PRIMARY KEY,

ФИО VARCHAR2(50) NOT NULL,

№ вод.прав NUMBER (10) NOT NULL,

Телефон VARCHAR2(10) UNIQUE,

Год начала сотрудничества VARCHAR2(6) NOT NULL,

АТП VARCHAR2(3) NOT NULL,

Прописка VARCHAR2(20) NOT NULL,

Год рождения NUMBER(4) UNIQUE,

Паспорт NUMBER(50) UNIQUE);

CREATE TABLE Bus

(Гос. № VARCHAR2 (6) PRIMARY KEY,

Марка VARCHAR2(30) NOT NULL,

ТО VARCHAR2(50) NOT NULL,

АТП VARCHAR2(3) UNIQUE,

Штраф NUMBER(50) NOT NULL);

CREATE TABLE Marshrut

(№ марш. NUMBER(3) PRIMARY KEY,

Нач. точка VARCHAR2(30) NOT NULL,

Конеч. точка VARCHAR2(50) NOT NULL,

Время в пути(м) VARCHAR2(10) UNIQUE,

Кол-во останов. VARCHAR2(10) NOT NULL,

Гос.№ авт. VARCHAR2(500) NOT NULL);

Заполнение таблиц:

INSERT INTO Voditeli (Поз.вод, ФИО, № вод. прав, Телефон, Год начала сотрудничества, АТП, Прописка, Год рождения, Паспорт)

VALUES (100, ‘Иванов’, ‘123456’, ‘2318217’, ‘2007’, ‘1’, ‘постоянная’, ‘1960’, ‘64261357’);

INSERT INTO Voditeli

VALUES (200, ‘Гачик’, ‘654321’, ‘8281917’, ‘2001’, ‘2’, ‘временная’, ‘1976’, ‘97123612’);

INSERT INTO Pisateli

VALUES (300, ‘Степанов’, ‘645321’, ‘9215368’, ‘2008’, ‘3’, ‘постоянная’, ‘1988’, ‘26243311’);

INSERT INTO Voditeli

VALUES (400, ‘Кравец’, ‘601543’, ‘5331279’, ‘2006’, ‘4’, ‘временная’, ‘1969’, ‘42565621);

INSERT INTO Voditeli

VALUES (500, ‘Шмелёв’, ‘302532’, ‘6282366’, ‘2012’, ‘5’, ‘нет’, ‘1973’, ‘43254363’);

INSERT INTO Bus (Гос.№, Марка, ТО, АТП, Штраф(номер протокола))

VALUES (р010но, ‘Golden Drakon’, ’11.09.2013’, ‘3’, ‘12’);

INSERT INTO Bus

VALUES (н113ов, ‘ZIL’, ‘8.10.2011’, ‘2’, ‘13’);

INSERT INTO Bus

VALUES (м158хр, ‘Golden Drakon’, ‘3.09.2012’, ‘4’, ‘87’);

INSERT INTO Bus

VALUES (р256рт, ‘Hern’, ‘3.12.2013’, ‘1’, ‘982’);

INSERT INTO Bus

VALUES (о782он, ‘ZIL’, ’31.09.2010’, ‘9’, ‘324’);

INSERT INTO Marshrut (№ марш, Нач. точка, Конеч. точка, Время в пути(мин), Кол-во остановок, Гос.№ авт)

VALUES (86, ‘Дербышки’, ‘ Ленина’, ‘120’, ‘10’, ‘о782он’);

INSERT INTO Marshrut

VALUES (53, ‘Халева’, ‘Габишева’, ‘180’, ‘28’, ‘р256рт’);

INSERT INTO Marshrut

VALUES (33, ‘Гаврилова’, ‘Гаврилова’, ‘120’, ‘19’, ‘м158хр’);

INSERT INTO Marshrut

VALUES (32, ‘Максимова’, ‘Тинчурина’, ‘90’, ‘17’, ‘н113ов’);

INSERT INTO Marshrut

VALUES (8, ‘РКБ’, ‘Кольцо’, ‘60’, ‘20’, ‘р010но’);

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

1. Выведите на дисплей фамилию, зарплату, и комиссионные для всех работников, которые зарабатывают комиссионные. Отсортировать данные в порядке зарплаты и комиссионных.

SELECT First_name, salary, commission_pct

FROM employees

Where commission_pct>0

ORDER BY salary, commission asc;

2. Для каждого работника, вывести идентификационный номер, фамилию, зарплату, заработную плату и зарплату с учетом надбавки на 15%.

SELECT employee_id, first_name, salary, salary+salary*0,15

FROM employees;

3. Написать запрос для отображения работника с указанием фамилии, департамента наименование, местонахождение ID, и город для всех работников, которые зарабатывают комиссионные.

Select employees.first_name, departments.department_name, departments.location_id, locations.city

From employees, departments, locations

Where employees.department_id=departments.department_id, departments.location_id=locations.location_id;

4. Написать запрос для отображения фамилии и даты (начала работы) для всех сотрудников, работающих в том же департаменте, где работает сотрудник Zlotkey.

SELECT firs_name, hire_date

FROM employees

WHERE departments_id =

(select departments_id

From employees

Where firs_name=Zlotkey);

5. Написать запрос, который отображает номера и фамилии всех работников. При этом они работают в департаменте с любым сотрудником, чья фамилия содержит букву u.

Select employee_id, first_name

From employees

Where departments_id=

(select departments_id

From employees

Where firs_name in (‘*u*’));

6. Написать запрос для отображения видов работ и количество людей связанных с этой работой.

Select departments.department_name, count( employee_id)

From employees, departments

Where employees.department_id=departments.department_id

Group by department_id;

7. Написать запрос для отображения номера департамента, количества работников, средней зарплаты для всех работников этого департамента.

Select E1.department_id, count(E1.employee_id), AVG(E1.salary)

From employees E1, employees E2

Where E1 department_id IN

(Select E2.department_id,

From E2

Where E2.department_id = E1.department_id);

8.1 Создание представлений из одной таблицы.

Create or replace view стаж

As select фамилия, год начала сотрудничества

from voditeli

Where год начала сотрудничества<2013;

8.2 Создание представлений из двух таблиц.

Create or replace view marshrut33

As select marshut.№марш=33, bus.№автобуса

From marhrut, bus

Where marshrut.гос.№ авт.=bus.гос.№;

8.3 Создание индекса.

CREATE INDEX bus_idx

ON bus(штраф);