Добавил:

Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.

Вуз:

Воронежский государственный университет

Предмет:

[НЕСОРТИРОВАННОЕ]

Файл:

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

.pdf

Скачиваний:

Добавлен:

21.05.2015

Размер:

917.91 Кб

Скачать

☆

<<< < Предыдущая 1 2 3 4 56 / 166 7 8 9 10 11 12 13 14 15 16 > Следующая >>>

След ует обратитьв нимание, ч то п рив ед енный в ыше з ап рос корректен

только в том случ ае,	если в	рез ультате в ып олнения указ анного			скобках
по д з а п роса в оз в р ащ а ется		ед и нст в енно е	зна чени е.	Е сли в рез ультате
в ып олнения п од з ап роса буд ет в оз в ра щ ено несколько з нач ений,					то	э тот
п од з а п рос буд ет ошибоч ным. В д анном п римере это				п р оиз ойд ет,	если в
таблице STUDENT	буд ет	несколько	з ап исей со	з нач ениями		п оля
SURNAME = ‘Петр о в’.

В некоторых случ аях д лягарантии п олуч енияед инств енного з нач енияв рез ультатев ып олненияп од з а п роса исп ольз уетсяDISTINCT. О д нимиз в ид ов

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

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

Д анные обо в сех	оценках (таблица EXAM_MARKS)	студ ентов из
Воронежа можно в ыбр атьс п омощ ьюслед ующ его з а п роса :
SELECT *
FROM EXAM_MARKS
WHERE STUDENT_ID IN
( SELECT STUDENT_ID
FROM STUDENT
WHERE CITY = ‘Во р о н еж ’);
Под з а п росы можно	п р именять в нутр и п ред ложения HAVING. Пусть
требуется оп ред елить	колич еств о п ред метов обуч ения	с оценкой,

п рев ыша ющ ей ср ед неез на ч ениеоценки студ ента с ид ентификатором301:

SELECT COUNT(DISTINCT SUBJ_ID), MARK

FROM EXAM_MARKS

GROUP BY MARK

HAVING MARK >

( SELECT AVG(MARK)

FROM EXAM_MARKS

WHERE STUDENT_ID = 301);

2.9. Ф орм ирование свя з анны х подз апросов

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

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

П ример: в ыбратьсв ед ения обо в сех п ред метах обуч ения, п о котор ым п ров од илсяэкз амен 20 янв аря1999 г.

SELECT *

FROM SUBJECT SU

WHERE ‘20/01/1999’ IN

( SELECT EXAM_DATE FROM EXAM_MARKS EX

WHERE SU.SUBJ_ID = EX.SUBJ_ID);

В некоторых СУ Б Д д ля в ып олнения		этого	з ап р оса, в оз можно,
п отребуется п реобраз ов а ние з на ч ения д аты		в	симв ольный тип .	В
п рив ед енном з а п росеSU и EX яв ляются п сев д онимами (а лиаса ми), то есть
сп ециа льно в в од имыми	имена ми, которые могут быть исп ольз ов аны			в
д анном з а п росе в место	настоящ их имен. В	п рив ед енном п римере они

исп ольз уютсяв место имен та блиц SUBJECT и EXAM_MARKS. .

Э ту жез ад ач у можно решитьс п омощ ьюоп ера ции соед инениятаблиц:

SELECT DISTINCT SU.SUBJ_ID, SUBJ_NAME, HOUR, SEMESTER

FROM SUBJECT FIRST, EXAM_MARKS SECOND

WHERE FIRST.SUBJ_ID = SECOND.SUBJ_ID AND SECOND.EXAM_DATE = ’20/01/1999’;

В этомв ыражении алиасами таблиц яв ляютсяимена FIRST и SECOND.

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

которомони уч атся.

SELECT DISTINCT STUDENT_ID, SURNAME, STIPEND

FROM STUDENT E1

WHERE STIPEND >

(SELECT AVG(STIPEND)

FROM STUDENT E2

WHERE E1.KURS = E2.KURS);

