27012

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

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

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

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

Русский

2013-08-19

77 KB

7 чел.

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


 

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

57869. Чому люди поважають звичаї, традиції і закони? 91.5 KB
  Розвивати уміння оцінювати власні вчинки або вчинки інших людей з точки зору моральних норм; розвивати мислення, уміння знаходити загальне і відмінне; творчо мислити і фантазувати; співпереживати витворам мистецтва;
57870. Mes goûts et mes loisirs 180 KB
  Bonjour, mes enfants! Je suis ravie de vous voir. Notre leçon est consacrée aux goûts et aux loisirs. Nous allons parler de vos préférences, de tout ce que vous aimez ou détestez, je ferai votre connaissance avec le sondage des écoliers français.
57871. Прилагателно име. Граматически особенности. Правопис и употреба 3 MB
  Ход на урока: Днес на нашия урок присъстват гости от цяла Запорожка област но нашата работа ще протече както и друг път. Проверка на домашната работа и поставяне на темата: В. Ето ви образец за домашната работа...
57872. Конспект Лекций по ТОЭ 1.75 MB
  Электрические цепи, в которых получение электрической энергии, её передача и преобразование происходят при неизменных во времени токах и напряжениях, называют цепями постоянного тока. В таких цепях электрические и магнитные поля также не изменяются во времени. Так как токи и напряжения постоянны, то изменения этих величин во времени равны нулю
57873. Пам’ять серця стоїть на посту. Голод 1932-33рр. в Україні як геноцид: мовою документів, через твори У.Самчука «Марія» та В. Барки «Жовтий князь» 174.5 KB
  Барка Жовтий князь знайомство з історичними документами спогадами свідків ознайомити учнів з противоправною політикою радянської держави політикою геноциду щодо українського селянства; сформувати у свідомості учнів уявлення про суб’єктивні причини масового голодомору та його наслідки...
57874. Просвітництво як літературна епоха. Розмаїття виявів літературного життя 72 KB
  Today we are going to listen to student’s reports about three famous representatives of Enlightenment. They are Robert Burns, Daniel Defoe, Heinrich Heine. They lived in different parts of Europe...
57875. Память. Виды памяти 87.5 KB
  Цель урока: дать понятие о видах и качестве памяти определить её роль в формировании индивидуальности; на практике ознакомиться с методами определения памяти; развивать умение тренировать и укреплять память; воспитывать стремление к развитию определенного вида памяти;...
57876. Фізика і мистецтво. Інтегрований урок 55.5 KB
  Це Леонардо да Вінчі. Повідомлення про Леонардо да Вінчі підготували. За свідченням сучасників Леонардо да Вінчі відзначався прекрасною зовнішністю він мав пропорційну структуру був витончений із привабливим обличчям. Леонардо постійно малював і записував.
57877. Классификация млекопитающих. Яйцекладущие и сумчатые млекопитающие 401 KB
  Цель урока: обучающая: Изучить классификацию млекопитающих. Познакомить учащихся с яйцекладущими и сумчатыми млекопитающими, раскрыть особенности размножения и индивидуального развития этих животных: черты сходства и отличия.