Лабораторная работа ’ 2 Тема- Организация запросов на выборку данных средствами SQL

Работа добавлена на сайт samzan.net:

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

Тема: Организация запросов на выборку данных средствами SQL.

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

Структура лабораторной работы

1.  Общие сведенья по работе запросами.
   1.  Выборка данных.
   2.  Выборка из нескольких таблиц.
   3.  Вычисления в нутрии SELECT.
2.  Задание к лабораторной работе.
3.  Оформление отчета.
4.  Контрольные вопросы.

1. Общие сведенья по работе запросами

1.1. Выборка данных

Для извлечения записей из таблиц в SQL определен оператор SELECT. С помощью этой команды осуществляется не только операция реляционной алгебры "выборка" (селекция), но и предварительное соединение (join) двух и более таблиц. Это наиболее сложное и мощное средство SQL, полный синтаксис оператора SELECT имеет вид:

 

      SELECT [DISTINCT] <список_выбора>

           FROM <имя_таблицы>, ...

           [ WHERE <условие> ]

           [ GROUP BY <имя_столбца>,... ]

              [ HAVING <условие> ]

           [ORDER BY <имя_столбца> [ASC | DESC],... ]

 

Порядок предложений в операторе SELECT должен строго соблюдаться (например, GROUP BY должно всегда предшествовать ORDER BY), иначе это приведет к появлению ошибок.

Все примеры, приведенные ниже, касающиеся базы данных publications, можно выполнить самостоятельно, используя пример лабораторной работы № 1, поэтому результаты запросов на создание таблиц здесь не приводятся.

Выборка данных всегда начинается с ключевого слова SELECT. В конструкции <список_выбора> определяется столбец или столбцы, включаемые в результат. Он может состоять из имен одного или нескольких столбцов, или из одного символа «*» (звездочка), определяющего все столбцы. Элементы списка разделяются запятыми.

Пример: получить список всех авторов

   SELECT author FROM authors;

 

получить список всех полей таблицы authors:

 

   SELECT * FROM authors;

 

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

Например, найдем все книги, опубликованные после 1996 года:

 

   SELECT title FROM titles WHERE yearpub > 1996;

 

Допустим, что надо найти все публикации за интервал 1995 - 1997 гг. Это условие можно записать в виде:

 

   SELECT title FROM titles WHERE yearpub>=1995 AND yearpub<=1997;

 

В заключение заметим, что при выполнении оператора SELECT результирующее отношение может иметь несколько записей с одинаковыми значениями всех полей. Чтобы исключить повторяющиеся записи из выборки используется ключевое слово DISTINCT.

1.2. Выборка из нескольких таблиц

Часто возникает ситуация, когда выборку данных необходимо производить из отношения, которое является результатом соединения двух других таблиц.

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

 

Title	Yearpub	Publisher

 

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

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

SELECT a.title, a.yearpub, b.publisher

        FROM titles AS a, publishers AS b

        WHERE a.pub_id=b.pub_id;

 

Пример, где одновременно задаются условия и соединение, и выборки (запроса ограничивается изданиями после 1996 года):

 

SELECT a.title, a.yearpub, b.publisher

        FROM titles AS a, publishers AS b

        WHERE a.pub_id = b.pub_id AND

              a.yearpub > 1996;

 

Следует обратить внимание на то, что когда в разных таблицах присутствуют одноименные поля, то для устранения неоднозначности перед именем поля указывается имя таблицы и знак "." (точка). (Рекомендуется имя таблицы указывать всегда!). Имя таблицы может быть заменено альтернативным логическим именем – синонимом, для чего используется связка AS в разделе FROM (…FROM titles AS a, publishers AS b…).

Естественно, имеется возможность производить слияние и более чем двух таблиц.

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

 

 SELECT d.author, a.title, a.yearpub,

       b.publisher

  FROM titles AS a, publishers AS b,

       titleauthors AS c, authors AS d

  WHERE c.au_id = d.au_id AND

        c.title_id = a.title_id AND

        a.pub_id = b.pub_id AND

        a.yearpub > 1996;

 

Альтернативный вариант соединения нескольких таблиц может использовать оператор JOIN в оазжеде FROM. Существует три

варианта оператора:

