数据库作业1.txt

数据库老师布置的一个很有趣的作业

CREATE TABLE ten046
	(tenno	NUMBER	NOT NULL,
	tname		CHAR(10) ,
	neighbor		NUMBER (4),
	livedate	DATE ,
	houserent		NUMBER(10,2),
	section  	char(2));


insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(045,'SMITH',031,'17-10月-2006',3750,'A');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(020,'ALLEN',006,'28-2月-2004',9000,'B');
insert into ten046(tenno,tname,neighbor,livedate,houserent,section)
values(029,'WARD',011,'22-2月-2005',8500,'B');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(004,'JONES',010,'02-4月-2002',6850,'C');
insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(014,'MARTIN',008,'28-9月-2003',5675,'D');
insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(030,'BLAKE',023,'01-5月-2005',4000,'B');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(031,'CLARK',045,'09-6月-2006',7500,'A');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(001,'SCOTT',013,'19-4月-2001',9500,'C');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section)
values(002,'KING',027,'17-11月-2001',2500,'C');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section)
values(009,'TURNER',019,'08-9月-2003',8500,'B');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(008,'ADAMS',014,'23-5月-2003',1900,'D');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(046,'JAMES',026,'03-12月-2006',2500,'A');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section)
values(040,'FORD',032,'03-12月-2005',1500,'A');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section)
values(028,'MILLER',020,'23-1月-2005',3250,'B');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section)
values(025,'GREEN',012,'24-7月-2004',8500,'C');

insert into ten046(tenno,tname,neighbor,livedate,houserent,section)
values(003,'STEVENS',033,'14-1月-2002',4750,'A');
insert into ten046(tenno,tname,neighbor,livedate,houserent,section) 
values(005,'BARNES',044,'16-1月-2002',1950,'A');


CREATE TABLE SECT046
(section CHAR(10),
sname  CHAR(10),
landlord   CHAR(10));


insert into SECT046(section,sname,landlord) 
values('A','WONDERFUL','MARY');

insert into SECT046(section,sname,landlord)  
values('B','ROSEPARK' ,'WILSON');
insert into SECT046(section,sname,landlord)
values('C','XINGHAI ','BILL');

insert into  SECT046(section,sname,landlord)values('D','WONDERFUL','MARY');
insert into SECT046(section,sname,landlord) 
values('E','XINGHAI', 'BILL');




CREATE TABLE LOC046
(SNAME CHAR(10),
loc   CHAR(10));


insert into LOC046(sname,loc)  
values('WONDERFUL ','HOUHAI');
insert into LOC046(sname,loc)  
values('ROSEPARK ','SHEKOU');
insert into LOC046(sname,loc)  
values('XINGHAI ','QIANHAI');
insert into LOC046(sname,loc)  
values('BLUEMOON ','NANTOU');


(1)SELECT tname,neighbor FROM ten046
WHERE TO_CHAR(liveDATE,'DAY')='星期二'
AND SECTION ='A'

(2)SELECT TNAME,
next_day(last_day(NEXT_day(ADD_MONTHS(LIVEdate,1),7))-7,7)
FROM TEN046,SECT046
WHERE TEN046.SECTION=SECT046.SECTION
AND LANDLORD='WILSON'
GROUP BY next_day(last_day(NEXT_day(ADD_MONTHS(LIVEdate,1),7))-7,7),TNAME 



(3)SELECT TENNO,TNAME ,12*HOUSERENT FROM TEN046
    WHERE TEN046.SECTION =(
SELECT  SECTION  FROM SECT046,LOC046
  WHERE  SECT046.SNAME=LOC046.SNAME
AND LOC='SHEKOU')
GROUP BY TENNO,TNAME,12*HOUSERENT
HAVING 12*HOUSERENT>100000