SQL в вопросах и задачах

41

яз ыка SQL (в оз можно, с указ аниемд лины, точ ности и масштаба) или быть NULL-з нач ением.

∙ любое ч ислов ое в ыражение может быть яв но п реобраз ов а но в любой

д ругой ч ислов ой тип .

∙симв ольноев ыра жениеможет бытьп реобраз ов ано в любой ч ислов ой тип .

д оп олняетсяп робелами; если меньше, то усекается.

∙NULL-з нач ениеп реобраз уетсяв NULL-з нач ениесоотв етств ующ его тип а.

∙ч ислов оев ыра жениеможет бытьп реобраз ов ано в симв ольный тип .

Пример.

SELECT CAST STUDENT_ID AS CHAR(10)

FROM STUDENT;

УП Р АЖ НЕ НИ Я

1. Состав ьтез ап рос д лятаблицы STUDENT таким образ ом, ч тобы в ыход на я

10;КУЗНЕЦОВ;БОРИС;0;БРЯНСК;8/12/1981;10.

2. Состав ьтез ап рос д лята блицы STUDENT такимобраз ом, ч тобы в ыход ная таблица сод ержала в сего од ин столбец след ующ емв ид е:

Б.КУЗНЕЦОВ; место жительства-БРЯНСК; родился - 8.12.81.

3. Состав ьтез ап рос д лята блицы STUDENT такимобраз ом, ч тобы в ыход ная таблица сод ержала в сего од ин столбец след ующ емв ид е:

42

б.кузнецов; место жительства-брянск; родился: 8-дек-1981.

4. Состав ьтез ап рос д лята блицы STUDENT такимобраз ом, ч тобы в ыход ная таблица сод ержала в сего од ин столбец след ующ емв ид е:

Борис Кузнецов родился в 1981 году.

5.Выв ести фа милии, имена студ ентов и в елич ину п олуч аемых ими стип енд ий, п ри этомз нач ениястип енд ий д олжны бытьув елич ены в 100 р аз .

6.То же, ч то и в з ад ач е4, но только д лястуд ентов 1, 2 и 4-го курсов и та ким образ ом, ч тобы фамилии и имена были в ыв ед ены п роп исными букв ами.

7.Состав ьтез ап рос д лята блицы UNIVERSITY такимобраз ом, ч тобы

в ыход наятаблица сод ержала в сего од ин столбец след ующ емв ид е:

Код-10; ВГУ-г.ВОРОНЕЖ; Рейтинг=296.

8. Тоже, ч то и в з а д ач е7, но з нач ениярейтинга требуетсяокруглитьд о п ерв ого з нака (на п р имер, з на ч ение382 округляетсяд о 400).

2.4. Агрегирование и групповы е ф ункц ии

д анного п оля;

∙AVG в ыч исляет сред неез нач ениед ляв сех в ыбранных з нач ений д а нного

п оля;

∙MAX в ыч исляет на ибольшееиз в сех в ыбра нных з нач ений д анного п оля;

∙MIN в ыч исляет на именьшееиз в сех в ыбранных з на ч ений д анного п оля. В SELECT-з ап р осе агрегир ующ ие функции исп ольз уются ана логич но

именам п олей, п ри э том п ослед ние(имена п олей) исп ольз уются в кач еств е аргументов этих функций.

Функция AVG п ред на з нач ена д ля п од сч ета сред него з нач ения п оля на множеств ез а п исей таблицы.

43

Н а п ример, д ля оп р ед еления сред него з нач ения п оля MARK (оценки) п о в сем з ап исямта блицы EXAM_MARKS можно исп ольз ов а тьз ап рос с функцией AVG след ующ его в ид а :

SELECT AVG(MARK)

FROM EXAM_MARKS;

Д ля п од сч ета общ его колич еств а строк в та блицеслед ует исп ольз ов ать функциюCOUNT со з в ез д оч кой.

SELECT COUNT(*)

FROM EXAM_MARKS;

А ргументы DISTINCT и ALL п оз в оляют, соотв етств енно, исключ атьи в ключ атьд убликаты обр абатыв а емых функцией COUNT з нач ений, п ри этом необход имо уч итыв ать, ч то п ри исп ольз ов а нии оп ции ALL з нач ения NULL в серав но нев ойд ут в ч исло п од сч итыв а емых з нач ений.

SELECT COUNT(DISTINCT SUBJ_ID)

FROM SUBJECT;

Пред ложение GROUP BYGROUP BY (ГРУППИРОВАТЬ ПО) п оз в оляет груп п ир ов атьз а п иси в п од множеств а , оп ред еляемыез нач ениями ка кого-либо п оля, и п рименятьа грегирующ иефункции уженеко в сем з ап исямта блицы,

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

44

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

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

След ует иметьв в ид у, ч то в п р ед ложении GROUP BY д олжны быть

или в ыражения, которые на в ыход е имеют ед инств енное з нач ение д ля кажд ой в ыв од имой груп п ы.

УП Р АЖ НЕ НИ Я

9. Н а п ишитез а п рос д ляп од сч ета колич еств а студ ентов , сд ав а в ших экз амен п о п ред мету обуч енияс ид ентификатором, рав ным20.

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

