Лабораторные_БД_ЭВМ_20 (AutoRecovered). Методические указания по выполнению лабораторных работ по дисциплине (модулю)

• Название: Методические указания по выполнению лабораторных работ по дисциплине (модулю)
• Дата: 05.05.2022
• Размер: 0.75 Mb.
• Имя файла: Лабораторные_БД_ЭВМ_20 (AutoRecovered).docx
• Тип: Методические указания; #512616
• страница: 3 из 12

Оборудование

Персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

6. 
   Задание на работу
   

1. Напишите запрос, выбирающий все поля и все строки из таблицы, используемой в предыдущей лабораторной работе. При этом таблица должна содержать 10-15 строк. Приведите содержимое данной таблицы.

2. Напишите запрос, демонстрирующий выбор нескольких (не всех) полей таблицы с удалением дубликатов строк (DISTINCT).

2. Напишите запрос, демонстрирующий выбор всех полей (*) и задание условий выборки в виде операций сравнения (>, <, =) и логических операций (AND, OR, NOT).

3. Напишите запрос, демонстрирующий работу конструкций IN, BETWEEN, IS NULL, IS NOT NULL.

4. Напишите запрос, демонстрирующий работу конструкции LIKE (с символами "%" и "_").

5. Напишите запрос, демонстрирующий вычисление арифметических выражений как в условиях выборки (после WHERE), так и в списке выбора (после SELECT) с заданием имени для результата выражения.

Указания к выполнению заданий 1-5.

а) сформулируете смысл запроса на языке, понятном пользователю в данной предметной области, например, "Запрос, выбирающий данные о фамилии, имени и номере курса для студентов, получающих стипендию больше 1400"

б) приведите сам запрос

в) приведите результат выполнения запроса в виде скриншота. Данные (и параметры в условии выборки) должны быть подобраны таким образом, чтобы в результирующей выборке было 3-4 записи.
7. Контрольные вопросы

1. 
   Дана таблица Рейс. Вывести в убывающем порядке список рейсов, вылетающих не позднее 1 апреля в Москву, Петербург или Самару, стоимость билета не более 1500 р.; в Саратов - не позднее 7 апреля, стоимость билета - от 500 до 800 р.
   
2. 
   Дана таблица Город. Вывести в алфавитном порядке список городов Поволжского региона, в коде которых встречается цифра 9, а в названии города на втором месте стоит буква <д> или <ж>.
   
3. 
   Дана таблица Автор. Вывести в алфавитном порядке фамилии авторов из Самары, в телефонном номере которых на первом или третьем месте стоит цифра от 5 до 8, а последними являются цифры 7 и 8.
   
4. 
   Дана таблица Блюдо. Вывести в алфавитном порядке фамилии поваров, блюда которых относятся к десерту или выпечке, стоимость не превышает 50 руб., а калорийность не больше 300 ккал.
   
5. 
   Дана таблица Рейс. Вывести список рейсов, продолжительность маршрутов которых не более 500 км и не менее 100 км, а стоимость билета - от 800 до 1500 руб.
   

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

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

1. 
   Цели и задачи работы
   

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

2. 
   Порядок выполнения работы
   

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.

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

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

4. 
   Теоретические сведения
   

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

avg(выражение)	smallint, int, bigint, real, double precision, numericили interval	арифметическое среднее для всех входных значений
count(*)		количество входных строк
count(выражение)	any	количество входных строк, для которых значение выражения не равно NULL
max(выражение)	любой числовой, строковый, сетевой тип или тип даты/времени, либо массив этих типов	максимальное значение выражения среди всех входных данных
min(выражение)	любой числовой, строковый, сетевой тип или тип даты/времени, либо массив этих типов	минимальное значение выражения среди всех входных данных
sum(выражение)	smallint, int, bigint, real, double precision, numeric, interval или money	сумма значений выражения по всем входным данным

Например, для определения среднего значения поля MARK (оценки) по всем записям таблицы EXAM _ MARKS можно использовать запрос с функцией AVG следующего вида:

SELECT AVG(MARK)
FROM EXAM_MARKS;
Для подсчета общего количества строк в таблице следует использовать

