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

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

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


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


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

Автор
Serge I

Статистика

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

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


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

Выпуск 289 от 10 апреля 2010 г.

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

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

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

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

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


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

§ Под номером 158 выставил новую 3-х бальную задачу от Ozzy.
Старая задача 158 перешла в раздел "Головоломки" под номером 252.

§ Продолжаю предупреждать. :-)
В конце месяца планирую выставить несколько новых рейтинговых задач.

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
Смена лидера!
1. Ozzy (158)
4. $erges (161, 163)

§ Продвинулись в рейтинге:
72. alex.i (136, 169.685)
73. qwrqwr (133, 42.928)
92. Eka (127, 21.775)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
103. SoVa (121, 677.072)
110. vitaliiS (117, 109.605)
116. Vladius (118, 23.534)
128. Inuyasha (111, 2.476)
175. IrishkaS (122, 106.850)

§ На этой неделе сертифицированы:
hamp (B10049585) [AR] - г. Смоленск, Россия

§ ВНИМАНИЕ, КОНКУРС!

SQL2008R2_200 Портал ITband.ru совместно с SQL.ru и форумами Microsoft TechNet, при информационной поддержке sql-ex.ru начинают конкурс посвященный выходу Microsoft SQL Server 2008 R2. На конкурс принимаются русскоязычные технические статьи описывающие техническую компоненту или технологию применяемую в Microsoft SQL Server 2008 R2. К участию в конкурсе так же допускаются подробные описания внедрения Microsoft SQL Server 2008 R2 на предприятии, а так же оригинальные переводы с других языков (в случае перевода необходима ссылка на оригинал).

Срок проведения конкурса с 8 апреля по 8 июля 2010 года. Итоги конкурса будут подведены 16 июля 2010 года.
Для участия в конкурсе необходимо отправить оформленную статью на адрес sqlcontest@sql.ru

Призы

Конкурс проводится в трех номинациях:

- SQL Server глазами администратора

- SQL Server глазами разработчика

- выбор экспертов

За первое место - ASUS Eee PC 1201N

asus Процессор Atom 1600 МГц
Установленная операционная система Windows 7 Starter
Тип графического контроллера встроенный
Беспроводная связь Bluetooth, Wi-Fi 802.11g
Память 2048 Мб DDR2
Дисплей 12.1 дюймов, 1366x768, широкоформатный
Графический чипсет NVIDIA ION
Жесткий диск 250 Гб
Аккумулятор время работы до 5 ч., Li-Ion
Размеры (ДхШхТ) 296x208x33.3 мм
Вес 1.46 кг

За второе место - MicroSoft Wireless Laser Mouse 6000 v2 White USB

microsoft-wireless-laser-mouse-6000-white-usb

За третье место - и Microsoft Wireless Mobile Mouse 6000 Black USB

microsoft-wireless-mobile-mouse-6000-black-usb

За каждую статью свыше 10 000 знаков USB флеш-накопители Transcend 16Gb с гравировкой.
Кроме того каждый автор получит по гарантированный приз: кружку с символикой SQL 2008 R2.
Самый активный (но по делу!) комментатор статей, так же получит MicroSoft Wireless Laser Mouse 6000v.

Правила оформления

Для участия в конкурсе статья должна быть размещена на сайтах itband.ru и sql.ru
Для размещения статьи, автор должен предоставить администрации сайта окончательно оформленную статью в файле формата doc или html.
При наличии скриншотов, схем, графиков или иных изображений автор должен предоставить их отдельными файлами (gif, jpeg, png, tiff, bmp), пронумерованными по порядку расположения в статье.

Критерии оценки

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

Ограничения

Все статьи являются личной собственностью их авторов, однако участие в конкурсе накладывает ряд ограничений.
К конкурсу допускается любой русскоязычный автор. Однако доставка физических призов осуществляется только по России. В случае присуждения приза участнику находящемуся за рубежом, приз будет доставлен в любую точку России, которую укажет автор.
К участию в конкурсе допускаются уже опубликованные статьи, однако, это исключение относится только к публикациям на личном блоге автора.
На время участия в конкурсе, автор берет на себя обязательство не публиковать конкурсную статью или переработку конкурсной статьи на сторонних ресурсах, кроме личного блога.
Отбор статей на конкурс, награждение и общая модерация конкурса проводится Администрацией исходя из собственных субъективных ощущений, опираясь на мнения общественности и сторонних специалистов Microsoft.

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

 

 

 

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

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

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

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

