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

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


Информационный Канал Subscribe.Ru

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

http://www.sql-ex.ru

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

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

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

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


С наступающим Новым Годом, коллеги! Счастья вам и успехов!

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

§ Где-то через неделю я собираюсь выставить пару задач по DML (от Shurgenz). Заканчивается отладка процедуры замены задач. Если участник решает задачу после заменяемой, то он сможет закончить ее, а следующей будет предложена задача-замена. Тем самым автоматически будут меняться сертификационные требования. Сделанные и непросроченные заказы сертификатов остаются действительными. Просроченные заказы буду удалены одновременно с добавлением новых задач (в одной транзакции :-)).

§ По предложению hamlet было внесено уточнение в формулировку задачи 131 (редакция Fiolent).

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

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

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

No Surname Number of
Sel_ex
Number of
DML_ex
Scores Days Days_2 Last_Solved Last_Visit
1 Зверев Д.Л. (dimzv) 136 17 274 252.92 2.219 14 Jul 2004 14 Jul 2004
2 Иткин И.Л. (joseph_itkin) 136 17 274 245.84 2.792 29 Oct 2004 22 Dec 2004
3 Якутин Н.В. (ZrenBy) 136 6 274 428.80 3.993 24 Jun 2004 16 Nov 2004
4 Сныткин В.Л. (vlad_snt) 136 17 274 96.10 6.470 07 Dec 2004 25 Dec 2004
5 Леденев С.А. (Shurgenz) 136 17 274 60.94 8.315 18 Oct 2004 23 Dec 2004
6 Михайлов В.Г. (mslava) 136 17 274 498.01 9.734 26 Oct 2004 26 Oct 2004
7 Spirin (spirin) 136 17 274 44.06 13.429 29 Sep 2004 06 Dec 2004
8 Валуев Д.И. (Fiolent) 136 17 274 293.15 19.314 23 Jun 2004 25 Dec 2004
9 >Halabyan H.H. (hamlet) 136 17 274 86.90 31.941 25 Dec 2004 25 Dec 2004
10 Мельникова И.А. (Iris_m) 136 17 274 141.12 65.651 30 Sep 2004 24 Dec 2004
11 Карабанов А. (gipa) 133 0 268 138.58 5.022 10 Jul 2004 10 Jul 2004
12 Новиков Д.А. (DimaN) 130 0 264 68.17 2.104 01 Mar 2004 01 Mar 2004
13 Драконов Ф.А. (f_d) 130 0 264 36.32 7.243 03 Jun 2004 11 Nov 2004
14 Пятница О.А. (Robin) 128 17 259 589.55 70.834 06 Oct 2004 12 Dec 2004
15 Губарь Д.К. (DEathkNIghtS) 124 17 251 22.67 1.048 03 Nov 2004 03 Nov 2004
16 Смирнов А. (Leshich) 124 17 251 147.15 84.180 03 Aug 2004 19 Nov 2004
17 Ганя А.Д. (Sandman25) 123 1 248 154.83 3.130 16 Jun 2004 16 Jun 2004
18 >Шипунов И. (IAS) 123 17 248 195.13 62.163 25 Dec 2004 25 Dec 2004
19 сафрошкин В.Ю. (safervas) 123 14 248 102.29 80.143 26 Nov 2004 12 Dec 2004
20 Шулакова Н. (nshu) 121 0 244 81.03 5.468 28 Feb 2004 28 Feb 2004

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Крицкая Ю.А. (juli) 64 64 115 9 124 185 25 Dec 2004
2 >Гончаров Д.В. (mitrisoft) 61 61 108 0 108 243 25 Dec 2004
3 Прыгун В. (victorpr) 44 62 87 3 90 227 24 Dec 2004
4 Смородина О.Н. (SONiko) 49 49 79 0 79 348 24 Dec 2004
5 Гривцов С.С. (Secret) 47 47 76 0 76 360 24 Dec 2004
6 Быкова С.С. (scs) 47 47 75 0 75 370 24 Dec 2004
7 Bratchikov (Brig) 41 48 69 0 69 362 24 Dec 2004
8 Газнабиева Д. (Dinara) 42 42 63 0 63 454 22 Dec 2004
9 Дудкин А.В. (Arsu) 32 32 54 9 63 543 23 Dec 2004
10 Сенгилеев А. (hroft1) 38 38 59 0 59 495 24 Dec 2004
11 Быкова С.С. (Быкова Светлана Сергеевна) 38 38 57 0 57 518 21 Dec 2004
12 Краснов Е. (Johny) 33 33 54 0 54 544 22 Dec 2004
13 Ильина О.Л. (Slonik) 28 28 49 0 49 572 21 Dec 2004
14 Кулягина К.С. (Ксю) 28 28 49 0 49 574 25 Dec 2004
15 asdqwe A.Q. (eldakar) 23 28 44 0 44 591 20 Dec 2004
16 Лагунова О. (goodoi) 21 28 42 0 42 589 24 Dec 2004
17 Старенкова Е. (seaqua) 26 57 41 0 41 273 22 Dec 2004
18 Ivanov (avv72) 24 29 41 0 41 642 23 Dec 2004
19 M E. (mysh_lena) 28 28 40 0 40 686 22 Dec 2004
20 Boytsova L.V. (Lena) 18 58 35 1 36 262 25 Dec 2004
21 Забара М.С. (максим111) 7 120 17 17 34 25 24 Dec 2004
22 Sall (sallbimer) 23 23 34 0 34 787 21 Dec 2004
23 Malashchenko T.N. (mtn) 17 62 33 0 33 226 21 Dec 2004
24 >Давлеткильдеев К.И. (severn) 19 54 32 0 32 277 25 Dec 2004
25 Шураков (shurale) 19 19 25 5 30 1018 22 Dec 2004

