Лабораторная работа № 12. Оператор select. Выборка всех записей из одной таблицы

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

1.Запрос на выборку всей таблицы. В лабораторной работе № 10 такой запрос уже был сформирован средствами Access. Можно упростить вид запроса, если вместо запроса

SELECT Заказы.* FROM Заказы;

Написать запрос

SELECT * FROM Заказы

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

Создание запросов на SQL в Access начинается вызовом конструктора запросов. Для этого в окне базы данных нужно выбрать объект «Запросы», пункт меню «Создать» и в окне «Новый запрос» пункт «Конструктор». Далее выберите таблицу «Заказы» и перейдите в режим SQL. Переход в режим SQL: меню Access ® Вид ® Режим SQL.

Для окончания формирования запроса вставьте в соответствующую позицию запроса символ «*». Затем выполните запрос.

2. Вывод избранных полей, замена имён полей псевдонимами, сортировка записей. Поля таблицы выводятся на экран дисплея в том порядке, в котором они перечислены в запросе. Имена полей при выводе результатов запроса часто неудобны для чтения. Их можно заменить в запросе псевдонимами, как показано в примере:

SELECT КодЗаказа AS Заказ, НазваниеПолучателя AS Получатель, АдресПолучателя AS Адрес, ДатаИсполнения AS Дата
FROM Заказы
ORDER BY НазваниеПолучателя ASC;

В примере КодЗаказа, НазваниеПолучателя, АдресПолучателя и ДатаИсполнения – имена полей в таблице «Заказы». При выводе результатов запроса на экран дисплея имена полей будут заменены соответствующими псевдонимами, указанными после слова AS.

Предложение

ORDER BY НазваниеПолучателя ASC

служит для сортировки отобранных записей по возрастанию (т.е. в алфавитном порядке) значения поля НазваниеПолучателя. Если нужно сортировать по убыванию, то вместоASC нужно использовать DESC (сокращение от descending).

Сформируйте и выполните этот запрос.

3. Вывод записей без дублирования. Сформируйте и выполните следующий запрос

SELECT НазваниеПолучателя AS Получатель
FROM Заказы
ORDER BY НазваниеПолучателя DESC.

Названия получателей многократно повторяются, так как выбраны все записи таблицы. Чтобы не было дублирования записей, добавьте в запрос после слова SELECT слово DISTINCT. Иногда в СУБД режим DISTINCT установлен по умолчанию. Для вывода всех записей в этом случае после слова SELECT вставляется слово ALL.

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

4.1. Умножение. Сформируйте запрос на вывод из таблицы «Заказано» кода товара, цены, количества и общей стоимости заказанного товара. Запрос выглядит так:

SELECT КодТовара,Цена,Количество, Цена*Количество AS Стоимость
FROM Заказано;

Самостоятельно дополните запрос стоимостью со скидкой.

4.2 Использование функций. Функция STR() преобразования в текстовый тип.

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

SELECT КодТовара, str (Цена*Количество/1000)+' тыс. руб' AS Стоимость FROM Заказано;

Для того чтобы в колонке «Стоимость» печатались число и текст, нужно преобразовать число в текстовый тип и объединить с текстом ' тыс. руб.'. Для преобразования служит функция str(<величина числового типа>) и операция слияния «+» (конкатенация).

Сформируйте запрос, в котором из таблицы «Заказы» выбираются 5 полей и результат выводится в две колонки. В первую колонку выводится поле «КодЗаказа», а в колонке с псевдонимом «Адрес клиента» объединены следующие поля:

ИндексПолучателя, СтранаПолучателя, ГородПолучателя, НазваниеПолучателя.

Не забудьте поставить между объединяемыми полями адреса запятую с пробелом. Результат запроса (показаны две первые строки) должен иметь вид:

 

Код заказа	Адрес клиента
	90110, Финляндия, Оулу, Wartian Herkku
	44087, Германия, Мюнстер, Toms Spezialitaten

 

4.3.Функция DATEPART() выделения части даты. Познакомьтесь с описанием этой функции в справке Access (Содержание, раздел «Справочник по языку Visual Basic», пункт «Functions», буква D).

Определите с помощью запроса к таблице «Заказы», за какие годы были поставки товаров.

4.4. Агрегатные функции. (В Access они называются статистическими). Подсчитаем общее количество записей в таблице «Заказы» и количество записей содержащих данные в поле «ОбластьПолучателя», то есть, количество записей с непустым полем «ОбластьПолучателя». Для этого выполним следующий запрос:

SELECT count(*),count(ОбластьПолучателя)
FROM Заказы;

В запросе используется агрегатная функция COUNT(). Используя агрегатные функции MAX(), MIN() и AVG(), составьте запрос для подсчёта максимальной минимальной и средней цены товара в таблице «Товары».

Используя агрегатную функцию SUM(), составьте запрос для подсчёта общей стоимости доставки всех заказанных товаров в таблице «Заказы».

Сохраните все созданные Вами запросы и покажите их преподавателю.

Лабораторная работа № 13. Оператор SELECT … WHERE. Выборка из таблицы записей, удовлетворяющих заданному условию

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

Синтаксис оператора выборки по условию следующий:

SELECT <список полей и выражений>

FROM <имя таблицы>

WHERE <условие>.

Условие – это выражение, принимающее значение «истина» или «ложь». Такое выражение называется предикатом. В предикате могут использоваться:

· поля,

· константы,

· арифметические действия +, -, *, /, возведение в степень **,

· операторы отношения =, <, >, <=,>=, <>,

· логические операции NOT, OR, AND.

В SQL имеются специальные предикаты BETWEEN, IN, LIKE, IS NULL, ANY или SOME, ALL, EXISTS.

1. Использование в условии логических операций. Сформируйте и выполните следующий запрос.

Выбрать из таблицы «Заказано» товары с ценой не меньше ста рублей и не больше двухсот. Отобразить все поля таблицы, кроме поля «КодЗаказа» и сформировать расчётное поле, в котором показать стоимость товара с учётом скидки. Запрос имеет вид:

SELECT КодТовара, Цена, Количество,
Скидка, Цена* Количество*(1- Скидка) AS [Стоимость со скидкой]
FROM Заказано
WHERE Цена>100 And Цена<200;

Обратите внимание на то, что скидка хранится в базе данных не в процентах, а в сотых долях от стоимости товара. Скидка 7% в базе хранится как 0.07.

Измените условие в запросе так, чтобы выбирались товары со скидкой больше 7% и либо имеющие цену больше двухсот рублей либо количество не меньше тридцати.

Отсортируйте выбранные записи в порядке убывания цен.

2. Использование агрегатных функций для отобранных записей. Сформируйте и выполните следующие запросы к таблице «Заказано»:

2.1. Подсчитать суммарную стоимость всех заказов, с ценой < 100 руб.

2.2. Подсчитать количество записей, в которых код заказа принимает значения 10252 или 10255 или >11000.

2.3. Найти минимальную, максимальную и среднюю цены для товаров с количеством, равным 10.

3. Использование в условии выборки функций для работы с датами. Познакомьтесь с описаниями этих функций в справке Access (Содержание, раздел «Справочник по языку Visual Basic», пункт «Functions», буквы С, D).

3.1. Функция CDATE(). Найдём в таблице «Заказы» все заказы, исполненные между 01.07.1996 и 01.01.1997. Для этого нужно преобразовать даты с помощью функции CDATE() из текстового типа в тип «date». Запрос имеет вид:

SELECT *
FROM Заказы
WHERE ДатаИсполнения>=CDATE('01.07.1996') AND ДатаИсполнения<CDATE('01.01.1997');

Сформируйте и выполните этот запрос.

Даты можно вычитать друг из друга. Разность получается в днях.

Самостоятельно сформируйте запрос на выборку из таблицы «Заказы» всех заказов, исполненных после 15.04.1998 и выполненных более чем за 5 дней.

3.2. Функция DatePart() служит для выделения из даты её части, например, года. Познакомьтесь с описанием этой функции в справке Access (Содержание, раздел «Справочник по языку Visual Basic» пункт «Functions», буква D).

С помощью функции DatePart() найдите все заказы, размещённые

a) в I квартале 1997 г.

b) по понедельникам в январе за все годы.

 

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

<выражение> IN (<список значений>)

Найдём в таблице «Заказано» все товары со скидками, равными 10%, 5%; 25% и 30%. Запрос имеет вид:

SELECT Цена, Скидка, КодЗаказа
FROM Заказано
WHERE Скидка In (0.05,0.1,0.25,0.3)
ORDER BY Скидка,Цена;

В запросе записи упорядочены по скидкам и цене в возрастающем порядке.

Самостоятельно найдите в таблице «Заказы» все заказы, размещённые:

