1. Создать запрос к таблице ЗАКАЗ, содержащий сведения о заказах, сделанных в течение последних 200 дней. Включить в запрос данные: номер заказа и количество. Сохранить под именем З1.
2. Создать запрос к таблице КЛИЕНТ, содержащий сведения о клиентах, с которыми фирма начала работать в 2007 году. Включить в запрос данные: год начала работы с клиентом, имя клиента, город, телефон, оборот. Сохранить под именем К1.
3. Создать запрос к таблице КЛИЕНТ, содержащий сведения о клиентах из Москвы и Екатеринбурга, упорядоченный по городу и имени клиента. Включить в запрос данные: имя клиента, город, телефон, оборот, год начала работы с клиентом. Сохранить под именем К2.
4. Создать запрос к таблице ЗАКАЗ, включающий номер клиента, общий объем заказов клиента фирме в денежном выражении, количество заказов клиента фирме. Сохранить под именем З2.
Задание 4. Запросы к связанным таблицам
1. Список клиентов (имена, города, телефон, номер заказа, дата заказа, артикул товара), делавших заказы на товар с указанным артикулом (сделать артикул параметром запроса). Сохранить под именем ЗСТ1.
2. Список всех заказов их стоимость с учетом скидки. Сохранить под именем ЗСТ2.
3. Список неоплаченных выполненных заказов с указанием номера заказа, имен клиентов, сделавшего заказ, даты заказа, артикула товара, группы товара и объёма заказа в денежном выражении. Сохранить под именем ЗСТ3.
4. Список клиентов - должников (номер клиента, имя клиента, город) с указанием общей суммы долга каждого клиента фирме и количества неоплаченных им заказов, упорядоченный по общей сумме долга. Сохранить под именем ЗСТ4.
5. Список всех товаров, включающих поля артикул, количество, продано, остаток. Сохранить под именем ЗСТ5.
6. Список всех клиентов с указанием количества заказов и общей суммы заказов каждого клиента фирме. Сохранить под именем ЗСТ6.
Задание 5. Перекрёстные запросы
1. Построить таблицу распределения объемов заказов по клиентам (по строкам номер клиента) и годам. Сохранить под именем ЗП1.
2. Построить таблицу распределения количества клиентов по городам и степеням надежности. Сохранить под именем ЗП2.
3. Построить таблицу распределения объемов заказов по годам и месяцам, включив в запрос итоги по строкам – общие продажи в каждом году. Сохранить под именем 3ПЗ.
4. Построить таблицу распределения продаж (в денежном выражении) каждого артикула в каждом году. Сохранить под именем ЗП4.
Задание 6. Запросы на изменение
1. Создать таблицу КЛИЕНТ1 на основе таблицы КЛИЕНТ, содержащую список клиентов из Москвы. Сохранить под именем ИЗМ1.
2. Создать запрос на обновление к таблице КЛИЕНТ1, в котором заменить значение поля «город »Москва на Уфу, номера клиентов увеличить на 50 и увеличить значение поля «оборот » на 5000 рублей. Сохранить под именем ИЗМ2.
3. Создать запрос на добавление записей о клиентах с высокой степенью надежности из таблицы КЛИЕНТ1 в таблицу КЛИЕНТ. Сохранить под именем ИЗМ3.
4. Создать запрос на удаление из таблицы КЛИЕНТ записей о клиентах из Уфы. Сохранить под именем ИЗМ4.
Задание 7. Формы
1. Построить форму на дополнение списка клиентов. Сохранить под именем ФК1.
2. Построить автоформу на дополнение заказов. Сохранить под именем ФЗ.
3. Построить форму для просмотра суммарной стоимости заказов каждого клиента. Сохранить под именем ФК2.
4. Построить форму, содержащую артикул товара и номера заказов, его содержащих. Сохранить под именем ФС1.
Задание 8. Отчеты
1. Создать отчет за указанный период, содержащий фамилию клиента, его заказы и их стоимость, предварительно создав необходимые запросы.
2. Создать отчет, отображающий продажи товаров по времени.
3. Создать отчет о долгах фирме, содержащий номер заказа, фамилию клиента и величину долга.
Задание 9. Кнопочная форма
Построить кнопочную форму, содержащую четыре кнопки. Первая – для вывода формы на добавление клиента. Вторая – для вывода формы на добавление заказов. Третья – для просмотра отчета по выполнению и оплате заказов, (отчет должен быть сформирован на альбомной странице, содержать столбцы: номер заказа, дата заказа, выполнение и оплата). Четвертая – для выхода из приложения.
Лабораторная работа №9
Результатом работы должна стать база данных для небольшой библиотеки. Она должна содержать следующую информацию.
1. О книжном фонде: автор, название, год издания, издательство, инвентарный номер, тип издания – учебное, художественное, справочное.
2. О читателях: фамилия, имя, отчество, № читательского билета, категория – студент, преподаватель, сотрудник.
3. О выдаче книг: что, когда и кому выдано, возвращено ли обратно. При возврате запись о выдаче книги не удаляется, а хранится до специального указания.
4. Для каждого типа изданий определены сроки пользования: учебные – 300 дней, художественные – 10 дней, справочные – 3 дня.
В каждой из таблиц целесообразно определить ключевое поле – одно из заданных полей или дополнительное поле – счетчик.
Задание 1
Подготовьте нужные таблицы, установите связи между ними. Заполните таблицы информацией: 5 читателей разных категорий, 10 книг разных типов, 25 записей о выдаче книг (часть должна быть уже возвращена, часть еще на руках у читателей).
Задание 2
На основании созданных таблиц подготовьте следующие запросы.
1. Перечень всех изданий, выпущенных указанным издательством за последние пять лет.
2. Перечень всех изданий, находящихся на руках у читателей с указанием даты истечения срока пользования.
3. Перечень всех задержанных книг (срок пользования истек, а книга не возвращена).
4. Список всех читателей – должников (если читатель задержал несколько книг, то он должен перечисляться в этом списке один раз).
5. Перечень всех книг, находящихся у указанного читателя.
6. Данные о читателе, взявшем данную книгу (по указанному инвентарному номеру).
7. Перечень всех книг, у которых сегодня истекает срок пользования.
8. Информация о читателе (фамилия, имя, отчество и перечень книг, которыми он когда-либо пользовался).
9. Информация о книге (автор, название, перечень читателей: кто и когда ею пользовался)
10. Спрос на различные виды изданий – количество выданных изданий каждого вида за указанный период.
Задание 3
Для удобства работы персонала библиотеки подготовьте следующие формы.
1. Добавление читателя.
2. Добавление издания.
3. Информация об указанном читателе (перечень книг, находящихся у него на руках с указанием, когда книга должна быть возвращена).
4. Информация обо всех читателях с перечнем всех книг, которыми каждый из них когда-либо пользовался (главная форма – читатели, подчиненная – выданные книги).
Задание 4
Подготовьте следующие отчеты.
1. Информация о читателе (фамилия, имя, отчество и перечень книг, которыми он когда-либо пользовался).
2. Информация о книге (кто и когда ею пользовался)
3. Спрос на различные виды изданий (см. рис. 2.36)
Рис. 2.36
Задание 5
Подготовьте кнопочную форму для удобства работы с базой данных.
Приложение 1
Варианты заданий
Вариант 1
База данных «Конференция»
База данных содержит три связанные таблицы:
1. Участники (ФИО, возраст, регион, заявка на гостиницу, количество дней пребывания).
2. Проезд из регионов в днях.
3. Доклады участников (ФИО участника и тема доклада).
Создать запросы:
1. ФИО докладчика и его регион
2. Темы докладов из определенного региона (регион – параметр).
3. Список участников, которым требуется гостиница.
4. Список молодых специалистов (возраст до 30 лет).
5. Участники без докладов.
6. Количество дней, затраченных участниками с учетом дней проезда.
7. Просчитать количество участников по каждому региону.
8. Создать таблицу, содержащую список участников с пребыванием на конференции более двух дней.
Создатьформы:
1. Добавление участника.
2. Добавление доклада.
3. Докладчики по темам.
4. Молодые специалисты.
5. Тема и докладчики (главная и подчинённая формы).
Создать отчеты:
1. Участники из определённого региона.
2. Заявки на гостиницу.
3. Молодые специалисты.
Создать кнопочную форму, содержащую четыре кнопки: две для открытия форм на добавление, третья – для открытия отчета по заявкам на гостиницу и четвертая – для выхода из приложения.
Вариант 2
База данных «Зоопарк»
База данных содержит три связанные таблицы:
1. Животные: название, возраст, стоимость содержания в месяц, сектор нахождения.
2. Рацион: животное, основное питание (сено, мясо, рыба, овощи), номер клетки.
3. Сектора: сектор (А, В, С…), вид животных (крупные хищники, крупные травоядные, медведи, …). Каждый вид животных располагается в своем секторе.
Создать запросы:
1. Список всех животных в возрасте от 7 до 15 лет.
2. Список всех травоядных животных.
3. Сумма, идущая на содержание всех травоядных животных в месяц.
4. Сумма, идущая на содержание всех животных указанного сектора (сектор параметр) в месяц.
5. Список всех животных, питающихся мясом, на содержание которых уходит меньше 50 000 р. в год.
6. Информация о всеядных животных (стоимость содержания, сектор, возраст).
7. Создать таблицу, содержащую список животных старше 12 лет.
Создать формы:
1. Форму в один столбец для добавления животного.
2. Ленточную форму для просмотра списка всех травоядных.
3. Главную форму, содержащую название сектора, и подчиненную, в которой указаны все животные, находящиеся в нем.
4. Кнопочную форму для вызова всех форм и отчетов.
Создать отчеты:
1. Сумма, идущая на содержание всех травоядных животных.
2. Список всех животных, питающихся мясом, на содержание которых уходит меньше 50 000 р. в год.
3. Информация о всеядных животных.
Создать кнопочную форму, содержащую четыре кнопки: две для открытия форм, третья – для открытия отчета и четвертая – для выхода из приложения.
Вариант 3
База данных «Туристическая фирма»
Фирма организует экскурсионные, экстремальные и оздоровительные туры по Южной Америке, Европе, России. Клиенту предоставляется возможность выбрать длительность путешествия самостоятельно.
База данных состоит из трех связанных таблиц: «Клиент», «Путевка» и «Договор»:
1. Таблица «Клиент»: код клиента, ФИО, телефон, город, номер паспорта, номер страховки.
2. Таблица «Договор»: номер договора, код клиента, код тура, дата отъезда, продолжительность тура в неделях.
3. Таблица «Путевка»: код тура, название тура, место пребывания, вид путевки, стоимость недели пребывания, питание – включение/невключение в стоимость путевки.
Создать запросы
1. Список всех туристов с продолжительностью тура более одной недели.
2. Список туров, где питание включено в стоимость путевки.
3. Перечень договоров и стоимость соответствующих туров.
4. Список туристов и стоимость купленных ими туров. Некоторые клиенты обращались в фирму несколько раз.
5. Сумма, полученная фирмой за реализацию всех путевок указанного тура (код тура – параметр).
6. Перечень названий туров и количество клиентов, их выбравших.
7. Информация о востребованности разных видов туров.
8. Создать таблицу, содержащую номера договоров с продолжительностью тура две недели.
Создатьформы:
1. Форма для добавления клиента
2. Форма для добавления тура
3. Главную форму, содержащую название тура, и подчиненную, в которой указаны все клиенты, которые им воспользовались.
Создать отчеты:
1. Информация о клиенте и дате отъезда.
2. Информация о номере страховки клиента.
3. Информация о стоимости путевки за неделю.
Подготовить кнопочную форму для всех форм и отчетов.
Вариант 4
База данных «Образовательные услуги»
Фирма оказывает платные образовательные услуги по специальностям. База данных состоит из четырех связанных таблиц:
1. Таблица «Студенты»: ФИО, номер зачетки, возраст в годах.
2. Таблица «Преподаватели»: ФИО, специальность.
3. Таблица «Предлагаемые специальности»: название, стоимость.
4. Таблица «Обучение»: ФИО студента, специальность, сроки обучения – начало и конец.
Создать запросы:
1. О студентах определенной специальности. Специальность – параметр.
2. Список студентов, которые начали обучение за последние 200 дней.
В запросе использовать функцию date().
3. Распределение студентов по специальностям.
4. О прибыльности обучения по различным специальностям.
5. Об общей стоимости всех услуг.
6. Окончание студентами курсов по месяцам и специальности (перекрестный).
7. Создать таблицу, содержащую ФИО студента и специальность.
Создать формы:
1. Для добавления студента.
2. Ленточная форма для просмотра специальностей.
3. Главную форму, содержащую название специальности и подчиненную, содержащую список всех студентов по этой специальности.
4. Кнопочная форма для вызова форм и отчетов.
Создать отчеты:
1. О прибыльности различных видов специальностей.
2. О студентах определенного специальности
3. О суммарной стоимости оказанных услуг.
Создать кнопочную форму, содержащую четыре кнопки: две для открытия форм, третья – для открытия отчета и четвертая – для выхода из приложения.
Вариант 5
База данных «Гастроли».
Фирма организует гастроли артистов по городам России. Артист может принимать участие в организации разных концертов.
База данных состоит из трех связанных таблиц: «Участники», «Концерты» и «График концертов»
1. Таблица «Участники»: ФИО, возраст в годах, наличие фонограммы, город проведения. При оплате концертов учитываются звания «Заслуженный артист…» и «Народный артист…». В первом случае оплата повышается на 25 %, а во втором - на 40 %.
2. Таблица «Концерты »: название, стоимость.
3. Таблица «График концертов»: название концерта, город и дата проведения.
Создать запросы:
1. Перечень городов, в которых будут проведены концерты за указанный период (период- параметр).
2. Список участников старше 25 лет, которые используют на концерте фонограмму.
3. Число участников по разным названиям концертов.
4. Список участников, имеющих званий «Заслуженный артист…» и «Народный артист…».
5. Количество участников: мужчины старше 40 лет и женщины старше 35 лет.
6. Названия концертов и их общее количество.
7. График проведения концертов: название концерта, месяц проведения и город (перекрестный).
Создать формы:
1. Для добавления артиста.
2. Ленточная форма для просмотра города проведения концертов, даты и названия.
3. Главную форму, содержащую название концерта и подчиненную, содержащую список всех участников этого концерта.
Создать отчеты:
1. О выплатах участникам концертов.
2. Об участниках определенного концерта.
3. О стоимости каждого концерта.
Подготовить кнопочную форму, открывающую отчеты.
Вариант 6
База данных «Отдел материально технического снабжения».
Отдел организует поставку необходимых материалов для функционирования предприятия и оплату работ. Сотрудники отдела делают закупки и пишут отчеты о затраченных средствах и оплаченных работах.
База данных состоит из трех связанных таблиц: «Сотрудники», «Виды затрат» и «Отчеты».
1. Таблица «Сотрудники»: ФИО, должность, наличие электронного адреса, рабочий телефон. При оплате закупок нескорые сотрудники имеют личные льготы от 1 до 10 % (по умолчанию – 0), которые впоследствии учитываются в их заработной плате.
2. Таблица «Виды затрат»: наименование, стоимость.
3. Таблица «Отчеты»: код сотрудника, наименование затрат, дата проведения операции, предварительная оплата.
Создать запросы:
1. Перечень затрат, за которые был выполнен окончательный расчет за указанный период (период – параметр).
2. Список сотрудников, имеющих льготы и электронный адрес.
3. Число сотрудников, оформивших отчеты на разные виды затрат.
4. Список сотрудников, которые при выполнении операции оформляли предварительную оплату.
5. Окончательный расчет по каждой операции с учетом предварительной оплаты и льгот сотрудника.
6. Виды затрат и общие потраченные на них суммы с учетом льгот и предварительной оплаты.
7. Количество видов затрат по месяцам выполнения (перекрестный).
Создать формы:
1. Для добавления вида затрат.
2. Ленточная форма для просмотра льгот в денежном выражении при оплате по отчетам.
3. Главную форму, содержащую код сотрудника, и подчиненную, содержащую название видов затрат, по которым он делал отчет.
Создать отчеты:
1. О сотрудниках отдела, имеющих более одного отчета.
2. О сумме затрат по каждому отчету.
3. О количестве разного вида затрат.
Подготовить кнопочную форму, открывающие отчеты.
Вариант 7
База данных «Санаторий».
База данных состоит из трех связанных таблиц: «Отдыхающие», «Услуги» и «Назначения»:
1. Таблица «Отдыхающие»: код, ФИО, возраст в годах, диагноз, форма размещения (с оплаченным проживанием и лечением в санатории или с оплаченным курсом лечением – курсовка), дата заезда, количество дней, льгота по умолчанию – 0.
2. Таблица «Услуги»: название, стоимость.
3. Таблица «Назначения»: ФИО, название услуги, количество.
Создать запросы:
1. Перечень услуг, которые будут получены отдыхающими за указанный период (период- параметр).
2. Список отдыхающих старше 25 лет, которые пользуются льготой.
3. Список отдыхающих и дата их отъезда.
4. Число отдыхающих, приехавших по курсовкам.
5. Суммарная стоимость льгот, предоставленных отдыхающим.
6. Суммарная стоимость услуг каждого отдыхающего.
7. Заезд отдыхающих по датам и по форме размещения (перекрестный).
8. Создать таблицу, содержащую список отдыхающих с пребыванием в санатории более десяти дней.
Создать формы:
1. Для добавления отдыхающего.
2. Ленточная форма для просмотра списка отдыхающего, даты его заезда и отъезда.
3. Главную форму, содержащую название услуги и подчиненную, содержащую список всех отдыхающих, получивших эту услуг.у
Создать отчеты:
1. О стоимости услуг санатория для каждого отдыхающего.
2. О количестве назначенных услуг каждого вида.
3. Список отдыхающих на дату (дата – параметр).
Создать кнопочную форму, содержащую четыре кнопки: две для открытия форм, третья – для открытия отчета и четвертая – для выхода из приложения.
Вариант 8
База данных «Продажа автомобилей».
Фирма организует продажу автомобилей.
База данных состоит из трех связанных таблиц: «Клиенты», «Автомобили» и «Наличие в продаже»:
1. Таблица «Клиенты»: код клиента, ФИО, год рождения, номер счета, оборот.
2. Таблица «Автомобили»: марка автомобиля, год выпуска, цвет, число скоростей, мощность, вид горючего, инжектор.
3. Таблица «Продажа»: код записи, код клиента, марка автомобиля, продающая фирма, количество, цена, изображение.
Создать запросы:
1. Марки автомобилей, имеющихся в продаже на фирме (фирма- параметр) и стоимость которых превышает 300 000 рублей.
2. Список клиентов старше 25 лет, оборот которых не меньше 500 000 р.
3. Суммы в денежном выражении, которые фирмы вложили в продаваемые машины.
4. Стоимость машин с учетом наценки фирмы.
5. Число клиентов, которые могут купить автомобиль дороже 400 000 р.
6. Количество автомобилей по маркам и по году выпуска (перекрестный).
7. Создать таблицу, содержащую список автомобилей, выпущенных за последние три года.
Создать формы:
1. Для добавления клиента.
2. Ленточная форма для просмотра остатка денежных средств клиентов, которые купили автомобиль дороже 400 000 р.
3. Главную форму, содержащую название фирмы, и подчиненную, содержащую список автомобилей, продаваемых фирмой.
Создать отчеты:
1. О стоимости автомобилей после наценки.
2. Список фирм, которые продают модификации ВАЗА.
3. О клиентах, которые могут купить автомобиль дороже 400000 р.
Создать кнопочную форму, содержащую четыре кнопки: две для открытия форм, третья – для открытия отчета о стоимости автомобилей после наценки и четвертая – для выхода из приложения.
Вариант 9
База данных «Ателье».
База данных состоит из трех связанных таблиц: «Ассортимент услуг», «Ткани» и «Изготовление»:
1. Таблица «Изготовление»: код, клиент, изделие, коэффициент сложности, ткань, количество ткани в метрах, дата заказа, выполнение. Коэффициент сложности имеет три значения: 1; 1,4; 1,8.
2. Таблица «Ассортимент услуг»: название, базовая стоимость, срок изготовления в днях.
3. Таблица «Ткани»: ткань, цена за метр.
Создать запросы:
1. Перечень изделий, заказанных за указанный период (период – параметр)
2. Список клиентов, заказавших изделия с коэффициентом сложности 1,4 или 1,8. Повторяющихся строк в запросе быть не должно.
3. Список заказанных изделий и даты их готовности.
4. Список клиентов и количество их заказов.
5. Список клиентов и стоимость каждого изделия, заказанного ими с учетом базовой стоимости изготовления, коэффициента сложности, стоимости и количества ткани.
6. Суммарная стоимость услуг каждого клиента.
7. Распределение изделий по клиентам и коэффициентам сложности (перекрестный).
8. Создать таблицу, содержащую список заказов, имеющих коэффициент сложности 1,8.
Создать формы:
1. Для добавления ассортимента.
2. Ленточная форма для просмотра списка клиентов и даты заказов изделий.
3. Главную форму, содержащую название ткани и подчиненную, содержащую список изделий, выполненных из этой ткани.
Создать отчеты:
1. О стоимости изделий ателье для каждого клиента.
2. О количестве изделий ателье каждого вида ассортимента.
3. Список незаконченных изделий на определенную дату (дата – параметр).
Создать кнопочную форму, содержащую четыре кнопки: две для открытия форм, третья – для открытия отчета о количестве изделий ателье каждого вида ассортимента и четвертая – для выхода из приложения.
Вариант 10
База данных «Транспортные перевозки»
База данных состоит из трех связанных таблиц: «Перевозки», «Транспортные средства» и «Клиенты»:
1. Таблица «Заказы»: код, клиент, транспортное средство, коэффициент комфортности, продолжительность аренды в часах, дата заказа, предварительная оплата. Коэффициент комфортности имеет четыре значения: 0,7; 1; 1,3; 1,9.
2. Таблица «Транспортные средства»: вид, базовая стоимость аренды за один час.
3. Таблица «Клиенты»: ФИО, вид деятельности, начало работы с фирмой, льгота от 5% до 15% (по умолчанию – 0), долг в денежном выражении.
Создать запросы:
1. Перечень заказов с предварительной оплатой за указанный период (период – параметр).
2. Список клиентов, которые пользуются транспортными средствами коэффициентом комфортности 1 и 1,3.
3. Список клиентов, код заказа и его стоимость с учетом комфортности и льготы.
4. Количество заказанных транспортных средств каждого вида.
5. Суммарная стоимость заказов каждого клиента с учетом предварительной оплаты.
6. Окончательный расчет каждого клиента с фирмой с учетом долга.
7. Выбор комфортности в заказах клиентов по видам транспортных средств (перекрестный).
Создать формы:
1. Для добавления клиента.
2. Ленточная форма для просмотра окончательного расчета каждого клиента с фирмой.
3. Главную форму, содержащую название транспортного средства, и подчиненную, отражающую список всех заказов с ним.
Создать отчеты:
1. Окончательный расчет каждого клиента с фирмой.
2. О количестве заказанных транспортных средств каждого вида.
3. Список заказов, где транспортное средство используется более 10 часов (количество часов – параметр).
Создать кнопочную форму, содержащую четыре кнопки: две для открытия форм, третья – для открытия отчета о количестве заказанных транспортных средств каждого вида. и четвертая – для выхода из приложения.
Приложение 2