学号2005151046姓名帅盼班级软件1班.docVIP

学号：2005151046 姓名：帅盼 班级：软件1班 PART1（TABLES AND QUESTIONS） TEN: TENNO TNAME NEIGHBOR LIVEDATE HOUSERENT SECTION 45 SMITH 31 17-10月-06 3750 A 20 ALLEN 6 28-2月 -04 9000 B 29 WARD 11 22-2月 -05 8500 B 4 JONES 10 02-4月 -02 6850 C 14 MARTIN 8 28-9月 -03 5675 D 30 BLAKE 23 01-5月 -05 4000 B 31 CLARK 45 09-6月 -06 7500 A 1 SCOTT 13 19-4月 -01 9500 C 2 KING 27 17-11月-01 2500 C 9 TURNER 19 08-9月 -03 8500 B 8 ADAMS 14 23-5月 -03 1900 D 46 JAMES 26 03-12月-06 2500 A 40 FORD 32 03-12月-05 1500 A 28 MILLER 20 23-1月 -05 3250 B 25 GREEN 12 24-7月 -04 8500 C 3 STEVENS 33 14-1月 -02 4750 A 5 BARNES 44 16-1月 -02 1950 A SECT: SECTION SNAME LANDLORD A WONDERFUL MARY B ROSEPARK WILSON C XINGHAI BILL D WONDERFUL MARY E XINGHAI BILL LOC: SNAME LOC WONDERFUL HOUHAI ROSEPARK SHEKOU XINGHAI QIANHAI BLUEMOON NANTOU PS: TEN:=TENANT TENNO:=TENANT NUMBER SECT:=SECTION SNAME:=SECTION NAME LOC:=LOCTION PART2（QUESTIONS AND ANSWERS） (1)who lived in sectionA on a Tuesday and show his neighbor.(20%) SELECT tname,neighbor FROM ten046（5%） WHERE TO_CHAR(liveDATE,DAY)=星期二（10%） AND SECTION =A（5%） TNAME NEIGHBOR SMITH 31 (2)Who paid the house rent to WILSON and when they paid the first rent(first rent was paid on the last Saturday of each month),group by the first day tenants paid their house rent. (40%) SELECT TNAME,（5%） next_day(last_day(NEXT_day(ADD_MONTHS(LIVEdate,1),7))-7,7) FROM TEN046,SECT046（15%） WHERE TEN046.SECTION=SECT046.SECTION AND LANDLORD=WILSON（10%） GROUP BY next_day(last_day(NEXT_day(ADD_MONTHS(LIVEdate,1),7))-7,7),TNAME （10%） TNAME NEXT_DAY(LAST_ TURNER 25-10月-03 ALLEN 24-4月 -04 MILLER 26-2月 -05 WARD 26-3月 -05 BLAKE 25-6月 -05 ’ numbers,names who lived in SHEKOU ,and Compute the annual house rent