[2021-New] Oracle 1Z0-051 Dumps With Update Exam Questions (151-160)

1Z0-051 Dumps

1Z0-051 Exam Questions - Online Test

1Z0-051 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. - (Topic 1)

Which two statements are true regarding single row functions? (Choose two.)

A. They can be nested only to two levels

B. They always return a single result row for every row of a queried table

C. Arguments can only be column values or constant

D. They can return a data type value different from the one that is referenced

E. They accept only a single argument

Answer: B,D

Explanation:

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.

Q2. - (Topic 2)

Which two statements are true regarding savepoints? (Choose two.)

A. Savepoints are effective only for COMMIT.

B. Savepoints may be used to ROLLBACK.

C. Savepoints can be used for only DML statements.

D. Savepoints are effective for both COMMIT and ROLLBACK.

E. Savepoints can be used for both DML and DDL statements.

Answer: B,C

Q3. - (Topic 2)

View the Exhibit; e xamine 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;

Answer: D

Q4. - (Topic 2)

You need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:

CUST_IDNUMBER(4)NOT NULL CUST_NAMEVARCHAR2(100)NOT NULL CUST_ADDRESSVARCHAR2(150)

CUST_PHONEVARCHAR2(20)

Which SELECT statement accomplishes this task?

A. SELECT * FROM customers

B. SELECT name, address FROM customers;

C. SELECT id, name, address, phone FROM customers;

D. SELECT cust_name, cust_address FROM customers;

E. SELECT cust_id, cust_name, cust_address, cust_phone FROM customers;

Answer: D

Explanation:

This answer provides correct list of columns for the output.

Incorrect Answers

A:This answer does not provide correct list of columns for the output. It is not required to

show all columns of the table. Symbol “*” is used in the SELECT command to substitute a

list of all columns of the table.

B:This answer does not provide correct list of columns for the output. There are not NAME

and ADDRESS columns in the CUSTOMERS table.

C:This answer does not provide correct list of columns for the output. There are not ID,

NAME, ADDRESS or PHONE columns in the CUSTOMERS table.

E:This answer does not provide correct list of columns for the output. It is not required to

show all columns of the table.

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 20-24

Chapter 1: Overview of Oracle Databases

Q5. - (Topic 2)

What is true about updates through a view?

A. You cannot update a view with group functions.

B. When you update a view group functions are automatically computed.

C. When you update a view only the constraints on the underlying table will be in effect.

D. When you update a view the constraints on the views always override the constraints on the underlying tables.

Answer: A

Q6. - (Topic 1)

You are currently located in Singapore and have connected to a remote database in

Chicago.

You issue the following command:

Exhibit:

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table.

What is the outcome?

A. Number of days since the promo started based on the current Singapore data and time.

B. An error because the ROUND function specified is invalid

C. An error because the WHERE condition specified is invalid

D. Number of days since the promo started based on the current Chicago data and time

Answer: D

Q7. - (Topic 1)

Which three statements are true regarding sub queries? (Choose three.)

A. Multiple columns or expressions can be compared between the main query and sub query

B. Sub queries can contain GROUP BY and ORDER BY clauses

C. Only one column or expression can be compared between the main query and subqeury

D. Main query and sub query can get data from different tables

E. Main query and sub query must get data from the same tables

F. Sub queries can contain ORDER BY but not the GROUP BY clause

Answer: A,B,D

Q8. - (Topic 2)

Which two statements are true regarding subqueries? (Choose two.)

A. A subquery can retrieve zero or more rows.

B. Only two subqueries can be placed at one level.

C. A subquery can be used only in SQL query statements.

D. A subquery can appear on either side of a comparison operator.

E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Answer: A,D

Explanation:

Using a Subquery to Solve a Problem Suppose you want to write a query to find out who earns a salary greater than Abel’s salary. To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount. You can solve this problem by combining the two queries, placing one query inside the other query. The inner query (or subquery) returns a value that is used by the outer query (or main query). Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query. Subquery Syntax A subquery is a SELECT statement that is embedded in the clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself. You can place the subquery in a number of SQL clauses, including the following: WHERE clause HAVING clause FROM clause In the syntax: operator includes a comparison condition such as >, =, or IN Note: Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL, EXISTS). The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query. Guidelines for Using Subqueries Enclose subqueries in parentheses. Place subqueries on the right side of the comparison condition for readability. (However, the subquery can appear on either side of the comparison operator.) Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries.

Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause. They can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.

Q9. - (Topic 1)

You need to extract details of those products in the SALES table where the PROD_ID column contains the string '_D123'. Which WHERE clause could be 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

Explanation:

A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12 shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and SA_REP and which conforms to the original requirement: select job_id from jobs where job_id like 'SA\_%' escape '\'

Q10. - (Topic 1)

You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL; B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL;

C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;

D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year')) FROM DUAL;

Answer: C

Explanation:

Using the TO_CHAR Function with Dates TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines

The format model must be enclosed with single quotation marks and is case-sensitive.

The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.

The names of days and months in the output are automatically padded with blanks.

To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

Elements of the Date Format Model

DY Three-letter abbreviation of the day of the week

DAY Full name of the day of the week

DD Numeric day of the month

MM Two-digit value for the month

MON Three-letter abbreviation of the month

MONTH Full name of the month

YYYY Full year in numbers

YEAR Year spelled out (in English)