Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа № 3
Тема: Использование операций реляционной алгебры для формирования запросов на выборку данных средствами SQL.
Цель работы: Изучить специальные и теоретико-множественные операции реляционной алгебры и возможность их примирения к формированию запросов на выборку данных. Используя синтаксические конструкции вложенных и составных запросов реализовать предложенные естественно-языковые и алгебраические формулировки запросов средствами SQL.
1. Общие сведенья об операторах реляционной алгебры
В SQL предусмотрена возможность выполнения операции реляционной алгебры "ОБЪЕДИНЕНИЕ" (UNION) над отношениями, являющимися результатами оператора SELECT. Естественно, эти отношения должны быть определены по одной схеме. Пример: получить все Интернет - ссылки, хранимые в базе данных publications. Эти ссылки хранятся в таблицах publishers и wwwsites. Для того чтобы получить их в одной таблице, необходимо построить следующие запрос:
SELECT publisher,url FROM publishers
UNION
SELECT site,url FROM wwwsites;
1.2. Использование предикатов IN, BETWEEN, LIKE, is NULL
При задании логического условия в предложении WHERE могут быть использованы операторы IN, BETWEEN, LIKE, is NULL.
Операторы IN (равен любому из списка) и NOT IN (не равен ни одному из списка) используются для сравнения проверяемого значения поля с заданным списком. Этот список значений указывается в скобках справа от оператора IN.
Построенный с использованием IN предикат (условие) считается истинным, если значение поля, имя которого указано слева от IN, совпадает (подразумевается точное совпадение) с одним из значений, перечисленных в списке, указанном в скобках справа от IN.
Предикат, построенный с использованием NOT IN, считается истинным, если значение поля, имя которого указано слева от NOT IN, не совпадает ни с одним из значений, перечисленных в списке, указанном в скобках справа от NOT IN.
Например, получить из таблицы titles сведения о книгах, изданные в 2000, 2001 и 2002 годах.
SELECT *
FROM titles
WHERE yearpub IN (2000, 2001, 2002);
Получить сведения об изданиях, которые не издавались в 2000, 2001 и 2002 годах.
SELECT publisher
FROM ((titles AS a INNER JOIN publishers AS b ON
a.pub_id = b.pub_id)
WHERE yearpub NOT IN (2000, 2001, 2002);
Оператор BETWEEN используется для проверки условия вхождения значения поля в заданный интервал, то есть вместо списка значений атрибута этот оператор задает границы его изменения.
Например, запрос типа: перечислить издательства, которые издали книги в количестве не менее 3 и не более 10 будет выглядеть как
SELECT publisher, COUNT(title)
FROM ((titles AS a INNER JOIN publishers AS b ON
a.pub_id = b.pub_id)
GRROUP BY publisher
HAVING COUNT(title) BETWEEN 3 AND 10;
Граничные значения, в данном случае значения 3 и 10, входят в множество значений, с которыми производится сравнение. Оператор BETWEEN может использоваться как для числовых, так и для символьных типов полей.
Оператор LIKE применим только к символьным полям типа CHAR или VARCHAR (см. л.р. № 1). Этот оператор просматривает строковые значения полей с целью определения, входит ли заданная в операторе LIKE подстрока (образец поиска) в символьную строку-значение проверяемого поля.
Для выборки строковых значений по заданному образцу подстроки можно применять шаблон искомого образца строки, использующий следующие символы:
Например, написать запрос, выбирающий из таблицы title сведения о книгах, в название которых входит слово «история».
SELECT *
FROM titles
WHERE title LIKE ‘*история*’;
Обращаем внимание на то, что рассмотренные выше операторы сравнения «=, <, >, <=, >=, <>» и операторы IN, BETWEEN и LIKE нельзя использовать для проверки содержимого поля на наличие в нем пустого значения NULL. Для этих целей предназначены специальные операторы is NULL (является пустым) и IS NOT NULL (является не пустым).
Например, получить список издателей (publisher), которые не имеют электронного адреса (поле url не заполнено).
SELECT publisher
FROM publisher
WHERE url IS NULL;
1.3. Вложенные (подчиненные) запросы
SQL позволяет использовать одни запросы внутри других запросов, то есть вкладывать запросы друг в друга. Предположим, известно название издательства (VHS), но неизвестно значение поля PUB_ID для него. Чтобы извлечь данные обо всех изданиях, можно сформулировать следующий запрос:
SELECT title
FROM titles
WHERE pub_id = (SELECT pub_id FROM publishers
WHERE publisher = ‘VHS’);
Как работает запрос SQL со связанным подзапросом?
• Выбирается строка из таблицы, имя которой указано во внешнем запросе.
• Выполняется подзапрос и полученное значение применяется для анализа этой строки в условии предложения WHERE внешнего запроса.
• По результату оценки этого условия принимается решение о включении или не включении строки в состав выходных данных.
• Процедура повторяется для следующей строки таблицы внешнего запроса.
Следует обратить внимание, что приведенный выше запрос корректен только в том случае, если в результате выполнения указанного в скобках подзапроса возвращается единственное значение. Если в результате выполнения подзапроса возвращается несколько значений, то этот подзапрос будет ошибочным. В данном примере это произойдет, если в таблице publishers будет несколько записей со значениями поля publisher = ‘VHS’.
В некоторых случаях для гарантии получения единственного значения в результате выполнения подзапроса используется DISTINCT. Одним из видов функций, которые автоматически всегда выдают в результате единственное значение для любого количества строк, являются агрегирующие функции.
Оператор IN также применяется в подзапросах. Он задает список значений, с которыми сравниваются другие значения для определения истинности, задаваемого этим оператором предиката.
Данные обо всех издательствах (таблица publishers), издавших литературу в 2000 году можно выбрать с помощью следующего запроса:
SELECT publisher
FROM publishers
WHERE pub_id IN (SELECT pub_id FROM titles
WHERE yearpub = ‘2000’);
Подзапросы можно применять внутри предложения HAVING.
Пусть требуется определить издательства, количество изданий которых больше чем у издательства ‘VHS’:
SELECT publisher, COUNT(b.pub_id)
FROM publishers AS a INNER JION titles AS b
ON a.pub_id = b.pub_id
GROUP BY publisher
HAVING COUNT(pub_id)>(SELECT COUNT(b.pub_id)
FROM publishers AS a INNER JION
titles AS b
ON a.pub_id = b.pub_id
WHERE publisher = ‘VHS’);
1.4. Формирование запросов на SQL по формулам реляционной алгебры
Любой запрос, сформулированный на естественном языке можно записать в виде формулы реляционной алгебры и наоборот. При переводе запроса с языка формул на язык SQL необходимо, несмотря на последовательность записи операций в формуле, распределить эти операции в соответствии с синтаксисом инструкции SELECT.
Например, реализовать запрос средствами SQL сформулированный в виде формулы реляционной алгебры
Запрос на SQL
SELECT author, title, yearpub
FROM (authors AS a INNER JOIN titleauthors AS b
ON a.au_id = b.au_id) INNER JION titles AS c
ON b.title_id = c. title_id
where yearpub > 1990
2. Задание к лабораторной работе
Замечания по ходу выполнения лабораторной работы.
При выполнении заданий лабораторной работы все вычисляемые поля заменять синонимами, используя опцию AS в предложении SELECT.
Если при выполнении запроса значения повторяются, необходимо использовать параметр DISTINCT.
Реализовать следующие запросы средствами SQL:
Реализовать запросы на SQL, по заданным операциям реляционной алгебры (все имена атрибутов переименовать в естественно-языковую форму).
В следующих запросах, во избежание конфликтов, всегда рекомендуется перед именем поля указывать имя таблицы или соответствующий псевдоним.
Для реализации 5-го запроса воспользоваться эквивалентной формулой, реализующей операцию пересечения через композицию операций вычитания.
Для реализации 6-го запроса использовать в качестве очередного источника данных сохраненный запрос Ti, сформулированный на предыдущем шаге.
3. Оформление отчета
4. Контрольные вопросы