Лабораторная работа. Использование языка запросов SQL при прикладном программировании

Цель: Ознакомление с методами и средствами создания баз данных на ЭВМ.

                                                                                               

1. Ознакомление с возможностями программы Access 2000 по созданию запросов с помощью языка SQL

Структурированный язык запросов SQL предназначен для выполнения операций над таблицами (создание, удаление, изменение структуры) и над данными таблиц (выборка, изменение, добавление и удаление), а также некоторых сопутствующих операций. SQL является непроцедурным языком и не содержит сопутствующих в обычных языках программирования операторов управления, организации подпрограмм, ввода – вывода и т.п. В связи с этим SQL автоматически не используется, а обычно включен в среду встроенного языка программирования СУБД.

В современных СУБД с интерактивным интерфейсом можно создавать запросы, не применяя SQL. Однако его применение в некоторых случаях позволяет расширить возможности СУБД. К примеру, при подготовке запрос в среде Access можно перейти из окна конструктора запросов (задание запроса по образцу) в окно с эквивалентным оператором SQL. Подготовку нового запроса путем редактирования уже имеющегося в ряде случаев проще выполнить путем изменения операторов SQL

К числу основных операторов усеченного подмножества SQL относятся следующие операторы:

· CREATe TABLE – создание таблицы.

· DROP TABLE – удаление таблицы.

· CREATE INDEX – создание индекса.

· DROP INDEX - удаление индекса

· ALTER TABLE – изменение структуры таблицы.

·   SELECT, UPDATE, INSERT, DELETE – выборка, изменение, вставка и удаление записей.

  В упрощенном виде важнейший оператор SELECT имеет следующий формат:

SELECT [ ALL\DISTINCT] < список данных >

FROM <список таблиц >

[ HERE <условие выбора>]

[ GROUP BY <имя столбца> [, <имя столбца>]...]

[ HAVING <условие поиска>]

[ ORDER BY <спецификация сортировки>[,<спецификация сортировки>]...]

Оператор SELECT позволяет выполнять выборку и вычисления над данными одной или нескольких таблиц. Результатом выполнения оператора является ответная таблица, которая может иметь (ALL) или не иметь (DISTINCT) повторяющиеся строки.

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

 

 

1.1.Проектирование запросов

При построении запроса в окне конструктора система Access работает в фоновом режиме, записывая эквивалентные инструкции SQL. Для просмотра программы SQL в меню Вид выберите команду Режим SQL.

Создайте на рабочем столе папку под своим именем и скопируйте в неебазу данных Петрус из папки. Дальнейшие вычисления проводите только в своей папке.

Примечание: Если таблицы не связаны, то их надо связать.

 

1.1.1.Запросы на выборку

1. Создайте запрос на выборку из таблиц Работник и Зарплата информации о работниках, включая имя, специальность, зарплату. Сохраните запрос под именем Занятие_3_1.

2. Просмотрите построение запроса Занятие_3_1 в режиме SQL.

 

 

3. Проанализируйте инструкцию построенного запроса:

· инструкции SELECT - о пределяет возвращаемую информацию как набор записей.

· параметр FROM - указывает, какие таблицы содержат поля, приведенные в инструкции SELECT.

· операция INNER JOIN устанавливаетвнутреннееобъединение между таблицами Работник и Зарплата.

4. Отредактируйте запрос в режиме SQL таким образом, чтобы не выдавалась информация о специальности работника.

5. Выполните запрос, проверьте правильность выдаваемых результатов и сохраните запрос под именем Занятие_3_1_1.

1.1.2.Перекрестные запросы

1. Создайте перекрестный запрос, извлекающий из таблиц Работник и Назначение информацию о количестве дней проработанных каждым работником на выделенном ему обьекте. Имена работников укажите в строках, профессию в столбцах. Сохраните запрос под именем Занятие_3_2.

2. Перейдите в режим SQL и просмотрите построение запроса в режиме SQL.

 

3. Проанализируйте инструкцию построения запроса:

¾ инструкции SELECT извлекает название специальности из таблицы Работник.

¾ параметры FROM и GROUP BY являются запросами на выборку.Указывают, какие таблицы содержат поля, приведенные в инструкции SELECT.

¾ Операция GROUP BY производит группировку всех полей списка SELECT.

4. Отредактируйте запрос в режиме SQL так, чтобы в строках выводилась специальность, а в столбцах – имя работника.

5. Выполните запрос, проверьте правильность полученной информации и сохраните запрос под именем Задание_3_2_1.

 

1.1.3.Групповые запросы

1. Создайте обычный запрос, применив операцию группирование, подсчитывающий средний заработок каменщиков, занятых на ремонте зданий. Среднее значение рассчитывает функция Avg. Сохраните запрос под именем Занятие_3_3.

2. Перейдите в режим SQL и просмотрите построение запроса в режиме SQL.

3. Проанализируйте инструкцию построения запроса:

· Инструкция Select определяет возвращаемые (извлекаемые) записи.

· Инструкции From и Group By являются запросами на выборку.

· Параметр Group By определяет группы, с которыми будут производиться расчеты.

· Предложение Having определяет записи, которые будут отображаться.

