SQL в вопросах и задачах
.pdf21
Таблица 1.5. EXAM_MARKS (Экзаменационные оценки)
EXAM_ID |
STUDENT_ID |
SUBJ_ID |
MARK |
EXAM_DATE |
145 |
12 |
10 |
5 |
12/01/2000 |
34 |
32 |
10 |
4 |
23/01/2000 |
75 |
55 |
10 |
5 |
05/01/2000 |
238 |
12 |
22 |
3 |
17/06/1999 |
639 |
55 |
22 |
NULL |
22/06/1999 |
43 |
6 |
22 |
4 |
18/01/2000 |
… … |
… … |
… … |
… … |
… … |
EXAM_ID – идентификатор экзамена, STUDENT_ID – идентификатор студента, SUBJ_ID – идентификатор предмета обучения,
MARK – экзаменационная оценка, EXAM_DATE – дата экзамена.
Таблица 1.6. SUBJ_LECT (Учебные дисциплины преподавателей)
LECTURER _ID |
SUBJ_ID |
24 |
24 |
46 |
46 |
74 |
74 |
108 |
108 |
276 |
276 |
328 |
328 |
… … |
… … |
LECTURER_ID – идентификатор преподавателя, SUBJ_ID – идентификатор предмета обучения.
В О П Р О С Ы
1.К акиеп оляп рив ед енных таблиц яв ляютсяп ерв ич ными ключ а ми?
2.К акиед анныехра нятсяв столбце2 в таблице“Пр ед мет обуч ения”?
3.К ак п о-д ругому наз ыв а етсястрока? Столбец?
4.Поч ему мы неможемз ап роситьд ляп росмотра п ер в ыеп ятьстрок?
22
2. Вы б о р ка да н н ы х (о пер а то р SELECT)
2.1. П рост ей ш ие SELECT-з апросы
О п ера тор SELECT (ВЫБРАТЬ) яз ыка |
SQL яв ляется самым в а жным и |
|
самым ч асто |
исп ольз уемым оп ератор ом. |
О н п ред наз нач ен д ля вы бо р ки |
информации |
из таблиц ба з ы д анных. У п р ощ енный синтаксис оп ера тора |
SELECT в ыгляд ит след ующ имобр аз ом.
SELECT [DISTINCT] <спи со ка тр и б уто в>
FROM <спи со кта б ли ц> [WHERE <усло ви евы б о р ки>]
[ORDER BY <спи со ка тр и б уто в>]
[GROUP BY <спи со ка тр и б уто в>] [HAVING <усло ви е>]
[UNION <вы р а ж ен и ес о пер а то р о м SELECT>];
Вкв ад ра тных скобках ука з аны элементы, которыемогут отсутств ов атьв
зап росе.
К люч ев ое |
слов о SELECT сообщ ает баз е д анных, |
ч то |
д а нное |
|
п ред ложение яв ляется з ап росом на и звлечени е информации. |
После слов а |
|||
SELECT ч ерез |
з ап ятую п ер еч исляются |
на и м ено ва ни я по лей |
(сп исок |
|
атрибутов ), сод ержимоекоторых з ап рашив ается. |
|
|
||
О бяз ательным ключ ев ым слов ом в |
п р ед ложении-з ап росе |
SELECT |
яв ляется слов о FROM (ИЗ). За ключ ев ым слов ом FROM указ ыв ается сп исок раз д еленных з ап ятыми имен таблиц, из котор ых из в лекаетсяинформа ция.
Н а п ример,
SELECT NAME, SURNAME
FROM STUDENT;
Л юбой SQL-з ап рос д олжен з ака нч ив аться симв олом “;” (т о чка с за пят о й).
23
Прив ед енный з а п рос осущ еств ляет в ыборку в сех з на ч ений п олей NAME
иSURNAME из таблицы STUDENT.
Его рез ульта томяв ляетсятаблица след ующ его в ид а :
|
NAME |
|
SURNAME |
|
|
|
Ив ан |
|
Ив анов |
|
|
|
Петр |
|
Петр ов |
|
|
|
Вадим |
|
Сид ор ов |
|
|
|
Б орис |
|
К узнецов |
|
|
|
О льга |
|
Зайцев а |
|
|
|
А нд рей |
|
Пав лов |
|
|
|
Пав ел |
|
К отов |
|
|
|
А ртем |
|
Л укин |
|
|
|
А нтон |
|
Петр ов |
|
|
|
Вадим |
|
Б елкин |
|
|
|
… … |
|
… … |
|
|
Поряд ок след ов а ния столбцов в |
этой таблице соотв етств ует п оряд ку |
||||
п олей NAME и SURNAME, ука з анному |
з ап росе, а неих п оряд ку в о в ход ной |
||||
таблицеSTUDENT. |
|
|
|
Е сли необход имо в ыв ести з нач ения всех столбцов та блицы, то можно в место п ереч исленияих имен исп ольз ов а тьсимв ол “* ” (з в ез д оч ка ).
SELECT *
FROM STUDENT;
В д а нном случ аев рез ульта тев ып олнения з ап роса буд ет п олуч ена в ся таблица STUDENT.
Е щ ера з обра тим в нимание на то, ч то п олуч аемые в рез ультатеSQL- з ап роса таблицы не в п олной мере отв еч а ют оп ред елению реляционного отношения. В ч астности, в них могут оказ аться кортежи с од инаков ыми з нач ениями а трибутов .
Н а п ример, з ап рос “Получ итьсп исок на з в аний город ов , гд еп рожив а ют студ енты, св ед ения о которых наход ятся в та блице STUDENT”, можно з ап исатьв след ующ емв ид е
SELECT CITY FROM STUDENT;
Е го рез ультатомбуд еттаблица
24
CITY
О рел К урск
Москв а
Брянск Л ип ецк
В оронеж
Белгор од
В оронеж
NULL
Воронеж
……
Вид но, ч то в таблице в стреч аются од инаков ые строки (в ыд елены жирнымшрифтом).
Д ляисключ енияиз рез ультата SELECT-з ап роса п ов торяющ ихсяз а п исей исп ольз уетсяключ ев оеслов о DISTINCT (ОТЛИЧНЫЙ). Е сли з а п рос SELECT из в лекает множеств о п олей, то DISTINCT и сключа ет д убликаты строк, в которых з на ч ениявсех в ыбр анных п олей ид ентич ны.
Зап рос “О п ред елитьсп исок наз в аний р а зли чны х город ов , гд еп рожив а ют студ енты, св ед ения о которых наход ятся в та блице STUDENT”, можно з ап исатьв след ующ емв ид е.
SELECT DISTINCT CITY
FROM STUDENT;
В рез ультатеп олуч имтаблицу, в которой д убликаты строк исключ ены.
CITY
О рел К урск
Москв а
Брянск Л ип ецк Воронеж
Белгор од
NULL
……
К люч ев ое слов о ALL |
(ВСЕ), в отлич ие от DISTINCT, |
оказ ыв ает |
п ротив оп оложноед ейств ие, |
то есть п ри его исп ольз ов а нии п ов торяющ иеся |
|
строки включа ют ся в состав в ыход ных д а нных. Режим, |
з а д ав аемый |
25 |
|
|
ключ ев ым слов ом ALL, д ейств ует п о умолч а нию, п оэтому |
реальных |
|
з ап росах д ляэтих целей оно п рактич ески неисп ольз уется. |
|
|
Исп ольз ов а ние в оп ер аторе SELECT п ред ложения, оп ред еляемого |
||
ключ ев ым слов ом WHERE (ГДЕ), п оз в оляет з ад а в ать в ыражение услов ия |
||
(п ред икат), п ринимающ еез нач ениеи с т и на или л о ж ь |
д ля з нач ений п олей |
|
строк та блиц, к которымобращ аетсяоп ератор SELECT. |
Пред ложениеWHERE |
оп ред еляет, к а к ие ст р ок и ука за нны х т а бли ц д о лж ны быт ь в ы бр а ны . В
таблицу, яв ляющ уюся рез ультатом з ап р оса, в ключ аются только те строки, д ля которых услов ие (п ред ика т), ука з анное в п ред ложении WHERE, п ринимает з нач ениеи с т и на .
П ример.
Н а п исатьз ап рос, в ып олняющ ий в ыборку имен (NAME) в сех студ ентов с фамилией (SURNAME) Петр о в, св ед ения о которых наход ятся в та блице
STUDENT.
SELECT SURNAME, NAME
FROM STUDENT
WHERE SURNAME = ‘Петр о в’;
Рез ульта томэ того з ап роса буд ет та блица :
|
SURNAME |
NAME |
|
|
|
Петров |
Петр |
|
|
|
Петров |
А нтон |
|
|
В з а д ав аемых в |
п ред ложении WHERE услов иях могут исп ольз ов аться |
|||
оп ерации сра в нения, |
оп р ед еляемыеслед ующ ими оп ератор ами: = (ра в но), > |
(больше), < (меньше), >= (большеили ра в но), <= (меньшеили рав но), <> (не рав но), а такжелогич ескиеоп ераторы AND, OR и NOT.
Н а п ример, |
з ап рос д ля |
п олуч ения и м ен и |
ф а м и ли й студ ентов , |
обуч ающ ихся |
на т р ет ьем |
кур се и п олуч а ющ их |
стип енд ию (ра з мер |
стип енд ии бо льш е нуля) буд ет в ыгляд етьтакимобраз ом:
SELECT NAME, SURNAME
FROM STUDENT
WHERE KURS = 3 AND STIPEND > 0;
26
Рез ульта т в ып олненияэтого з ап роса имеет в ид :
SURNAME |
NAME |
Петров |
Петр |
Л укин |
А ртем |
УП Р АЖ НЕ НИ Я |
|
|
|
|
|
|
|
|
||
1. |
Н а п ишите |
з а п рос |
д ля в ыв од а |
ид ентификатора |
(номера) |
п ред мета |
||||
|
обуч ения, |
его наименов ания, семестра , в котором он ч итается, и |
||||||||
|
колич еств а отв од имых на него ч асов д ляв сех строк та блицы SUBJECT. |
|||||||||
2. |
Н а п ишите |
з а п рос, |
п оз в оляющ ий |
в ыв ести |
в се строки |
таблицы |
||||
|
EXAM_MARKS, в которых п р ед мет обуч ения имеет номер (SUBJ_ID), |
|||||||||
|
р ав ный 12. |
|
|
|
|
|
|
|
|
|
3. |
Н а п ишите з а п рос, |
в ыбира ющ ий |
в се д анные из |
таблицы |
STUDENT, |
|||||
|
р асп оложив |
столбцы таблицы в |
след ующ ем п оряд ке: KURS, |
SURNAME, |
||||||
|
NAME, STIPEND. |
SELECT, который |
|
|
|
|
|
|||
4. |
Н а п ишите з ап рос |
в ып олняет |
в ыв од |
наименов аний |
||||||
|
п ред метов |
обуч ения (SUBJ_NAME) и |
след ом з а |
ним колич еств а ч а сов |
||||||
|
(HOUR) д ля кажд ого |
п ред мета |
обуч ения (SUBJECT) в |
4-м семестре |
||||||
|
(SEMESTR). |
|
|
|
|
|
|
|
|
|
5. |
Н а п ишите з ап рос, |
п оз в оляющ ий п олуч ить из |
та блицы |
EXAM_MARKS |
||||||
|
з нач ения столбца |
MARK (э кз амена ционна я оценка) |
д ля в сех студ ентов , |
исключ ив из сп иска п ов торениеод ина ков ых строк.
6.Н а п ишитез а п рос, который в ып олняет в ыв од сп иска фа милий студ ентов , обуч ающ ихсяна третьеми болеестарших курсах.
7. |
Н а п ишитез ап рос, в ыбирающ ий д анныео фа милии, имени и номерекурса |
|
|
д лястуд ентов , п олуч ающ их стип енд июбольше140. |
|
8. |
Н а п ишитез а п рос, в ып олняющ ий в ыборку из таблицы SUBJECT наз в аний |
|
|
в сех п ред метов обуч ения, на которыеотв од итсяболее30 ч асов . |
|
9. |
Н а п ишите з а п рос, который в ып олняет |
в ыв од сп иска унив ерситетов , |
|
р ейтингкоторых п р ев ыша ет 300 баллов . |
|
10.Н а п ишите з ап рос к таблице STUDENT |
д ля в ыв од а сп иска фамилий |
27
(SURNAME), имен (NAME) и номера курса (KURS) в сех студ ентов со стип енд ией большей или ра в ной 100, и жив ущ их в Воронеже.
11.К акиед а нныебуд ут п олуч ены в рез ультатев ып олненияз ап р оса?
SELECT *
FROM STUDENT
WHERE (STIPEND < 100 OR
NOT (BIRTHDAY >= ‘10/03/1980’
AND STUDENT_ID > 1003 ));
12.К акиед а нныебуд ут п олуч ены в рез ультатев ып олненияз ап р оса?
SELECT *
FROM STUDENT
WHERE NOT ((BIRTHDAY = ‘10/03/1980’OR STIPEND > 100) AND STUDENT_ID > = 1003 );
28
2.2. О перат оры IN, BETWEEN, LIKE, IS NULL
При з ад ании логич еского услов ия в п ред ложении WHERE могут быть исп ольз ов аны оп ера торы IN, BETWEEN, LIKE, IS NULL.
О п ера торы IN (РАВЕН ЛЮБОМУ ИЗ СПИСКА) и NOT IN (НЕ РАВЕН НИ ОДНОМУ ИЗ СПИСКА) исп ольз уются д ля срав нения п ров еряемого з на ч ения
п оля с з ад анным сп иском. Э тот сп исок з на ч ений ука з ыв ается в |
скобках |
сп рав а от оп ератора IN. |
|
Постр оенный с исп ольз ов анием IN п р ед икат (услов ие) |
сч итается |
истинным, если з нач ениеп оля, имякоторого ука з ано слев а от IN, со впа д а ет (п од р аз умев аетсяточ ноесов п ад ение) с од ним из з на ч ений, п ер еч исленных в сп иске, указ анномв скобках сп ра в а отIN.
Пред икат, п остроенный с исп ольз ов анием NOT IN, сч итаетсяистинным, если з нач ениеп оля, имякоторого указ а но слев а от NOT IN, не со впа д а ет ни с од нимиз з нач ений, п ереч исленных в сп иске, указ анномв скобка х сп рав а от
NOT IN.
П римеры.
Получ ить из таблицы EXAM_MARKS св ед ения о студ ента х, и м еющи х экз а менационныеоценки только 4 и 5.
SELECT *
FROM EXAM_MARKS
WHERE MARK IN (4, 5);
Получ итьсв ед енияо студ ентах, не и м ею щи х ни од ной э кз аменационной оценки, рав ной 4 и 5.
SELECT *
FROM EXAM_MARKS
WHERE MARK NOT IN (4, 5);
О п ера тор BETWEEN исп ольз уется д ля п ров ерки услов ия в хожд ения з нач енияп оляв з ад анный интерв ал, то естьв место сп иска з нач ений атр ибута этотоп ератор з ад ает границы его из менения.
Н а п ример, з ап рос, в ып олняющ ий в ыв од з ап исей о п ред метах обуч ения, колич еств о ч асов , отв од имых на которые, лежит в п ред елах межд у 30 и 40,
29
имеет в ид :
SELECT *
FROM SUBJECT
WHERE HOUR BETWEEN 30 AND 40;
Г ранич ные з на ч ения, |
в д анном случ ае з нач ения 30 и 40, |
вхо д ят в о |
|
множеств о з на ч ений, с |
которыми п роиз в од ится |
сра в нение. |
О п ер атор |
BETWEEN может исп ольз ов а ться как д ля ч ислов ых, |
так и д ля симв ольных |
||
тип ов п олей. |
|
|
|
О п ера тор LIKE п рименим только к симв ольным п олям тип а CHAR или VARCHAR (см. раз д ел 1.5, Т ип ы д анных SQL). Э тот оп ера тор осущ еств ляет
п росмотр |
строков ых з нач ений п олей с |
целью оп ред еления, в ход ит |
ли |
|
з ад а нная в |
оп ера торе LIKE п од строка |
(образ ец п оиска) в |
симв ольную |
|
строку, яв ляющ уюсяз нач ениемп р ов еряемого п оля. |
|
|
||
Д ля того, ч тобы осущ еств лять в ыборку строков ых |
з нач ений |
п о |
з ад а нному обра з цу п од строки, можно п рименятьшаблон искомого образ ца строки, исп ольз ующ ий след ующ иесимв олы:
∙ |
симв ол |
п од ч еркив ания “_”, указ анный в |
шаблоне образ ца, оп ред еляет |
|
в оз можностьна лич ияв ука з анномместео д но го любо го симв ола, |
||
∙ |
симв ол |
“%” д оп ускает п рисутств ие в |
указ анном месте п ров еряемой |
стр оки п ослед ов ательности любых симв олов п роиз в ольной д лины.
П ример. |
|
Н а п исать з ап р ос, в ыбира ющ ий из таблицы STUDENT |
св ед ения о |
студ ентах, у которых фамилии на ч ина ютсяна букв у “Р”. |
|
SELECT * |
|
FROM STUDENT |
|
WHERE SURNAME LIKE ‘Р %’; |
|
В случ а е в оз никнов ения необход имости в ключ ения в |
образ ец д ля |
срав нения самих симв олов “_” и “%” п р именяют, так на з ыв аемые escapeсимв олы. Е сли escape-симв ол п ред шеств ует з на ку “_” и “%”, то эти з наки буд ут интерп ретиров аться букв а льно. Н а п ример, можно з а д ать образ ец п оиска с п омощ ьюслед ующ его в ыражения
LIKE ‘_\_Р ’ ESCAPE ‘\’.
|
30 |
В этом в ыр ажении симв ол |
‘\’ с п омощ ью ключ ев ого слов а ESCAPE |
объяв ляется escape-симв олом. |
Перв ый симв ол “_” в з ад анном ша блоне |
п оиска ‘_\_Р ’ буд ет соотв етств ов ать, ка к и ранее, любому набору симв олов в п ров ер яемой строке. О д на ко в торой симв ол “_”, след ующ ий п ослесимв ола ‘\’, объяв ленного escape-симв олом, ужебуд ет интерп ретиров а тьсябукв а льно как обыч ный симв ол, та к жекак и симв ол Р в з а д анномшаблоне.
|
О бращ аем в а шев нима ние на |
то, |
ч то ра ссмотренные в ышеоп ераторы |
|||||
срав нения“=, <, >, <=, >=, <>” и оп ераторы IN, BETWEEN и LIKE ни в коем |
||||||||
случ а енельз я исп ольз ов атьд ля п ров ерки сод ержимого п оля на |
налич ие в |
|||||||
нем п устого з нач ения NULL (см. раз д ел 1.5, Тип ы д а нных SQL). Д ля этих |
||||||||
целей сп ециально п ред на з нач ены оп ера торы IS NULL (ЯВЛЯЕТСЯ ПУСТЫМ) |
||||||||
и IS NOT NULL (ЯВЛЯЕТСЯ НЕ ПУСТЫМ). |
|
|
|
|||||
УП Р АЖ НЕ НИ Я |
|
|
|
|
|
|
|
|
1. |
Н а п ишите з ап рос, |
в ып олняющ ий |
в ыв од наход ящ ихся |
в |
та блице |
|||
|
EXAM_MARKS номеров п ред метов |
обуч ения, |
экз амены |
п о |
которым |
|||
|
сд ав алисьмежд у 10 и 20 янв аря1999 год а. |
|
|
|
||||
2. |
Н а п ишите з ап рос, в ыбира ющ ий |
д анные обо в сех п ред метах обуч ения, |
||||||
|
э кз амены п о которымсд аны |
студ ентами, имеющ ими ид ентификаторы 12 |
||||||
|
и 32. |
|
|
|
|
|
|
|
3. |
Н а п ишите з ап рос, |
который |
в ып олняет в ыв од |
на з в аний |
п ред метов |
обуч ения, нач ина ющ ихсяна букв у ‘И’.
4.Н а п ишитез а п рос, в ыбирающ ий св ед ения о студ ентах, у котор ых имена нач ина ютсяна букв ы ‘И’или ‘С’.
5. |
Н а п ишите з а п рос д ля в ыбора из |
таблицы |
EXAM_MARKS з а п исей, д ля |
|
которых отсутств уют з нач енияоценок (п олеMARK). |
||
6. |
Н а п ишите з а п рос, в ып олняющ ий |
в ыв од |
из таблицы EXAM_MARKS |
|
з ап исей, д лякоторых в п олеMARK п ростав лены з нач енияоценок. |