Обязательные ключевые слова команды - SELECT и FROM.

Выборка из одной таблицы

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

Получить полную информацию обо всех предприятиях:

SELECT * FROM k_firm

Выбор отдельных полей таблицы.

Получить названия и адреса всех предприятий:

SELECT firm_name, firm_addr FROM k_firm

Результат:

firm_name firm_addr

----------------------

Альфа Москва

Бета Казань

Гамма Париж

Дельта Лондон

Омега Токио

Поля выборки можно переименовывать. Если новое название состоит из нескольких слов, помещайте его в двойные кавычки.

SELECT firm_name AS "Название предприятия",

firm_addr AS "Адрес предприятия"

FROM k_firm

Результат:

Название предприятия Адрес предприятия

-----------------------------------------------------------Альфа Москва

Бета Казань

Гамма Париж

Дельта Лондон

Омега Токио

В списке полей выборки можно использовать выражения. В этом случае часто приходится преобразовывать данные из одного типа в другой с помощью функции CONVERT. Строковые константы следует помещать в одинарные кавычки. Операция + означает сцепление строк.

Распечатать информацию о счетах:

SELECT 'Счет № '+CONVERT(CHAR(6),bill_num)+

' от '+ CONVERT(CHAR(12),bill_date)+

' на сумму '+CONVERT(CHAR(9),bill_sum)

FROM k_bill

Результат:

------------------------------------------------

Счет № 1 от ноя 12 2011 на сумму 1000

Счет № 2 от дек 12 2011 на сумму 2000

Счет № 3 от янв 12 2012 на сумму 2000

Счет № 4 от дек 12 2011 на сумму 6000

Счет № 5 от янв 12 2012 на сумму 2000

Счет № 6 от янв 12 2012 на сумму 2500

Счет № 7 от дек 12 2011 на сумму 1500

Счет № 8 от дек 12 2011 на сумму 1200

Счет № 9 от янв 12 2012 на сумму 10000

Для того чтобы исключить дубликаты строк, нужно использовать ключевое слово DISTINCT.

Напечатать список городов, в которых находятся предприятия-клиенты:

SELECT DISTINCT firm_addr FROM k_firm

Результат:

firm_addr

----------

Казань

Лондон

Москва

Париж

Токио

Использование условий отбора

Для выбора отдельных строк по некоторому критерию используется ключевое слово WHERE

Получить список предприятий, расположенных в Москве:

SELECT firm_name as "Название предприятия"

FROM k_firm

WHERE firm_addr='Москва'

Результат:

Название предприятия

--------------------

Альфа

Для сравнения поля со значением NULL нельзя использовать операции = и !=, вместо них нужно использовать выражения IS NULL и IS NOT NULL.

Получить список постоянно работающих сотрудников, т.е., таких, у которых staff_termdate равно NULL:

SELECT staff_name FROM k_staff

WHERE staff_termdate IS NULL

Результат:

staff_name

-----------

Иванов

Петров

Сидоров

Условия могут быть сложные, представляющие собой комбинацию нескольких операций сравнения. В них можно использовать логические связки AND и OR, а также отрицание NOT.

Получить список предприятий, расположенных в Москве или Казани:

SELECT firm_name as "Название предприятия"

FROM k_firm

WHERE firm_addr='Москва' OR firm_addr='Казань'

Результат:

Название предприятия

---------------------

Альфа

Бета

Если условие заключается в сравнении поля со списком значений, удобно использовать ключевое слово IN.

Получить список предприятий, расположенных в Москве или Казани:

SELECT firm_name as "Название предприятия"

FROM k_firm

WHERE firm_addr IN ('Москва','Казань')

Результат:

Название предприятия

---------------------

Альфа

Бета

Если условие заключается в сравнении поля с диапазоном значений, удобно использовать ключевое слово BETWEEN.

Получить список договоров, заключенных в ноябре 2011 г.:

SELECT * FROM k_contract

WHERE contract_date BETWEEN '2011-11-01' AND '2011-11-30'

Заметим, что полезно предварительно задать желаемый формат даты год-месяц-день:

SET DATEFORMAT YMD

Результат:

contract_num contract_date contract_type firm_num staff_num

----------------------------------------------------------

1 2011-11-01 00:00:00.000 A 1 1

4 2011-11-15 00:00:00.000 A 2 2

7 2011-11-12 00:00:00.000 A 4 1

Для полей строкового типа можно применять сравнение с подстрокой.

Получить список сотрудников, фамилия которых начинается на И:

SELECT staff_name FROM k_staff

WHERE staff_name LIKE 'И%'

(рассмотрите более подробно использование LIKE в MSDN)

Результат:

staff_name

----------

Иванов

Использование агрегирующих функций

Для подсчета итоговых значений используются функции SUM, COUNT, MAX, MIN, AVG. Если не используется группировка строк, запрос с использованием итоговой функции вернет ровно одну строку.

Подсчитать, на какую сумму выставлены счета в декабре.

SELECT SUM(bill_sum) FROM k_bill

WHERE bill_date

BETWEEN '2011-12-01' AND '2011-12-31'

Результат:

----------------------------------------

10700

Функция COUNT позволяет подсчитать, сколько строк в таблице имеется вообще.

Подсчитать количество сотрудников.

SELECT COUNT(*) FROM k_staff

Результат:

-----------

5

А также эта функция позволяет подсчитать, сколько строк с не-NULL-значениями в определенном поле.

Подсчитать количество временно работающих сотрудников (у них заполнен срок окончания трудового довора - поле staff_termdate ).

SELECT COUNT(staff_termdate) FROM k_staff

Результат:

-----------

0

Сортировка

Для сортировки используется ключевое слово ORDER BY и имя поля или его номер в списке полей выборки.

Напечатать список сотрудников, отсортированный по алфавиту:

SELECT staff_name FROM k_staff ORDER BY 1

Результат:

staff_name

-------------

Григорьев

Иванов

Петров

Семенов

Сидоров

Можно сортировать строки даже по такому полю, которое не входит в список полей выборки.

Напечатать список сотрудников, отсортированный по дате поступления на работу:

SELECT staff_name FROM k_staff ORDER BY staff_hiredate

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

Напечатать информацию о 5 последних выписанных счетах в порядке убывания даты счета:

SELECT TOP 5 bill_num, bill_date

FROM k_bill ORDER BY bill_date DESC

