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

81

3.2. И спол ь з ование подзапросов в INSERT

INSERT INTO STUDENT1

SELECT *

FROM STUDENT

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

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

INSERT INTO STUDENT1 SELECT *

FROM STUDENT

WHERE UNIV_ID IN (SELECT UNIV_ID

FROM UNIVERSITY

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

82

3.2.1.И спол ь з ование подз апросов, основанны х на т абл иц ах внеш них з апросов

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

INSERT INTO SSTUD

SELECT *

FROM STUDENT A

WHERE CITY IN (SELECT CITY

FROM UNIVERSITY B

WHERE A.UNIV_ID = B.UNIV_ID);

д ругой та блицес именемEXAM. Э то можно осущ еств итьс п омощ ьюз ап р оса

INSERT INTO EXAM

SELECT EXAM_ID, STUDENT_ID, SUBJ_ID, MARK, EXAM_DATE FROM EXAM_MARKS A

WHERE MARK =

(SELECT MAX(MARK)

FROM EXAM_MARKS B

WHERE A.EXAM_DATE = B.EXAM_DATE);

3.2.2. И спол ь з ование подз апросов сDELETE

Пустьфилиал унив ерситета в Н ью-Васюка х ликв ид иров а н, и требуется уд алитьиз та блицы STUDENT з ап иси о студ ентах, которыета муч ились. Э ту оп ерациюможно в ып олнитьс п омощ ьюз ап роса

DELETE

FROM STUDENT

WHERE UNIV_ID IN (SELECT UNIV_ID

FROM UNIVERSITY

WHERE CITY = ‘Н ью-Ва сюки’);

83

В п ред икате п ред ложения FROM (п од з ап роса) нельз я ссылаться на таблицу, из которой осущ еств ляется уд а ление. О д нако можно ссылаться на текущ ую строку из таблицы, яв ляющ уюся ка нд ид атом на уд аление, то есть на строку, котора яв настоящ еев ремяп ров еряетсяв основ номп ред ика те.

DELETE

FROM STUDENT

WHERE EXISTS

(SELECT *

FROM UNIVERSITY WHERE RATING = 401

AND STUDENT.UNIV_ID = UNIVERSITY.UNIV_ID);

DELETE

FROM STUDENT

WHERE 401 IN (SELECT RATING

FROM UNIVERSITY

WHERE STUDENT.UNIV_ID = UNIVERSITY.UNIV_ID);

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

84

которой наход ится эта оценка , требуется остав ить, то решениебуд ет иметь в ид :

DELETE

FROM STUDENT

WHERE STUDENT_ID IN (SELECT STUDENT_ID

FROM EXAM_MARKS A

WHERE MARK = (SELECT MIN(MARK)

FROM EXAM_MARKS B

WHERE A.EXAM_DATE = B.EXAM_DATE

AND 1 <

(SELECT COUNT(SUBJ_ID)

FROM EXAM_MARKS B

WHERE A.EXAM_DATE = B.EXAM_DATE)));

3.2.3. И спол ь з ование подз апросов сUPDATE

С п омощ ью кома нд ы UPDATE можно п рименятьп од з ап росы в любой форме, п риемлемой д лякома нд ы DELETE.

Н а п ример, исп ольз уясв яз анныеп од з ап росы, можно ув елич итьз на ч ение раз мер а стип енд ии на 20 в з ап исях студ ентов , сд ав ших экз амены на 4 и 5.

UPDATE STUDENT1

SET STIPEND = STIPEND + 20

WHERE 4 <= (SELECT MIN(MARK)

FROM EXAM_MARKS

WHERE EXAM_MARKS.STUDENT_ID = STUDENT1.STUDENT_ID);

Д ругой з ап рос: “У меньшитьв елич ину стип енд ии на 20 в сем студ ентам, п олуч ив шимна экз аменеминима льнуюоценку”.

UPDATE STUDENT1

SET STIPEND = STIPEND – 20 WHERE STUDENT_ID IN

(SELECT STUDENT_ID

FROM EXAM_MARKS A

85

WHERE MARK = (SELECT MIN(MARK)

FROM EXAM_MARKS B

WHERE A.EXAM_DATE = B.EXAM_DATE));

УП Р АЖ НЕ НИ Я

91.Пустьсущ еств ует таблица с именем STUDENT1, оп ред еления столбцов

93.Н а п ишитез а п рос, ув елич ив ающ ий д а нныео в елич инестип енд ии на 20% в семстуд ентам, у которых общ аясумма ба ллов п рев ыша ет з нач ение50.

86

4. С о зда н и ео б ъ екто в б а зы да н н ы х

4.1. С оз дание табл иц баз ы данны х

Соз д а ниеобъектов ба з ы д а нных осущ еств ляетсяс п омощ ьюоп ераторов яз ыка оп ред еленияд анных (DDL).

Таблицы баз ы д анных соз д а ютсяс п омощ ью кома нд ы CREATE TABLE. Э та команд а соз д а ет п устую таблицу, то есть таблицу, неимеющ ую строк. Знач ения в эту таблицу в в од ятся с п омощ ью кома нд ы INSERT. К оманд а CREATE TABLE оп ред еляет имя та блицы и множеств о п оименов анных столбцов в ука з анном п оряд ке. Д лякажд ого столбца д олжен бытьоп ред елен тип и раз мер. К ажд аясоз д ав аемая таблица д олжна иметь, п о крайней мер е, од ин столбец. Синтаксис кома нд ы CREATE TABLE имеет след ующ ий в ид .

на ци о на льны йи нст и т ут ст а нд а р т о в) (см. раз д ел 1.5.Тип ы д анных SQL):

