Oracle最新的Sql筆試題及答案

部門表(SM_DEPT)

字段名稱	資料類型	是否主鍵	注釋
DEPT_ID	NUMBER	Y	部門ID
PARENT_DEPARTMENT_ID	NUMBER	N	上級部門
DEPARTMENT_NAME	VARCHAR2(50)	N	部門名稱

使用者部門中間表(SM_USER_DEPT)

字段名稱	資料類型	是否主鍵	注釋
ID	NUMBER	Y	主鍵
DEPT_ID	NUMBER	N	部門ID
USER_ID	NUMBER	N	使用者ID

使用者表(SM_USER)

字段名稱	資料類型	是否主鍵	注釋
USER_ID	NUMBER	Y	使用者ID
USER_NAME	VARCHAR2(50)	N	使用者名稱
LOGON_NAME	VARCHAR2(50)	N	登入名
IS_SALES	VARCHAR2(1)	N	是否為業務員

客戶資訊表(CTM_CUSTOMERS)

字段名稱	資料類型	是否主鍵	注釋
CUSTOMER_ID	NUMBER	Y	客戶ID
CUSTOMER_NAME	VARCHAR2(50)	N	客戶姓名
CITY	VARCHAR2(50)	N	所屬城市
STATE	VARCHAR2(25)	N	所屬州
ZIP_CODE	VARCHAR2(10)	N	郵政編碼
CONTACT_NAME	VARCHAR2(50)	N	聯系人
ADDRESS	VARCHAR2(50)	N	聯系位址
TYPE	VARCHAR2(50)	N	客戶類型
COMMENTS	VARCHAR2(100)	N	備注

訂單頭表(PIM_HEADERS)

字段名稱	資料類型	是否主鍵	注釋
PI_ID	NUMBER	Y	訂單ID
PI_NO	VARCHAR2(50)	N	訂單号
CREATION_DATE	DATE	N	建立日期
CUSTOMER_ID	NUMBER	N	關聯客戶表的客戶ID
OPERATOR_ID	NUMBER	N	關聯使用者表的使用者ID
CURRENCY_CODE	VARCHAR2(50)	N	訂單币種
EXCHAHGE_RATE	NUMBER	N	匯率

訂單行表(PIM_LINES)

字段名稱	資料類型	是否主鍵	注釋
PI_ID	NUMBER	N	訂單ID
PI_LINES_ID	VARCHAR2(50)	Y	訂單行ID
PRODUCT_CODE	NUMBER	N	産品編碼
QUANTITY	NUMBER	N	産品數量
PRICE	NUMBER	N	産品單價

問題

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;

posted on

2019-03-11 12:44

波波烤鴨

閱讀(3298)

評論(0)

編輯

舉報

Oracle最新的Sql筆試題及答案 - 波波烤鴨