ocp 071 最新题库02

36.View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

need-to-insert-img

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement: SELECT product_name, list_price

FROM product_information

WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

A. It would execute but the output would return no rows.

B. It would execute and the output would display the desired result.

C. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.

D. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.

Answer:A

解析：这题答案应该没问题。

可以去公司测试下，以下语句

Select * from margincalls

Where margincallid = 11111111（放一个真实存在的margincallid）

And margincallid = 22222222(放一个真实存在的margincallID），执行一下，看这个语句会不会报错。

37.Evaluate the following SELECT statement and view the Exhibit to examine its output:

解析：这题没怎么看需要好好重新看一下

need-to-insert-img

SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status

FROM user_constraints WHERE table_name = ORDERS Which two statements are true about the output? (Choose two.) A. In the second column, indicates a check constraint.

B. The STATUS column indicates whether the table is currently in use.

C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

Answer:A,D

38.Evaluate the following SQL statement:

SQL> SELECT cust_id, cust_last_name "Last Name"

FROM customers

WHERE country_id = 10

UNION

SELECT cust_id CUST_NO, cust_last_name

FROM customers

WHERE country_id = 30;

Which ORDER BY clause are valid for the above query? (Choose all that apply.)

A. ORDER BY 2,1

B. ORDER BY CUST_NO

C. ORDER BY 2,cust_id

D. ORDER BY "CUST_NO"

E. ORDER BY "Last Name" Answer:A,C,E Explanation:

Using the ORDER BY Clause in Set Operations

-The ORDER BY clause can appear only once at the end of the compound query.

-Component queries cannot have individual ORDER BY clauses.

-The ORDER BY clause recognizes only the columns of the first SELECT query.

-By default, the first column of the first SELECT query is used to sort the output in an ascending order.

解析这题答案没问题我会

39.Evaluate the following SQL statements that are issued in the given order:

CREATE TABLE emp

(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, enameVARCHAR2(15), salary NUMBER(8,2),

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);

ALTER TABLE emp

DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp

ENABLE CONSTRAINT emp_emp_no_pk;

What would be the status of the foreign key EMP_MGR_FK?

A. It would be automatically enabled and deferred.

B. It would be automatically enabled and immediate.

C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Answer:A,B,D

解析：这题应该选c，根据网站https://www.cnblogs.com/longjshz/p/4303314.html

40.You want to display 5 percent of the rows from the sales table for products with the lowestAMOUNT_SOLD and also want to include the rows that have the sameAMOUNT_SOLDeven if this causes the output to exceed 5 percent of the rows.

Which query willprovide the required result?

A. SELECT prod_id, cust_id, amount_sold

FROM sales

ORDER BY amount_sold

FETCH FIRST 5 PERCENT ROWS WITH TIES;

B. SELECT prod_id, cust_id, amount_sold

FROM sales

ORDER BY amount_sold

FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;

C. SELECT prod_ id, cust_id, amount_sold

FROM sales

ORDER BY araount_sold

FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;

D. SELECT prod_id, cust_id, amount_sold

FROM sales

ORDER BY amount__sold

FETCH FIRST 5 PERCENT ROWS ONLY;

Answer:B

解析这一题需要背诵一下

Fetch first 5 percent rows only with ties

41.View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

need-to-insert-img

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SELECT p.product_name, i.item_cnt

FROM (SELECT product_id, COUNT (*) item_cnt

FROM order_items

GROUP BY product_id) i RIGHT OUTER JOIN products p

ON i.product_id = p.product_id;

What would happen when the above statement is executed?

A. The statement would execute successfully to produce the required output.

B. The statement would not execute because inline views and outer joins cannot be used together.

C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

D. The statement would not execute because the GROUP BY clause cannot be used in the inline view. Answer:A

解析这题答案是对的可以参考网页https://blog.csdn.net/u013012406/article/details/80779841

去公司测试一下这种用法

42.Which two statements are true about Data Manipulation Language (DML) statements?

A. AH INSERT INTO. . .VALUES. . statement can add multiple rows per execution to a table.

B. An UPDATE...SET... statement can modify multiple rows based on multiple conditions on a table.

C. A DELETE FROM ..... statement can remove rows based on only a single condition on a table.

D. An INSERT INTO...VALUES..... statement can add a single row based on multiple conditions on a table.

E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.

F. An UPDATE...SET.... statement can modify multiple rows based on only a single condition on a table. Answer: A,C

解析这题答案貌似不正确应该选b e

oracle不支持一次插入多行和多条件插入

https://www.2cto.com/database/201806/754406.html

43.Which two statements are true regarding constraints? (Choose two.)

A foreign key cannot contain NULL values. 错误，可以包含空值的。

B. A column with the UNIQUE constraint can contain NULL. 正确可以包含空值

C. A constraint is enforced only for the INSERT operation on a table. 错误，insert & update

D. A constraint can be disabled even if the constraint column contains data. 正确

E. All the constraints can be defined at the column level as well as the table level 错误

NULL and NOT NULL are only defined at the column level

Answer:B,D

44.Examine the command:

need-to-insert-img

What does ON DELETE CASCADE Imply?

A. When the books table is dropped, the BOOK_TRANSACTIONS table is dropped.

B. When the books table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.

C. When a row in the books table is deleted, the rows in the BOOK__TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the books table are also deleted.

D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the books transactions. BOOK_ID column. Answer:C

45.Examine the data in the CUST_NAME column of the CUSTOMERS table.

CUST_NAME Renske Ladwig

Jason Mallin

Samuel McCain

Allan MCEwen

Irene Mikkilineni

Julia Nayer

You need to display customers' second names where the second name starts with "Mc" or "MC." Which query gives the required output?

解析，这一题不太懂，这一题涉及到三个不熟悉的函数。

A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)

