OCP—051试题

isgood

　　FROM:
　　http://blog.itpub.net/26736162/viewspace-1252569/?page=2
　　http://blog.iyunv.com/elearnings/article/details/10103611
1.
　　
　　View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES tables. The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively. Evaluate the following CREATE TABLE command:
　　Which statement is true regarding the above command? （B）
　　

CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE) AS 　　SELECT prod_id, cust_id, time_id FROM sales;

　　A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
　　B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
　　C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
　　D.The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.

CREATE TABLE T2 　　( 　　Col1 NUMBER(2) PRIMARY KEY, 　　Col2 VARCHAR2(10) 　　); 　　CREATE TABLE T3 AS SELECT * FROM T2;

隐式声明的NOT NULL约束（例如主键的NOT NULL约束）不会继承。
（上面的例子能说明能说明这点？）　　
　　Creating a Table Using a Subquery
　　Create a table and insert rows by combining the CREATE
　　TABLE statement and the AS subquery option.
　　CREATE TABLE table
　　[(column, column...)]
　　AS subquery;
　　Match the number of specified columns to the number of subquery columns.
　　Define columns with column names and default values.
　　Guidelines
　　The table is created with the specified column names, and the rows retrieved by the
　　SELECT statement are inserted into the table.
　　The column definition can contain only the column name and default value.
　　If column specifications are given, the number of columns must equal the number of
　　columns in the subquery SELECT list.
　　If no column specifications are given, the column names of the table are the same as the column
　　names in the subquery.
　　The column data type definitions and the NOT NULL constraint are passed to(被传递) the new table.
　　Note that only the explicit(显式的) NOT NULL constraint will be inherited. The PRIMARY KEY column will
　　not pass the NOT NULL feature to the new column. Any other constraint rules are not passed to
　　the new table. However, you can add constraints in the column definition.
　　
　　
2
　　
　　View the Exhibit to examine the description for the SALES table.

--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   why？ 　　 　　 4 　　Which two statements are true regarding single row functions? (Choose two.) （DE） 　　A. They accept only a single argument.（明细错误） 　　B. They can be nested only to two levels.（单行函数嵌套任意层数，分组函数最多可以嵌套2层） 　　C. Arguments can only be column values or constants.（函数返回值也行） 　　D. They always return a single result row for every row of a queried table. 　　E. They can return a data type value different from the one that is referenced. 　　 　　A function is a program written to optionally accept input parameters, perform an operation, or 　　return a single value. A function returns only one value per execution. 　　Three important components form the basis of defining a function. The first is the input parameter 　　list. It specifies zero or more arguments that may be passed to a function as input for processing. 　　These arguments or parameters may be of differing data types, and some are mandatory while 　　others may be optional. The second component is the data type of its resultant value. Upon 　　execution, only one value is returned by the function. The third encapsulates （封装）the details of the 　　processing performed by the function and contains the program code that optionally manipulates（操作） 　　the input parameters, performs calculations and operations, and generates a return value. 　　 5 　　 　　Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three) [table]

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
　　
　　Examine the structure of the SHIPMENTS（出货量） table：

name Null Type 　　PO_ID NOT NULL NUMBER(3) 　　PO_DATE NOT NULL DATE 　　SHIPMENT_DATE NOT NULL DATE 　　SHIPMENT_MODE VARCHAR2(30) 　　SHIPMENT_COST NUMBER(8,2)

　　You want to generate a report that displays the PO_ID and the penalty amount to be paid if the SHIPMENT_DATE is later than one month from the PO_DATE. The penalty is $20 per day. Evaluate the following two queries:

SELECT po_id, 　　CASE WHEN MONTHS_BETWEEN (shipment_date,po_date) > 1 THEN TO_CHAR((shipment_date - po_date) * 20) ELSE 'No Penalty' ENDPENALTY 　　FROM shipments; 　　 　　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 true regarding the above commands?
　　A. Both execute successfully and give correct results.
　　B. Only the first query executes successfully but gives a wrong result.
　　C. Only the first query executes successfully and gives the correct result.
　　D. Only the second query executes successfully but gives a wrong result.
　　E. Only the second query executes successfully and gives the correct result.
　　DECODE函数在此不适用。

　　
　　The MONTHS_BETWEEN(date 1, date 2) function returns the number of
　　months between two dates:
　　months_between('01-FEB-2008','01-JAN-2008') = 1
　　The DECODE Function
　　Although its name sounds mysterious, this function is straightforward. The DECODE function
　　implements if-then-else conditional logic by testing its first two terms for equality and returns the
　　third if they are equal and optionally returns another term if they are not.
　　DECODE Function
　　Facilitates conditional inquiries by doing the work of a CASE expression or an
　　IF-THEN-ELSE statement:
　　DECODE(col|expression, search1, result1
　　[, search2, result2,...,]
　　[, default])
　　DECODE Function
　　The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is
　　used in various languages. The DECODE function decodes expression after comparing it to each
　　search value. If the expression is the same as search, result is returned.
　　If the default value is omitted, a null value is returned where a search value does not match anyof
　　the result values
　　
7
　　
　　Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
　　A. Both USING and ON clauses can be used for equijoins and nonequijoins.
　　B. A maximum of one pair of columns can be joined between two tables using the ON clause.
　　C. The ON clause can be used to join tables on columns that have different names but compatible data types.
　　D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
　　A. USING只能用于等值连接。
　　B. 使用ON子句进行连接时，可以指定多个列对。
　　
　　Creating Joins with the USING Clause
　　If several columns have the same names but the data types do not mat ch, use
　　the USING clause to specify the columns for the equijoin.
　　Use the USING clause to match only one column when more than one column
　　matches.
　　The NATURAL JOIN and USING clauses are mutually exclusive
　　Using Table Aliases with the USING clause
　　When joining with the USING clause, you cannot qualify a column that is used in
　　the USING clause itself. Furthermore, if that column is used anywhere in the SQL
　　statement, you cannot alias it. For example, in the query mentioned in the slide,
　　you should not alias the location_id column in the WHERE clause because the
　　column is used in the USING clause.
　　The columns that are referenced in the USING clause should not have a qualifier
　　(table name oralias) anywhere in the SQL statement.
　　Creating Joins with the ON Clause
　　The join condition for the natural join is basically an equijoin of all columns with
　　the same name.
　　Use the ON clause to specify arbitrary conditions or specify columns to join. –
　　ANSWER C
　　The join condition is separated from other search conditions. ANSWER D
　　The ON clause makes code easy to understand.
　　
8
　　
　　View the Exhibit and examine the structure of the CUSTOMERS table.
　　Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)

