Oracle数据库考试试题库

PROG_ID NOT NULL NUMBER(3) PROG_COST NUMBER(8,2) START_DATE NOT NULL DATE END_DATE DATE

下列哪两个SQL语句能执行成功？（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结构： name Null Type

INV_NO NOT NULL NUMBER(3) INV_DATE DATE

INV_AMT NUMBER(10,2)

下列哪两个SQL语句能执行成功？（ 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 8000 20000

30000 30000

哪两个表达式能得到一样的结果？（ 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的结果：

哪两个SQL语句能执行成功？（ 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结构： name Null Type

----------------------------------------------------------- STUDENT_ID NOT NULL VARCHAR2(4) STUDENT_NAME VARCHAR2(25) SUBJECT1 NUMBER(3) SUBJECT2 NUMBER(3) SUBJECT3 NUMBER(3)

哪两个SQL语句能执行成功？（ CD ） A. SELECT student_name,subject1 FROM marks

WHERE subject1 > AVG(subject1);

B. SELECT student_name,SUM(subject1) FROM marks

WHERE student_name LIKE 'R%';

C. SELECT SUM(subject1+subject2+subject3) FROM marks

WHERE student_name IS NULL;

D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks

WHERE subject1 > subject2;

51. 查看表PROMOTIONS结构，检查下列SQL语句：

SQL>SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead

FROM promotions

WHERE UPPER(promo_category) IN ('TV', 'INTERNET','POST') GROUP BY Avg_Cost

ORDER BY Avg_Overhead;

此SQL查询在执行时会生成一个错误。哪个是原因导致这个错误？（ C ） A. WHERE B. SELECT C. GROUP BY D. ORDER BY

52. 查看表CUSTOMERS的数据： CUSTNO CUSTNAME CITY

------------------------------------------------ 1 KING SEATTLE 2 GREEN BOSTON 3 KOCHAR SEATTLE 4 SMITH NEW YORK

你想要显示多个客户的相关详细信息的所有城市，查看下列查询： SQL>SELECT c1.custname, c1.city

FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname); 哪两个JOIN选项能得到正确的输出？（ AE ） A. JOIN

B. NATURAL JOIN C. LEFT OUTER JOIN D. FULL OUTER JOIN E. RIGHT OUTER JOIN

53. 查看表CUSTOMERS, SALES, 和COUNTRIES，你需要生成一个报表用来显示所有的

城市 名及相关的所有客户（如果有）和销售明细（如果有）。 哪个FROM子句能得到需要的结果？（ 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. 哪两个关于子查询描述正确的？（ AD ） A. 一个子查询能返回0或多行 B. 只有二个子查询能放在同一层。

C. 一个子查询只能用于SQL查询语句。 D. 子查询可以出现在一个比较操作符的两边

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结构： name Null Type

PROD_ID NOT NULL NUMBER(4) PROD_NAME VARCHAR2(20) PROD_STATUS VARCHAR2(6) QTY_IN_HAND NUMBER(8,2) UNIT_PRICE NUMBER(10,2)

你想显示总价最高的产品的名称。哪个SQL语句能得到正确的结果？（ A ） A. SELECT prod_name FROM products

WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);

B. SELECT prod_name FROM products

WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products

GROUP BY prod_name); C. SELECT prod_name FROM products

GROUP BY prod_name

HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products

GROUP BY prod_name); D. SELECT prod_name FROM products

WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products)

GROUP BY prod_name;

57. 查看表PROMOTIONS的下列数据：