FROM customers

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';

B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)

FROM customers

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';

C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)

FROM customers

WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');

D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)

FROM customers

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%'); Answer:B

46.Evaluate the following statement:

INSERT ALL

WHEN order_total < 10000 THEN

INTO small_orders

WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders

WHEN order_total > 2000000 THEN

INTO large_orders

SELECT order_id, order_total, customer_id

FROM orders;

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

D. The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true. Answer:A

解析这题答案选A没错的

need-to-insert-img

47.Evaluate the following SQL statement:

SELECT product_name || 'it's not available for order'

FROM product_information

WHERE product_status = 'obsolete';

You received the following error while executing the above query:

ERROR:

ORA-01756: quoted string not properly terminated

What would you do to execute the query successfully?

A. Enclose the character literal string in the SELECT clause within the double quotation marks.

B. Do not enclose the character literal string in the SELECT clause within the single quotation marks.

C. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.

D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.

Answer:C

解析这题答案选c是对的详细解析参考网页 https://blog.csdn.net/u012793120/article/details/47027469

下面这种说法有意思，原来char（39）在oracle里也可以使用。

need-to-insert-img

48.Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

A. First normal form

B. Second normal form

C. Third normal form

D. Fourth normal form

Answer:B 这题答案正确

参考网页https://blog.udemy.com/database-normal-forms/

49.Which statement is true about transactions?

A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.

B. Each Data Definition Language (DDL) statement executed forms a single transaction.

C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.

D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Answer: D 这题答案是对的

下面这个网页不错

http://www.aiotestkinguk.com/dumps/1z0-071/page-276.html

50.Examine the structure of the members table:

need-to-insert-img

Which query can be used to display the last names and city names only for members from the states MO and MI?

need-to-insert-img

A. Option A

B. Option B

C. Option C

D. Option D Answer:C

解析这题很简单，答案选C

