Oracle 1Z0-051 Dumps Questions 2021

NEW QUESTION 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 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 2
View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.

Evaluate the following SQL command:
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id;
Which two statements are true regarding the outcome of the above query? (Choose two.)

• A. It locks all the rows that satisfy the condition in the statemen
• B. It locks only the columns that satisfy the condition in both the table
• C. The locks are released only when a COMMIT or ROLLBACK is issue
• D. The locks are released after a DML statement is executed on the locked row

Answer: AC

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 SELECTstatement SELECT employee_id, salary, commission_pct, job_id FROM employees WHERE job_id = 'SA_REP' FOR UPDATE ORDER BY employee_id;

NEW QUESTION 3
Examine the structure of the EMPLOYEES table:

Which INSERT statement is valid?

• A. INSERT INTO employees (employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ‘01/01/01’);
• B. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, ’01 January 01’);
• C. INSERT INTO employees(employee_id, first_name, last_name, Hire_date) VALUES ( 1000, ‘John’, ‘Smith’, To_date(‘01/01/01’));
• D. INSERT INTO employees(employee_id, first_name, last_name, hire_date) VALUES ( 1000, ‘John’, ‘Smith’, 01-Jan-01);

Answer: D

Explanation: It is the only statement that has a valid date; all other will result in an error. Answer A is incorrect, syntax error, invalid date format

NEW QUESTION 4
View the Exhibit and examine the structure of the PROMOTIONS table.
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
1.
The promo name should not begin with 'T' or 'N'.
2.
The promo should cost more than $20000.
3.
The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?

• A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
• C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
• D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')

Answer: C

NEW QUESTION 5
What are two reasons to create synonyms? (Choose two.)

• A. You have too many table
• B. Your tables names are too lon
• C. Your tables have difficult name
• D. You want to work on your own table
• E. You want to use another schema's table
• F. You have too many columns in your table

Answer: BC

Explanation:
Create a synonyms when the names of the tables are too long or the table names are difficult.

NEW QUESTION 6
View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category.
Which query would give the correct output?

• A. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
• B. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
• C. SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category);
• D. SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products);

Answer: A

Explanation:
Using the ANY Operator in Multiple-Row Subqueries
The ANY operator (and its synonym, the SOME operator) compares a value to each value
returned by a subquery.
<ANY means less than the maximum.
>ANY means more than the minimum.
=ANY is equivalent to IN
Using the ALL Operator in Multiple-Row Subqueries
The ALL operator compares a value to every value returned by a subquery.
>ALL means more than the maximum and
<ALL means less than the minimum.
The NOT operator can be used with IN, ANY, and ALL operators.

NEW QUESTION 7
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 8
The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR.
Which statement would create a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;

• A. CREATE SYNONYM ord FOR orders; This command is issued by O
• B. CREATE PUBLIC SYNONYM ord FOR orders; This command is issued by O
• C. CREATE SYNONYM ord FOR oe.orders; This command is issued by the database administrato
• D. CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrato

Answer: D

Explanation:
Creating a Synonym for an Object To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views. In the syntax: PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to be created object Identifies the object for which the synonym is created Guidelines The object cannot be contained in a package. A private synonym name must be distinct from all other objects that are owned by the same user. If you try to execute the following command (alternative B, issued by OE):

NEW QUESTION 9
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30)
TRANS_DATE TIMESTAMP
TRANS_AMT NUMBER(10,2)
You want to display the date, time, and transaction amount of transactions that where done before 12 noon.
The value zero should be displayed for transactions where the transaction amount has not been entered.
Which query gives the required result?

• A. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), TO_CHAR(trans_amt,'$99999999D99') FROM transactions WHERE TO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 AND COALESCE(trans_amt,NULL)<>NULL;
• B. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL(TO_CHAR(trans_amt,'$99999999D99'),0) FROM transactions WHERE TO_CHAR(trans_date,'hh24') < 12;
• C. SELECT TO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'), COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;
• D. SELECT TO_DATE (trans_date,'dd-mon-yyyy hh24:mi:ss'), NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'), 0) FROM transactions WHERE TO_DATE(trans_date,'hh24') < 12;

Answer: B

NEW QUESTION 10
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 11
Which SQL statement returns a numeric value?

• A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
• B. SELECT ROUND(hire_date) FROM EMP;
• C. SELECT sysdate-hire_date FROM EMP;
• D. SELECT TO_NUMBER(hire_date + 7) FROM EMP;

Answer: C

Explanation:
DATE value subtract DATE value will return numeric value.
Incorrect Answer: Adoes not return numeric value Bdoes not return numeric value Ddoes not return numeric value
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-13

NEW QUESTION 12
View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables.
You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers.
Which FROM clause gives the required result?

• A. FROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id);
• B. FROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
• C. FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id);
• D. FROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id);

Answer: C

NEW QUESTION 13
View the Exhibits and examine the structures of the PROMOTIONS and SALES tables.


Evaluate the following SQL statements:
Which statement is true regarding the output of the above query?

• A. It gives details of product IDs that have been sold irrespective of whether they had a promo or not
• B. It gives the details of promos for which there have been no sales
• C. It gives the details of promos for which there have been sales
• D. It gives details of all promos irrespective of whether they have resulted in a sale or not

Answer: D

NEW QUESTION 14
Evaluate the following SQL statement:
SQL> SELECT cust_id. cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city='Singapore'):
Which statement is true regarding the above query if one of the values generated by the sub query is NULL?

• A. It produces an erro
• B. It executes but returns no row
• C. It generates output for NULL as well as the other values produced by the sub quer
• D. It ignores the NULL value and generates output for the other values produced by the sub quer

Answer: C

NEW QUESTION 15
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?

• A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• C. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);
• D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.last_name);

Answer: A

Explanation:
The correct statement for MERGE is MERGE INTO table_name Incorrect Answer: BWrong statement with the keyword EXISTS CWrong statement with the keyword EXISTS DWrong statement on the MERGE new_employees
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-29

NEW QUESTION 16
See the Exhibit and examine the structure of the PROMOSTIONS table: Exhibit:

Which SQL statements are valid? (Choose all that apply.)

• A. SELECT promo_id, DECODE(NVL(promo_cost,0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions;
• B. SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions;
• C. SELECT promo_id, DECODE(NULLIF(promo_cost, 10000), NULL, promo_cost*.25, 'N/A') "Catcost" FROM promotions;
• D. SELECT promo_id, DECODE(promo_cost, >10000, 'High', <10000, 'Low') "Range" FROM promotions;

Answer: AB

Explanation:
The DECODE Function Although its name sounds mysterious, this function is straightforward. The DECODE function implements ifthen-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. The DECODE function takes at least three mandatory parameters, but can take many more. The syntax of the function is DECODE(expr1,comp1, iftrue1, [comp2,iftrue2...[ compN,iftrueN]], [iffalse]).