Тема: «Создание запросов SQL»
Практическая работа №10. Тема: «Создание запросов SQL»
Откройте базу данных, содержащую все таблицы базы данных «Борей».
Создайте запрос данных «Выбор товаров» из другой базы данных «Борей» таблицы «Товары».
SELECT *
From товары
In "C:\program files\Microsoft office\office\samples\борей.mdb";
Создайте запрос данных «Страны Получателей», выбирающий из таблицы Заказы поле СтранаПолучателя с исключением повторений, используя предикат Distinct:
SELECT distinct странаполучателя
From заказы;
Создайте запрос данных «Цены Заказано», выбирающий из таблицы «Заказано» поля кодтовара и цена без повторяющихся значений:
SELECT DISTINCT кодтовара, цена
FROM заказано;
Создайте запрос данных «10 самых дешевых товаров», выбирающий из таблицы «Заказы» 10 самых дешевых товаров по стоимости доставки товаров:
SELECT top 10 *
From заказы
Order by стоимостьдоставки desc;
Создайте запрос с параметрами «Цены Заказано2», запрашивающий для поля цена начальное значение и конечное значение и выводящий все поля таблицы «Заказано».
SELECT *
FROM заказано
WHERE Цена Between [начальная цена] And [конечная цена];
Создайте запрос данных «Заказы сотрудников», выбирающий из таблиц «Сотрудники» сведения о сотруднике (фамилия, имя, должность) и из таблицы «Заказы» дату исполнения заказа сотрудником, используя команду inner join.
SELECT фамилия &" "& имя as сотрудник, должность, датаисполнения
from сотрудники inner join заказы
on сотрудники.кодсотрудника=заказы.кодсотрудника;
Создайте запрос данных «Клиенты», выбирающий из таблиц «Клиенты» все названия и соответствующие выполненным заказам даты размещения заказов из таблицы «Заказы», используя команду left join (если клиент не выполнил заказ, он все равно отображается в таблице)
SELECT название, датаразмещения
from клиенты left join заказы
on клиенты.кодклиента=заказы.кодклиента;
Создайте запрос данных «Заказы товаров», выбирающий из 3х таблиц «Товары», «Заказано» и «Заказы» сведения, используя вложенную команду inner join.
SELECT марка, товары.цена, единицаизмерения, количество, стоимостьдоставки
from товары Inner join (заказано Inner join заказы on заказы.кодзаказа=заказано.кодзаказа)
on товары.кодтовара=заказано.кодтовара;
Создайте запрос данных «названия клиентов», выбирающий из таблицы Клиенты поле название с исключением повторений, используя предикат Distinctrow
SELECT DISTINCTROW название
FROM Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента= Заказы.КодКлиента
Order by название;
Самостоятельная работа
(по базе данных «Заказы товаров»)
Создайте запрос sql1, отображающий данные поля Вес без повторений.
Создайте запрос sql2, отображающий 3х самых молодых заказчиков.
Создайте запрос с параметром sql3по таблице Товары, отображающий все поля таблицы Товары для введенного пользователем промежутка значений поля вес.
Создайте запрос sql4, отображающий поля товар, цена и количество таблиц Товары и Заказы.
Создайте запрос sql5, отображающий данные полей фио, телефон, адрес, товар, и цена, где заказчики из введенного пользователем города, фио упорядочите по алфавиту.
Практическая работа №11. Тема: «Создание запросов SQL на объединение, на изменение».
Откройте базу данных, содержащую все таблицы базы данных «Борей».
Создание запросов на изменение
Создайте запрос на создание таблицы «Продукты» из базы данных "Заказы товаров".
SELECT *
into продукты
from товары
in "диск:\путь к файлу\заказы товаров.mdb";
Запустите этот запрос для создания таблицы.
Сохраните запрос под именем «Создание таблицы Продукты»
Откройте запрос прошлого занятия - запрос11 по таблице «Клиенты», выбирающий всех менеджеров, (если у вас, его нет, то создайте запрос по таблице "Клиенты", выводящий поля Название, Должность (всех менеджеров) и Адрес по возрастанию), добавьте в него поля «телефон», «страна» и преобразуйте его в запрос на создание таблицы «Менеджеры».
Сохраните запрос под именем «Создание таблицы менеджеры»
Создайте запрос на добавление Представителей в таблицу «Менеджеры».
Insert Into менеджеры
SELECT название, должность, телефон, страна
From клиенты
Where должность="представитель";
Сохраните запрос под именем «Добавление представителей»
Создайте запрос на добавление одной записи в таблицу «Менеджеры».
Insert Into менеджеры (название, должность, адрес, телефон, страна)
Values ("Гигабайт", "Техник", "Мира,4", "34-45-33", "Россия");
Сохраните запрос под именем «Добавление техника»
Создадим запрос на удаление клиентов из Испании «Удаление клиентов из Испании»
Delete *
From менеджеры
Where страна="Испания";
Создадим запрос на обновление должности «менеджер по покупкам» на «менеджер по продажам» из страны США
Update менеджеры
Set должность="менеджер по покупкам"
Where должность="менеджер по продажам" and страна="США";
Сохраните запрос под именем «Обновление менеджеров».
Объединение запросов в одном запросе.
Операция UNION позволяет объединить в одно множество результирующие множества нескольких запросов, созданных инструкцией Select.
Создадим запрос, объединяющий запросы данных по таблицам Клиенты и Поставщики из страны Франция.
SELECT название, город, индекс, страна, кодпоставщика as Код
from поставщики
where страна="Франция"
UNION
SELECT название, город, индекс, страна, кодклиента as код
from клиенты
where страна="Франция";
сохраните запрос под именем «Предприятия из Франции».
Самостоятельная работа
(по базе данных Борей)
Создайте запрос «Зачет1» данных по таблицам Заказы и Клиенты, содержащий поля: Название, адрес, дата исполнения 1996 года и Стоимость доставки в промежутке [30;40]. Задайте соединение по полю Кодклиента, выборка первых 10 записей по наименьшей стоимости доставки.
Создайте запрос «Зачет2» на создание таблицы «Рыбопродукты» по запросу данных из таблицы "Товары" полей Марка, единица измерения, цена и минимальный запас. Среди товаров выберите Рыбопродукты (кодтипа-8).
Создайте запрос «Зачет3» на добавление в таблицу «Рыбопродукты» продуктов типа Мясо/птица (код типа-6).
Создайте запрос «Зачет4», объединяющий 2 запроса: запрос, выбирающий все поля по таблицы «Продукты», кроме Вес и запрос выбирающий поля таблицы «Товары» - кодтовара, марка, цена.
Создайте запрос «Зачет5» на изменение цен в таблице «Рыбопродукты»– уменьшение цен поставщика Lyngbysild в 10 раз.
Практическая работа №12. Тема: «Создание диаграмм в программе Access»
Создадим диаграмму, отражающую суммы продаж категорий товаров по месяцам 1995 года.
Создание запроса, на котором основана диаграмма.
Сначала необходимо создать запрос, для этого создайте запрос ио таблицам «Типы», «Товары». «Заказано» и «Заказы»
Включите в запрос поля «Категория» из таблицы «Типы», «Дата Исполнения» из таблицы «Заказы».
Добавьте поле Цена, используя следующее выражение [Заказано]![Цена]*[Заказано]![Количество]
Чтобы выбрать заказы 1995 года, используйте Like”*.95”
Перейдите в режим таблицы и проверьте работу запроса.
Закройте запрос и сохраните под именем «МастерДиаграмм».
Использование Мастера Диаграмм.
Создайте новую форму, из списка «Новая форма» выберите «Диаграмма» и из списка таблиц выберите запрос «МастерДиаграмм».
Выберите все поля запрос, далее
Выберите диаграмму «График».
Мастер диаграмм пытается построить перекрестный запрос, основываясь на типах данных записей выбранного запроса. Однако в этом случае предлагаемый вариант не подходит, т.к. нам не нужно строить зависимость месяцев от типов продаж.
Надо построить зависимость типов продаж от месяцев и цен, т.е. типы продаж должны быть помещены в легенду диаграммы, а месяцы располагаться по оси Х. В правой части расположены кнопки полей. Перетащите оотуда поле «Категория» в прямоугольник под легендой, а кнопку поля «ДатаИсполнения» перетащите под ось абсцисс. Дважды щёлкните по ней, чтобы изменить способ группировки, Далее.
Назовите диаграмму «Суммы продаж категорий товаров по месяцам 1995 года», Готово.
Перейдите в режим Конструктор
Поскольку диаграмма помещена в свободную рамку объекта, то элементы оформления формы для переходов между записями не нужны: вызовите свойства формы и установите значение «Отсутствуют» для свойства «Полосы прокрутки» и «Нет» для свойств «Область выделения» и «Поле номера записи».
Перейдите в режим формы для просмотра диаграммы.
Изменение дизайна диаграммы.
Может понадобиться преобразовать диаграмму к другому типу, например, в диаграмму с областями или составную гистограмму. Диаграмма с областями наиболее полезна для отображения вклада, вносимого одной категорией товаров в общую сумму продаж. Чтобы преобразовать диаграмму к другому типу:
Выберите команду «Диаграмма, Тип диаграммы», далее выберите тип диаграммы «С областями» и в списке «Вид»- «С накоплением» (средняя в верхнем ряду).
Вклад каждой категории товаров представлен закрашенной областью, а самая верхняя область относится к общему объёму продаж.
Просмотрите результат.
Преобразуйте диаграмму в гистограмму «С накоплением».
Подтипом диаграммы с областями и гистограммы с накоплением является диаграмма нормированная на 100%. Чтобы создать диаграмму, выберите «тип диаграммы»- Гистограмма и вид «Нормированная на 10%» (правая верхняя), на которой все столбцы имеют одинаковую высоту.
Поскольку ранее был установлен формат оси Y «Денежный» без десятичных знаков, то надо вручную изменить формат на «Процент». Для этого выделите ось Y, и выберите команду «Формат, Выделенная ось».
Просмотрите результат.
Измените тип диаграммы на график.
Создание диаграммы на основе перекрестного запроса.
После построения диаграммы, можно изменить её свойство «Источник строк» так, чтобы оно указывало на существующий перекрестный запрос. Т.е. можно использовать запрос, созданный ранее. Новый запрос будет назваться «Квартальные обороты по типам товаров 1995(диаграмма)».
. Чтобы создать связанную диаграмму, в свойстве «Источник строк» диаграммы надо указать таблицу или запрос.
Чтобы создать новый зарос:
Откройте перекрестный запрос, созданный ранее по практической, если его у вас нет, то создайте по практической работе «Создание перекрестного запроса».
Добавьте в запрос таблицу «Типы».
Удалите в бланке запроса поля «КодТипа», «Марка» и перетащите поле «Категория» таблицы «Типы» в первый столбец запроса и выберите «Заголовки строк» в строке «Перекрестная таблица».
Задайте имя для поля «Категории», введя Категории:
Замените выражение «Выражение1» в строке «Поле» на «Выражение1: Format ([Дата размещения];”mmm”)».
Выберите команду «Файл, Сохранить как» и сохраните запрос под именем «Квартальные обороты по типам товаров 1995 (диаграмма)» и запустите запрос
Откройте форму «Мастер Диаграмм» в режиме Конструктор, выделите свободную рамку объекта и откройте окно свойств.
Раскройте вкладку «Данные», из списка «Источник строк» выберите «Квартальные обороты по типам товаров 1995 (диаграмма)».
Посмотрите результат и сравните с прежней диаграммой.
Связывание диаграммы с отдельной записью таблицы или запроса.
Чтобы создать связанную диаграмму, надо установить свойства «Подчиненные поля» и «Основные поля». Такой же тип связи устанавливается между формой и подчиненной формой. Связанная диаграмма отображает зависимость между данными из одной записи таблицы или запроса, на которой ссылается свойство «Источник записей» формы. При переходе между записями в форме диаграмма перерисовывается, чтобы отражать новые значения.
Чтоб связать диаграмму с формой:
Откройте форму «мастер диаграмм» в режиме Конструктор, откройте окно свойств.
На вкладке «Макет», установите значение «Да» для свойства «Поле номера записи».
Выделите свободную рамку объекта и раскройте вкладку «Данные», в текстовых полях свойств «Подчиненные поля» и «Основные поля» введите Категории.
При этом создается связь между текущей записью формы и строкой запроса, который фигурирует в свойстве «Источник строк» диаграммы.
Просмотрите результат.
Одна линия, изображенная на графике слишком незаметна. Перейдите в Конструктор и дважды щёлкните мышкой по диаграмме, и когда произойдет запуск по месту, дважды щёлкните в любом месте кривой, чтобы открыть окно «Формат ряда данных». Из списка «толщина» выберите самую широкую линию. чтобы изменить маркеры, из списка «Стиль» выберите новый значок, например, квадрат, а из списков «цвет» и «фон» выберите контрастные цвета.
Дважды щёлкните по легенде. Чтобы открыть диалоговое окно «формат легенды» выберите переключатель «Невидимая» в группе «Рамка», чтобы удалить рамку вокруг легенды. Раскройте вкладку «Шрифт», измените начертание на «полужирный» и размер шрифта на 11.
Чтобы легенда стала подзаголовком диаграммы, щёлкните по ней мышью и перетащите её под заголовок.
Чтобы сделать ломанную кривую гладкой, активизируйте диаграмму и дважды щёлкните по кривой. В группе «Линия» установите флажок «сглаженная линия»
Практическая работа №13. Контрольная работа по теме «Создание запросов SQL»
Вариант 1
Напишите инструкцию SQL-запроса на выборку, возвращающий из таблицы «Продажи»
а) поля: Код_товара, Название, Цена, Количество и Категория, добавить вычисляемое поле Всего=Цена*Количество.
б) 15 самых дешевых товаров категории продукты.
Что даст следующая инструкция:
SELECT DISTINCTROW Заказы.[Код заказа], Заказчики.Фамилия, Заказчики.Имя, Заказы.[Дата заказа]
FROM Заказчики INNER JOIN Заказы ON Заказчики.[Код заказчика] = Заказы.[Код заказчика]
ORDER BY Заказы.[Код заказа];
Найдите ошибки в написании инструкции:
SELECT*
from поставщики
where страна="Испания"
UNION SELECT название,город,индекс,страна,кодклиента as Код
from клиенты
where страна="Испания"
Продолжите фразу:
Ключевое слово Having используется для…
Вариант 2
Напишите инструкцию SQL-запроса на выборку, возвращающий из таблицы «Архив профессий» данные
а) полей Профессия, Тип и Оклад (переименовать в Зарплата),добавьте вычисляемое поле Новая зарплата=Оклад*1,1. Задайте сортировку по полю Профессия.
б) выбор первых 15 самых высокооплачиваемых профессий.
Что даст следующая инструкция:
DELETE*
FROM Студент INNER JOIN [Студент заочник]
ON Студент.Группа=[Студент заочник].Группа
Найдите ошибки в написании инструкции:
SELECT Код заказа, Фамилия & “ ”& Имя AS Заказчик, Дата заказа
FROM Заказчики INNER JOIN Заказы ON Заказчики. Заказчик = Заказы.Код заказчика
ORDER BY Заказы.Код заказа;
Продолжите фразу:
Ключевое слово Where используется для…
|