Which query can be used to display the last names and city names only for members from the states MO and MI?A. SELECT last_name, city FROM members WHERE state =’MO’ AND state =’MI’;B. SELECT last_name, city FROM members WHERE state LIKE ‘M%’;C. SELECT last_name, city FROM members WHERE state IN (‘MO’, ‘MI’);

D. SELECT DISTINCT last_name, city FROM members WHERE state =’MO’ OR state =’MI’;

51.Which two statements are true regarding roles? (Choose two.) A. A role can be granted to itself.

B. A role can be granted to PUBLIC.

C. A user can be granted only one role at any point of time.

D. The REVOKE command can be used to remove privileges but not roles from other users.

E. Roles are named groups of related privileges that can be granted to users or other roles. Answer:B,E

52.The first DROP operation is performed on PRODUCTS table using the following command: DROP TABLE products PURGE; Then you performed the FLASHBACK operation by using the following command: FLASHBACK TABLE products TO BEFORE DROP; Which statement describes the outcome of the FLASHBACK command?

A. It recovers only the table structure.

B. It recovers the table structure, data, and the indexes.

C. It recovers the table structure and data but not the related indexes.

D. It is not possible to recover the table structure, data, or the related indexes.

Answer:D

这题考察drop和purge的区别

53.See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:

need-to-insert-img

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A. You issue the following SQL statements: Exhibit:

need-to-insert-img

What would be the outcome?

A. It generates an error because multiple conditions cannot be specified for the WHEN clause

B. It executes successfully and gives the required result

C. It generates an error because CASE cannot be used with group functions

D. It generates an error because NULL cannot be specified as a return value Answer:B

Explanation: 这题答案没问题

CASE Expression Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement: CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

54.Evaluate the following SQL query;

need-to-insert-img

What would be the outcome?

A. 200

B. 16

C. 160

D. 150

E. 100

Answer:C

Explanation:

Function Purpose 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.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero

55.Which statement is true regarding the default behavior of the ORDER BY clause?

A. In a character sort, the values are case-sensitive

B. NULL values are not considered at all by the sort operation

C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause

D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions Answer:A

解析这题答案正确，参考网页https://blog.csdn.net/holly2008/article/details/25084901

Explanation:

Character Strings and Dates Character strings and date values are enclosed with single quotation marks.

Character values are case-sensitive and date values are format-sensitive. The default date display format is DD-MON-RR.

56.Which two statements are true regarding the EXISTS operator used in the correlated subqueries?

(Choose two.)

A. The outer query stops evaluating the result set of the inner query when the first value is found.

B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query. C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Answer:A,C

57.View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.

need-to-insert-img

Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST LAST NAME is Roberts and CREDIT LIMIT is 600?

A. INSERT INTO orders

VALUES (1,'10-mar-2007', 'direct',

(SELECT customer_id

FROM customers

WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

B. INSERT INTO orders (order_id,order_date,order_mode,

(SELECT customer_id

FROM customers

WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total)

VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000);

C. INSERT INTO orders (order_id.order_date.order_mode,

(SELECT customer_id

FROM customers

WHERE cust_last_name='Roberts' AND credit _limit=600) .order_total)

VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000);

D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id,

o.order_total FROM orders o, customers c

WHERE o.customer_id = c.customer_id

AND c.cust_last_name='Roberts'ANDc. Credit_limit=600)

VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id

FROM customers

WHERE cust_last_name='Roberts' AND

Credit_limit=600), 1000);

Answer:A

58.View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:

CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS

SELECT order_id.order_date,customer_id FROM orders; Which statement is true regarding the above command?

need-to-insert-img

A. The NEW_IDRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

B. The NEW_IDRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

Answer:B

59.Which task can be performed by using a single Data Manipulation Language (DML) statement?

A. adding a column constraint when inserting a row into a table

B. adding a column with a default value when inserting a row into a table

C. removing all data only from one single column on which a unique constraint is defined

D. removing all data only from one single column on which a primary key constraint is defined

Answer:D

60.View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.

You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.

need-to-insert-img

