лабораторный практикум. Русакова М.С. Лабораторный практикум. Практикум по базам данных самара 2015 министерство образования и науки российской федерации
 Скачать 0.72 Mb.
|
|
Пример Вывод средней, самой высокой и самой низкой арендной платы. SELECT AVG (Rn) as “AVG”, MAX (Rn) as “MAX”, MIN (Rn) as “MIN” FROM Rent / Примечание. Функции AVG и SUM применяются к столбцам с числовыми данными, функции MAX и MIN применяются к данным любого типа. Функция COUNT имеет два варианта формата: COUNT(*) и COUNT(выражение). Функция COUNT(*) возвращает количество всех строк в таблице, включая повторяющиеся строки и строки с неопределенными значениями. Пример Вывод количества всех договоров. 29 SELECT COUNT (*) FROM Lease / В отличие от COUNT(*) функция COUNT(выражение) подсчитывает только строки с определенными значениями в столбце, описанном выражением. Предложение GROUP BY Предложение GROUP BY используется для разбиения строк таблицы на группы. Затем для получения сводной информации по каждой группе можно использовать групповые функции. Указания Если в предложение SELECT включена групповая функция, получить одновременно отдельные результаты можно только в случае, если в предложении GROUP BY задан отдельный столбец. Если список столбцов отсутствует, выдается сообщение об ошибке. Предложение WHERE позволяет исключить некоторые строки до начала разбиения на группы. В предложении GROUP BY должны быть заданы столбцы. В предложении GROUP BY нельзя использовать позиционные обозначения или псевдонимы столбцов. По умолчанию строки сортируются в порядке возрастания в соответствии со списком GROUP BY. Изменить порядок сортировки можно с помощью предложения ORDER BY. Сводные результаты по группам и подгруппам можно получить путем указания, более чем одного столбца в предложении GROUP BY. Пример Вывод суммарной годовой стоимости аренды объектов Ивановым. SELECT Tenant.Tn, (SUM (12* Rent.Rn)) AS ИТОГ FROM Realty, Lease, Tenant, Rent WHERE Lease.AdR = Realty.AdR AND Lease.NTn = Realty.NTn AND Realty.Type = Rent.Type AND Tenant.Tn LIKE ‘%Иванов%’ GROUP BY Tenant.Tn / Предложение HAVING Предложение HAVING задает условие отбора групп для вывода. Следовательно, на группы накладывается дальнейшее ограничение, основанное на сводной информации. Если используется предложение HAVING, сервер Oracle выполняет следующие действия: 1) группирует строки; 2) применяет групповую функцию; 3) производит вывод групп, удовлетворяющих условию предложения HAVING. 30 Предложение HAVING может предшествовать предложению GROUP BY, но более логично ставить предложение GROUP BY первым. Образование групп и вычисление групповых функций происходят до того, как к группам из списка SELECT применяется ограничение, заданное в предложении HAVING. Пример Определить, средняя стоимость каких арендуемых объектов превышает 5000 руб. SELECT Realty.AdR, Realty.Type FROM Realty, Rent, WHERE (Rent.Type=Realty.Type) GROUP BY Realty.AdR HAVING AVG(Rent.Rn)>5000 / Задания Часть 1. Однострочные функции Выведите фамилии владельцев недвижимости с заглавной буквы, а типы недвижимости – через запятую и заглавными буквами. Установите сначала значение арендной платы за аренду гаража неопределенным, а потом сделайте ее равной 3000 руб. Подсказка: воспользуйтесь NVL. Определите дату окончания аренды для всех арендаторов, если дом сдается на год, квартира – на полгода, гараж – на квартал. Выведите дату в формате «27 of October, 2004». Упорядочьте по дате, начиная с самой ранней. Определите дату следующего платежа для каждого арендатора, если платежи производятся ежемесячно по вторникам. Выведите дату в формате «27/X/04». Сделайте сортировку по датам по возрастанию. Определите, какое время прошло от момента аренды. Выведите адрес недвижимости, фамилию арендатора, дату начала аренды в формате «27th Oct 004» и количество месяцев, прошедшее с момента начала аренды, а также количество кварталов, прошедшее с момента начала аренды (значение округлите – подумайте, чем здесь лучше воспользоваться – round или trunk?). Для столбцов создайте псевдонимы, данные упорядочьте по количеству кварталов по возрастанию. Придумайте самостоятельно (и реализуйте) еще 2 запроса, позволяющие продемонстрировать возможности однострочных функций. Часть 2. Групповые функции Определите количество договоров, заключенных до 01.01.2004 г. Результат выведите с логичной значимой подписью. Определите количество владельцев недвижимости, со дня заключения договора с которыми прошло более 2 лет. Результат выведите с логичной значимой подписью. Получите для каждого арендатора суммарную квартальную стоимость аренды, для каждого владельца – суммарную выручку за квартал. Сделайте это двумя запросами. Придумайте и реализуйте собственный запрос на групповые функции. 31 Требования к сдаче лабораторной работы Выполнить запросы с использованием однострочных функций. Каждый запрос должен быть сохранен в отдельный файл. Выполнить запросы на групповые функции. Каждый запрос должен быть сохранен в отдельный файл. Придумать и соответствующим образом оформить ваши собственные запросы. Естественно, они должны быть выполнены, сохранены в отдельные файлы и не должны совпадать с запросами ваших одногруппников. 32 Лабораторная работа №3 Подзапросы Общие сведения Подзапрос – это команда SELECT, вложенная в предложение другой команды SQL. С помощью подзапросов можно создавать из простых команд гораздо более мощные. Это может быть удобно для выборки строк таблицы по условию, зависящему от данных в самой таблице. Подзапросы можно использовать в разных предложениях команд SQL: Предложение WHERE. Предложение HAVING. Предложение FROM команды SELECT или DELETE. Подзапросы очень полезны при написании команд SELECT для выборки значений по неизвестному условному значению. С помощью подзапроса можно находить неизвестные значения. Синтаксис: SELECT список_выбора FROM таблица WHERE выражение оператор (SELECT список_выбора FROM таблица) / Здесь: оператор оператор сравнения (например, >, = или IN). Примечание. Операторы сравнения делятся на два класса: однострочные (>, =, >=, <, <>, <=) и многострочные (IN, NOT IN). Подзапросы иногда называют вложенной командой SELECT, подкомандой SELECT или внутренней командой SELECT. Как правило, подзапрос выполняется первым, а его результат используется для полного определения условия во внешнем запросе. Указания Подзапрос должен быть заключен в скобки. Подзапрос должен находиться после оператора сравнения. В подзапросе нельзя использовать предложение ORDER BY. На каждую команду SELECT разрешается только одно предложение ORDER BY, и если оно используется, то должно быть последним в главной команде SELECT. Команда SELECT может рассматриваться как блок запроса. Этот пример состоит из двух блоков запроса: главный запрос и внутренний запрос. 33 Однострочные подзапросы Однострочный подзапрос возвращает из вложенной команды SELECT только одну строку. В подзапросах этого типа используется однострочный оператор сравнения или логический оператор: =, >, <, >= или <=. Пример Выборка номеров договоров, которые были заключены в тот же день, что и у владельца Иванова. SELECT NLease FROM Lease WHERE LDate = (SELECT Lease.LDate FROM Lease, Owner WHERE (Owner.Ow = 'Иванов') AND (Owner.NOn=Lease.NOn)) / Первой выполняется команда SELECT вложенного блока запроса. Пусть результат запроса – 01-Feb- 05. Далее обрабатывается главный блок запроса. Результат подзапроса используется при этом для вычисления условия поиска. Фактически для сервера Oracle главный запрос будет выглядеть следующим образом: SELECT NLease FROM Lease WHERE LDate = '01-Feb-05' / Можно выводить данные из основного запроса, используя групповую функцию в подзапросе для возврата одной строки. Подзапрос заключается в скобки и помещается после оператора сравнения. Пример Вывод адресов объектов с арендной платой ниже среднего. SELECT Realty.AdR FROM Realty, Rent WHERE (Realty.Type=Rent.Type) AND (Rent.Rn<(SELECT AVG(Rent.Rn) FROM Rent)) / 34 Многострочные подзапросы Подзапросы, возвращающие более одной строки, называются многострочными. В них следует использовать не однострочный, а многострочный оператор – например, IN. Оператор IN ожидает одного или нескольких значений. Предложение HAVING с подзапросами Подзапросы можно использовать не только в предложении WHERE, но и в предложении HAVING. Сервер Огас1е выполняет подзапрос первым и возвращает результаты в предложение HAVING главного запроса. Задания Определить, для каких типов недвижимости минимальная арендная плата не превышает 10000 руб. Посчитать количество арендаторов, готовых платить за недвижимость данных типов. Посчитать количество владельцев недвижимости, готовых предоставлять эту недвижимость в аренду. Посчитать количество договоров, заключенных по недвижимости данных типов. Определить адреса недвижимости и номера договоров, заключенных с одним и тем же владельцем недвижимости. Определить адреса недвижимости и номера договоров, заключенных с одним и тем же арендатором недвижимости. Вывести тип и адрес недвижимости, для которых было заключено 2 и более договоров аренды. Подсчитать количество владельцев недвижимости, сумма выручки которых превысила среднюю арендную стоимость по всем заключенным договорам. Придумать и реализовать 2 собственных запроса, в которых необходимо использовать подзапрос (или подзапросы). 35 Лабораторная работа №4 Определение переменных во время выполнения Для ограничения количества выходных строк можно создать командный файл с предложением WHERE. Для изменения условия при каждом выполнении командного файла используются так называемые переменные подстановки. Значения переменных подстановки могут заменять значения в предложении WHERE, текстовую строку и даже имя столбца таблицы. Отчеты, которые вы получали до сих пор, не были интерактивными. В готовом приложении пользователь запрашивал отчет, который создавался без каких-либо приглашений ввести данные. Объем данных в отчете был предопределен постоянным предложением WHERE. Но SQL*Plus позволяет создавать и интерактивные отчеты, когда пользователя приглашают ввести значения, ограничивающие объем выходных данных. Для создания отчета в командный файл или в отдельные команды SQL включаются переменные подстановки. Переменная выступает в роли контейнера, в котором временно хранятся значения. В SQL*Plus можно использовать переменную подстановки с одним амперсандом для временного хранения значений. SQL*Plus позволяет также определять переменные заранее с помощью команд ACCEPT и DEFINE. Команда ACCEPT считывает строку данных, введенную пользователем, и сохраняет ее в переменной. Команда DEFINE создает переменную и присваивает ей значение. Примеры ограничения интервалов данных Отчет за текущий квартал или период времени Отчет, содержащий данные только по пользователю, который его запросил Получение списка служащих только одного отдела. Другие интерактивные возможности Динамическое изменение верхних и нижних колонтитулов страниц Получение входных параметров из файла, а не от оператора Передача значений между командами SQL. Переменные подстановки с одним амперсандом При создании отчетов пользователь часто заинтересован в динамическом ограничении объема выходных данных. SQL*Plus обеспечивает такую гибкость через переменные пользователя. Задать переменные в своих командах SQL можно с помощью амперсанда (&). Присваивать значение каждой переменной при этом не требуется. Таблица 19. Переменная подстановки с одним амперсандом Пример Описание &переменная Переменная в команде SQL; если переменная не существует, SQL*Plus просит пользователя ввести значение. SQL*Plus не сохраняет переменную после ее использования 36 Пример SELECT AdR, LDate FROM lease WHERE NLease = &n_lease; Текстовые строки и даты в предложении WHERE должны быть заключены в апострофы. Это же правило распространяется и на переменные подстановки. Чтобы не вводить апострофы во время выполнения команды SQL, рекомендуется заключать в них переменную в самой команде SQL. Пример SELECT AdT, NTn FROM tenant WHERE Tn = ‘&tn_name’; Подстановка переменных может использоваться не только в предложении WHERE, но и для подстановки имен столбцов, выражений и текстовых строк. Пример SELECT NLease, &column_name FROM lease WHERE &column = &condition; Определение переменных пользователя Задать переменные можно до выполнения команды SELECT. Для определения и установки значений переменных SQL*Plus предлагает две команды – DEFINE и ACCEPT. Таблица 20. Команды определения переменных пользователя Команда Описание DEFINE переменная = значение Создание переменной типа CHAR и присвоение ей значения DEFINE переменная Вывод переменной, ее значения и типа данных DEFINE Вывод всех переменных пользователя, их значений и типа данных ACCEPT Чтение строки, введенной пользователем, и сохранение ее в переменной Сокращенный синтаксис: ACCEPT переменная [тип_данных] [FORMAT] [PROMPT текст] [HIDE] Здесь: переменная имя переменной, хранящей значение. Если переменная не существует, SQL* Plus ее создаст; 37 тип_данных NUMBER, CHAR или DATE. Максимальная длина для типа CHAR – 240 байтов. DATE сверяется с моделью формата; FORMAT модель формата, например, A10 или 9.999; PROMPT текст текст, который выдается прежде, чем пользователь может ввести значение; HIDE предотвращает отображение данных, введенных пользователем. Примечание: Если для определения переменной используется команда ACCEPT, амперсанд перед параметром подстановки не ставится. Указания Команды ACCEPT и DEFINE создают переменную, если она не существует, а существующую переменную переопределяют; Пользуясь командой DEFINE, не забывайте заключать в апострофы строки, содержащие пробелы; Команда ACCEPT используется в следующих целях: o Для создания приглашения к вводу данных с учетом пожеланий пользователя. В противном случае выдается стандартное приглашение, используемое по умолчанию: «Enter value for variable»; o Для явного определения переменных типа NUMBER и DATE; o Для ввода данных без их отображения в целях секретности. Пример ACCEPT Type_Name PROMPT ‘Задайте тип недвижимости:’ SELECT d.Rn, d1.AdR, d2.LDate FROM rent d, realty d1, lease d2 WHERE d.type = d1.type AND d1.AdR = d2.AdR AND d.Type = ‘&Type_Name’ / Команда UNDEFINE Переменная сохраняет свое значение до: очистки командой UNDEFINE; выхода из SQL*Plus. Отменив определение переменной, можно проверить произведенное изменение с помощью команды DEFINE. При выходе из SQL*Plus переменные, заданные во время сеанса, теряются. Для сохранения переменных от сеанса к сеансу измените свой файл login.sql так, чтобы эти переменные инициализировались сразу после загрузки системы. 38 Передача значений переменных в командный файл Параметр – это значение, которое можно передать в отчет прямо из командной строки. Для создания получения отчета с учетом параметров выполните следующие шаги. Создайте командный файл с командой SELECT. В команде SELECT для каждой переменной используйте ссылку типа «&номер». В командной строке задайте значение переменной прямо за именем командного файла. Значение переменной должно отделяться от имени файла пробелом. Пример SELECT NLease, AdR, LDate FROM Lease WHERE NTn = &1 / Указания Присвоить параметрам значимые имена можно с помощью команды DEFINE. Используйте префикс для различных названий столбцов, обычных переменных и переменных с параметрами. Последовательность значений параметров в командной строке очень важна. Первое значение соответствует переменной &1, второе – переменной &2 и т. д. Запрос может содержать до девяти параметров с именами от &1 до &9. SQL*Plus сохраняет параметры и их значения до следующего переопределения или выхода из SQL*Plus. Задания Создайте командный файл для выборки информации о недвижимости, дата начала аренды которой заключена в пределах определенного диапазона. Выходные данные должны включать фамилии владельцев и арендаторов, адрес недвижимости, ее тип и арендную плату. Даты, определяющие временной диапазон, запрашиваются у пользователя. Используйте формат MM/DD/YY. Создайте командный файл для получения списка имен, адресов проживания и номеров арендаторов определенного типа недвижимости. Тип недвижимости задается с клавиатуры. Поиск должен осуществляться независимо от регистра символов. То же, что и в задании 2, но только для владельцев недвижимости. Создайте отчет, содержащий фамилии владельцев недвижимости, фамилии всех арендаторов, заключивших договор с данным владельцем, выручку владельцев с каждого своего арендатора. Арендная плата начисляется ежемесячно с момента заключения договора. Придумайте и реализуйте еще 2 запроса с использованием переменных подстановки. Требования к сдаче лабораторной работы Каждое задание должно быть сохранено в отдельный командный файл. Ваши 2 запроса должны быть оригинальными (т. е. не совпадать с запросами из книги, заданиями и запросами ваших одногруппников). 39 Лабораторная работа №5 Представления Представление – это логическая таблица, созданная на основе реальной таблицы или другого представления. Представление не содержит собственных данных, а скорее является «окном», через которое можно просматривать или изменять данные из таблиц. Представление хранится в словаре данных как команда SELECT. Преимущества представлений: Ограничивается доступ к базе данных вследствие того, что представления могут отображать определенное подмножество базы данных; С помощью простых запросов пользователь может такие же результаты, как с помощью сложных. Например, использование представлений позволяет осуществить выборку данных из нескольких таблиц без каких-либо знаний об операторе соединения таблиц (join); Обеспечивается независимость данных для пользователей, посылающих случайные незапрограммированные запросы, и прикладных программ. Одно и то же представление может использоваться для выборки данных из нескольких таблиц; Использование представлений позволяет обеспечить доступ к данным по различным критериям для различных групп пользователей. |