　　A. listing of customers who do not have a credit limit and were born before 1980
　　B. finding the number of customers, in each city, whose marital status is 'married'
　　C. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
　　D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'
　　E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
　　答案选择最长的2个就OK

A SELECT * FROM customers WHERE cust_credit_limit IS NULL AND cust_year_of_birth select * from student1 　　2 intersect 　　3 select * from student2; 　　 　　ID NAME 　　---------- ---------- 　　1001 Student1 　　1003

　　
　　INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and
　　removing duplicates.
　　The columns in the queries that make up a compound query can have different names, but the
　　output result set will use the names of the columns in the first query
　　
10
　　
　　View the Exhibit; examine the structure of the PROMOTIONS table.
　　Each promotion has a duration of at least seven days .
　　Your manager has asked you to generate a report, which provides the weekly cost for each promotion done to l date.
　　Which query would achieve the required result?

　　

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;

　　
　　
11
　　
　　View the Exhibit and examine the structure of the PRODUCTS table. All products have a list price. You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of $100 have to be applied to all the products.

SELECT prod_name, prod_list_price -(prod_list_price*(25/100)) +(prod_list_price -(prod_list_price*(25/100))*(15/100))+100 　　AS "TOTAL PRICE" 　　FROM products;

　　What would be the outcome if all the parentheses are removed from the above statement?

　　A. It produces a syntax error.
　　B. The result remains unchanged.
　　C. The total price value would be lower than the correct value.
　　D. The total price value would be higher than the correct value.
　　
　　解释：这道题说sql中所有的圆括号去掉之后对结果有没有影响，小学生的问题
　　SELECT prod_name,
　　prod_list_price - (prod_list_price * (25 / 100)) +(prod_list_price - (prod_list_price * (25 / 100)) * (15 / 100)) + 100 AS "TOTAL PRICE"
　　FROM products;
　　
　　
12
　　
　　You need to produce a report where each customer's credit limit has been incremented by $1000. In the output, the customer's last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase .
　　Which statement would accomplish this requirement?

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 + 1000 AS New Credit Limit FROM customers; 　　C. SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers; 　　D. SELECT INITCAP(cust_last_name) "Name", cust_credit_limit + 1000 INITCAP("NEW CREDIT LIMIT") FROM customers;

　　
　　A也正确
　　A column alias:
　　- Renames a column heading
　　- Is useful with calculations
　　- Immediately follows the column name (There can also be the optional AS
　　keyword between the column name and the alias.)
　　- Requires double quotation marks if it contains spaces or special characters, or
　　if it is case-sensitive
　　
13
　　
　　View the Exhibit and examine the structure of the PRODUCTS table. You need to generate a report in the following format:
　　CATEGORIES
　　5MP Digital Photo Camera's category is Photo Y Box's category is Electronics
　　Envoy Ambassador's category is Hardware
　　Which two queries would give 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_category CATEGORIES FROM products; 　　C. SELECT prod_name || q'\'s\' ' || category is ' || prod_category CATEGORIES FROM products; 　　D. SELECT prod_name || q'. 　　In the example shown, the string contains a single quotation mark, which is 　　normally interpreted as a delimiter of a character string. By using the q operator, 　　however, brackets [] are used as the quotation mark delimiters. The string 　　between the brackets delimiters is interpreted as a literal character string. 　　 14 　　 　　Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result? [table]

A.SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers; 　　B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers; 　　C.SELECT DISTINCT cust_income_level || cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers; 　　D. SELECT cust_income_level || cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;

　　
　　解释：
　　A. DISTINCT关键字只能出现在第一列之前。
　　B. 一个SELECT语句上只能出现一个DISTINCT关键字
　　
　　Unless you indicate otherwise, SQL displays the results of a query without
　　eliminating the duplicate rows.
　　To eliminate duplicate rows in the result, include the DISTINCT keyword in the
　　SELECT clause immediately after the SELECT keyword.
　　You can specify multiple columns after the DISTINCT qualifier. The DISTINCT
　　qualifier affects all the selected columns, and the result is every distinct
　　combination of the columns.
　　
15
　　
　　View the Exhibit and examine the data in the CUSTOMERS table.
　　Evaluate the following query:

SELECT cust_name AS "NAME", 　　cust_credit_limit/2 AS MIDPOINT, 　　MIDPOINT + 100 AS "MAX LOWER LIMIT" 　　FROM customers;

　　The above query produces an error on execution. What is the reason for the error?