Тот жерез ультат можно п олуч итьс п омощ ьюслед ующ его з ап роса :

SELECT DISTINCT STUDENT_ID, SURNAME, STIPEND FROM STUDENT E1,

(SELECT KURS, AVG(STIPEND) AS AVG_STIPEND

FROM STUDENT E2 GROUP BY E2.KURS) E3

WHERE E1.STIPEND > AVG_STIPEND AND E1.KURS=E3.KURS;

О братитев нима ние– в торой з ап р ос буд ет в ып олнен гораз д о быстрее.

Д ело в том, ч то	в	п ерв ом в ариа нте з а п роса агрегирующ а я функция AVG
в ып олняется над	таблицей, указ анной в				п од з а п росе,	д ля ка ж д о й строки
в нешнего з а п роса.		В	д ругом	в а риа нте в торая таблица (а лиас E2)
обраба тыв ается	агрегирующ ей			функцией	од ин р аз ,	в рез ульта те ч его
формируется в сп омогательная таблица (в					з ап росеона	имеет алиас E3), со
строками которой з атем соед иняются строки п ер в ой та блицы (а лиас E1).
След ует иметьв	в ид у,		ч то реальное в ремя в ып олнения з ап роса в большой

степ ени з ав исит от оп тимиз атор а з ап росов конкретной СУ Б Д .

2.10. С вя занны е подз апросы в HAVING

В	раз д еле 2.4 указ ыв а лось, ч то	п ред ложение GROUP BY п оз в оляет
груп п ир ов атьв ыв од имыеSELECT-з ап росомз ап иси п о з нач ению некоторого
п оля.	Исп ольз ов а ние п ред ложения	HAVING	п оз в оляет		п ри	в ыв од е
осущ еств лять фильтрацию таких груп п . Пред икат				п ред ложения HAVING
оценив а ется не д ля кажд ой строки		рез ультата,	а	д ля кажд ой		груп п ы
в ыход ных з а п исей, сформиров а нной		п ред ложением GROUP BY в нешнего
з ап роса.
Пусть, на п ример, необход имо п о д а нным из та блицы					EXAM_MARKS
оп ред елитьсумму п олуч енных студ ентами оценок (з нач ений п оля MARK),
сгруп п иров а в з на ч енияоценок п о д атам экз а менов			и исключ ив тед ни, когд а

ч исло студ ентов , сд а в а в ших в теч ениед няэкз амены, было меньше10.

SELECT EXAM_DATE, SUM(MARK)

FROM EXAM_MARKS A

GROUP BY EXAM_DATE

	54
HAVING 10 <
( SELECT COUNT(MARK)
	FROM EXAM_MARKS B
	WHERE A.EXAM_DATE = B.EXAM_DATE);
Под з а п рос	в ыч исляет колич еств о	строк с од ной	и той же д атой,
сов п а д ающ ей	с д атой, д ля которой	сформиров ана	оч еред на я груп п а
основ ного з ап роса.

УП Р АЖ НЕ НИ Я

26.Н а п ишитез а п рос с п од з а п росом д ляп олуч енияд а нных обо в сех оценка х студ ента с фамилией “Ива н о в”. Пред п оложим, ч то его п ерсональный номер неиз в естен. Всегд а ли та кой з ап рос буд ет корректным?

27.Н а п ишите з ап рос, в ыбира ющ ий д а нные об именах в сех студ ентов , имеющ их п о п ред мету c ид ентифика тором 101 балл в ыше общ его сред него ба лла.

28.Н а п ишите з ап рос, который в ып олняет в ыборку имен в сех студ ентов , имеющ их п о п ред мету c ид ентификатором 102 балл ниже общ его сред него ба лла.

29.Н а п ишите	з ап рос,	в ып олняющ ий		в ыв од	колич еств а	п ред метов , п о
которым	экз аменов ался ка жд ый			студ ент,	сд а в а в ший	более 20-ти
п ред метов .
30.Н а п ишите команд у		SELECT,	исп ольз ующ ую св яз анные п од з ап росы и
в ып олняющ ую в ыв од имен и			ид ентификаторов студ ентов , у которых

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