Результат:

bill_num bill_date

-------- -----------------------

3 2012-01-12 00:00:00.000

6 2012-01-12 00:00:00.000

5 2012-01-12 00:00:00.000

9 2012-01-12 00:00:00.000

4 2011-12-12 00:00:00.000

Подзапросы

Для более сложных формулировок иногда удобно использовать подзапросы.

Подзапрос всегда указывается в скобках.

Подзапрос может быть несвязанным, т.е. в формулировке подзапроса нет ссылки на главный запрос. В этом случае подзапрос выполняется один раз при выполнении главного запроса. В данном примере используется ключевое слово IN, так как подзапрос может возвращать несколько значений.

Получить список договоров, по которым в декабре выписаны счета:

SELECT contract_num, contract_date FROM k_contract

WHERE contract_num IN

(SELECT contract_num FROM k_bill

WHERE bill_date

BETWEEN '2011-12-01' AND '2011-12-31')

Результат:

contract_num contract_date

-----------------------------------

1 2011-11-01 00:00:00.000

2 2011-10-01 00:00:00.000

4 2011-11-15 00:00:00.000

5 2011-08-01 00:00:00.000

Тот же самый запрос с использованием ключевого слова ANY:

SELECT contract_num, contract_date FROM k_contract c

WHERE contract_num = ANY

(SELECT contract_num FROM k_bill

WHERE bill_date

BETWEEN '2011-12-01' AND '2011-12-31')

Тот же самый запрос можно выполнить и с помощью связанного подзапроса, т.е., подзапроса, в котором есть ссылка на главный запрос. Для ссылки на таблицу главного запроса нужно указать псевдоним. Такой подзапрос будет выполняться заново для каждой строки главного запроса.

Кроме того, в данном примере иллюстрируется использование ключевого слова EXISTS:

SELECT contract_num, contract_date FROM k_contract c

WHERE EXISTS

(SELECT * FROM k_bill b

WHERE bill_date

BETWEEN '2011-12-01' AND '2011-12-31'

AND c.contract_num=b.contract_num)

Пример использования ключевого слова ALL.

Напечатать информацию о товаре (товарах) с наименьшей ценой.

SELECT price_name, price_sum FROM k_price

WHERE price_sum <= ALL

(SELECT price_sum FROM k_price)

Результат:

price_name price_sum

--------------------------

Раздача слонов 100.00

Этот запрос можно сформулировать и по-другому. В этом примере мы можем использовать операцию сравнения =, т.к. подзапрос возвращает ровно одну строку и один столбец.

SELECT price_name, price_sum FROM k_price

WHERE price_sum =

(SELECT MIN(price_sum) FROM k_price)

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

SELECT price_name, MIN(price_sum) FROM k_price

Результат - сообщение об ошибке:

Msg 8120, Level 16, State 1, Line 1

Столбец "k_price.price_name" недопустим в списке выбора, поскольку он не содержится ни в статистической функции, ни в предложении GROUP BY.

(Подробнее по поводу ошибок - см. Приложение 1.)

Группировка

Для подведения итога по группе данных используется комбинация ключевого слова GROUP BY и агрегирующих функций. Причем в списке полей для выборки могут присутствовать только поля группировки и агрегирующие функции (при необходимости можно просто расширить список полей группировки).

Получить список договоров и общую сумму счетов по каждому договору:

SELECT contract_num, SUM(bill_sum) AS contract_sum

FROM k_bill

GROUP BY contract_num

Результат:

contract_num contract_sum

--------------------------

1 5000

2 8000

3 2500

4 1500

5 11200

В том случае, когда нужно выбрать не все группы, а только некоторые из них, используется ключевое слово HAVING:

Получить список договоров, имеющих 2 или более счетов, и общую сумму счетов по каждому договору:

SELECT contract_num, SUM(bill_sum) AS contract_sum

FROM k_bill

GROUP BY contract_num

HAVING COUNT(bill_num)>=2

Результат:

contract_num contract_sum

------------ ----------------------------------------

1 5000

2 8000

5 11200

Выборка из нескольких таблиц

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

Напечатать список договоров с указанием названия предприятия.

SELECT firm_name, contract_num, contract_date

FROM k_firm f, k_contract c

WHERE f.firm_num=c.firm_num

Результат:

firm_name contract_num contract_date

-----------------------------------------------------------

Альфа 1 2011-11-01 00:00:00.000

Альфа 2 2011-10-01 00:00:00.000

Альфа 3 2011-09-01 00:00:00.000

Бета 4 2011-11-15 00:00:00.000

Бета 5 2011-08-01 00:00:00.000

Гамма 6 2011-07-15 00:00:00.000

Дельта 7 2011-11-12 00:00:00.000

То же самое можно получить, если использовать синтаксис JOIN...ON. Это так называемое внутреннее (INNER) соединение. Строки соединяются, если совпадают значения полей в условии ON.

SELECT firm_name, contract_num, contract_date

FROM k_firm f JOIN k_contract c ON f.firm_num=c.firm_num

Кроме внутреннего, бывают еще левое (LEFT), правое (RIGHT) и полное (FULL) соединения.

Рассмотрим, например, левое соединение. В результат попадут строки, в которых совпадают значения полей в условии ON, и те строки из левой таблицы, для которых не нашлось соответствующих строк в правой таблице. Поля из правой таблицы будут заполнены значениями NULL.

Напечатать список договоров с указанием названия предприятия плюс список предприятий, у которых нет договоров:

SELECT firm_name, contract_num, contract_date

FROM k_firm f LEFT JOIN k_contract c ON f.firm_num=c.firm_num

Результат:

firm_name contract_num contract_date

-----------------------------------------------------------Альфа 1 2011-11-01 00:00:00.000

Альфа 2 2011-10-01 00:00:00.000

Альфа 3 2011-09-01 00:00:00.000

Бета 4 2011-11-15 00:00:00.000

Бета 5 2011-08-01 00:00:00.000

Гамма 6 2011-07-15 00:00:00.000

Дельта 7 2011-11-12 00:00:00.000

Омега NULL NULL

А что будет в том случае, если условие связи вообще не указывать? Получится так называемое декартово произведение таблиц, в котором каждая строка первой таблицы будет сцеплена с каждой строкой второй таблицы. Результат получается обычно очень большим и не имеющим смысла.

