Oracle 1Z0-051 Exam Questions and Answers 2021

NEW QUESTION 1
The SQL statements executed in a user session as follows: Exhibit:

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.)

• A. Both the DELETE statements and the UPDATE statement are rolled back
• B. The rollback generates an error
• C. Only the DELETE statements are rolled back
• D. Only the seconds DELETE statement is rolled back
• E. No SQL statements are rolled back

Answer: BE

NEW QUESTION 2
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:



You issue the following query:

Which statement is true regarding the outcome of this query?

• A. It produces an error because the NATURAL join can be used only with two tables
• B. It produces an error because a column used in the NATURAL join cannot have a qualifier
• C. It produces an error because all columns used in the NATURAL join should have a qualifier
• D. It executes successfully

Answer: B

Explanation:
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.

NEW QUESTION 3
Which two statements are true regarding working with dates? (Choose two.)

• A. The default internal storage of dates is in the numeric format
• B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required
• C. The default internal storage of dates is in the character format
• D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century

Answer: AB

Explanation:
Working with Dates The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-RR. RR Date Format The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element.

untitled Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want. This data is stored internally as follows: CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43

NEW QUESTION 4
You work as a database administrator at ABC.com. You study the exhibit carefully.
Exhibit:

You issue the following SQL statement:

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

• A. It produces an error because the AMT_SPENT column contains a null valu
• B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMI
• C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is nul
• D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 functio

Answer: C

Explanation:
The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.

NEW QUESTION 5
Which best describes an inline view?

• A. a schema object
• B. a sub query that can contain an ORDER BY clause
• C. another name for a view that contains group functions
• D. a sub query that is part of the FROM clause of another query

Answer: D

Explanation:
a sub query that is part of the FROM clause of another query
Incorrect Answer:
Ais not a schema object
Bsub query can contain GROUP BY clause as well.
Cdoes not necessary contains group functions
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-21

NEW QUESTION 6
Evaluate the SQL statement:
SELECT LPAD (salary,10,’*’)
FROM EMP
WHERE EMP_ID = 1001;
If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

• A. 17000.00
• B. 17000*****
• C. ****170.00
• D. **17000.00
• E. an error statement

Answer: D

NEW QUESTION 7
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: ABD

NEW QUESTION 8
Which statement is true regarding the COALESCE function?

• A. It can have a maximum of five expressions in a lis
• B. It returns the highest NOT NULL value in the list for all row
• C. It requires that all expressions in the list must be of the same data typ
• D. It requires that at least one of the expressions in the list must have a NOT NULL valu

Answer: C

Explanation:
The COALESCE Function The COALESCE function returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned. The COALESCE function takes two mandatory parameters and any number of optional parameters. The syntax is COALESCE(expr1, expr2,…,exprn), where expr1 is returned if it is not null, else expr2 if it is not null, and so on. COALESCE is a general form of the NVL function, as the following two equations illustrate: COALESCE(expr1,expr2) = NVL(expr1,expr2) COALESCE(expr1,expr2,expr3) = NVL(expr1,NVL(expr2,expr3)) The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter. To avoid an “ORA-00932: inconsistent data types” error, all not null parameters must have data types compatible with the first not null parameter.

NEW QUESTION 9
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 constrain
• B. You cannot use the BETWEEN clause in the condition of a CHECK constrain
• C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a colum
• D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD NO is also the FOREIGN KE

Answer: AC

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

NEW QUESTION 10
Examine the structure of the PROMOS table:

You want to generate a report showing promo names and their duration (number of days).
If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed. Which queries give the correct output? (Choose all that apply.)

• A. SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING')) FROM promos;
• B. SELECT promo_name,COALESCE(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
• C. SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING') FROM promos;
• D. SELECT promo_name, DECODE(promo_end_date -promo_start_date,NULL,'ONGOING',promo_end_date - promo_start_date) FROM promos;
• E. SELECT promo_name,ecode(coalesce(promo_end_date,promo_start_date),null,'ONGOING', promo_end_date - promo_start_date) FROM promos;

Answer: BCD

NEW QUESTION 11
Which two statements are true regarding the USING clause in table joins?(Choose two.)

• A. It can be used to join a maximum of three table
• B. It can be used to restrict the number of columns used in a NATURAL joi
• C. It can be used to access data from tables through equijoins as well as nonequijoin
• D. It can be used to join tables that have columns with the same name and compatible data type

Answer: BD

Explanation:
NATURAL JOIN operation A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables. If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):
All the common columns
Every column in the first (left) table that is not a common column
Every column in the second (right) table that is not a common column
An asterisk qualified by a table name (for example, COUNTRIES.*) will be expanded to
every column of that table that is not a common column.
If a common column is referenced without being qualified by a table name, the column
reference points to the column in the first (left) table if the join is an INNER JOIN or a LEFT
OUTER JOIN. If it is a RIGHT OUTER JOIN, unqualified references to a common column
point to the column in the second (right) table.
Syntax
TableExpression NATURAL [ { LEFT | RIGHT } [ OUTER ] | INNER ] JOIN {
TableViewOrFunctionExpression |
( TableExpression ) }
Examples
If the tables COUNTRIES and CITIES have two common columns named COUNTRY and
COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:
SELECT * FROM COUNTRIES NATURAL JOIN CITIES
SELECT * FROM COUNTRIES JOIN CITIES
USING (COUNTRY, COUNTRY_ISO_CODE)