　　A. An alias cannot be used in an expression.
　　B. The alias NAME should not be enclosed with in double quotation marks .
　　C. The MIDPOINT+100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values.
　　D. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression .
　　
16
　　
　　Evaluate the following query:

SELECT promo_name || q'{'s start date was }' || promo_begin_date 　　AS "Promotion Launches" 　　FROM promotions;

　　What would be the outcome of the above query?
　　A. It produces an error because flower braces have been used.
　　B. It produces an error because the data types are not matching.
　　C. It executes successfully and introduces an 's at the end of each promo_name in the output.
　　D. It executes successfully and displays the literal " {'s start date was } " for each row in the output.
　　
　　So, how are words that contain single quotation marks dealt with? There are essentially two
　　mechanisms available. The most popular of these is to add an additional single quotation mark
　　next to each naturally occurring single quotation mark in the character string
　　Oracle offers a neat way to deal with this type of character literal in the form of the alternative
　　quote (q) operator. Notice that the problem is that Oracle chose the single quote characters as
　　the special pair of symbols that enclose or wrap any other character literal. These
　　character-enclosing symbols could have been anything other than single quotation marks.
　　Bearing this in mind, consider the alternative quote (q) operator. The q operator enables you to
　　choose from a set of possible pairs of wrapping symbols for character literals as alternatives to
　　the single quote symbols. The options are any single-byte or multibyte character or the four
　　brackets: (round brackets), {curly braces}, [squarebrackets], or . Using the q
　　operator, the character delimiter can effectively be changed from a single quotation mark to any
　　other character
　　The syntax of the alternative quote operator is as follows:
　　q'delimiter'character literal which may include the single quotes delimiter' where delimiter can
　　be any character or bracket.
　　Alternative Quote (q) Operator
　　Specify your own quotation mark delimiter.
　　Select any delimiter.
　　Increase readability and usability.
　　SELECT department_name || q'[ Department's Manager Id: ]'
　　|| manager_id
　　AS "Department and Manager"
　　FROM departments;
　　Alternative Quote (q) Operator
　　Many SQL statements use character literals in expressions or conditions. If the
　　literal itself contains a single quotation mark, you can use the quote (q) operator
　　and select your own quotation mark delimiter.
　　You can choose any convenient delimiter, single-byte or multibyte, or any of the
　　following character pairs: [ ], { }, ( ), or < >.
　　In the example shown, the string contains a single quotation mark, which is
　　normally interpreted as a delimiter of a character string. By using the q operator,
　　however, brackets [] are used as the quotation mark delimiters. The string
　　between the brackets delimiters is interpreted as a literal character string.
　　
17
　　
　　View the Exhibit and examine the data in the EMPLOYEES table.
　　You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:

SELECT ename || ' joined on ' || hiredate || 　　', the total compensation paid is ' || 　　TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm) 　　"COMPENSATION UNTIL DATE" 　　FROM employees;

　　What is the outcome?

　　A. It generates an error because the alias is not valid.
　　B. It executes successfully and gives the correct output.
　　C. It executes successfully but does not give the correct output.
　　D. It generates an error because the usage of the     ROUND function in the expression is not valid.
　　E. It generates an error because the concatenation operator can be used to combine only two items.
　　解释：有些记录的comm为空。
　　ROUND(column|expression, n) Rounds the column, expression, or value to n
　　decimal places or, if n is omitted, no decimal places (If n is negative, numbers to
　　the left of decimal point are rounded.)
　　
18
　　
　　Examine the structure of the PROMOTIONS table:

name Null Type 　　PROMO_ID NOT NULL NUMBER(6) 　　PROMO_NAME NOT NULL VARCHAR2(30) 　　PROMO_CATEGORY NOT NULL VARCHAR2(30) 　　PROMO_COST NOT NULL NUMBER(10,2)

　　The management wants to see a report of unique promotion costs in each promotion category. Which query would achieve the required result?

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;

　　
19
　　
　　Evaluate the following query:

SELECT INTERVAL '300' MONTH, INTERVAL '54-2' YEAR TO MONTH, 　　INTERVAL '11:12:10.1234567' HOUR TO SECOND 　　FROM dual;

　　What is the correct output of the above query?
　　A. +25-00 , +54-02, +00 11:12:10.123457
　　B. +00-300, +54-02, +00 11:12:10.123457
　　C. +25-00 , +00-650, +00 11:12:10.123457
　　D. +00-300 , +00-650, +00 11:12:10.123457
　　SQL Language Reference->Literal
　　Datetime Data Types
　　You can use several datetime data types:
　　INTERVAL YEAR TO MONTH
　　Stored as an interval of years and months
　　INTERVAL DAY TO SECOND
　　Stored as an interval of days, hours, minutes,and seconds
　　给点例子：

