이번 레포트는 책의 Exercise를 푸는 문제였습니다. 그런데 손으로 푸는 문제를 스캔하지 않고 바로 제출해버렸습니다.OTL.. 그래서 SQL을 만드는 문제 풀이만 올립니다.
문제는 교재인 Database Management Systems입니다. 구글링을 하니 구글에서 해당 책의 스캔본을 제공하는군요.
그래서 거기에서 문제를 가져왔습니다.
테스트 환경은 Oracle 9i입니다.
exercise_5_4.sql
1: -- DDL2: create table E54Emp (eid number, ename varchar(20), age number, salary number, PRIMARY KEY(eid));3: create table E54Works (eid number, did number, pct_time number, PRIMARY KEY(eid, did));4: create table E54Dept (did number, dname varchar(20), budget number, managerid number, PRIMARY KEY(did), FOREIGN KEY(managerid) REFERENCES E54Emp);5:6: -- Sample Data Creation7: insert into E54Emp (eid, ename, age, salary) values (108,'NoSyu',25,1650000);8: insert into E54Emp (eid, ename, age, salary) values (122,'DUSTIN',45,630000);9: insert into E54Emp (eid, ename, age, salary) values (191,'CHRISTOPER',24,240000);10: insert into E54Emp (eid, ename, age, salary) values (195,'BOB',63,1680000);11: insert into E54Emp (eid, ename, age, salary) values (229,'BRUTUS',33,240000);12: insert into E54Emp (eid, ename, age, salary) values (331,'LUBBER',55,960000);13: insert into E54Emp (eid, ename, age, salary) values (369,'SMITH' ,23,240000);14: insert into E54Emp (eid, ename, age, salary) values (432,'ANDY',25,600000);15: insert into E54Emp (eid, ename, age, salary) values (499,'ALLEN' ,24,480000);16: insert into E54Emp (eid, ename, age, salary) values (500,'DAVE',29,540000);17: insert into E54Emp (eid, ename, age, salary) values (521,'WARD' ,23,375000);18: insert into E54Emp (eid, ename, age, salary) values (558,'RUSTY',35,750000);19: insert into E54Emp (eid, ename, age, salary) values (566,'JONES' ,32,892500);20: insert into E54Emp (eid, ename, age, salary) values (650,'SMITH' ,29,570000);21: insert into E54Emp (eid, ename, age, salary) values (654,'MARTIN' ,24,375000);22: insert into E54Emp (eid, ename, age, salary) values (664,'HORATIO',35,630000);23: insert into E54Emp (eid, ename, age, salary) values (666,'JONES' ,28,510000);24: insert into E54Emp (eid, ename, age, salary) values (688,'SMITH' ,28,540000);25: insert into E54Emp (eid, ename, age, salary) values (698,'BLAKE' ,31,855000);26: insert into E54Emp (eid, ename, age, salary) values (771,'ZORBA',26,750000);27: insert into E54Emp (eid, ename, age, salary) values (782,'CLARK' ,27,735000);28: insert into E54Emp (eid, ename, age, salary) values (788,'SCOTT' ,45,900000);29: insert into E54Emp (eid, ename, age, salary) values (831,'MADAYAN',21,240000);30: insert into E54Emp (eid, ename, age, salary) values (832,'GULDU',22,270000);31: insert into E54Emp (eid, ename, age, salary) values (839,'KING' ,50,2100000);32: insert into E54Emp (eid, ename, age, salary) values (844,'TURNER' ,26,450000);33: insert into E54Emp (eid, ename, age, salary) values (874,'HORATIO',35,690000);34: insert into E54Emp (eid, ename, age, salary) values (876,'ADAMS' ,25,330000);35: insert into E54Emp (eid, ename, age, salary) values (900,'JAMES' ,22,285000);36: insert into E54Emp (eid, ename, age, salary) values (902,'FORD' ,43,900000);37: insert into E54Emp (eid, ename, age, salary) values (911,'JACOB',23,240000);38: insert into E54Emp (eid, ename, age, salary) values (912,'MICHAEL',24,270000);39: insert into E54Emp (eid, ename, age, salary) values (913,'ETHAN',23,270000);40: insert into E54Emp (eid, ename, age, salary) values (914,'JOSHUA',22,240000);41: insert into E54Emp (eid, ename, age, salary) values (915,'DANIEL',20,270000);42: insert into E54Emp (eid, ename, age, salary) values (916,'ALEXANDER',24,240000);43: insert into E54Emp (eid, ename, age, salary) values (917,'ANTHONY',23,270000);44: insert into E54Emp (eid, ename, age, salary) values (918,'WILLIAM',22,240000);45: insert into E54Emp (eid, ename, age, salary) values (919,'MATTHEW',23,270000);46: insert into E54Emp (eid, ename, age, salary) values (934,'MILLER' ,30,390000);47: insert into E54Emp (eid, ename, age, salary) values (985,'ART',25,450000);48: insert into E54Emp (eid, ename, age, salary) values (990,'ELI',26,480000);49: insert into E54Emp (eid, ename, age, salary) values (992,'KYLE',27,480000);50: insert into E54Emp (eid, ename, age, salary) values (994,'DOMINIC',30,480000);51: insert into E54Emp (eid, ename, age, salary) values (996,'ALEX',32,495000);52:53: insert into E54Works (eid, did, pct_time) values (839,40,100);54: insert into E54Works (eid, did, pct_time) values (108,20,100);55: insert into E54Works (eid, did, pct_time) values (369,10,100);56: insert into E54Works (eid, did, pct_time) values (900,20,100);57: insert into E54Works (eid, did, pct_time) values (499,30,50);58: insert into E54Works (eid, did, pct_time) values (499,40,50);59: insert into E54Works (eid, did, pct_time) values (521,10,30);60: insert into E54Works (eid, did, pct_time) values (521,30,70);61: insert into E54Works (eid, did, pct_time) values (566,10,70);62: insert into E54Works (eid, did, pct_time) values (566,20,30);63: insert into E54Works (eid, did, pct_time) values (654,40,100);64: insert into E54Works (eid, did, pct_time) values (698,10,70);65: insert into E54Works (eid, did, pct_time) values (698,30,30);66: insert into E54Works (eid, did, pct_time) values (782,10,30);67: insert into E54Works (eid, did, pct_time) values (782,20,30);68: insert into E54Works (eid, did, pct_time) values (788,10,80);69: insert into E54Works (eid, did, pct_time) values (788,40,20);70: insert into E54Works (eid, did, pct_time) values (844,30,100);71: insert into E54Works (eid, did, pct_time) values (876,30,70);72: insert into E54Works (eid, did, pct_time) values (876,40,30);73: insert into E54Works (eid, did, pct_time) values (902,30,80);74: insert into E54Works (eid, did, pct_time) values (902,40,20);75: insert into E54Works (eid, did, pct_time) values (934,30,50);76: insert into E54Works (eid, did, pct_time) values (934,20,50);77: insert into E54Works (eid, did, pct_time) values (122,20,100);78: insert into E54Works (eid, did, pct_time) values (229,10,100);79: insert into E54Works (eid, did, pct_time) values (331,10,50);80: insert into E54Works (eid, did, pct_time) values (331,20,50);81: insert into E54Works (eid, did, pct_time) values (432,30,100);82: insert into E54Works (eid, did, pct_time) values (558,40,100);83: insert into E54Works (eid, did, pct_time) values (664,20,100);84: insert into E54Works (eid, did, pct_time) values (771,20,50);85: insert into E54Works (eid, did, pct_time) values (771,10,50);86: insert into E54Works (eid, did, pct_time) values (874,20,70);87: insert into E54Works (eid, did, pct_time) values (874,30,30);88: insert into E54Works (eid, did, pct_time) values (985,10,70);89: insert into E54Works (eid, did, pct_time) values (985,30,30);90: insert into E54Works (eid, did, pct_time) values (195,50,100);91: insert into E54Works (eid, did, pct_time) values (500,20,100);92: insert into E54Works (eid, did, pct_time) values (666,20,100);93: insert into E54Works (eid, did, pct_time) values (688,20,100);94: insert into E54Works (eid, did, pct_time) values (650,20,100);95: insert into E54Works (eid, did, pct_time) values (831,20,100);96: insert into E54Works (eid, did, pct_time) values (832,20,100);97: insert into E54Works (eid, did, pct_time) values (911,20,100);98: insert into E54Works (eid, did, pct_time) values (912,20,100);99: insert into E54Works (eid, did, pct_time) values (913,20,100);100: insert into E54Works (eid, did, pct_time) values (914,20,100);101: insert into E54Works (eid, did, pct_time) values (915,20,100);102: insert into E54Works (eid, did, pct_time) values (916,20,100);103: insert into E54Works (eid, did, pct_time) values (917,20,100);104: insert into E54Works (eid, did, pct_time) values (918,20,100);105: insert into E54Works (eid, did, pct_time) values (191,20,100);106: insert into E54Works (eid, did, pct_time) values (919,20,100);107: insert into E54Works (eid, did, pct_time) values (990,50,100);108: insert into E54Works (eid, did, pct_time) values (992,50,100);109: insert into E54Works (eid, did, pct_time) values (994,50,100);110: insert into E54Works (eid, did, pct_time) values (996,50,100);111:112: insert into E54Dept (did, dname, budget, managerid) values (10,'Hardware',800000 ,788);113: insert into E54Dept (did, dname, budget, managerid) values (20,'Software',1500000 ,108);114: insert into E54Dept (did, dname, budget, managerid) values (30,'business',3000000 ,902);115: insert into E54Dept (did, dname, budget, managerid) values (40,'management',7000000 ,839);116: insert into E54Dept (did, dname, budget, managerid) values (50,'test',5500000 ,108);117:118:119: -- SQL120: -- 1. Print the names and ages of each employee who works in both the Hardware department and the Software department.121: select e.ename, e.age122: from E54Emp e, E54Works w1, E54Works w2, E54Dept d1, E54Dept d2123: where e.eid = w1.eid and w1.did = d1.did and d1.dname = 'Hardware' and e.eid = w2.eid and w2.did = d2.did and d2.dname = 'Software';124:125: -- 2. For each department with more than 20 full-time-equivalent employees (i.e., where the part-time and full-time employees add up to at least that many full-time employees), print the did together with the number of employees that work in that department.126:127: select w.did, COUNT(w.eid)128: from E54Works w129: group by w.did130: having 2000 < (select sum(w1.pct_time) from E54Works w1 where w.did = w1.did);131:132: -- 3. Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.133: select e.ename134: from E54Emp e135: where e.salary > ALL (select d.budget from E54Works w, E54Dept d where w.did = d.did and e.eid = w.eid);136:137: -- 4. Find the managerids of managers who manage only departments with budgets greater than $1 million.138: select distinct d.managerid139: from E54Dept d140: where 1000000 < d.budget;141:142: -- 5. Find the enames of managers who manage the departments with the largest budgets.143: select e.ename144: from E54Emp e, E54Dept d145: where d.managerid = e.eid and d.budget = (select max(d1.budget) from E54Dept d1);146:147: -- 6. If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $5 million.148: select d.managerid149: from E54Dept d150: where 5000000 < (select sum(d1.budget) from E54Dept d1 where d1.managerid = d.managerid);151:152: -- 7. Find the managerids of managers who control the largest amounts.153: select dTemp.managerid154: from ( select distinct d1.managerid, sum(d1.budget) as d1_budget155: from E54Dept d1156: group by d1.managerid ) dTemp157: where dTemp.d1_budget = (select max(d2_budget)158: from ( select distinct d2.managerid, sum(d2.budget) as d2_budget159: from E54Dept d2160: group by d2.managerid ) dTemp);161:162: -- 8. Find the enames of managers who manage only departments with budgets larger than $1 million, but at least one department with budget less than $5 million.163: select e.ename164: from E54Emp e, E54Dept d165: where e.eid = d.managerid and d.budget > 1000000 and 5000000 > any (select d1.budget from E54Dept d1 where d1.did = d.did);166:167: -- C. Find the name of employee whose salary is larger than all his managers.168: select e.ename169: from E54Emp e170: where e.salary > all (select e1.salary from E54Emp e1 where e1.eid in (select d2.managerid from E54Dept d2, E54works w2 where d2.did = w2.did and w2.eid = e.eid));171:172:173: -- Table 제거174: drop table E54Works;175: drop table E54Dept;176: drop table E54Emp;
result.sql
1: SQL> select * from e54emp;2:3: EID ENAME AGE SALARY4: ---------- -------------------- ---------- ----------5: 108 NoSyu 25 16500006: 122 DUSTIN 45 6300007: 191 CHRISTOPER 24 2400008: 195 BOB 63 16800009: 229 BRUTUS 33 24000010: 331 LUBBER 55 96000011: 369 SMITH 23 24000012: 432 ANDY 25 60000013: 499 ALLEN 24 48000014: 500 DAVE 29 54000015: 521 WARD 23 37500016:17: EID ENAME AGE SALARY18: ---------- -------------------- ---------- ----------19: 558 RUSTY 35 75000020: 566 JONES 32 89250021: 650 SMITH 29 57000022: 654 MARTIN 24 37500023: 664 HORATIO 35 63000024: 666 JONES 28 51000025: 688 SMITH 28 54000026: 698 BLAKE 31 85500027: 771 ZORBA 26 75000028: 782 CLARK 27 73500029: 788 SCOTT 45 90000030:31: EID ENAME AGE SALARY32: ---------- -------------------- ---------- ----------33: 831 MADAYAN 21 24000034: 832 GULDU 22 27000035: 839 KING 50 210000036: 844 TURNER 26 45000037: 874 HORATIO 35 69000038: 876 ADAMS 25 33000039: 900 JAMES 22 28500040: 902 FORD 43 90000041: 911 JACOB 23 24000042: 912 MICHAEL 24 27000043: 913 ETHAN 23 27000044:45: EID ENAME AGE SALARY46: ---------- -------------------- ---------- ----------47: 914 JOSHUA 22 24000048: 915 DANIEL 20 27000049: 916 ALEXANDER 24 24000050: 917 ANTHONY 23 27000051: 918 WILLIAM 22 24000052: 919 MATTHEW 23 27000053: 934 MILLER 30 39000054: 985 ART 25 45000055: 990 ELI 26 48000056: 992 KYLE 27 48000057: 994 DOMINIC 30 48000058:59: EID ENAME AGE SALARY60: ---------- -------------------- ---------- ----------61: 996 ALEX 32 49500062:63: 45 개의 행이 선택되었습니다.64:65: SQL> select * from e54works;66:67: EID DID PCT_TIME68: ---------- ---------- ----------69: 839 40 10070: 108 20 10071: 369 10 10072: 900 20 10073: 499 30 5074: 499 40 5075: 521 10 3076: 521 30 7077: 566 10 7078: 566 20 3079: 654 40 10080:81: EID DID PCT_TIME82: ---------- ---------- ----------83: 698 10 7084: 698 30 3085: 782 10 3086: 782 20 3087: 788 10 8088: 788 40 2089: 844 30 10090: 876 30 7091: 876 40 3092: 902 30 8093: 902 40 2094:95: EID DID PCT_TIME96: ---------- ---------- ----------97: 934 30 5098: 934 20 5099: 122 20 100100: 229 10 100101: 331 10 50102: 331 20 50103: 432 30 100104: 558 40 100105: 664 20 100106: 771 20 50107: 771 10 50108:109: EID DID PCT_TIME110: ---------- ---------- ----------111: 874 20 70112: 874 30 30113: 985 10 70114: 985 30 30115: 195 50 100116: 500 20 100117: 666 20 100118: 688 20 100119: 650 20 100120: 831 20 100121: 832 20 100122:123: EID DID PCT_TIME124: ---------- ---------- ----------125: 911 20 100126: 912 20 100127: 913 20 100128: 914 20 100129: 915 20 100130: 916 20 100131: 917 20 100132: 918 20 100133: 191 20 100134: 919 20 100135: 990 50 100136:137: EID DID PCT_TIME138: ---------- ---------- ----------139: 992 50 100140: 994 50 100141: 996 50 100142:143: 58 개의 행이 선택되었습니다.144:145: SQL> select * from e54dept;146:147: DID DNAME BUDGET MANAGERID148: ---------- -------------------- ---------- ----------149: 10 Hardware 800000 788150: 20 Software 1500000 108151: 30 business 3000000 902152: 40 management 7000000 839153: 50 test 5500000 108154:155:156: -- 1. Print the names and ages of each employee who works in both the Hardware department and the Software department.157: SQL> select e.ename, e.age158: 2 from E54Emp e, E54Works w1, E54Works w2, E54Dept d1, E54Dept d2159: 3 where e.eid = w1.eid and w1.did = d1.did and d1.dname = 'Hardware' and e.eid = w2.eid and w2.did = d2.did and d2.dname = 'Software';160:161: ENAME AGE162: -------------------- ----------163: JONES 32164: CLARK 27165: LUBBER 55166: ZORBA 26167:168: -- 2. For each department with more than 20 full-time-equivalent employees (i.e., where the part-time and full-time employees add up to at least that many full-time employees), print the did together with the number of employees that work in that department.169: SQL> select w.did, COUNT(w.eid)170: 2 from E54Works w171: 3 group by w.did172: 4 having 2000 < (select sum(w1.pct_time) from E54Works w1 where w.did = w1.did);173:174: DID COUNT(W.EID)175: ---------- ------------176: 20 26177:178: -- 3. Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.179: SQL> select e.ename180: 2 from E54Emp e181: 3 where e.salary > ALL (select d.budget from E54Works w, E54Dept d where w.did = d.did and e.eid = w.eid);182:183: ENAME184: --------------------185: NoSyu186:187:188: -- 4. Find the managerids of managers who manage only departments with budgets greater than $1 million.189: SQL> select distinct d.managerid190: 2 from E54Dept d191: 3 where 1000000 < d.budget;192:193: MANAGERID194: ----------195: 108196: 839197: 902198:199:200: -- 5. Find the enames of managers who manage the departments with the largest budgets.201: SQL> select e.ename202: 2 from E54Emp e, E54Dept d203: 3 where d.managerid = e.eid and d.budget = (select max(d1.budget) from E54Dept d1);204:205: ENAME206: --------------------207: KING208:209: -- 6. If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $5 million.210: SQL> select d.managerid211: 2 from E54Dept d212: 3 where 5000000 < (select sum(d1.budget) from E54Dept d1 where d1.managerid = d.managerid);213:214: MANAGERID215: ----------216: 108217: 839218: 108219:220: -- 7. Find the managerids of managers who control the largest amounts.221: SQL> select dTemp.managerid222: 2 from ( select distinct d1.managerid, sum(d1.budget) as d1_budget223: 3 from E54Dept d1224: 4 group by d1.managerid ) dTemp225: 5 where dTemp.d1_budget = (select max(d2_budget)226: 6 from ( select distinct d2.managerid, sum(d2.budget) as d2_budget227: 7 from E54Dept d2228: 8 group by d2.managerid ) dTemp);229:230: MANAGERID231: ----------232: 108233: 839234:235: -- 8. Find the enames of managers who manage only departments with budgets larger than $1 million, but at least one department with budget less than $5 million.236: SQL> select e.ename237: 2 from E54Emp e, E54Dept d238: 3 where e.eid = d.managerid and d.budget > 1000000 and 5000000 > any (select d1.budget from E54Dept d1 where d1.did = d.did);239:240: ENAME241: --------------------242: NoSyu243: FORD244:245: -- C. Find the name of employee whose salary is larger than all his managers.246: SQL> select e.ename247: 2 from E54Emp e248: 3 where e.salary > all (select e1.salary from E54Emp e1 where e1.eid in (select d2.managerid from E54Dept d2, E54works w2 where d2.did = w2.did and w2.eid = e.eid));249:250: ENAME251: --------------------252: BOB253:254:
"in Lesson" 카테고리의 다른 글
- 2008년 1학기 심리학 입문 - 시험 대비 문서 (4)2009/10/13
- 2009년 2학기 컴퓨터공학세미나 - 소감문 03 (0)2009/10/11
- 2009년 2학기 문제해결기법 - Report 02 (0)2009/10/11
- 2009년 2학기 데이터베이스 - Report 02 (0)2009/10/11
- 2009년 2학기 데이터베이스 - Report 01 (0)2009/09/28
- 2009년 2학기 문제해결기법 - Report 01 (0)2009/09/18
- 2009년 2학기 컴퓨터공학세미나 - 소감문 02 (2)2009/09/18
글에 잘못된 점, 다른 점, 부족한 점이 있다면 지적해주세요.
댓글, 트랙백, 메일 모두 고맙습니다.
댓글을 달아 주세요