4. Отредактируйте запрос в режиме SQL так, чтобы выводилось и среднее число проработанных каменщиками дней.

5. Выполните запрос. Проверьте правильность полученных результатов и сохраните запрос под именем Занятие_3_3_1.

 

1.1.4. Запрос на создание таблицы

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

1. Перейдите в режим конструктора запроса.

2. При необходимости закройте окно добавления таблиц.

3. Выберите команды меню Вид - Режим SQL.

4. В окне запроса введите инструкции, составляющие SQL – запрос на создание новой таблицы:

SELECT Здание.[Код здания], Здание.[Дата начала], Назначение.[Код работника]

INTO Перспектива

FROM Здание INNER JOIN Назначение ON Здание.[Код здания] = Назначение.[Код здания]

WHERE (((Здание.[Дата начала])>=#1/1/2002#));

Приведенный запрос состоит из инструкции SELECT С писок_полей   INTO новая_таблица с параметрами FROM, WHERE и операции INNER JOIN.

  Инструкция SELECT… INTO определяет поля, которые должны быть извлечены и вставлены в новую таблицу

Параметр FROM указывает, какие таблицы содержат данные поля, приведенные в инструкции SELECT… INTO.

Параметр WHERE определяет, какие записи таблиц, указанные в списке FROM, появятся в результирующем наборе данного запроса.

Операция INNER JOIN создает специальное объединение таблиц.

5. Для выполнения запроса активизируйте кнопку с восклицательным знаком.

6. Если в SQL – инструкции отсутствуют ошибки, закройте окно запроса с сохранением запроса под именем Создание таблицы.

7. Откройте таблицу Перспектива в режиме таблицы и проверьте наличие в ней выбранных записей.

 

1.1.5.Запросы на обновление

Запросы на обновление изменяют значения полей на основании определенных критериев. Например, изменить в таблице Специальность часовую ставку Подсобного работника с 50 рублей на 55 рублей.

1. Перейдите в режим конструктора запроса.

2. Закройте окно добавления таблиц.

3. Выберите команды меню Вид - Режим SQL

4. В окне запроса введите инструкции, составляющие SQL – запрос на обновление записей.

 

UPDATE Специальность SET Специальность.[Часовая ставка] = 55

WHERE (((Специальность.Специальность)=»Подсобный»));

Выполнить запрос.

5. Сохраните запрос под именем Обновление.

 

1.1.6.Запрос на удаление записей

Запрос на удаление целиком или частично удаляет содержимое таблицы. Например, удалить из таблицы здание, информацию о зданиях, уровень качества которых больше 5.

1. Перейдите в режим конструктора запроса (При необходимости закройте окно добавления таблиц).

2. Выберите команды меню Вид - Режим SQL

3. В окне запроса введите инструкции, составляющие SQL – запрос на удаление записей.

DELETE Здание.[Код здания], Здание.[Уровень качества], Здание.Адрес

FROM Здание

WHERE (((Здание.[Уровень качества])>»5»));

4. Выполните запрос и сохраните его под именем Удаление.

5. Откройте таблицу Здание и проверьте правильность выполнения запроса в режиме таблицы и проверьте наличие в ней удаленных записей.

 

1.1.7.Запрос на добавление записи

Запрос на добавление добавляет одну или более записей в конец таблицы. Например, перенести запись о зданиях, ремонт которых начнется в первом полугодии 2002 года в таблицу Перспектива.

1. Перейдите в режим конструктора запроса (При необходимости закройте окно добавления таблиц).

2. Выберите команды меню Вид - Режим SQL

3. В окне запроса введите инструкции, составляющие SQL – запрос на удаление записей.

 

INSERT INTO Перспектива ([Код здания], [Дата начала])

SELECT Здание.[Код здания], Здание.[Дата начала]

FROM Здание

WHERE (((Здание.[Дата начала])>#1/1/2002# And (Здание.[Дата начала])<#8/1/2002#));

4. Выполните запрос и сохраните его под именем Добавление.

5. Откройте таблицу Перспектива и проверьте правильность выполнения запроса в режиме таблицы и проверьте наличие в ней добавленных записей.

 

2.Самостоятельная работа

1. Скопируйте базу данных Конфеты в свою папку под именем Шоколадные конфеты.

2. Откройте базу данных Шоколадные конфеты и выполните следующие задания с использованием структурированного языка запросов SQL

 

Задание 1: Создайте запрос на создание новой таблицы Дорогие наборы и занесите в нее наборы, вес и цена которых превышают соответственно 375 г. и 150 рублей. Запрос сохраните под именем Создание таблицы.

Задание 2: Создайте запрос, подсчитывающий общее количество заказов каждого заказчика. Суммарное значение рассчитывает функция Sum. Сохраните заказ под именем Групповые операции.

Задание 3: Создайте запрос на обновление названия набора Дары природы на Дары осени. Сохраните запрос под именем Обновление.

Задание 4: Создайте запрос на добавление, добавляющий в таблицу Дорогие наборы записи о наборе с названием Нежность. Запрос сохраните под именем Добавление.

 Задание 5: Создайте запрос на удаление записей о наборах стоимостью от 100 рублей до 150 рублей, число которых на складе превышает 500 штук. Сохраните запрос под именем Удаление записей.