时间间隔字面量	说明
INTERVAL '3' DAY	时间间隔为3天
INTERVAL '2' HOUR	时间间隔为2小时
INTERVAL '25' MINUTE	时间间隔为25分钟
INTERVAL '45' SECOND	时间间隔为45秒
INTERVAL '3 2' DAY TO HOUR	时间间隔为3天零2小时
INTERVAL '3 2:25' DAY TO MINUTE	时间间隔为3天零2小时25分
INTERVAL '3 2:25:45' DAY TO SECOND	时间间隔为3天零2小时25分45秒
INTERVAL '123 2:25:45.12' DAY(3) 　　TO SECOND(2)	时间间隔为123天零2小时25分45.12秒； 天的精度是3位数字，秒的小数部分的精度是2位数字
INTERVAL '3 2:00:45' DAY TO SECOND	时间间隔为3天2小时0分45秒
INTERVAL '-3 2:25:45' DAY TO SECOND	时间间隔为负数，值为3天零2小时25分45秒
INTERVAL '1234 2:25:45' DAY(3) 　　TO SECOND	时间间隔无效，因为天的位数超过了指定的精度3
INTERVAL '123 2:25:45.123' DAY 　　TO SECOND(2)	时间间隔无效，因为秒的小数部分的位数超过了指定的精度2

　　
　　
20
　　
　　Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
　　A. Only one LONG column can be used per table.
　　B. A TIMESTAMP data type column stores only time values with fractional seconds.
　　C. The BLOB data type column is used to store binary data in an operating system file.
　　D. The minimum column width that can be specified for a VARCHAR2 data type column is one.
　　E. The value for a CHAR data type column is blank-padded to the maximum defined column width.■ LONG Character data in the database character set, up to 2GB. All the functionality of LONG
　　(and more) is provided by CLOB; LONGs should not be used in a modern database, and if your
　　database has any columns of this type they should be converted to CLOB. There can only be
　　one LONG column in a table.
　　DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the
　　database character set. The VARCHAR2 data type must be qualified with a number indicating the
　　maximum length of the column. If a value is inserted into the column that is less than this, it is not
　　a problem: the value will only take up as much space as it needs. If the value is longer than this
　　maximum, the INSERT will fail with an error.
　　VARCHAR2(size)
　　Variable-length character data (A maximum size must be specified: minimum size is 1;
　　maximum size is 4,000.)
　　BLOB Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.
　　BFILE A locator pointing to a file stored on the operating system of the database server. The size
　　of the files is limited to 4GB.
　　TIMESTAMP This is length zero if the column is empty, or up to 11 bytes, depending on the
　　precision specified. Similar to DATE, but with precision of up to 9 decimal places for the seconds,
　　6 places by default.
　　
21
　　
　　Examine the description of the EMP_DETAILS table given below:

name NULL TYPE 　　EMP_ID NOT NULL NUMBER 　　EMP_NAME NOT NULL VARCHAR2 (40) 　　EMP_IMAGE LONG

　　Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)
　　A. An EMP_IMAGE column can be included in the GROUP BY clause.
　　B. An EMP_IMAGE column cannot be included in the ORDER BY clause.
　　C. You cannot add a new column to the table with LONG as the data type.
　　D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
　　
　　LONG Character data in the database character set, up to 2GB. All the functionality of LONG
　　(and more) is provided by CLOB; LONGs should not be used in a modern database, and if your
　　database has any columns of this type they should be converted to CLOB. There can only be
　　one LONG column in a table.
　　Guidelines
　　A LONG column is not copied when a table is created using a subquery.
　　A LONG column cannot be included in a GROUP BY or an ORDER BY clause.
　　Only one LONG column can be used per table.
　　No constraints can be defined on a LONG column.
　　You might want to use a CLOB column rather than a LONG column
　　
22
　　
　　You need to create a table for a banking application. One of the columns in the table has the following requirements:
　　1) You want a column in the table to store the duration of the credit period.
　　2) The data in the column should be stored in a format such that it can be easily added and subtracted with DATE data type without using conversion functions.
　　3) The maximum period of the credit provision in the application is 30 days.
　　4) The interest has to be calculated for the number of days an individual has taken a credit for. Which data type would you use for such a column in the table?
　　A. DATE
　　B. NUMBER
　　C. TIMESTAMP
　　D. INTERVAL DAY TO SECOND
　　E. INTERVAL YEAR TO MONTH
　　
23
　　
　　Examine the structure proposed for the TRANSACTIONS table:

name Null Type 　　TRANS_ID NOT NULL NUMBER(6) 　　CUST_NAME NOT NULL VARCHAR2(20) 　　CUST_STATUS NOT NULL CHAR 　　TRANS_DATE NOT NULL DATE 　　TRANS_VALIDITY VARCHAR2 　　CUST_CREDIT_LIMIT NUMBER

　　Which statements are true regarding the creation and storage of data in the above table structure? (Choose all that apply.)
　　A. The CUST_STATUS column would give an error.
　　B. The TRANS_VALIDITY column would give an error.
　　C. The CUST_STATUS column would store exactly one character.
　　D. The CUST_CREDIT_LIMIT column would not be able to store decimal values.
　　E. The TRANS_VALIDITY column would have a maximum size of one character.
　　F. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
　　解释：
　　B，VARCHAR2类型必须指定最大长度
　　F，DATE类型，没有fractions of seconds.
　　VARCHAR2(size)Variable-length character data (A maximum size must be specified:
　　minimum size is 1; maximum size is 4,000.)
　　CHAR [(size)] Fixed-length character data of length size bytes (Default and minimum size is
　　1; maximum size is 2,000.)
　　NUMBER [(p,s)] Number having precision p and scale s (Precision is the total number of
　　decimal digits and scale is the number of digits to the right of the decimal point; precision can
　　range from 1 to 38, and scale can range from –84 to 127.)
　　DATE Date and time values to the nearest second between January 1, 4712 B.C., and
　　December 31, 9999 A.D.
　　
24
　　
　　Examine the structure proposed for the TRANSACTIONS table:

name Null Type 　　TRANS_ID NOT NULL NUMBER(6) 　　CUST_NAME NOT NULL VARCHAR2(20) 　　CUST_STATUS NOT NULL VARCHAR2 　　TRANS_DATE NOT NULL DATE 　　TRANS_VALIDITY INTERVAL DAY TO SECOND 　　CUST_CREDIT_VALUE NUMBER(10)

