Oracle面試題

標籤：

題目：有一張老師表T，欄位是T_ID，T_NAME；有一張學生表S，欄位是S_ID，S_NAME；還有一張班級表C，欄位是T_ID，S_ID，C_NAME，其中C_NAME的取值只有‘大班’和‘小班’，請查詢出合格老師的名字，條件是老師在大班中帶的學生數大於老師在小班中帶的學生數

--建立T表

create table T(t_id number(4) primary key,t_name varchar2(20)); 

--建立S表

create table S(s_id number(4) primary key,s_name varchar2(20));

--建立C表

 create table C( t_id number(4) references T(t_id), s_id number(4) references S(s_id), c_name char(6) check(c_name in (‘大班‘,‘小班‘)));

--插入資料

--T表 insert into T values(1,‘A‘); insert into T values(2,‘B‘);--S表 insert into S values(11,‘1A‘); insert into S values(12,‘1B‘); insert into S values(13,‘1C‘); insert into S values(14,‘1D‘); insert into S values(15,‘1E‘); insert into S values(16,‘1F‘); insert into S values(17,‘1G‘); insert into S values(18,‘1H‘);--C表 insert into C values(1,11,‘大班‘); insert into C values(1,12,‘大班‘); insert into C values(1,17,‘大班‘); insert into C values(1,16,‘大班‘); insert into C values(1,13,‘小班‘); insert into C values(1,14,‘小班‘); insert into C values(1,15,‘小班‘); insert into C values(2,18,‘小班‘);

--SQL語句

select   t_name from t where t_id in (      select distinct c1.t_id from c c1      where        (select count(*) from c c2                where c2.t_id = c1.t_id and c2.c_name=‘大班‘)       >(select count(*) from c c3                where c3.t_id = c1.t_id and c3.c_name=‘小班‘))

--查詢結果

T_NAME--------------------A

 

Oracle面試題