На обучающем этапе - 11867

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 >Зотов П.Г. (Ozzy)1 165 158 22 406 760 199.850 211.374 76 09 Apr 2010 09 Apr 2010
2 Карасёва Н.В. (vlksm)1 166 252 22 408 1412 127.448 91.965 75 18 Mar 2010 08 Apr 2010
3 Креславский О.М. (Arcan)1 166 252 22 408 1147 122.467 97.986 75 22 Mar 2010 09 Apr 2010
4 >Сальников С.А. ($erges)1 165 252 22 406 773 7.593 7.315 73 09 Apr 2010 09 Apr 2010
5 Яцук А.А. (Faust_zp)1 164 252 22 405 1093 121.912 92.200 72 01 Feb 2010 20 Mar 2010
6 Дроздков А.Н. (anddros)5 163 252 22 400 667 8.311 8.292 67 25 Mar 2010 09 Apr 2010
7 Умрихина Е.В. (Umrikhina)1 150 252 22 372 488 41.244 47.336 59 25 Aug 2009 29 Sep 2009
8 Дубинский А.В. (_yizraor)8 159 252 22 389 671 67.232 28.098 56 31 Mar 2010 06 Apr 2010
9 Тарасов Д.Б. (Gavrila)9 154 251 22 373 1669 70.897 36.480 42 22 Mar 2010 09 Apr 2010
10 Сенкевич С.В. (GreyC)1 133 251 21 337 612 57.718 26.238 41 07 Oct 2009 01 Jan 2010
11 Селезнёв А.С. (Артём С.)1 130 251 21 329 444 47.221 34.492 41 25 Jan 2009 03 Mar 2009
12 Бойко Д.М. (Angellore)10 155 252 22 372 964 845.686 137.469 39 28 Mar 2010 09 Apr 2010
13 Никотин В.М. (@Nikotin)1 134 251 21 334 471 8.335 3.704 35 11 Dec 2009 09 Apr 2010
14 Мурашкин И.В. (lepton)1 126 251 21 314 1139 69.439 58.709 32 05 May 2009 14 Dec 2009
15 Печатнов В.В. (pvv)1 142 252 21 345 845 36.963 17.410 31 10 Feb 2010 03 Apr 2010
16 Шиндин А.В. (AlShin)10 142 251 21 343 498 26.251 418.630 30 28 Feb 2010 09 Mar 2010
17 Муллаханов Р.Х. (rem)9 136 251 22 334 649 14.598 20.041 29 20 Jun 2009 02 Feb 2010
18 Орлов М.В. (Eagleoff)15 150 252 22 361 687 47.274 12.519 28 05 Mar 2010 09 Apr 2010
19 Анисимов Д. (danilko)13 139 252 22 332 32 12.700 9.283 26 12 Aug 2009 17 Aug 2009
20 Любченко В.А. (IAS56)6 136 251 22 328 1285 655.251 138.580 23 12 Mar 2010 14 Mar 2010

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 Санько А.В. (count) 42 42 83 34 117 942 09 Apr 2010
2 >Пантелеев Ю.Б. (ypant) 41 45 79 9 88 902 09 Apr 2010
3 >Борисов А.В. (Князь) 28 42 58 6 64 1503 09 Apr 2010
4 >Шмонов (testman) 8 8 13 33 46 3745 09 Apr 2010
5 >smolina O.S. (burka) 14 27 36 0 36 2978 09 Apr 2010
6 A A. (akibaki) 5 5 8 22 30 6018 09 Apr 2010
7 Kuzmik (Dmitro_K) 12 24 29 0 29 3850 07 Apr 2010
8 gong (legend) 0 0 27 27 09 Apr 2010
9 Рыбкина Т.В. (Rybka) 17 48 27 0 27 848 08 Apr 2010
10 Цешинский А.А. (Alex Czech) 7 31 20 0 20 2605 08 Apr 2010
11 >Тупикин М.И. (maivtu) 7 13 20 0 20 4310 09 Apr 2010
12 >Дмитриев А.А. (v.s.o.p.) 10 10 20 0 20 7331 09 Apr 2010
13 dolgopolova (dankas) 0 0 19 19 09 Apr 2010
14 Vovka (Stas141) 7 11 19 0 19 6519 08 Apr 2010
15 Козликова Д.А. (kozlik) 4 40 5 13 18 1259 08 Apr 2010
16 Пронина И.Ю. (iPro) 9 10 18 0 18 7230 05 Apr 2010
17 Zalyaev D.N. (3dpoison_) 8 84 16 0 16 467 09 Apr 2010
18 >Kac (AlexKac) 1 2 1 15 16 4915 09 Apr 2010
19 Ganzha (R.P.McMurphy) 5 5 14 0 14 7809 04 Apr 2010
20 Щукин А.С. (Faust) 6 103 13 0 13 220 09 Apr 2010
21 Скуридина (89058611807) 6 6 13 0 13 7905 08 Apr 2010