　　Which two statements are true regarding the storage of data in the above table structure? (Choose two.)
　　A. The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.
　　B. The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
　　C. The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
　　D. The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
　　
　　
　　
25
　　
　　You need to create a table with the following column specifications:
　　1. Employee ID (numeric data type) for each employee
　　2. Employee Name (character data type) that stores the employee name
　　3. Hire date, which stores the date of joining the organization for each employee
　　4. Status (character data type), that contains the value 'ACTIVE' if no data is entered
　　5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
　　Which is the correct syntax to create this table?

A. 　　CREATE TABLE EMP_1 　　( 　　emp_id NUMBER(4), 　　emp_name VARCHAR2(25), 　　start_date DATE, 　　e_status VARCHAR2(10) DEFAULT 'ACTIVE', 　　resume CLOB(200) 　　); 　　B. 　　CREATE TABLE 1_EMP 　　( 　　emp_id NUMBER(4), 　　emp_name VARCHAR2(25), 　　start_date DATE, 　　emp_status VARCHAR2(10) DEFAULT 'ACTIVE', 　　resume CLOB 　　); 　　 　　C. 　　CREATE TABLE EMP_1 　　( 　　emp_id NUMBER(4), 　　emp_name VARCHAR2(25), 　　start_date DATE, 　　emp_status VARCHAR2(10) DEFAULT "ACTIVE", 　　resume CLOB 　　); 　　D. 　　CREATE TABLE EMP_1 　　( 　　emp_id NUMBER, 　　emp_name VARCHAR2(25), 　　start_date DATE, 　　emp_status VARCHAR2(10) DEFAULT 'ACTIVE', 　　resume CLOB 　　);

　　
　　解释：
　　A，CLOB不能指定精度
　　B，表名得以字母开头
　　C，emp_status列默认值应该为'ACTIVE'，不应该用双引号
　　CLOB Character data (up to 4 GB)
　　NUMBER [(p,s)] Number having precision p and scale s (Precision is the total
　　number of decimal digits and scale is the number of digits to the right of the
　　decimal point; precision can range from 1 to 38, and scale can range from –84 to 127.)
　　
26
　　
　　Which is the valid CREATE TABLE statement?

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);

　　
　　解释：
　　B，表名不允许以数字开头
　　C，表名中不允许出现特殊符号*
　　D，date为保留字，不能作为列名。
　　Schema Object Naming Rules
　　Every database object has a name. In a SQL statement, you represent the name of an object with
　　a quoted identifier or a nonquoted identifier.
　　A quoted identifier begins and ends with double quotation marks ("). If you name a schema
　　object using a quoted identifier, then you must use the double quotation marks whenever you
　　refer to that object.
　　A nonquoted identifier is not surrounded by any punctuation.
　　The following list of rules applies to both quoted and nonquoted identifiers unless otherwise
　　indicated:
　　Names must be from 1 to 30 bytes long with these exceptions:
　　Names of databases are limited to 8 bytes.
　　Names of database links can be as long as 128 bytes.
　　If an identifier includes multiple parts separated by periods, then each attribute can be up to 30
　　bytes long. Each period separator, as well as any surrounding double quotation marks, counts as
　　one byte. For example, suppose you identify a column like this:
　　"schema"."table"."column"
　　Nonquoted identifiers cannot be Oracle Database reserved words (ANSWER D). Quoted
　　identifiers can be reserved words, although this is not recommended.
　　Depending on the Oracle product you plan to use to access a database object, names might be
　　further restricted by other product-specific reserved words.
　　The Oracle SQL language contains other words that have special meanings. These words
　　include datatypes, schema names, function names, the dummy system table DUAL, and
　　keywords (the uppercase words in SQL statements, such as DIMENSION, SEGMENT,
　　ALLOCATE, DISABLE, and so forth). These words are not reserved. However, Oracle uses them
　　internally in specific ways. Therefore, if you use these words as names for objects and object
　　parts, then your SQL statements may be more difficult to read and may lead to unpredictable
　　results.
　　In particular, do not use words beginning with SYS_ as schema object names, and do not use the
　　names of SQL built-in functions for the names of schema objects or user-defined functions.
　　You should use ASCII characters in database names, global database names, and database link
　　names, because ASCII characters provide optimal compatibility across different platforms and
　　operating systems.
　　Nonquoted identifiers must begin with an alphabetic character (ANSWER B - begins with
　　9) from your database character set. Quoted identifiers can begin with any character.
　　Nonquoted identifiers can contain only alphanumeric characters from your database character set
　　and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain
　　periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted
　　identifiers.
　　Quoted identifiers can contain any characters and punctuations marks as well as spaces.
　　However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null
　　character (\0).
　　Within a namespace, no two objects can have the same name.
　　Nonquoted identifiers are not case sensitive. Oracle interprets them as uppercase. Quoted
　　identifiers are case sensitive.
　　By enclosing names in double quotation marks, you can give the following names to different
　　objects in the same namespace:
　　employees
　　"employees"
　　"Employees"
　　"EMPLOYEES"
　　Note that Oracle interprets the following names the same, so they cannot be used for different
　　objects in the same namespace:
　　employees
　　EMPLOYEES
　　"EMPLOYEES"
　　Columns in the same table or view cannot have the same name. However, columns in different
　　tables or views can have the same name.
　　Procedures or functions contained in the same package can have the same name, if their
　　arguments are not of the same number and datatypes. Creating multiple procedures or functions
　　with the same name in the same package with different arguments is called overloading the
　　procedure or function.
　　
27
　　
　　Which two statements are true regarding tables? (Choose two.)
　　A. A table name can be of any length.
　　B. A table can have any number of columns.
　　C. A column that has a DEFAULT value cannot store null values.
　　D. A table and a view can have the same name in the same schema.
　　E. A table and a synonym can have the same name in the same schema.
　　F. The same table name can be used in different schemas in the same database.
　　解释：
　　E，有问题，但是创建公共同义词可以：create public synonym ttt for ttt;
　　Synonyms
　　Synonyms are database objects that enable you to call a table by another name.
　　You can create synonyms to give an alternative name to a table.
　　
　　
28
　　
　　Which two statements are true regarding constraints? (Choose two.)
　　A. A foreign key cannot contain NULL values.
　　B. A column with the UNIQUE constraint can contain NULL values.
　　C. A constraint is enforced only for the INSERT operation on a table.
　　D. A constraint can be disabled even if the constraint column contains data.
　　E. All constraints can be defined at the column level as well as the table level.
　　
　　Including Constraints
　　? Constraints enforce rules at the table level.
　　? Constraints prevent the deletion of a table if there are dependencies.
　　? The following constraint types are valid:
　　– NOT NULL
　　– UNIQUE
　　– PRIMARY KEY
　　– FOREIGN KEY
　　– CHECK
　　
29
　　
　　Which two statements are true regarding constraints? (Choose two.)
　　A. A foreign key cannot contain NULL values.
　　B. The column with a    UNIQUE constraint can store NULLS .
　　C. A constraint is enforced only for an INSERT operation on a table.
　　D. You can have more than one column in a table as part of a primary key.
　　
30
　　
　　Evaluate the following CREATE TABLE commands:

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) 　　);