31.Н а п ишитез а п рос, котор ый п оз в оляет в ыв ести имена и ид ентификаторы


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

с исп ольз ов а нием соед инения, а д ругой – с исп ольз ов анием св яз а нного

п од з ап роса.

2.11. И спол ь з ование операт ора EXISTS


Исп ольз уемый	в SQL оп ератор EXISTS (СУЩЕСТВУЕТ) генер ирует
з нач ение и сти н а	или ло ж ь, п од обно		булев у в ыражению.		Исп ольз уя
п од з а п росы в кач еств е аргумента, этот			оп ератор	оценив ает	р ез ультат
в ып олнения п од з ап роса как		истинный, если этот		п од з ап рос	генерирует
в ыход ные д а нные,	то есть в	случ а е сущест во в а ни я (в оз в р ата) хотя бы
од ного найд енного з нач ения.		В п ротив ном случ ае р ез ультат п од з ап роса –
ложный. О п ератор	EXISTS не может		п ринимать з нач ение unknown

(неизвестно).

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

SELECT DISTINCT STUDENT_ID

FROM EXAM_MARKS A

WHERE EXISTS ( SELECT *

FROM EXAM_MARKS B

WHERE MARK < 3

AND B.STUDENT_ID=A.STUDENT_ID);

При исп ольз ов а нии св яз анных п од з ап росов п ред ложение EXISTS анализ ирует ка жд ую стр оку таблицы, на которую имеется ссылка в о в нешнем з ап росе. Г ла в ный з а п рос п олуч ает стр оки-ка нд ид аты на п ров ерку услов ия. Д ля кажд ой строки-ка нд ид ата в ып олняется п од з ап рос. К ак только

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

экз а мены п о которымсд а в а лисьнеод ним, а несколькими студ ентами:

SELECT DISTINCT SUBJ_ID

FROM EXAM_MARKS A

WHERE EXISTS ( SELECT *

FROM EXAM_MARKS B

WHERE A.SUBJ_ID = B.SUBJ_ID

AND A.STUDENT_ID < > B.STUDENT_ID);

Ч асто EXISTS п р именяетсяс оп ера тором NOT (п о-русски NOT EXISTS


интерп ретируется, как “не сущест вует		… ”).		Е сли п ред ыд ущ ий з ап рос
сформулиров атьслед ующ им образ ом – найти			ид ентифика торы п ред метов
обуч ения,	которые сд ав алисьод ним и только		од ним студ ентом (д ругими
слов а ми,	д ля которых не сущ еств ует д р угого			сд ав ав шего студ ента), то
д оста точ но п росто п остав итьNOT п еред EXISTS.
След ует иметь в в ид у, ч то в п од з ап росе,				указ ыв а емом в оп ера торе
EXISTS, нельзяи спо льзо ва т ь а гр еги р ующи е ф ункци и .
Воз можности п рименения в ложенных		з ап росов в есьма раз нообраз ны.

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

SELECT *

FROM STUDENT FIRST

WHERE EXISTS (SELECT SUBJ_ID

FROM EXAM_MARKS SECOND

GROUP BY SUBJ_ID

HAVING COUNT(SUBJ_ID) >1

WHERE FIRST.STUDENT_ID = SECOND.STUDENT_ID);

УП Р АЖ НЕ НИ Я
33.Н а п ишите з ап рос	с EXISTS, п оз в оляющ ий в ыв ести д анные обо в сех
студ ентах обуч а ющ ихсяв в уз а х, имеющ их рейтингв ыше300.
34.Н а п ишитеп ред ыд ущ ий з ап рос, исп ольз уясоед инения.
35.Н а п ишитез а п рос с EXISTS, в ыбирающ ий св ед енияобо в сех студ ента х,
д ля которых в	том же город е, гд е жив ет студ ент, сущ еств уют

