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.  Каким требованиям должно удовлетворять представление, чтобы оно могло быть обновляемым?


 

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

13530. Сокрытие информации в субтитрах 198.5 KB
  Сокрытие информации в субтитрах Подготовка к работе По указанной литературе и методическим указаниям изучить основные понятия стеганографии и криптографии уяснить принцип сокрытия информации в субтитрах. Ответить на контрольные вопросы. Контроль
13531. Проведення реєстрації осіб, які виявили бажання пройти зовнішнє незалежне оцінювання 172 KB
  Урок інформатики Проведення реєстрації осіб які виявили бажання пройти зовнішнє незалежне оцінювання в 2013 році. Робота з програмою створення заявиреєстраційної картки. Тема уроку: Проведення реєстрації осіб які виявили бажання пройти зовнішнє незалежне оцін...
13532. Системы счисления и двоичное представление информации в памяти компьютера 218 KB
  Системы счисления и двоичное представление информации в памяти компьютера. Что нужно знать: перевод чисел между десятичной двоичной восьмеричной и шестнадцатеричной системами счисления см. презентацию Системы счислени
13533. Использование информационных моделей (таблицы, диаграммы, графики) 822 KB
  Тема: Использование информационных моделей таблицы диаграммы графики. Перебор вариантов выбор лучшего по какомуто признаку. Что нужно знать: в принципе особых дополнительных знаний кроме здравого смысла и умения перебирать варианты не пропустив ни од...
13534. Построение таблиц истинности логических выражений 501.5 KB
  Тема: Построение таблиц истинности логических выражений. Про обозначения К сожалению обозначения логических операций И ИЛИ и НЕ принятые в серьезной математической логике  неудобны интуитивно непонятны и никак не проявляют аналогии с обычной алгеброй. Ав...
13535. Файловая система 188.5 KB
  Тема: Файловая система. Что нужно знать: данные на дисках хранятся в виде файлов наборов данных имеющих имя чтобы было удобнее разбираться с множеством файлов их объединяют в каталоги в Windows каталоги называются папками каталог можно воспринимать как ко...
13536. Проверка закономерностей методом рассуждений 134 KB
  A5 базовый уровень время 2 мин Тема: Проверка закономерностей методом рассуждений. Что нужно знать: в общемто никаких знаний из курса информатики здесь не требуется эту задачу можно давать детям начальной школы для развития логического мышления в задачах ...
13537. Поиск и сортировка информации в базах данных 1.19 MB
  Тема: Поиск и сортировка информации в базах данных. Что нужно знать: при составлении условия отбора можно использовать знаки отношений меньше или равно больше или равно не равно последовательность выполнения логических операций в сложных ...
13538. Электронные таблицы 455 KB
  Тема: Электронные таблицы. Что нужно знать: адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки например C15 формулы в электронных таблицах начинаются знаком = равно знаки / и в формулах означают соответстве...