Тестовые задания в ОТР по MSSQL

Тесты по MSSQL

  1. Перечислите основные конструкции языка SQL, которые могут использоваться в инструкции SELECT.

Ответ:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

  1. Напишите запрос, который выводит имена всех компаний, сделавших больше 10 заказов.

mssql2

Ответ:

SELECT t_Customers.CompanyName

FROM Customers t_Customers

WHERE (SELECT COUNT(OrderID)

          FROM Orders t_Orders

          WHERE CustomerID = t_Customers.CustomerID) > 10

 

  1. Что такое представление (View)? Для чего оно предназначено? Чем представление отличается от производной таблицы? Что предпочтительнее использовать с точки зрения производительности — представление или производную таблицу?

Ответ:

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

                  Оно может использоваться для упрощения доступа к данным, то есть содержать в своём теле часто используемый SQL запрос.

                  Производная таблица отличается от представления тем, что её SQL запрос полностью помещается в месте её использования и при необходимости повторного использования в другом месте полностью копируется.

  1. Что такое ограничение (constraint )? Перечислите все известные типы ограничений.

Ответ:

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

                  Типы ограничений:

                  PRIMARY KEY

                  FOREIGN KEY

                  CHECK

                  UNIQUE

 

  1. Что такое триггер? Перечислите все известные типы триггеров. Для каких объектов базы данных можно определить триггеры и какие?

Ответ:

                  Триггер – это хранимая процедура, которая выполняется после наступления определённого события в базе данных. Триггеры бывают на добавление данных, на изменение данных и на удаление данных. Также бывают триггеры выполняющиеся после наступления события (AFTER) и триггеры, выполняющиеся вместо события (INSTEAD OF)

  1. Чем отличается таблица #Table от таблицы ##Table?

Ответ:

                  #Table – временная таблица, доступная только текущему пользователю и только в текущей сессии.

                  ##Table – временная таблица, доступная всем пользователям.

  1. Как организовать связь многие-ко-многим?

Ответ:

                  Связь многие ко многим организовывается через промежуточную таблицу. Промежуточная таблица содержит первичный ключ из первой таблицы и из второй.

  1. Что такое нормальная форма? Перечислите все известные нормальные формы. Какие нормальные формы чаще всего применяются на практике?

Ответ:

                  Нормальная форма – свойство базы данных, которое показывает избыточность её структуры.

                  Бывает:

                  Первая нормальная форма

                  Вторая нормальная форма

                  Третья нормальная форма

                  Нормальная форма Бойса-Кодда    

                  Четвёртая нормальная форма

                  Пятая нормальная форма

 

  1. Что такое транзакция? Какие режимы транзакций существуют в SQL Server 2000?

Ответ:

                   Транзакция – последовательность действий, после выполнения которой либо будут сохранены все выполненные ей изменения, либо они все будут отменены.

                  В SQL Server есть режим явных транзакций (когда пользователь сам вызывает BEGIN TRANSACTION) и режим автоматических транзакций.

  1. Какой параллелизм используется в SQL Server 2000 по умолчанию — оптимистический или пессимистический? Чем они отличаются?

Ответ:

                  Оптимистический параллелизм не накладывает блокировок при считывании данных. Когда пользователь обновит данные, то если ранее считываемые данные были изменены генерируется ошибка.

                  Пессимистический параллелизм при каждой операции считывания накладывает блокировку на считываемые данные. Другие пользователи не могут изменять данные, пока пользователь, наложивший блокировку её не снимет.

  1. Какие существуют уровни изоляции транзакций? Чем они отличаются друг от друга?