　　The above command fails when executed. What could be the reason?
　　A. SYSDATE cannot be used with the CHECK constraint.
　　B. The BETWEEN clause cannot be used for the CHECK constraint.
　　C. The CHECK constraint cannot be placed on columns having the DATE data type.
　　D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

　　CHECK Constraint
　　The CHECK constraint defines a condition that each row must satisfy. The condition can use the
　　same constructs as the query conditions, with the following exceptions:
　　References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
　　Calls to SYSDATE, UID, USER, and USERENV functions
　　Queries that refer to other values in other rows
　　A single column can have multiple CHECK constraints that refer to the column in its definition.
　　There is no limit to the number of CHECK constraints that you can define on a column.
　　CHECK constraints can be defined at the column level or table level.
　　CREATE TABLE employees
　　(...
　　salary NUMBER(8,2) CONSTRAINT emp_salary_min
　　CHECK (salary > 0),
　　
　　
31
　　
　　Evaluate the following SQL commands:

CREATE SEQUENCE ord_seq 　　INCREMENT BY 10 　　START WITH 120 　　MAXVALUE 9999 NOCYCLE; 　　 　　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) 　　);

　　The command to create a table fails. Identify the reason for the SQL statement failure? (Choose all that apply.)
　　A. You cannot use SYSDATE in the condition of a CHECK constraint.
　　B. You cannot use the BETWEEN clause in the condition of a CHECK constraint.
　　C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
　　D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.
　　
　　The CHECK constraint defines a condition that each row must satisfy. The condition can use the
　　same constructs as the query conditions, with the following exceptions:
　　References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
　　Calls to SYSDATE, UID, USER, and USERENV functions
　　Queries that refer to other values in other rows
　　A single column can have multiple CHECK constraints that refer to the column in its definition.
　　There is no limit to the number of CHECK constraints that you can define on a column.
　　CHECK constraints can be defined at the column level or table level.
　　CREATE TABLE employees
　　(...
　　salary NUMBER(8,2) CONSTRAINT emp_salary_min
　　CHECK (salary > 0),
　　
32
　　
　　Which CREATE TABLE statement is valid?

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) 　　);

　　
　　A，一个表中只能有一个主键
　　B，UNIQUE和NOT NULL之前多一个，号
　　C，指定了DEFAULT，没有指定默认值。
　　PRIMARY KEY Constraint
　　A PRIMARY KEY constraint creates a primary key for the table. Only one primary
　　key can be created for each table. The PRIMARY KEY constraint is a column or
　　a set of columns that uniquely identifies each row in a table. This constraint
　　enforces the uniqueness of the column or column combination and ensures that
　　no column that is part of the primary key can contain a null value.
　　Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on
　　the primary key column or columns.
　　
　　
33
　　
　　33. You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement:
　　1) The order ID will be unique and cannot have null values.
　　2) The order date cannot have null values and the default should be the current date.
　　3) The order amount should not be less than 50.
　　4) The order status will have values either shipped or not shipped.
　　5) The order payment mode should be cheque, credit card, or cash on delivery (COD). Which is the valid DDL statement for creating the ORD_DETAIL table?

