4.6Манипулирование данными: изменение существующей информации
Изменение или обновление существующей информации - часто применяемая операция, для выполнения которой используется инструкция UPDATE. Этой инструкцией удобно пользоваться для изменения адресов, номеров телефонов, цен и других сведений, содержащихся в столбцах, т.е. для изменения существующих значений в столбцах таблицы.
Для удаления строк в одной таблице используется инструкция DELETE.
Синтаксис инструкции UPDATE
(сокращенная форма, достаточная для большинства случаев)
UPDATE {<таблица> | <представление>}
SET { имя_столбца = {выражение | DEFAULT | NULL}}[,...n]
[WHERE <условие_поиска >]
Обновляемые столбцы указываются в предложении SET, где простым присваиванием столбцу приписывается новое значение. Если обновляется несколько столбцов, то все они перечисляются через запятую.
Предложение WHERE используется для задания строк, которые должны быть изменены.
Пример 1. Использование арифметического выражения в предложении SET
Уменьшить стоимость всех книг на 2.
UPDATE Titles
SET price=price-2
Пример 2. Использование предложения WHERE
Изменить телефон у одного из авторов с заданным идентификатором.
UPDATE Authors
SET phone=‘234-7898’
WHERE au_id=‘0800’
Если изменилось также и место жительства этого автора, то через запятую необходимо определить новые значения для соответствующих столбцов в предложении SET.
4.7Манипулирование данными: удаление существующей информации
Синтаксис предложения DELETE
(упрощенная форма, достаточная для большинства случаев)
DELETE
[FROM] <имя_таблицы>
WHERE <условие_поиска>
Инструкцию DELETE следует применять с большой осторожностью. Можно непреднамеренно удалить все строки таблицы и срочно искать последнюю резервную копию базы данных.
Пример 1. Удаление строк
Удалить информацию обо всех книгах данного издательства.
DELETE FROM Titles
WHERE pub_id=‘1389’
Из таблицы Titles удаляются все строки, относящиеся к издательству с идентификатором, равным 1389.
-
Порядок выполнения лабораторных работ
Лабораторные работы проводятся в два этапа.
-
Этап 1. Проектирование информационной системы и ее реализация средствами СУБД MS SQL Server
Задание 1. Построить E-R модель (сформировать сущности, отношения и связи между ними). Представить модель в виде диаграммы Чена.
Задание 2. Реализовать для соответствующих моделей запросы на языке реляционной алгебры.
К модели 1.:
получить полную информацию обо всех сданных экзаменах и зачетах в выбранной группе;
получить список всех студентов, не сдавших зачет по выбранной дисциплине;
получить список студентов, не сдавших зачет более чем по 2-м предметам;
получить список студентов, не получивших стипендию;
получить список студентов, получивших ту же оценку на экзамене по выбранному предмету, что и выбранный студент;
получить список предметов, по которым получено одинаковое количество зачетов;
получить список групп, в которых студенты получили заданное количество оценок «пять» по выбранному предмету.
К модели 2.:
получить список клиентов с заданным объектом страховки;
получить список клиентов с заданным объектом страховки и одинаковой страховой суммой;
получить список всех договоров, по которым произошел заданный страховой случай;
получить список всех страховых случаев, происшедших с даты по дату;
получить список клиентов, у которых тот же объект страховки, что и у заданного клиента;
для заданного объекта страховки получить пары (имя клиента и номер договора), по которым произошел страховой случай.
К модели 3.:
получить список всех пациентов с заданным диагнозом;
получить список пациентов, которые обслуживаются тем же доктором, что и заданный пациент;
получить список докторов- однофамильцев с заданным пациентом;
получить список докторов, у которых лечатся пациенты не меньше заданного количества с заданным диагнозом;
получить список пациентов, имеющих те же симптомы болезни, что и у заданного пациента;
для заданного доктора «А» получить список его пациентов, имеющих тот же диагноз, что и выбранный пациент, лечащийся у доктора «В».
К модели 4.:
получить список всех сотрудников, работающих в заданном подразделении;
получить список сотрудников, работающих под руководством того же начальника, что и у выбранного сотрудника;
получить список всех однофамильцев заданного начальника подразделения;
для заданного подразделения получить пары (Ф.И.О. сотрудника, № военного билета), сотрудников, работающих в заданной должности;
получить список всех подразделений, в которых работают сотрудники с ученой степенью (на основании диплома);
получить список сотрудников, состоящий из троек (Ф.И.О. сотрудника, № паспорта, должность), пенсионного возраста. (Обратить внимание на то, что у мужчин и женщин пенсионный возраст разный).
К модели 5.:
получить список всех актеров, снимающихся на заданной киностудии;
получить список кинофильмов, в которых снимаются те же актеры, что и в заданном кинофильме;
получить пары (Ф.И.О. актера, № контракта), занятых в фильмах, выпущенных на заданной киностудии;
получить список киностудий, в которых были сняты фильмы по заданной тематике и в заданном году;
получить список актеров, у которых имеется контракт с киностудией, расположенной в том же городе, в каком проживают эти актеры;
получить список кинофильмов, в которых заданный актер не снялся ни разу.
К модели 6.:
получить список всех матерей, у которых родились девочки;
получить список матерей – однофамилиц, с заданным врачом;
выбрать докторов, у которых пациентами являются дети, родившиеся в тот же день, что и у выбранной матери;
получить список докторов, у которых пациентами являются близнецы;
получить список матерей, у которых родились мальчики, на определенную дату;
для заданного доктора получить пары (мать, ребенок), у которых пол совпадает.
К модели 7.:
получить список всех пассажиров заданного рейса;
получить список пассажиров, вылетающих рейсами до заданного города и имеющих билет на заданное место;
получить список рейсов, на которых вылетали пассажиры с заданными Ф.И.О. и датой вылета;
получить список, состоящий из пар (№ рейса, № места, на которые не проданы билеты);
получить список пассажиров, вылетавших до заданного города в заданном интервале дат;
получить список типов самолетов, летающих по тому же маршруту, что и самолет, на котором летел пассажир с заданным номером билета.
К модели 8.:
получить список всех товаров, находящихся на всех складах;
получить список кладовщиков, принимавших заданный товар;
получить пары (название товара, № акта), отпущенные с заданного склада в заданный день товаров;
получить список складов, на которые был осуществлен прием тех же товаров, что и на заданный склад;
получить номера всех накладных на прием тех же товаров, что и принимал заданный кладовщик;
получить список (наименование товара, номер склада, Ф.И.О. кладовщика), принятых по заданной накладной.
К модели 9.:
получить список поставщиков заданной детали;
получить список всех изделий, в которое входит заданная деталь от заданного поставщика;
получить список потребителей изделия, в которые входит та же деталь, что и производится заданным поставщиком;
получить список изделий, у которых поставщик деталей находится в том же городе, что и потребитель;
получить список потребителей изделия, в которое входит одно и то же количество деталей, производимых разными поставщиками;
получить все номера деталей, которые поставляются только одним поставщиком.
К модели 10.:
получить список всех изданий, выпускаемых заданным издательством;
получить список авторов, которые публикуют заданный вид рукописи в заданном издательстве;
получить список изданий, выпущенных в том же издательстве, в котором публиковался автор, проживающий в том же городе, что и автор с заданной фамилией;
получить список издательств и авторов, расположенных в одном и том же городе;
получить список авторов, опубликовавших одну и ту же рукопись в разных издательствах;
получить пары (автор, издательство) для рукописей, изданных за заданный период времени.
К модели 11.:
получить список всех мат.ценностей (МЦ), требуемых заданному подразделению;
получить список всех подразделений, которым требуется та же МЦ, что и заданному подразделению;
получить список пар (МЦ, поставщик) таких, у которых стоимость МЦ не больше заданной;
получить список пар (МЦ, количество), требуемых подразделениям организации, находящимся в том же городе, что и заданное подразделение;
получить номера заявок от подразделений, которым требуется заданная МЦ;
получить список поставщиков МЦ, находящихся в той же стране, что и подразделение организации, которой эта МЦ требуется.
К модели 12.:
получить список всех исходящих документов за период;
получить список всех корреспондентов, которым пришли письма в тот же день, что и заданному корреспонденту;
получить номера входящих документов от корреспондентов, проживающих в том же городе, что и адресат;
получить пары (номер входящего документа, номер исходящего), у которых темы одинаковы;
получить список всех адресатов, которым были направлены письма по той же теме, что и заданному адресату;
получить список (корреспонденты, номер входящего письма), ответы которым должны быть подготовлены к заданной дате.
К модели 13.:
получить список всех налогоплательщиков, начисляющих заданный вид налога;
получить список налогоплательщиков, переплативших по заданному виду налога;
получить список налогов, по которым произошла недоплата;
получить суммы начисленных налогов налогоплательщиками, которые перечислили деньги в тот же день, что и заданный налогоплательщик;
получить список налогоплательщиков, не перечисливших деньги к заданной дате;
получить список налогоплательщиков, у которых начисленная сумма по заданному виду налога лежит в заданном диапазоне.
К модели 14.:
получить список всех УФПС, которым необходим заданный вид филат.продукции;
получить список УФПС, которые заказали ту же фил. продукцию, что и заданное УФПС;
получить номера заявок от УФПС, которым требуется заданное количество заданной фил.продукции;
получить пары (вид фил. продукции, количество) для УФПС, расположенного в заданном регионе.
К модели 15.:
получить все номера деталей, поставляемых поставщиком из заданного города;
получить номера деталей, поставляемых для всех проектов, обеспечиваемых поставщиком из того же города, где размещен проект;
получить список всех поставщиков, поставляющих одну и ту же деталь для всех проектов;
получить список проектов, использующих по крайней мере одну деталь, производимую заданным поставщиком;
получить список троек (номер детали, количество, поставщик), аналогичных деталям, поставляемым заданным поставщиком;
получить список проектов, которые обеспечиваются деталями от одного поставщика.
К модели 16.:
получить список ведущих артистов всех театров;
получить список спектаклей, в которых занят заданный артист;
получить список театров, в которых играют однофамильцы;
получить пары (название театра, город), в котором идут те же спектакли, что и спектакль, в котором занят заданный артист;
получить список спектаклей, в которых участвуют артисты, живущие в том же городе, что и театр;
получить пары (название театра, спектакль), в которых играют артисты с той же фамилией, что и автор пьесы.
К модели 17.:
получить список собак заданной породы;
получить адреса хозяев собаки с заданными родителями;
получить список хозяев собак заданной породы и заданного окраса;
получить пары (имя хозяев, телефон) всех собак женского пола, у которых «папа» был тем же самым, что и у заданной собаки;
получить список пород собак, хозяева которых проживают на одной улице;
получить список имен собак заданной породы, у которых родители были чемпионами в заданном году.
К модели 18.:
получить список всех поставщиков, производящих заданную ткань;
получить список моделей одежды, у которых поставщики ткани и фурнитуры расположены в одном городе;
получить пары(номер модели, номер изделия фурнитуры), входящих в модель одежды, сшитую из заданной ткани;
получить список всех поставщиков, поставляющих ткань и фурнитуру для моделей заданного сезона;
получить пары (наименование фурнитуры, количество), входящих в модели заданного сезона и заданной ткани;
получить список номеров моделей одежды, сшитых из ткани заданного цвета.
К модели 19.:
получить список всех товаров, продаваемых в заданном магазине;
получить список товаров, поставщик которых находится в том же городе, что и магазин, их продающий;
получить список всех потребителей, купивших товары стоимостью не больше заданной;
получить список поставщиков, которые поставили заданный товар в магазин с заданным адресом;
получить пары (номер товара, название поставщика), продаваемых через магазины, в которых обслуживается заданный потребитель;
получить список пар (магазин, адрес), в которых продается заданный товар от заданного поставщика.
К модели 20.:
получить список всех автомобилей, зарегистрированных в заданном году;
получить список владельцев автомобиля заданной марки;
получить список номеров свидетельств о регистрации АМТС той же марки и того же цвета, что и автомобиль, принадлежащий заданному владельцу;
получить список пар (марка автомобиля, гос.номер), принадлежащих заданному владельцу и зарегистрированному не позднее заданного года;
получить список троек (Ф.И.О. владельца, марка автомобиля, гос.номер) автомашин, выпущенных после заданного года и зарегистрированных в заданном отделении ГАИ;
получить список адресов владельцев автомобилей, зарегистрированных в том же отделениии ГАИ и в том же году, что и автомобиль заданного владельца.
К модели 21.:
получить список членов политклуба по определенной форме;
получить список членов клуба, участвующих в заданном исполнительном органе государственной власти;
получить список членов клуба, имеющих ту же награду, что и заданный член клуба;
определить общую сумму взносов за заданный период времени;
получить список невыполненных поручений и ответственных за них.
Задание 3. Реализация информационной системы в MS SQL Server
Создание новой базы данных
Перед созданием базы данных проверьте установки сервера базы данных: язык (кодовая страница), методика доступа и др. Во всех вариантах рабочий журнал транзакций располагать в том же каталоге, где будет размещена база данных, с тем же названием.
Формирование запросов к БД
Сформировать запросы к построенной базе данных информационной системы в соответствии с выбранной моделью и заданием 2 Этапа 1 выполнения лабораторной работы.
Список вариантов моделей приведен в разделе 7 настоящего документа.
|
