oracle数据库面试题

b programer no privilege or add table ownerTable extent is full max extend 设置成 unlimited，Table Space is full datafile autoextend 设置成 on

答案如下,

如果不想与另一个表产关联可以直接decode

with a as(

select distinct hci_id, regexp_substr(hstu_ids, '[^,]+', 1, level) as cl1

from pm_ci h

connect by level <=

length(hstu_ids) - length(replace(hstu_ids, ',', '')) + 1), b as (select qstu_id,

max(qstu_name) name

from pm_stu q

group by qstu_id)

select distinct aci_id,

wmsyswm_concat(decode(acl1,

'1',

(select bname

from b

where bstu_id = '1'),

'2',

(select bname

from b

where bstu_id = '2'),

'3',

(select bname

from b

where bstu_id = '3'),

'4',

(select bname

from b

where bstu_id = '4'))) over(partition by ci_id) tt

from a

第二种方法

select a, c

from (with test as (select tci_id a, tstu_ids c from pm_ci t)

select a,

substr(tca,

instr(tca, ',', 1, clv) + 1,

instr(tca, ',', 1, clv + 1) -

(instr(tca, ',', 1, clv) + 1)) AS c

from (select a,

',' || c || ',' AS ca,

length(c || ',') - nvl(length(REPLACE(c, ',')), 0) AS cnt

FROM test) t,

(select LEVEL lv from dual CONNECT BY LEVEL <= 100) c

where clv <= tcnt)

其它的同上面一样,level<=100表示循环次数,这个可以取C的长度除以2

=========================================================================

第三种方法

create or replace function getNote(v_content in varchar2)

return varchar2 is

result varchar2(8000);

cursor res is select from pm_stu;

begin

result:=v_content;

for i in res loop

result:= replace(result,istu_id,istu_name);

end loop;

return result;

end getNote;

调用

select aci_id,getNote(astu_ids) from pm_ci a

a 找出最贵的商品(item)的名称和价格

SELECT

name, price

FROM

item

WHERE

price = ( SELECT MAX(itprice) FROM item it);

b 找出每个月每个商品的销售总金额；

SELECT

TO_CHAR(transactiondate, 'MM') AS 月,

itemname AS 商品名,

SUM( itemSalequantity itemprice ) AS 销售额

FROM

transaction, itemSale, item

WHERE

transactiontransid = itemSaletransid

AND itemSaleitemid = itemitemid

GROUP BY

TO_CHAR(transactiondate, 'MM') ,

itemname

c 找出从来也没有销售过的商品；

SELECT

FROM

item

WHERE

itemid NOT IN ( SELECT DISTINCT itemid FROM itemSale)

-- 上面这个估计执行效率不高

d找出从来也没有买过“酒”的所有客户。

SELECT

FROM

customer

WHERE

customercustid NOT IN

( SELECT transactioncustid

FROM

transaction, itemSale, item

WHERE

transactiontransid = itemSaletransid

AND itemSaleitemid = itemitemid

AND itemname LIKE '%酒%'

)

-- 上面这个估计执行效率也是不高

以上就是关于oracle数据库面试题全部的内容，包括:oracle数据库面试题、oracle数据库一道面试题、求解答:oracle数据库的题目.等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！