A. 　　CREATE TABLE ord_details 　　( 　　ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL, 　　ord_date DATE DEFAULT SYSDATE NOT NULL, 　　ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 　　CHECK (ord_amount > 50), 　　ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 　　CHECK (ord_status IN ('Shipped', 'Not Shipped')), 　　ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 　　CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 'Cash On Delivery')) 　　); 　　B. 　　CREATE TABLE ord_details 　　( 　　ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL, 　　ord_date DATE DEFAULT SYSDATE NOT NULL, 　　ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 　　CHECK (ord_amount > 50), 　　ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 　　CHECK (ord_status IN ('Shipped', 'Not Shipped')), 　　ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 　　CHECK (ord_pay_mode IN 　　('Cheque', 'Credit Card', 'Cash On Delivery')) 　　); 　　C. 　　CREATE TABLE ord_details 　　( 　　ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY, 　　ord_date DATE DEFAULT SYSDATE NOT NULL, 　　ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 　　CHECK (ord_amount >= 50), 　　ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 　　CHECK (ord_status IN ('Shipped', 'Not Shipped')), 　　ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 　　CHECK (ord_pay_mode IN 　　('Cheque', 'Credit Card', 'Cash On Delivery'))); 　　D. 　　CREATE TABLE ord_details 　　( 　　ord_id NUMBER(2), 　　ord_date DATE NOT NULL DEFAULT SYSDATE, 　　ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 　　CHECK (ord_amount >= 50), 　　ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 　　CHECK (ord_status IN ('Shipped', 'Not Shipped')), 　　ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 　　CHECK (ord_pay_mode IN 　　('Cheque', 'Credit Card','Cash On Delivery')) 　　);

　　
　　A，ord_id应该定义主键约束
　　
34
　　
　　You created an ORDERS table with the following description:

name Null Type 　　ORD_ID NOT NULL NUMBER(2) 　　CUST_ID NOT NULL NUMBER(3) 　　ORD_DATE NOT NULL DATE 　　ORD_AMOUNT NOT NULL NUMBER (10,2)

　　You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. Which statement is true in this scenario?
　　A. You cannot have two constraints on one column.
　　B. You cannot add a primary key constraint if data exists in the column.
　　C. The primary key constraint can be created only at the time of table creation .
　　D. You can add the primary key constraint even if data exists, provided that there are no duplicate values.
　　
35
　　
　　Which two statements are true regarding constraints? (Choose two.)
　　A. A table can have only one primary key and one foreign key.
　　B. A table can have only one primary key but multiple foreign keys.
　　C. Only the primary key can be defined at the column and table levels.
　　D. The foreign key and parent table primary key must have the same name.
　　E. Both primary key and foreign key constraints can be defined at both column and table levels.
　　
　　
　　
36
　　
　　Examine the following SQL commands:

CREATE TABLE products 　　( 　　prod_id NUMBER(3) CONSTRAINT p_ck CHECK (prod_id > 0), 　　prod_name CHAR(30), 　　prod_qty NUMBER(6), 　　CONSTRAINT p_name NOT NULL, 　　CONSTRAINT prod_pk PRIMARY KEY (prod_id) 　　); 　　CREATE TABLE warehouse 　　( 　　warehouse_id NUMBER(4), 　　roomno NUMBER(10) CONSTRAINT r_id 　　CHECK(roomno BETWEEN 101 AND 200), 　　location VARCHAR2(25), 　　prod_id NUMBER(3), 　　CONSTRAINT wr_pr_pk PRIMARY KEY (warehouse_id,prod_id), 　　CONSTRAINT prod_fk FOREIGN KEY (prod_id) 　　REFERENCES products(prod_id) 　　);

　　Which statement is true regarding the execution of the above SQL commands?
　　A. Both commands execute successfully.
　　B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.
　　C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
　　D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same column.
　　E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraints.
　　解释：
　　The slide gives the syntax for defining constraints when creating a table. You can
　　create constraints at either the column level or table level. Constraints defined at
　　the column level are included when the column is defined. Table -level constraints
　　are defined at the end of the table definition and must refer to the column or
　　columns on which the constraint pertains in a set of parentheses. It is mainly the
　　syntax that differentiates the two; otherwise, functionally, a columnlevel
　　constraint is the same as a table-level constraint.
　　NOT NULL constraints must be defined at the column level.
　　Constraints that apply to more than one column must be defined at the table level
　　
37
　　
　　You issued the following command to drop the PRODUCTS table:
　　DROP TABLE products;
　　What is the implication of this command? (Choose all that apply.)
　　A. All data along with the table structure is deleted.
　　B. The pending transaction in the session is committed.
　　C. All indexes on the table will remain but they are invalidated.
　　D. All views and synonyms will remain but they are invalidated.
　　E. All data in the table are     deleted but the table structure will remain.
　　解释：drop table时，索引被删除了，但视图和同义词仍在。
　　
38
　　
　　Which two statements are true regarding views? (Choose two.)
　　A. A simple view in which column aliases have been used cannot be updated.
　　B. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
　　C. Rows added through a view are deleted from the table automatically when the view is dropped.
　　D. The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
　　E. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
　　
39
　　
　　Evaluate the following command:

CREATE TABLE employees 　　( 　　employee_id NUMBER(2) PRIMARY KEY, 　　last_name VARCHAR2(25) NOT NULL, 　　department_id NUMBER(2)NOT NULL, 　　job_id VARCHAR2(8), 　　salary NUMBER(10,2) 　　);

　　You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization:

CREATE OR REPLACE VIEW sales_staff_vu AS 　　SELECT employee_id, last_name,job_id 　　FROM employees 　　WHERE job_id LIKE 'SA_%' WITH CHECK OPTION;

　　Which two statements are true regarding the above view? (Choose two.)
　　A. It allows you to insert rows into the EMPLOYEES table .
　　B. It allows you to delete details of the existing sales staff from the EMPLOYEES table.
　　C. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table.
　　D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements
　　A，视图基表中有列未出现在视图中，且它具有NOT NULL约束。
　　C，因为具有WITH CHECK OPTION
　　D，关于多表插入

INSERT ALL 　　INTO sal_history VALUES(EMPID,HIREDATE,SAL) 　　INTO mgr_history VALUES(EMPID,MGR,SAL) 　　SELECT employee_id EMPID, hire_date HIREDATE, 　　salary SAL, manager_id MGR 　　FROM employees 　　WHERE employee_id > 200;

　　
　　
　　
40
　　
　　View the Exhibit to examine the description for the SALES and PRODUCTS tables. You want to create a SALE_PROD view by executing the following SQL statement:

CREATE VIEW sale_prod 　　AS SELECT p.prod_id, cust_id, SUM(quantity_sold) "Quantity" , 　　SUM(prod_list_price) "Price" 　　FROM products p, sales s 　　WHERE p.prod_id=s.prod_id 　　GROUP BY p.prod_id, cust_id;

　　Which statement is true regarding the execution of the above statement?

　　A. The view will be created and you can perform DML operations on the view.
　　B. The view will be created but no DML operations will be allowed on the view.
　　C. The view will not be created because the join statements are not allowed for creating a view.
　　D. The view will not be created because the GROUP BY clause is not allowed for creating a view.
　　ments.
　　
　　
　　Rules for Performing DML Operations on a View
　　You cannot add data through a view if the view includes:
　　Group functions

• 
  A GROUP BY clause
  
• 
  The DISTINCT keyword
  
• 
  The pseudocolumn ROWNUM keyword
  
• 
  Columns defined by expressions
  
• 
  NOT NULL columns in the base tables that are not selected
  
• 
  by the view
  

　　
41
　　
　　Which two statements are true regarding views? (Choose two.)
　　A. A subquery that defines a view cannot include the GROUP BY clause.
　　B. A view that is created with the subquery having the DISTINCT keyword can be updated.
　　C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot be updated.
　　D. A data manipulation language ( DML) operation can be performed on a view that is created with the subquery having all the NOT NULL columns of a table.
　　解释：
　　Rules for Performing DML Operations on a View
　　You cannot add data through a view if the view includes:
　　Group functions
　　A GROUP BY clause
　　The DISTINCT keyword
　　The pseudocolumn ROWNUM keyword
　　Columns defined by expressions
　　NOT NULL columns in the base tables that are not selected
　　by the view
　　
　　
42
　　
　　Which three statements are true regarding views? (Choose three.)
　　A. Views can be created only from tables.
　　B. Views can be created from tables or other views.
　　C. Only simple views can use indexes existing on the underlying tables.
　　D. Both simple and complex views can use indexes existing on the underlying tables.
　　E. Complex views can be created only on multiple tables that exist in the same schema.
　　F. Complex views can be created on multiple tables that exist in the same or different schemas.
　　
　　解释：这道题没有only的都是正确答案
　　
43
　　
　　Evaluate the following CREATE SEQUENCE statement:

CREATE SEQUENCE seq1 　　START WITH 100 　　INCREMENT BY 10 　　MAXVALUE 200 CYCLE NOCACHE;

　　The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement: SELECT seq1.nextval FROM dual;
　　What is displayed by the SELECT statement?
　　A. 1
　　B. 10
　　C. 100
　　D. an error
　　原因：指定cycle选项后，如果达到了该序列的最大值（maxvalue)，则会从它的最小值（minvalue)开始，产生下一个值。注意，不是从start with开始。如果没指定minvalues，则相当于指定nominvalue选项，则minvalue的值为1。
　　
　　CYCLE | NOCYCLE Specifies whether the sequence continues to generate
　　values after reaching its maximum or minimum value
　　(NOCYCLE is the default option.)
　　CACHE n | NOCACHE Specifies how many values the Oracle server preallocates
　　and keeps in memory (By default, the Oracle server
　　caches 20 values.)
　　But why the answer is not "C" ?
　　Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition
　　that you created it will have the default value of 1, which it reverts to when cycling.
　　If you wanted to keep the minimum value you would need to specify it in the sequence creation.
　　sequence Is the name of the sequence generator
　　INCREMENT BY n Specifies the interval between sequence numbers, where
　　n is an integer (If this clause is omitted, the sequence
　　increments by 1.)
　　START WITH n Specifies the first sequence number to be generated (If this
　　clause is omitted, the sequence starts with 1.)
　　MAXVALUE n Specifies the maximum value the sequence can generate
　　NOMAXVALUE Specifies a maximum value of 10^27 for an ascending
　　sequence and –1 for a descending sequence (This is the
　　default option.)
　　MINVALUE n Specifies the minimum sequence value
　　NOMINVALUE Specifies a minimum value of 1 for an ascending sequence
　　and –(10^26) for a descending sequence (This is the default
　　option.)
　　CYCLE | NOCYCLE Specifies whether the sequence continues to generate
　　values after reaching its maximum or minimum value
　　(NOCYCLE is the default option.)
　　CACHE n | NOCACHE Specifies how many values the Oracle server preallocates
　　and keeps in memory (By default, the Oracle server
　　caches 20 values.)
　　
　　
44
　　
　　View the Exhibit and examine the structure of the ORD table.
　　Evaluate the following SQL statements that are executed in a user session in the specified order:

CREATE SEQUENCE ord_seq; 　　SELECT ord_seq.nextval FROM dual; 　　INSERT INTO ord 　　VALUES (ord_seq.CURRVAL, '25-jan-2007',101); 　　UPDATE ord 　　SET ord_no= ord_seq.NEXTVAL WHERE cust_id =101;

　　What would be the outcome of the above statements?

　　A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.
　　B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
　　C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.
　　D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.
　　
45
　　
　　45. Which two statements are true about sequences created in a single instance database? (Choose two.)
　　A. The numbers generated by a sequence can be used only for one table.
　　B. DELETE  would remove a sequence from the database.
　　C. CURRVAL is used to refer to the last sequence number that has been generated.
　　D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.
　　E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.
　　
　　解释：
　　tobecontinued