- INNER JOIN – соединение, при котором записи включаются в результирующий набор только в том случае, если в связных атрибутах будут найдены одинаковые значения;

- LEFT JOIN – левое соединение, при котором все записи из первой (левой) таблицы включаются в результирующий набор, даже если во второй (правой) таблице нет соответствующих им записей;

- RIGHT JOIN –правое соединение, при котором все записи из второй (правой) таблицы включаются в результирующий набор, даже если в первой (левой) таблице нет соответствующих им записей.

 

Например, предыдущий пример можно реализовать с использованием оператора

INNER JOIN следующим образом:

 

SELECT d.author, a.title, a.yearpub, b.publisher

 FROM ((titles AS a INNER JOIN publishers AS b ON

        a.pub_id = b.pub_id)

               INNER JOIN titleauthors AS c ON

               c.title_id = a.title_id)

               INNER JOIN authors AS d ON

                c.au_id = d.au_id

 WHERE a.yearpub > 1996;

1.3. Вычисления внутри SELECT.

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

Например, чтобы узнать, сколько лет прошло с 1992 года (год принятия стандарта SQL-92) до публикации той или иной книги можно выполнить команду:

 

    SELECT title, yearpub-1992 FROM titles

   WHERE yearpub > 1992;

 

В арифметических выражениях допускаются операции сложения (+), вычитания (-), деления (/), умножения (*), а также различные функции (COS, SIN, ABS – абсолютное значение и др.).

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

AVG(<имя поля>) - среднее по всем значениям данного поля

COUNT(<имя поля>) или COUNT (*) - число строк

MAX(<имя поля>) - максимальное из всех значений данного поля

MIN(<имя поля>) - минимальное из всех значений данного поля

SUM(<имя поля>) - сумма всех значений данного поля

 

Следует учитывать, что каждая агрегирующая функция возвращает единственное значение.

Примеры:

определить дату публикации самой "древней" книги в базе данных

 

     SELECT MIN(yearpub) FROM titles;

 

подсчитать количество книг в базе данных:

 

     SELECT COUNT(*) FROM titles;

 

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

Например.

Найти количество книг, выпущенных после 2000 года:

 

     SELECT COUNT(*) FROM titles WHERE yearpub > 2000;

 

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

Например:

Найти книги, количество которых после 2000 года превысило 100000 экземпляров

     SELECT title, COUNT(yearpub) AS Количество 

          FROM titles WHERE yearpub > 2000

          GROUP BY title

          HEVING COUNT(yearpub)>100000;

Если в запросе необходимо выполнить сортировку данных необходимо использовать параметр ORDER BY с указанием атрибута, по которому выполняется сортировка. Можно выполнять сортировку в двух направлениях: по возрастанию – параметр ASC (выполняется по умолчанию) и по убыванию – параметр DESC.

Например:

Реализовать предыдущий запрос и при этом отсортировать название книг в обратном алфавитном порядке.

     SELECT title, COUNT(yearpub) AS Количество 

          FROM titles WHERE yearpub > 2000

          GROUP BY title

          HEVING COUNT(yearpub)>100000

          ORDER BY title DESC;

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

 

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

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

 

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