SELECT firm_name, contract_num, contract_date

FROM k_firm f, k_contract c

Разумеется, можно связывать не только две, а три и более таблицы, использовать в этих запросах подзапросы, группировки и т.п. Например:

Напечатать информацию о платежах с указанием названия предприятия:

SELECT firm_name, payment_date, payment_sum

FROM k_firm f, k_contract c, k_bill b, k_payment p

WHERE f.firm_num=c.firm_num AND

c.contract_num=b.contract_num AND b.bill_num=p.bill_num

Результат:

firm_name payment_date payment_sum

-----------------------------------------------------------Альфа 2011-12-01 00:00:00.000 1000.00

Альфа 2011-12-15 00:00:00.000 1000.00

Альфа 2012-01-13 00:00:00.000 1500.00

Альфа 2012-01-12 00:00:00.000 1000.00

Бета 2012-01-05 00:00:00.000 100.00

Бета 2011-12-25 00:00:00.000 1000.00

Альфа 2012-01-15 00:00:00.000 500.00

Бета 2012-01-12 00:00:00.000 900.00

Объединение запросов

Для объединения результатов двух и более запросов нужно использовать ключевое слово UNION. Объединяемые запросы должны иметь одинаковое количество и тип полей. Параметр ORDER BY, если он нужен, следует указывать только в последнем запросе.

Получить список договоров и общую сумму счетов по каждому договору, а также строку с итоговой суммой:

SELECT 'Договор № '+CONVERT(CHAR(6),contract_num)+

'на сумму ' AS "Номер",

SUM(bill_sum) AS "Сумма" FROM k_bill

GROUP BY contract_num

UNION

SELECT 'ИТОГО: ', SUM(bill_sum) FROM k_bill ORDER BY 1

Результат:

Номер Сумма

------------------------- ------------

Договор № 1 на сумму 5000

Договор № 2 на сумму 8000

Договор № 3 на сумму 2500

Договор № 4 на сумму 1500

Договор № 5 на сумму 11200

ИТОГО: 28200

И еще несколько примеров

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

SELECT pr.price_name, pr.price_sum,

SUM(prot.kolvo*prot.price_sum)

FROM k_price pr, k_protokol prot

WHERE pr.price_num=prot.price_num

GROUP BY pr.price_num, pr.price_name, pr.price_sum

Результат:

price_name price_sum

-------------------------------------------------------

Материализация духов 1000.00 7000.00

Раздача слонов 100.00 3700.00

Слоновий бивень 3000.00 6000.00

Моржовый клык 1500.00 1500.00

Копыто Пегаса 5000.00 10000.00

Полностью оплаченные счета:

SELECT b.bill_num AS "Номер счета",

b.bill_date AS "Дата счета",

b.bill_sum AS "Сумма счета",

SUM(p.payment_sum) AS "Сумма оплаты"

FROM k_bill b, k_payment p

WHERE b.bill_num=p.bill_num AND

b.bill_sum<=

(SELECT SUM(payment_sum) FROM k_payment p2

WHERE b.bill_num=p2.bill_num)

GROUP BY b.bill_num, b.bill_date, b.bill_sum

Результат:

Номер счета Дата счета Сумма счета Сумма оплаты

-----------------------------------------------------------

1 2011-11-12 00:00:00.000 1000 1000.00

3 2012-01-12 00:00:00.000 2000 2000.00

Полностью неоплаченные счета

SELECT b.bill_num AS "Номер счета",

b.bill_date AS "Дата счета",

b.bill_sum AS "Сумма счета",

0 AS "Сумма оплаты"

FROM k_bill b

WHERE b.bill_num NOT IN (SELECT bill_num FROM k_payment)

Результат:

Номер счета Дата счета Сумма счета Сумма оплаты

-----------------------------------------------------------

5 2012-01-12 00:00:00.000 2000 0

6 2012-01-12 00:00:00.000 2500 0

9 2012-01-12 00:00:00.000 10000 0

Частично оплаченные счета - обратите внимание, что в этом примере в параметре FROM вместо второй таблицы используется вложенный SELECT

SELECT b.bill_num AS "Номер счета",

b.bill_date AS "Дата счета",

b.bill_sum AS "Сумма счета",

p.pay_sum AS "Сумма оплаты"

FROM k_bill b,

(SELECT bill_num, SUM(payment_sum) as pay_sum

FROM k_payment

GROUP BY bill_num) p

WHERE b.bill_sum >p.pay_sum AND b.bill_num=p.bill_num

Результат:

Номер счета Дата счета Сумма счета Сумма оплаты

-----------------------------------------------------------

2 2011-12-12 00:00:00.000 2000 1000.00

4 2011-12-12 00:00:00.000 6000 1000.00

7 2011-12-12 00:00:00.000 1500 1000.00

8 2011-12-12 00:00:00.000 1200 1000.00

Вопрос

Какие функции есть в языке SQL server? Изучите данный вопрос самостоятельно по MSDN или другим источникам.

Задание для индивидуальной работы 5

Напишите несколько (не менее 5) интересных запросов к вашей базе данных. Используйте вложенные подзапросы, группировки, итоговые значения, выборки из нескольких таблиц. Если ваш запрос требует ввода параметра, замените его пока на константу, запросы с параметрами можно будет в дальнейшем реализовать с помощью хранимых процедур.

DDL. Представления

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

Необходимость в использовании представлений возникает, например, в том случае, когда нужно запретить доступ пользователя к отдельным столбцам или строкам таблицы - тогда можно просто написать представление, в котором эти столбцы или строки не будут присутствовать, и предоставить доступ пользователю именно к этому представлению, а не к реальной таблице.

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

Представление, как и запрос, может содержать информацию из разных таблиц.

Представления могут быть обновляемыми (т.е., предоставлять возможность не только чтения, но и изменения данных в исходных таблицах) и необновляемыми. Представление будет обновляемым только в том случае, если его структура такова, что SQL server может точно определить, в какие строки каких таблиц нужно поместить измененные данные. Необновляемыми будут, например, представления, содержащие итоговые данные и группировки.

Для создания представлений используется команда CREATE VIEW.

Краткий формат этой команды:

CREATE VIEW имя_представления AS

Команда_SELECT

Команду создания представления нужно либо выполнять отдельно от других команд, либо сразу после нее поставить команду GO, как в следующем примере.

Например, создадим представление, содержащее список договоров и их кураторов для отдела с номером 1. Будет ли это представление обновляемым?

CREATE VIEW k_contract1

AS

SELECT k_contract.contract_num, k_contract.contract_date,

k_contract.contract_type, k_contract.firm_num,

k_staff.staff_name

FROM k_contract INNER JOIN

k_staff ON k_contract.staff_num = k_staff.staff_num

WHERE dept_num = 1

GO

Для просмотра представления следует выполнить команду

SELECT * FROM k_contract1

Результат выполнения команды:

contract_num contract_date contract_type firm_num staff_name

-----------------------------------------------------------

1 2011-11-01 00:00:00.000 A 1 Иванов

3 2011-09-01 00:00:00.000 C 1 Иванов

6 2011-07-15 00:00:00.000 C 3 Иванов

7 2011-11-12 00:00:00.000 A 4 Иванов

Создадим вспомогательное представление для запросов о полностью оплаченных и частично оплаченных счетах (см. предыдущее занятие). Это представление для каждого счета содержит его номер и сумму оплаты.

CREATE VIEW k_pay_sum

AS

SELECT bill_num, SUM(payment_sum) AS pay_sum

FROM k_payment

GROUP BY bill_num

GO

Для просмотра представления следует выполнить команду

SELECT * FROM k_pay_sum.

Это представление не будет обновляемым.

Результат выполнения команды:

bill_num pay_sum

----------------------

1 1000.00

2 1000.00

3 2000.00

4 1000.00

7 1000.00

8 1000.00

Теперь с помощью данного представления можно переформулировать сам запрос, он станет проще:

Полностью оплаченные счета

SELECT b.bill_num AS "Номер счета",

b.bill_date AS "Дата счета",

b.bill_sum AS "Сумма счета",

p.pay_sum AS "Сумма оплаты"

FROM k_bill b, k_pay_sum p

WHERE b.bill_num=p.bill_num AND

b.bill_sum<=p.pay_sum

Задание для индивидуальной работы 6

Создайте несколько (не менее 3) представлений для вашей базы данных. Будут ли они обновляемыми или нет? Проверьте.

Хранимые процедуры

Хранимые процедуры - это объекты базы данных, которые представляют собой программы, манипулирующие данными и выполняемые на сервере. Эти программы, кроме команд языка SQL, могут использовать немногочисленные управляющие команды.

Структура хранимой процедуры следующая:

CREATE PROC[EDURE] имя_процедуры [параметры]

AS

Код процедуры

Локальные переменные и параметры в процедуре начинаются с символа @.

Глобальные переменные начинаются с символов @@. Есть довольно много системных глобальных переменных с полезной информацией. Некоторые из них мы будем использовать в следующих темах.

Объявление переменных имеет вид

DECLARE имя_переменной тип_переменной [(длина)]

Блок операторов заключается в команды BEGIN … END

Оператор присвоения выгладит довольно странно:

SELECT переменная=значение

Зато с помощью такого синтаксиса при выполнении команды SELECT можно сохранять значения в переменных.

Альтернативный формат оператора присвоения:

SET переменная=значение

Условный оператор выглядит так:

IF условие

Оператор1

[ELSE

Оператор2]

Цикл по счетчику отсутствует, есть только цикл по условию

WHILE условие

Оператор

Для прерывания цикла используется команда BREAK.

Для прерывания итерации цикла используется команда CONTINUE.

Оператор печати имеет вид PRINT выражение

Выход из процедуры: RETURN [код_завершения]

Это первый способ возвращения значения из процедуры - таким образом можно возвращать только целочисленное значение.

Команда

RAISERROR сообщение, уровень_опасности, код_состояния

применяется для вывода сообщений об ошибках и прочих предупреждений в стандартной для SQL server форме.

Выражение CASE применяется для выбора на основании нескольких опций:

CASE выражение

WHEN вариант1 THEN выражение1

WHEN вариант2 THEN выражение2

…

ELSE выражениеN

END

Создадим процедуру, которая в качестве параметра получает фамилию сотрудника и печатает список всех договоров, которые он курирует.

Это второй способ возвращения значений из процедуры - печать результата выполнения команды SELECT.

CREATE PROCEDURE show_contracts @name Varchar(30)

AS

SELECT contract_num, contract_date, contract_type

FROM k_contract c JOIN k_staff s ON c.staff_num=s.staff_num

WHERE s.staff_name=@name

GO

Для запуска этой процедуры нужно выполнить, например, команду

EXEC show_contracts 'Иванов'

В результате получим:

contract_num contract_date contract_type

------------ --------------------------- -------------

1 2011-11-01 00:00:00.000 A

3 2011-09-01 00:00:00.000 C

6 2011-07-15 00:00:00.000 C

7 2011-11-12 00:00:00.000 A

Создадим процедуру «Распродажа», которая находит самый непродаваемый (по количеству) товар и уценивает его на заданный процент (по умолчанию задается 10 процентов).

CREATE PROCEDURE clearance @percent Int = 10

AS

DECLARE @p Int

IF @percent > 0 AND @percent < 100

BEGIN

SELECT @p=price_num FROM k_protokol

GROUP BY price_num

HAVING SUM(kolvo)<=ALL

(SELECT SUM(kolvo) FROM k_protokol

GROUP BY price_num)

UPDATE k_price

SET price_sum=price_sum*(100-@percent)/100

WHERE price_num=@p

END

GO

Содержимое таблицы "Прайс-лист" до выполнения процедуры:

price_num price_name price_sum

------------------------------------------------

1 Материализация духов 1000.00

2 Раздача слонов 100.00

3 Слоновий бивень 3000.00

4 Моржовый клык 1500.00

5 Копыто Пегаса 5000.00

Для запуска этой процедуры нужно выполнить, например, команду

EXEC clearance 10

Содержимое таблицы "Прайс-лист" после выполнения процедуры:

price_num price_name price_sum

------------------------------------------

1 Материализация духов 1000.00

2 Раздача слонов 100.00

3 Слоновий бивень 3000.00

4 Моржовый клык 1350.00

5 Копыто Пегаса 5000.00

Как видим, товар с номером 4 в прайс-листе уценен на 10%.

В том случае, если из хранимой процедуры нужно вернуть значение переменной, нужно объявить эту переменную как выходной (OUTPUT) параметр процедуры в двух местах: в описании процедуры и в вызове процедуры. Это третий способ возвращения значений из процедуры - выходные параметры.

Пусть, например, в предыдущей процедуре мы хотим не только уценить товар, но и возвратить его номер. Описание процедуры будет выглядеть следующим образом:

CREATE PROCEDURE clearance @percent Int, @p Int OUTPUT

AS

IF @percent > 0 AND @percent < 100

BEGIN

SELECT @p=price_num FROM k_protokol

GROUP BY price_num

HAVING SUM(kolvo)<=ALL

(SELECT SUM(kolvo) FROM k_protokol

GROUP BY price_num)

UPDATE k_price

SET price_sum=price_sum*(100-@percent)/100

WHERE price_num=@p

END

А вызов процедуры будет выглядеть следующим образом (все три команды должны выполняться вместе):

DECLARE @num NUMERIC(6)

EXEC clearance 1, @num OUTPUT

PRINT 'Уценили товар с номером '+STR(@num)

И еще один пример. Предположим, у нас есть таблица для хранения списка счетов:

CREATE TABLE bill_list

(name VARCHAR(20), dat DATETIME, summa NUMERIC(9,2))

Мы хотим сформировать список выставленных за месяц счетов с названиями предприятий и с итогами по дням. Рассмотрите этот пример самостоятельно. Месяц и год передаются в процедуру в качестве параметров. Функция DATEDIFF здесь вычисляет разность между двумя датами в днях.

CREATE PROCEDURE calc_bill_list @mon Int, @year Int

AS

DECLARE @day Int, @end Int, @date DateTime

SET @day=1

IF @mon=2

IF @year%4=0

SET @end=29

ELSE

SET @end=28

ELSE IF @mon=4 OR @mon=6 OR @mon=9 OR @mon=11

SET @end=30

ELSE

SET @end=31

DELETE FROM bill_list

WHILE (@day<=@end)

BEGIN

SET @date=CONVERT(CHAR(2),@mon)+'/'+

CONVERT(CHAR(2),@day)+'/'+

CONVERT(CHAR(4),@year)

INSERT INTO bill_list (name, dat, summa)

SELECT firm_name, bill_date, bill_sum

FROM k_firm, k_contract, k_bill

WHERE k_firm.firm_num=k_contract.firm_num

AND

k_contract.contract_num=k_bill.contract_num

AND

DATEDIFF(day, k_bill.bill_date, @date)=0

INSERT INTO bill_list (name, dat, summa)

SELECT ' ИТОГО ЗА:', @date,

ISNULL(SUM(bill_sum),0) FROM k_bill

WHERE

DATEDIFF(day, k_bill.bill_date, @date)=0

SET @day=@day+1

END

Обратите внимание на формат команды INSERT. В таблицу bill_list добавляются строки, являющиеся результатом выполнения команды SELECT.

Для удаления хранимой процедуры используется команда:

DROP PROCEDURE имя_процедуры

Задание для индивидуальной работы 7

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

CCL. Курсоры

CCL (Cursor Control language) - язык управления курсорами, составная часть SQL.

Как вы уже поняли, команды манипулирования данными SELECT, UPDATE, DELETE работают сразу с группами строк. Эти группы, вплоть до отдельных строк, можно выбрать с помощью опции WHERE. Что же делать в том случае, если требуется перебрать строки некоторой таблицы последовательно, одну за другой? Для этого в языке SQL существует такое понятие, как курсор. Курсор (current set of record) - это временный набор строк, которые можно перебирать последовательно, с первой до последней.

Для работы с курсорами существуют следующие команды.

Объявление курсора:

DECLARE имя_курсора CURSOR FOR SELECT текст_запроса

Таким образом, любой курсор создается на основе некоторого оператора SELECT.

Открытие курсора:

OPEN имя_курсора

Только после открытия курсора он становится активным, и из него можно читать строки.

Чтение значений из следующей строки курсора в набор переменных:

FETCH имя_курсора INTO список_переменных

Переменные в списке должны иметь то же количество и тип, что и столбцы курсора.

Глобальная переменная @@FETCH_STATUS принимает ненулевое значение, если строк в курсоре больше нет. Если же набор строк еще не исчерпан, то @@FETCH_STATUS равна нулю, и оператор FETCH при выполнении перепишет значения полей из текущей строки в переменные.

Закрытие курсора:

CLOSE имя_курсора

Для удаления курсора из памяти используется команда

DEALLOCATE имя_курсора

Для иллюстрации использования курсора создадим процедуру, начисляющую пени по тем неоплаченным счетам, по которым истек срок платежа.

CREATE PROCEDURE peni @percent NUMERIC(5,2)

AS

DECLARE @num INT

@dat DATETIME,

@days INT,

@sum NUMERIC(6)

IF @percent > 0 AND @percent < 100

BEGIN

DECLARE cur1 CURSOR FOR

SELECT bill_num, bill_term FROM k_bill b

WHERE bill_term<GETDATE()

AND ( bill_sum>

(SELECT SUM(payment_sum) FROM k_payment p

WHERE b.bill_num=p.bill_num)

OR NOT EXISTS

(SELECT bill_num FROM k_payment p

WHERE b.bill_num=p.bill_num)

)

OPEN cur1

FETCH cur1 INTO @num, @dat

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @days=DATEDIFF(day, @dat, GETDATE())

SELECT @sum=ISNULL(SUM(payment_sum),0) FROM k_payment

WHERE @num=bill_num

UPDATE k_bill

SET bill_peni=(bill_sum-@sum)*@percent/100*@days

WHERE @num=bill_num

FETCH cur1 INTO @num, @dat

END

DEALLOCATE cur1

END

GO

Рассмотрим эту процедуру более подробно.

Параметром этой процедуры является процент для вычисления пени.

Объявляем курсор на основе следующего запроса: выбрать счета, которые оплачены не полностью и по которым истек срок платежа (т.е, срок оплаты менее текущей даты). В эту выборку попадут частично оплаченные счета, для которых выполняется условие

bill_sum>

(SELECT SUM(payment_sum) FROM k_payment p

WHERE b.bill_num=p.bill_num)

а также полностью неоплаченные счета, для которых платежей вообще не существует, т.е., выполняется условие

NOT EXISTS

(SELECT bill_num FROM k_payment p

WHERE b.bill_num=p.bill_num)

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

Для текущего счета вычисляем количество дней, на который он просрочен, с помощью функции DATEDIFF:

DATEDIFF(day, @dat, GETDATE())

Первый параметр этой функции означает единицу измерения (дни), второй и третий - даты, для которых мы вычисляем разность (текущая дата минус дата счета).

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

Напечатаем список счетов до выполнения процедуры:

SELECT bill_num, bill_term, bill_peni, bill_sum

FROM k_bill

bill_num bill_term bill_peni bill_sum

-------------------------------------------------------

1 2011-12-12 00:00:00.000 0 1000

2 2012-01-12 00:00:00.000 0 2000

3 2012-02-12 00:00:00.000 0 2000

4 2012-01-12 00:00:00.000 0 6000

5 2012-02-12 00:00:00.000 0 2000

6 2012-02-12 00:00:00.000 0 2500

7 2012-01-12 00:00:00.000 0 1500

8 2012-01-12 00:00:00.000 0 1200

9 2012-02-12 00:00:00.000 0 10000

Пусть, например, сегодня 1 мая 2012 г. Запустим процедуру.

EXEC peni 0.5

Напечатаем список счетов после выполнения процедуры:

SELECT bill_num, bill_term, bill_peni, bill_sum

FROM k_bill

bill_num bill_term bill_peni bill_sum

-------------------------------------------------------

1 2011-12-12 00:00:00.000 0 1000

2 2012-01-12 00:00:00.000 550 2000

3 2012-02-12 00:00:00.000 0 2000

4 2012-01-12 00:00:00.000 2750 6000

5 2012-02-12 00:00:00.000 790 2000

6 2012-02-12 00:00:00.000 988 2500

7 2012-01-12 00:00:00.000 275 1500

8 2012-01-12 00:00:00.000 110 1200

9 2012-02-12 00:00:00.000 3950 10000

Задание для индивидуальной работы 8

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

Триггеры

Триггеры - это хранимые процедуры специального вида, которые автоматически выполняются при изменении таблицы с помощью операторов INSERT, UPDATE и DELETE. Триггер создается для определенной таблицы, но может использовать данные других таблиц и объекты других баз данных.

Существует 3 типа триггеров: INSERT, UPDATE и DELETE. Правила работы с триггерами следующие:

· триггеры запускаются только после выполнения вызвавшего их оператора;

· если при выполнении оператора возникает нарушение какого-либо ограничения или другая ошибка, триггер не срабатывает (даже не начинает выполняться);

· триггер и вызвавший его оператор образует транзакцию. Если нужно из триггера отменить вызвавшую его операцию, следует выполнить откат транзакции ROLLBACK;

· триггер срабатывает один раз для каждого оператора, независимо от количества изменяемых им записей.

Краткий формат триггера (более подробно смотрите в MSDN):

CREATE TRIGGER имя_триггера

ON имя_таблицы

FOR INSERT | UPDATE | DELETE

AS

Код_триггера

Рассмотрим элементарный пример: при обновлении таблицы «Сотрудники» печатается сообщение. (Не делайте подобных триггеров в качестве задания для самостоятельной работы!)

CREATE TRIGGER upd_staff

ON k_staff FOR UPDATE

AS

PRINT 'Обновили таблицу Сотрудники'

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

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

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

CREATE TRIGGER ins_prot

ON k_protokol FOR INSERT

AS

DECLARE @s_new NUMERIC(9,2),

@kolvo NUMERIC(6),

@bill_num NUMERIC(6)

SELECT @kolvo=kolvo FROM Inserted

IF @kolvo>0

BEGIN

SELECT @s_new=p.price_sum,

@bill_num=bill_num

FROM k_price p, Inserted i

WHERE p.price_num=i.price_num

IF @s_new !=0

UPDATE k_bill

SET bill_sum=bill_sum+@s_new*@kolvo

WHERE k_bill.bill_num=@bill_num

END

Для тестирования триггера следует выполнить команду добавления, например:

Выберем информацию о счете №1:

SELECT bill_num, bill_sum FROM k_bill WHERE bill_num=1

Получим:

bill_num bill_sum

-----------------

1 1000

Теперь добавим строку в протокол этого счета:

INSERT INTO k_protokol

(price_num, bill_num, kolvo, price_sum)

VALUES(5, 1, 1, 5000);

Снова выберем информацию о счете №1:

SELECT bill_num, bill_sum FROM k_bill WHERE bill_num=1

Получим:

bill_num bill_sum

-----------------

1 6000

Как видим, сумма счета увеличилась на стоимость выбранного товара.

Рассмотрим пример триггера удаления, который вызывается при выполнении команды DELETE в таблице протоколов счетов. При удалении позиции в счете нам нужно пересчитать его сумму. Здесь возникает следующая проблема - если в команде DELETE было удалено сразу несколько строк, трудно будет их обработать. Поэтому сначала мы выполняем проверку: сколько строк было удалено. Эта информация хранится в глобальной переменной @@ROWCOUNT. Если количество удаленных строк больше 1, выводим сообщение об ошибке и отменяем команду DELETE. В остальном этот триггер похож на предыдущий.

CREATE TRIGGER del_prot

ON k_protokol FOR DELETE

AS

DECLARE @s_old NUMERIC(9,2),

@kolvo NUMERIC(6),

@bill_num NUMERIC(6)

IF @@ROWCOUNT>1

BEGIN

RAISERROR

('Нельзя удалять более 1 строки за раз!', 16, 1)

ROLLBACK TRAN

END

ELSE

BEGIN

SELECT @kolvo=kolvo FROM Deleted

IF @kolvo>0

BEGIN

SELECT @s_old=p.price_sum,

@bill_num=bill_num

FROM k_price p, Deleted d

WHERE p.price_num=d.price_num

IF @s_old !=0

UPDATE k_bill

SET bill_sum=bill_sum-@s_old*@kolvo

WHERE k_bill.bill_num=@bill_num

END

END

Ту же задачу можно решить другим образом, не ограничивая количество удаляемых счетов. Просто пересчитаем суммы для всех счетов. Если в таблице Deleted есть строки протокола для какого-то счета, его сумма будет уменьшена. Этот триггер получится гораздо короче, но он неэффективен, так как обрабатывает все счета.

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

CREATE TRIGGER del_prot

ON k_protokol FOR DELETE

AS

UPDATE k_bill SET bill_sum = bill_sum -

(SELECT SUM(price_sum*kolvo)

FROM Deleted d

WHERE d.bill_num=k_bill.bill_num)

Выполним команду создания триггера. Все нормально, ошибок нет.

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

Cannot insert the value NULL into column 'bill_sum'

В чем же дело? Дело в том, что функция SUM вместо ожидаемых числовых значений 0 возвратила NULL-значения для тех счетов, информации о которых нет в таблице Deleted. Чтобы преобразовать ненужные NULL в числовые нули, удобно использовать функцию ISNULL. Она имеет формат

ISNULL(выражение, значение_вместо_NULL)

В том случае, если выражение не равно NULL, функция возвращает выражение. Если оно равно NULL, то значение_вместо_NULL. Триггер примет вид:

CREATE TRIGGER del_prot

ON k_protokol FOR DELETE

AS

UPDATE k_bill SET bill_sum = bill_sum -

ISNULL((SELECT SUM(price_sum*kolvo)

FROM Deleted d

WHERE d.bill_num=k_bill.bill_num),0)

Рассмотрим еще один пример. В таблице платежей мы (на свою голову) установили составной первичный ключ: "номер_счета, номер_платежа", причем номер_платежа должен быть уникальным только в пределах его счета. Т.о., мы не могли для заполнения этого поля использовать свойство IDENTITY (по умолчанию в этом поле мы назначили 0). Попробуем создать триггер для поиска максимального кода платежа по данному счету и формирования нового номера платежа. Все команды в этом триггере вам уже знакомы.

CREATE TRIGGER ins_pay

ON k_payment FOR INSERT

AS

DECLARE @n NUMERIC(6),

@bill NUMERIC(6)

SELECT @bill=bill_num FROM Inserted

SELECT @n=ISNULL(MAX(p.payment_num), 0)

FROM k_payment p, Inserted i

WHERE p.bill_num=i.bill_num

UPDATE k_payment SET payment_num=@n+1

WHERE bill_num=@bill and payment_num=0

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

· IGNOGE - игнорировать,

· RESTRICT - запрещать,

· CASCADE - обрабатывать каскадным образом,

· SET DEFAULT - назначать значения по умолчанию,

· SET NULL - назначать NULL-значения.

Политика IGNORE означает, что мы не предусматриваем никаких проверок и ограничений.

Политика RESTRICT действует, когда мы применяем ограничения внешних ключей.

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

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

Политика SET NULL похожа на предыдущую политику, только вместо значений по умолчанию мы назначаем NULL-значения.

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

Уберем этот внешний ключ:

ALTER TABLE k_protokol DROP CONSTRAINT fk_protokol_bill_num

Создадим триггер:

CREATE TRIGGER del_bill

ON k_bill FOR DELETE

AS

DELETE FROM k_protokol WHERE bill_num IN

(SELECT bill_num FROM Deleted d)

Протестируем триггер. Распечатаем сначала протокол счета с номером 5.

SELECT * FROM k_protokol WHERE bill_num=5

price_num bill_num kolvo price_sum

--------------------------------------

1 5 1 1000.00

2 5 10 100.00

(2 row(s) affected)

Теперь удалим этот счет.

DELETE FROM k_bill WHERE bill_num=5

Снова распечатаем протокол этого счета.

SELECT * FROM k_protokol WHERE bill_num=5

price_num bill_num kolvo price_sum

--------------------------------------

(0 row(s) affected)

Как видим, строки протокола тоже удалены.

Заметим, что этот триггер удаляет строки из таблицы k_protokol, вызывая тем самым ее собственный триггер. Такие цепочки вызовов триггеров могут быть и более длинными, главное - чтобы триггеры не конфликтовали друг с другом и не зацикливались.

Задание для индивидуальной работы 9

Создайте несколько (не менее 2) триггеров для вашей базы данных. Например, с помощью триггеров вы можете реализовать каскадную политику ссылочной целостности.

Приложение 1. Работа с ERModeler

Программа ERModeler разработана одним из авторов пособия специально для данного курса. Загрузить программу можно по адресу:

http://kek.ksu.ru/EOS/BD/ERModeler.zip.

После загрузки следует распаковать программу в произвольный каталог. Файл для запуска - ERModel.exe. Программа представляет собой исполняемый файл.NET, поэтому для функционирования программы необходимо, чтобы на компьютере был установлен .NET Framework версии 2.0 или выше.

Краткое описание функций программы содержится в справочной системе, в секции «Часто задаваемые вопросы»:

Как создать сущность?

Для создания сущности щелкните левой кнопкой мыши в любом месте рабочего поля. Эту настройку можно отключить через меню "Настройки".

Как изменить имя сущности?

Для того чтобы изменить имя сущности, выполните двойной щелчок мышью по этой сущности. Появится текстовое поле, в котором можно изменить имя сущности. Для подтверждения нового имени нажмите "Enter". Обратите внимание, что имя сущности не может быть длиннее 10 букв.

Можно ли перемещать сущность по рабочему полю?

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

Как создать атрибут сущности?

Для создания атрибута сущности сначала выделите сущность, т.е., щелкните на ней левой кнопкой мыши. Выделенная сущность будет отмечена черными квадратиками по углам. Теперь выберите пункт меню "Сущность" - "Новый атрибут". Атрибут будет добавлен над сущностью. Его можно передвинуть в любое место.

Как изменить имя атрибута?

Для того чтобы изменить имя атрибута, выполните двойной щелчок мышью по этому атрибуту. Появится текстовое поле, в котором можно изменить имя атрибута. Для подтверждения нового имени нажмите "Enter". Обратите внимание, что имя атрибута не может быть длиннее 10 букв. Другой способ изменить имя атрибута - использование диалогового окна "Свойства атрибута". Оно вызывается по щелчку правой кнопкой мыши на атрибуте.

Как изменить тип и длину атрибута?

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

Как создать ключевое поле?

Для того чтобы создать ключевое поле, используйте диалоговое окно "Свойства атрибута". Оно вызывается по щелчку правой кнопкой мыши на атрибуте. Название ключевого поля в модели будет подчеркнуто.

Как создать связь между сущностями?

Для создания связи между сущностями используйте диалоговое окно "Свойства связи". Оно вызывается через пункт меню "Связь" - "Новая связь". В этом окне выбирайте сущности и тип связи между ними. Можно создавать только бинарные связи.

