Отправляет email-рассылки с помощью сервиса Sendsay

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru)

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Декабрь 2006   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


За последние 60 дней ни разу не выходила


Сайт рассылки: http://www.sql-ex.ru
Открыта: 15-09-2004

Автор
Serge I

Статистика

2.594 подписчиков
0 за неделю
←   Все выпуски   →

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 118


Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 118 (16 декабря 2006 г.)

http://www.sql-ex.ru

Новым посетителям сайта

Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.

Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается сейчас после решения 58-ти задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений.

Демонстрация плана выполнения запроса и сравнительная оценка эффективности решений поможет вам освоить принципы оптимизации запросов.

Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.

Новости сайта

§ Cyrilus стал пятым, кто решил все задачи; второе место в рейтинге.
dimzv решил 139 задачу и переместился на 7-ю позицию.

§ Новые лица в сотне:
Botch (задач 118, время 13.213)
Fomichev (110, 8.630)
Damirishe (109, 5.228)

§ Продвинулись в рейтинге:
Дайнин (132, 108.468)
VIG (132, 14.607)
Алекс (128, 49.143)
ds (123, 116.646)
IAS56 (120, 42.801)
Fencer (117, 154.281)
snikol (108, 246.904)

§ На этой неделе сертифицированы:
Sergei(RUS) (A06014292) [BK] (г.Москва, Россия)
Andrej (A06008243) [BK] (г.Коряжма, Россия)
martini (A06014882) [BK] (г.Москва, Россия)

§ Число подписчиков - 3378

Число участников рейтинга - 8330

Число участников второго этапа - 804

Сертифицировано на сайте - 119

Лучшие результаты (ТОР 20)

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Агапов В. (KERBEROS) 141 58 20 333 89 6.183 1.262 11 20 Nov 2006 15 Dec 2006
2 Кувалкин К.С. (Cyrilus) 141 141 20 333 746 12.417 2.519 11 10 Dec 2006 15 Dec 2006
3 Карасёва Н.В. (vlksm) 141 58 20 333 196 27.850 2.710 11 18 Nov 2006 15 Dec 2006
4 Мурашкин И.В. (lepton) 141 58 20 333 240 14.865 4.724 11 18 Nov 2006 10 Dec 2006
5 Голубин Р.С. (Roman S. Golubin) 141 58 20 333 457 54.984 33.803 11 18 Nov 2006 15 Dec 2006
6 Тарасов Д.Б. (Gavrila) 140 140 20 329 466 20.242 .513 7 05 Dec 2006 15 Dec 2006
7 Зверев Д.Л. (dimzv) 140 139 20 329 1131 6.500 2.115 7 09 Dec 2006 15 Dec 2006
8 Васьков Е.В. (Johan) 140 58 20 329 124 12.713 11.402 7 20 Nov 2006 12 Dec 2006
9 Войнов П.Е. (pаparome) 139 58 20 326 453 2.689 .000 4 20 Nov 2006 15 Dec 2006
10 Валуев Д.И. (Fiolent) 139 140 20 326 1188 117.017 62.302 4 05 Dec 2006 15 Dec 2006
11 Юлдашев М.Р. (Snowbear) 138 139 20 324 441 4.106 .000 3 02 Oct 2006 15 Dec 2006
12 Держальцев В.А. (MadVet) 138 139 20 324 540 34.249 3.085 3 08 Oct 2006 19 Oct 2006
13 Палий С.А. (PS_Sergey) 139 139 20 325 212 15.756 4.188 3 01 Dec 2006 03 Dec 2006
14 Мальцев А.В. (Палкин) 138 138 20 322 101 20.184 .000 0 26 Nov 2006 10 Dec 2006
15 Утёнков М.Н. (=Maxim=) 138 58 20 322 200 24.998 .000 0 19 Nov 2006 08 Dec 2006
16 Slobodcicov A.N. (Testo) 136 138 20 319 337 7.171 .000 0 25 Aug 2006 10 Nov 2006
17 Иванов А.Н. (Goapsy) 136 138 20 319 270 18.911 .000 0 07 Aug 2006 09 Oct 2006
18 Абашин П.И. (Dizil) 137 58 20 318 453 3.966 .000 0 20 Nov 2006 30 Nov 2006
19 Крижевич С.А. (yaff) 137 58 20 318 523 15.010 .000 0 05 Dec 2006 05 Dec 2006
20 Вязовецков А.С. (alex_v) 136 137 20 317 141 22.788 .000 0 20 Oct 2006 20 Oct 2006

Лучшие результаты за неделю

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 Ada (fuaron) 57 57 107 28 135 508 15 Dec 2006
2 Амбалова Е.С. (Ambee) 42 57 91 32 123 466 14 Dec 2006
3 Дятлов (оболтус) 66 66 123 0 123 692 15 Dec 2006
4 >Титов С.И. (Титов) 57 57 107 0 107 882 15 Dec 2006
5 Ронжин Д. (ronzhin) 43 57 91 5 96 804 13 Dec 2006
6 Хрящиков Д.М. (Хрик) 52 53 96 0 96 1135 12 Dec 2006
7 >Шолина Н. (needle) 49 52 91 0 91 1179 15 Dec 2006
8 Платонова Т.В. (TiVi) 23 57 47 23 70 602 15 Dec 2006
9 ilitirit I.I. (mr ilitirit) 36 36 65 0 65 1902 13 Dec 2006
10 Кейн Г. (kaine) 21 64 45 15 60 379 14 Dec 2006
11 Чирков (cpp_2003) 25 57 54 0 54 886 11 Dec 2006
12 >Гаськов А.А. (gaskov) 24 71 50 3 53 485 15 Dec 2006
13 >Иванов (_timmy) 32 32 52 1 53 2434 15 Dec 2006
14 >Жук А.О. (ThePower) 31 31 47 5 52 2481 15 Dec 2006
15 Евсеев А.А. (Anton85) 29 29 50 1 51 2542 12 Dec 2006
16 >Бойцова Т.В. (Боец) 29 29 50 0 50 2574 15 Dec 2006
17 Чернышев А.А. (BlackRSU) 28 28 49 0 49 2644 14 Dec 2006
18 >ramesh R.R. (ramesh_sirimalla) 28 28 49 0 49 2648 15 Dec 2006
19 Новиков А.Н. (Bulldozer) 17 24 30 17 47 2407 15 Dec 2006
20 >Иняева Т.М. (tatosha) 7 58 14 32 46 444 15 Dec 2006
21 Сапунов Н.О. (sn0) 23 58 46 0 46 747 15 Dec 2006
22 dddddd (asd) 23 53 46 0 46 1132 12 Dec 2006
23 >Носков К.П. (MouseK) 22 66 45 0 45 346 15 Dec 2006
24 Музалевская (setik) 7 56 17 28 45 545 13 Dec 2006
25 Золотухин А.С. (Zavr) 21 53 43 0 43 929 12 Dec 2006
26 muthavarapu R. (ram_mut) 20 20 43 0 43 2935 12 Dec 2006
27 Avtaev (Niko-an) 17 57 34 8 42 773 15 Dec 2006
28 Tse (OlegTse) 25 25 40 0 40 3086 13 Dec 2006
29 >Хрящев М.Ю. (Delite) 25 25 40 0 40 3117 15 Dec 2006

Изучаем SQL

Рекурсивные запросы в SQL:1999 и SQL Server 2005 (продолжение, начало в вып. #117)

Frederic BROUARD (оригинал: Recursive Queries in SQL:1999 and SQL Server 2005)
Перевод Моисеенко С.И.

3 Два трюка для рекурсии

Чтобы сделать рекурсию, синтаксис SQL нуждается в двух трюках:
ПЕРВЫЙ: Вы должны предоставить начальную точку рекурсии. Это должно делаться с помощью запроса, состоящего из двух частей. Первый запрос сообщает, откуда начинать, а второй запрос говорит, где перейти к следующему шагу. Эти два запроса объединяются теоретико-множественным оператором UNION ALL.
ВТОРОЙ: Вы должны связать CTE и SQL внутри CTE (Inside out, outside in, - была такая популярная песня в стиле диско ... помните?), чтобы обеспечить пошаговое выполнение. Это делается посредством <имя_алиаса_запроса> внутри SQL, который строит CTE.

4 - Первый пример: простая иерархия

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

-- Если таблица существует, удалить ее
IF EXISTS (SELECT *
         FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = USER
         AND TABLE_NAME = 'T_VEHICULE')
     DROP TABLE T_VEHICULE
-- Создать таблицу
CREATE TABLE T_VEHICULE
(VHC_ID INTEGER NOT NULL PRIMARY KEY,
VHC_ID_FATHER INTEGER FOREIGN KEY REFERENCES T_VEHICULE (VHC_ID),
VHC_NAME VARCHAR(16))
-- Наполнить данными
INSERT INTO T_VEHICULE VALUES (1, NULL, 'ALL')
INSERT INTO T_VEHICULE VALUES (2, 1, 'SEA')
INSERT INTO T_VEHICULE VALUES (3, 1, 'EARTH')
INSERT INTO T_VEHICULE VALUES (4, 1, 'AIR')
INSERT INTO T_VEHICULE VALUES (5, 2, 'SUBMARINE')
INSERT INTO T_VEHICULE VALUES (6, 2, 'BOAT')
INSERT INTO T_VEHICULE VALUES (7, 3, 'CAR')
INSERT INTO T_VEHICULE VALUES (8, 3, 'TWO WHEELES')
INSERT INTO T_VEHICULE VALUES (9, 3, 'TRUCK')
INSERT INTO T_VEHICULE VALUES (10, 4, 'ROCKET')
INSERT INTO T_VEHICULE VALUES (11, 4, 'PLANE')
INSERT INTO T_VEHICULE VALUES (12, 8, 'MOTORCYCLE')
INSERT INTO T_VEHICULE VALUES (13, 8, 'BYCYCLE')

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

ALL
|--SEA
|     |--SUBMARINE
|     |--BOAT
|--EARTH
|     |--CAR
|     |--TWO WHEELES
|     |     |--MOTORCYCLE
|     |     |--BYCYCLE
|     |--TRUCK
|--AIR
     |--ROCKET
     |--PLANE

Теперь давайте построим запрос. Мы хотим узнать, откуда пришел МОТОЦИКЛ (MOTORCYCLE). Другими словами, требуется найти всех предков "МОТОЦИКЛА". Начать следует со строки данных, которая содержат motorbyke:

SELECT VHC_NAME, VHC_ID_FATHER
FROM T_VEHICULE
WHERE VHC_NAME = 'MOTORCYCLE'

Мы должны иметь родительский ID, чтобы перейти к следующему шагу. Второй запрос, который делает этот следующий шаг, должен быть написан подобно следующему:

SELECT VHC_NAME, VHC_ID_FATHER
FROM T_VEHICULE

Как Вы видите, запросы отличаются только тем, что мы не задаем фильтр WHERE для перехода к следующему шагу. Напомню, что мы должны объединить эти два запроса с помощью UNION ALL, что определит пошаговый метод:

SELECT VHC_NAME, VHC_ID_FATHER
FROM T_VEHICULE
WHERE VHC_NAME = 'MOTORCYCLE'
UNION ALL
SELECT VHC_NAME, VHC_ID_FATHER
FROM T_VEHICULE

Давайте теперь разместим все это в CTE:

WITH
     tree (data, id)
     AS (SELECT VHC_NAME, VHC_ID_FATHER
         FROM T_VEHICULE
         WHERE VHC_NAME = 'MOTORCYCLE'
         UNION ALL
         SELECT VHC_NAME, VHC_ID_FATHER
         FROM T_VEHICULE)

Теперь мы вплотную подошли к рекурсии. Последний шаг должен сделать цикл, чтобы организовать выполнение пошагового метода. Это делается при использовании имени CTE в качестве таблицы внутри SQL-запроса CTE. В нашем случае мы должны соединить второй запрос CTE с самим CTE, организовав цепочку по tree.id = (второй запрос).VHC_ID. Это можно сделать следующим образом:

WITH
     tree (data, id)
     AS (SELECT VHC_NAME, VHC_ID_FATHER
         FROM T_VEHICULE
         WHERE VHC_NAME = 'MOTORCYCLE'
         UNION ALL
         SELECT VHC_NAME, VHC_ID_FATHER
         FROM T_VEHICULE V
             INNER JOIN tree t
             ON t.id = V.VHC_ID) SELECT *
FROM tree

Осталось лишь написать самый простой SELECT, чтобы вывести данные. Теперь, если Вы для выполнения запроса нажмете кнопку F5, то увидите следующее:

data                     id
---------------- -----------
MOTORCYCLE     8
TWO WHEELES     3
EARTH                 1
ALL                  NULL

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

                 correlation
         ____________________________________
         |
         v
WITH tree (data, id)
AS (SELECT VHC_NAME, VHC_ID_FATHER
     FROM T_VEHICULE
     WHERE VHC_NAME = 'MOTORCYCLE'
     UNION ALL
     SELECT VHC_NAME, VHC_ID_FATHER
     FROM T_VEHICULE V
         INNER JOIN tree t     <---------------
         ON t.id = V.VHC_ID)
SELECT *
FROM tree

Кстати, а что остановило рекурсивный процесс? Факт, что больше нет звеньев цепочки, когда достигается значение id "NULL", что в нашем примере означает случай достижения "ALL".

Теперь Вы получаете метод. Отметим, что по неясным причинам MS SQL Server 2005 не допускает ключевого слова RECURSIVE после слова WITH, которое вводит CTE. Но 2005 пока находится в бета версии и, таким образом, мы можем ожидать, что оно появится в финальной версии продукта.

(Продолжение следует...

Полезная информация

§ Все статьи, публикуемые в рассылке, затем выкладываются на сайте Книги и статьи по SQL.

§ Поступила в продажу книга SQL. Задачи и решения, посвященная анализу ошибок, допускаемых при решении задач первого этапа. На сайте издательства Питер можно сделать заказ и познакомиться с содержанием.

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"
В избранное