Ключевые слова. В этой и предыдущей лекциях мы обсудили наиболее важные аспекты языка SQL, связанные с определением схемы базы данных – типы данных SQL

Тесты

Заключение

В этой и предыдущей лекциях мы обсудили наиболее важные аспекты языка SQL, связанные с определением схемы базы данных – типы данных SQL, средства определения доменов, базовых таблиц и ограничений целостности. Кроме того, были рассмотрены средства SQL, позволяющие динамически изменять и удалять определения этих объектов. Чтобы не перегружать материал, мы опустили некоторые не слишком существенные детали.

Язык SQL устроен таким образом, что практически невозможно изложить какую-либо его часть независимо от других частей. И хотя эти две по смыслу должна быть первыми среди набора лекций, посвященных SQL (было бы странно обсуждать операторы выборки строк из таблиц, вставки, изменения и удаления строк до обсуждения средств создания таблиц и ограничений целостности), нам пришлось забежать вперед и воспользоваться материалом следующих лекций для объяснения средств определения ограничений целостности. Автор надеется, что это не создало слишком больших неудобств для читателей. Мы старались компенсировать отсутствие формальных определений наличием простых примеров.

1 (1) В каких из перечисленных ниже случаях значением столбца по умолчанию является NULL?

(а) +

в разделе DEFAULT определения столбца указано NULL

(б) +

в определении столбца отсутствует раздел DEFAULT, но столбец определяется на домене, в определении которого присутствует раздел DEFAULT NULL

(в) -

в определении столбца отсутствует раздел DEFAULT, и столбец определяется на базовом типе данных или на домене, в определении которого отсутствует раздел DEFAULT

1 (2) Пусть при определении базовой таблицы указано ограничение UNIQUE (a, b, c), где a, b и c – составляют полный список имен столбцов определяемой таблицы. Будем считать, что все столбцы целого типа и могут содержать неопределенное значение. Какие из показанных ниже пар строк смогут находиться в теле таблицы?

(а) +

<NULL, NULL, 5>, <5, NULL, NULL>

(б) +

<NULL, NULL, NULL>, <5, NULL, NULL>

(в) -

<NULL, NULL, 5>, <NULL, NULL, 5>

1 (3) Пусть в базовой таблице S определен внешний ключ (a, b, c), где a, b и c – имена столбцов определяемой таблицы. Будем считать, что все столбцы целого типа и могут содержать неопределенное значение. Предположим, что внешний ключ ссылается на первичный ключ таблицы T, состоящий из столбцов с такими же именами. При какой спецификации способа соответствия значение внешнего ключа <NULL, 1, 10> соответствует значению первичного ключа <25, 1, 10>?

(а) -

SIMPLE

(б) -

FULL

(в) +

PARTIAL

2 (1) Пусть в базовой таблице S определен внешний ключ (a, b, c), где a, b и c – имена столбцов определяемой таблицы. Пусть для этого внешнего ключа специфицирован способ соответствия SIMPLE. Будем считать, что все столбцы целого типа и могут содержать неопределенное значение. Предположим, что внешний ключ ссылается на первичный ключ таблицы T, состоящий из столбцов с такими же именами. Пусть в таблице S присутствуют строки s1, s2 и s2 со значениями внешнего ключа <NULL, NULL, 5>, <25, 1, NULL> и <25, 1, 5> соответственно. Что произойдет при удалении строки из таблицы T со значением первичного ключа <25, 1, 5>, если при определении внешнего ключа (a, b, c) таблицы S указано ссылочное действие ON DELETE SET NULL?

(а) -

операция удаления строки из таблицы T не выполнится

(б) -

строка из таблицы T будет удалена, а значением внешнего ключа в строках s1, s2 и s2 таблицы S станет <NULL, NULL, NULL>

(в) +

строка из таблицы T будет удалена, а значениями внешнего ключа в строках s1, s2 и s2 таблицы S станут <NULL, NULL, 5>, <25, 1, NULL> и <NULL, NULL, NULL> соответственно

2 (2) Пусть в базовой таблице S определен внешний ключ (a, b, c), где a, b и c – имена столбцов определяемой таблицы. Пусть для этого внешнего ключа специфицирован способ соответствия PARTIAL. Будем считать, что все столбцы целого типа, и для них специфицированы значения по умолчанию 4, 44 и 444 соответственно. Предположим, что внешний ключ ссылается на первичный ключ таблицы T, состоящий из столбцов с такими же именами. Пусть в таблице S присутствуют строки s1, s2 и s2 со значениями внешнего ключа <NULL, NULL, 5>, <25, 1, NULL> и <25, 1, 5> соответственно. Что произойдет при удалении строки t из таблицы T со значением первичного ключа <25, 1, 5>, если при определении внешнего ключа (a, b, c) таблицы S указано ссылочное действие ON DELETE SET DEFAULT и строки s1, s2 и s2 соответствуют исключительно строке t?

(а) -

операция удаления строки из таблицы T не выполнится

(б) -