NEW QUESTION 12
View the Exhibit and examine the structure of the EMPLOYEES table.

Examine the data in the ENAME and HIREDATE columns of the EMPLOYEES table:
ENAME HIREDATE
SMITH 17-DEC-80 ALLEN 20-FEB-81 WARD 22-FEB-81
You want to generate a list of user IDs as follows: USERID
Smi17DEC80 All20FEB81 War22FEB81
You issue the following query:
SQL>SELECT CONCAT(SUBSTR(INITCAP(ename),1,3), REPLACE(hiredate,'-'))
"USERID"
FROM employees;
What is the outcome?

• A. It executes successfully and gives the correct outpu
• B. It executes successfully but does not give the correct outpu
• C. It generates an error because the REPLACE function is not vali
• D. It generates an error because the SUBSTR function cannot be nested in the CONCAT functio

Answer: A

Explanation:
REPLACE(text, search_string,replacement_string) Searches a text expression for a character string and, if found, replaces it with a specified replacement string The REPLACE Function The REPLACE function replaces all occurrences of a search item in a source string with a replacement term and returns the modified source string. If the length of the replacement term is different from that of the search item, then the lengths of the returned and source strings will be different. If the search string is not found, the source string is returned unchanged. Numeric and date literals and expressions are evaluated before being implicitly cast as characters when they occur as parameters to the REPLACE function. The REPLACE function takes three parameters, with the first two being mandatory. Its syntax is REPLACE (source string, search item, [replacement term]). If the replacement term parameter is omitted, each occurrence of the search item is removed from the source string. In other words, the search item is replaced by an empty string. . The following queries illustrate the REPLACE function with numeric and date expressions: Query 1: select replace(10000-3,'9','85') from dual Query 2: select replace(sysdate, 'DEC','NOV') from dual

NEW QUESTION 13
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: ACE

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.

NEW QUESTION 14
View the Exhibit and examine the data in the PRODUCTS table. You need to display product names from the PRODUCTS table that belong to the 'Software/Other1 category with minimum prices as either $2000 or $4000 and no unit of measure. You issue thej following query:

Which statement is true regarding the above query?

• A. It executes successfully but returns no resul
• B. It executes successfully and returns the required resul
• C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not vali
• D. It generates an error because the condition specified for the PROD_CATEGORY column is not vali

Answer: A

NEW QUESTION 15
Which are iSQL*Plus commands? (Choose all that apply.)

• A. INSERT
• B. UPDATE
• C. SELECT
• D. DESCRIBE
• E. DELETE
• F. RENAME

Answer: D

Explanation:
The only SQL*Plus command in this list : DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename, including all columns in that table, the datatype for each column and an indication of whether the column permits storage of NULL values.
Incorrect Answer: A INSERT is not a SQL*PLUS command B UPDATE is not a SQL*PLUS command C SELECT is not a SQL*PLUS command E DELETE is not a SQL*PLUS command F RENAME is not a SQL*PLUS command
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 7

NEW QUESTION 16
Which view should a user query to display the columns associated with the constraints on a table owned by the user?

• A. USER_CONSTRAINTS
• B. USER_OBJECTS
• C. ALL_CONSTRAINTS
• D. USER_CONS_COLUMNS
• E. USER_COLUMNS

Answer: D

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view. Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25

NEW QUESTION 17
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

NEW QUESTION 18
Examine the structure of the CUSTOMERS table:

CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names.
Which two methods can you use to get the required result? (Choose two.)

• A. self-join
• B. subquery
• C. full outer-join with self-join
• D. left outer-join with self-join
• E. right outer-join with self-join

Answer: AB

NEW QUESTION 19
Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date));
For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)

• A. SER_NO
• B. ORDER_ID
• C. STATUS
• D. PROD_ID
• E. ORD_TOTAL
• F. composite index on ORDER_ID and ORDER_DATE

Answer: AF

Explanation: Index exist for UNIQUE and PRIMARY KEY constraints
Incorrect Answer: BORDER_ID is neither UNIQUE nor PRIMARY KEY CSTATUS is neither UNIQUE nor PRIMARY KEY DPROD_ID is neither UNIQUE nor PRIMARY KEY EORD_TOTAL is neither UNIQUE nor PRIMARY KEY
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-15

NEW QUESTION 20
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?

• A. 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;
• B. 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;
• C. 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;
• D. 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