SQL в вопросах и задачах
.pdf71
Н ужный рез ультат, од нако, может бытьп олуч ен и п утемисп ольз ов а ния
внеш него со ед и нени я, точ нееод ной из его ра з нов ид ностей – лево го внеш него со ед и нени я, с исп ольз ов а нием которого з ап рос буд ет в ыгляд етьслед ующ им образ ом:
SELECT SURNAME, MARK
FROM STUDENT LEFT OUTER JOIN EXAM_MARKS
ON STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;
При исп ольз ов ании лево го соед инения расширение в ыв од имой таблицы осущ еств ляетсяз а сч ет з ап исей в ход ной таблицы, имякоторой указ а но слева от оп ератора JOIN.
След ует з аметить, ч то нотацияз ап росов с в нешнимсоед инением СУ Б Д ORACLE отлич аетсяот п рив ед енной нота ции, з ад а в аемой ста нд артом яз ыка SQL. В нота ции, исп ольз уемой в Oracle, этот жез а п рос буд ет иметьв ид
SELECT SURNAME, MARK, SUBJ_ID FROM STUDENT, EXAM_MARKS
WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID(+);
Зна к (+) став итсяу той та блицы, котораяд оп олняетсяз ап исями с NULL- з нач ениями, ч тобы п ри соед инении таблиц в в ыход ноеотношениеп оп а ли и тез ап иси д ругой таблицы, д лякоторых в та блицесо з наком(+) нена ход ится
строк с соотв етств ующ ими |
з нач ениями атрибутов , исп ольз уемых д ля |
соед инения. То естьд лялево го |
в нешнего соед инения(п о нотации станд арта |
SQL) в з а п росеORACLE-SQL указ а тель(+) став итсяу пр а во йтаблицы.
Прив ед енный в ыше з ап рос может бытьреа лиз ов а н и с п р именением
пр а во го в неш него со ед и нени я. О н буд ет иметьслед ующ ий в ид
SELECT SURNAME, MARK
FROM EXAM_MARKS RIGHT OUTER JOIN STUDENT
ON EXAM_MARKS.STUDENT_ID = STUDENT.STUDENT_ID;
Зд есь таблица STUDENT, з а сч ет з ап исей котор ой осущ еств ляется расширениев ыв од имой таблицы, стоит сп рав а от оп ератора JOIN.
В нотации Oracle э тот зап р ос буд ет в ыгляд етьслед ующ имобраз ом.
SELECT SURNAME, MARK, SUBJ_ID FROM STUDENT, EXAM_MARKS
WHERE EXAM_MARKS.STUDENT_ID(+) = STUDENT.STUDENT_ID;
Вид но, ч то исп ольз ов ание в нешнего п р а в ого или лев ого соед инения
|
|
72 |
п оз в оляет |
сущ еств енно |
уп ростить з а п рос, сд елать его з ап ись более |
комп актной. |
|
|
Иногд а |
в оз никает |
необход имость в ключ ения в рез ультат з а п роса |
з ап исей из |
обеих (п рав ой и лев ой) соед иняемых та блиц, д ля которых не |
|
уд ов летв оряется услов иесоед инения. Такоесоед инениеназ ыв ается по лны м
внеш ни м со ед и нени ем |
и осущ еств ляетсяуказ анием |
з а п росеключ ев ых слов |
FULL OUTER JOIN или UNION JOIN. |
|
|
УП Р АЖ НЕ НИ Я |
|
|
45.Н а п ишите з ап рос, |
который в ып олняет в ыв од |
д анных о фамилиях, |
сд а ва вш и х э кзамены студ ентов , в месте с ид ентификатора ми кажд ого сд анного ими п ред мета обуч ения.
46.Н а п ишите з ап р ос, который в ып олняет в ыборку з нач ений фамилии в сех студ ентов с указ а нием д ля студ ентов , сд а в а в ших экз а мены, ид ентифика торов сд анных ими п ред метов обуч ения.
47.Н а п ишите з а п рос, который в ып олняет |
в ыв од д анных о |
фа милиях |
|||
студ ентов , сд а ва вш и х экз амены, в месте с |
наименов а ниями |
кажд ого |
|||
сд анного ими п ред мета обуч ения. |
|
|
|
|
|
48.Н а п ишите з ап рос на |
в ыд ач у д ля ка жд ого |
студ ента |
наз в а ний в сех |
||
п ред метов обуч ения, п о которымэтот студ ентп олуч ил оценку 4 или 5. |
|||||
49.Н а п ишите з ап р ос на |
в ыд а ч у д а нных о |
наз в а ниях в сех |
п ред метов , п о |
||
которымстуд енты п олуч или только хорошие(4 и 5) оценки. В в ыход ных д анных д олжны бытьп р ив ед ены фамилии студ ентов , на з в анияп ред метов и оценка.
50.Н а п ишите з ап рос, который в ып олняет в ыв од сп иска унив ерситетов с
р ейтингом, п рев ыша ющ им 300, в месте со |
з нач ением максимального |
р аз мера стип енд ии, п олуч аемой студ ентами |
этих унив ерситетах. |
51.Н а п ишите з ап р ос на в ыд ач у сп иска фа милий студ ентов (в а лфа в итном п оряд ке) в местесо з нач ением рейтинга унив ерситета , гд екажд ый из них уч ится, в ключ ив в сп исок и тех студ ентов , д лякоторых в баз ед анных не указ ано место их уч ебы.
73
2.19.3. И спол ь з ование псевдоним ов при соединении
табл иц
Часто п ри п олуч ении информации из таблиц баз ы д анных необход имо осущ еств лятьсоед инениетаблицы с еежекоп ией. Н а п ример, это требуетсяв случ а е, когд а требуется найти фамилии студ ентов , имеющ их од инаков ые имена. При соед инении таблицы с еежекоп ией в в од ят п сев д онимы (алиасы)
таблицы. Зап рос д ля п оиска фа милий студ ентов , имеющ их од инаков ые имена, в ыгляд ит след ующ имобраз ом
SELECT FIRST.SURNAME, SECOND.SURNAME
FROM STUDENT FIRST, STUDENT SECOND
WHERE FIRST.NAME = SECOND.NAME
В э том з а п росев в ед ены д в а п сев д онима д ляод ной та блицы STUDENT,
ч то п оз в оляет |
корректно |
з ад а ть в ыражение, |
св яз ыв а ющ ее д в е коп ии |
|
таблицы. |
Ч тобы исключ ить п ов торения строк |
в в ыв од имом рез ультате |
||
з ап роса |
из -з а |
п ов торного |
сра в нения од ной и |
той же п ар ы студ ентов , |
необход имо з ад атьп оряд ок след ов ания д ля д в ух з нач ений так, ч тобы од но з нач ениебыло меньше, ч емд ругое, ч то д елаетп р ед икат асимметрич ным.
SELECT FIRST.SURNAME, SECOND.SURNAME
FROM STUDENT FIRST, STUDENT SECOND
WHERE FIRST.NAME = SECOND.NAME
AND FIRST.SURNAME < SECOND.SURNAME
УП Р АЖ НЕ НИ Я
52.Н а п исать з ап р ос, в ып олняющ ий в ыв од сп иска в сех п ар фамилий студ ентов , п рожив а ющ их в од номгор од е. При этомнев ключ атьв сп исок комбина ции фамилий студ ентов самих с собой (то естькомбинацию тип а “Ив анов -Ив а нов ”) и комбина ции фамилий студ ентов , отлич ающ иеся п оряд ком след ов а ния (то естьв ключ атьод ну из д в ух комбина ций тип а “Ив анов -Петров ” и “Петров -Ив анов ”).
53.Н а п исать з ап рос, в ып олняющ ий |
в ыв од сп иска |
в сех |
п ар |
наз в аний |
унив ерситетов , расп оложенных в |
од ном город е, |
не в ключ ая в сп исок |
||
комбина ции на з в аний унив ерситетов самих с собой и |
п ары |
наз в аний |
||
унив ерситетов , отлич ающ иесяп оряд комслед ов а ния. |
|
|
||
|
|
74 |
|
|
54.Н а п исать з а п рос, |
который |
п оз в оляет |
п олуч ить д анные о |
наз в а ниях |
унив ерситетов и |
город ов , в |
которых |
они расп оложены, с |
рейтингом, |
р ав нымили п рев ыша ющ имрейтингВГ У .
Д О П О Л НИ Т Е Л ЬНЫ |
Е УП Р АЖ НЕ НИ Я НА В Ы БО Р КУД АННЫ |
Х |
55.Н а п исатьз ап рос, |
в ып олняющ ий в ыв од д анных об именах и |
фамилиях |
студ ентов , п олуч ив ших хотябы од ну отлич нуюоценку. |
|
|
56.Н а п исатьз ап рос, |
в ып олняющ ий в ыв од д анных об именах и |
фамилиях |
студ ентов , имеющ их в есьнабор оценок (тройки, ч етв ерки и п ятерки). |
||
57.Н а п исать з а п рос, |
в ып олняющ ий в ыбор ку з нач ений ид ентифика торов |
|
студ ентов , имеющ их такиежеоценки, ч то и студ ент с ид ентификатором
12.
58.Н а п исать з а п рос, |
в ып олняющ ий |
в ыборку |
в сех |
п а р ид ентифика торов |
|||
п реп од ав ателей, в ед ущ их од инаков ыеп ред меты обуч ения. |
|
|
|||||
59.Н а п исатьз ап рос, в ып олняющ ий в ыв од |
д анных об именах и |
фамилиях |
|||||
студ ентов , неп олуч ив ших ни од ной отлич ной оценки. |
|
|
|||||
60.Н а п исать з ап рос, |
в ып олняющ ий |
в ыборку з нач ений |
наименов аний |
||||
п ред метов обуч ения, на п реп од ав а ниекоторых отв од итсяболее50 ч асов . |
|||||||
61.Н а п исать з ап рос, |
в ып олняющ ий |
в ыв од |
колич еств а |
студ ентов , не |
|||
имеющ их ни од ной оценки. |
|
|
|
|
|
|
|
62.Н а п исатьз ап рос, в ып олняющ ий в ыв од |
колич еств а |
студ ентов , |
имеющ их |
||||
только отлич ныеоценки. |
|
|
|
|
|
|
|
63.Н а п исатьз ап рос, |
в ып олняющ ий в ыв од |
д анных о |
п р ед метах |
обуч ения, |
|||
которыеп реп од а ютсяп реп од а в ателемп о фа милии К олесников . |
|
||||||
64.Н а п исатьз ап рос, в ып олняющ ий в ыв од |
имен и фамилий п реп од ав ателей, |
||||||
п ров од ящ их з анятияна п ер в омкурсе. |
|
|
|
|
|
||
65.Н а п исатьз ап р ос, в ып олняющ ий в ыв од имен и фамилий студ ентов , место п рожив а ния которых не сов п а д ает с город ом, в котором наход ится их унив ерситет.
66.Н а п исатьз а п рос, в ып олняющ ий в ыв од колич еств а экз аменов , сд а нных (с п оложительной оценкой) студ ентомс ид ентификатором32.
75
67.Н а п исатьз ап рос, в ып олняющ ий в ыв од имен и фамилий п реп од ав ателей, ч итающ их д в а и болеера з лич ных п ред мета обуч ения.
68.Н а п исатьз ап рос, в ып олняющ ий в ыв од имен и фамилий п реп од ав ателей,
п ров од ящ их з анятияв д в ух и болеесеместр ах. |
|
|
|||
69.Н а п исать з ап рос, |
в ып олняющ ий |
в ыв од |
д анных о |
наименов а ниях |
|
п ред метов обуч ения, ч итаемых д в умяи болееп реп од а в ателями. |
|
||||
70.Н а п исатьз а п рос, в ып олняющ ий в ыв од д ля ка жд ого п р ед мета |
обуч ения, |
||||
п реп од ав аемого д ля студ ентов ВГ У , его на именов ание, |
фа милию и имя |
||||
п реп од ав ателя, и город , в которомжив етстуд ент. |
|
|
|||
71.Н а п исать з ап р ос, |
в ып олняющ ий |
в ыв од |
колич еств а |
ч а сов |
з анятий, |
п ров од имых п реп од а в ателемЛ агутиным. |
|
|
|
||
72.Н а п исать з ап рос, |
в ып олняющ ий |
в ыв од |
фамилий |
п реп од ав ателей, |
|
ч итающ их та киежеп ред меты обуч ения, ч то и п реп од ав а тельСорокин.
73.Н а п исатьз ап р ос, в ып олняющ ий в ыв од фа милий п реп од ав ателей, уч ебна я нагруз ка которых (колич еств о уч ебных ч асов ) п рев ышает нагр уз ку п реп од ав ателяН икола ев а.
74.Н а п исать |
з ап р ос, |
в ып олняющ ий |
в ыв од |
д анных |
о |
п реп од ав ателях, |
в ед ущ их обуч ение хотя бы п о од ному из |
п ред метов |
обуч ения, которые |
||||
п реп од аютсяп реп од ав а телемп о фа милии Сор окин. |
|
|
||||
75.Н а п исать |
з а п рос, |
в ып олняющ ий |
в ыв од д анных |
о фамилиях |
||
п реп од ав ателей, п реп од ающ их студ ентам, обуч ающ имся в |
унив ерситетах |
||||
с рейтингом, меньшим200. |
|
|
|
|
|
76.Н а п исать з ап рос, |
в ып олняющ ий |
в ыв од |
д анных |
о |
наименов а ниях |
унив ерситетов , ра сп оложенных в М оскв е, |
и имеющ их рейтинг меньше, |
||||
ч ему ВГ У . |
|
|
|
|
|
77.Н а п исать з а п рос, |
в ып олняющ ий |
в ыв од |
сп иска фамилий студ ентов , |
||
обуч аемых в унив ерситете, расп оложенном в город е, |
на з в а ниекоторого |
||||
стоит п ерв ымв а лфав итномсп искегород ов . |
|
|
|
||
78.Н а п исатьз ап р ос, в ып олняющ ий в ыв од сп иска студ ентов , сред няяоценка которых п рев ышает 4 балла .
79.Н |
а п исатьз ап рос, в ып олняющ ий в ыв од общ его колич еств а уч ебных ч а сов |
з |
анятий, п ров од имых д лястуд ентов п ер в ого курса ВГ У . |
|
|
76 |
|
|
|
|
|
80.Н а п исать з ап рос, |
в ып олняющ ий в ыв од |
сред него |
колич еств а уч ебных |
||||
ч асов п ред метов обуч ения, п реп од а в аемых студ ента мв торого курса ВГ У . |
|||||||
81.Н а п исатьз ап рос, |
в ып олняющ ий в ыв од |
колич еств а |
студ ентов , имеющ их |
||||
хотябы од ну неуд ов летв орительнуюоценку и п рожив а ющ их в город е, |
не |
||||||
сов п ад а ющ емс город омих унив ерситета. |
|
|
|
|
|||
82.Н а п исать з а п рос, |
в ып олняющ ий в ыв од |
сп иска |
фамилий |
студ ентов , |
|||
имеющ их |
только |
отлич ные оценки |
и |
п рожив а ющ их в |
город е, |
не |
|
сов п ад а ющ емс город омих унив ерситета. |
|
|
|
|
|||
83.Н а п исать з а п рос, |
в ып олняющ ий в ыв од |
сп иска |
фамилий |
студ ентов , |
|||
имеющ их |
д в е и |
более отлич ных |
оценки в |
кажд ом семестре, |
и |
||
п рожив а ющ их в город е, несов п а д а ющ емс город омих унив ерситета.
84.Прив ед итекак можно большеформулиров ок з ап роса “Получ итьфамилии студ ентов , сд а в а в ших экз а мен п о информатике”.
85.Прив ед итекак можно большеформулиров ок з ап роса “Получ итьфамилии п реп од ав ателей, п реп од а ющ их информатику”.
3. М а н и пули р о ва н и еда н н ы ми
3.1. Ком анды м анипул ирования данны м и
В SQL д ляв ып олненияоп ер аций в в од а д анных в таблицу, их из менения и уд аления п р ед наз нач ены три кома нд ы яз ыка манип улиров а ния д а нными
(DML). Э то команд ы – INSERT (вставить), UPDATE (обновить), DELETE
(удалить).
К оманд а |
INSERT осущ еств ляет в ста в ку в таблицу нов ой строки. В |
|||||
п ростейшемслуч аеона имеет след ующ ий в ид : |
|
|
||||
INSERT INTO <и м ят а бли цы > |
VALUES (<зна чени е>, <зна чени е>, … ); |
|||||
При такой з ап иси ука з анныев |
скобках п ослеключ ев ого слов а VALUES |
|||||
з нач ения в в од ятся в |
п оля д обав ленной |
в таблицу нов ой |
строки в том |
|||
п оряд ке, в |
котором |
соотв етств ующ ие столбцы |
указ аны |
п ри соз д ании |
||
таблицы, то естьв оп ератореCREATE TABLE. |
|
|
||||
Н а п ример, в в од |
нов ой стр оки в |
таблицу |
STUDENT |
может быть |
||
осущ еств лен след ующ имобраз ом |
|
|
|
|
||
INSERT INTO STUDENT
VALUES (101,‘Ива н о в’,‘Алекса н др ’, 200, 3,‘М о сква ’,‘6/10/1979’, 15);
Ч тобы така якоманд а могла бытьв ып олнена , таблица с указ аннымв ней
именем (STUDENT) д олжна |
быть п ред в ар ительно оп ред елена |
(соз д ана) |
команд ой CREATE TABLE. |
Е сли в ка кое-либо п оле необход имо |
в ста в ить |
NULL-з на ч ение, то оно в в од итсякак обыч ноез нач ение: |
|
|
INSERT INTO STUDENT
VALUES (101,‘Ива н о в’, NULL, 200, 3,‘М о сква ’,‘6/10/1979’, 15);
В случ аях, когд а необход имо в в ести з нач ения п олей п оряд ке, отлич ном от п оряд ка столбцов , з а д анного команд ой CREATE TABLE, или если требуется в в ести з нач ения нев о в сестолбцы, то след ует исп ольз ов ать след ующ уюформу кома нд ы INSERT:
INSERT INTO STUDENT (STUDENT_ID, CITY, SURNAME, NAME)
VALUES (101, ‘М о сква ’, ‘Ива н о в’, ‘С а ша ’);
Столбцам, на именов а ниякоторых неуказ аны в п р ив ед енном в скобках сп иске, ав томатич ески п рисв а ив ается з нач ение п о умолч а нию, если оно
78
наз на ч ено п р и оп исании таблицы (кома нд а CREATE TABLE), либо з на ч ение
NULL.
С п омощ ью кома нд ы INSERT можно из в леч ь з нач ение из од ной
таблицы и раз меститьего |
д ругой, к п римеру, з ап росомслед ующ его в ид а : |
|||
INSERT INTO STUDENT1 |
|
|
||
SELECT * |
|
|
|
|
FROM STUDENT |
‘М о сква ’; |
|
|
|
WHERE CITY = |
|
|
||
При |
этом таблица STUDENT1 д олжна |
быть п ред в арительно соз д ана |
||
команд ой |
CREATE TABLE (раз д ел |
4.1) и |
иметь структуру, ид ентич ную |
|
таблицеSTUDENT. |
|
|
|
|
У да ление строк из |
та блицы |
осущ еств ляется с п омощ ью кома нд ы |
||
DELETE. |
|
|
|
|
След ующ еев ыражениеуд а ляет в сестроки таблицы EXAM_MARKS1.
DELETE FROM EXAM_MARKS1;
В рез ультатетаблица ста нов ится п устой (п ослеэ того она может быть уд алена кома нд ой DROP TABLE).
Д ля уд а ления из |
та блицы |
сраз у нескольких строк, уд ов летв ор яющ их |
некоторому услов ию |
можно |
в осп ольз ов аться п ред ложением WHERE, |
нап ример , |
|
|
DELETE FROM EXAM_MARKS1
WHERE STUDENT_ID = 103;
М ожно уд алитьгруп п у строк
DELETE FROM STUDENT1
WHERE CITY = ‘М о сква ’;
79
К оманд а UPDATE п оз в оляет изменять , то есть обнов лять, з на ч ения некоторых или в сех п олей сущ еств ующ ей строке или строках та блицы. Н а п ример, ч тобы д ля в сех унив ерситетов , св ед ения о которых на ход ятся в
таблице UNIVERSITY1, |
из менить рейтинг на |
з нач ение 200, |
можно |
исп ольз ов атьконструкцию: |
|
|
|
UPDATE UNIVERSITY1 |
|
|
|
SET RATING = |
200; |
|
|
Д ля ука з ания конкретных строк таблицы, |
з на ч ения п олей |
которых |
|
д олжны бытьиз менены, в |
команд еUPDATE можно исп ольз ов атьп ред икат, |
||
указ ыв аемый в п ред ложении WHERE. |
|
|
|
UPDATE UNIVERSITY1 |
|
|
|
SET RATING = 200 |
|
|
|
WHERE CITY = |
‘М о сква ’; |
|
|
В р ез ультатев ып олненияэ того з ап р оса буд ет из менен рейтинг только у унив ерситетов , расп оложенных в М оскв е.
К оманд а UPDATE п оз в оляет из менятьнетолько |
од ин, но и множеств о |
||
столбцов . |
Д ля указ ания конкретных столбцов , |
з нач ения которых д олжны |
|
бытьмод ифициров а ны, исп ольз уетсяп ред ложениеSET. |
|||
Н а п ример, на именов а ниеп ред мета обуч ения ‘М а тема ти ка’ (д ля него |
|||
SUBJ_ID |
= 43) д олжно быть з аменено, |
на |
на з в ание ‘Вы сша я |
ма тема ти ка’, п р и этом ид ентификационный номер необход имо сохранить, но в соотв етств ующ ие п оля строки таблицы в в ести нов ыед а нныеоб этом п ред метеобуч ения. Зап рос буд ет в ыгляд етьслед ующ имобра з ом.
UPDATE SUBJECT1 |
‘Вы сша я ма тема ти ка’, HOUR = 36, SEMESTER |
SET SUBJ_NAME = |
|
= 1 |
|
WHERE SUBJ_ID = |
43; |
В п ред ложении SET кома нд ы UPDATE можно исп ольз ов атьска лярные в ыражения, указ ыв а ющ иесп особ из мененияз нач ений п оля, в которыемогут в ход итьз нач енияиз меняемого и д ругих п олей.
UPDATE UNIVERSITY1
SET RATING = RATING*2;
|
80 |
|
Н а п ример, д ля ув елич ения в таблице STUDENT1 |
з на ч ения п оля |
|
STIPEND в д в а раз а д лястуд ентов из М оскв ы можно исп ольз ов атьз ап р ос |
||
UPDATE STUDENT1 |
|
|
SET STIPEND = STIPEND*2 |
|
|
WHERE CITY = |
‘М о сква ’; |
|
Пред ложение SET |
не яв ляется п ред икатом, п оэтому |
нем можно |
указ а тьз нач ениеNULL след ующ имобраз ом. |
|
|
UPDATE UNIVERSITY1 |
|
|
SET RATING = |
NULL |
|
WHERE CITY = |
‘М о сква ’; |
|
УП Р АЖ НЕ НИ Я |
|
|
|
|
|
|
|
86.Н а п ишите команд у, котора я в в од ит |
в |
таблицу |
SUBJECT строку |
д ля |
|||
нов ого п ред мета обуч ениясо след ующ ими з нач ениями п олей: |
|
|
|
||||
SEMESTER = 4; SUBJ_NAME |
= |
‘Алгеб р а ’; HOUR |
|
= |
72; |
||
SUBJ_ID = 201. |
|
|
|
|
|
|
|
87.Вв ед ите з ап ись д ля нов ого студ ента, которого |
зов ут О р лов |
Н икола й, |
|||||
обуч ающ егося на п ерв ом курсеВГ У , |
жив ущ его |
Воронеже, св ед ения о |
|||||
д атерожд енияи раз мер естип енд ии неиз в естны. |
|
|
|
|
|||
88.Н а п ишите кома нд у, уд а ляющ ую из |
таблицы |
EXAM_MARKS з ап иси |
обо |
||||
в сех оценка х студ ента, ид ентификатор которого рав ен 100. |
|
|
|
||||
89.Н а п ишите кома нд у, которая ув елич ив ает на |
5 з на ч ение рейтинга в сех, |
||||||
имеющ ихся в ба з е д а нных унив ерситетов , |
расп оложенных |
в |
Са нкт- |
||||
Петербурге. |
|
|
|
|
|
|
|
90.Из мените в та блице з на ч ение город а, |
в котором п рожив ает |
студ ент |
|||||
Ив а нов , на “Во р о н еж ”. |
|
|
|
|
|
|
|