17214

Запросы с использованием логических операторов. Вложенные запросы

Лабораторная работа

Информатика, кибернетика и программирование

Лабораторная работа № 3 Тема: Запросы с использованием логических операторов. Вложенные запросы. Цель работы: Изучить синтаксис инструкции SQL – SELECT при использовании логических операторов и составных запросов на выборку данных. Используя предложения WHERE и/или HAVING...

Русский

2013-06-30

53.5 KB

7 чел.

Лабораторная работа № 3

Тема:  Запросы с использованием логических операторов. Вложенные запросы.

Цель работы: Изучить синтаксис инструкции SQL SELECT при использовании логических операторов и составных запросов на выборку данных. Используя предложения WHERE и/или HAVING задать условия, для которых параметры отбора неизвестны. Изучить организацию вложенных запросов на выборки данных.

1. Логические операции.

В том случае, когда необходимо получить не все записи, а только те, которые удовлетворяют некому условию, это условие можно указать после ключевого слова WHERE. Допустим, что надо найти все публикации (БД publications) за интервал 1995 - 1997 гг. Это условие можно записать в виде с использованием логической операции BETWEEN – проверки на вхождение в интервал:

   SELECT title FROM titles WHERE yearpub BETWEEN 1995 AND 1997;

При использовании конструкции NOT BETWEEN находятся все строки, не входящие в указанный диапазон.

Еще один вариант этой команды можно построить с помощью логической операции IN – проверки на вхождение в список:

   SELECT title FROM titles WHERE yearpub IN (1995,1996,1997);

Здесь мы задали в явном виде список интересующих нас значений. Конструкция NOT IN позволяет найти строки, не удовлетворяющие условиям, перечисленным в списке.

2. Формат выбора данных по неполному значению.

Некоторые задачи нельзя решить с использованием только операторов сравнения. Например, необходимо найти web-site издательства "Wiley", но неизвестно его точного наименования. Для решения этой задачи используется ключевое слово LIKE, его синтаксис имеет вид:

   WHERE <имя_столбца> LIKE <образец>;

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

* (звездочка) - заменяет любое количество символов;

? (знак вопроса) - заменяет одиночный символ;

# (решетка) - заменяет одиночную цифру.

Найдем искомый web-site:

   SELECT publiser, url FROM publishers WHERE publisher LIKE '*Wiley*';

В соответствии с шаблоном СУБД найдет все строки, включающие в себя подстроку "Wiley".

Другой пример: найти все книги, название которых начинается со слова "SQL":

   SELECT title FROM titles WHERE title LIKE 'SQL*';

В том случае, когда надо найти значение, которое само содержит один из символов шаблона, необходимо использовать квадратные скобки [<ключевой_символ>]. Литерал, следующий в шаблоне после ключевого символа, рассматривается как обычный символ, все последующие символы имеют обычное значение.

Например, надо найти ссылку на web-страницу, о которой известно, что в ее url содержится подстрока "my#_works":

  SELECT site, url FROM wwwsites WHERE url LIKE '*my[#]_works*';

3. Группировка данных.

Группировка данных в операторе SELECT осуществляется с помощью ключевого слова GROUP BY и ключевого слова HAVING, с помощью которого задаются условия разбиения записей на группы.

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

Например, определить количество книг каждого издательства в БД publications.

     SELECT publishers.publisher, COUNT(titles.title)

         FROM titles INNER JOIN publishers ON

              titles.pub_id=publishers.pub_id

         GROUP BY publisher;

Ключевое слово HAVING работает следующим образом: сначала GROUP BY разбивает строки на группы, затем на полученные наборы накладываются условия HAVING.

Например, устраним из предыдущего запроса те издательства, которые имеют только одну книгу:

     SELECT publishers.publisher, COUNT(titles.title)

        FROM titles INNER JOIN publishers ON

             titles.pub_id=publishers.pub_id

        GROUP BY publisher

          HAVING COUNT(titles.title)>1;

