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

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

Examine the structure of the EMP_DEPT_VU view:

Which SQL statement produces an error?

A. SELECT *

FROM emp_dept_vu;

B. SELECT department_id, SUM(salary)

FROM emp_dept_vu

GROUP BY department_id;

C. SELECT department_id, job_id, AVG(salary)

FROM emp_dept_vu

GROUP BY department_id, job_id;

D. SELECT job_id, SUM(salary)

FROM emp_dept_vu

WHERE department_id IN (10,20)

GROUP BY job_id

HAVING SUM(salary) > 20000;

E. None of the statements produce an error; all are valid.

Answer: E

Explanation: Explanation: None of the statements produce an error. Incorrect Answer: AStatement will not cause error BStatement will not cause error CStatement will not cause error DStatement will not cause error

Q2. - (Topic 1)

The following data exists in the PRODUCTS table: PROD_ID PROD_LIST_PRICE

123456 152525.99

You issue the following query:

SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,'*')

FROM products

WHERE prod_id = 123456;

What would be the outcome?

A. 152526****

B. **152525.99

C. 152525**

D. an error message

Answer: A

Explanation:

The LPAD(string, length after padding, padding string) and RPAD(string, length after padding, padding string) functions add a padding string of characters to the left or right of a string until it reaches the specified length after padding.

Q3. - (Topic 2)

Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.)

A. It locks only the columns specified in the SELECT list.

B. It locks the rows that satisfy the condition in the SELECT statement.

C. It can be used only in SELECT statements that are based on a single table.

D. It can be used in SELECT statements that are based on a single or multiple tables.

E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.

Answer: B,D

Explanation:

FOR UPDATE Clause in a SELECT Statement Locks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECT statement. FOR UPDATE Clause in a SELECT Statement When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is required because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data). There are times, however, when you may want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking. When you issue a SELECT...FOR UPDATE statement, the relational database management system (RDBMS) automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, thereby holding the records “for your changes only.” No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT. You can append the optional keyword NOWAIT to the FOR UPDATE clause to tell the Oracle server not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program or to your SQL Developer environment so that you can perform other work, or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available, when the locks are released by the other user through the issue of a COMMIT or a ROLLBACK command.

Q4. - (Topic 1)

You created an ORDERS table with the following description: Exhibit:

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 add a primary key constraint if data exists in the column

B. You can add the primary key constraint even if data exists, provided that there are no duplicate values

C. The primary key constraint can be created only a the time of table creation

D. You cannot have two constraints on one column

Answer: B

Q5. - (Topic 1)

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.

Q6. - (Topic 1)

Evaluate the following SQL commands:

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.

Answer: A,C

Explanation:

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

Q7. - (Topic 2)

Examine the structure proposed for the TRANSACTIONS table:

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.

Answer: B,D

Explanation:

B: The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually

any magnitude can be stored and are guaranteed portable among different systems

operating Oracle, up to 38 digits of precision.

The following numbers can be stored in a NUMBER column:

Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits Zero Positive and negative infinity (generated only by importing from an Oracle Version 5 database)

D: The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. An interval literal specifies a period of time, and Oracle supports two types of interval literals: YEAR_TO_MONTH and DAY TO SECOND. For DAY TO SECOND, you can specify these differences in terms in terms of days, hours, minutes, and seconds. DAY TO SECOND contains a leading field and may contain an optional trailing field. If trailing field is specified it must be less significant than the leading field. For example, INTERVAL MINUTE TO DAY is not valid.

A DAY TO MINUTE interval considers an interval of days to the nearest minute. Reference: Oracle Database Concepts 10g, Native Datatypes

Q8. - (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

Q9. - (Topic 2)

Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table:

PROMO_BEGIN _DATE

04-jan-00

10-jan-00

15-dec-99

18-oct-98

22-aug-99

You want to display the number of promotions started in 1999 and 2000.

Which query gives the correct output?

SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000",

SUM(DECODE(SUBSTR

(promo_begin_date,8),'99',1,0)) "1999"

FROM promotions;

SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0

END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE

0 END) "2000"

FROM promotions;

SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions;

SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1,

0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1,

0)) "2000"

FROM promotions;

Answer: A

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)