Ответ:

                  В SQL Server существуют следующие уровни изоляций транзакций:

                  READ UNCOMMITED – инструкции могут считывать строки, которые были изменены другими транзакциями, но ещё не были закоммичены.

                  READ COMMITED – команды не могут считывать данные, которые были изменены другими транзакциями, но ещё не были закоммичены.

                  REPEATABLE READ – инструкции не могут считывать данные, которые были изменены другими транзакциями, но ещё не были закоммичены. Другие транзакции не могут изменять данные, считываемые текущей транзакцией.

                  SERIALIZABLE – самый строгий уровень блокировки. Инструкции не могут считывать данные, которые были изменены другими транзакциями, но ещё не были зафиксированы. Другие транзакции не могут изменять данные, считываемые текущей транзакцией. Другие транзакции не могут вставлять строки, которые входят в диапазон ключей, считываемых текущей транзакцией (то есть в течении транзакции одна и та же инструкция будет возвращать один и тот же набор строк)

 

  1. Какая блокировка налагается на ресурсы при выполнении команды SELECT? Команды UPDATE? Команды INSERT?

 

  1. Что такое мертвая блокировка (deadlock)? Какие причины могут привести к возникновению мертвой блокировки? Как SQL Server решает проблемы мертвых блокировок и по какому принципу?

Ответ:

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

          SQL Server в случае обнаружения мёртвой блокировки откатывает ту транзакцию, которая захватила меньше ресурсов.

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

Ответ::

                  Хранимая процедура – последовательность команд SQL с определённым именем. С точки зрения производительности лучше использовать хранимые процедуры, а не пакет команд, потому что пакет команд будет компилироваться каждый раз при выполнении. А хранимая процедура компилируется один раз при первом запуске. Хранимая процедура перекомпилируется сама при изменении объектов базы данных, к которым она обращается. Нет смысла перекомпилировать её вручную. Чтобы перекомпилировать хранимые процедуры, которые осуществляют доступ к определённой таблице нужно вызвать системнух хранимую процедуру sp_recompile.

  1. Что такое динамический SQL? Какие проблемы с правами доступа к объектам могут возникнуть при использовании динамического SQL?

Ответ:

                  Динамический SQL – это инструкции SQL, которые генерируется в строковых переменных во время выполнения.

  1. Для чего предназначена функция EXEC() и хранимая процедура sp_executesql? Какая между ними разница?

Ответ:

                       Они предназначены для выполнения динамически созданного пакета sql команд. Sp_executesql позволяет подставить параметры в пакет команд, а в exec нужно каждый раз создавать новую строка SQL команд, подставив параметры преобразованные в строку.

  1. Для чего нужны индексы? Для каких объектов базы данных SQL Server 2000 можно создать индекс? Какие бывают индексы? Что означает опция fill factor? Что означает опция pad_index? Что означает и как работает опция IGNORE_DUP_KEY? Что означает опция SORT_IN_TEMPDB? Какие существуют способы перестройки индексов? Для чего нужна опция DROP_EXISTING?

Ответ:

                  Индексы нужны для ускорения поиска. Индексы бывают кластерные и некластерные. Уникальные и неуникальные. При наличии кластерного индекса строки таблицы физически располагаются упорядоченными в соответствии с этим индексом.

                  FillFactor указывает процент заполнения страницы, хранящей значения индекса.

                  Pad_index указывает количество пустого пространства на внутренних узлах дерева индекса.

                  Если при создании индекса включена опция INGORE_DUP_KEY, то при вставке данных, которые добавляют повторяющееся значение для поля с уникальным индексом, строки, которые создают такие поля будут проигнорированы. Если эта опция не включена, то при добавлении данных, добавляющих повторяющееся значение в поле с уникальным индексом будет возникать ошибка и вся вставка будет отменена.

  1. Для чего используется статистика? Создается ли статистика по индексированному столбцу? По неиндексированному столбцу? В чем разница между выполнением команды UPDATE STATISTICS и автоматическим обновлением статистики?

Ответ:

                  Статистика используется оптимизатором выполнения запросов. Статистика создаётся и по индексированному столбцу и по неиндексированому. UPDATE STATISTICS позволяет обновить статистику вручную, но обычно это не требуется, потому что автоматическое обновление статистики как правило само справляется со своими обязанностями.

  1. Что такое цепочка владения? Ситуация: Пользователь TOwner владеет таблицей Тable. Он выполнил команду GRANT SELECT ON [Table] TO VOwner.

Пользователь VOwner создал на основе таблицы Table представление View и выполнил команду GRANT SELECT ON [View] TO CommonUser.