Другой вариант использования HAVING - включить в результат только те издательства, название которых оканчивается на подстроку "Press":

   SELECT publishers.publisher, COUNT(titles.title)

        FROM titles INNER JOIN publishers ON

             titles.pub_id=publishers.pub_id

        GROUP BY publisher

           HAVING publisher LIKE '*Press';

Примечание. В чем различие между двумя этими вариантами использования HAVING? Во втором варианте условие отбора записей можно поместить в раздел предложения WHERE, в первом же варианте этого сделать не удастся, поскольку WHERE не допускает использования агрегирующих функций.

4. Сортировка данных.

Для сортировки данных в операторе SELECT используется ключевое слово ORDER BY. С его помощью можно сортировать результаты по любому столбцу или выражению, указанному в <списке_выбора>. Данные могут быть упорядочены как по возрастанию, так и по убыванию. Пример: сортировать список авторов по алфавиту:

   SELECT author FROM authors ORDER BY author;

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

SELECT authors.author,titles.title,titles.yearpub,

      publishers.publisher

 FROM ((titleauthors INNER JOIN authors ON

                    titleauthors.au_id=authors.au_id)

                    INNER JOIN titles ON

                    titleauthors.title_id=titles.title_id)

                    INNER JOIN publishers ON

                    titles.pub_id=publishers.pub_id

 ORDER BY authors.author ASC, titles.yearpub DESC;

Ключевое слово DESC задает обратный порядок сортировки по полю yearpub, ключевое слов ASC (его можно опускать) - прямой порядок сортировки по полю author.

5. Вложенные (подчиненные) запросы.

Наиболее полно преимущества ключевого слова IN проявляются во вложенных запросах, также называемых подзапросами. Предположим, нужно найти все издания, выпущенные компанией "Oracle Press". Наименования издательских компаний содержатся в таблице publishers, названия книг в таблице titles. Ключевое слово IN позволяет объединить обе таблицы (без получения общего отношения) и извлечь при этом нужную информацию:

SELECT title FROM titles WHERE pub_id IN

(SELECT pub_id FROM publishers WHERE publisher='Oracle Press');

При выполнении этой команды СУБД вначале обрабатывает вложенный запрос по таблице publishers, а затем его результат передает на вход основного запроса по таблице titles.

Следующий пример позволяет выбирать данные по параметру, вычисленному вложенным запросом.

Например, найти название и год издания книг автора W.J. Gordon.

SELECT titles.title, titles.yearpub

FROM titles

WHERE title_id = (SELECT title_id FROM titleauthors

                 WHERE au_id = (SELECT au_id FROM authors

                                WHERE author = ‘W.J. Gordon’));

6. Задание к лабораторной работе

Замечания по ходу выполнения лабораторной работы.

Для просмотра результата выполнения запросов необходимо чтобы в таблицах были внесены данные соответствующими сформулированным запросам. При этом данные в запросах (даты, фамилии, количество и т.п.) могут быть изменены по факту внесенных данных в БД.

При выполнении заданий лабораторной работы все вычисляемые поля заменять синонимами, используя опцию AS в предложении SELECT.

Например: SELECT COUNT(*) AS Количество_строк FROM titles;

Реализовать следующие запросы средствами SQL:

Получить список заказов, количество которых составляет более 3.

Получить список изделий и их цены, отсортировать список по возрастанию цены.

Найти заказы в поле «Адрес» которых присутствует слово (название улицы) «пр. Московский».

Получить список заказчиков, которые сделали заказы для изделий с номерами 118, 136, 141, 148 (использовать предикат IN).

Найти заказы, по которым сумма поставок превышает 5.

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

Получить список изделий, для которых в поле «Характеристика» присутствует слово «металлический».

Получить список заказчиков, которые заказали изделий больше, чем заказала фирма ООО «Рога и Копыта».

Найти заказы, которые были заказаны после заказа сделанного фирмой ООО «Рога и Копыта».

Получить список заказчиков, у которых количество заказов столько же, как и у фирмы ООО «Рога и Копыта».

7. Контрольные вопросы

Чем отличаются предложения WHERE и HAVING?

Что вычисляет функция COUNT?

Как используется предложение GROUP BY?

