27012

Создание и использование представлений

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

Информатика, кибернетика и программирование

Введение в представления. В отличии от них представления это таблицы которые содержат данные других таблиц. В действительности представления это запросы выполняемые всякий раз когда представление является объектом команды. Например: CREATE VIEW СотрудникиМН AS SELECT FROM СОтрудники WHERE №отд = ‘О2; В результате создается представление СотрудникиМН с этим представлением можно выполнять любые операции то есть формировать запросы удалять вставлять соединять с другими таблицами и представлениями.

Русский

2013-08-19

77 KB

8 чел.

Лабораторная работа №7.

Создание и использование представлений.

Цель работы:

приобретение практических навыков по созданию представлений средствами языка SQL, использованию представлений в запросах.

Краткие теоретические сведения.

  1.  Введение в представления.

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

Например:

CREATE VIEW СотрудникиМН

 AS SELECT * FROM СОтрудники

 WHERE №отд = ‘О2’;

В результате создается представление СотрудникиМН, с этим представлением можно выполнять любые операции, то есть формировать запросы, удалять, вставлять, соединять с другими таблицами и представлениями.

Преимуществом представления является то, что оно обновляется автоматически при изменении формирующих его таблиц. Создание представления не фиксируется и вычисляется повторно всякий раз, когда в команде ссылаются на представление. запрос для представления – это фактически запрос к запросу. Например:

SELECT * FROM СотрудникиМН

WHERE ЗРПЛ > 1200000;

Фактически этот запрос будет эквивалентен:

SELECT * FROM Сотрудники

WHERE №отд = ‘О2’

AND ЗРПЛ > 1200000;

В SQL допускается использование групповых представлений, то есть представлений, которые базируются нf команде GROUP BY либо на других групповых представлениях. Предполагается, что каждый день надо отслеживать количество покупателей, имеющих заказы, количество продавцов, получивших заказы и общее количество поступивших заказов:

CREATE VIEW Итогдня

AS SELECT Дата, COUNT(DISTINCT №аренд)

FROM Осмотр GROUP BY Дата;

Затем получать информацию из запроса:

SELECT * FROM Итогдня;

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

CREATE VIEW ВладОбъектыСотр

AS SELECT №влад,a.ФИо As ФиоВлад,№объект,Адрес,Тип,Аренда,№сотр,c.ФИО As ФиоСотр

FROM Владельцы a, Объекты b, Сотрудники c

WHERE a.№влад = b.№влад

 AND b.Сотр = c.Сотр;

SELECT * FROM ВладОбъектыСотр

WHERE ФиоСотр = ‘Иванов’;

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

SELECT a.sname, cname, ame*comm.

FROM ВладОбъектыСотр a, Осмотр b

WHERE a.ФиоСотр = ‘Петров’

 AND b.№объект = a.№объект;

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

CREATE VIEW Esf

 AS SELECT №объект, Адрес, Тип

 FROM ВладОбъектыСотр

 WHERE = Аренда(

 SELECT MAX(Аренда)

 FROM ВладОбъектыСотр);

Для извлечения из представления используется запрос

SELECT * FROM Esf;

Для удаления представления из БД используется команда DROP VIEW <имя представления>.

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

  1.  Изменение значений с помощью представлений.

Команды обновления применительно к представлениям работают следующим образом: воздействуют на базовую таблицу представления. Существуют критерии для определения обновленовляемого представления представления:

  1.  Оно базируется только на одной таблице.
    1.  Должно включать первичный ключ таблицы.
    2.  Не должно содержать ключей, полученных агрегированием.
    3.  Не должно содержать DISTINCT в своем определении.
    4.  Не должно содержать GROUP BY и HAVING в своем определении.
    5.  Не должно содержать подзапросов.
    6.  Может быть ограничено на обновляемом представлении.
    7.  Не может содержать констант, строк или выражений в списке выбираемых выходных полей.
    8.  Для команды INSERT должно включать любые поля, которые имеют ограничения NOT NULL.

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

CREATE VIEW Highrating

 AS SELECT cnum, rating FROM Customers

WHERE rating = 300;

Выполняется следующая команда:

INSERT INTO Highrating

VALUES (2000, 200);

Обновление выполнено не будет. Эта проблема в SQL решается следующим образом: в определение представления добавляется команда:

WITH CHECK OPTION

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

CREATE VIEW Londonstaff

AS SELECT cnum, cname, comm