11.Н а п ишите з ап рос, который в ып олняет в ыборку д ля кажд ого студ ента

46

Е сли аргументом функции яв ляется ст о лбец, сод ержащ ий п устое з нач ение, то COUNT в ер нет ч исло стр ок, несод ер жа щ их п устыез нач ения, и к которымп рименимо оп ред еленноеуслов иеили груп п иров а ние.

Е сли бы механиз м NULL не был д оступ ен, то неп р именимые и отсутств ующ иез нач ения п ришлосьбы исключ а тьс п омощ ью конструкции

WHERE.

Пов ед ение функции COUNT(*) не з ав исит от п устых з нач ений. О на в оз в ратит общ ееколич еств о стр ок в та блице.

2.5.2. В л ия ние NULL-з начений в ф ункц ии AVG

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

Функция AVG в ыч исляет сред нее з нач ение в сех и зв ест ны х з нач ений множеств а элементов , то есть эта функция п од сч итыв ает сумму и звест ны х з нач ений и д елит еена колич еств о эт и х з нач ений, а нена общ ееколич еств о з нач ений, сред и котор ых могут бытьNULL-з нач ения. Е сли столбец состоит только из п устых з нач ений, то функцияAVG такжев оз в ратитNULL.

2.6.Рез ул ь т ат дей ст вия т рехз начны х усл овны х операт оров

У слов ныеоп ераторы п ри отсутств ии п устых з нач ений в оз в ра щ ают либо TRUE (и сти н а ), либо FALSE (ло ж ь). Е сли жев столбцеп рисутств уют п устые

логич еских оп ера торов NOT, AND и OR.

Опера тор NOT

47

О быч ный уна р ный оп ератор NOT обра щ а ет оценку TRUE в FALSE и наоборот. О д нако NOT NULL п о п режнему буд ет в оз в ра щ а тьп устоез на ч ение NULL. При э том след ует отлич атьслуч а й NOT NULL от услов ия IS NOT NULL, котороеяв ляется п ротив оп оложностью IS NULL, отд еляя из в естные з нач енияотнеиз в естных.

Опера тор AND

∙Е сли рез ультат д в ух услов ий, объед иненных оп ераторомAND, из в естен, то п рименяются п ра в ила булев ой логики, то естьп р и обоих утв ержд ениях TRUE состав ноеутв ержд ениетакжебуд ет TRUE. Е сли жехотябы од но из

д в ух утв ержд ений буд ет FALSE, то состав ноеутв ер жд ениебуд ет FALSE.

∙Е сли рез ультат од ного из утв ержд ений неиз в естен, а д ругой оценив ается

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

∙Е сли рез ультат од ного из утв ержд ений неиз в естен, а д ругой оценив ается как FALSE, итогов ый рез ультат буд ет FALSE.

TRUE, если оба утв ержд ения оценив а ются как FALSE, то соста в ное утв ержд ениебуд ет FALSE.

∙Е сли рез ультат од ного из утв ержд ений неиз в естен, а д ругой оценив ается как TRUE, итогов ый рез ультат буд ет TRUE.

∙Е сли рез ультат од ного из утв ержд ений неиз в естен, а д ругой оценив ается

П рим ечание.

Практич ески в о в сех остальных случ аях п устых з на ч ений след ует из бегать, так как п ри их налич ии сущ еств енно усложняется кор ректное п остр оение услов ий отбора, п рив од я иногд а к неп ред сказ уемым рез ульта там в ыборки. Д ляинд ика ции жеотсутств ующ их, неп рименимых или п о какой-то п рич ине неиз в естных д а нных можно исп ольз ов ать з нач ения п о умолч анию, уста на в лив а емыез а ранее (на п ример, с п омощ ью команд ы CREATE TABLE

(раз д ел 4.1).

2.7.Упоря дочение вы ходны х пол ей (ORDER BY)

сортир ов ки д ляка жд ого из столбцов . По умолч анию п р инята в оз р астающ ая п ослед ов ательностьсортиров ки.

Тот жесп исок, но уп оряд оч енный в обратном п оряд ке, можно п олуч ить з ап росом:

SELECT *

FROM SUBJECT

ORDER BY SUBJ_NAME DESC;

М ожно уп ор яд оч ить в ыв од имый сп исок п ред метов обуч ения п о з нач ениямсеместр ов , а в нутри семестров – п о на именов а ниямп ред метов .

49

SELECT *

FROM SUBJECT

ORDER BY SEMESTR, SUBJ_NAME;

SELECT SUBJ_NAME, SEMESTR, MAX(HOUR)

FROM SUBJECT

GROUP BY SEMESTR, SUBJ_NAME

ORDER BY SEMESTR;

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

50

п оряд кеа) убыв а ния семестров и б) в оз ра стания отв од имых на п р ед мет ч асов . Полесеместра в в ыход ных д а нных д олжно бытьп ер в ым, з а ним д олжны след ов атьимяп ред мета обуч енияи ид ентификатор п ред мета.

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

2.8. В л ож енны е подз апросы

SQL п оз в оляет исп ольз ов атьод ни з а п росы в нутри д ругих з ап росов , то