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?


 

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

80869. Правовые основы местного самоуправления 42.55 KB
  По вопросам осуществления отдельных государственных полномочий переданных органам местного самоуправления федеральными законами и законами субъектов Российской Федерации могут приниматься муниципальные правовые акты на основании и во исполнение положений установленных соответствующими федеральными законами и или законами субъектов Российской Федерации. За неисполнение муниципальных правовых актов граждане руководители организаций должностные лица органов государственной власти и должностные лица органов местного самоуправления несут...
80870. Территориальная организация местного самоуправления 46.64 KB
  МСУ осуществляется на всей территории РФ в городских сельских поселениях муниципальных районах городских округах и на внутригородских территориях городов федерального значения. В городах районного значения поселках волостях и сельсоветах на территории района имеются территориальные администрации подчиненные администрации района. 2 территории всех поселений за исключением территорий городских округов а также возникающие на территориях с низкой плотностью населения межселенные территории входят в состав муниципальных районов; 3...
80871. Предметы ведения и полномочия местного самоуправления 44.5 KB
  Предметы ведения и полномочия местного самоуправления. ФЗ №131 Об общих принципах организации местного самоуправления в Российской Федерации от 6. Это основополагающий базовый закон устанавливающий общие правовые территориальные организационные и экономические принципы организации местного самоуправления в России определяющий предмет ведения и полномочия его органов. Предметы ведения местного самоуправления необходимый элемент правового статуса каждого муниципального образования.
80872. Формы участия граждан в осуществлении местного самоуправления в муниципальном образовании 46.48 KB
  Предусматривает что МСУ осуществляется гражданами путем референдума выборов других форм прямого волеизъявления через выборные и другие формируемые ОМСУ. включает формы непосредственной демократии когда сам гражданин непосредственно принимает участие в решении того или иного вопроса. группу составляют формы представительной демократии выборные и другие ОМСУ когда участие гражданина состоит в выборах своих представителей для решения вопросов местной жизни.
80873. Организация территориального общественного самоуправления в муниципальном образовании 45.57 KB
  В соответствии с 131ФЗ Об общих принципах организации местного самоуправления в РФ: ТОС самоорганизация граждан по месту их жительства на части территории муниципального образования территориях поселений не являющихся муниципальными образованиями микрорайонов кварталов улиц дворов и других территориях для самостоятельного и под свою ответственность осуществления собственных инициатив в вопросах местного значения непосредственно населением или через создаваемые им органы территориального общественного самоуправления. Границы...
80874. Структура органов местного самоуправления: состав, полномочия 48.43 KB
  В субъектах РФ в городах федерального значения Москве и Санкт-Петербурге в соответствии с уставами и законами соответствующих субъектов РФ могут не создаваться выборные городские органы местного самоуправления; 2 другие органы образуемые в соответствии с уставами муниципальных образований структура органов местного самоуправления определяется населением самостоятельно. Образование органов местного...
80875. Организация аттестации муниципальных служащих 42.43 KB
  Порядок проведения аттестации Порядок проведения аттестации муниципальных служащих определяется путем принятия положения о проведении аттестации муниципальных служащих каждым муниципальным образованием в соответствии с Типовым положением о проведении аттестации муниципальных служащих. Для проведения аттестации на каждого муниципального служащего готовятся аттестационный лист муниципального служащего и отзыв об исполнении подлежащим аттестации муниципальным служащим должностных обязанностей за аттестационный период. Отзыв представляется не...
80876. Муниципальное образование как объект управления 43.36 KB
  Федеральный закон №131 Об общих принципах организации МСУ в РФ: МО это городское или сельское поселение муниципальный район городской округ либо внутригородская территория города федерального значения. Сельское поселение один или несколько объединенных общей территорией сельских населенных пунктов поселков сел станиц деревень хуторов кишлаков аулов и других сельских населенных пунктов в которых МСУ осуществляется населением непосредственно и или через выборные и иные органы МСУ; Городское поселение город или поселок в...