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

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

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

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

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

WHERE <условие>.

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

- поля,

- константы,

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

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

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

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

NULL, ANY или SOME, ALL, EXISTS.

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

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

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

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

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

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

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

подсчитать количество записей, в которых код заказа принимает значения 10252 или 10255 или больше 11000;

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

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

Функция 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 дней.

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

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

a) в первом квартале 1997 г;

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

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

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

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

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

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

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

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

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

Использование в условии выборки диапазона значений Диапазон значений задаётся с помощью предиката 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 ищет все названия стран, начинающиеся на А,Б, …,К и имеющие любую длину, а на букву ‘Л’ ищет названия страны длиной в одну букву, то есть страну ‘Л’. Можно в качестве конца диапазона указать ‘Кя’, тогда также будут найдены все страны на букву ‘К’.

Самостоятельно выберите из таблицы «Заказы:

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

b) алфавитном порядке города (поле ГородПолучателя) от Лилля до Парижа.

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

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

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

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

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

Таблица 2

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

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

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

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

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

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

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

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

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

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

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

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