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

61

ему з нач ение UNKNOWN. Поэтому рез ульта те в ып олнения п од з ап роса не буд ет п олуч ено ни од ного з нач енияи п од з ап рос п римет з нач ениело ж ь. Э то в св ою оч еред ьсд елает NOT EXISTS истинным, и, след ов ательно, строка с

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

2.14. И спол ь з ование COUNT вм ест о EXISTS

При отсутств ии NULL-з нач ений оп ера тор EXISTS может быть исп ольз ов ан в место ANY и ALL. Такжев место EXISTS и NOT EXISTS могут бытьисп ольз ов а ны тежесамыеп од з а п росы, но с исп ольз ов анием COUNT(*) в п ред ложении SELECT. Н а п ример, з ап рос

SELECT *

FROM UNIVERSITY A

WHERE NOT EXISTS

(SELECT *

FROM UNIVERSITY B

WHERE A.RATING > = B.RATING

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

может бытьп ред став лен и след ующ емв ид е

SELECT *

FROM UNIVERSITY A

WHERE 1 >

(SELECT COUNT(*)

FROM UNIVERSITY B

WHERE A.RATING > = B.RATING

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

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

40.Н а п ишитеэ тот жез ап рос с исп ольз ов аниемMAX.

2.15. О перат ор объ единения UNION

О п ера тор UNION исп ольз уетсяд ляобъед иненияв ыход ных д а нных д в ух или болееSQL-з ап росов в ед иноемножеств о строк и столбцов . Н а п ример,

SELECT ‘С туден т______’, SURNAME, STUDENT_ID

FROM STUDENT

WHERE CITY = ‘М о сква ’

UNION

SELECT ‘Пр епо да ва тель’, SURNAME, LECTURER_ID

FROM LECTURER

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

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

2.16. Уст ранение дубл ирования в UNION

В отлич ие от обыч ных з а п росов UNION а в томатич ески исключ ает из в ыход ных д а нных д ублика ты строк, на п ример, в з ап р осе

SELECT CITY

FROM STUDENT

UNION

SELECT CITY

FROM LECTURER;

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

Е сли в се же необход имо кажд ом з ап росе в ыв ести в се строки нез а в исимо от того, имеются ли такие же строки в д ругих объед иняемых з ап росах, то след ует исп ольз ов атьв о множеств енном з ап росеконструкциюс оп ераторомUNION ALL. Так в з ап росе

SELECT CITY

FROM STUDENT

UNION ALL

SELECT CITY

FROM LECTURER;

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

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

SELECT ‘ма кс о ц’, A.STUDENT_ID, SURNAME, MARK, EXAM_DATE

64

FROM STUDENT A, EXAM_MARKS B WHERE (A.STUDENT_ID = B.STUDENT_ID

AND B.MARK =

(SELECT MAX(MARK)

FROM EXAM_MARKS C

WHERE C.EXAM_DATE = B.EXAM_DATE))

UNION ALL

SELECT ‘ми н о ц ’, A.STUDENT_ID, SURNAME, MARK, EXAM_DATE FROM STUDENT A, EXAM_MARKS B

WHERE (A.STUDENT_ID = B.STUDENT_ID AND B.MARK =

(SELECT MIN(MARK)

FROM EXAM_MARKS C

WHERE C.EXAM_DATE = B.EXAM_DATE));

Д ляотлич иястрок, в ыв од имых п ерв ой и в торой ч астями з а п роса, в них

форма з ап роса, в оз в ращ ающ его а налогич ный рез ультат:

SELECT ‘ма кс о ц’, A.STUDENT_ID, SURNAME, E.MARK, E.EXAM_DATE FROM STUDENT A,

(SELECT B.STUDENT_ID, B.MARK, B.EXAM_DATE FROM EXAM_MARKS B,

(SELECT MAX(MARK) AS MAX_MARK, C.EXAM_DATE FROM EXAM_MARKS C

GROUP BY C.EXAM_DATE) D

WHERE B.EXAM_DATE=D.EXAM_DATE AND B.MARK=MAX_MARK) E

WHERE A.STUDENT_ID=E.STUDENT_ID

UNION ALL

SELECT ‘ми н о ц ’, A.STUDENT_ID, SURNAME, E.MARK, E.EXAM_DATE FROM STUDENT A,

(SELECT B.STUDENT_ID, B.MARK, B.EXAM_DATE FROM EXAM_MARKS B,

(SELECT MIN(MARK) AS MIN_MARK, C.EXAM_DATE FROM EXAM_MARKS C

GROUP BY C.EXAM_DATE) D

65

WHERE B.EXAM_DATE=D.EXAM_DATE

AND B.MARK=MIN_MARK) E

WHERE A.STUDENT_ID=E.STUDENT_ID

2.17. И спол ь з ование UNION сORDER BY

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

SELECT ‘ма кс о ц’, A.STUDENT_ID, SURNAME, E.MARK, E.EXAM_DATE FROM STUDENT A,

(SELECT B.STUDENT_ID, B.MARK, B.EXAM_DATE FROM EXAM_MARKS B,

(SELECT MAX(MARK) AS MAX_MARK, C.EXAM_DATE FROM EXAM_MARKS C

GROUP BY C.EXAM_DATE) D WHERE B.EXAM_DATE=D.EXAM_DATE AND B.MARK=MAX_MARK) E

WHERE A.STUDENT_ID=E.STUDENT_ID

UNION ALL

SELECT ‘ми н о ц ’, A.STUDENT_ID, SURNAME, E.MARK, E.EXAM_DATE FROM STUDENT A,

(SELECT B.STUDENT_ID, B.MARK, B.EXAM_DATE FROM EXAM_MARKS B,

(SELECT MIN(MARK) AS MIN_MARK, C.EXAM_DATE FROM EXAM_MARKS C

GROUP BY C.EXAM_DATE) D

WHERE B.EXAM_DATE=D.EXAM_DATE AND B.MARK=MIN_MARK) E

WHERE A.STUDENT_ID=E.STUDENT_ID ORDER BY SURNAME, E.EXAM_DATE;

66

2.18. В неш нее объ единение

Ч асто п олез на оп ерация объед инения д в ух з ап росов , в которой в торой

SELECT SURNAME, NAME, UNIV_NAME

FROM STUDENT, UNIVERSITY

WHERE STUDENT.UNIV_ID = UNIVERSITY.UNIV_ID

UNION

SELECT SURNAME, NAME, ‘н еи звестен ’

FROM STUDENT

WHERE UNIV_ID IS NULL

ORDER BY 1;

Д ля сов местимости столбцов объед иняемых з ап росов конста нту ‘н еи звестен ’ в о в торомз ап росеслед ует д оп олнитьп робела ми та к, ч тобы ее д лина соотв етств ов а ла д лине п оля UNIV_NAME или исп ольз ов ать д ля согла сов аниятип ов функцию CAST. В некоторых СУ Б Д согласов а ниетип ов п оляи з амещ ающ ей его текстов ой конста нты осущ еств ляетсяав томатич ески.

УП Р АЖ НЕ НИ Я

41.Соз д айте объед инение д в ух з ап росов , которые в ыд ают з нач ения п олей UNIV_NAME, CITY, RATING д ляв сех унив ерситетов . Теиз них, у которых р ейтинг рав ен или в ыше300, д олжны иметькомментарий ‘Вы со ки й’, в се остальные– ‘Н и зки й’.

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

имеющ их хотя бы од ну неуд ов летв орительную оценку, и комментарием ‘н е сда ва л’ – д ля в сех остальных. В в ыв од имом рез ультате фамилии студ ентов уп оряд оч итьп о а лфа в иту.

43.Выв ед итеобъед иненный сп исок студ ентов и п реп од ав ателей, жив ущ их в

2.19.С оединение т абл иц сиспол ь зованием операт ора JOIN

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

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

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

68

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

помощ ьюслед ующ его з ап роса .

SELECT STUDENT.SURNAME, UNIVERSITY.UNIV_NAME, STUDENT.CITY FROM STUDENT, UNIVERSITY

WHERE STUDENT.CITY = UNIVERSITY.CITY;

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

Прив ед енный в ышез ап рос может бытьз а п исан инач е, с исп ольз ов а нием ключ ев ого слов а JOIN.

SELECT STUDENT.SURNAME, UNIVERSITY.UNIV_NAME, STUDENT.CITY

FROM STUDENT INNER JOIN UNIVERSITY

ON STUDENT.CITY = UNIVERSITY.CITY;

оп ератор JOIN неп од д ержив ается.

69

2.19.1.О перац ии соединения т абл иц посредст вом ссы л очной ц ел ост ност и

наз ыв а етсясостоянием ссылоч ной целостности. В д анном случ а ессылоч ная

оп ерации соед инениясостоит в из в леч ении д анных в терминах этой св яз и.

SELECT SURNAME, MARK, SUBJ_ID

FROM STUDENT, EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;

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

SELECT SURNAME, MARK

FROM STUDENT JOIN EXAM_MARKS

ON STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;

Х отя в ышереч ьшла о соед инении д в ух таблиц, можно сформиров ать

SELECT SUBJ_NAME, SURNAME, MARK

FROM STUDENT, SUBJECT, EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID AND SUBJECT.SUBJ_ID = EXAM_MARKS.SUBJ_ID

70

AND EXAM_MARKS.MARK = 2;

То жесамоес исп ольз ов а ниемоп ератор а JOIN

SELECT SUBJ_NAME, SURNAME, MARK

FROM STUDENT JOIN SUBJECT JOIN EXAM_MARKS ON STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID

AND SUBJECT.SUBJ_ID = EXAM_MARKS.SUBJ_ID AND EXAM_MARKS.MARK = 2;

многих случ аях это может п рив ести к нежела тельной п отереинформа ции.

п римере, в нутреннего соед иненияв рез ультат з а п роса неп оп ад ут студ енты, которыеещ енесд а в али экз амены и которые, след ов ательно, отсутств уют таблицеEXAM_MARKS. Е сли женеобход имо иметьз ап иси об этих студ ентах в в ыд ав аемом з ап росом сп иске, то можно п рисоед инить св ед ения о студ ентах, несд а в а в ших экз амен, п утем исп ольз ов а нияоп ератора UNION с соотв етств ующ имз ап росом. Н а п ример, след ующ имобра з ом:

SELECT SURNAME, CAST MARK AS CHAR(1), CAST SUBJ_ID AS CHAR(10) FROM STUDENT, EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID

UNION

SELECT SURNAME, CAST NULL AS CHAR(1), CAST NULL AS CHAR(10)

FROM STUDENT

WHERE NOT EXIST (SELECT *

FROM EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID);

(з д есьфункция п реобразов ания тип ов CAST исп ольз уется д ля обесп еч ения сов местимости тип ов п олей объед иняемых з а п росов ).