Oracle數(shù)據(jù)庫考試試題庫【教學試題】
1. 啟動數(shù)據(jù)庫的命令（ A ）。A.startupB.startup openC.shutdownD.startup database2. （ C ）是啟動數(shù)據(jù)庫時用來記錄數(shù)據(jù)文件、聯(lián)機日志文件的相關(guān)信息的二進制文件。A.數(shù)據(jù)文件B.參數(shù)文件C.控制文件D.歸檔文件3. 你需要在表SALES里查找一些產(chǎn)品明細，其中PROD_ID列包括字符'_D123'。（ B ）WHERE子句條件能得到這些相符產(chǎn)品。A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'B. WHERE prod_id LIKE '%_D123%' ESCAPE ''C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'D. WHERE prod_id LIKE '%_D123%' ESCAPE '_'4. 關(guān)于單行函數(shù)，哪一個描述是正確的？（ D ）A.他們只能接受一個參數(shù)。B.他們只能嵌套兩層。C.參數(shù)只能是字段值或常量。D.他們始終為查找表中的每個行返回一個值。5. 下面哪個SQL語句顯示1890.55 as $1,890.55？（ ADE ）選三項A. SELECT TO_CHAR(1890.55,'$0G000D00')FROM DUAL;B. SELECT TO_CHAR(1890.55,'$9,999V99')FROM DUAL;C. SELECT TO_CHAR(1890.55,'$99,999D99')FROM DUAL;D. SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;E. SELECT TO_CHAR(1890.55,'$99G999D99')FROM DUAL;6. 下列關(guān)于ORACLE的to_date函數(shù)和to_char函數(shù)，描述正確的是（ BC ）選兩項A.to_char函數(shù)是將字符串數(shù)據(jù)轉(zhuǎn)換為日期數(shù)據(jù)B.to_date函數(shù)是將字符串數(shù)據(jù)轉(zhuǎn)換為日期數(shù)據(jù)C.to_char函數(shù)是將日期數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)D.to_date函數(shù)是將日期數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)7. 在表emp里，計算員工的年薪SAL*12并生成別名Annual Salary。（ B ）A.SELECT ename,sal*12, Annual Salary FROM emp;B.SELECT ename,sal*12 "Annual Salary " FROM emp;C.SELECT ename,sal*12 AS Annual Salary FROM emp;D.SELECT ename,sql*12 AS INICTAP("Annual Salary ") FROM emp;8. 查看promotion（促銷表），每個促銷周期為7天。你的經(jīng)理要你生成一個報表，提供每周的促銷成本。（ D ）A. SELECT promo_name, promo_cost/promo_end_date-promo_begin_date/7 FROM promotions;B. SELECT promo_name,(promo_cost/promo_end_date-promo_begin_date)/7 FROM promotions;C. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date/7) FROM promotions;D. SELECT promo_name, promo_cost/(promo_end_date-promo_begin_date)/7) FROM promotions;9. 你需要為每個客戶的信息額度增加$1000。在輸出時，客戶的last name列頭應為Name,cust_credit_limit的列頭為New Credit Limit。（ C ）A. SELECT cust_last_name Name, cust_credit_limit + 1000"New Credit Limit"FROM customers;B. SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000AS "New Credit Limit"FROM customers;D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW CREDIT LIMIT") FROM customers;10. 查看表PRODUCTS。你需要生成一個報表顯示如下格式：CATEGORIES5MP Digital Photo Camera's category is PhotoY Box's category is ElectronicsEnvoy Ambassador's category is Hardware哪兩個查詢可以獲得需要的輸出?（ CD ）A. SELECT prod_name | q'''s category is ' | prod_category CATEGORIES FROM products;B. SELECT prod_name | q''s 'category is ' | prod_category CATEGORIES FROM products;C. SELECT prod_name | q''s ' | ' category is ' | prod_category CATEGORIES FROM products;D. SELECT prod_name | q'<'s >' | 'category is ' | prod_category CATEGORIES FROM products;11. 下面查詢：SQL> SELECT cust_name AS "NAME", cust_credit_limit/2 AS MIDPOINT,MIDPOINT+100 AS "MAX LOWER LIMIT"FROM customers;上面查詢在執(zhí)行時發(fā)生錯誤，這個錯誤的原因是什么？（ A ）A.一個別名不能使用在表達式中。B.一個別名NAME沒有一個隔開的雙引號。C.MIDPOINT+100是錯誤的因為CUST_CREDIT_LIMIT包含了NULL值。D.為CUST_CREDIT_LIMIT/2表達式取一個別名MIDPOINT應用雙引號隔開。12. 你想去生成一個報表顯示每個員工從入職以來的總的薪金。你執(zhí)行下列查詢：SQL>SELECT ename ' joined on ' hiredate', the total compensation paid is 'TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm)"COMPENSATION UNTIL DATE"FROM employees;輸出結(jié)果：哪個說法是正確的？（ C ）A.此查詢執(zhí)行錯誤是因為別名無效的。B.此查詢能正確執(zhí)行并得到正確的結(jié)果。C.此查詢能正確執(zhí)行但不能得到正確結(jié)果。D.此查詢執(zhí)行錯誤是因為ROUND函數(shù)是無效的。13. 當你執(zhí)行下列查詢：SQL> select e.empno, e.ename, d.loc 2 from emp e, dept d 3 where e.deptno = d.deptno 4 and substr(e.ename, 1, 1) = S;下列哪個語句是Oracle數(shù)據(jù)庫中可用的ANSI兼容等價語句？（ A ）Aselect empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1) = S; Bselect empno, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1) = S;Cselect empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename, 1, 1) = S;Dselect empno, ename, loc from emp left join dept on emp.deptno = dept.deptno and substr(emp.ename, 1, 1) = S;14. 你要操縱Oracle數(shù)據(jù)，下列哪個不是SQL命令？（ B ）Aselect * from dual; Bset defineCupdate emp set ename = 6543 where ename = SMITHERS;Dcreate table employees(empid varchar2(10) primary key);SQL *Plus中發(fā)出的下列語句： SQL> select ceil(-97.342), 2 floor(-97.342), 3 round(-97.342), 4 trunc(-97.342) 5 from dual;15. 下列哪個函數(shù)不返回結(jié)果-97？( B )Aceil() Bfloor() Cround() Dtrunc()16. 查看下面PROMOTIONS表結(jié)構(gòu)：name Null TypePROMO_ID NOT NULL NUMBER(6)PROMO_NAME NOT NULL VARCHAR2(30)PROMO_CATEGORY NOT NULL VARCHAR2(30)PROMO_COST NOT NULL NUMBER(10,2管理層想看到每種促銷種類的唯一促銷成本。下面哪個查詢得到需要的結(jié)果？（ D ）A. SELECT DISTINCT promo_cost, promo_category FROM promotions;B. SELECT promo_category, DISTINCT promo_cost FROM promotions;C. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;17. 在Oracle數(shù)據(jù)庫10g/11g里哪三個關(guān)于數(shù)據(jù)類型描述正確的是？（ ADE ）A.每個表里只能有一個LONG字段。B.一個TIMESTAMP數(shù)據(jù)類型字段只能存儲帶秒的時間值。C.BLOB數(shù)據(jù)類型字段用于在操作系統(tǒng)文件里存儲二進制數(shù)據(jù)。D.可以為一個VARCHAR2數(shù)據(jù)類型列中指定的最小列寬為1E.一個CHAR數(shù)據(jù)類型列中的值是空格填充到最大定義的列寬18. 查看下面表EMP_DETAILS結(jié)構(gòu)：name NULL TYPEEMP_ID NOT NULL NUMBEREMP_NAME NOT NULL VARCHAR2 (40)EMP_IMAGE LONG19. 哪兩個SQL語句在表EMP_DETAILS執(zhí)行是正確的？（ BC ）A.一個EMP_IMAGE字段能包含在GROUY BY子句。B.一個EMP_IMAGE字段不能包含在ORDER BY子句。C.你不能在表中增加新的LONG字段。D.您可以更改表，包括對EMP_IMAGE列的NOT NULL約束。20. 檢查下列表結(jié)構(gòu)：name Null TypeTRANS_ID NOT NULL NUMBER(6)CUST_NAME NOT NULL VARCHAR2(20)CUST_STATUS NOT NULL CHARTRANS_DATE NOT NULL DATETRANS_VALIDITY VARCHAR2CUST_CREDIT_LIMIT NUMBER在上面的表結(jié)構(gòu)中創(chuàng)建和存儲中哪些說法是正確的？（ BC ）選二項A.CUST_STATUS列將給出錯誤。B.TRANS_VALIDITY列將給出錯誤。C.CUST_STATUS列能存儲一個字符。D.CUST_CREDIT_LIMIT列不能存儲有小數(shù)位的值。E.TRANS_VALIDITY列最大長度為一個字符。F.TRANS_DATE列能存儲天、月、世紀、年、小時、分、秒和秒的小數(shù)部分。21. 查看下面表TRANSACTIONS結(jié)構(gòu)：name Null Type TRANS_ID NOT NULL NUMBER(6)CUST_NAME NOT NULL VARCHAR2(20)CUST_STATUS NOT NULL VARCHAR2TRANS_DATE NOT NULL DATETRANS_VALIDITY INTERVAL DAY TO SECONDCUST_CREDIT_VALUE NUMBER(10)22. 在上面的表結(jié)構(gòu)中創(chuàng)建和存儲中哪些說法是正確的？（ BC ）選二項A.TRANS_DATE列只允許存儲格式為dd-mon-yyyy的日期。B.CUST_CREDIT_VALUE列允許存儲正負整數(shù)。C.TRANS_VALIDITY列允許存儲日、小時、分和秒間隔時間。D.CUST_STATUS列允許存儲最大為4000字符。23. 下列哪個CREATE TABLE語法是有效的？（ A ）A. CREATE TABLE emp9$# (emp_no NUMBER (4);B. CREATE TABLE 9emp$# (emp_no NUMBER(4);C. CREATE TABLE emp*123 (emp_no NUMBER(4);D. CREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);24. 哪兩個關(guān)于約束正確的說法？（ BD ）選二項A.外鍵不能包含NULL值。B.一個有UNIQUE約束的字段能包含NULL值。C.約束是只對一個表上的INSERT操作執(zhí)行。D.約束能被禁用即使約束字段包含數(shù)據(jù)。E.所有的約束可以在列級定義，也可以在表級別定義。25. 評估下列CREATE TABLE命令：CREATE TABLE orders(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,ord_date DATE,cust_id NUMBER(4);CREATE TABLE ord_items(ord_no NUMBER(2),item_no NUMBER(3),qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),expiry_date date CHECK (expiry_date > SYSDATE),CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no);當執(zhí)行上述命令時失敗，原因是什么？（ A ）A.SYSDATE不能用于CHECK約束。B.BETWEEN子句不能用于CHECK約束。C.CHECK約束不能放在具有DATE數(shù)據(jù)類型的列。D.ORD_NO和ITEM_NO不能用于復合主鍵因為ORD_NO也是外鍵。26. 評估下列SQL命名：SQL>CREATE SEQUENCE ord_seqINCREMENT BY 10START WITH 120MAXVALUE 9999NOCYCLE;SQL>CREATE TABLE ord_items(ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL,item_no NUMBER(3),qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),expiry_date date CHECK (expiry_date > SYSDATE),CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no);上述命令建立表失敗，哪兩個SQL語法失?。浚?AC ）A.你在CHECK約束的條件下不能使用SYSDATE。B.你在CHECK約束條件下不能使用BETWEEN。C.你不能使用NEXTVAL序列值作為列的DEFAULT值。D.ORD_NO和ITEM_NO不能用于復合主鍵因為ORD_NO也是外鍵。27. 哪個CREATE TABLE語法是有效的？（ D ）A. CREATE TABLE ord_details(ord_no NUMBER(2) PRIMARY KEY,item_no NUMBER(3) PRIMARY KEY,ord_date DATE NOT NULL);B. CREATE TABLE ord_details(ord_no NUMBER(2) UNIQUE, NOT NULL,item_no NUMBER(3),ord_date DATE DEFAULT SYSDATE NOT NULL);C. CREATE TABLE ord_details(ord_no NUMBER(2) ,item_no NUMBER(3),ord_date DATE DEFAULT NOT NULL,CONSTRAINT ord_uq UNIQUE (ord_no),CONSTRAINT ord_pk PRIMARY KEY (ord_no);D. CREATE TABLE ord_details(ord_no NUMBER(2),item_no NUMBER(3),ord_date DATE DEFAULT SYSDATE NOT NULL,CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no);28. 你用下列命令刪除PRODUCTS表：SQL>DROP TABLE products;下列哪三個說法正確的？（ ABD ）A.表的結(jié)構(gòu)及所有數(shù)據(jù)都刪除。B.在會話中未提交的事務被提交。C.表對應的所有的索引將保留但他們變?yōu)闊o效。D.所有的視圖和同義詞將保留但他們變?yōu)闊o效。E.所有的數(shù)據(jù)被刪除但是表結(jié)構(gòu)將保留。29. 檢查下列CREATE SEQUENCE語法：CREATE SEQUENCE seq1START WITH 100INCREMENT BY 10MAXVALUE 200CYCLENOCACHE;當SEQ1序列生成到最大值為200。你執(zhí)行下列SQL語法：SELECT seq1.nextval FROM dual;此SELECT語法顯示什么？( A )A.1B.10C.100D. 出現(xiàn)錯誤30. 下面哪兩個關(guān)于索引說法正確？（ AD ）A.他們可以建立在表和聚簇上。B.他們可以建立在表上和簡單視圖上。C.你可以使用相同的列創(chuàng)建唯一索引。D.如果你指定相同列的不同組合可以建立多個索引。31. 檢查下列SQL語法：用PRODUCTS表，你需要生成名字，當前價格列表，及打折扣25%后小于$10的所有的這些產(chǎn)品。SQL>SELECT prod_name, prod_list_price,prod_list_price - (prod_list_price * .25) "DISCOUNTED_PRICE"FROM productsWHERE discounted_price < 10;此查詢執(zhí)行時發(fā)生一個錯誤。此錯誤的原因是什么？( C )A.括號應包含整個表達式。B.雙引號應該從列別名被刪除。C.在WHERE子句的表達式應被替換。D.列別名應為大寫且在WHERE子句中用雙引號括起來。32. 查看表PROMOTIONS表，PROMO_BEGIN_DATE是存儲默認的日期，格式為dd-mon-rr.你需要查看一個報表包括表promos 所有的name,cost,start date。你能用哪個語法顯示下列內(nèi)容？（ D ）A. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category = 'post' AND promo_begin_date < '01-01-00'B. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000'C. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00'D. SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00'33. 查看PART_CODE字段包括下列內(nèi)容：PART_CODE-A%_WQ123A%BWQ123AB_WQ123檢查下列查詢：SQL> SELECT part_code FROM spares WHERE part_code LIKE '%_WQ12%' ESCAPE ''哪個語句的輸入是正確的？( D )A. 此語句錯誤.B. 此語句顯示所有的值.C. 此語句只顯示 A%_WQ123 and AB_WQ123 .D. 此語句只顯示A%_WQ123 and A%BWQ123 .E. 此語句只顯示A%BWQ123 and AB_WQ123.34. 查看PROMOTIONS表結(jié)構(gòu)，你需要生成符合1.促銷名不能以'T'或'N'開頭。2.促銷成本應大于$2000。3.促銷結(jié)束時間大于2001年1月1日。哪個WHERE子句能得到正確的結(jié)果？（ C ）A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')35. 查看表PROMOTIONS中的PROMO_CATEGORY 和 PROMO_COST字段，檢查下列查詢語句：SQL>SELECT DISTINCT promo_category to_char(promo_cost)"code"FROM promotionsORDER BY code;SQL>SELECT DISTINCT promo_category promo_cost "code"FROM promotionsORDER BY 1;上述SQL語句說法是正確的是？（ B ） A. 只有第一個查詢執(zhí)行成功.B. 只有第二個查詢執(zhí)行成功.C. 兩個查詢執(zhí)行成功但得到不同的結(jié)果。but give different results.D. 兩個查詢執(zhí)行成功但得到相同的結(jié)果.36. 查看表CUSTOMERS，你需要生成一個報表顯示客戶的明細并按城市降序排序和收入水平由高到底排序。哪個查詢能完成這個任務？（ A ）A. SELECT cust_city, cust_income_level, cust_last_nameFROM customersORDER BY cust_city desc, cust_income_level DESC ;B. SELECT cust_city, cust_income_level, cust_last_nameFROM customersORDER BY cust_income_level desc, cust_city DESC;C. SELECT cust_city, cust_income_level, cust_last_nameFROM customersORDER BY (cust_city, cust_income_level) DESC;D. SELECT cust_city, cust_income_level, cust_last_nameFROM customersORDER BY cust_city, cust_income_level DESC;37. 哪些任務能用SQL函數(shù)完成？（ ABC ）選三項A.顯示非默認格式的日期B.查找字符串的個數(shù)C.用指定的字符串替換表達式的字符串D.兩個以上的列或表達式組合成在一個列并輸出38. 在表PRODUCTS中有下列數(shù)據(jù)：PROD_ID PROD_LIST_PRICE-123456 152525.99你執(zhí)行下列查詢：SQL> SELECT RPAD( ROUND(prod_list_price), 10,'*')FROM productsWHERE prod_id = 123456;輸出的結(jié)果正確的是（ A ）？A. 152526 *B. *152525.99C. 152525* *D. an error message39. 在表CUSTOMER中你需要顯示first name中包括'e'字符且最后第二個字符是'a'的所有的客戶。下列哪個能得到需要的結(jié)果？（ A ）A. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, -2, 1)='a'B. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>'' ANDSUBSTR(cust_first_name, -2, 1)='a'C. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')IS NOT NULL ANDSUBSTR(cust_first_name, 1,-2)='a'D. SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name),-2)='a'40. 檢查下列查詢：SQL> SELECT INITCAP(cust_first_name | ' ' | UPPER(SUBSTR(cust_city,-LENGTH(cust_city),2)FROM customersWHERE cust_first_name = 'ABIGAIL'哪個輸出是正確的？（ B ）A. Abigail PAB. Abigail PaC. Abigail ISD. an error message41. 查看下列查詢：SQL> SELECT TRUNC(ROUND(156.00,-1),-1) FROM DUAL;哪個輸出是正確的？（ C ）A. 16B. 100C. 160D. 200E. 15042. 查看表CUSTOMER的CUST_NAME字段的值：CUST_NAME-Renske LadwigJason MallinSamuel McCainAllan MCEwenIrene MikkilineniJulia Nayer你需要顯示的客戶，其中第二名稱以“Mc”或“MC ”。哪個查詢能得到正確的結(jié)果？（ B ）A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)='Mc'B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE 'Mc%'C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customersWHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1) = INITCAP('MC%');43. 查看表CUST_TRANS的結(jié)構(gòu)和數(shù)據(jù)：CUST_TRANS- Name Null TypeCUSTNO NOT NULL CHAR(2)TRANSDATE DATETRANSAMT NUMBER(6,2)CUSTNO TRANSDATE TRANSAMT-11 01-JAN-07 100022 01-FEB-07 200033 01-MAR-07 3000日期存儲的默認格式為dd-mon-rr。哪個SQL語句能執(zhí)行成功？（ ACD ）選三項A. SELECT transdate + '10' FROM cust_trans;B. SELECT * FROM cust_trans WHERE transdate = '01-01-07'C. SELECT transamt FROM cust_trans WHERE custno > '11'D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07'E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;44. 你需要計算從2007年1月1日至今天的天數(shù)？日期的默認存儲格式為dd-mon-rr。下列哪兩個SQL能得到正確的結(jié)果？（ BC ）選二項A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL;B. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL;D. SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL;E. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL;45. 下列哪兩個SQL語句能執(zhí)行成功？（ AB ）A. UPDATE promotionsSET promo_cost = promo_cost+ 100WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000'B. SELECT promo_begin_dateFROM promotionsWHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98'C. UPDATE promotionsSET promo_cost = promo_cost+ 100WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8);D. SELECT TO_CHAR(promo_begin_date,'dd/month')FROM promotionsWHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98');46. 查看表PROGRAMS結(jié)構(gòu)：name Null TypePROG_ID NOT NULL NUMBER(3)PROG_COST NUMBER(8,2)START_DATE NOT NULL DATEEND_DATE DATE下列哪兩個SQL語句能執(zhí)行成功？（AD）A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)FROM programs;B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE)FROM programs;C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date),'Ongoing') FROM programs;47. 查看表INVOICE結(jié)構(gòu)：name Null TypeINV_NO NOT NULL NUMBER(3)INV_DATE DATEINV_AMT NUMBER(10,2)下列哪兩個SQL語句能執(zhí)行成功？（ AC ）A. SELECT inv_no,NVL2(inv_date,'Pending','Incomplete')FROM invoice;B. SELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')FROM invoice;C. SELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)FROM invoice;D. SELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')FROM invoice;48. 查看表PRODUCTS的LIST_PRICE和MIN_PRICE字段：LIST_PRICE MIN_PRICE-10000 80002000030000 30000哪兩個表達式能得到一樣的結(jié)果？（ BD ）A. NVL(NULLIF(list_price, min_price), 0)B. NVL(COALESCE(list_price, min_price), 0)C. NVL2(COALESCE(list_price, min_price), min_price, 0)D. COALESCE(NVL2(list_price, list_price, min_price), 0)49. 查看表INVOICE的結(jié)果：哪兩個SQL語句能執(zhí)行成功？（ BD ）A. SELECT AVG(inv_date )FROM invoice;B. SELECT MAX(inv_date),MIN(cust_id)FROM invoice; C. SELECT MAX(AVG(SYSDATE - inv_date)FROM invoice;D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt)FROM invoice;50. 查看表MARKS結(jié)構(gòu)：name Null Type-STUDENT_ID NOT NULL VARCHAR2(4)STUDENT_NAME VARCHAR2(25)SUBJECT1 NUMBER(3)SUBJECT2 NUMBER(3)SUBJECT3 NUMBER(3)哪兩個SQL語句能執(zhí)行成功？（ CD ）A. SELECT student_name,subject1FROM marksWHERE subject1 > AVG(subject1);B. SELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE 'R%'C. SELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;D. SELECT SUM(DISTINCT NVL(subject1,0), MAX(subject1)FROM marksWHERE subject1 > subject2;51. 查看表PROMOTIONS結(jié)構(gòu)，檢查下列SQL語句：SQL>SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_OverheadFROM promotionsWHERE UPPER(promo_category) IN ('TV', 'INTERNET','POST')GROUP BY Avg_CostORDER BY Avg_Overhead;此SQL查詢在執(zhí)行時會生成一個錯誤。哪個是原因?qū)е逻@個錯誤？（ C ）A. WHEREB. SELECTC. GROUP BYD. ORDER BY52. 查看表CUSTOMERS的數(shù)據(jù)：CUSTNO CUSTNAME CITY- 1 KING SEATTLE2 GREEN BOSTON3 KOCHAR SEATTLE4 SMITH NEW YORK你想要顯示多個客戶的相關(guān)詳細信息的所有城市，查看下列查詢：SQL>SELECT c1.custname, c1.cityFROM Customers c1 _ Customers c2ON (c1.city=c2.city AND c1.custname<>c2.custname);哪兩個JOIN選項能得到正確的輸出？（ AE ）A. JOINB. NATURAL JOINC. LEFT OUTER JOIND. FULL OUTER JOINE. RIGHT OUTER JOIN53. 查看表CUSTOMERS, SALES, 和COUNTRIES，你需要生成一個報表用來顯示所有的城市 名及相關(guān)的所有客戶（如果有）和銷售明細（如果有）。哪個FROM子句能得到需要的結(jié)果？（ C ）A. FROM sales JOIN customers USING (cust_id)FULL OUTER JOIN countries USING (country_id);B. FROM sales JOIN customers USING (cust_id)RIGHT OUTER JOIN countries USING (country_id);C. FROM customers LEFT OUTER JOIN sales USING (cust_id)RIGHT OUTER JOIN countries USING (country_id);D. FROM customers LEFT OUTER JOIN sales USING (cust_id)LEFT OUTER JOIN countries USING (country_id);54. 哪兩個關(guān)于子查詢描述正確的？（ AD ）A.一個子查詢能返回0或多行B.只有二個子查詢能放在同一層。C.一個子查詢只能用于SQL查詢語句。D.子查詢可以出現(xiàn)在一個比較操作符的兩邊E.在SELECT的WHERE子句中子查詢層是沒有限制的。55. 子查詢能在哪里使用？（ ABCF ）選四項A. 在SELECT語句的字段名B. 在SELECT語句的FROM子句C. 在SELECT語句的 HAVING 子句D. 在SELECT語句的 GROUP BY 子句E. 只在在SELECT語句的 WHERE 子句F. 在所有DML語句里的SELECT的WHERE子句56. 查看表PRODUCTS結(jié)構(gòu)：name Null TypePROD_ID NOT NULL NUMBER(4)PROD_NAME VARCHAR2(20)PROD_STATUS VARCHAR2(6)QTY_IN_HAND NUMBER(8,2)UNIT_PRICE NUMBER(10,2)你想顯示總價最高的產(chǎn)品的名稱。哪個SQL語句能得到正確的結(jié)果？（ A ）A. SELECT prod_nameFROM productsWHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);B. SELECT prod_nameFROM productsWHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM productsGROUP BY prod_name);C. SELECT prod_nameFROM productsGROUP BY prod_nameHAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM productsGROUP BY prod_name);D. SELECT prod_nameFROM productsWHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand) FROM products)GROUP BY prod_name;
個人主頁