Which CREATE VIEW statement would create the view successfully?

A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) A 错误

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)

"NO OF ITEMS"

FROM orders o JOIN order_items i

ON (o.order_id = i.order_id)

GROUP BY o.order_id,o.order_date;

B. CREATE OR REPLACE VIEW ord_vu

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)

"NO OF ITEMS"

FROM orders o JOIN order_items i

ON (o.order_id = i.order_id)

GROUP BY o.order_id,o.order_date;

C. CREATE OR REPLACE VIEW ord_vu

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)

FROM orders o JOIN order_items i ON (o.order_id = i.order_id)

GROUP BY o.order_id,o.order_date;

D. CREATE OR REPLACE VIEW ord_vu

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ NO OF ITEMS'

FROM orders o JOIN order_items i

ON (o.order_id = i.order_id)

GROUP BY o.order_id,o.order_date

WITH CHECK OPTION;

Answer:B

这题我自己做我也会选择B。

61.View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS tables.

need-to-insert-img

Evaluate the following MERGE statement:

MERGE INTO orders_master o

USING monthly_orders m

ON (o.order_id = m.order_id)

WHEN MATCHED THEN

UPDATE SET o.order_total = m.order_total

DELETE WHERE (m.order_total IS NULL)

WHEN NOT MATCHED THEN

INSERT VALUES (m.order_id, m.order_total);

What would be the outcome of the above statement?

A. The ORDERS_MASTER table would contain the ORDERJDs 1 and 2.

B. The ORDERS_MASTER table would contain the ORDERJDs 1,2 and 3.

C. The ORDERS_MASTER table would contain the ORDERJDs 1,2 and 4.

D. The ORDERS MASTER table would contain the ORDER IDs 1,2,3 and 4. Answer:C

62.Which statements are correct regarding indexes? (Choose all that apply.)

A. When a table is dropped, the corresponding indexes are automatically dropped.

B. For each DML operation performed, the corresponding indexes are automatically updated.

C. Indexes should be created on columns that are frequently referenced as part of an expression.

D. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

解析这题答案正确

https://blog.csdn.net/rlhua/article/details/12780849

need-to-insert-img

Answer:A,B,D

63.View the Exhibit for the structure of the STUDENT and FACULTY tables.

need-to-insert-img

You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:

need-to-insert-img

Which statement is true regarding the outcome?

A. Only statement 1 executes successfully and gives the required result.

B. Only statement 2 executes successfully and gives the required result.

C. Both statements 1 and 2 execute successfully and give different results.

D. Both statements 1 and 2 execute successfully and give the same required result. Answer:D

解析：这题答案没问题，考察知识点using 和natural join。

64.The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:

GRANT ALL

ON orders, order_items

TO PUBLIC;

What correction needs to be done to the above statement? A. PUBLIC should be replaced with specific usernames.

B. ALL should be replaced with a list of specific privileges.

C. WITH GRANT OPTION should be added to the statement.

D. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables.

Answer:D

65.The following are the steps for a correlated subquery, listed in random order:

1) The WHERE clause of the outer query is evaluated.

2) The candidate row is fetched from the table specified in the outer query.

3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.

4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.

Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.

A. 4,2,1,3

B. 4,1,2,3

C. 2,4,1,3

D. 2,1,4,3

Answer:C

参考网页https://www.cnblogs.com/longjshz/p/4303314.html

这题答案可能要背诵一下咯。

66.The BOOKS_TRANSACTIONStable exists in your database.

Examine the SQL statement:

SQL>SELECT * FROM books_transactionsORDER BY 3; What is the outcome on execution?

A. The execution tails unless the numeral 3 in the order by clause is replaced by a column name,

B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.

C. Rows are displayed in the order that they are stored in the table only for the first three rows.

D. Rows are displayed sorted in ascending order of the values in the third column in the table. Answer:D

解析这题是送分题。

67.Examine the business rule:

Each student can take up multiple projects and each project can have multiple students.