FROM Salespeople

WHERE city =’London’;

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

Получится представление, в котором будут одинаковые значения для поля city. Чтобы исключить это из выходных данных нужно:

SELECT snum, sname

FROM Londonstaff;

Опция WITH CHECK OPTION действует только на представление, в котором она указана, но не действует на представление, которое базируется на этом представлении.  Например имеется:

CREATE VIEW Highrating

AS SELECT cnum, rating FROM Customers

WHERE rating = 300

WHITH CHECK OPTION;

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

CREATE VIEW Vyrating

AS SELECT *

FROM Highrating;

UPDATE Murating

SET rating = 200

WHERE cnum = 2004;

Задание по работе.

Вариант 1.

  1.  Создать представление, содержащее код раздела, число книг, изданных в прошлом году.
  2.  С помощью созданного представления, определить по каким разделам не поступало книг, изданных в прошлом году.
  3.  Создать представление, содержащее №чит.билета, число взятых книг.
  4.  С помощью созданного представления  составить список читателей, число взятых книг у которых ниже среднего.
  5.  Создать представлениe, содержащее год издания, число экземпляров книг.
  6.  С помощью созданного представления  подсчитать сколько получено экземпляров книг за последние 5 лет.

Вариант 2.

  1.  Создать представление, содержащее фамилию владельца, номер карточки, тип, срок действия.
  2.  С помощью созданного представления отыскать фамилии владельцев и номера карточек, срок действия которых истек.
  3.  Создать представление, показывающее сколько карточек каждого типа выдано.
  4.  С помощью созданного представления определить какого типа карточек выдано меньше всего, какого больше всего.
  5.  Создать представление, показывающее номера карточек , на которые в течении 30 дней не было операций «зачисление».
  6.  С помощью созданного представления отыскать владельцев этих карточек.

Вариант 3.

  1.  Создать представление, содержащее номерподр, таб.номер,зарплата, отсортированное по подразделениям.
  2.  С помощью созданного представления подсчитать бюджет каждого подразделения.
  3.  Создать представление, содержащее информацию о среднем возрасте сотрудников в подразделении.
  4.  С помощью созданного подразделения найти самое молодое подразделение.
  5.  Создать представление, содержащее информацию о подразделениях и должностях, в которых есть вакансии,
  6.  С помощью созданного представления подсчитать сколько в каждом подразделении есть вакантных мест.

Вариант 4.

  1.  Создать представление, содержащее шифр факультета, номер группы, количество студентов.
  2.  С помощью созданного представления определить на каком факультете самая малочисленная группа, самая большая группа.
  3.  Создать представление, содержащее шифр факультета, средний балл на факультете
  4.  С помощью созданного представления составить список факультетов, которые имеют средний балл ниже среднего по вузу.
  5.  Создать представление, которое формирует список студентов по факультетам, имеющим средний балл  и выше.
  6.  С помощью созданного представления отыскать факультет, имеющий наибольшее число таких студентов.

Вариант 5.

  1.  Создать представление, содержащее фамилию водителя, число его командировок, общий километраж их.
  2.  С помощью созданного представления отыскать водителя, который имел наибольшее число командировок, у которого наибольший километраж их.
  3.  Создать представление, содержащее информацию о числе командировок по датам..
  4.  С помощью созданного представления определить дни, когда число командировок было выше среднего.
  5.  Создать представление, содержащее информацию о среднем километраже командировок каждого водителя.
  6.  С помощью созданного представления отыскать стаж и фамилию водителя, у которого наименьший километраж, наибольший километраж.

Вариант 6.

  1.  Создать представление, содержащее число пропусков каждого студента по видам занятий.
  2.  С помощью созданного представления подсчитать число пропусков занятий в каждой группе.
  3.  Создать представление, содержащее фамилию преподавателя, число его дисциплин.
  4.  С помощью созданного представления отыскать преподавателей, за которыми закреплено две дисциплины.
  5.  Создать представление, которое формирует список дисциплин, которые ведут несколько преподавателей.
  6.  С помощью этого представления сравнить число пропусков по этим дисциплинам для каждого преподавателя.

