FROM employee e, department d,
FROM employee e, department d, job j
WHERE d.mngr_no = e.emp_no
AND e.job_code = j.job_code
AND e.job_grade = j.job_grade
AND e.job_country = j.job_country
ïîëó÷èòü ñïèñîê ñîòðóäíèêîâ
ñ íàçâàíèÿìè èõ äîëæíîñòåé
è íàçâàíèÿìè îòäåëîâ
FIRST_NAME LAST_NAME JOB_TITLE DEPARTMENT
========== ============ ======================= ======================
Robert Nelson Vice President Engineering
Phil Forest Manager Quality Assurance
K. J. Weston Sales Representative Field Office: East Coast
Katherine Young Manager Customer Support
Chris Papadopoulos Manager Research and Development
Janet Baldwin Sales Co-ordinator Pacific Rim Headquarters
Roger Reeves Sales Co-ordinator European Headquarters
Walter Steadman Chief Financial Officer Finance
 äàííîì ïðèìåðå ïîñëåäíèå òðè óñëîâèÿ íåîáõîäèìû â ñèëó òîãî, ÷òî ïåðâè÷íûé êëþ÷ â òàáëèöå JOB ñîñòîèò èç òðåõ ïîëåé - ñì. ðèñ.1.
Ìû ðàññìîòðåëè âíóòðåííèå ñîåäèíåíèÿ ñ èñïîëüçîâàíèåì ñòàíäàðòà ANSI-89. Òåïåðü îïèøåì íîâûé (ANSI-92) ñòàíäàðò:
· óñëîâèÿ ñîåäèíåíèÿ çàïèñûâàþòñÿ â ïðåäëîæåíèè FROM, â êîòîðîì ñëåâà è ñïðàâà îò çàðåçåðâèðîâàííîãî ñëîâà “JOIN” óêàçûâàþòñÿ ñîåäèíÿåìûå òàáëèöû;
· óñëîâèÿ ïîèñêà, îñíîâàííûå íà
ïðàâîé
òàáëèöå, ïîìåùàþòñÿ â ïðåäëîæåíèå
ON;
· óñëîâèÿ ïîèñêà, îñíîâàííûå íà
ëåâîé òàáëèöå, ïîìåùàþòñÿ â ïðåäëîæåíèå
WHERE.
SELECT first_name, last_name, department
FROM employee e JOIN department d
ON e.dept_no = d.dept_no
AND department = "Customer Support"
WHERE last_name starting with "P"
ïîëó÷èòü ñïèñîê ñëóæàùèõ
(à çàîäíî è íàçâàíèå îòäåëà),
ÿâëÿþùèõñÿ ñîòðóäíèêàìè îòäåëà
“Customer Support”, ôàìèëèè êîòî-
ðûõ íà÷èíàþòñÿ ñ áóêâû “P”
FIRST_NAME LAST_NAME DEPARTMENT
============= =============== ===================
Leslie Phong Customer Support
Bill Parker Customer Support
Ñîäåðæàíèå Íàçàä Âïåðåä