ocp 1Z0-051 1-22题解析
1. View the Exhibit andexamine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES tables. The PROD_ID column isthe foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_IDand TIME_ID columns are a
1. View the Exhibit andexamine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES
tables.
The PROD_ID column isthe foreign key in the SALES table, which references the PRODUCTS table.
Similarly, the CUST_IDand TIME_ID columns are also foreign keys in the SALES table referencing the
CUSTOMERS and TIMEStables, respectively.
Evaluate the followingCREATE TABLE command:
CREATE TABLEnew_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id,time_id
FROM sales;
Which statement is trueregarding the above command?
A. The NEW_SALES tablewould not get created because the DEFAULT value cannot be specified in the
column definition.
B. The NEW_SALES tablewould get created and all the NOT NULL constraints defined on the specified
columns would be passedto the new table.
C. The NEW_SALES tablewould not get created because the column names in the CREATE TABLE
command and the SELECTclause do not match.
D. The NEW_SALES tablewould get created and all the FOREIGN KEY constraints defined on the
specified columns wouldbe passed to the new table.
Answer: B
解析：题目意思是利用：
CREATE TABLEnew_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id,time_id
FROM sales;
创建表能否传递sales表的非空约束限制和外键约束
测试：
create table zbcxy
(
id number(20) not null,
sno number(10) unique,
sex char(2) check (sex in('男','女')),
cno number(6) references student(cno)
);
create table zbdba
as
select * from zbcxy;
erwei@ORCL>desc zbdba;
名称
-------------------------
ID not null number(20)
SNO number(10)
SEX char(2)
CNO number(6)
经过一系列插入数据操作，发现只有非空约束才能传递。
2. View the Exhibit toexamine the description for the SALES table.
Which views can have allDML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * FROM SALES
WHERE time_id
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id,cust_id, time_id FROM SALES
WHERE time_id
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id,cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id
GROUP BY prod_id,cust_id
WITH CHECK OPTION;
Answer: AB
解析：
这道题的意思是哪个视图可以做dml的所有操作，很明显c和d选项是不行的，因为c和d选项创建视图的时候值查询了sales表的部分字段，如果在该视图上做insert操作，即使满足with check option，也无法对原表进行插入，原sales表所有属性列都是为非空的
这里的 with checkoption就是对视图操作的一个条件限制，条件就是创建视图时 where后面的条件，例如 B选项，则对视图做更新操作时，time_id是不能>sysdate-2*365的
3. You need to extractdetails of those products in the SALES table where the PROD_ID column
contains the string'_D123'.
Which WHERE clause couldbe used in the SELECT statement to get the required output?
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 '_'
Answer: B
解析：
如果要查询的信息本身包含”%”或”_”,则可以使用ESCAPE定义一个用于表示转义的字符
例如：
查询名字中包含”_”的员工信息
Select * from emp whereename like ‘’%x_%’ escape ‘x’;
4. Which two statementsare true regarding single row functions? (Choose two.)
A. They a ccept only asingle argument.
B. They c an be nestedonly to two levels.
C. Arguments can only becolumn values or constants.
D. They a lways return asingle result row for every row of a queried table.
E. They c an return adata type value different from the one that is referenced.
Answer: DE
解析：
引用oracle 官方文档 sql reference中对Single-rowfunctions的解释
Single-row functions return a single result row for every row of a queriedtable or
view. These functionscan appear in select lists, WHERE clauses, START WITH and
CONNECT BY clauses, andHAVING clauses
很多单行函数都不止传一个参数，比如数值函数中的ATAN2(m,n),所以a选项错误
单行函数返回的结果也不一定和改行的数据类型相同，比如字符函数中的ASCII(char)
返回的number类型
综上可得 D和E正确
5. Which SQL statementswould display the value 1890.55 as $1,890.55? (Choose three .)
A. SELECTTO_CHAR(1890.55,'$0G000D00')
FROM DUAL;
B. SELECTTO_CHAR(1890.55,'$9,999V99')
FROM DUAL;
C. SELECTTO_CHAR(1890.55,'$99,999D99')
FROM DUAL;
D. SELECTTO_CHAR(1890.55,'$99G999D00')
FROM DUAL;
E. SELECTTO_CHAR(1890.55,'$99G999D99')
FROM DUAL;
Answer: ADE
解析：
scott@ORCL>SELECTTO_CHAR(1890.55,'$0G000D00')
2 FROMDUAL;
TO_CHAR(18
----------
$1,890.55
scott@ORCL>SELECTTO_CHAR(1890.55,'$9,999V99')
2 FROMDUAL;
TO_CHAR(1
---------
$1,89055
scott@ORCL>SELECT TO_CHAR(1890.55,'$99,999D99')
2 FROMDUAL;
SELECTTO_CHAR(1890.55,'$99,999D99')
*
第 1 行出现错误:
ORA-01481: 无效的数字格式模型
scott@ORCL>SELECTTO_CHAR(1890.55,'$99G999D00')
2 FROMDUAL;
TO_CHAR(189
-----------
$1,890.55
scott@ORCL>SELECTTO_CHAR(1890.55,'$99G999D99')
2 FROMDUAL;
TO_CHAR(189
-----------
$1,890.55
格式里面的G代表逗号，D代表为点
V 它的作用在于做一个计算。
例如TO_CHAR(N,'999V9'),以p表示V的位置,则该表达式＝to_char(N×(10的P-1次方)).但是9个数又必须保证大于等于乘积之后表示的位数.
更多格式说明：
http://blog.csdn.net/zbdba/article/details/17042195
6. Examine the structureof the SHIPMENTS table:
name Null Type
PO_ID NOT NULL NUMBER(3)
PO_DATE NOT NULL DATE
SHIPMENT_DATE NOT NULLDATE
SHIPMENT_MODEVARCHAR2(30)
SHIPMENT_COSTNUMBER(8,2)
You want to generate areport that displays the PO_ID and the penalty amount to be paid if the
SHIPMENT_DATE is laterthan one month from the PO_DATE. The penalty is $20 per day.
Evaluate the followingtwo queries:
SQL> SELECT po_id,CASE
WHEN MONTHS_BETWEEN(shipment_date,po_date)>1 THEN
TO_CHAR((shipment_date -po_date) * 20) ELSE 'No Penalty' END PENALTY
FROM shipments;
SQL>SELECT po_id,DECODE
(MONTHS_BETWEEN(po_date,shipment_date)>1,
TO_CHAR((shipment_date- po_date) * 20), 'No Penalty') PENALTY
FROM shipments;
Which statement is trueregarding the above commands?
A. Both executesuccessfully and give correct results.
B. Only the first queryexecutes successfully but gives a wrong result.
C. Only the first queryexecutes successfully and gives the correct result.
D. Only the second queryexecutes successfully but gives a wrong result.
E. Only the second queryexecutes successfully and gives the correct result.
Answer: C
解析：
第一个查询用的 case expressionswhich let you use IF ... THEN ... ELSE logic in SQL statements without
having to invokeprocedures
经过测试，第一个是可以执行成功
第二个查询用的decode,引用官方文档的介绍：
DECODE compares expr toeach search value one by one. If expr is equal to a
search, then OracleDatabase returns the corresponding result. If no match is
found, then Oraclereturns default. If default is omitted, then Oracle returns null.
The arguments can be anyof the numeric types (NUMBER, BINARY_FLOAT, or
BINARY_DOUBLE) orcharacter types.
就是第一个表达式的值和后面的一一匹配，成功就返回匹配成功对应的那个值
经测试，第二表达式是无法执行的，参数个数无效
7. Which two statementsare true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ONclauses can be used for equijoins and nonequijoins.
B. A maximum of one pairof columns can be joined between two tables using the ON clause.
C. The ON clause can beused to join tables on columns that have different names but compatible data
types.
D. The WHERE clause canbe used to apply additional conditions in SELECT statements containing the
ON or the USING clause.
Answer: CD
解析：
引用官方文档中的说明：
ON condition Use the ON clause to specify a joincondition. Doing so lets you specify
join conditions separatefrom any search or filter conditions in the WHERE clause.
USING (column) When you are specifying an equijoin ofcolumns that have the same
name in both tables, theUSING column clause indicates the columns to be used. You
can use this clause onlyif the join columns in both tables have the same name. Within
this clause, do notqualify the column name with a table name or table alias.
8. View the Exhibit andexamine the structure of the CUSTOMERS table.
Which two tasks wouldrequire subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customerswho do not have a credit limit and were born before 1980
B. finding the number ofcustomers, in each city, whose marital status is 'married'
C. finding the averagecredit limit of male customers residing in 'Tokyo' or 'Sydney'
D. listing of thosecustomers whose credit limit is the same as the credit limit of customersresiding in the
city 'Tokyo'
E. finding the number ofcustomers, in each city, whose credit limit is more than the average creditlimit of
all the customers
Answer: DE
解析：
该题的意思是哪些选项需要用子查询或者join连接，很容易看出。
9. Which statement istrue regarding the INTERSECT operator?
A. It ignores NULLvalues.
B. Reversing the orderof the intersected tables alters the result.
C. The names of columnsin all SELECT statements must be identical.
D. The number of columnsand data types must be identical for all SELECT statements in the query.
Answer: D
解析：通过insert规则很容易得到D选项
10. View the Exhibit; examine the structure of the PROMOTIONS table.
Each promotion has a durationof at least seven days .
Your manager has askedyou to generate a report, which provides the weekly cost for each promotion
done to l date.
Which query wouldachieve the required result?
A. SELECT promo_name,promo_cost/promo_end_date-promo_begin_date/7
FROM promotions;
B. SELECTpromo_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;
Answer: D
解析：这个题就是涉及到优先级的问题，括号里面的优先执行
结束日期减去开始日期除以7得到星期数，再用总值除以星期数就是每个星期的
11. View the Exhibit andexamine the structure of the PRODUCTS table.
All products have a listprice.
You issue the followingcommand to display the total price of each product after a discount of 25% anda
tax of 15% are appliedon it. Freight charges of $100 have to be applied to all the products.
SQL>SELECT prod_name,prod_list_price -(prod_list_price*(25/100))
+(prod_list_price-(prod_list_price*(25/100))*(15/100))+100
AS "TOTALPRICE"
FROM products;
What would be theoutcome if all the parenthese s are removed from the above statement?
A. It produces a syntaxerror.
B. The result remainsunchanged.
C. The total price valuewould be lower than the correct value.
D. The total price valuewould be higher than the correct value.
Answer: B
解析：题目的意思是去掉括号会输出什么，去掉括号应该是毫无影响的，因为所有加括号的地方都是乘法或者除法运算，他们谁优先不会影响到结果
12. You need to producea report where each customer's credit limit has been incremented by $1000. In
the output, t hecustomer's last name should have the heading Name and the incremented creditlimit
should be labeled NewCredit Limit. The column headings should have only the first letter of eachword in
uppercase .
Which statement wouldaccomplish this requirement?
A. SELECT cust_last_nameName, cust_credit_limit + 1000
"New CreditLimit"
FROM customers;
B. SELECT cust_last_nameAS Name, cust_credit_limit + 1000
AS New Credit Limit
FROM customers;
C. SELECT cust_last_nameAS "Name", cust_credit_limit + 1000
AS "New CreditLimit"
FROM customers;
D. SELECTINITCAP(cust_last_name) "Name", cust_credit_limit + 1000
INITCAP("NEW CREDITLIMIT")
FROM customers;
Answer: C
解析：
scott@ORCL>selectename Name,sal+1000 "New Credit Litmit" from emp;
NAME New Credit Litmit
scott@ORCL>selectename as "Name",sal+1000 as "New Credit Limit" from emp;
Name New Credit Limit
加上as 才能以规定样式输出，当然双引号也可以：
scott@ORCL>selectename "Name",sal+1000 "New Credit Litmit" from emp;
Name New Credit Litmit
13. View the Exhibit andexamine the structure of the PRODUCTS table.
You need to generate areport in the following format:
CATEGORIES
5MP Digital Photo Camera'scategory is Photo
Y Box's category isElectronics
Envoy Ambassador'scategory is Hardware
Which two queries wouldgive the required output? (Choose two.)
A. SELECT prod_name ||q'' || 's category is ' || prod_category CATEGORIES
FROM products;
B. SELECT prod_name || q'['s] || 'category is ' || prod_categoryCATEGORIES
FROM products;
C. SELECT prod_name || q''s' || 'category is ' || prod_categoryCATEGORIES
FROM products;
D. SELECT prod_name || q'' || 'category is ' || prod_category CATEGORIES
FROM products;
Answer: CD
解析：
scott@ORCL>selectename || q''s' || 'category is ' || sal from emp;
ENAME||Q''S'||'CATEGORYIS'||SAL
----------------------------------------------------------------
SMITH'scategory is 968
ALLEN'scategory is 1600
scott@ORCL>selectename || q'' ||'category is ' || sal from emp;
ENAME||Q''||'CATEGORYIS'||SAL
----------------------------------------------------------------
SMITH'scategory is 968
14. Using the CUSTOMERStable, you need to generate a report that shows 50% of each credit
amount in each incomelevel. The report should NOT show any repeated credit amounts in each income
level.
Which query would givethe required result?
A. SELECTcust_income_level, DISTINCT cust_credit_limit * 0.50
AS "50% CreditLimit"
FROM customers;
B. SELECT DISTINCTcust_income_level, DISTINCT cust_credit_limit * 0.50
AS "50% CreditLimit"
FROM customers;
C. SELECT DISTINCTcust_income_level || ' ' ||cust_credit_limit * 0.50
AS "50%Credit Limit"
FROMcustomers;
D. SELECTcust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit"
FROMcustomers;
Answer: C
解析：主要考察dictinct的应用，去除重复列
15. View the Exhibit andexamine the data in the CUSTOMERS table.
Evaluate the followingquery:
SQL> SELECT cust_nameAS "NAME", cust_credit_limit/2 AS MIDPOINT,MIDPOINT+100 AS "MAX
LOWER LIMIT"
FROM customers;
The above query producesan error on execution.
What is the reason forthe error?
A. An alias cannot beused in an expression.
B. The a lias NAMEshould not be enclosed with in double quotation marks .
C. The MIDPOINT+100expression gives an error because CUST_CREDIT_LIMIT contains NULL
values.
D. The a lias MIDPOINTshould be enclosed with in double quotation marks for the
CUST_CREDIT_LIMIT/2expression .
Answer: A
解析：明显错误，别名是不能用来作为表达式的
16. Evaluate thefollowing query:
SQL> SELECTpromo_name || q'{'s start date was }' ||promo_begin_date
AS "PromotionLaunches"
FROM promotions;
What would be theoutcome of the above query?
A. It produces an errorbecause flower braces have been used.
B. It produces an errorbecause the data types are not matching.
C. It executessuccessfully and introduces an 's at the end of each promo_name in the output.
D. It executessuccessfully and displays the literal " {'s start date was } " foreach row in the output.
Answer: C
解析：
scott@ORCL>SELECTename || q'{'s start date was }' || hiredate
2 AS"Promotion Launches"
3 fromemp;
Promotion Launches
------------------------------------------
SMITH's start date was17-12月-80
ALLEN's start date was20-2月 -81
WARD's start date was22-2月 -81
JONES's start date was02-4月 -81
MARTIN's start date was28-9月 -81
BLAKE's start date was01-5月 -81
CLARK's start date was09-6月 -81
SCOTT's start date was19-4月 -87
KING's start date was17-11月-81
TURNER's start date was08-9月 -81
ADAMS's start date was23-5月 -87
JAMES's start date was03-12月-81
FORD's start date was03-12月-81
MILLER's start date was23-1月 -82
17. View the E xhibitand examine the data in the EMPLOYEES table.
You want to generate areport showing the total compensation paid to each employee to date.
You issue the followingquery:
SQL>SELECT ename|| ' joined on ' || hiredate ||
', the totalcompensation paid is ' ||
TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365)* sal + comm)
"COMPENSATION UNTILDATE"
FROM emp;
What is the outcome?
A. It generates an errorbecause the alias is not valid.
B. It executessuccessfully and gives the correct output.
C. It executessuccessfully but does not give the correct output.
D. It generates an errorbecause the usage of the ROUND function in the expression is not valid.
E. It generates an errorbecause the concatenation operator can be used to combine only two items.
Answer: C
解析：能正确执行，但是不能得到正确的结果
scott@ORCL>
scott@ORCL>SELECTename || ' joined on ' || hiredate ||
2 ',the total compensation paid is ' ||
3 TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm)
4 "COMPENSATION UNTIL DATE"
5 FROMemp;
COMPENSATION UNTIL DATE
--------------------------------------------------------------------
SMITH joined on 17-12月-80, the total compensation paid is
ALLEN joined on 20-2月 -81, the total compensation paid is 52960
WARD joined on 22-2月 -81, the total compensation paid is 41750
JONES joined on 02-4月 -81, the total compensation paid is
MARTIN joined on 28-9月 -81, the total compensation paid is 41400
BLAKE joined on 01-5月 -81, the total compensation paid is
CLARK joined on 09-6月 -81, the total compensation paid is
SCOTT joined on 19-4月 -87, the total compensation paid is
KING joined on 17-11月-81, the total compensation paid is
TURNER joined on 08-9月 -81, the total compensation paid is 48000
ADAMS joined on 23-5月 -87, the total compensation paid is
JAMES joined on 03-12月-81, the total compensation paid is
FORD joined on 03-12月-81, the total compensation paid is
MILLER joined on 23-1月 -82, the total compensation paid is
已选择14行。
发现奖金为空的，最后得不到结果
scott@ORCL>select1000+comm from emp where empno=7369;
1000+COMM
在oracle中如果如果不出来空值，任何数相加都会得到空值
scott@ORCL>select1000+nvl(comm,0) from emp where empno=7369;
1000+NVL(COMM,0)
----------------
1000
18. Examine thestructure of the PROMOTIONS table:
name Null Type
PROMO_ID NOT NULLNUMBER(6)
PROMO_NAME NOT NULLVARCHAR2(30)
PROMO_CATEGORY NOT NULLVARCHAR2(30)
PROMO_COST NOT NULLNUMBER(10,2)
The management wants tosee a report of unique promotion costs in each promotion category.
Which query wouldachieve the required result?
A. SELECT DISTINCTpromo_cost, promo_category FROM promotions;
B. SELECTpromo_category, DISTINCT promo_cost FROM promotions;
C. SELECT DISTINCTpromo_cost, DISTINCT promo_category FROM promotions;
D. SELECT DISTINCTpromo_category, promo_cost FROM promotions ORDER BY 1;
Answer: D
解析：distinct后的字段顺序不同，得到的结果顺序也不同，但是最终的内容都是一样的，所以A和D的差别主要是order by 1的区别，下面我们看看它的作用：
使用前：
scott@ORCL>selectdistinct job,sal from emp;
JOB SAL
--------- ----------
MANAGER 3324.24
SALESMAN 1600
SALESMAN 1250
CLERK 950
MANAGER 2450
ANALYST 3000
PRESIDENT 6300
ANALYST 1850
CLERK 1100
MANAGER 2975
SALESMAN 1500
CLERK 1430
已选择12行。
使用后：
scott@ORCL>selectdistinct job,sal from emp order by 1;
JOB SAL
--------- ----------
ANALYST 1850
ANALYST 3000
CLERK 950
CLERK 1100
CLERK 1430
MANAGER 2450
MANAGER 2975
MANAGER 3324.24
PRESIDENT 6300
SALESMAN 1250
SALESMAN 1500
SALESMAN 1600
已选择12行。
根据题意这里选择D选项更佳
19. Evaluate thefollowing query:
SELECT INTERVAL '300'MONTH,
INTERVAL '54-2' YEAR TOMONTH,
INTERVAL'11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correctoutput of the above query?
A. +25-00 , +54-02, +0011:12:10.123457
B. +00-300, +54-02, +0011:12:10.123457
C. +25-00 , +00-650, +0011:12:10.123457
D. +00-300 , +00-650,+00 11:12:10.123457
Answer: A
解析：
scott@ORCL>SELECTINTERVAL '300' MONTH,
2 INTERVAL '54-2' YEAR TO MONTH,
3 INTERVAL '11:12:10.1234567' HOURTO SECOND
4 FROMdual;
INTERVAL'300'MONTH INTERVAL'54-2'YEARTOMONTH INTERVAL'11:12:10.1234567'HOURTOSECOND
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+25-00 +54-02 +00 11:12:10.123457
scott@ORCL>
官方文档：
INTERVAL YEAR TO MONTHData Type
INTERVAL YEAR TO MONTHstores a period of time using the YEAR and MONTH
datetime fields. Thisdata type is useful for representing the difference between two
datetime values whenonly the year and month values are significant.
Specify INTERVAL YEAR TOMONTH as follows:
INTERVAL YEAR[(year_precision)] TO MONTH
where year_precision isthe number of digits in the YEAR datetime field. The
default value ofyear_precision is 2.
引用网友：
由于MONTH 默认是2为有效位数，所以不能是300，就表示成YEAR TO MONTH 于是成了+25-00
第二个不够2位补0，第三个最后一位默认6位，收上来就是123457了
20. Which threestatements are true regarding the data types in Oracle Database 10g/11g?(Choose
three.)
A. Only one LONG columncan be used per table.
B. A TIMESTAMP data typecolumn stores only time values with fractional seconds.
C. The BLOB data typecolumn is used to store binary data in an operating system file.
D. The minimum columnwidth that can be specified for a VARCHAR2 data type column is one.
E. The value for a CHARdata type column is blank-padded to the maximum defined column width.
Answer: ADE
解析：
引用oracle官方文档：
The use of LONG values is subject to these restrictions:
■ A table can contain only one LONG column.
■ You cannot create anobject type with a LONG attribute.
■ LONG columns cannotappear in WHERE clauses or in integrity constraints (except
that they can appear inNULL and NOT NULL constraints).
……….
The TIMESTAMP data typeis an extension of the DATE data type. It stores the year,
month, and day of theDATE data type, plus hour, minute, and second values. This
data type is useful forstoring precise time values and for collecting and evaluating
date information acrossgeographic regions.
The BLOB data typestores unstructured binary large objects. BLOB objects can be
thought of as bitstreamswith no character set semantics. BLOB objects can store binary
data up to (4 gigabytes-1) * (the value of the CHUNK parameter of LOB storage). If the
tablespaces in yourdatabase are of standard block size, and if you have used the
default value of theCHUNK parameter of LOB storage when creating a LOB column,
then this is equivalentto (4 gigabytes - 1) * (database block size).
The VARCHAR2 data typespecifies a variable-length character string.When you create
a VARCHAR2 column, yousupply the maximum number of bytes or characters of data
that it can hold. Oraclesubsequently stores each value in the column exactly as you
specify it, provided thevalue does not exceed the maximum length of the column. If
you try to insert avalue that exceeds the specified length, then Oracle returns an error
The CHAR data typespecifies a fixed-length character string. Oracle ensures that all
values stored in a CHARcolumn have the length specified by size. If you inserta
value that isshorter than the column length, then Oracle blank-pads the value to
column length. If you try to insert a value that is too long for thecolumn, then Oracle
returns an error.
21. Examine thedescription of the EMP_DETAILS table given below:
name NULL TYPE
EMP_ID NOT NULL NUMBER
EMP_NAME NOT NULLVARCHAR2 (40)
EMP_IMAGE LONG
Which two statements aretrue regarding SQL statements that can be executed on the EMP_DETAIL
table? (Choose two.)
A. An EMP_IMAGE columncan be included in the GROUP BY clause.
B. An EMP_IMAGE columncannot be included in the ORDER BY clause.
C. You cannot add a newcolumn to the table with LONG as the data type.
D. You can alter thetable to include the NOT NULL constraint on the EMP_IMAGE column.
Answer: BC
解析：
引用oracle官方文档：
In addition, LONGcolumns cannot appear in these parts of SQL statements:
■ GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses orwith the
DISTINCToperator in SELECT statements
■ The UNIQUE operator ofa SELECT statement
■ The column list of aCREATE CLUSTER statement
……
因为 EMP_IMAGE 列的数据类型是LONG类型，所以不能做group by操作，也不能做order by 操作
The use of LONG valuesis subject to these restrictions:
■ A table can contain only one LONG column.
■ You cannot create anobject type with a LONG attribute.
■ LONG columns cannot appear in WHERE clauses or in integrityconstraints (except
that they canappear in NULL and NOT NULL constraints).
…………
所以是不能再添加一个数据类型为LONG的列
也不能设置非空约束
22. You need to create atable for a banking application. One of the columns in the table has thefollowing
requirements:
1) You want a column inthe table to store the duration of the credit period.
2) The data in thecolumn should be stored in a format such that it can be easily added andsubtracted
with
DATE data type withoutusing conversion functions.
3) The maximum period ofthe credit provision in the application is 30 days.
4) The interest has tobe calculated for the number of days an individual has taken a credit for.
Which data type wouldyou use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TOSECOND
E. INTERVAL YEAR TOMONTH
Answer: D
解析：
能和date直接做加减的数据类型有date 和INTERVAL DAY TOSECOND
INTERVAL DAY TO SECONDstores a period of time in terms of days, hours, minutes,
and seconds. This datatype is useful for representing the precise difference between
two datetime values.