|
|
ЛАБОРАТОРНЫЕ РАБОТЫ MS ACCESS. Лабораторная работа 1 создание таблиц и схемы базы данных
Лабораторная работа 5
СОЗДАНИЕ SQL-ЗАПРОСОВ
Цель работы - познакомиться с основными инструкциями SQL, получить практические навыки по созданию запросов на SQL.
Краткие сведения по среде разработки
SQL (Structured Query Language) – это стандартный язык управления базами данных. Версия языка SQL, реализованная в Access, несколько отличается от стандартной версии. Язык Access SQL имеет теперь новое название Microsoft Jet Database Engine SQL, сокращенно Jet SQL.
Любой запрос, созданный с помощью Мастера запросов либо с помощью Конструктора запросов хранится в БД в формате SQL. Есть несколько типов запросов, которые невозможно создать с помощью Конструктора запросов.
Создание запроса на выборку.
Структура запроса на выборку:
SELECT[ALL|DISTINCT|DISTINCTROW][TOP n PERCENT]] <списоквыбора>
From <список таблиц (запросов) >
[Where <условие отбора строк>]
[Group BY <список полей, по которым будут группироваться записи>]
[HAVING <условие отбора групп записей>]
[order by <определение сортировки записей>];
Рассмотрим последовательно составляющие запроса на выборку
SELECT – команда, определяющая запрос на выборку. Список выбора (список полей) содержит те поля, которые должны быть включены в результирующую таблицу запроса и их имена в этой новой таблице. Имена полей отделяются друг от друга запятыми. Имя поля формируется из имени таблицы и имени столбца, разделенных точкой.
Необязательные предикаты ALL, DISTINCT, DISTINCTROW определяют способ отбора строк. При использовании ALL в результирующую таблицу включаются все строки, удовлетворяющие указанным далее условиям. Предикат DISTINCT исключает повторяющиеся строки, основываясь на данных результирующего набора записей. Предикат DISTINCTROW исключает повторяющиеся строки, основываясь на данных полных строк исходной таблицы, независимо от того, включены ли в запрос те поля таблицы, данные которых различаются.
Необязательные [PERCENT] возвращает первые n записей или n процентов набора записей, удовлетворяющих запросу. Предикат TOP n может использоваться при выполнении запросов, в которых требуется вывести некоторое количество(n), самых «популярных» записей.
From- определяет таблицы или запросы, которые служат источником данных. Синтаксис предложение From :
From <имя таблицы, (имена таблиц)>
[{INNER/ LEFT/ RIGHT }JOIN < связанная таблица>
ON <условие объединения >]
INNER JOIN – внутреннее объединение таблиц; LEFT JOIN – левое внешнее объединение; RIGHT JOIN – правое внешнее объединение.
Например, в запросе о выборе книг по заданной теме связывание типа: «INNER JOIN» ( внутреннее объединение таблиц) с условием равенства поля [код темы]
Задание 1.
Создать запрос формирующий вывод списка книг по темам.
Для создания запроса в режиме SQL необходимо выполнить следующую последовательность действий:
Создать новый запрос с использованием Конструктора запросов.
Закрыть диалоговое окно Добавление таблицы, не добавляя таблиц.
Выбрать команду Режим, Режим SQL для вывода окна SQL.
Удалить весь текст, который может быть выведен в окне SQL.
Ввести текст запроса.
В данном примере:
SELECT тема.[название темы], Книга.Автор, Книга.Название, Книга.Издательство, Книга.[Год издания]
FROM тема INNER JOIN Книга ON тема.[Код темы] = Книга.[Код темы]
Выполнить запрос, для этого выбрать команду Режим/режим таблицы.
WHERE - задает условие отбора строк. Для написания условий отбора можно использовать операторы сравнения, логические операторы, операторы LIKE, BETWEEN, IN, IS (Приложение2 ).
Задание 2.
Создать запрос формирующий вывод списка книг по указанной теме.
-Введите текст запроса в режиме SQL:
SELECT тема.[название темы], Книга.Автор, Книга.Название, Книга.Издательство, Книга.[Год издания]
FROM тема INNER JOIN Книга ON тема.[Код темы] = Книга.[Код темы]
WHERE (тема.[название темы]= [Введите название книги] );
-Выполните запрос.
order by- задает порядок сортировки записей созданного запросом набора строк: по возрастанию (ASC) или по убыванию (DESC). По умолчанию принимается значение ASC:
order by <имя столбца> [{ASC, DESC}].
Задание 3
Создать запрос формирующий вывод списка книг по темам, упорядочить информацию по авторам.
-Введите текст запроса в режиме SQL:
SELECT тема.[название темы], Книга.Автор, Книга.Название, Книга.Издательство, Книга.[Год издания]
FROM тема INNER JOIN Книга ON тема.[код темы] = книга.[код темы]
orderbyтема.[название темы] ASC;.
-Выполните запрос.
GROUP BY - определяет столбец (столбцы) по значениям которого записи объединяются в группы.
Например, расчет размера штрафов, в данном запросе используется агрегатная функция: Sum([Расчет размера пени].Пени, переименование столбца: AS Штраф, группировка по номеру билета, фамилии , имени
SELECT [Расчет размера пени].[Номер билета], [Расчет размера пени].Фамилия, [Расчет размера пени].Имя, Sum([Расчет размера пени].Пени) AS Штраф
FROM [Расчет размера пени]
GROUP BY [Расчет размера пени].[Номер билета], [Расчет размера пени].Фамилия, [Расчет размера пени].Имя;
Задание 4
Создать запрос определяющий количество книг по каждой теме.
-Введите текст запроса в режиме SQL:
SELECT тема.[название темы], Sum(Книга. [Количество экземпляров]) AS Количество
FROM тема INNER JOIN Книга ON тема.[Код темы] = Книга.[Код темы]
GROUPBY тема.[название темы]
-Выполните запрос.
HAVING - определяет условие отбора групп записей в столбцах, указанных в предложении Group BY.
Например, вывести список тем, количество взятых книг по которым превышает 3. Наберите текст запроса в СУБД и посмотрите результат.
SELECT тема.[название темы], Count(Книга.[Номер книги]) AS Количество
FROM тема INNER JOIN Книга ON тема.[Код темы] = Книга.[Код темы]
GROUPBY тема.[название темы]
HAVING(Count(Книгa.[Номеркниги])) >3
Задание 5
Создать запрос определяющий количество книг по каждой теме, вывести темы, где количество книг превышает 300 шт.
-Введите текст запроса в режиме SQL:
SELECT тема.[название темы], Sum(книга.[Количество экземпляров]) AS Количество
FROM тема INNER JOIN Книга ON тема.[Код темы] = Книга.[Код темы]
GROUP BY тема.[название темы]
HAVING (Sum(книга.[Количество экземпляров]) >300)
-Выполните запрос.
Самостоятельная работа.
Создайте средствами SQL следующие запросы:
- формирующий список читателей с указанием фамилии, имени, отчества, года рождения; упорядочить список по фамилии;
- определить количество тем в каждом разделе;
- определить пять самых популярных книг ( использовать предикат TOP 5);
Запрос на создание таблицы
Базовые таблицы описываются в SQL с помощью предложения CREATE TABLE (создать таблицу), синтаксис которого имеет небольшие различия в различных СУБД. Однако все они поддерживают следующую минимальную форму:
CREATE TABLE базовая_таблица (имя_ столбца тип_данных [NOT NULL] [,имя_ столбца тип_данных [NOT NULL]] ...);
где базовая_таблица - имя создаваемой таблицы
имя_ столбца - имя поля в создаваемой таблице
тип_данных – тип данных создаваемого в таблицы поля
В результате создается пустая базовая таблица
Задание 6
Создайте таблицу «Списание» со следующей структурой (табл.5.1).
Таблица 5.1
Структура таблицы Списание
Имя поля
|
Тип поля
|
|
номер книги
|
integer
|
NOT NULL
|
Дата списания
|
date
|
|
причина
|
string
|
|
Созданную таблицу интегрировать в схему данных и заполнить следующими данными ( табл.5.2):
Таблица 5.1
Данные таблицы Списание
Номер книги
|
дата
|
причина
|
1
|
10.10.10
|
ветхость
|
2
|
10.10.10
|
утеря
|
3
|
10.10.10
|
ветхость
|
5
|
10.10.10
|
Передана в другой фонд
|
7
|
10.10.10
|
ветхость
|
Посмотрите самостоятельно структуру запросов на обновление, добавление, удаление.
Создание перекрестных запросов
Для определения перекрестных запросов используется следующий синтаксис:
TRANSFORM <выражение с итоговой функцией > [AS подпись]
<инструкция SELECT>
PIVOT <выражение>
Ключевое слово TRANSFORM определяет перекрестный запрос; <выражение с итоговой функцией > [AS подпись]- определяет область данных перекрестной таблицы и вычисления, выполняемые над данными.
<инструкция SELECT> соответствует синтаксису обычного запроса и обязательно содержит предложение GROUP BY; После инструкций SELECT и GROUP BY определяется столбец, значения которого используются в качестве заголовков строк перекрестной таблицы.
PIVOT определяет столбец или выражение, значения которого используются в качестве заголовков столбцов перекрестной таблицы. В предложении PIVOT можно задать формат вывода заголовков столбцов.
Например, определить количество книг по каждой теме.
TRANSFORM Count(Журнал.[Номер книги]) AS [Count-Номер книги]
SELECT раздел.[название раздела]
FROM (раздел INNER JOIN тема ON раздел.[код раздела] = тема.[код раздела]) INNER JOIN (Книга INNER JOIN Журнал ON Книга.[Номер книги] = Журнал.[Номер книги]) ON тема.[код темы] = Книга.[Код темы]
GROUP BY раздел.[название раздела]
PIVOT тема.[название темы];
Задание 7
Средствами SQL перекрестный запрос, определяющий количество списанных книг по темам и разделам.
Контрольные вопросы
В каком виде храниться запрос в БД Access?
Объясните технологию создания запроса в режиме SQL.
Что задает команда SELECT?
Объясните структуру запроса на выборку на созданных в работе примерах.
Что задает команда TRANSFORM.
Объясните структуру перекрестного запроса на созданном в работе примере.
Каким образом можно средствами SQL создать таблицу.
|
|
|
Скачать 1.89 Mb.