Изучаем SQL

Общие табличные выражения (CTE).(Окончание, начало в вып.288)

Моисеенко С.И.

Заметим, что CTE могут использоваться не только с оператором SELECT, но и с другими операторами языка DML. Давайте решим такую задачу:

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

Т.е. эта задача на обновление записей в таблице Pass_in_trip. Я не буду приводить здесь решение этой задачи, которое не использует CTE, но вы можете сами это сделать, чтобы сравнить объемы кода двух решений.

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

WITH
Trip_for_replace AS(
SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129'
)
SELECT * FROM Trip_for_replace;

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

Теперь нам нужна информация о рейсе 7772 для того, чтобы найти ближайший к нему подходящий рейс. Добавим еще одно CTE в определение:

WITH
Trip_for_replace AS(
SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129'
),
Trip_7772 AS (
SELECT * FROM Trip WHERE trip_no=7772
)
SELECT * FROM Trip_7772;

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

WITH
Trip_for_replace AS(
SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129'
),
Trip_7772 AS (
SELECT * FROM Trip WHERE trip_no=7772
),
Trip_candidates as(
SELECT Trip.* FROM Trip, Trip_7772
WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from+Trip_7772.town_to
and Trip.time_out > Trip_7772.time_out
)
SELECT * FROM Trip_candidates;

Trip_candidates - это табличное выражение, которое определяет кандидатов на замену, а именно, рейсы, которые вылетают позже, чем 7772, и которые совершаются между теми же городами. Я использую конкатенацию строк town_from+town_to, чтобы не писать отдельные критерии для пункта отправления и места назначения.

Найдем теперь среди строк-кандидатов наиболее близкий по времени рейс: 

WITH
Trip_for_replace AS(
SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129'
),
Trip_7772 AS (
SELECT * FROM Trip WHERE trip_no=7772
),
Trip_candidates AS(
SELECT Trip.* FROM Trip, Trip_7772
WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from+Trip_7772.town_to
and Trip.time_out > Trip_7772.time_out
),
Trip_replace AS(
SELECT * FROM Trip_candidates
WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates)
)
SELECT * FROM Trip_replace;

Теперь нам осталось последний оператор SELECT заменить на UPDATE, чтобы решить задачу:

WITH
Trip_for_replace AS(
SELECT * FROM Pass_in_trip WHERE trip_no=7772 AND date='20051129'
),
Trip_7772 AS (
SELECT * FROM Trip WHERE trip_no=7772
),
Trip_candidates AS(
SELECT Trip.* FROM Trip, Trip_7772
WHERE Trip.town_from+Trip.town_to = Trip_7772.town_from+Trip_7772.town_to
and Trip.time_out > Trip_7772.time_out
),
Trip_replace AS(
SELECT * FROM Trip_candidates
WHERE time_out <= ALL(SELECT time_out FROM Trip_candidates)
)
UPDATE Trip_for_replace SET trip_no = (SELECT trip_no FROM Trip_replace);

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

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

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

§ Приглашаем вас посетить Интерактивный учебник по SQL.
   Ресурс позиционируется как "справочное обеспечение" для сайта SQL-EX.RU, но может использоваться и независимо от него.

§ Онлайновый выпуск рассылки можно почитать на сайте.

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

§ Хотите поддержать проект? Вот инструкция по применению.

Контакты

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