| ëÁÔÁÌÏÇ | ïÇÌÁ×ÌÅÎÉÅ | éÎÄÅËÓ ÒÁÚÄÅÌÁ |
| îÁÚÁÄ | ÷ÐÅÒÅÄ |
ðÒÅÄÓÔÁ×ÌÅÎÉÅ: ÉÄÅÎÔÉÆÉËÁÃÉÏÎÎÙÊ ËÏÄ, æéï, ÐÏÄÒÁÚÄÅÌÅÎÉÅ, ÄÏÌÖÎÏÓÔØ, ÏËÌÁÄ, ÏÂßÅÍ ÄÏÌÖÎÏÓÔÉ
CREATE VIEW emplview AS (
SELECT employee.empl_id, name_last, name_1st, name_2nd,
dep_name, job_name, salary, work.rate
FROM employee, work, staff, job, department
WHERE employee.empl_id = work.empl_id
AND work.staff_id = staff.staff_id
AND staff.job_id = job.job_id
AND staff.dep_id = department.dep_id);
SELECT name_last, SUM(salary*rate) AS salary FROM emplview WHERE dep_name='ÐÒÏÅËÔ á' GROUP BY name_last, name_1st, name_2ndORDER BY 2 DESC;
Oracle:
NAME_LAST SALARY -------------------- --------- ëÒÑË×ÉÎ 4500 íÅÌØÎÉË 3000 ðÕÛËÏ× 3000 ëÁÒÁ×ÁÅ×Á 1700 ëÏÔÅÊËÉÎ 1500 íÙÛËÉÎ 1250 ðÏÇÏÒÅÌÏ×Á 850 7 rows selected.
DB2:
NAME_LAST SALARY -------------------- --------------------------------- ëÒÑË×ÉÎ 4500.000 íÅÌØÎÉË 3000.000 ðÕÛËÏ× 3000.000 ëÁÒÁ×ÁÅ×Á 1700.000 ëÏÔÅÊËÉÎ 1500.000 íÙÛËÉÎ 1250.000 ðÏÇÏÒÅÌÏ×Á 850.000 7 record(s) selected.
SELECT dep_name, SUM(rate) AS rate FROM emplview GROUP BY dep_name ORDER BY 2;
DB2:
DEP_NAME RATE -------------------- --------------------------------- ÏÔÄÅÌ ôï 3.0 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 5.5 ÌÁÂÏÒÁÔÏÒÉÑ íí 6.0 ÌÁÂÏÒÁÔÏÒÉÑ ðëó 6.0 ÐÒÏÅËÔ á 6.5 ÌÁÂÏÒÁÔÏÒÉÑ óõâä 7.0 ÆÉÎ.ÏÔÄÅÌ 7.0 ÐÒÏÅËÔ óôáôõó 7.5 ÕÐÒÁ×ÌÅÎÉÅ 8.5 9 record(s) selected.
Oracle:
DEP_NAME RATE -------------------- --------- ÏÔÄÅÌ ôï 3 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 5.5 ÌÁÂÏÒÁÔÏÒÉÑ íí 6 ÌÁÂÏÒÁÔÏÒÉÑ ðëó 6 ÐÒÏÅËÔ á 6.5 ÌÁÂÏÒÁÔÏÒÉÑ óõâä 7 ÆÉÎ.ÏÔÄÅÌ 7 ÐÒÏÅËÔ óôáôõó 7.5 ÕÐÒÁ×ÌÅÎÉÅ 8.5 9 rows selected.
SELECT dep_name, name_last
FROM emplview, education
WHERE emplview.empl_id = education.empl_id
AND education.level_id>=(SELECT level_id
FROM edu_level
WHERE level_name = '×ÙÓÛÅÅ')
ORDER BY 1, 2;
DB2:
DEP_NAME NAME_LAST -------------------- -------------------- ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ ëÏÇÁÎ ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ óÔÅÐÁÎÏ×Á ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ óÔÅÐÁÎÏ×Á ÌÁÂÏÒÁÔÏÒÉÑ íí áÎÀÔÉÎÁ ÌÁÂÏÒÁÔÏÒÉÑ íí óÉÎÑ×ÓËÉÊ ÌÁÂÏÒÁÔÏÒÉÑ ðëó çÒÖÉÍÁÊÌÏ ÌÁÂÏÒÁÔÏÒÉÑ ðëó ëÏÂÅÌÅ×Á ÌÁÂÏÒÁÔÏÒÉÑ ðëó íÁÌÅÅ×Á ÌÁÂÏÒÁÔÏÒÉÑ óõâä ÷ÁÌØÔÍÁÎ ÌÁÂÏÒÁÔÏÒÉÑ óõâä çÁÂÁÊ ÌÁÂÏÒÁÔÏÒÉÑ óõâä úÁÈÁÒÏ× ÌÁÂÏÒÁÔÏÒÉÑ óõâä ìÑÛÅÎËÏ ÌÁÂÏÒÁÔÏÒÉÑ óõâä ìÑÛÅÎËÏ ÌÁÂÏÒÁÔÏÒÉÑ óõâä ñÎÏ×Á ÏÔÄÅÌ ôï âÅÓÃÅÎÎÙÊ ÏÔÄÅÌ ôï âÅÓÃÅÎÎÙÊ ÏÔÄÅÌ ôï ëÏÌÂÁÓÉÎ ÐÒÏÅËÔ á ëÏÔÅÊËÉÎ ÐÒÏÅËÔ á ëÒÑË×ÉÎ ÐÒÏÅËÔ á íÅÌØÎÉË ÐÒÏÅËÔ á ðÕÛËÏ× ÐÒÏÅËÔ á ðÕÛËÏ× ÐÒÏÅËÔ óôáôõó âÅÒÍÁÎ ÐÒÏÅËÔ óôáôõó ëÕÍÕÛËÉÎ ÐÒÏÅËÔ óôáôõó íÕÖÉÞËÏ× ÐÒÏÅËÔ óôáôõó ïÓÔÒÏÕÍÏ× ÐÒÏÅËÔ óôáôõó ðÕÓÔÏ×ÅÃËÁÑ ÕÐÒÁ×ÌÅÎÉÅ çÁÒÍÁÛ ÕÐÒÁ×ÌÅÎÉÅ çÏÒÉÄÚÅ ÕÐÒÁ×ÌÅÎÉÅ çÕÓÅ×Á ÕÐÒÁ×ÌÅÎÉÅ äÏÒÏÖËÉÎ ÕÐÒÁ×ÌÅÎÉÅ óÁÂÁÄÁÛ ÕÐÒÁ×ÌÅÎÉÅ óÁÂÁÄÁÛ ÕÐÒÁ×ÌÅÎÉÅ óÁËÒÉÓÑÎ ÆÉÎ.ÏÔÄÅÌ âÅÌØÞÅÎËÏ ÆÉÎ.ÏÔÄÅÌ ÷ÁÓÉÌÅÎËÏ ÆÉÎ.ÏÔÄÅÌ ëÉÓÌÑÒÓËÉÊ ÆÉÎ.ÏÔÄÅÌ ëÉÓÌÑÒÓËÉÊ ÆÉÎ.ÏÔÄÅÌ ëÏÃÀÂÁ ÆÉÎ.ÏÔÄÅÌ ìÑÛÅÎËÏ ÆÉÎ.ÏÔÄÅÌ ìÑÛÅÎËÏ 41 record(s) selected.
Oracle:
DEP_NAME NAME_LAST -------------------- -------------------- ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ ëÏÇÁÎ ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ óÔÅÐÁÎÏ×Á ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ óÔÅÐÁÎÏ×Á ÌÁÂÏÒÁÔÏÒÉÑ íí áÎÀÔÉÎÁ ÌÁÂÏÒÁÔÏÒÉÑ íí óÉÎÑ×ÓËÉÊ ÌÁÂÏÒÁÔÏÒÉÑ ðëó çÒÖÉÍÁÊÌÏ ÌÁÂÏÒÁÔÏÒÉÑ ðëó ëÏÂÅÌÅ×Á ÌÁÂÏÒÁÔÏÒÉÑ ðëó íÁÌÅÅ×Á ÌÁÂÏÒÁÔÏÒÉÑ óõâä ÷ÁÌØÔÍÁÎ ÌÁÂÏÒÁÔÏÒÉÑ óõâä çÁÂÁÊ ÌÁÂÏÒÁÔÏÒÉÑ óõâä úÁÈÁÒÏ× ÌÁÂÏÒÁÔÏÒÉÑ óõâä ìÑÛÅÎËÏ ÌÁÂÏÒÁÔÏÒÉÑ óõâä ìÑÛÅÎËÏ ÌÁÂÏÒÁÔÏÒÉÑ óõâä ñÎÏ×Á ÏÔÄÅÌ ôï âÅÓÃÅÎÎÙÊ ÏÔÄÅÌ ôï âÅÓÃÅÎÎÙÊ ÏÔÄÅÌ ôï ëÏÌÂÁÓÉÎ ÐÒÏÅËÔ á ëÏÔÅÊËÉÎ ÐÒÏÅËÔ á ëÒÑË×ÉÎ ÐÒÏÅËÔ á íÅÌØÎÉË ÐÒÏÅËÔ á ðÕÛËÏ× DEP_NAME NAME_LAST -------------------- -------------------- ÐÒÏÅËÔ á ðÕÛËÏ× ÐÒÏÅËÔ óôáôõó âÅÒÍÁÎ ÐÒÏÅËÔ óôáôõó ëÕÍÕÛËÉÎ ÐÒÏÅËÔ óôáôõó íÕÖÉÞËÏ× ÐÒÏÅËÔ óôáôõó ïÓÔÒÏÕÍÏ× ÐÒÏÅËÔ óôáôõó ðÕÓÔÏ×ÅÃËÁÑ ÕÐÒÁ×ÌÅÎÉÅ çÁÒÍÁÛ ÕÐÒÁ×ÌÅÎÉÅ çÏÒÉÄÚÅ ÕÐÒÁ×ÌÅÎÉÅ çÕÓÅ×Á ÕÐÒÁ×ÌÅÎÉÅ äÏÒÏÖËÉÎ ÕÐÒÁ×ÌÅÎÉÅ óÁÂÁÄÁÛ ÕÐÒÁ×ÌÅÎÉÅ óÁÂÁÄÁÛ ÕÐÒÁ×ÌÅÎÉÅ óÁËÒÉÓÑÎ ÆÉÎ.ÏÔÄÅÌ âÅÌØÞÅÎËÏ ÆÉÎ.ÏÔÄÅÌ ÷ÁÓÉÌÅÎËÏ ÆÉÎ.ÏÔÄÅÌ ëÉÓÌÑÒÓËÉÊ ÆÉÎ.ÏÔÄÅÌ ëÉÓÌÑÒÓËÉÊ ÆÉÎ.ÏÔÄÅÌ ëÏÃÀÂÁ ÆÉÎ.ÏÔÄÅÌ ìÑÛÅÎËÏ ÆÉÎ.ÏÔÄÅÌ ìÑÛÅÎËÏ 41 rows selected.
| îÁÚÁÄ | ÷ÐÅÒÅÄ |
| ëÁÔÁÌÏÇ | ïÇÌÁ×ÌÅÎÉÅ | éÎÄÅËÓ ÒÁÚÄÅÌÁ |