Вариант 7.

  1.  Создать представление, содержащее счет на оплату для каждого постояльца госциницы «Беларусь».
  2.  С помощью созданного представления подсчитать общую выручку госциницы.
  3.  Создайте представление, содержащее информацию о названии отеля, номере комнаты, фамилии постояльца в настоящее время проживающем там.
  4.  С помощью созданного представления определить есть ли свободные места в отелях.
  5.  Создать представление, содержащее информацию сколько каждый постоялец дней пробыл в каждом отеле.
  6.  С помощью созданного представления определить среднее число дней пребывания в каждом отеле.

Вариант 8.

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

3.Создать представление, показывающее производителей и модели цветных лазерных принтеров.

4. С помощью созданного представления отыскать производителей, которые не производят цветные лазерные принтеры.

5.Создать представление, показывающее средние характеристики ПК-блокнотов.

6. С помощью созданного представления получить список ПК-блокнотов с характеристиками выше средних.

Вариант 9.

  1.  Создать представление, показывающее среднюю зарплату по каждой должности.
  2.  С помощью созданного представления определить  в каких странах и городах зарплата по этим должностям ниже средней.
  3.  Создать представление, показывающее какую максимальную сумму кредита может взять каждый сотрудник
  4.  С помощью этого представления Отыскать сотрудников сумма кредита которых  ниже максимальной.
  5.  Создать представление, подсчитывающее среднюю зарплату в каждой организации.
  6.  С помощью созданного представления отыскать организацию, город, страну с наименьшей средней зарплатой.

Вариант 10.

  1.  Создать представление, показывающее горелки , которые зарегистрированы в текущем году и уже ремонтировались.
  2.  С помощью созданного представления определить какой тип горелки ремонтировался чаще всего.
  3.  Создать представление, показывающее число горелок, которое регистрируется каждый день.
  4.  С помощью созданного представления определить какое среднее число горелок регистрируется ежедневно.
  5.  Создать представление, показывающее ежедневную выручку от ремонта горелок.
  6.  С помощью созданного представления определить среднюю ежемесячную выручку.

Вариант 11.

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

2. С помощью созданного представления определить продавцов, имеющих сумму заказов ниже средней.

3.Создать представление, содержащее информацию о заказах по городам.

4. С помощью созданного представления отыскать продавцов, которые работали более чем с тремя городами.

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

6. С помощью созданного представления отыскать покупателя с наибольшей средней суммой заказов.

Вариант 12.

  1.  Создать представление, содержаўее информацию о количестве наименований деталей для каждго изделия.
  2.  С помощью созданного представления наити среднее количество деталей для изделий.
  3.  Создать представление, формирующее заказы для каждого поставщика из города Минска.
  4.  С помощью созданного представления наити все заказы для заданного изделия.
  5.  Создать представление, содержащее информацию о количестве поставщиков  в каждом городе .
  6.  С помощью созданного представления найти город, в котором количество поставщиков равно количеству поставок, сделанных этими поставщиками.

Вариант13.

  1.  Создать представление, показывающее номер детали и количество поставленных деталей.
  2.  С помощью этого представления  определить название деталей с наименьшим количеством и наибольшим количеством.
  3.  Создать представление, показывающее для каждой поставки номер поставщика и на какую сумму выполнена поставка.
  4.  С помощью созданного представления для каждого поставщика подсчитать общую сумму выполненных поставок.
  5.  Создать представление , показывающее поставки города Минска, т.е. поставщик и потребитель из города Минска.
  6.  С помощью созданного представления определить число потребителей для каждого поставщика.

Вариант14.

  1.  Создать представление, содержащее информацию о сотрудниках и объектах недвиж.
  2.  С помощью созданного представления отыскать сотрудников, число объектов которых выше среднего.
  3.  Создать представление, содержащее информацию о владельцах коттеджей.
  4.  С помощью созданного представления подсчитать среднюю арендную плату для коттеджей.
  5.  Создать представление, которое подсчитывает число объектов, закреплённых за каждым отделением.
  6.  С помощью созданного представления отыскать отделения, имеющие максимальное число курируемых объектов.

Вариант 15.

  1.  Создать представление, содержащее информацию о сотрудникх и объектах недвиж.
  2.  С помощью созданного представления отыскать сотрудников, число объектов которых выше среднего.
  3.  Создать представление, содержащее информацию о арендаторах коттеджей.
  4.  С помощью созданного представления подсчитать среднюю арендную плату для коттеджей.
  5.  Создать представление, которое подсчитывает сколько раз осматривался каждый объект недвижимости, закреплённых за каждым сотрудником.
  6.  С помощью созданного представления отыскать объект, который осматривался чаще всего.

