Задания. Модификация структуры таблицы(1)

Задание. В таблице «Дисциплина» создать поле «исполнитель», определенное на домене TEXT (8) — внешний ключ для связи с таблицей «Кафедра» по полю «шифр» («кафедра читает курс»). В качестве ограничения ссылочной целостности по связи между таблицами «Дисциплина» и «Кафедра» задайте стратегию ON UPDATE CASCADE ON DELETE SET NULL. Изменение шифра кафедры-исполнителя в таблице «Кафедра» будет передано по каскаду в таблицу-потомок «Дисциплина» и приведет к изменению внешнего ключа «исполнитель». Удаление строки родительской таблицы «Кафедра» не должно привести к удалению соответствующих строк.

ALTER TABLE Дисциплина

ADD COLUMN исполнитель TEXT (8),

ADD CONSTRAINT FK_Исполнитель

FOREIGN KEY (исполнитель) REFERENCES Кафедра (шифр)

ON UPDATE CASCADE

ON DELETE SET NULL;

Задания. Ввод (добавление) данных в таблицу(2)

Задание. Добавить в таблицу «Студент» данные о новом студенте: «рег_номер» = '11111', «номер»= '11.11.11', «фамилия»= 'Одинцов О. О.' (вставить строку). Объясните причину неудачи.

Insert into Студент (рег_номер, номер, фамилия)

VALUES ('11111', '11.11.11', 'Одинцов О. О.'); Причина неудачи: есть ограничение уникальности на поле "номер" (TEXT(8))

Задание. Добавить в таблицу «Специальность» данные о новой специальности: «номер»= '11.11.11', «название»='Новая специальность', «шифр»='ис' (вставить строку).

Insert into Специальность (номер, направление, шифр)

Values ('11.11.11', 'Новая специальность', 'ис'); задания. Обновление данных. (5)

Задание. Инженеров кафедры «Информационные системы» (шифр ='ис') перевести в подчинение сотрудника с табельным номером 201 (столбец «шеф»). Для конъюнкции двух условий использовать оператор AND.

UPDATE Инженер

SET таб_номер = '201'

WHERE таб_номер IN (SELECT таб_номер

FROM Сотрудник

WHERE шифр = 'ис');

Задание. Увеличить объем преподавания информатики на 20 часов.

UPDATE Дисциплина

SET объем = объем + 20

WHERE код = 'Инф001'; -- Замените 'Инф001' на код соответствующей дисциплины по информатике

Задание. Преподавателю с таб_номером = 102 присвоить звание профессора.

UPDATE Преподаватель

SET звание = 'Профессор'

WHERE таб_номер = '102';

Задание. Перевести студента с заданным номером («рег- номер») на специальность с заданным номером («номер»). Компоненты команды UPDATE задать параметрически.

UPDATE Студент

SET номер = @номер_param

WHERE рег_номер = @рег_номер_param;

Задание. Удалить из таблицы «Специальность» строку о специальности («номер») '09.03.03'. Объяснить причину неудачи.

DELETE FROM Специальность

WHERE номер = '09.03.03';

Задания. Простые запросы(13)

Задание. Выполнить проекцию таблицы «Экзамен» на атрибуты «код» и «таб_номер» (по какой дисциплине кто экзаменует). Выборка строк по условию (селекция)

SELECT код, таб_номер

FROM Экзамен

Задание. Вывести некоторые поля таблицы «Сотрудник» («шифр», «должность» и «зарплата»).

SELECT шифр, должность, зарплата

FROM Сотрудник;

Задание. Вывести список сотрудников данной кафедры. Шифр кафедры задать параметрически.

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

FROM Сотрудник

JOIN Кафедра ON Сотрудник.шифр = Кафедра.шифр

WHERE Кафедра.шифр = 'Ваш_Шифр_Кафедры';

Задание. Вывести список дисциплин, которые ведет данная кафедра. Шифр кафедры (столбец «исполнитель») задать параметрически.

Задача 16. Вывести список сотрудников данной кафедры, чья зарплата менее заданной величины. Шифр кафедры и граничную величину зарплаты задать параметрически. Использовать оператор AND.

SELECT *

FROM Сотрудник

WHERE шифр IN (SELECT шифр FROM Кафедра WHERE шифр = :параметр_кафедры)

AND зарплата < :параметр_зарплата;

Задание. Вывести из таблицы «Экзамен» все данные о результатах сдачи экзаменов в диапазоне заданных дат. Запрос оформить как параметрический с BETWEEN... AND.

SELECT *

FROM Экзамен

WHERE дата BETWEEN :начальная_дата AND :конечная_дата;

Задание. Вывести данные о сотрудниках (таблица «Сотрудник») по первым буквам названия должности. Запрос оформить как параметрический запрос.

SELECT *

FROM Сотрудник

WHERE LEFT(должность, 1) = :первая_буква_должности;

Задание. Вывести из таблицы «Сотрудник» данные о сотрудниках, работающих на кафедрах факультета «Естественные науки», т. е. с «шифрами» из списка {оф, вм}. Запрос оформить как выборку с IN.

SELECT *

FROM Сотрудник

WHERE шифр IN ('оф', 'вм');

Задание. Вывести из таблицы «Студент» данные о студентах, упорядоченные по возрастанию номеров специальностей («номер») и в алфавитном порядке фамилий («фамилия»). Запрос оформить как выборку с ORDER BY.

SELECT *

FROM Студент

ORDER BY номер, фамилия;

Задание. Вывести из таблицы «Студент» под заголовком «номера специальностей» номера специальностей, сгруппировав строки по этому атрибуту. Запрос оформить как выборку с GROUP BY.

SELECT номер AS "номера специальностей"

FROM Студент

GROUP BY номер;

Задание. Вывести из таблицы «Сотрудник» штатное расписание кафедр, т. е. «шифр», «должность», «зарплату» и «количество ставок» (Count (зарплата)), сгруппировав строки по этим полям.

SELECT

шифр,

должность,

зарплата,

COUNT(зарплата) AS "количество ставок"

FROM Сотрудник

GROUP BY шифр, должность, зарплата;

Задание. Вывести из таблицы «Экзамен» номера студентов («рег_номер»), количество сданных экзаменов (Count (оценка)) и средний балл (AVG (оценка)) для каждого студента как поля с соответствующими заголовками.

SELECT

Экзамен.рег_номер AS "Номер студента",

COUNT(Экзамен.оценка) AS "Количество экзаменов",

AVG(Экзамен.оценка) AS "Средний балл"

FROM

Экзамен

GROUP BY

Экзамен.рег_номер;

Задание. Вывести из таблицы «Сотрудник» штатное расписание кафедр, т. е. «шифр», «должность», «зарплату» и «количество ставок» (Count (зарплата)), сгруппировав строки по этим полям.

SELECT

шифр AS "Шифр кафедры",

должность AS "Должность",

зарплата AS "Зарплата",

COUNT(*) AS "Количество ставок"

FROM

Сотрудник

GROUP BY

шифр, должность, зарплата;