унив ерситеты, в котор ых он неуч ится.

36.Н а п ишитез ап рос, в ыбира ющ ий из таблицы SUBJECT д анныео наз в а ниях п ред метов обуч ения, экз амены п о которым сд а ны более ч ем од ним студ ентом.

2.12.О перат оры сравнения см нож ест вом значений IN, ANY, ALL

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

Ра в но любому из з нач ений, п олуч енных в о в нутреннем

NOT IN

з ап росе.

Не р а вно

ни

од ному

из

з на ч ений, п олуч енных в о

= ANY

в нутреннемз а п росе.

То же, ч то и

. Соотв етств ует логич ескому оп ера тору

OR.

> ANY,

> = ANY

Бо льш е,

чем

(либо

бо льш е

и ли

р а вно )

любое

п олуч енноеч исло. Э кв ив алентно > или > = д лясамого

меньшего п олуч енного ч исла.

< ANY,

< = ANY

М еньш е,

чем

(либо

м еньш е

и ли

р а вно )

любое

п олуч енное ч исло. Э кв ив а лент < или < = д ля самого

= ALL

большего п олуч енного ч исла.

Ра в но в сем

п олуч енным

з нач ениям.

Э кв ив а лентно

> ALL,

> = ALL

логич ескому оп ератору AND.

Бо льш е, чем

(либо бо льш е

и ли

р а вно )

в сеп олуч енные

ч исла. Э кв ив а лент >

или

> =

д ля са мого большего

< ALL,

< = ALL

п олуч енного ч исла .

М еньш е, чем

(либо м еньш е и ли

р а в но )

в сеп олуч енные

ч исла. Э кв ив а лентно

или

= са мого меньшего

п олуч енного ч исла .

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

П рим еры з апросов сиспол ь з ованием приведенны х операт оров.

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

	58
SELECT *
FROM STUDENT S
WHERE CITY = ANY
( SELECT CITY
FROM UNIVERSITY U
WHERE U.UNIV_ID = S.UNIV_ID);
Д ругой в а риа нт этого з ап роса
SELECT *
FROM STUDENT S
WHERE CITY IN
(SELECT CITY
FROM UNIVERSITY U
WHERE U.UNIV_ID = S.UNIV_ID);
Выборка д анных	об ид ентификатор ах студ ентов , у которых оценки
п рев осход ят в елич ину,	п о кра йней мере, од ной из оценок, п олуч енных ими
же6 октября1999 год а.

SELECT DISTINCT STUDENT_ID

FROM EXAM_MARKS

WHERE MARK > ANY (SELECT MARK

FROM EXAM_MARKS

WHERE EXAM_DATE = ‘06/10/1999’);

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

в сем з нач ениям	обыч ном смысле. В SQL в ыра жение < > ALL реально
оз нач ает не р а вно ни о д но м уиз		рез ульта тов	п од з ап р оса.
Под з а п рос,	в ыбирающ ий	д анные о	наз в а ниях в сех унив ерситетов с

рейтингомболеев ысоким, ч емрейтинглюбого унив ер ситета в Воронеже:

SELECT *

FROM UNIVERSITY

WHERE RATING > ALL

( SELECT RATING

FROM UNIVERSITY

WHERE CITY = ‘Во р о н еж ’);

В э том з а п росе	в место	ALL можно также исп ольз ов ать ANY.
(Проана лиз ируйте, ка к	в этом	случ ае из менится смысл п рив ед енного
з ап роса?)

SELECT *

FROM UNIVERSITY

WHERE NOT RATING > ANY

( SELECT RATING

FROM UNIVERSITY

WHERE CITY = ‘Во р о н еж ’);

2.13.О собенност и прим енения операт оров ANY, ALL, EXISTS при обработ ке пуст ы х з начений (NULL)

Н еобход имо иметьв в ид у, ч то п ри обра боткеNULL-з нач ений след ует уч итыв а тьраз лич иереакции на них оп ераторов EXISTS, ANY и ALL.