1.  Отобразить все данные таблицы «Справочник материалов»
2.  Получить список поставщиков;
3.  Получить список деталей;
4.  Получить список поставщиков и их адреса;
5.  Получить список номеров деталей, наименования и цены;
6.  Получить список наименований деталей и их цены;
7.  Получить список номеров деталей и их наименования;
8.  Получить соединение таблиц POST и POSTKA;
9.  Получить соединение таблиц POSTKA и DETAL;
10.  Получить соединение таблиц POST, POSTKA, DETAL и SP_MATER;
11.  Перечислить поставщиков, поставляющих деталь Д1;
12.  Вывести имена и адреса поставщиков, поставляющих детали Д1, или Д2;
13.  Вывести имена и адреса поставщиков, поставляющих деталь «Гайка»;
14.  Перечислить номера деталей, их наименования, материал и цены для поставщика «Джон»;
15.  Вывести имена и адреса поставщиков, а также наименования деталей, материал, количество и цены для поставщиков «Джон» и «Коля»;
16.  Вывести имена и адреса поставщиков, а также наименования деталей, материал, количество и цены для поставщиков с номерами «1» и «5», поставляющих детали Д2, или Д3, или Д6;
17.  Перечислить имена, адреса, наименование деталей, материал, количество и цены для поставщиков, поставляющих «Чугунные» или «Стальные» детали;
18.  Перечислить имена, адреса, наименование деталей, материал, количество и цены для поставщиков, поставляющих «Чугунные» или «Стальные» детали, цена которых не менее 3;
19.   То же, но цена находится в пределах не менее 3 и не более 10.
20.  Найти детали, которые поставляются в количестве не меньше 10, и не больше 100.
21.  Получить список деталей поставляемых поставщиком “МАША”, цена которых более 3.
22.  Сколько деталей «Болт» поставляется по всем заказам.
23.  Получить список наименований деталей, суммарная поставка которых превышают 50.
24.  На какую общую сумму поставлено деталей поставщиком «Коля»
25.  Какие поставщики поставили деталь «Корпус» и «ФЛАНЕЦ».

3. Оформление отчета

1.  Титульный лист оформляется согласно традиционным требованиям, включая Номер работы, Номер группы и ФИО студента, а также кто принимал данную работу.
2.  Содержание должно включать Тему лабораторной работы и Ход ее выполнения.
3.  Ход выполнения работы должен содержать все инструкции SQL (SELECT) реализованные в лабораторной работе, включая инструкции создания вычисляемых полей.
4.  Вывод

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

1.  Какие атрибуты называются соединимыми?
2.  Операция проекции.
3.  Операция селекции (выборки).
4.  Операция соединения.
5.  Виды операции соединения.
6.  Соответствие специальных операций реляционной алгебры инструкции SELECT.
7.  Какой будет результат, если при выполнении запроса к двум и более отношениям не использовать никакого условия соединении?
8.  Какие типы соединений (JOIN) поддерживает инструкция SELECT?
9.  В каком случае обязательно указывать имя таблицы перед именем поля?
10.  Как формировать вычисляемые поля в SELECT?
11.  Какой альтернативный синтаксис операции JOIN (с использованием WHERE) можно использовать для корректного выполнения запроса?
12.  В чем отличие предложений WHERE и РУМШТП при задании условия выборки данных?
13.  В каких случаях необходимо использовать параметр GROUP BY?

1. Компьютеры как средство общения людей1
2. х роках ХІХ ст у галузі освіти було проведено нізку реформ зумовлених скасуванням кріпосного права 1861
3. год Уравнения Максвелла для вакуума в интегральной и дифференциальной формах и их физический смысл
4. Longmn Business English Dictionry BBYY LINGUO The Encyclopedi Britnnic Trdos Google Trnslte отраслевой словарь электро
5. тема 1 Причинами нарушения парагипофизарной регуляции могут быть нарушение кровоснабжения гипофиза
6. Тема урока в 3 классе- Правописание падежных окончаний имён существительных в предложном падеже Цель ур
7. Финансовое право Казань2007 Учебнометодический комплекс по курсу
8. на тему- Социальная реклама благотворительных фондов органов опеки и попечительства Выполни
9. а бы Тебе нужно собрать самых надёжных в спортивную
10. экономический институт Ректор акад
11. Все не правы Много лет тому назад мне довелось близко познакомиться с неким Вильямом Леграном
12. вестернизация экономических и политических аспектов жизни многих стран мира позволяют многим делать пред
13. Сущность средних величин и условия их применения
14. Отчет по преддипломной практике СОДЕРЖАНИЕ ПРАВОВЫЕ И ЭКОНОМИЧИСКИЕ ОСНОВЫ ДЕЯТЕЛЬ
15. пороховой бочки Европы и сегодня остаются одним из самых нестабильных европейских регионов
16. Психодиагностика профессионально важных свойств и деловых качеств
17. Российская таможенная академия Ростовский филиал Кафедра конституционного и международного права
18. Анализ финансово-хозяйственной деятельности ЗАО Радуга
19. Введение. С точки зрения теории менеджмента которая по большей части отражает накопленный практический оп
20. Классификация управленческих решений

Материалы собраны группой SamZan и находятся в свободном доступе