1.6. Выборка данных из таблиц

Задание 1.6.1. Выборка начальных строк таблицы.

USE Northwind

Select Top 7 * From Territories --различные

Select Top 10 Percent * From Territories --различные упорядоченные по TerritoryID

Select Order By TerritoryID With Ties * From Territories --все 7

Select All * From Territories --все строки таблицы

Select Distinct * From Territories -- все без дублирующих

Задание 1.6.2. Использование псевдонимов в запросе.

USE Pubs

Select Top 10 au_id, au_fname AS [Фамилия], au_lname

From Authors --имя 2-го столбца изменено

Задание 1.6.3. Включение в результат дополнительного столбца-выражения.

Select Top 7 NewID() AS Глобальный_уникальный_номер, '--', au_id, DatePart (ms,GetDate())

From Authors --три колонки являются выражениями, из них две - безымянные

Задание 1.6.4. Использование подзапроса, возвращающего одну строку.

Select Top 5 (Select au_fname From authors

Where au_id='527-72-3246') AS Подзапрос, title_id

From Titles

Задание 1.6.5. Включение столбца-счетчика.

Select Top 50 Percent jobs.IDENTITYCOL AS Number,

job_id, job_desc From jobs

Задание 1.6.6. Использование ключевых слов CROSS JOIN для связывания двух таблиц.

USE Pubs

SELECT discounts.stor_id, discounts. discounttype,

stores.stor_name

FROM discounts CROSS JOIN stores

Задание 1.6.7. Использование ключевых слов INNER JOIN для связывания двух таблиц.

SELECT authors.au_lname, authors.au_fname, titleauthor.au_ord, titleauthor.royaltyper

FROM authors INEER JOIN titleauthor

ON authors.au_id = titleauthor.au_id

WHERE authors.sate = 'CA'

Задание 1.6.8. Q)Использование ключевых слов LEFT OUTER JOIN для связывания двух таблиц.

SELECT authors.au_lname, authors.au_fname, titleauthor.royaltyper

FROM authors LEFT OUTER JOIN titleauthor

ON authors.au_id = titleauthor.au_id

WHERE (authors.state = 'CA')

Задание 1.6.9. Использование ключевых слов RIGHT OUTER JOIN для связывания двух таблиц:

SELECT titleauthor.au_ord, titleauthor.royaltyper, authors.au_lname, authors.au_fname

FROM titleauthor RIGHT OUTER JOIN authors

ON titleauthor.au_id = authors.au_id

WHERE (authors.state = 'CA')

Задание 1.6.10. Использование ключевых слов FULL OUTER JOIN для связывания двух таблиц.

SELECT discounts.stor_id, discounts.discounttype, stores.stor_name

FROM discounts FULL OUTER JOIN stores

ON discounts.stor_id = stores.stor_id

Задание 1.6.11. Использование раздела WHERE оператора SELECT.

Select * From authors Where 3=6

Select * From authors Where state <> 'CA'

Declare @@Var1 int

Set @@Var1 = 4095

Select title_id, type, pub_id, price From titles

Where ((ytd_sales = @@Var1) OR

(price BETWEEN 5 AND 15))

Select discounts.stor_id, discounts.discounttype, stores.stor_name

From discounts, stores --здесь”,”-это CROSS JOIN

Where disscounts.stor_id = stores.stor_id --1 строка

Select discounts/stor_id, discounts.discounttype, stores.stor_name

From discounts INEER JOIN stores

ON discounts.stor_id = stores.stor_id

Select discounts.stor_id, discounts.discounttype, stores.stor_name

From discounts, stores

Where discounts.stor_id, *= stores.stor_id --3 строки

Select discounts.stor_id, discounts.disconttype, stores.stor_name

From discounts LEFT OUTER JOIN stores

ON discounts.stor_id = stores.stor_id --3 строки

Select discounts.stor_id, discounts.discounttype, stores.stor_name

Where discounts.stor_id =* stores.stor_id

Select discounts.stor_id, discounts.discounttype, stores.stor_name

From discounts RIGHT OUTER JOIN stores

ON discounts.stor_id = stores.stor_id --6 строк

Задание 1.6.12. Использование разделов GROUP BY и HAVING.

Select type, SUM(price), COUNT(*)

From titles

Group By type

Select type, SUM(price), count = COUNT(*)

Form titles

Where type < 'ps'

Group BY type

Select type, SUM(price), count = COUNT(*)

From titles

Where type < 'ps'

Group By ALL type

Select type, pub_id, SUM(price), COUNT(*)

From titles

Where price <> 0

Group By type, pub_id

Select type, pub_id, SUM(price), COUNT(*)

From titles

Where price <> 0

Group By type, pub_id With Cube

Select type, pub_id, SUM(price), COUNT(*)

From titles

Where price <> 0

Group By type, pub_id

With ROLLUP

Задание 1.6.13. Объединение таблиц с помощью раздела UNION.

USE Northwind

Select City, Phone Info #Tab1 From customers

Where contacttitle = 'Marketing Assistant'

Select City, Phone Info #Tab 2 From Customers

Where contacttitle = 'Sales Associate'

Selest City, Phone From #Tab1

UNION

Select City, Phone From #Tab2

Задание 1.6.14. Использование раздела ORDER BY.

USE Northwind

Select ContactTitle, City From Customers

Where ContactTitle IN ('Marketing Assistant'

'Sales Agent'

'Sales Associate')

Order By ContactTitle,City

Задание 1.6.15. Использование раздела COMPUTE.

Select title_id, price From Titles

Where (type = 'bisiness') OR (type = 'mod_cook')

Compute SUM(price), COUNT(price)

Select title_id, price From Titles

Where (type = 'business') OR (type = 'mod_cook')

Order By type

Compute Count(price), Sum(Price) By type

1.7. Создание триггеров

Задание 1.7.1. Создать таблицу authsmall из таблицы authors базы данных Pubs и для новой таблицы запрограммировать триггер auth_del, который будет выводить информацию о попытках удаления и количестве удаляемых строк.

Сначала создадим таблицу authsmall c колонками au_id, au_fname, au_lname, phone и скопируем в нее данных из таблицы authors следующей командой

SELECT au_id, au_fname, au_lname, phone

INTO authsmoll

FROM authors

Затем создадим и запрограммируем триггер

CREATE TRIGGER auth_del

ON authsmall

FOR DELETE

AS

PRINT ‘Попытка удаления’ + STR (@@ POWCOUNT)+

‘строк в таблице authsmall’

PRINT ‘Пользователь’ + CURRENT_USER

IF CURRENT_USER <> ‘dbo’

BEGIN

PRINT ‘Удаление запрещено’

ROLLBACK TRANSACTION

END

ELSE

PRINT ‘Удаление разрешено’

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

DELETE FROM authsmall WHERE au_fname = ‘Johnson’

DELETE FROM authsmall WHERE 2*2=5

Задание 1.7.2. Создать триггер auth_upd для таблицы authsmall, построенный в первом задании, который будет разрешать изменение столбца au_id этой таблицы всем, кроме владельца dbo.

Создание и программирование триггера можно выполнить следующим образом:

CREATE TRIGGER auth_upd

ON authsmall

FOR UPDATE

AS

SET NOCOUNT ON -- не сообщать о завершении команд;

PRINT ‘Попытка изменения данных в таблице authsmall’

IF (COLUMNS_UPDATE () &1)! = 0 -- 1-й столбец;

PRINT ‘Изменение столбца au_id’

IF (COLUMNS_UPDATE () &2)! = 0 -- 2-й столбец;

PRINT ‘Изменение столбца au_fname’

IF (COLUMNS_UPDATE () &4)! = 0 -- 3-й столбец;

PRINT ‘Изменение столбца au_lname’