Лабораторная работа № 4. Написание запросов на языке SQL

Данная лабораторная работа, как и предыдущая, посвящена созданию запросов. Мы рассмотрим еще один режим работы с запросами — режим SQL. Нужно понимать, что после создания даже с помощью мастера или в режиме конструктора запрос в базе данных хранится как выражение на языке SQL. Знание SQL нередко позволяет быстрее написать сложные запросы или разобраться при возникновении каких-то проблем.

Задание. Используя базу lib. accdb из второй лабораторной работы, в конструкторе создайте запрос, выводящий список авторов книг из таблицы Book (нам нужны только авторы, без названий книг и т. д.). Добавьте в таблицу данные таким образом, чтобы в ней было несколько книг одного автора. Выполните запрос и обратите внимание на то, что если автор написал несколько книг, то в результатах запроса он будет фигурировать тоже несколько раз. Перейдите в режим SQL, в котором Вы увидите то предложение SQL, которое и является запросом:

SELECT Book. Author FROM Book;

В SQL для указания на то, что нужно отбрасывать повторения, служит ключевое слово DISTINCT. Измените запрос так, как показано ниже, и проверьте его работу:

SELECT DISTINCT Book. Author FROM Book;

Нужно отметить, что в выражении SELECT можно явно указать, что надо возвращать все записи, включая дубликаты. Для этого используется ключевое слово ALL, но обычно его не ставят, так как оно подразумевается, если ничего другого явно не указано.

Обратите внимание, что при выполнении второго запроса, кроме отбрасывания повторяющихся записей, может поменяться порядок их вывода. Если требуется задать определенный порядок сортировки выводимых записей, в режиме SQL это можно сделать, добавив инструкцию ORDER BY, после указываются столбец или столбцы, по которым проводится сортировка. По умолчанию или при явном добавлении ключевого слова ASC сортировка проводится, но возрастанию, если стоит DESC — по убыванию.

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

SELECT DISTINCT Book. Author FROM Book.

ORDER BY Book. Author;

SELECT DISTINCT Book. Author, Book.BookYear.

FROM Book.

ORDER BY Book. Author ASC, Book. BookYear DESC;

Задание. Напишите запрос, выводящий информацию о книгах из таблицы Book в порядке от самых старых к самым новым. Книги с одинаковым годом издания упорядочивайте в списке по названию в алфавитном порядке.

В некоторых случаях нужно вывести не весь сортированный список, а какую-то его часть. Например, нас могут интересовать первые пять записей из списка или первые 10% списка. В подобных случаях в запрос SELECT добавляют соответственно «ТОР п» или «ТОР n PERCENT», где п — количество записей или процент. Например, следующий запрос выведет список из двух самых новых изданий из Book (символ * указывает на то, что нам нужны все столбцы):

SELECT ТОР 2 * FROM Book.

ORDER BY Book. BookYear DESC;

Задание. Напишите запрос, выводящий информацию о книгах, составляющих первую половину общего списка из таблицы Book, упорядоченного по возрастанию года (т.е. самые старые книги).

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

SELECT BookInLib. LibID, Book. Author, Book.Title.

FROM Book INNER JOIN BooklnLib.

ON Book. BookID = BooklnLib. BookID;

Здесь инструкция INNER JOIN указывает на то, что надо соединить таблицы по условию совпадения значений в столбцах Book. BookID и BookInLib.BookID. Причем соединение должно быть «внутренним» — соответствующие значения должны встречаться как в одной, так и в другой таблице. Кроме внутреннего, Access также поддерживает левое и правое внешние соединения — LEFT JOIN и RIGHT JOIN. В первом случае в выборку берутся все записи из левой таблицы и соответствующие им записи из правой, если они есть. Во втором случае — наоборот.

Задание. Скопируйте приведенный выше запрос в свою базу. Выполните его. При необходимости измените набор данных в таблицах так. чтобы в таблице Book были записи, не связанные с записями из таблицы BooklnLib. В запросе замените INNER JOIN на LEFT JOIN, проанализируйте результат. Объясните, почему для нашей структуры БД замена в этом запросе INNER JOIN на RIGHT JOIN не приведет к изменениям в выводимом наборе данных (тут надо вспомнить о внешних ключах, которые создавались в ходе выполнения первой лабораторной).

В предложениях SELECT условия, накладываемые на отбираемые записи, указываются после ключевого слова WHERE. Например, запрос на выборку книг, выпущенных с 1997 по 2001 г.:

SELECT Book. Author, Book. Title, Book. Publisher,.

Book.BookYear.

FROM Book.

WHERE Book. BookYear BETWEEN 1997 and 2001;

Как мы разбирали в предыдущей лабораторной работе, условия могут включать проверку на равенство, неравенство, принадлежность множеству (IN), соответствие текстовой маске (LIKE) и т. д. Условие также может быть составным и включать логические операторы NOT (отрицание), AND (и), OR (или); их приоритет убывает именно в такой последовательности. Например, пусть кроме условия на гол издания нужно проверить принадлежность издательства заранее определенному списку. Запрос может выглядеть следующим образом:

SELECT Book. Author, Book. Title, Book. Publisher,.

Book.BookYear.

FROM Book.

WHERE Book. BookYear BETWEEN 1997 AND 2001.

AND Book. Publisher IN ('Азбука', 'Политехника');

Эквивалентная запись этого запроса может быть такой:

SELECT Book. Author, Book. Title, Book. Publisher,.

Book.BookYear FROM Book.

WHERE Book. BookYear BETWEEN 1997 AND 2001 AND (Book.Publisher = 'Азбука'.

OR Book. Publisher = 'Политехника');

Обратите внимание, что если скобки в условии WHERE не поставить, то логика работы запроса изменится: он будет выводить книги, изданные издательством «Азбука» в указанном диапазоне лет, и любые книги издательства «Политехника». Поэтому очень важно помнить про порядок выполнения операций NOT, AND, OR и правильно использовать скобки в случаях, подобных рассмотренному выше.

Задание. Напишите на SQL запрос, который выведет в обратном алфавитном порядке названия книг, выпущенных после 1999 г., фамилия автора (значение поля Author) в котором: начинается с буквы «Г» или название издательства заканчивается буквой «а» .

Задание. Напишите на SQL запрос, возвращающий список названий неиспользуемых статусов книг, т. е. таких, что данный статус не назначен ни одной книге в таблице BooklnLib. Подсказка: нужно использовать внешнее соединение таблиц и условие IS NULL. При необходимости отредактируйте используемый набор данных, чтобы такие статусы были, и проверьте работу запроса.

Задание. Опишите, что на ваш взгляд делает приведенный ниже запрос. Скопируйте его код в Access и выполните. Правильным ли оказалось ваше предположение?

SELECT *.

FROM Book.

WHERE Author LIKE 'A*' OR BookYear> 2000.

AND NOT Publisher LIKE ' [И, П] * ';