К огд а п ра в ильный п од з ап р ос негенерирует никаких в ыход ных д а нных, оп ератор ALL ав тома тич ески п ринима ет з нач ениеи сти н а , а оп ератор ANY – з нач ениело ж ь.

Зап рос

SELECT *

FROM UNIVERSITY

WHERE RATING > ANY ( SELECT RATING

FROM UNIVERSITY

WHERE CITY = ‘New York’);

негенерирует в ыход ных д анных (п од раз умев ается, ч то в баз енет д анных об унив ерситета х из город а New York), в то в ремякак з ап рос

		60
SELECT *
FROM UNIVERSITY
WHERE RATING > ALL
	( SELECT RATING
	FROM UNIVERSITY
	WHERE CITY = ‘New York’);
п олностьюв осп роиз в ед ет таблицу UNIVERSITY.
Исп ольз ов а ние NULL-з на ч ений соз д ает			оп ред еленные п роблемы	д ля
рассма трив аемых оп ер аторов . К огд а в SQL срав нив а ютсяд в а з нач ения, од но
из которых	NULL-з на ч ение, рез ультат п ринимает з на ч ение UNKNOWN
(неиз в естно).	Пред икат	UNKNOWN, также ка к и FALSE-п ред икат, соз д ает
ситуа цию, когд а строка		не в ключ ается в	состав в ыход ных д а нных,	но

рез ультат п ри этомбуд ет раз лич ен д ляраз ных тип ов з ап росов , в з ав исимости от исп ольз ов анияв них ALL или ANY в место EXISTS. Ра ссмотримв кач еств е п римера д в е реализ ации з а п р оса : на йти в се д а нные об унив ерситета х, рейтингкоторых меньшер ейтинга любого унив ерситета в М оскв е.

1)SELECT *

FROM UNIVERSITY

WHERE RATING < ANY (SELECT RATING

FROM UNIVERSITY

WHERE CITY = ‘М о сква ’);

2)SELECT *

FROM UNIVERSITY A

WHERE NOT EXISTS

(SELECT *

FROM UNIVERSITY B

WHERE A.RATING >= B.RATING

AND B.CITY = ‘М о сква ’);

При отсутств ии в таблица х NULL оба эти з ап роса в ед ут себясов ершенно

од инаков о.	Пусть теп ерь в таблице UNIVERSITY есть стр ока с NULL-
з нач ениями	столбцеRATING. В в ерсии з ап р оса c ANY в основ ном з ап росе,

когд а в ыбирается п оле RATING с NULL, п ред икат п ринима ет з на ч ение UNKNOWN и строка нев ключ аетсяв состав в ыход ных д а нных. Во в торомже

в ариа нте з а п роса, когд а NOT EXISTS	в ыбирает эту стр оку в	основ ном
з ап росе, NULL-з на ч ение исп ольз уется в	п ред икате п од з ап роса,	п рисв а ив ая

<<< < Предыдущая 1 2 3 4 56 / 166 7 8 9 10 11 12 13 14 15 16 > Следующая >>>

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

#
22.11.201830.11 Кб6sotsiologia(2).docx
#
21.05.2015485.86 Кб9sotsiologia.rtf
#
19.03.2016267.64 Кб12speckurs.pdf
#
21.05.20155.14 Mб156spez_fiz_pr_zachita.pdf
#
20.05.2015137.2 Кб85SPSE2014.docx
#
21.05.2015917.91 Кб34SQL в вопросах и задачах.pdf
#
21.05.20155.12 Mб12srrf2014web.pdf
#
20.05.2015318.46 Кб8strany_i_stolitsy_blank_dlya_studentov.doc
#
20.05.2015120.59 Кб31Sudebnaya_sistema_v_RF (4).docx
#
20.05.2015126.73 Кб56Sudebnaya_sistema_v_RF+.docx
#
19.03.2016203.58 Кб39Sudebnaya_sistema_v_RF_5 (4).docx