Вариант 16.

  1.  Создать представление, показывающее общее число командировик для каждого водителя.
  2.  С помощью созданного представления получить список водителей, число командировок которых выше среднего.
  3.  Создать представление, показывающее число водителей в каждом отделе.
  4.  С помощью созданного представления найти отдел с мин. и макс.ячислом водителей.
  5.  Создать представление, показывающее число командировок, выполняемых каждым отделом ежедневно.

  1.  С помощью представления получить информацию о среднем числе командировок в день.

Вариант 17.

1. Создать представление в кот содержится информация о числе командировок в каждом отделе.

2. С помощью представления отыскать отдел с макс и мин числом командировок.

3. Создать представление,  которое формирует задания на командировки для каждого водителя.

4. С помощью созданного представления получить информацию о заданиях водителя Иванова.

5. Создать представление,  кот показывает число командировок  выполняемых ежедневно.

6. С помощью представления получить информацию о среднем числе командировок в день.

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

  1.  Дайте определение представлению.
  2.  Перечислите достоинства и недостатки, свойственные представлениям.
  3.  Каким требованиям должно удовлетворять представление, чтобы оно могло быть обновляемым?


 

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

18211. Засоби фізичного виховання 143 KB
  Змістовий модуль 1 Тема 3. Засоби фізичного виховання Визначення поняття засоби€ основні та допоміжні засоби фізичного виховання рух€ рухова дія€ рухова діяльність€. Фізичні вправи як основний засіб фізичного виховання. 2.1. Визначення понятт
18212. Методи фізичного виховання 154.5 KB
  Змістовий модуль 1 Тема 4. Методи фізичного виховання. Вихідні поняття метод€ методичний прийом€ методика€ методичний підхід€ методичний напрямок€. Методи навчання рухових дій. Методи вдосконалення та закріплення рухових дій. Методи вдоск...
18213. Основи методики фізкультурно-оздоровчих занять із школярами 158 KB
  Змістовий модуль 6 Тема 12. Основи методики фізкультурнооздоровчих занять із школярами. Здоровя та фактори що на нього впливають. 1.1. Визначення поняття здоровя€. Здоровя в ієрархії потреб людини. 1.2. Здоровий спосіб життя та фактори що впливають на здор
18214. Організаційно-методичні особливості проведення уроку фізичної культури в школі 183.5 KB
  Змістовий модуль 5 Тема 11. Організаційнометодичні особливості проведення уроку фізичної культури в школі. План. Зміст навчальної дисципліни Фізична культура€. 1.1. Аналіз шкільної базової програми Основи здоровя та фізична культура€ Київ 2001 року. ...
18215. Організація і методика фізичного виховання дітей та підлітків з ослабленим здоров’ям 87 KB
  Змістовий модуль 6 Тема 13. Організація і методика фізичного виховання дітей та підлітків з ослабленим здоровям. Особливості організації фізкультурнооздоровчих занять учнів підготовчої і спеціальної медичної груп. 1.1. Організація занять фізичними вправами ...
18216. Планування, контроль та облік навчальної діяльності учнів 141.5 KB
  Змістовий модуль 4 Тема 9. Планування контроль та облік навчальної діяльності учнів. Технологія планування та його функції. 1.1. Функції планування вимоги до планування перспективне поточне оперативне планування. 1.2. Технологія планування встановлення мет...
18217. Принципи побудови процесу фізичного виховання 84.5 KB
  Змістовий модуль 1 Тема 2. Принципи побудови процесу фізичного виховання. Усі явища та процеси у природі і житті підпорядковані певним закономірностям і розвиваються відповідно до них. Ці закономірності існують у природі незалежно від волі людини. П
18218. Загальні основи навчання рухових дій 126 KB
  Змістовий модуль 2 Тема 5. Загальні основи навчання рухових дій. Особливості навчання у фізичному вихованні. 1.1. Зміст спеціальних фізкультурноспортивних знань. 1.2. Класифікація рівнів засвоєння знань. Рухові уміння та навички. 2.1. Характеристика
18219. Загальна характеристика та основи методики розвитку рухових здібностей 262 KB
  Змістовий модуль 3 Тема 6. Загальна характеристика та основи методики розвитку рухових здібностей. Поняття про рухові здібності та основні форми їх прояву. 1.1. Визначення поняття рухові здібності€ потенціальні€ та актуальні€ рухові здібності конди