Характерные ошибки при решении упражнений

SELECT

Задача 58
Вес снаряда (в фунтах), выпускаемого орудием, примерно равен половине куба его калибра (в дюймах). Определите средний вес снарядов для кораблей каждой страны. Учесть также корабли из таблицы Outcomes.

Калибр орудий, как и страна, является атрибутом таблицы Classes. Т.е. нам нужно найти все корабли в базе данных, для которых известен класс. Замечание об учете кораблей из таблицы Outcomes означает, как обычно, что класс головного корабля известен, даже если его нет в таблице Ships.

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

Рассмотрим следующий запрос, отбраковываемый системой:

SELECT DISTINCT Classes.country, ( SELECT AVG( pen.p ) FROM
   (
    SELECT (c1.bore*c1.bore*c1.bore)/2 AS p FROM Classes AS c1, Ships AS s1
         WHERE c1.class=s1.class AND c1.country = Classes.country
            AND c1.bore IS NOT NULL
    UNION ALL
    SELECT (c2.bore*c2.bore*c2.bore)/2 FROM Classes AS c2, Outcomes
        WHERE c2.country = Classes.country AND c2.class=Outcomes.ship
            AND c2.bore IS NOT NULL
            AND Outcomes.ship NOT IN ( SELECT ss.name FROM Ships AS ss )
    ) AS pen
    WHERE pen.p IS NOT NULL
             ) AS weight
FROM Classes
WHERE Classes.country IS NOT NULL

Запрос интересен тем, что в нем не используется группировка, а среднее значение по стране определяется с помощью коррелирующего подзапроса, выполняемого для каждой страны из таблицы Classes. Кроме того, он выполнен в полном соответствии со стандартом. Можно сразу сделать замечание относительно эффективности выполнения этого запроса, т.к. если у страны несколько классов кораблей (что не является для нас большой неожиданностью), то фактически подзапрос будет выполняться для каждого класса, что явно излишне. Появляющиеся при этом дубликаты записей устраняются при помощи DISTINCT, что тоже скажется на быстродействии. Но нас интересует другой вопрос, а именно, почему этот запрос неверен. Чтобы это понять, давайте рассмотрим его по частям.

Начнем с подзапроса, в котором объединяются (UNION ALL) два запроса:

(1)

SELECT (c1.bore*c1.bore*c1.bore)/2 AS p FROM Classes AS c1, Ships AS s1
     WHERE c1.class=s1.class AND c1.country = Classes.country
        AND c1.bore IS NOT NULL
и
(2)

SELECT (c2.bore*c2.bore*c2.bore)/2 FROM Classes AS c2, Outcomes
    WHERE c2.country = Classes.country AND c2.class=Outcomes.ship
        AND c2.bore IS NOT NULL
        AND Outcomes.ship NOT IN ( SELECT ss.name FROM Ships AS ss )

В запросе (1) вычисляется вес снарядов кораблей из таблицы Ships для страны, передаваемой из внешнего запроса (коррелирующий подзапрос). Условие c1.bore IS NOT NULL, на мой взгляд, совершенно излишне, т.к. даже если есть классы с неизвестным калибром, такие значения автоматически будут исключены при вычислении среднего значения с помощью функции AVG. Но это не ошибка в решении задачи.

В запросе (2) аналогичные вычисления делаются для головных кораблей из Outcomes, которые отсутствуют в Ships.

Далее объединение с помощью UNION ALL позволяет сохранить все дубликаты веса, что необходимо, т.к. по крайней мере, корабли одного класса имеют снаряды одного калибра (веса).

Во внешнем запросе вычисляется среднее значение по стране, отфильтровывая случай, когда калибр неизвестен для всех кораблей некоторой страны (WHERE pen.p IS NOT NULL). Это объясняется тем, что если AVG применяется к пустому набору записей, то результат вычисления будет NULL.

Наконец, в основном запросе выводим требуемые по условиям задачи данные.

Вы уже нашли ошибку? Если нет, то нам помогут знания предметной области. Что за таблица Outcomes? Здесь хранятся данные об участии кораблей в сражениях. А корабль, если он не был потоплен, может принимать участие в нескольких сражениях. Таким образом, мы потенциально учитываем головной корабль несколько раз. Если же рассуждать формально, то первичный ключ на этой таблице {корабль, сражение} допускает появление одного и того же корабля неоднократно.

При этом мы не можем вместо UNION ALL использовать UNION по описанным выше причинам, но, тем не менее, исправить теперь этот запрос вам будет несложно.

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

§ Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

Контакты

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

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

http://subscribe.ru/
http://subscribe.ru/feedback/
Подписан адрес:
Код этой рассылки: comp.soft.db.sqlex
Отписаться

В избранное