a) в мае, августе и декабре в1996 и 1998 годах,

b) 5 августа в 1996, 1997 и 1998 годах.

 

5. Использование в условии выборки диапазона значений (предиката BETWEEN).

Предикат BETWEEN имеет следующий синтаксис:

<выражение> BETWEEN <значение 1> AND <значение 2>

Если значения числовые, то границы диапазона включаются в в выборку.

Запрос на выборку из таблицы «Заказано» товаров с ценами между 500 и 1000 руб. можно составить так:

SELECT *
FROM Заказано
WHERE Цена BETWEEN 500 AND 1000
ORDER BY Скидка,Цена;

Внесите в этот запрос дополнительное условие: или со скидкой между 5% и 10%.

Можно выбирать символьные величины, например, названия стран, которые начинаются с букв из заданной последовательности. Выберем из таблицы «Заказы» все заказы из стран, названия которых начинаются на А, Б, В, …, К:

SELECT *
FROM Заказы
WHERE СтранаПолучателя Between 'А' And 'Л'
ORDER BY СтранаПолучателя

Обратите внимание на то, что диапазон поиска на одну букву больше, чем в условии задачи. Access ищет все названия стран, начинающиеся на А,Б, …,К и имеющие любую длину, а на букву ‘Л’ ищет названия страны длиной в одну букву, то есть страну ‘Л’. Можно в качестве конца диапазона указать ‘Кя’, тогда также будут найдены все страны на букву ‘К’.

 

Самостоятельно:

· выберите из таблицы «Заказы» только поле СтранаПолучателя, при условии, что названия стран начинаются на А, Б, В или Р, С, Т и выборке не должно быть повторений названий стран.

· выберите из таблицы «Заказы» в алфавитном порядке города (поле ГородПолучателя) от Лилля до Парижа.

·

6. Формирование с помощью предиката LIKE условных выражений со строковыми полями. В ACCESS предикат LIKE называют оператором.

Оператора LIKE сравнивает строковое поле со строковым выражением. Пусть, из таблицы «Заказы» нужно выбрать все заказы при условии, что название города, в котором находится получатель, начинается на «Л». Запрос выглядит так:

SELECT *
FROM Заказы
WHERE ГородПолучателя Like 'Л*';

Символ «*» означает «любая последовательность из нуля или более символов». Кроме «*» используются и другие символы групповой замены (willcards).

Познакомьтесь с описанием оператора LIKE в справке Access (Содержание, раздел «Справочник по языку Visual Basic», пункт «Operators», LIKE Operator). В следующей таблице приводятся примеры использования в операторе LIKE символов групповой замены.

Тип совпадения	Образец	Совпадение (True)	Несовпадение (False)
Несколько знаков	a*a	aa, aBa, aBBBa	aBC
	*ab*	abc, AABB, Xab	aZb, bac
Специальный знак	a[*]a	a*a	aaa
Несколько знаков	ab*	abcdefg, abc	cab, aab
Одиночный знак	a?a	aaa, a3a, aBa	aBBBa
Одиночная цифра	a#a	a0a, a1a, a2a	aaa, a10a
Диапазон знаков	[a-z]	f, p, j	2, &
Вне диапазона	[!a-z]	9, &, %	b, a
Не цифра	[!0-9]	A, a, &, ~	0, 1, 9
Комбинированное выражение	a[!b-m]#	An9, az0, a99	abc, aj0

 

 

Сформируйте, используя оператор LIKE, и выполните следующие запросы к таблице «Заказы»:

a) выбрать все заказы с названием города получателя, начинающимся на А, Л или П;

b) изменить предыдущий запрос, выбирая только поле ГородПолучателя и не допуская повторений;

c) выбрать все заказы с названием города получателя, начинающимся на Л, а со второй буквой – «и» или «о»;

d) выбрать названия городов, состоящие из пяти букв и начинающиеся на П;

e) выбрать заказы, в которых адрес получателя сдержит запятую;

f) выбрать заказы, в которых адрес получателя начинается с цифры;

g) выбрать заказы, в которых адрес получателя начинается не с цифры;

h) выбрать заказы, в которых адрес получателя начинается не с букв с C (лат.) по L и не с цифры;

i) выбрать заказы, в которых адрес получателя начинается с цифры и имеет длину, не более 20 символов.

Для определения длины строки используется функция LEN(«строковое выражение»).

Сохраните запросы и покажите их преподавателю.