You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:

STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK

Which two statements are true in this scenario?

A. The ERD must have a1:M relationship between the students and projects entitles.

B. The ERD must have a M:M relationship between the students and projects entities that must be resolved into 1:M relationships.

C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.

D. PROJECT_ID must be the primary key in the projects entity and foreign key in the STUDENTS entity.

E. An associative table must be created with a composite key of STUDENT_ID andPROJECT_ID; which is the foreign key linked to theSTUDENTSandPROJECTSentities.

Answer:D,E

这题不太理解，背答案吧

http://cdn-media1.teachertube.com/doc604/32116.pdf

68.View the Exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

need-to-insert-img

To retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:

SELECT employee_id, first_name, department_name

FROM employees NATURAL JOIN departments;

The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

A. The NATURAL JOIN clause is missing the USING clause.

B. The table prefix is missing for the column names in the SELECT clause.

C. The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.

D. The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Answer:D

解析：这题答案没问题。

need-to-insert-img

69.View the Exhibit and examine the structure of the CUSTOMERS table.

need-to-insert-img

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

Answer:D,E Explanation:

Describe the Types of Problems That the Subqueries Can Solve There are many situations where you will need the result of one query as the input for another.Use of a Subquery Result Set for Comparison Purposes Which employees have a salary that is less than the average salary? This could be answered by two statements, or by a single statement with a subquery. The following example uses two statements:

select avg(salary) from employees; select last_name from employees where salary < result_of_previous_query ; Alternatively, this example uses one statement with a subquery: select last_name from employees where salary < (select avg(salary)from employees); In this example, the subquery is used to substitute a value into the WHERE clause of the parent query: it is returning a single value, used for comparison with the rows retrieved by the parent query. The subquery could return a set of rows. For example, you could use the following to find all departments that do actually have one or more employees assigned to them:

select department_name from departments where department_id in (select distinct(department_id) from employees);

70.Which three statements are true about the ALTER TABLE ----DROP COLUMN .....command? A. A column can be dropped only if it does not contain any data.

B. A column can be dropped only if another column exists in the table.

C. A dropped column can be rolled back.

D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

E. A parent key column in the table cannot be dropped.

Answer:C

71.View the Exhibit and examine the description of the PRODUCT_INFORMATION table.

Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

need-to-insert-img

A. SELECT COUNT(list_price)

FROM product_information

WHERE list_price IS NULL;

B. SELECT COUNT(list_price)

FROM product_information

WHERE list_price = NULL;

C. SELECT COUNT(NVL(list_price, 0))

FROM product_information

WHERE list_price IS NULL;

D. SELECT COUNT(DISTINCT list_price)

FROM product_information

WHERE list_price IS NULL;

Answer:C

72.Which statement is true about an inner join specified in the WHERE clause of a query?

A. It must have primary-key and foreign key constraints defined on the columns used in the join condition.

B. It requires the column names to be the same in all tables used for the join conditions.

C. It is applicable for equijoin and nonequijoin conditions.

D. It is applicable for only equijoin conditions.

Answer:C

73.You want to display the date for the first Monday of the next month and issue the following command: SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),'MON'), 'dd "is the first Monday for"fmmonth rrrr') FROM DUAL; What is the outcome?

A. It executes successfully and returns the correct result.

B. It executes successfully but does not return the correct result.

C. It generates an error because TO_CHAR should be replaced with TO_DATE.

D. It generates an error because rrrr should be replaced by rr in the format string.

E. It generates an error because fm and double quotation marks should not be used in the format string. Answer:A

Explanation:

NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.

LAST_DAY(date): Finds the date of the last day of the month that contains date The second innermost function is evaluated next. TO_CHAR('28-OCT-2009', 'fmMonth') converts the given date based on the Month format mask and returns the character string October. The fm modifier trims trailing blank spaces from the name of the month.

ocp 071 最新题库02

推荐阅读更多精彩内容