Oracle最新的Sql筆試題及答案

部門表(SM_DEPT)

使用者部門中間表(SM_USER_DEPT)

使用者表(SM_USER)

客戶資訊表(CTM_CUSTOMERS)

訂單頭表(PIM_HEADERS)

訂單行表(PIM_LINES)

問題

1.編寫SQL語句，查詢PIM_HEADERS表，符合日期在2011-03-01和2011-03-25之間的PI訂單，顯示訂單号，建立日志，訂單客戶名稱，訂單業務員名稱

select 
    t1.PI_NO
    ,t1.CREATION_DATE
    ,t2.CUSTOM_NAME
    ,t3.USER_NAME
from (select 
        * 
       from PIM_HEADERS
       where CREATION_DATE between 
                 to_date('2011-03-01','yyyy-mm-dd')
             and
                  to_date('2011-03-25','yyyy-mm-dd')
        ) t1
   left join CTM_CUSTOMERS t2
        on t1.CUSTOMER_ID = t2.CUSTOMER_ID
   left join SM_USER t3
        on t1.OPERATOR_ID = t3.USER_ID      

2.編寫SQL語句，查詢所有部門名稱及其給部門的業務員的個數，部門在2011-01-01到2011-03-01之間建立的PI訂單數

select t1._DEPARTMENT_NAME,count(t2.USER_NAME),count(t3.PI_NO)
from SM_DEPT t1
  left join SM_USER_DEPT t2
     on t1.dept_id = t2.dept_id
  left join (select 
              * 
             from PIM_HEADERS
             where CREATION_DATE between 
                       to_date('2011-01-01','yyyy-mm-dd')
                   and
                        to_date('2011-03-01','yyyy-mm-dd')
              ) t3
     on t2.user_id = t3.OPERATOR_ID    
group by t1.DEPT_ID      

3.編寫SQL語句，查詢所有客戶及其相關PI訂單号，PI建立日期，PI訂單相關業務員姓名及其業務所屬部門名稱

select
      t1.*
      ,t2.PI_NO
      ,t2.CREATION_DATE
      ,t3.USER_NAME
      ,t5.DEPARTMENT_NAME
from SM_CUSTOMERS t1
   left join PIM_HEADERS t2
     on t1.CUSTOER_ID = t2.CUSTOMER_ID
   left join SM_USER t3
     on t2.OPERATOR_ID = t3.user_id
   left join SM_USER_DEPT t4
     on t3.USER_ID = t4.USER_ID
   left join SM_DEPT t5
     on t4.DEPT_ID = t5.DEPT_ID      

4.編寫SQL語句，更新沒有下過PI訂單的客戶名稱的備注為“從未發生業務往來”

update CTM_CUSTOMERS t1 set COMMENTS = '從未反生業務往來'
 where not exists (
       select PI_NO from PIM_HEADERS t2
        where t2.CUSTOMER_ID = t1.CUSTOMER_ID
 ) ;
 commit;      

5.編寫SQL語句，查詢所有客戶名稱及其所屬城市，并按照城市名稱降序，使用者名稱升序排序

select CUSTOM_NAME,CITY
 from CTM_CUSTOMERS
 order by city desc,CUSTOMER_NAME asc      

6.編寫SQL語句，查詢出所有的客戶的PI訂單的下單金額，顯示客戶名稱，訂單币種，訂單總數量及訂單總額

select
       t2.CUSTOMER_NAME
       ,t1.CURRENCY_CODE
       ,t3.TOTAL
       ,t4.TOTAL_MONEY
 from PIM_HEADERS t1
    left join CTM_CUSTOMERS t2
        on t1.CUSTOMER_ID = t2.CUSTOMER_ID
   left join (
        select PI_ID,sum(QUANTITY) TOTAL,sum(QUANTITY*PRICE) TOTAL_MONEY
        from PIM_LINES 
        group by PI_ID
        
        ) t3
        on t1.PI_ID = t3.PI_ID;      

7.編寫函數，函數需傳入部門ID，傳回結果部門對應所有業務員姓名連接配接起來，中間用逗号隔開

create or replace function getUserByDeptId
       ( dept_id number) return varchar2
 is
       res varchar2(200);
        cursor mycursor(did number) is
             select t2.CUSTOMER_NAME
             from (select * 
                  from SM_USER_DEPT
                  where dept_id = did
                  ) t1
               left join SM_USER t2
               on t1.USER_ID = t2.USER_ID ;
 
 
 begin
     for c_row in mycursor(dept_id) loop
         res := res ||','|| c_row
     end loop;
     return res;
 end;