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?


 

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

25977. Аудит учета готовой продукции, её отгрузки и реализации 39.5 KB
  Далее уточняется как оценивается готовая продукция; правильность оценки и определения себестоимости каждого вида продукции; правильность расчета отклонений фактической себестоимости от плановой нормативной и составления бухгалтерских проводок по учету готовой продукции; соответствие данных аналитического учета готовой продукции с данными синтетического учета. Полноту оприходования произведенной продукции можно проверить путем составления альтернативного баланса расхода сырья и материалов выхода готовой продукции исходя из нормативных...
25978. Аудит учета нематериальных активов 40 KB
  При анализе системы внутреннего контроля аудитор обращает внимание на следующее: определен ли круг лиц ответственных за сохранность нематериальных активов; каким образом организация обеспечивает неразглашение коммерческой тайны; создана ли комиссия по приемке нематериальных активов; проводится ли инвентаризация нематериальных активов. Чтобы сделать вывод об организации бухгалтерского учета аудитор анализирует учетную политику на момент раскрытия в ней информации: о способах оценки нематериальных активов приобретенных не за...
25979. Холодная пластическая деформация 169 KB
  Основными механизмами сдвиговой пластической деформации кристаллических тел являются скольжение и двойникование. Скольжение - это такое перемещение одной части кристалла относительно другой, при котором кристаллическое строение обеих частей остается неизменным
25980. Аудиторская проверка финансовых вложений 43 KB
  Как и при проверке других активов аудитор исходит из предпосылок: полноты все финансовые вложения отражены в бухгалтерском учете и бухгалтерской отчетности не существует неучтенных финансовых вложений: в бухгалтерском учете и отчетности отражены все приобретенные организацией ценные бумаги и выданные займы; сальдо и обороты по счетам синтетического учета финансовых вложений совпадают с сальдо и оборотами по счетам аналитического учета; сальдо и обороты по счетам в полном объеме перенесены из регистров бухгалтерского учета в Главную книгу и...
25981. АУДИТ УЧЕТА ФИНАНСОВЫХ РЕЗУЛЬТАТОВ И ИХ ИСПОЛЬЗОВАНИЯ 35.5 KB
  Выручка от продукции реализованной на сторону отражается прежде всего на счете 90. Кроме того на данном счете отражается себестоимость реализованной продукции которая включает в себя: себестоимость готовой продукции и полуфабрикатов собственного производства; себестоимость работ и услуг промышленного характера; стоимость покупных изделий; стоимость строительномонтажных и проектноизыскательских работ; стоимость товаров; расходы по перевозке грузов; транспортноэкспедиционные расходы на погрузочноразгрузочные работы; услуги связи; зарплата...
25982. Аудит учета финансовых вложений 40.5 KB
  Законодательные и нормативные документыПри учете и аудите финансовых вложений необходимо руководствоваться следующими законодательнонормативными документами:1. Положение по бухгалтерскому учету Учет финансовых вложений ПБУ 19 02 утвержденное приказом Минфина России от 10. Методические указания по инвентаризации имущества и финансовых обязательств приказ Минфина России от 13 июня 1995 г.
25983. Философия Гераклита. Принципы диалектики. Диалектика и метафизика 25.3 KB
  Принципы диалектики. Согласно его рассуждениям мудрый тот кто не дает названия предметамони меняются Основные принципы диалектики. Гегель расширил понимание диалектики вывел ее из рамки движения мыслиувидел столкновение и объединение противоположностей в самой действительности в истории в культуре. В современных вариантах диалектики практически отсутствует понимания ее как о развитии.
25984. Философия и жизнь Сократа 19.09 KB
  Философия и жизнь Сократа О жизни и деятельности Сократа одного из величайших философов Древней Греции можно узнать лишь по произведениям его современников и учеников в первую очередь Платона потому что сам Сократ письменных источников после себя не оставил. Платон же познакомился с Сократом за восемь лет до гибели последнего когда Сократу было уже за шестьдесят и встреча эта произвела революцию в душе будущего знаменитого философа. Платон же написал и Апологию Сократа из которой можно узнать о некоторых аспектах сократовской...
25985. Платон. Сущность философского идеализма 18.03 KB
  Выделить в творчестве Платона какойлибо аспект и систематически изложить его довольно сложно так как приходится реконструировать мысли Платона из отдельных высказываний которые настолько динамичны что в процессе эволюции мысли порой превращаются в свою противоположность.Систематическое широкое использование математического материала имеет место у Платона начиная с диалога Менон где Платон подводит к основному выводу с помощью геометрического доказательства. Значительно в большей мере чем в гносеологии влияние математики...