Что задает предложение ORDER BY?


 

А также другие работы, которые могут Вас заинтересовать

37656. Генератор высокой частоты ГВЧ 1.11 MB
  Цель работы: определить неисправность в генераторе высокой частоты ГВЧ. Оборудование: осциллограф С1 – 101, тренажер Т – 97, вольтметр В7 – 20.
37657. МІЖНАРОДНО-ПРАВОВА ВІДПОВІДАЛЬНІСТЬ МІЖНАРОДНИХ ОРГАНІЗАЦІЙ. СПІЛЬНІ І ОСОБЛИВІ РИСИ В ПОРІВНЯННІ З ВІДПОВІДАЛЬНІСТЮ ДЕРЖАВИ 285 KB
  Проблема відповідальності в міжнародному праві безпосередньо пов’язана з функціонуванням міжнародного права та забезпеченням миру і правопорядку у світі. Таке розуміння цього міжнародно-правового інституту характерне, як для міжнародної наукової доктрини, так і для вітчизняної.
37658. Радиопередающее устройство РПрдУ 1.14 MB
  Цель работы: определить неисправность в радиопередающем устройстве РПрдУ. Оборудование: осциллограф С1 – 101, тренажер Т – 97, вольтметр В7 – 20.
37659. Расчет параметров силового трансформатора 317.5 KB
  Рассчитываем ЭДС наводимую в одном витке Находим ожидаемое падение напряжения в обмотках трансформатора: для первичной обмотки для вторичных обмоток . Находим предварительно число витков для обмоток трансформатора: для первичной обмотки число витков ; для вторичных обмоток . Принимаем: Вычисляем индукцию в сердечнике при работе трансформатора на холостом ходу Определяем удельные потери в стали магнитопровода: при при Вычисляем составляющую тока первичной обмотки зависящую от токов вторичных обмоток: Определяем...
37660. Тепловой расчет блока 137.5 KB
  Вычисляем приведенную высоту деталей размещенных на кассете: 2. Вычисляем поверхность рабочей зоны охватывающей четыре кассеты 5. Вычисляем площадь поверхности кассет обращенных друг к другу: 6. Вычисляем значение коэффициента 9.
37661. Расчет транзисторного ключа 157.5 KB
  Задержка включения как указывалось обусловлена наличием входной емкости Свх транзистора.1б: где – постоянная времени входной цепи транзистора; – сопротивление резистора в этой цепи.1в имеет Исходные данные: питание коллектора Ек = 13В; сопротивление коллектора Rк = 17кОм; сопротивление базы Rб = 23 кОм; напряжение В; постоянная времени транзистора в схеме с общим эмиттером коэффициент усиления транзистора емкость коллектора Ск = 5пФ; емкость эмиттера Сэ = 5пФ; напряжение порога переключения транзистора Требуется...
37662. Расчет надежности двухканального усилителя 421.5 KB
  Обеспечение надежности является одной из основных задач техники. Надежностью называют свойство изделия выполнять заданные функции, сохраняя во времени значения установленных эксплуатационных показателей в заданных пределах, соответствующих заданным режимам и условиям использования, технического обслуживания, хранения и транспортирования.
37663. Расчет транзисторного мультивибратора в автоколебательном режиме 161 KB
  Мультивибраторами называют электронные устройства, генерирующие электрические колебания, близкие по форме к прямоугольной. Спектр колебаний, генерируемых мультивибратором, содержит множество гармоник - тоже электрических колебаний, но кратных колебаниям основной частоты, что и отражено в его названии: мульти - много, вибро - колеблю.
37664. Анализ и расчет технологичности двухканального усилителя 664.5 KB
  Практическая работа №03 Анализ и расчет технологичности двухканального усилителя Под технологичностью изделия понимается определённое количество параметров выпускаемого на производстве изделия технологической подготовки и производственного процесса от которых в результате зависит качество изделия. Комплексный показатель технологичности рассчитывается с использованием базовых показателей по следующей формуле: где: Кi базовый показатель технологичности; φi коэффициент характеризующий весовую значимость базового показателя технологичности;...