| ← Октябрь 2004 → | ||||||
|
1
|
2
|
|||||
|---|---|---|---|---|---|---|
|
4
|
5
|
6
|
7
|
8
|
9
|
|
|
11
|
12
|
13
|
14
|
15
|
16
|
|
|
18
|
19
|
20
|
22
|
23
|
||
|
25
|
26
|
27
|
28
|
29
|
30
|
31
|
За последние 60 дней ни разу не выходила
Сайт рассылки:
http://www.sql-ex.ru
Открыта:
15-09-2004
Статистика
0 за неделю
Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru)
| Информационный Канал Subscribe.Ru |
Новости сайта "Упражнения по SQL" Выпуск 6 (24 октября 2004 г.)
 |
Новым посетителям сайтаСайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.Мы надеемся, что справочного материала сайта окажется достаточно для самостоятельного обучения. Кроме того, свои решения вы можете обсудить на форуме сайта. Опытных же специалистов приглашаем проверить (продемонстрировать) свое мастерство и принять участие в соревновании, обеспечиваемом рейтинговой системой учета времени выполнения заданий. Фактически, рейтинг ведется на втором этапе тестирования, который начинается после решения 62-х задач первого этапа. При подсчете рейтинга каждого участника отбрасывается один самый худший показатель среди всех решенных им упражнений. |
Новости сайта
§ Устранена неоднозначность формулировки задачи #87, которую заметил Tracer.
§ Начат новый опрос о нужности на сайте тестов по T-SQL. Просьба проголосовать, если этот вопрос вас интересует.
Краткая справка. T-SQL (или Transact-SQL) - процедурное расширение языка SQL. Используется для программирования на стороне сервера, в частности, для написания хранимых процедур и триггеров. Поддерживается в MS SQL Server и Sybase.
Приглашаю всех желающих поучаствовать в разработке тестовых вопросов. Это ускорило бы реализацию и, кроме того, всегда проще отвечать на свои вопросы, чем на чужие :-).Число участников рейтинга - 2095
Число участников второго этапа - 193
Лучшие результаты (ТОР 20)
| No | Surname | Number of exercises |
Scores | Days | Days_2 | Last_Solved | Last_Visit |
| 1 | Зверев Д.Л. (dimzv) | 136 | 274 | 252.92 | 2.219 | 14 Jul 2004 | 22 Oct 2004 |
| 2 | Якутин Н.В. (ZrenBy) | 136 | 274 | 428.80 | 3.993 | 24 Jun 2004 | 07 Oct 2004 |
| 3 | Леденев С.А. (Shurgenz) | 136 | 274 | 60.94 | 8.315 | 18 Oct 2004 | 22 Oct 2004 |
| 4 | Spirin (spirin) | 136 | 274 | 44.06 | 13.429 | 29 Sep 2004 | 18 Oct 2004 |
| 5 | Валуев Д.И. (Fiolent) | 136 | 274 | 293.15 | 19.314 | 23 Jun 2004 | 22 Oct 2004 |
| 6 | Мельникова И.А. (Iris_m) | 136 | 274 | 141.12 | 65.651 | 30 Sep 2004 | 22 Oct 2004 |
| 7 | Карабанов А. (gipa) | 133 | 268 | 138.58 | 5.022 | 10 Jul 2004 | 08 Oct 2004 |
| 8 | Новиков Д.А. (DimaN) | 130 | 264 | 68.17 | 2.104 | 01 Mar 2004 | 07 Apr 2004 |
| 9 | Драконов Ф.А. (f_d) | 130 | 264 | 36.32 | 7.243 | 03 Jun 2004 | 28 Jun 2004 |
| 10 | Пятница О.А. (Robin) | 128 | 259 | 589.55 | 70.834 | 06 Oct 2004 | 22 Oct 2004 |
| 11 | Иткин И.Л. (joseph_itkin) | 124 | 251 | 130.99 | 2.068 | 06 Jul 2004 | 22 Oct 2004 |
| 12 | Михайлов В.Г. (mslava) | 124 | 251 | 494.51 | 7.431 | 23 Oct 2004 | 23 Oct 2004 |
| 13 | Смирнов А. (Leshich) | 124 | 251 | 147.15 | 84.180 | 03 Aug 2004 | 30 Sep 2004 |
| 14 | Ганя А.Д. (Sandman25) | 123 | 248 | 154.83 | 3.130 | 16 Jun 2004 | 21 Sep 2004 |
| 15 | Шулакова Н. (nshu) | 121 | 244 | 81.03 | 5.468 | 28 Feb 2004 | 02 Mar 2004 |
| 16 | Митронин А.А. (mitronin) | 120 | 241 | 405.96 | 2.150 | 09 Aug 2004 | 08 Sep 2004 |
| 17 | Муравейко О.Ю. (Aaz) | 120 | 241 | 134.65 | 5.720 | 07 Mar 2004 | 22 Oct 2004 |
| 18 | Gershovich (VIG) | 119 | 238 | 486.32 | 4.006 | 10 Jul 2004 | 22 Oct 2004 |
| 19 | Безруков Н.Н. (eviluser) | 119 | 238 | 11.15 | 6.891 | 12 Jan 2004 | 12 Jan 2004 |
| 20 | Krbashyan R. (raf_krb) | 117 | 234 | 144.28 | 41.050 | 09 Mar 2004 | 20 Sep 2004 |
Лучшие результаты за неделю
| No | surname | n_sel | sel_all | sel_scores | dml_scores | scores | rating | last_visit |
| 1 | - (Felenka) | 58 | 58 | 99 | 17 | 116 | 217 | 18 Oct 2004 |
| 2 | Филиппов (fil) | 60 | 60 | 105 | 1 | 106 | 206 | 23 Oct 2004 |
| 3 | frolov G.A. (JayS) | 42 | 57 | 81 | 4 | 85 | 221 | 23 Oct 2004 |
| 4 | >Губарь Д.К. (DEathkNIghtS) | 23 | 84 | 54 | 26 | 80 | 56 | 24 Oct 2004 |
| 5 | Коваленко А.С. (alexbest) | 42 | 42 | 74 | 0 | 74 | 321 | 22 Oct 2004 |
| 6 | Раздобудько С.А. (ivc_mayak) | 38 | 38 | 69 | 0 | 69 | 340 | 22 Oct 2004 |
| 7 | Высоцкий С.К. (sergwsk) | 37 | 37 | 66 | 0 | 66 | 355 | 19 Oct 2004 |
| 8 | >Grunichev A.V. (lexxxa) | 28 | 28 | 49 | 9 | 58 | 483 | 23 Oct 2004 |
| 9 | Шаповалов М.П. (shap) | 34 | 34 | 46 | 0 | 46 | 516 | 23 Oct 2004 |
| 10 | Иванов Д. (Figo) | 9 | 55 | 21 | 23 | 44 | 228 | 22 Oct 2004 |
| 11 | Овечкин М. (Gralph) | 25 | 25 | 42 | 0 | 42 | 547 | 21 Oct 2004 |
| 12 | Сныткин В.Л. (vlad_snt) | 11 | 89 | 24 | 17 | 41 | 50 | 22 Oct 2004 |
| 13 | Пидодня О.В. (9370) | 25 | 25 | 40 | 0 | 40 | 560 | 21 Oct 2004 |
| 14 | Ризне М. (rmaged) | 30 | 30 | 40 | 0 | 40 | 561 | 23 Oct 2004 |
| 15 | Matsevich V. (vadimm) | 25 | 25 | 40 | 0 | 40 | 567 | 23 Oct 2004 |
| 16 | Mayboroda A.I. (Hohol) | 10 | 63 | 22 | 17 | 39 | 168 | 21 Oct 2004 |
| 17 | Zoziashvili A. (Akela007) | 24 | 24 | 37 | 0 | 37 | 599 | 22 Oct 2004 |
| 18 | Тортумашев (johnnytort) | 10 | 72 | 27 | 9 | 36 | 91 | 22 Oct 2004 |
| 19 | Дегтярь (degtyar) | 19 | 34 | 27 | 9 | 36 | 430 | 21 Oct 2004 |
| 20 | Шимко И. (banga) | 27 | 27 | 36 | 0 | 36 | 634 | 24 Oct 2004 |
| 21 | Dzmanashvili I. (Ioseb) | 25 | 25 | 35 | 0 | 35 | 640 | 24 Oct 2004 |
| 22 | Грицюта И.В. (Atez) | 23 | 23 | 34 | 0 | 34 | 673 | 23 Oct 2004 |
| 23 | Vadim (Bubba) | 23 | 23 | 34 | 0 | 34 | 675 | 21 Oct 2004 |
| 24 | Puzyrevskaya (pnv) | 22 | 22 | 32 | 0 | 32 | 717 | 22 Oct 2004 |
| 25 | Латкин Н.Н. (l0ng) | 11 | 108 | 28 | 3 | 31 | 23 | 23 Oct 2004 |
| 26 | Самохвалов (asam) | 22 | 22 | 31 | 0 | 31 | 734 | 18 Oct 2004 |
| 27 | Старенкова Е. (seaqua) | 21 | 21 | 31 | 0 | 31 | 741 | 22 Oct 2004 |
| 28 | Demushkin I. (id21) | 22 | 22 | 31 | 0 | 31 | 750 | 22 Oct 2004 |
| 29 | Yatsun (tor) | 22 | 22 | 31 | 0 | 31 | 751 | 22 Oct 2004 |
| 30 | Грига В.П. (grigavp) | 22 | 22 | 31 | 0 | 31 | 765 | 21 Oct 2004 |
Характерные ошибки при решении упражнений
SELECT
Задача 37Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).
Вот один из запросов, которые отвергает система проверки:
SELECT classFROM ships
GROUP BY class
HAVING COUNT(name) = 1
UNION
SELECT class
FROM classes c, outcomes o
WHERE c.class = o.ship AND
NOT EXISTS (SELECT 'x'
FROM ships s
WHERE o.ship = s.class);
Первый запрос в объединении подсчитывает корабли каждого класса из таблицы Ships, оставляя в результирующем наборе только те классы, которые имеют только один корабль. Второй запрос определяет классы, у которых головной корабль находится в таблице Outcomes при условии, что кораблей такого класса нет в таблице Ships.
Рассмотрим следующий пример данных, для которых этот запрос будет давать неправильный результат.
Каждый, кто решал задачи по этой схеме данных ("Корабли"), знает, что такое Бисмарк. Это головной корабль, которого нет в таблице Ships. Теперь представим себе, что один другой корабль класса Бисмарк имеется в таблице Ships, скажем, Терплиц.
Тогда первый запрос вернет класс Бисмарк, т.к. в таблице Ships имеется один корабль этого класса. Второй запрос класс Бисмарк не вернет, т.к. предикат:
NOT EXISTS (SELECT 'x'FROM ships s
WHERE o.ship = s.class);
для корабля Бисмарк в таблице Outcomes будет оценен как FALSE. В результате объединения этих запросов получим класс Бисмарк в выходных данных всего запроса. Всякому, кто внимательно следил за ходом рассуждений, понятно, что в базе данных имеется ДВА корабля класса Бисмарк. Т.е. этот класс не должен присутствовать в результатах выполнения запроса.
Приведя на нашем форуме рассматриваемый запрос, ivc_mayak попросил предоставить данные, на которых он даст неверный результат. Пожалуйста,
INSERT INTO Ships VALUES('Terplits', 'Bismark', 1940)Изучаем SQL
Нумерация (окончание, начало в выпуске 5)
Нумерация строк при наличии дубликатов в результирующем столбце
Согласно реляционной теории в таблице не может быть одинаковых строк. И хотя реализации допускают построение таблиц, не имеющих первичного ключа, и, как следствие, допускающих одинаковые строки, следует отнести эту ситуацию к ошибкам в проектировании. Кроме того, таблица, не имеющая первичного ключа или уникального индекса, не является обновляемой. Последнее заключение вполне естественно, т.к. система не имеет информации о том, какой из дубликатов предпочесть.
Поэтому, говоря о дубликатах, мы имеем в виду дубликаты в результирующем наборе, появление которых может быть обусловлено тем, что первичный ключ весь или частично (в случае составного ключа) отсутствует в результирующем наборе.
Чтобы пояснить сказанное, рассмотрим следующий запрос
SELECT id_psg FROM pass_in_trip
который вернет номера пассажиров, которые совершали полеты. Поскольку один и тот же пассажир может совершить несколько рейсов, мы получаем здесь дубликаты. Однако этот пассажир не может в один и тот же день более одного раза полететь одним и тем же рейсом, что регламентируется соответствующим первичным ключом - {trip_no, date, id_psg}.
Итак, нам нужно перенумеровать пассажиров, которые могут повторяться. Зададимся для начала порядком, в котором их нужно переименовать. Пусть этот порядок соответствует сортировке по трем полям - дата полета, идентификатор пассажира и номер рейса (по возрастанию).
Чтобы свести задачу к ранее рассмотренной (а это возможно, т.к. три перечисленных поля представляют собой первичный ключ), сконструируем столбец, который объединял бы информацию из перечисленных полей. Поскольку поля имеют разные типы данных, приведем их к символьному представлению и выполним конкатенацию.
При этом нам нужно определиться с количеством символов. Поскольку в представлении даты вылета отсутствует время, ограничимся 11 символами. Номер рейса везде представлен четырехсимвольным числом. Остается идентификатор пассажира. В соответствии с имеющейся базой данных ограничимся 2 символами, что не принижает общности подхода. Однако для правильности сортировки нужно "односимвольных" пассажиров записывать с лидирующим нулем - 01, 09 и т.д. Иначе пассажир с номером 10 будет предшествовать, скажем, 2-му. Выполним соответствующие преобразования:
(2)
Дата - CAST(date AS CHAR(11))
Номер рейса - CAST(trip_no AS CHAR(4))
Идентификатор пассажира - RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2).
В последнем преобразовании (2) я использовал нестандартную функцию RIGHT (SQL Server), которая извлекает из строки указанное количество символов справа. Можно было бы использовать функцию SUBSTRING, однако так получается короче и, кроме того, наверняка в других коммерческих продуктах имеются аналогичные "улучшения" стандарта. Соединяя эти выражения в указанном порядке, мы получим уникальный столбец, который и будет использоваться для нумерации пассажиров в соответствии с возрастанием (убыванием) значений в этом столбце. Используя этот столбец, мы можем воспользоваться решением (1)(см. вып.5). Окончательно получим
SELECT COUNT(*) num, P2.id_psg FROM (
SELECT *, CAST(date AS CHAR(11)) + RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
CAST(trip_no AS CHAR(4)) dit FROM pass_in_trip) P1 JOIN (
SELECT *, CAST(date AS CHAR(11)) + RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2)+
CAST(trip_no AS CHAR(4)) dit FROM pass_in_trip) P2
ON P1.dit <= P2.dit
GROUP BY P2.dit, P2.id_psg
ORDER BY 1
Для нумерации в другом порядке достаточно конкатенировать преобразования (2) в другой последовательности. Например, чтобы пронумеровать пассажиров в порядке их идентификационных номеров, первым слагаемым должно быть RIGHT('00' + CAST(id_psg AS VARCHAR(2)), 2).
§ Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.
Полезная информация
§ Решая задачу один наш участник написал запрос, который подвешивал сервер (SQL Server 2000 SP3). При этом штатными средствами не удавалось ни убить подключение (KILL), ни даже остановить сервер. Как оказалось, в подобный ступор сервер входил еще до выполнения запроса, на этапе построения плана его выполнения.
Обращение в службу технической поддержки Майкрософт (Glory, MVP) дало следующие результаты:
1. Майкрософт признал это за баг и принял в обработку.
2. Т.к. SP4 для SQL2000 уже в стадии выпуска, то данный баг в нем не будет исправлен. Значит будет позднее отдельный фикс для него.
Контакты
По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
| http://subscribe.ru/
http://subscribe.ru/feedback/ |
Подписан адрес: Код этой рассылки: comp.soft.db.sqlex |
Отписаться |
| В избранное | ||
