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

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


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

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

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

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

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

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


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

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

§ Выставил на третьем этапе простую задачку. Это временно. Мне просто необходимо посоветоваться с лидерами о том, где ей место. Поэтому просьба ко всем, кому доступен третий этап, решить задачку (много времени она не займет) и высказаться.

§ Некоторые перестановки среди лидеров связаны с новой 143 задачей, которая, скорее всего, переместится на второй этап. Однако подождем, что скажут другие лидеры после решения этой задачи.

§ Одна задача до третьего этапа осталась inkerman (задач 137, время 4.834).

§ Новые лица в сотне и вернувшиеся в нее:
Олег (121, 8.072)
-=ac=- (121, 42.861)
Shadow77 (120, 4.448)

§ Продвинулись в рейтинге:
burakov58 (138, 29.483)
SoVa (128, 238.060)
maar (122, 48.032)
ba (120, 46.020)

§ На этой неделе сертифицированы:
Anatolii (A07005528) [BK] (г.Пермь, Россия)
neron82 (A07020929) [BK] (г.Благовещенск, Россия)
inkerman (B07021053) [AR] (г.Долгопрудный, МО, Россия)

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

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

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

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

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

No Person Number of
Sel_ex
Last_Sel Number of
DML_ex
Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 >Северюхин Ю.А. (Venser) 143 143 21 343 188 4.942 .684 15 07 Sep 2007 07 Sep 2007
2 >Солдатенков Ю.С. (SolYUtor) 143 143 21 343 477 17.826 2.714 15 07 Sep 2007 07 Sep 2007
3 >Карасёва Н.В. (vlksm) 143 143 21 343 489 31.559 6.127 15 07 Sep 2007 07 Sep 2007
4 >Голубин Р.С. (Roman S. Golubin) 143 143 21 343 750 55.394 34.206 15 07 Sep 2007 07 Sep 2007
5 Шептунов П.П. (PavelPS) 142 142 21 342 222 8.145 3.499 14 06 Aug 2007 07 Sep 2007
6 Мурашкин И.В. (lepton) 142 142 21 342 499 15.737 5.539 14 04 Aug 2007 05 Sep 2007
7 Мальцев А.В. (Палкин) 142 142 21 342 383 27.974 7.690 14 04 Sep 2007 04 Sep 2007
8 >Тарасов Д.Б. (Gavrila) 142 143 21 340 742 21.395 1.527 12 07 Sep 2007 07 Sep 2007
9 Агапов В. (KERBEROS) 137 141 20 329 89 6.163 1.262 11 20 Nov 2006 27 Jul 2007
10 Кувалкин К.С. (Cyrilus) 141 141 20 337 1014 12.541 2.519 11 04 Sep 2007 07 Sep 2007
11 Зверев Д.Л. (dimzv) 137 141 20 329 1141 9.294 4.938 11 19 Dec 2006 22 Dec 2006
12 Войнов П.Е. (pаparome) 141 142 21 338 712 2.765 .049 10 06 Aug 2007 09 Aug 2007
13 Любченко В.А. (IAS56) 141 142 21 338 334 176.801 20.130 10 04 Aug 2007 13 Aug 2007
14 Васьков Е.В. (Johan) 140 140 21 335 402 12.786 11.402 7 25 Aug 2007 25 Aug 2007
15 >Креславский О.М. (Arcan) 140 143 21 332 220 9.965 .349 4 07 Sep 2007 07 Sep 2007
16 Валуев Д.И. (Fiolent) 139 140 20 330 1433 117.088 62.302 4 07 Aug 2007 07 Sep 2007
17 Юлдашев М.Р. (Snowbear) 139 139 21 331 749 4.132 .000 3 06 Aug 2007 06 Sep 2007
18 Утёнков М.Н. (=Maxim=) 139 139 21 331 459 25.187 .036 3 05 Aug 2007 20 Aug 2007
19 Держальцев В.А. (MadVet) 134 139 20 320 540 34.190 3.085 3 08 Oct 2006 19 Oct 2006
20 Палий С.А. (PS_Sergey) 135 139 20 321 212 15.704 4.188 3 01 Dec 2006 03 Dec 2006

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Sofronia A. (alexsof) 62 62 115 34 149 551 07 Sep 2007
2 >Fisher A.N. (Captain025) 57 57 110 19 129 794 07 Sep 2007
3 >Абашин (AMba) 57 57 110 17 127 844 07 Sep 2007
4 Выходцев А.А. (AndreyV) 44 59 94 11 105 933 07 Sep 2007
5 >Dvoryashin M. (mixd) 31 31 58 34 92 1602 07 Sep 2007
6 >Осым К.Р. (osym) 47 47 89 0 89 1661 07 Sep 2007
7 >Завьялов А.Н. (Blazer) 45 45 83 1 84 1791 07 Sep 2007
8 >Бекиш Ю.М. (FedorStalker) 42 42 79 0 79 1929 07 Sep 2007
9 Huts Y.V. (N0rb) 39 39 69 3 72 2189 05 Sep 2007
10 >dimdim D.D. (Dim) 32 39 67 0 67 2110 07 Sep 2007
11 Yaremchuk N. (Nazar) 28 28 50 17 67 2407 07 Sep 2007
12 >Закревский И.П. (IZakrevskiy) 31 46 66 0 66 1337 07 Sep 2007
13 Федотов А.А. (-ALEXANDER-) 22 55 45 19 64 893 06 Sep 2007
14 >Петренко А.М. (Andemon) 28 28 49 15 64 2542 07 Sep 2007
15 Abaddon (bubykin) 31 44 63 0 63 1992 07 Sep 2007
16 Трушина О.С. (TRU) 21 40 45 17 62 1692 07 Sep 2007
17 >cast3r C.A. (lury) 28 28 49 9 58 2855 07 Sep 2007
18 Гарнцев А.В. (galexius) 19 46 38 17 55 1272 07 Sep 2007
19 >a A.V. (aleksashka) 27 41 52 3 55 2144 07 Sep 2007
20 Бойко Д.М. (Angellore) 15 61 28 25 53 624 06 Sep 2007
21 Сухарев В.В. (Psevdo) 25 25 42 9 51 3389 06 Sep 2007
22 Клещенок (PavelR) 24 24 41 9 50 3497 06 Sep 2007
23 >Ракицкий А.К. (tester) 28 28 50 0 50 3498 07 Sep 2007
24 >Чарышкин (PeterChar) 27 27 48 0 48 3655 07 Sep 2007
25 Лебедев Д.Н. (dettaglio) 27 27 46 0 46 3799 07 Sep 2007
26 >Шустов В. (Vite`k) 27 27 46 0 46 3805 07 Sep 2007
27 Mikhailovsky I. (IL55) 6 65 11 29 40 509 06 Sep 2007
28 >Мартинес В. (slava) 15 53 29 11 40 1085 07 Sep 2007
29 Тихонов Д.А. (Ellumilel) 18 33 34 6 40 2599 06 Sep 2007

Изучаем SQL

Мастерская XML IV - FOR XML EXPLICIT (окончание, начало в вып.152, 153)

Jacob Sebastian (оригинал: XML Workshop IV - FOR XML EXPLICIT )
Перевод Моисеенко С.И.

Шаг 3: Генерация элемента Customer

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

 


    1/*
    2 Как обычно, сначала создаем результирующий набор и рассматриваем его структуру
    3 прежде чем генерировать XML.
    4 */
    5
    6 SELECT
    7     1 AS Tag,
    8     NULL AS Parent,
    9     c.CountryName AS 'Country!1!name',
   10     c.Currency AS 'Country!1!currency',
   11     NULL AS 'City!2!name',
   12     NULL AS 'Customer!3!id',
   13     NULL AS 'Customer!3!name',
   14     NULL AS 'Customer!3!phone'
   15 FROM
   16     Countries c
   17 UNION ALL
   18 SELECT
   19     2 AS Tag,
   20     1 AS Parent,
   21     Country.CountryName,
   22     Country.Currency,
   23     City.CityName,
   24     NULL,
   25     NULL,
   26     NULL
   27 FROM Cities City
   28 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
   29 UNION ALL
   30 SELECT
   31         3 AS Tag,
   32         2 AS Parent,
   33         Country.CountryName AS [name],
   34         Country.Currency,
   35         City.CityName AS [name],
   36         Customer.CustomerNumber AS [id],
   37         Customer.CustomerName AS [name],
   38         Customer.Phone
   39     FROM
   40         Customers Customer
   41         INNER JOIN Cities City ON (City.CityID = Customer.CityID)
   42         INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
   43 ORDER BY 'Country!1!name', 'City!2!name'
   44

 

45 /* 46 Результат: 47 48 Tag Parent Country!1!name Country!1!currency City!2!name Customer!3!id Customer!3!name Customer!3!phone 49 ---- ------ -------------- -------------------- ----------- ------------- ----------------- ---------------- 50 1 NULL England Pound Sterling NULL NULL NULL NULL 51 2 1 England Pound Sterling London NULL NULL NULL 52 3 2 England Pound Sterling London TH Thomas Hardy 444-444-4444 53 1 NULL India Rupee NULL NULL NULL NULL 54 2 1 India Rupee New Delhi NULL NULL NULL 55 3 2 India Rupee New Delhi JS Jacob Sebastian 555-555-5555 56 1 NULL USA US Dollars NULL NULL NULL NULL 57 2 1 USA US Dollars NJ NULL NULL NULL 58 3 2 USA US Dollars NJ EN Elizabeth Lincoln 333-333-3333 59 3 2 USA US Dollars NY MK John Mark 111-111-1111 60 2 1 USA US Dollars NY NULL NULL NULL 61 3 2 USA US Dollars NY WS Will Smith 222-222-2222 62 63 "Tag" 64 Отметим, что сейчас мы имеем несколько записей со значением "3", 65 означающим третий уровень в иерархии XML. 66 "Parent" 67 Новые записи (Tag = 3) имеют своего родителя с установкой "2", т.е. 68 родитель такого элемента есть запись с "Tag", который имеет значение "2" 69 "Customer!3!*" 70 Эти три столбца содержат информацию, необходимую для узла третьего уровня. 71 */ 72

 

 
   73 /*
   74 Теперь получим XML.
   75 */
   76
   77 SELECT
   78     1 AS Tag,
   79     NULL AS Parent,
   80     c.CountryName AS 'Country!1!name',
   81     c.Currency AS 'Country!1!currency',
   82     NULL AS 'City!2!name',
   83     NULL AS 'Customer!3!id',
   84     NULL AS 'Customer!3!name',
   85     NULL AS 'Customer!3!phone'
   86 FROM
   87     Countries c
   88 UNION ALL
   89 SELECT
   90     2 AS Tag,
   91     1 AS Parent,
   92     Country.CountryName,
   93     Country.Currency,
   94     City.CityName,
   95     NULL,
   96     NULL,
   97     NULL
   98 FROM Cities City
   99 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
  100 UNION ALL
  101 SELECT
  102         3 AS Tag,
  103         2 AS Parent,
  104         Country.CountryName AS [name],
  105         Country.Currency,
  106         City.CityName AS [name],
  107         Customer.CustomerNumber AS [id],
  108         Customer.CustomerName AS [name],
  109         Customer.Phone
  110     FROM
  111         Customers Customer
  112         INNER JOIN Cities City ON (City.CityID = Customer.CityID)
  113         INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
  114 ORDER BY 'Country!1!name', 'City!2!name'
  115 FOR XML EXPLICIT
  116

 

117 /* 118 Результат: 119 120 <Country name="England" currency="Pound Sterling"> 121 <City name="London"> 122 <Customer id="TH" name="Thomas Hardy" phone="444-444-4444" /> 123 </City> 124 </Country> 125 <Country name="India" currency="Rupee"> 126 <City name="New Delhi"> 127 <Customer id="JS" name="Jacob Sebastian" phone="555-555-5555" /> 128 </City> 129 </Country> 130 <Country name="USA" currency="US Dollars"> 131 <City name="NJ"> 132 <Customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333" /> 133 <Customer id="MK" name="John Mark" phone="111-111-1111" /> 134 </City> 135 <City name="NY"> 136 <Customer id="WS" name="Will Smith" phone="222-222-2222" /> 137 </City> 138 </Country> 139 */

Шаг 4: Генерация корневого узла (Root)

Мы почти все сделали. У нас имеются все данные, которые требуются для получения желаемого формата XML. Однако у нас отсутствует корневой узел CustomersByRegion. В отличие от режимов AUTO и RAW, EXPLICIT не дает способа создания корневого узла. Чтобы получить корневой узел, давайте создадим фиктивный результирующий набор и объединим его (UNION) с нашими данными. Фиктивный результирующий набор будет САМЫМ ВЕРХНИМ узлом, и другие узлы будут передвинуты на один уровень вниз. Следующий пример подробно это демонстрирует.

 

    1 SELECT
    2     1 AS Tag,
    3     NULL AS Parent,
    4     NULL AS 'CustomersByRegion!1', -- пустой корневой элемент
    5     NULL AS 'Country!2!name',
    6     NULL AS 'Country!2!currency',
    7     NULL AS 'City!3!name',
    8     NULL AS 'Customer!4!id',
    9     NULL AS 'Customer!4!name',
   10     NULL AS 'Customer!4!phone'
   11 UNION ALL
   12 SELECT
   13     2 AS Tag,
   14     1 AS Parent,
   15     NULL,
   16     c.CountryName,
   17     c.Currency,
   18     NULL,
   19     NULL,
   20     NULL,
   21     NULL
   22 FROM
   23     Countries c
   24 UNION ALL
   25 SELECT
   26     3 AS Tag,
   27     2 AS Parent,
   28     NULL,
   29     Country.CountryName,
   30     Country.Currency,
   31     City.CityName,
   32     NULL,
   33     NULL,
   34     NULL
   35 FROM Cities City
   36 INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
   37 UNION ALL
   38 SELECT
   39         4 AS Tag,
   40         3 AS Parent,
   41         NULL,
   42         Country.CountryName AS [name],
   43         Country.Currency,
   44         City.CityName AS [name],
   45         Customer.CustomerNumber AS [id],
   46         Customer.CustomerName AS [name],
   47         Customer.Phone
   48     FROM
   49         Customers Customer
   50         INNER JOIN Cities City ON (City.CityID = Customer.CityID)
   51         INNER JOIN Countries Country ON (Country.CountryID = City.CountryID)
   52 ORDER BY 'Country!2!name', 'City!3!name', Parent
   53 FOR XML EXPLICIT
   54

 

55 /* 56 Результат: 57 58 <CustomersByRegion> 59 <Country name="England" currency="Pound Sterling"> 60 <City name="London"> 61 <Customer id="TH" name="Thomas Hardy" phone="444-444-4444" /> 62 </City> 63 </Country> 64 <Country name="India" currency="Rupee"> 65 <City name="New Delhi"> 66 <Customer id="JS" name="Jacob Sebastian" phone="555-555-5555" /> 67 </City> 68 </Country> 69 <Country name="USA" currency="US Dollars"> 70 <City name="NJ"> 71 <Customer id="EN" name="Elizabeth Lincoln" phone="333-333-3333" /> 72 </City> 73 <City name="NY"> 74 <Customer id="MK" name="John Mark" phone="111-111-1111" /> 75 <Customer id="WS" name="Will Smith" phone="222-222-2222" /> 76 </City> 77 </Country> 78 </CustomersByRegion> 79 */

Отметьте, что я добавил фиктивный результирующий набор в качестве узла первого уровня. Этот результирующий набор дает строку, содержащую везде значения NULL кроме Tag. Обратите внимание, что я не определял имя атрибута с элементом (CustomersByRegion!1). В результате получили элемент без всяких атрибутов.

Заключение

В этой статье, я попытался объяснить использование директивы EXPLICIT с предложением FOR XML. Большинство XML требований к форматированию может быть удовлетворено с помощью ключевых слов AUTO, RAW и PATH. Использование этих режимов достаточно просто. EXPLICIT потребуется только в том случае, когда возможностей других режимов окажется недостаточно.

08/08/2007

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

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

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

§ Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях:
del.icio.us
dzone.com
Digg.com
Reddit.com

Контакты

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

Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"

В избранное