Разработка базы данных на языке SQL
Руководитель:
___________ Борисова Ю.Л.
(подпись)
__________________________
(оценка, дата)
Выполнил:
Студент группы ПРО-31
____________ В.И.Климов
(подпись)
__________________________
(дата)
Абакан, 2019 г.
Задание: Разработайте базу данных “Учёта проектов”. В отчете отразите схему данных, скрипты на добавление базы и таблиц, запросы к базе.
Схема данных:
Скрипт запросов на создание базы данных и таблиц в SQL:
CREATE DATABASE [йньи ёжеыбиеш];
use [йньи ёжеыбиеш];
CREATE TABLE Заказчики
(IDЗаказчика INT NOT NULL IDENTITY(1,1),
Фамилия VARCHAR(20), Имя VARCHAR(20), Отчество VARCHAR(20),
Телефон VARCHAR(14),
Почта VARCHAR(30),
PRIMARY KEY (IDЗаказчика))
CREATE TABLE Проектирование
(IDПроектирования INT NOT NULL IDENTITY(1,1),
Название VARCHAR(20), Описание VARCHAR(20), Заказчик INT,
ДатаНачала DATE,
ДатаКонца DATE,
PRIMARY KEY (IDПроектирования),
FOREIGN KEY (Заказчик) REFERENCES Заказчики(IDЗаказчика))
CREATE TABLE Роль
(IDРоли INT NOT NULL IDENTITY(1,1),
Роль VARCHAR(20),
PRIMARY KEY (IDРоли))
CREATE TABLE Сотрудники
(IDСотрудника INT NOT NULL IDENTITY(1,1),
Фамилия VARCHAR(20), Имя VARCHAR(20), Отчество VARCHAR(20),
Телефон VARCHAR(18),
Почта VARCHAR(30),
PRIMARY KEY (IDСотрудника))
CREATE TABLE РолиПроекта
(IDЗаписи INT NOT NULL IDENTITY(1,1),
IDПроектирования INT,
Роль INT,
Сотрудник INT,
Отзыв VARCHAR(40),
PRIMARY KEY (IDЗаписи),
FOREIGN KEY (IDПроектирования) REFERENCES Проектирование(IDПроектирования),
FOREIGN KEY (Роль) REFERENCES Роль(IDРоли),
FOREIGN KEY (Сотрудник) REFERENCES Сотрудники(IDСотрудника))
Скрипты запросов на добавление данных в таблицы:
INSERT INTO Заказчики VALUES('Сорокин', 'Евстигней', 'Сергеевич', '8 (910) 727-57-82', 'SorokinEvstigney363@gmail.com')
INSERT INTO Заказчики VALUES('Денисова', 'Ульяна', 'Валерьевна', '8 (968) 315-18-69', 'DenisovaUlyana166@ya.ru')
INSERT INTO Заказчики VALUES('Рощин', 'Парамон', 'Валентинович', '8 (966) 185-76-59', 'RoschinParamon102@yahoo.com')
INSERT INTO Заказчики VALUES('Баженов', 'Фотий', 'Владимирович', '8 (970) 908-11-21', 'BajenovFotiy348@gmail.com')
INSERT INTO Заказчики VALUES('Чистяков', 'Никита', 'Миронович', '8 (929) 230-48-45', 'ChistyakovNikita137@gmail.com')
INSERT INTO Проектирование VALUES('Цниуиредху', 'Описание проекта "Цниуиредху"', 1, '2013.06.11', '2015.01.22')
INSERT INTO Проектирование VALUES('Бугбафей', 'Описание проекта "Бугбафей"', 2, '2014.02.26', '2014.11.03')
INSERT INTO Проектирование VALUES('Чрапиауна', 'Описание проекта "Чрапиауна"', 3, '2015.08.02', '2019.03.30')
INSERT INTO Проектирование VALUES('Лауналуга', 'Описание проекта "Лауналуга"', 4, '2019.04.17', NULL)
INSERT INTO Проектирование VALUES('Всётагюйе', 'Описание проекта "Всётагюйе"', 4, '2021.05.13', NULL)
INSERT INTO Роль VALUES('Веб-дизайнер')
INSERT INTO Роль VALUES('Верстальщик')
INSERT INTO Роль VALUES('Тестировщик')
INSERT INTO Роль VALUES('Системный администратор')
INSERT INTO Роль VALUES('Программист')
INSERT INTO Сотрудники VALUES('Трофимов','Семён','Вениаминович','8 (906) 220-17-76','TrofimovSemen271@mail.ru')
INSERT INTO Сотрудники VALUES('Холодков','Ферапонт','Альбертович','8 (914) 964-37-11','HolodkovFerapont183@ya.ru')
INSERT INTO Сотрудники VALUES('Лапина','Мария','Владимировна ','8 (918) 561-65-36','Dvvy9h5mmaEN@mail.ru')
INSERT INTO Сотрудники VALUES('Соболев',' Глеб','Павлович','8 (959) 979-17-75','SobolevGleb238@gmail.com')
INSERT INTO Сотрудники VALUES('Фомов','Иннокентий','Викторович','8 (937) 609-67-19','FomovInnokentiy74@inbox.ru')
INSERT INTO РолиПроекта VALUES(1,1,5,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(1,3,1,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(2,2,1,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(2,4,3,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(3,3,1,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(3,4,5,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(4,5,1,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(4,3,2,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(5,5,5,'Отзыв... ')
INSERT INTO РолиПроекта VALUES(5,1,5,'Отзыв (вставить текст)')
Запрос на вывод номеров всех сотрудников:
SELECT DISTINCT Имя from Сотрудники
Запрос на выбор всех проектов проводимых в промежутке 13 и 16 года:
SELECT IDПроектирования, Название, Описание, Заказчик, ДатаНачала, ДатаКонца
FROM Проектирование
WHERE ДатаНачала BETWEEN '2013.01.01' AND '2016.12.31'
AND
ДатаКонца BETWEEN '2013.01.01' AND '2016.12.31'
Запрос на вывод заказчиков пользующихся почтой Gmail:
SELECT DISTINCT Почта from Заказчики WHERE Почта LIKE '%@gmail.com'
Запрос на вывод проектов в которых не учавствовал Трофимов Семён Вениаминович и Фомов Иннокентий Викторович:
SELECT * FROM РолиПроекта WHERE Сотрудник NOT IN (1,5)
Запрос на вывод заказчиков и их проектов:
SELECT Заказчики.Фамилия, Проектирование.Название FROM Заказчики, Проектирование
WHERE Заказчики.IDЗаказчика = Проектирование.Заказчик
Запрос на вывод кол-ва проектов каждого заказчика без повторов:
SELECT Заказчики.Фамилия, COUNT(Проектирование.Название) AS 'Кол-во (шт.)'FROM Заказчики, Проектирование
WHERE Заказчики.IDЗаказчика = Проектирование.Заказчик
GROUP BY Заказчики.Фамилия
Запрос на вывод кол-ва проектов каждого заказчика, кол-во проектов больше либо равно 2:
SELECT Заказчики.Фамилия, COUNT(Проектирование.Название) AS 'Кол-во (шт.)'FROM Заказчики, Проектирование
WHERE Заказчики.IDЗаказчика = Проектирование.Заказчик
GROUP BY Заказчики.Фамилия
HAVING COUNT(Проектирование.Название) >= 2
--(СТОРОННИЙ ПРИМЕР ДЛЯ РАЗБОРА ЗАЧЕМ НУЖЕН HAVING)
--Получить количество ПК и среднюю цену для каждой модели, средняя цена которой менее $800
--SELECT model, COUNT(model) AS Qty_model,
-- AVG(price) AS Avg_price
--FROM PC
--GROUP BY model
--HAVING AVG(price) < 800;