Сможет ли пользователь  CommonUser выполнить команду SELECT * FROM View? Почему?

Ответ:

                  Пользователь CommonUser не сможет выполнить эту команду, потому что права на выборку из SELECT ему дал пользователь VOwner, а он не является владельцем исходной таблицы Table. Поэтому будут проверяться права доступа на исходную таблицу Table.

  1. Что такое курсор? Какие бывают курсоры T-SQL? Когда используются курсоры?

Ответ:

            Курсоры позволяют производить низкоуровневые операции с отдельной строкой выборки. Курсоры бывают последовательного просмотра, для пролистывания и вперёд и назад, для обновления. Курсоры используются, когда невозможно выполнить какую-нибудь операцию стандартными командами SELECT или UPDATE.

 

— Задание № 1

CREATE TABLE #T1 (c1 INT IDENTITY,c2 INT)
CREATE TABLE #T2 (c1 INT IDENTITY,c2 CHAR(5))

 

INSERT INTO #T1 VALUES (1)
INSERT INTO #T1 VALUES (2)
INSERT INTO #T1 VALUES (2)
INSERT INTO #T1 VALUES (3)
INSERT INTO #T1 VALUES (4)
INSERT INTO #T1 VALUES (4)
INSERT INTO #T2 VALUES (‘N1’)
INSERT INTO #T2 VALUES (‘N2’)

 

CREATE PROCEDURE #P1
@name CHAR(5)
AS
SELECT #T1.c2,#T2.c2 AS [name]
FROM #T1 LEFT OUTER JOIN #T2
ON #T1.c2=#T2.c1
WHERE #T2.c2=@name

 

CREATE PROCEDURE #P2
@name CHAR(5)
AS
SELECT #T1.c2,#T2.c2 AS [name]
FROM #T1 LEFT OUTER JOIN #T2
ON #T1.c2= #T2.c1 AND  #T2.c2=@name

 

— Вопросы

  1. Чем с точки зрения возвращаемого результата различаются
    две хранимые процедуры?

Ответ:

Во #P2 в конечном результате будут все строки, которые в результате #P1 плюс строки, из таблицы #T1, для которых не найдено соответствия в таблице #T2.
2. Какой результат вернет запрос:
SELECT * FROM #T1

Ответ:

С1     С2

1      1

2      2

3      2

4      3

5      4

6      4
3. Какой результат вернет запрос:
SELECT * FROM #T2

Ответ:

C1     C2

1      N1

2      N2
4. Какой результат вернет вызов хранимой процедуры:
EXEC #P1 ‘N1’

Ответ:

C2     name

1      N1
5. Какой результат вернет вызов хранимой процедуры:
EXEC #P2 ‘N1’

Ответ:

C2     name

1      N1

2      NULL

2      NULL

3      NULL

4      NULL

4      NULL
6. Какой результат вернет вызов хранимой процедуры:
EXEC #P1 ‘N3’

Ответ:

Вернётся пустая таблица.
7. Какой результат вернет вызов хранимой процедуры:
EXEC #P2 ‘N3’

Ответ:

С2     name

1      NULL

2      NULL

2      NULL

3      NULL

4      NULL

4      NULL

 

— Задание № 2

CREATE TABLE #Temp (val VARCHAR(10),grp INT,[id] INT IDENTITY NOT NULL)

INSERT INTO #Temp VALUES (‘#a’,1)
INSERT INTO #Temp VALUES (‘#b’,1)
INSERT INTO #Temp VALUES (‘#c’,5)
INSERT INTO #Temp VALUES (‘#c’,5)
INSERT INTO #Temp VALUES (‘#d’,5)
Необходимо написать запрос, который нумерует значения val внутри группы grp. Вывести: <столбец_нумерации>,val,grp,[id]

Ответ:

SELECT (select count(*)

from #Temp t2 where t1.grp=t2.grp and t2.val <= t1.val)

— (select count(*) from #Temp t4 where t4.grp = t1.grp and t4.val = t1.val and t4.id > t1.id) ,

t1.val, t1.grp, t1.id

FROM #Temp t1

order by t1.grp, t1.id

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *