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

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

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Январь 2007   →
 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) 122


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

SQL Exercises

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

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

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

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

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

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

§ Подлатал дыру в проверке задачи 80 в ответ на сообщение от valkov.

§ o3 убедил меня опубликовать цены на сертификаты на доступной странице сайта. Собственно, это никогда и не было секретом, и цена сообщалась в ответ на вопросы, задаваемые на форуме. Но мне как-то казалось неприличным в хорошем обществе говорить о деньгах :-); пришли люди поучиться и других поучить, посоревноваться в мастерстве - это я полагал основным мотивом у посетителей сайта. Однако времена меняются, и мотивом, как оказалось, может стать получение сертификата.
Это приятно, т.к. для меня это означает, что сертификат начинает приобретать вес.

§ Палкин решил 141 задачу, пропустив 140; видимо, имя автора испугало :-). В итоге место в десятке (9).

§ Новые лица в сотне:
Aladdin (задач 114, время 8.970)
Moiseenko (111, 51.582)

§ Продвинулись в рейтинге:
marishkin (121, 11.464)
raul (121, 14.295)
Damirishe (120, 6.168)
ДимонХХХ (117, 21.192)
Kamin (117, 37.324)
Fomichev (114, 9.888)

§ На этой неделе сертифицированы:
ds (A06008645) [BK], (B06008645) [AR] (г.Томск, Россия)
=Maxim= (B06010250) [AR] (г.Коломна, МО, Россия)

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

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

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

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

Лучшие результаты (ТОР 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 141 20 333 89 6.183 1.262 11 20 Nov 2006 12 Jan 2007
2 Кувалкин К.С. (Cyrilus) 141 141 20 333 746 12.417 2.519 11 10 Dec 2006 12 Jan 2007
3 Карасёва Н.В. (vlksm) 141 141 20 333 196 27.850 2.710 11 18 Nov 2006 12 Jan 2007
4 Мурашкин И.В. (lepton) 141 141 20 333 240 14.865 4.724 11 18 Nov 2006 05 Jan 2007
5 Зверев Д.Л. (dimzv) 141 141 20 333 1141 9.324 4.938 11 19 Dec 2006 22 Dec 2006
6 Голубин Р.С. (Roman S. Golubin) 141 141 20 333 457 54.984 33.803 11 18 Nov 2006 08 Jan 2007
7 Войнов П.Е. (pаparome) 140 140 20 329 489 2.738 .049 7 26 Dec 2006 12 Jan 2007
8 Тарасов Д.Б. (Gavrila) 140 140 20 329 466 20.242 .513 7 05 Dec 2006 12 Jan 2007
9 Мальцев А.В. (Палкин) 140 141 20 329 145 27.557 7.373 7 09 Jan 2007 10 Jan 2007
10 Васьков Е.В. (Johan) 140 140 20 329 124 12.713 11.402 7 20 Nov 2006 30 Dec 2006
11 Валуев Д.И. (Fiolent) 139 140 20 326 1188 117.017 62.302 4 05 Dec 2006 12 Jan 2007
12 Юлдашев М.Р. (Snowbear) 138 139 20 324 441 4.106 .000 3 02 Oct 2006 12 Jan 2007
13 Держальцев В.А. (MadVet) 138 139 20 324 540 34.249 3.085 3 08 Oct 2006 19 Oct 2006
14 Палий С.А. (PS_Sergey) 139 139 20 325 212 15.756 4.188 3 01 Dec 2006 03 Dec 2006
15 Утёнков М.Н. (=Maxim=) 138 138 20 322 200 24.998 .000 0 19 Nov 2006 10 Jan 2007
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 18 Dec 2006
18 Абашин П.И. (Dizil) 137 137 20 318 453 3.966 .000 0 20 Nov 2006 25 Dec 2006
19 Крижевич С.А. (yaff) 137 137 20 318 523 15.010 .000 0 05 Dec 2006 05 Dec 2006
20 Виноградов С.М. (Botch) 137 137 20 318 74 19.782 .000 0 26 Dec 2006 12 Jan 2007

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Гуленко А. (xlum) 36 91 77 32 109 167 12 Jan 2007
2 >Gorbunov M. (Inuyasha) 59 59 109 0 109 887 12 Jan 2007
3 Nalivashko M.M. (Maha) 57 57 107 0 107 921 08 Jan 2007
4 Прибыльская М. (Prima) 57 57 107 0 107 923 08 Jan 2007
5 Белая Т.И. (Tatiana) 57 57 107 0 107 924 08 Jan 2007
6 Rezanovich N.L. (natacha) 50 56 98 0 98 989 08 Jan 2007
7 >Королева Н.С. (Taha2203) 36 60 73 23 96 567 12 Jan 2007
8 >Safroshkin (Vsafroshkin) 52 52 96 0 96 1205 12 Jan 2007
9 >Устинова О.В. (OxanaUstinova) 51 51 92 0 92 1276 12 Jan 2007
10 Человеков Ч.Ч. (SQLexec) 38 38 86 0 86 1388 08 Jan 2007
11 >Вершило А.С. (Boss) 33 40 57 19 76 1466 12 Jan 2007
12 de Jesus J.B. (josephdejesus) 33 33 51 17 68 1895 09 Jan 2007
13 >Дружинин Д.Г. (paik) 39 39 62 0 62 2103 12 Jan 2007
14 >Устинова О.В. (oxana) 31 58 61 0 61 902 12 Jan 2007
15 kkk K.A. (natka) 32 32 61 0 61 2144 07 Jan 2007
16 Бондаренко А.А. (oo7) 29 39 60 0 60 1815 11 Jan 2007
17 >Night (lostsakura) 34 34 60 0 60 2178 12 Jan 2007
18 >Шептунов П.П. (PavelPS) 23 90 53 6 59 171 12 Jan 2007
19 Калюшик Н.С. (vitaminka) 25 57 58 0 58 925 09 Jan 2007
20 >Martins (Feagond) 30 30 55 0 55 2440 12 Jan 2007
21 Davidova I. (Dory) 27 55 52 0 52 1095 07 Jan 2007
22 sayeed K. (Kavin) 29 29 52 0 52 2569 11 Jan 2007
23 Курбанов Н.И. (_MaSteR_NN_) 24 24 37 15 52 2575 10 Jan 2007
24 >Дупик А.А. (_Andrey_) 29 29 50 2 52 2581 12 Jan 2007
25 hes G.G. (sogi) 29 29 50 0 50 2672 09 Jan 2007
26 sistla I. (indu) 33 33 50 0 50 2679 08 Jan 2007
27 >Голубинцев А.А. (lexaNRJ) 12 53 29 19 48 782 12 Jan 2007
28 >Лукьяненко Д. (Demoon) 18 53 47 0 47 1158 12 Jan 2007
29 aha A.A. (ahanda) 27 27 46 0 46 2887 06 Jan 2007
30 Атлантидов А.А. (Ivan[first]) 19 57 45 0 45 941 11 Jan 2007
31 Алпеев А.А. (Lehan) 26 26 43 0 43 3045 11 Jan 2007
32 Простоквашина А. (Anechka) 18 31 42 0 42 2420 11 Jan 2007
33 >Hramolin O. (oh) 24 25 41 0 41 3130 12 Jan 2007
34 >Levadnaya T. (L.Tamara) 28 28 41 0 41 3148 12 Jan 2007

Изучаем SQL

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

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

Теперь, леди и джентльмены, РЕКУРСИВНЫЙ ЗАПРОС рад представить Вам решение очень сложной задачи, названной задачей коммивояжера (одна из действительных проблем исследования, на которых Edsger Wybe Dijkstra нашел первый эффективный алгоритм и получил премию Turing Award в 1972):

WITH journey (TO_TOWN, STEPS, DISTANCE, WAY)
AS
     (SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST('PARIS' AS VARCHAR(MAX))
     FROM T_JOURNEY
     WHERE JNY_FROM_TOWN = 'PARIS'
     UNION ALL
     SELECT JNY_TO_TOWN, departure.STEPS + 1,
         departure.DISTANCE + arrival.JNY_MILES,
         departure.WAY + ', ' + arrival.JNY_TO_TOWN
     FROM T_JOURNEY AS arrival
         INNER JOIN journey AS departure
             ON departure.TO_TOWN = arrival.JNY_FROM_TOWN)
SELECT TOP 1 *
FROM journey
WHERE TO_TOWN = 'TOULOUSE'
ORDER BY DISTANCE

TO_TOWN     STEPS     DISTANCE     WAY
------------------ ----------- ----------- ---------------------------------
TOULOUSE     2         795         PARIS, CLERMONT-FERRAND, TOULOUSE

Между прочим, TOP n - нестандартная для SQL конструкция... Избегайте ее... Наслаждайтесь CTE!

WITH
     journey (TO_TOWN, STEPS, DISTANCE, WAY)
     AS
          (SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST('PARIS' AS VARCHAR(MAX))
          FROM T_JOURNEY
          WHERE JNY_FROM_TOWN = 'PARIS'
          UNION ALL
          SELECT JNY_TO_TOWN, departure.STEPS + 1,
               departure.DISTANCE + arrival.JNY_MILES,
               departure.WAY + ', ' + arrival.JNY_TO_TOWN
          FROM T_JOURNEY AS arrival
               INNER JOIN journey AS departure
                    ON departure.TO_TOWN = arrival.JNY_FROM_TOWN),
     short (DISTANCE)
     AS
          (SELECT MIN(DISTANCE)
          FROM journey
          WHERE TO_TOWN = 'TOULOUSE')
SELECT *
FROM journey j
     INNER JOIN short s
          ON j.DISTANCE = s.DISTANCE
WHERE TO_TOWN = 'TOULOUSE'

8 - Что еще мы можем сделать?

Фактически, только одно ограничение имеется в нашей обработке сети автострад; это то, что мы построили маршруты в одном направлении. Я имею в виду, что мы можем проехать из Парижа до Лиона, но нам не позволено совершить поездку из Лиона до Парижа. Для этого мы должны добавить обратные пути в таблицу, например:

JNY_FROM_TOWN     JNY_TO_TOWN     JNY_MILES
------------------------------- --------------- ----------------------
LYON                   PARIS         470

Это может быть сделано с помощью очень простого запроса:

INSERT INTO T_JOURNEY
SELECT JNY_TO_TOWN, JNY_FROM_TOWN, JNY_MILES
FROM T_JOURNEY

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

А вот расстояния по различным направлениям:

WITH journey (TO_TOWN)
AS
     (SELECT DISTINCT JNY_FROM_TOWN
     FROM T_JOURNEY
     WHERE JNY_FROM_TOWN = 'PARIS'
     UNION ALL
     SELECT JNY_TO_TOWN
     FROM T_JOURNEY AS arrival
         INNER JOIN journey AS departure
             ON departure.TO_TOWN = arrival.JNY_FROM_TOWN)
SELECT *
FROM journey

TO_TOWN
------------------
PARIS
NANTES
CLERMONT-FERRAND
LYON
...
LYON
MONTPELLIER
MARSEILLE
PARIS
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
(Выполнение оператора прервано. Максимальная рекурсия в 100 итераций была исчерпана до завершения оператора.)

Что случилось? Все просто, Вы пытаетесь перебрать все пути, включая циклические типа Париж, Лион, Париж, Лион, Париж ... и так до бесконечности... Есть ли способ избежать периодически повторяющихся маршрутов? Возможно. В одном из наших предыдущих запросов, мы получили столбец, который дает полный список пройденных городов. Почему бы не использовать его, чтобы избежать зацикливания? Условие будет таким: не проезжайте через город, который уже встречался нам на ПУТИ. Это условие можно записать следующим образом:

WITH journey (TO_TOWN, STEPS, DISTANCE, WAY)
AS
     (SELECT DISTINCT JNY_FROM_TOWN, 0, 0, CAST('PARIS' AS VARCHAR(MAX))
     FROM T_JOURNEY
     WHERE JNY_FROM_TOWN = 'PARIS'
     UNION ALL
     SELECT JNY_TO_TOWN, departure.STEPS + 1,
         departure.DISTANCE + arrival.JNY_MILES,
         departure.WAY + ', ' + arrival.JNY_TO_TOWN
     FROM T_JOURNEY AS arrival
         INNER JOIN journey AS departure
             ON departure.TO_TOWN = arrival.JNY_FROM_TOWN
     WHERE departure.WAY NOT LIKE '%' + arrival.JNY_TO_TOWN + '%')
SELECT *
FROM journey
WHERE TO_TOWN = 'TOULOUSE'

TO_TOWN     STEPS     DISTANCE     WAY
-------------- ----------- ----------- ---------------------------------------------------------
TOULOUSE         3          1015     PARIS, LYON, MONTPELLIER, TOULOUSE
TOULOUSE          4           1485     PARIS, LYON, MONTPELLIER, CLERMONT-FERRAND, TOULOUSE
TOULOUSE          2           795     PARIS, CLERMONT-FERRAND, TOULOUSE
TOULOUSE         3          995     PARIS, CLERMONT-FERRAND, MONTPELLIER, TOULOUSE

Как видно, возник новый маршрут. Худший по расстоянию, но возможно самый красивый!

ЗАКЛЮЧЕНИЕ

CTE может упростить выражение сложных запросов. РЕКУРСИВНЫЕ запросы должны использоваться там, где рекурсия необходима. Если Вы напишите плохой вопрос в MS SQL Server, не бойтесь, циклы рекурсий ограничены числом 100. Вы можете преодолеть этот предел с помощью OPTION (MAXRECURSION n), где n - необходимое вам значение. Предложение OPTION должно быть последним в выражении CTE. Но помните одну вещь: MS SQL Server 2005 фактически находится бета версии!

Наконец, ISO SQL:1999 имел еще некоторые варианты синтаксиса, которые могут позволить Вам выполнять навигацию по данным: DEPTH FIRST (первый в глубину) или BREADTH FIRST (первый в ширину), а также по всем данным, содержавшимся в шагах (в массиве строки, который должен иметь "достаточный" размер, чтобы покрыть все случаи!).

Вот синтаксис:

WITH [ RECURSIVE ] [ ( <liste_colonne> ) ]
     AS ( <requete_select> )
[ <clause_cycle_recherche> ]

with :
<clause_cycle_recherche> ::=
     <clause_recherche>
     | <clause_cycle>
     | <clause_recherche> <clause_cycle>
and :
     <clause_recherche> ::=
     SEARCH { DEPTH FIRTS BY
          | BREADTH FIRST BY } <liste_specification_ordre>
     SET <colonne_sequence>

     <clause_cycle> ::=
     CYCLE <colonne_cycle1> [ { , <colonne_cycle2> } ... ]
     SET <colonne_marquage_cycle>
          TO <valeur_marque_cycle>
          DEFAULT <valeur_marque_non_cycle>
     USING <colonne_chemin>

Вонус (CTE, рекурсивно примененный запрос)

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

CREATE PROCEDURE P_WHAT_TO_DELETE_BEFORE
     @TABLE_TO_DELETE VARCHAR(128), -- Таблица, которую требуется удалить
         @DB VARCHAR(128), -- база данных
         @USR VARCHAR(128) -- схема (dbo в большинстве случаев)
AS

WITH T_CONTRAINTES (table_name, father_table_name)
AS (SELECT DISTINCT CTU.TABLE_NAME, TCT.TABLE_NAME
     FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RFC
         INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU
             ON RFC.CONSTRAINT_CATALOG = CTU.CONSTRAINT_CATALOG
                 AND RFC.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA
                 AND RFC.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME
             INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
                 ON RFC.UNIQUE_CONSTRAINT_CATALOG = TCT.CONSTRAINT_CATALOG
                 AND RFC.UNIQUE_CONSTRAINT_SCHEMA = TCT.CONSTRAINT_SCHEMA
                 AND RFC.UNIQUE_CONSTRAINT_NAME = TCT.CONSTRAINT_NAME
         WHERE CTU.TABLE_CATALOG = @DB
             AND CTU.TABLE_SCHEMA = @USR)
,T_TREE_CONTRAINTES (table_to_delete, level)
AS (SELECT DISTINCT table_name, 0
     FROM T_CONTRAINTES
         WHERE father_table_name = @TABLE_TO_DELETE
         UNION ALL
         SELECT priorT.table_name, level - 1
         FROM T_CONTRAINTES priorT
             INNER JOIN T_TREE_CONTRAINTES beginT
                 ON beginT.table_to_delete = priorT.father_table_name
         WHERE priorT.father_table_name <> priorT.table_name)
SELECT DISTINCT *
FROM T_TREE_CONTRAINTES
ORDER BY level
GO

Учтен случай самосоединения. Параметры: @DB (имя базы данных),
@USR (имя схемы: dbo),
@TABLE_TO_DELETE (таблица, которую Вы хотите удалить).

Библиографический список:

" Le langage SQL : Frederic Brouard, Christian Soutou - Pearson Education 2005 (France)
" Joe Celko's Trees & Hierarchies in SQL for Smarties : Joe Celko - Morgan Kaufmann 2004
" SQL:1999 : J. Melton, A. Simon - Morgan Kauffman, 2002
" SQL developpement : Frederic Brouard - Campus Press 2001 (France)
" SQL for Dummies : Allen G. Taylor - Hungry Minds Inc 2001
" SQL-99 complete really : P. Gulutzan, T. Pelzer - R&D Books, 1999
" SQL 3, Implementing the SQL Foundation Standard : Paul Fortier - Mc Graw Hill, 1999
" SQL for smarties : Joe Celko - Morgan Kaufmann 1995

О самом коротком алгоритме Dijkstra для задачи коммивояжера и других распространенных задачах, см.: www.hsor.org/downloads/Speedy_Delivery_teacher.pdf

15-09-2006

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

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

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

Контакты

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