строка из таблицы T будет удалена, а значением внешнего ключа в строках s1, s2 и s2 таблицы S станет <4, 44, 444>

(в) +

строка из таблицы T будет удалена, а значениями внешнего ключа в строках s1, s2 и s2 таблицы S станут <NULL, NULL, 444>, <4, 44, NULL> и <4, 44, 444> соответственно

2 (3) Пусть в базовой таблице S определен внешний ключ (a, b, c), где a, b и c – имена столбцов определяемой таблицы. Пусть для этого внешнего ключа специфицирован способ соответствия PARTIAL. Будем считать, что все столбцы целого типа и могут содержать неопределенное значение. Предположим, что внешний ключ ссылается на первичный ключ таблицы T, состоящий из столбцов с такими же именами. Пусть в таблице S присутствуют строки s1, s2 и s2 со значениями внешнего ключа <NULL, NULL, 5>, <25, 1, NULL> и <25, 1, 5> соответственно. Пусть в таблице T присутствуют строки t1, t2 со значениями первичного ключа <25, 1, 5> и <25, 2, 5> соответственно. Что произойдет при удалении из таблицы T строки t1, если при определении внешнего ключа (a, b, c) таблицы S указано ссылочное действие ON DELETE SET NULL?

(а) -

операция удаления строки из таблицы T не выполнится

(б) -

строка из таблицы T будет удалена, а значением внешнего ключа в строках s1, s2 и s2 таблицы S станет <NULL, NULL, NULL>

(в) +

строка из таблицы T будет удалена, а значениями внешнего ключа в строках s1, s2 и s2 таблицы S станут <NULL, NULL, 5>, <NULL, NULL, NULL> и <NULL, NULL, NULL> соответственно

3 (1) Предположим, что некоторые служащие могут учиться в аспирантуре своего предприятия. Каждый аспирант имеет единственного научного руководителя, являющегося служащим того же предприятия. Требуется, чтобы у одного научного руководителя было не более пяти аспирантов. Выполнение каких из приведенных ниже операторов изменит определение таблицы EMP требуемым образом?

(а) -

ALTER TABLE EMP
ADD EMP_ADVISER EMP_NO DEFAULT NULL REFERENCES EMP ON DELETE SET NULL
CHECK (VALUE < 6);

(б) -

ALTER TABLE EMP
ADD EMP_ADVISER EMP_NO NO NULL REFERENCES EMP ON DELETE CASCADE;

ALTER TABLE EMP ADD CONSTRAINT STUD_NUMBER
CHECK ((SELECT COUNT (*) FROM EMP E
WHERE E.EMP_ADVISER = EMP_ADVISER) < 6);

(в) +

ALTER TABLE EMP
ADD EMP_ADVISER EMP_NO DEFAULT NULL REFERENCES EMP ON DELETE SET NULL;

CHECK (IF (VALUE IS NOT NULL) THEN
(SELECT COUNT (*) FROM EMP E
WHERE E.EMP_ADVISER = VALUE) < 6);

3 (2) Предположим, что проекты финансируются отделами. Каждый отдел финансирует не более одного проекта, один проект может финансироваться несколькими отделами, и общая сумма финансирования проекта не должна составлять менее 1000000.00 руб. Выполнение каких из приведенных ниже операторов изменит определения таблиц DEPT и PRO требуемым образом?

(а) +

ALTER TABLE DEPT
ADD DEPT_PRO_INVEST NUMERIC (10, 2) DEFAULT NULL;

ALTER TABLE DEPT
ADD DEPT_PRO PRO_NO DEFAULT NULL;
REFERENCES PRO ON DELETE SET NULL;

ALTER TABLE DEPT ADD CONSTRAINT PRO_INVEST_VALUE
CHECK (IF (DEPT_PRO IS NOT NULL) THEN
(SELECT SUM (D.DEPT_PRO_INVEST) FROM DEPT D
WHERE DEPT_PRO = D.DEPT_PRO) >= 1000000.00);

(б) -

ALTER TABLE DEPT
ADD DEPT_PRO_INVEST NUMERIC (10, 2)DEFAULT NULL;

ALTER TABLE PRO
ADD PRO_DEPT DEPT_NO NO NULL;

REFERENCES DEPT ON DELETE CASCADES;
CHECK (SELECT SUM (DEPT_PRO_INVEST) FROM DEPT
WHERE DEPT_NO = VALUE) >= 1000000.00);

(в) -

ALTER TABLE PRO
ADD PRO_DEPT_INVEST NUMERIC (10, 2)DEFAULT NULL;

ALTER TABLE PRO
ADD PRO_DEPT DEPT_NO NO NULL;

REFERENCES PRO ON DELETE CASCADES;
CHECK (SELECT SUM (PRO_DEPT_INVEST) FROM PRO
WHERE PRO_DEPT = VALUE) >= 1000000.00);

3 (3) Предположим, что служащие, участвующие в проектах, могут получать дополнительную премию, равную средней основной заработной плате участников соответствующего проекта. Выполнение каких из приведенных ниже операторов изменит определение таблицы EMP требуемым образом?

