(Do this question using a subquery) – List cleaners by name who belong to the same depot as Betty– do not include Betty in your answer.
用subquery的方法把所有跟Betty同属于一个车间的清洁工的名字列出来,不要包含Betty的名字在内Create table Cleaner
(cno varchar2(5),
cname varchar2(20),
csalary number(6,2),
dno varchar2(5),
constraint pk_cno primary key(cno),
constraint fk_dno1 foreign key(dno) references Depot(dno) );Create table Depot
(dno varchar2(5),
dname varchar2(20),
daddress varchar2(20),
constraint pk_dno primary key(dno) );=====================================================================
--load Depot with data insert into Depot values
('101','Holloway','Camden Road');insert into Depot values
('102','Hornsey','High Road');insert into Depot values
('104','Islington','Upper Street');=====================================================================
--load Cleaner with data insert into Cleaner values
('110','John',2550,'101');insert into Cleaner values
('111','Jean',2500,'101');insert into Cleaner values
('112','Betty',2400,'102');insert into Cleaner values
('113','Vince',2800,'102');insert into Cleaner values
('114','Jay',3000,'102');insert into Cleaner values
('115','Doug',2000,'102');insert into Cleaner values
('116','Geeta',4000,'');
用subquery的方法把所有跟Betty同属于一个车间的清洁工的名字列出来,不要包含Betty的名字在内Create table Cleaner
(cno varchar2(5),
cname varchar2(20),
csalary number(6,2),
dno varchar2(5),
constraint pk_cno primary key(cno),
constraint fk_dno1 foreign key(dno) references Depot(dno) );Create table Depot
(dno varchar2(5),
dname varchar2(20),
daddress varchar2(20),
constraint pk_dno primary key(dno) );=====================================================================
--load Depot with data insert into Depot values
('101','Holloway','Camden Road');insert into Depot values
('102','Hornsey','High Road');insert into Depot values
('104','Islington','Upper Street');=====================================================================
--load Cleaner with data insert into Cleaner values
('110','John',2550,'101');insert into Cleaner values
('111','Jean',2500,'101');insert into Cleaner values
('112','Betty',2400,'102');insert into Cleaner values
('113','Vince',2800,'102');insert into Cleaner values
('114','Jay',3000,'102');insert into Cleaner values
('115','Doug',2000,'102');insert into Cleaner values
('116','Geeta',4000,'');
from Cleaner a,Depot b
where a.dno=b.dno and a.cname<>'Betty'
group by b.bname
;
from Cleaner a
innerjoin Depot b on b.dno = a.dno and b.dno = 'Betty'
where not exists (select 1 from Depot where dname = 'Betty' and dno = a.dno)
select a.cname
from Cleaner a
where a.dno = (select dno from Cleaner where cname = 'Betty')
and a.cname <> 'Betty' ;