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

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

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


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

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

http://www.sql-ex.ru

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

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

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

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


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

§ В результате оживленной дискуссии на форуме сайта родилась формулировка, более точно соответствующая решению задачи 83.

§ Отвечая на предложение john_ua1acc, внес пояснение по поводу нумерации мест в самолетах (см. описание схемы БД).

§ Внесены проверочные данные (Fiolent), не допускающие привязки решения задачи 99 к конкретному году.

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

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

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

Лучшие результаты (ТОР 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 17 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 18 Dec 2004
5 Леденев С.А. (Shurgenz) 136 17 274 60.94 8.315 18 Oct 2004 17 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 18 Dec 2004
9 Мельникова И.А. (Iris_m) 136 17 274 141.12 65.651 30 Sep 2004 17 Dec 2004
10 Карабанов А. (gipa) 133 0 268 138.58 5.022 10 Jul 2004 10 Jul 2004
11 Новиков Д.А. (DimaN) 130 0 264 68.17 2.104 01 Mar 2004 01 Mar 2004
12 Драконов Ф.А. (f_d) 130 0 264 36.32 7.243 03 Jun 2004 11 Nov 2004
13 Пятница О.А. (Robin) 128 17 259 589.55 70.834 06 Oct 2004 12 Dec 2004
14 Губарь Д.К. (DEathkNIghtS) 124 17 251 22.67 1.048 03 Nov 2004 03 Nov 2004
15 Смирнов А. (Leshich) 124 17 251 147.15 84.180 03 Aug 2004 19 Nov 2004
16 Ганя А.Д. (Sandman25) 123 1 248 154.83 3.130 16 Jun 2004 16 Jun 2004
17 Halabyan H.H. (hamlet) 123 17 248 77.91 24.756 16 Dec 2004 17 Dec 2004
18 сафрошкин В.Ю. (safervas) 123 14 248 102.29 80.143 26 Nov 2004 12 Dec 2004
19 Шулакова Н. (nshu) 121 0 244 81.03 5.468 28 Feb 2004 28 Feb 2004
20 Gershovich (VIG) 121 17 244 628.96 6.905 30 Nov 2004 18 Dec 2004

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 Иванов А.И. (dracovolans) 63 63 112 0 112 195 16 Dec 2004
2 >1 1.1. (111) 52 52 89 9 98 291 18 Dec 2004
3 Malashchenko T.N. (mtn) 45 45 77 0 77 347 16 Dec 2004
4 >Караваев Ю. (Nemo_plastik) 40 50 73 0 73 321 18 Dec 2004
5 Вопилова Е.В. (evd) 39 39 64 0 64 427 17 Dec 2004
6 >Boytsova L.V. (Lena) 39 39 63 0 63 441 18 Dec 2004
7 Рахманов И.Е. (bloom) 40 40 59 3 62 484 15 Dec 2004
8 Величко М.В. (Scaut) 27 27 46 0 46 600 14 Dec 2004
9 >Шипунов И. (IAS) 18 120 43 0 43 23 18 Dec 2004
10 Куликов А.О. (Domiel) 25 25 40 0 40 650 15 Dec 2004
11 Симоненко Д. (duxbellorum) 24 24 37 3 40 694 17 Dec 2004
12 Ponomarenko I. (ghost_i) 24 53 37 2 39 296 16 Dec 2004
13 Hartyunyan A. (arthur85) 30 30 37 0 37 713 17 Dec 2004
14 >Булаев В.В. (Kvix) 15 118 35 0 35 25 18 Dec 2004
15 >Черемных В.П. (Sp999) 16 105 34 0 34 32 18 Dec 2004
16 Карцев (migel) 21 21 29 0 29 913 15 Dec 2004
17 Komissarov V. (visor123) 21 21 29 0 29 915 14 Dec 2004
18 Кулицкий Д.С. (StrayCat) 10 59 19 9 28 250 15 Dec 2004
19 Забара М.С. (максим111) 11 113 26 0 26 30 17 Dec 2004
20 Палин К.С. (ghost32) 11 27 24 0 24 609 13 Dec 2004
21 Крошкин А.В. (al05) 15 22 24 0 24 880 17 Dec 2004
22 Прыгун В. (victorpr) 18 18 23 0 23 1030 17 Dec 2004
23 >Bolschakoff (Bolschakoff) 10 88 21 0 21 65 18 Dec 2004
24 A A.A. (fastsolver) 17 17 21 0 21 1066 16 Dec 2004

Изучаем SQL

Об использовании оператора CASE

В справке на сайте описан синтаксис этого оператора и приведены примеры его использования. Однако эти примеры демонстрируют применение CASE в предложении SELECT. Действительно, это место наиболее частого использования оператора, однако не единственное. Мы можем использовать CASE практически везде, и здесь я покажу несколько примеров его применения в других предложениях оператора SELECT.

Предложение WHERE

Предложение WHERE ограничивает выходной набор теми строками, которые удовлетворяют предикату в этом предложении. Предположим, что у нас имеется такое соответствие между объемом памяти ПК и типом используемой операционной системы (естественно, условно):

RAM < 64     W95
RAM >=64 и < 128      W98
RAM >= 128      W2k

Если мы захотим отобрать компьютеры по типу ОС (заметим, что такого поля нет в таблице PC), то можно написать следующий оператор:

SELECT * FROM pc
WHERE CASE WHEN ram <64 THEN 'W95'
     WHEN ram <128 THEN 'W98'
     ELSE 'W2k' END
     ='W98'

Здесь мы выбираем модели, подходящие для операционной системы W98. Ниже приведен результат выполнения этого запроса.

code model speed ram hd cd price
1 1232 500 64 5 12x 600.0
3 1233 500 64 5 12x 600.0
8 1232 450 64 8 24x 350.0

Это может оказаться более полезным, чем кажется на первый взгляд, если иметь в виду конфиденциальность информации. Например, на клиенте можно формировать запросы, которые будут оперировать такими категориями, как высоко, средне и низко оплачиваемый специалист. Т.е. сами критерии (оклады) будут спрятаны, скажем, в хранимой процедуре, а через параметр будет передаваться что-то типа символьной строки "средний".

Предложение GROUP BY

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

SELECT
     CASE WHEN ram <64 THEN 'W95'
         WHEN ram <128 THEN 'W98'
         ELSE 'W2k' END Type,
     COUNT(*) Qty FROM pc
GROUP BY
     CASE WHEN ram <64 THEN 'W95'
         WHEN ram <128 THEN 'W98'
         ELSE 'W2k' END

В результате выполнения запроса получим

Type Qty
W2k 5
W95 3
W98 3

Здесь мы дублируем оператор CASE в предложении SELECT, чтобы получить столбец с наименованием ОС.

Предложение ORDER BY

Использование оператора CASE в предложении ORDER BY позволяет выполнить более сложную сортировку, чем та, которая допускается при использовании сортировки по набору столбцов. Если мы будем выполнять сортировку по столбцу RAM, то у нас есть две возможности - по возрастанию или по убыванию. Если же мы хотим вывести сначала средние модели, т.е. те, которые отвечают W98, потом высшие (W2k), а уже потом низшие (W95), то можно поступить так

SELECT * FROM pc
ORDER BY
CASE WHEN ram <64 THEN '3-W95'
     WHEN ram <128 THEN '1-W98'
     ELSE '2-W2k' END

Цифры перед названием ОС проставлены в соответствии с желательным порядком сортировки. В противном случае, упорядочение текстовых значений по возрастанию будет следующим: W2k, W95, W98. Вот результат вышеприведенного запроса (столбец сортировки выделен цветом):

code model speed ram hd cd price
3 1233 500 64 5 12x 600.0
1 1232 500 64 5 12x 600.0
8 1232 450 64 8 24x 350.0
2 1121 750 128 14 40x 850.0
4 1121 600 128 14 40x 850.0
5 1121 600 128 8 40x 850.0
6 1233 750 128 20 50x 950.0
11 1233 900 128 40 40x 980.0
7 1232 500 32 10 12x 400.0
9 1232 450 32 10 24x 350.0
10 1260 500 32 10 12x 350.0

Есть еще более интересная возможность сортировки, а именно, сортировать по разным столбцам в зависимости от значения в некотором поле. Пусть, например, в группе W95 мы хотим выполнить сортировку по столбцу speed, в группе W98 - по столбцу hd, в группе W2k - по столбцу price. Т.е. нам нужно в каждой группе, характеризуемой ОС (описанными выше критериями), выполнить сортировку по разным полям. Эту, на первый взгляд непростую задачу, решает простой запрос с оператором CASE в предложении ORDER BY:

SELECT * FROM pc
ORDER BY
    ram, CASE WHEN ram <64 THEN speed
         WHEN ram <128 THEN hd
         ELSE price END

В нижеприведенном результате поля сортировки каждой группы выделены разными цветами.

code model speed ram hd cd price
9 1232 450 32 10 24x 350.0
10 1260 500 32 10 12x 350.0
7 1232 500 32 10 12x 400.0
1 1232 500 64 5 12x 600.0
3 1233 500 64 5 12x 600.0
8 1232 450 64 8 24x 350.0
2 1121 750 128 14 40x 850.0
4 1121 600 128 14 40x 850.0
5 1121 600 128 8 40x 850.0
6 1233 750 128 20 50x 950.0
11 1233 900 128 40 40x 980.0

Если вы хотите поделиться другими примерами использования оператора CASE, пишите.

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

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77@yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.
Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"
http://subscribe.ru/
http://subscribe.ru/feedback/		 Подписан адрес:
Код этой рассылки: comp.soft.db.sqlex		 Отписаться
В избранное