CHAR(CHARACTER), INT (INTEGER), SMALLINT,

DEC (DECIMAL), NUMERIC, FLOAT,

… .

Тип д анных, д лякоторого обяз ательно д олжен бытьука з ан раз мер – это CHAR. Реа льное колич еств о симв олов , которое может наход иться в п оле, меняется от нуля (если в п олесод ержится NULL-з на ч ение) д о з ад анного CREATE TABLE максимального з нач ения.

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

4.2.И спол ь з ование индексац ии дл я бы ст рого дост упа к данны м

О п ера ции п оиска-в ыборки (SELECT) д анных из таблиц п о з нач ениямих п олей могут бытьсущ еств енно ускор ены п утем исп ольз ов ания инд екса ции

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

Инд ексацияп оз в оляет наход итьсод ержащ ий инд ексир ов анную строку блок

уп ра в ление инд ексом сущ еств енно з амед ляет в ремя в ып олнения оп ера ций, св яз анных с обнов лениемд анных (таких, ка к INSERT и DELETE), так как эти оп ерации тр ебуют п ерестройки инд ексов .

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

88

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

Синта ксис команд ы соз д а нияинд екса имеетслед ующ ий в ид :

CREATE INDEX <и м яи нд екса > ON <и м ят а бли цы > (<и м яст о лбца > [,<и м яст о лбца >]… );

При этомтаблица д олжна бытьужесоз д ана и сод ержатьстолбцы, имена

соз д а нным, инд екс яв ляется нев ид имым д ля п ольз ов а теля, в сеоп ера ции с нимосущ еств ляет СУ Б Д .

89

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

ALTER TABLE <и м ят а бли цы > MODIFY <и м яст о лбца > <т и п д а нны х> <р а зм ер /т о чно ст ь>;

п ониз ить.

∙огранич ениеNOT NULL может бытьуста нов лено, если ни од но з нач ениев столбценесод ержит NULL. О п циюNOT NULL в сегд а можно отменить.

∙р аз решаетсяиз менятьз нач ения, уста нов ленныеп о умолч анию.

4.4. Удал ение т абл иц ы

Ч тобы уд алитьсущ еств ующ ую таблицу, необход имо п ред в ар ительно

УП Р АЖ НЕ НИ Я

94.Н а п ишитекома нд у CREATE TABLE д лясоз д аниятаблицы LECTURER 95.Н а п ишитекома нд у CREATE TABLE д лясоз д аниятаблицы SUBJECT 96.Н а п ишитекома нд у CREATE TABLE д лясоз д аниятаблицы UNIVERSITY. 97.Н а п ишитекома нд у CREATE TABLE д лясоз д аниятаблицы EXAM_MARKS. 98.Н а п ишитекома нд у CREATE TABLE д лясоз д аниятаблицы SUBJ_LECT.

90

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

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

4.5. О граничения на м нож ест водопуст им ы х з начений данны х

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

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