Как изменить тип связи?

Для изменения типа связи используйте то же самое диалоговое окно "Свойства связи". Его можно вызвать, щелкнув правой кнопкой мыши на связи.

Как изменить название связи?

Для изменения названия связи используйте то же самое диалоговое окно "Свойства связи". Его можно вызвать, щелкнув правой кнопкой мыши на связи.

Как создать атрибут связи?

Для создания атрибута связи сначала выделите связь, т.е., щелкните на ней левой кнопкой мыши. Выделенная связь будет отмечена черными квадратиками по углам. Теперь выберите пункт меню "Связь" - "Новый атрибут". Атрибут будет добавлен над связью. Его можно передвинуть в любое место.

Как удалить сущность?

Для удаления сущности сначала выберите ее, а затем используйте пункт меню "Сущность" - "Удалить сущность".

Как удалить атрибут?

Для удаления атрибута сущности или связи сначала выберите атрибут, а затем используйте пункт меню "Сущность" - "Удалить атрибут" или "Связь" - "Удалить атрибут".

Как удалить связь?

Для удаления связи сначала выберите ее, а затем используйте пункт меню "Связь" - "Удалить связь".

Как сохранить ER-модель для дальнейшей работы?

В пункте меню "Сохранить как..." можно использовать два формата - XML и двоичный файл. Преимущество файла XML в том, что его можно прочитать в текстовом редакторе или браузере. Преимущество двоичного формата в том, что получаются более короткие файлы.

Как преобразовать ER-модель в графический файл?

В пункте меню "Сохранить как..." есть возможность сохранить модель в виде PNG-файла.

Заметим также, что в окне настроек данной программы можно управлять некоторыми параметрами интерфейса ER-модели:

Обратите внимание, что цвета сущностей, атрибутов и связей сохраняются не в файле модели, а в реестре Windows (т.е., они связаны с определенным компьютером).

Приложение 2. Некоторые типичные ошибки SQL

При отладке программ неизбежно обнаруживаются разнообразные ошибки. Рассмотрим некоторые типичные ситуации при работе в SQL Management Studio.

Команда:

SELCT * FROM k_bill

Ошибка:

Msg 102, Level 15, State 1, Line 1

Неправильный синтаксис около конструкции "*".

Объяснение:

Синтаксическая ошибка, пропущена буква в слове SELECT.

Команда:

INSERT INTO k_firm (firm_name, firm_addr)

VALUES(10, 'Сигма', 'Киев');

Ошибка:

Msg 110, Level 15, State 1, Line 1

Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES. Число значений в предложении VALUES должно соответствовать числу столбцов, указанному в инструкции INSERT.

Объяснение:

В команде вставки в списке полей перечислены два поля, а в списке значений - три значения.

Команда:

INSERT INTO k_firm (firm_num, firm_name, firm_addr)

VALUES(10, 'Сигма', 'Киев');

Ошибка:

Msg 544, Level 16, State 1, Line 1

Невозможно вставить явное значение для столбца идентификаторов в таблице "k_firm", когда параметр IDENTITY_INSERT имеет значение OFF.

Объяснение:

По умолчанию нельзя указывать явное значение для поля, у которого установлено свойство IDENTITY, т.е., для поля firm_num. Если требуется явно указывать значения для таких полей, следует предварительно выполнить команду:

SET IDENTITY_INSERT ON

Команда:

INSERT INTO k_staff

(staff_name, dept_num, staff_hiredate, staff_post)

VALUES('Смит', 4, GETDATE(), 'Менеджер');

Ошибка:

Msg 547, Level 16, State 0, Line 1

Конфликт инструкции INSERT с ограничением FOREIGN KEY "fk_staff_dept_num". Конфликт произошел в базе данных "kontora", таблица "dbo.k_dept", column 'dept_num'.

Выполнение данной инструкции было прервано.

Объяснение:

Нарушено ограничение внешнего ключа: мы пытаемся вставить ссылку на несуществующий отдел с номером 4.

Команда:

DELETE FROM k_contract WHERE contract_num=1

Ошибка:

Msg 547, Level 16, State 0, Line 1

Конфликт инструкции DELETE с ограничением REFERENCE "fk_bill_contract_num". Конфликт произошел в базе данных "kontora", таблица "dbo.k_bill", column 'contract_num'.

Выполнение данной инструкции было прервано.

Объяснение:

Нарушено ограничение внешнего ключа: мы пытаемся удалить договор с номером 1, а к этому договору привязаны счета в таблице k_bill.

Команда:

SELECT * FROM k_contract

WHERE contract_date BETWEEN '01/03/2012' AND '31/03/2012'

Ошибка:

Msg 242, Level 16, State 3, Line 1

Преобразование типа данных char в тип данных datetime привело к значению datetime за пределами диапазона.

Объяснение:

Видимо, на компьютере установлен другой формат даты.

Если вы хотите задать определенный формат даты, например, день:месяц:год, выполните команду:

SET DATEFORMAT dmy

Команда:

SELECT price_name, MIN(price_sum) FROM k_price

Ошибка:

Msg 8120, Level 16, State 1, Line 1

Столбец "k_price.price_name" недопустим в списке выбора, поскольку он не содержится ни в статистической функции, ни в предложении GROUP BY.

Объяснение:

Если используются агрегирующие функции без группировки, в списке полей могут присутствовать только агрегирующие функции.

Команда:

SELECT contract_num, contract_date, bill_num, bill_date

FROM k_bill, k_contract

WHERE k_bill.contract_num=k_contract.contract_num

Ошибка:

Msg 209, Level 16, State 1, Line 1

Неоднозначное имя столбца "contract_num".

Объяснение:

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

Команда:

SELECT contract_num, contract_date FROM k_contract

WHERE contract_num =

(SELECT contract_num FROM k_bill

WHERE bill_date

BETWEEN '01/01/2012' AND '12/31/2012'

AND k_contract.contract_num=k_bill.contract_num)

Ошибка:

Msg 512, Level 16, State 1, Line 1

Вложенный запрос вернул больше одного значения. Это запрещено, когда вложенный запрос следует после =, !=, <, <=, >, >= или используется в качестве выражения.

Объяснение:

Нельзя использовать обычные операции сравнения с подзапросом, если подзапрос возвращает несколько строк. Следует использовать ключевые слова ALL или ANY.

...