функцию COUNT со звёздочкой. SELECT COUNT (*)

FROM EXAM _ MARKS;

Аргументы DISTINCT и ALL позволяют, соответственно, исключать и включать дубликаты обрабатываемых функцией COUNT значений, при этом необходимо учитывать, что при использовании опции ALL значения NULL все равно не войдут в число подсчитываемых значений.

SELECT COUNT (DISTINCT SUBJID) FROM SUBJECT;

Предложение GROUP BY (ГРУППИРОВАТЬ ПО) позволяет группировать записи в подмножества, определяемые значениями какого-либо поля, и применять агрегирующие функции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе.

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

SELECT STUDENT_ID, MAX (MARK) FROM EXAM_MARKS GROUP BY STUDENT_ID;

Выбираемые из таблицы EXAM _ MARKS записи группируются по значениям поля STUDENT _ ID , указанного в предложении GROUP BY , и для каждой группы находится максимальное значение поля MARK . Предложение GROUP BY группирует строки таблицы, объединяя их в одну группу при совпадении значений во всех перечисленных столбцах. Порядок, в котором указаны столбцы, не имеет значения. В результате наборы строк с одинаковыми значениями преобразуются в отдельные строки, представляющие все строки группы. Это может быть полезно для устранения избыточности выходных данных и/или для вычисления агрегатных функций, применённых к этим группам. В приведенном запросе рассматриваются группы записей, сгруппированные по идентификаторам студентов.

В конструкции GROUP BY для группирования может быть использовано более одного столбца. Например:

SELECT STUDENT_ID,SUB
J_ID, MAX (MARK) FROM EXAM_MARKS GROUPBY STUDENT_ID, SUBJ_ID;

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

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

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

SELECT SUB J_NAME, MAX(HOUR) FROM SUBJECT GROUP BY SUBJ_NAME HAVING MAX(HOUR)>=72;

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

Влияние NULL – значений в функции count.

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

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

Если бы механизм NULL не был доступен, то неприменимые и отсутствующие значения пришлось бы исключать с помощью конструкции WHERE.

Поведение функции COUNT (*) не зависит от пустых значений. Она возвратит общее количество строк в таблице.

Влияние NULL – значений в функции avg.

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

Функция AVG вычисляет среднее значение всех известных значений множества элементов, то есть эта функция подсчитывает сумму известных значений и делит её на количество этих значений, а не на общее количество значений, среди которых могут быть NULL – значения. Если столбец состоит только из пустых значений, то функция AVG также возвратит NULL.

5. 
   Оборудование
   

Персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.

6. 
   Задания на работу
   

1. 
   Напишите запрос, выбирающий все поля и все строки из таблицы, используемой в данной лабораторной работе. При этом таблица должна содержать 10-15 строк. Приведите содержимое данной таблицы.
   
2. 
   Напишите запрос, демонстрирующий возможности функций MAX и MIN.
   
3. 
   Напишите запрос, демонстрирующий возможности функций AVG и SUM.
   
4. 
   Напишите запрос, демонстрирующий работу конструкций COUNT, COUNT(*) и COUNT(DISTINCT).
   
5. 
   Напишите запрос, демонстрирующий работу конструкции GROUP BY
   
6. 
   Напишите запрос, демонстрирующий работу конструкции HAVING.
   
7. 
   Напишите запрос, демонстрирующий совместную работу конструкций HAVING и WHERE.
   

Указания к выполнению заданий 1-7.

а) сформулируете смысл запроса на языке, понятном пользователю в данной предметной области, например, «Запрос, выбирающий среднее значение стипендии по каждому курсу»

б) приведите сам запрос

в) приведите результат выполнения запроса. Данные (и параметры в условии выборки) должны быть подобраны таким образом, чтобы в результирующей выборке было 3-4 записи (или одна).
7. Контрольные вопросы

1. 
   Определить количество и общую продолжительность разговоров для каждого региона, с городами которого осуществляли телефонную связь абоненты, чьи фамилии содержат слог <-ва->.
   
2. 
   На какую сумму были проданы билеты на рейс до Москвы в день вылета?
   