(а) -

ALTER TABLE EMP
ADD EMP_PRO_BONUS SALARY DEFAULT NULL;

ALTER TABLE EMP ADD CONSTRAINT PRO_BONUS
CHECK (IF (EMP_PRO_BONUS IS NOT NULL) THEN
PRO_NO IS NOT NULL)

ALTER TABLE DEPT ADD CONSTRAINT MORE_TOTAL_INCOME
CHECK (DEPT_TOTAL_SAL >=
(SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0) +
COALESCE(EMP_PRO_BONUS,0))
FROM EMP
WHERE EMP.DEPT_NO = DEPT_NO));

(б) -

ALTER TABLE EMP
ADD EMP_PRO_BONUS SALARY DEFAULT NULL;

ALTER TABLE EMP
DROP CONSTRAINT TOTAL_INCOME;

ALTER TABLE DEPT ADD CONSTRAINT TOTAL_INCOME
CHECK (DEPT_TOTAL_SAL >=
(SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0) +
COALESCE(EMP_PRO_BONUS,0))
FROM EMP
WHERE EMP.DEPT_NO = DEPT_NO));

(в) +

ALTER TABLE EMP
ADD EMP_PRO_BONUS SALARY DEFAULT NULL;

ALTER TABLE EMP ADD CONSTRAINT PRO_BONUS_VALUE
CHECK (IF (EMP_PRO_BONUS IS NOT NULL) THEN
EMP_PRO_BONUS = (SELECT AVG (E.EMP_SAL)
FROM EMP E
WHERE PRO_NO = E.PRO_NO));

ALTER TABLE EMP ADD CONSTRAINT PRO_BONUS
CHECK (IF (EMP_PRO_BONUS IS NOT NULL) THEN
PRO_NO IS NOT NULL)

ALTER TABLE DEPT ADD CONSTRAINT MORE_TOTAL_INCOME
CHECK (DEPT_TOTAL_SAL >=
(SELECT SUM(EMP_SAL + COALESCE(EMP_BONUS,0) +
COALESCE(EMP_PRO_BONUS,0))
FROM EMP
WHERE EMP.DEPT_NO = DEPT_NO));

4 (1) Какой из режимов проверки разумно установить для ограничения таблицы EMP TOTAL_INCOME?

(а) -

INITIALLY IMMEDIATE NOT DEFERRABLE

(б) -

INITIALLY IMMEDIATE DEFERRABLE

(в) +

INITIALLY DEFERRED

4 (2) Какой из режимов проверки разумно установить для ограничения таблицы PRO_EMP_NO?

(а) -

INITIALLY IMMEDIATE NOT DEFERRABLE

(б) -

INITIALLY IMMEDIATE DEFERRABLE

(в) +

INITIALLY DEFERRED

4 (3) Какой из режимов проверки разумно установить для ограничения таблицы MIN_EMP_BDATE?

(а) +

INITIALLY IMMEDIATE NOT DEFERRABLE

(б) -

INITIALLY IMMEDIATE DEFERRABLE

(в) -

INITIALLY DEFERRED

Лекция 13. Язык баз данных SQL: общая характеристика оператора SELECT и организация списка ссылок на таблицы в разделе FROM

В этой и следующих трех лекциях обсуждается важнейший оператор языка SQL – оператор SELECT, предназначенный для выборки данных из SQL-ориентированной базы данных. Этот оператор имеет довольно сложную и развитую структуру, но, по нашему мнению, его необходимо хорошо понимать любому специалисту, так или иначе связанному с использованием баз данных; поэтому в этом курсе отведена такая большая часть для его обсуждения. Первая лекция носит подготовительный характер. В ней мы рассматриваем виды скалярных выражений, используемые, прежде всего, в конструкциях оператор SELECT, обсуждаем базовую семантику выполнения этого оператора и анализируем принципы и разновидности указания таблиц, из которых производится выборка данных.

Скалярное выражение, первичное выражение, агрегатная функция, скалярный подзапрос, численное выражение, вызов функций с численным значением, выражения символьных и битовых строк, вызов функций, возвращающих строчные значения, выражения даты-времени, вызовы функций, возвращающих значение дата-время, выражения со значениями типа временного интервала, булевские выражения, выражения с переключателем, выражение с поисковым переключателем, выражение с простым переключателем, выражение NULLIF, выражение COALESCE, оператор SELECT, семантика оператора выборки, раздел FROM, раздел WHERE, раздел GROUP BY, раздел HAVING, раздел SELECT, раздел ORDER BY, порождаемая таблица с горизонтальной связью, соединенная таблица, табличное выражение, спецификация запроса, выражение запросов, “теоретико-множественные” операции в SQL, раздел WITH выражения запросов, конструктор значения-строки, конструктор значения-таблицы, ссылки на базовые и представляемые таблицы, ссылки на порождаемые таблицы, представляемые таблицы, или представления (VIEW), оператор CREATE VIEW, оператор DROP VIEW.

Дата добавления: 2014-01-03