Ход работы Задание 1

Создать БД Научные конференции который содержит таблицы УЧЕНЫЙ (код ученого, фамилия и имя, ученая степень, организация, страна) КОНФЕРЕНЦИЯ (код конференции, название, дата проведения, место проведения) УЧАСТИЕ (код ученого, код

конференции, тип участия, тема доклада (сообщения), публикация (да/нет)) и сделать на основе данной БД SQL запросы:

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

2 Определить количество докторов наук, участвовавших в каждой

конференции.

Для решения поставленной задачи нужно создать БД а так же создать внутри нашей БД три таблицы

Рисунок 1 — Создание подключения

Рисунок 2 — Модель БД

Рисунок 3-Заполненая таблица конференций

Рисунок 4-Заполненая таблица участие

Рисунок 5-Заполненая таблица ученые

Для нахождения конференций, где ни один участник не представил публикацию мы пишем запрос за языке SQL – сперва используется оператор SELECT DISTINCT, который выбирает уникальные названия конференций из таблицы CONFERENCE. Для фильтрации результатов используется оператор WHERE NOT EXISTS. Он проверяет, существуют ли записи в таблице PARTICIPATION, где значение Publication равно 'Да' и ConferenceCode совпадает с ConferenceCode в таблице CONFERENCE. Если таких записей не существует, то название конференции добавляется в результат выборки. Подзапрос SELECT * FROM PARTICIPATION WHERE CONFERENCE.ConferenceCode = PARTICIPATION.ConferenceCode AND Publication = 'Да' используется для проверки наличия публикаций участников на каждой конференции. В результате выполнения данного кода мы получим список названий конференций, где ни один участник не представил публикацию.

Рисунок 6-Первый запрос

Для определения количества докторов наук, участвовавших в каждой конференции, используем оператор SELECT, который выбирает название конференции и количество докторов наук, участвовавших в ней. Функция COUNT(*) используется для подсчета количества строк, соответствующих условиям запроса. В следующей строке мы объединяем таблицы PARTICIPATION, CONFERENCE и SCIENTIST, используя операторы JOIN. Оператор JOIN используется для связывания строк в таблицах, которые имеют общие значения в определенных столбцах. Мы связываем таблицу PARTICIPATION с таблицами CONFERENCE и SCIENTIST, используя значения ConferenceCode и ScientistCode. Затем мы добавляем условие WHERE, которое фильтрует только докторов наук. Условие scientist.Degree = 'Доктор наук' выбирает только те строки из таблицы SCIENTIST, где значение столбца Degree равно 'Доктор наук'. В конце мы группируем результаты по названию конференции с помощью оператора GROUP BY conference.ConferenceName. Это группирует строки таблицы по значениям в столбце ConferenceName. В результате выполнения данного запроса мы получаем список конференций и количество докторов наук, участвовавших в каждой конференции.

Рисунок 7-Второй запрос

Задание 2 Создать БД рекламного агенства в котором есть таблицы :

1 Сотрудники с колонками код сотрудника, ФИО, дата рождения, пол, телефон, код должности

2 Должности с колонками код должности, наименование должности, оклад.

3 Рекламные конструкции с колонками код конструкции, номер конструкции, адрес, стоимость для аренды одной стороны конструкции.

4 Рекламные места с колонками код места, код конструкции, сторона конструкции.

5 Рекламодатели с колонками код рекламодателя, наименование рекламодателя, адрес, телефон.

6 Договоры аренды с колонками номер договора, дата начала, дача окончания, код рекламодателя, код сотрудника, отметка об оплате.

7 Списки арендуемых мест с колонками код договора, код рекламного места, описание рекламы.

Нужно составить к ним запросы:

1. Список сотрудников, заданного возраста с указанием всех реквизитов.

2. Список рекламных мест с указанием всех реквизитов.

3. Список договоров с указанием всех реквизитов и общей стоимости аренды.

4. Список арендуемых по заданному договору рекламных мест с указанием всех реквизитов.

5. Список неоплаченных договоров с указанием всех реквизитов.

6. Список из 3 наиболее популярных рекламных конструкций.

Для решение данной задачи нужно создать БД а так же создать таблицы и заполнить их

Рисунок 8- создание подключения БД

Рисунок 9 — Модель БД Рисунок 10-Таблица договоры аренды Рисунок 11-Таблица должности Рисунок 12-Таблица рекламные конструкции Рисунок 13-Таблица рекламные места Рисунок 14-Таблица рекламодатели Рисунок 15-Таблица сотрудники Рисунок 16-Таблица список арендуемых мест

Для получения списка сотрудников заданного возраста с указанием всех реквизитов можно использовать следующий запрос:

Оператор SELECT *, который выбирает все столбцы из таблицы. Оператор FROM сотрудники указывает, из какой таблицы мы выбираем данные. Условие WHERE YEAR(CURRENT_DATE()) - YEAR(ДатаРождения) = 33 использует функцию YEAR(), чтобы извлечь год из столбца ДатаРождения, а затем вычитает его из текущего года, который возвращает функция CURRENT_DATE(). Разница между текущим годом и годом рождения дает возраст сотрудника, который сравнивается с заданным. В результате выполнения данного запроса мы получим список всех сотрудников, возраст которых равен заданному. Рисунок 17-Запрос один

Для вывода списка рекламных мест с указанием всех реквизитов, можно использовать следующий запрос:

Оператор SELECT * выбирает все столбцы из результатов запроса. Условие FROM рекламныеместа указывает, что мы выбираем данные из таблицы рекламныеместа. Затем оператор INNER JOIN соединяет таблицы рекламныеместа и рекламныеконструкции, используя условие ON рекламныеместа.КодКонструкции = рекламныеконструкции.КодКонструкции, где КодКонструкции - это общее поле, связывающее две таблицы. Это означает, что будут выбраны только те строки, в которых значения поля КодКонструкции совпадают в обеих таблицах. В результате выполнения данного запроса мы получим список всех рекламных мест вместе с информацией о соответствующих рекламных конструкциях, к которым они принадлежат.

Рисунок 18-Запрос два

Для получения списка договоров с указанием всех реквизитов и общей стоимости аренды, можно использовать следующий запрос:

Запрос выбирает все столбцы из таблицы договорыаренды и вычисляет общую стоимость аренды для каждого договора. Оператор SUM используется для вычисления суммарной стоимости аренды каждого рекламного места, а оператор DATEDIFF используется для вычисления количества дней аренды.

В запросе используется несколько операторов JOIN для связывания таблиц между собой. Оператор JOIN связывает таблицы договорыаренды, сотруднкики, рекламодатели, спискиарендуемыхмест, рекламныеместа и рекламныеконструкции. Каждая таблица связывается с другой по общим полям. Например, таблица спискиарендуемыхмест связывается с таблицей договорыаренды по полю КодДоговора, а таблица рекламныеконструкции связывается с таблицей рекламныеместа по полю КодКонструкции.

Затем используется оператор GROUP BY, который группирует результаты запроса по полю НомерДоговора. Это означает, что для каждого уникального значения поля НомерДоговора будет вычислена суммарная стоимость аренды.

В результате выполнения запроса мы получим список всех договоров аренды рекламных мест вместе с общей стоимостью аренды каждого места, связанных с соответствующими сотрудниками, рекламодателями и конструкциями.

Рисунок 19-Запрос три

Для получения списка арендуемых по заданному договору рекламных мест с указанием всех реквизитов используем следующий запрос:

В первой строке после SELECT указаны поля, которые должны быть включены в результирующую таблицу. Здесь указаны все поля из таблицы спискиарендуемыхмест, а также несколько полей из других таблиц.

В строке FROM перечислены таблицы, которые будут использованы в запросе. Здесь мы выбираем данные из таблиц спискиарендуемыхмест, рекламныеместа, рекламныеконструкции, договорыаренды и рекламодатели.

В строке JOIN указаны условия для соединения таблиц. Здесь мы соединяем таблицы спискиарендуемыхмест и рекламныеместа по полю КодРекламногоМеста, таблицы рекламныеместа и рекламныеконструкции по полю КодКонструкции, таблицы спискиарендуемыхмест и договорыаренды по полю НомерДоговора и таблицы договорыаренды и рекламодатели по полю КодРекламодателя.

В строке WHERE указывается условие, которому должны соответствовать записи, чтобы они были включены в результирующую таблицу. Здесь мы выбираем только те записи из таблицы спискиарендуемыхмест, которые соответствуют коду договора равному 4.

В результате выполнения данного запроса мы получим информацию о всех местах аренды, которые включены в договор с кодом 4. Кроме того, для каждого места аренды будут указаны соответствующие конструкция, рекламодатель и другая информация, которая указана в запросе. Рисунок 20-Запрос четыре

Для получения списка неоплаченных договоров с указанием всех реквизитов можно использовать следующий запрос:

Оператор SELECT *, который выбирает все столбцы из таблицы. Оператор FROM договорыаренды указывает, из какой таблицы мы выбираем данные. Условие WHERE ОтметкаОбОплате = 'Нет' фильтрует результаты запроса, оставляя только те строки, где значение столбца ОтметкаОбОплате равно 'Нет'. В результате выполнения данного кода мы получим список всех договоров аренды, где еще не была поставлена отметка об оплате.

Рисунок 21-Запрос пять

Для получения списка из 3 наиболее популярных конструкций можно использовать следующий запрос:

Производится объединение таблиц рекламных конструкций, рекламных мест и списков арендуемых мест по их соответствующим ключам. Затем используется оператор GROUP BY для группировки записей по кодам рекламных конструкций и вычисления количества аренд, которые относятся к каждой конструкции с помощью функции COUNT(). Результаты сортируются в порядке убывания количества аренд, и ограничиваются выводом только трех записей с помощью оператора LIMIT. Рисунок 22-Запрос шесть