3. 
   В каком количестве и на какую сумму издавал свои книги автор Борисов в каждом издательстве?
   
4. 
   Даны таблицы Город и Разговор.
   

CREATE TABLE Город ( Код_Города INT , Название VARCHAR(20) NOT NULL, Тариф MONEY, Регион VARCHAR(20))

CREATE TABLE Разговор (Код_Разговора INT , Код_ГородаINTNOTNULL, Фамилия VARCHAR(20), Дата DATETIME NOT NULL, Продолжительность INT NOT NULL)

Рассчитать стоимость каждого телефонного разговора с Москвой

5. 
   Даны таблицы Автор и Книга
   

CREATE TABLE Автор ( Код_Автора INT , Фамилия VARCHAR(50) NULL)

CREATE TABLE Книга ( Код_Книги INT, Название VARCHAR(50) NOT NULL, Цена MONEY, Издательство VARCHAR(50) NOT NULL, Код_Автора INT NOT NULL, Количество INT)

Книги каких авторов были проданы на сумму, превышающую 10000 руб.

6. 
   Даны таблицы
   

CREATE TABLE Город ( Код_Города INT , Название VARCHAR(20) NOT NULL, Тариф MONEY, Регион VARCHAR(20))

CREATE TABLE Разговор (Код_Разговора INT, Код_Города INT NOT NULL, Фамилия VARCHAR(20), Дата DATETIME NOT NULL, Продолжительность INT NOT NULL)

Определить фамилии абонентов, общее время разговоров которых менее 10 мин, а общая стоимость оказалась больше 100 руб.

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

Запросы на объединение отношений
1. Цель и задачи работы

Целью лабораторной работы является изучение и практическое применение запросов на объединение отношений.
2. Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.
3. Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.
4. Теоретические сведения

Можно поместить многочисленные запросы вместе и объединить их вывод используя предложение UNION. Предложение UNION объединяет вывод двух или более SQL запросов в единый набор строк и столбцов. Например, чтобы получить всех продавцов и заказчиков размещенных в Лондоне и вывести их как единое целое вы могли бы ввести:

SELECT snum, sname

FROM Salespeople

WHERE city = 'London'

UNION

SELECT cnum, cname

FROM Customers

WHERE city = 'London';

Как вы можете видеть, столбцы, выбранные двумя командами выведены так как если она была одна. Заголовки столбца исключены, потому что ни один из столбцов, выведенных объединением, не был извлечен непосредственно из только одной таблицы. Следовательно, все эти столбцы вывода не имеют никаких имен.

Кроме того, обратите внимание, что только последний запрос заканчивается точкой с запятой. Отсутствие точки с запятой дает понять SQL, что имеется еще одно или более запросов.

=============== SQL Execution Log ============

| |

| SELECT snum, sname |

| FROM Salespeople |

| WHERE city = 'London' |

| UNION |

| SELECT cnum, cname |

| FROM Customers |

| WHERE city = 'London'; |

| ============================================= |

| |

| ----- -------- |

| 1001 Peel |

| 1004 Motika |

| 2001 Hoffman |

| 2006 Climens |

| |

=============================================

Когда два (или более) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения. Это означает, что каждый запрос должен указывать одинаковое число столбцов и в том же порядке что и первый, второй, третий, и так далее, и каждый должен иметь тип, совместимый с каждым. Значение совместимости типов - меняется. ANSI следит за этим очень строго и поэтому числовые пол должны иметь одинаковый числовой тип и размер, хотя некоторые имена, используемые ANSI для этих типов, являются - синонимами. Кроме того, символьные поля должны иметь одинаковое число символов.

Хорошо, что некоторые SQL программы обладают большей гибкостью чем это определяется ANSI. Типы, не определенные ANSI, такие как DATA и BINARY, обычно должны совпадать с другими столбцами такого же нестандартного типа. Длина строки также может стать проблемой. Большинство программ разрешают пол переменной длины, но они не обязательно будут использоваться с UNION. С другой стороны, некоторые программы (и ANSI тоже) требуют, чтобы символьные поля были точно равной длины. В этих вопросах вы должны проконсультироваться с документацией вашей собственной программы.