Free Oracle 1z0-051 Questions Answers - Oracle 1z0-051 Exam Dumps PDF Dumps4Download

Get February Latest Oracle 1z0-051 Exam Dumps Questions - Dumps4Download

Question No : 1
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.

Question No : 2
See the exhibit and examine the structure of the CUSTOMERS and GRADES tables:

You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.)

A.
SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval;

B.
SELECT custname, grade
FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit)
FROM customers) BETWEEN startval and endval
AND cust_credit_limit BETWEEN startval AND endval;

C.
SELECT custname, grade
FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit)
FROM customers)
AND cust_credit_limit BETWEEN startval AND endval;

D.
SELECT custname, grade
FROM customers , grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit)
FROM customers)
AND MAX(cust_credit_limit) BETWEEN startval AND endval;


Answer: B,C

Complete Oracle 1z0-051 Exam Dumps - Pass In 24 Hours - Dumps4Download

Question No : 3
View the Exhibit and examine the structure of the CUSTOMERS table. 

NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the CUSTOMERS table.

Evaluate the following INSERT statement:

The INSERT statement fails when executed. What could be the reason?

A. The VALUES clause cannot be used in an INSERT with a subquery

B. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table

C. The WHERE clause cannot be used in a sub query embedded in an INSERT statement

D. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match

Answer: A

Explanation:

Copying Rows from Another Table Write your INSERT statement with a subquery:Do not use the VALUES clause.Match the number of columns in the INSERT clause to those in the subquery.Inserts all the rows returned by the subquery in the table, sales_reps. 

Question No : 4

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

Download Oracle 1z0-051 Exam - Valid Oracle 1z0-051 Question Answers - Dumps4Download

Question No : 5

View the Exhibit and examine the structure of the PRODUCTS table. All products have a list price.

You issue the following command to display the total price of each product after a discount of 25% and a tax of 15% are applied on it. Freight charges of S100 have to be applied to all the products.

What would be the outcome if all the parentheses are removed from the above statement?

A. It produces a syntax error.

B. The result remains unchanged.

C. The total price value would be lower than the correct value.

D. The total price value would be higher than the correct value.

Answer: B

Question No : 6

Examine the structure of the MARKS table:

Exhibit:

Which two statements would execute successfully? (Choose two.)

A. SELECT student_name,subject1

FROM marks

WHERE subject1 > AVG(subject1);

B. SELECT student_name,SUM(subject1)

FROM marks

WHERE student_name LIKE 'R%';

C. SELECT SUM(subject1+subject2+subject3)

FROM marks

WHERE student_name IS NULL;

D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)

FROM marks

WHERE subject1 > subject2;

Answer: C,D

Prepare Oracle 1z0-051 Question Answers - Oracle 1z0-051 Exam Dumps - Dumps4Download

Question No : 7

Which three statements/commands would cause a transaction to end? (Choose three.)

A. COMMIT

B. SELECT

C. CREATE

D. ROLLBACK

E. SAVEPOINT

Answer: A,C,D

Question No :8

Examine the description of the EMP_DETAILS table given below:

Exhibit:

Which two statements are true regarding SQL statements that can be executed on the EMP_DETAIL table? (Choose two.)

A. An EMP_IMAGE column can be included in the GROUP BY clause

B. You cannot add a new column to the table with LONG as the data type

C. An EMP_IMAGE column cannot be included in the ORDER BY clause

D. You can alter the table to include the NOT NULL constraint on the EMP_IMAGE column

Answer: B,C

Explanation: 

LONG Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB.There can only be one LONG column in a table.Guidelines

A LONG column is not copied when a table is created using a subquery.A LONG column cannot be included in a GROUP BY or an ORDER BY clause.Only one LONG column can be used per table.No constraints can be defined on a LONG column.

You might want to use a CLOB column rather than a LONG column.

Free Oracle 1z0-051 Exam Dumps - Oracle 1z0-051 Certifications - Dumps4Download

Question No :9

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

Question No : 10

Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table: 

Which two expressions give the same output? (Choose two.)

A. NVL(NULLIF(list_price, min_price), 0)

B. NVL(COALESCE(list_price, min_price), 0)

C. NVL2(COALESCE(list_price, min_price), min_price, 0)

D. COALESCE(NVL2(list_price, list_price, min_price), 0)

Answer: B,D

Explanation:

Using the COALESCE Function

• The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.

• If the first expression is not null, the COALESCE function returns that expression; otherwise, it does a COALESCE of the remaining expressions.Using the COALESCE Function The COALESCE function returns the first non-null expression in the list. Syntax

COALESCE (expr1, expr2, ... exprn)

In the syntax:

• expr1 returns this expression if it is not null

• expr2 returns this expression if the first expression is null and this expression is not null

• exprn returns this expression if the preceding expressions are null

Note that all expressions must be of the same data type.

Download Oracle 1z0-051 Exam - 2018 Updated Oracle 1z0-051 Question Answers - Dumps4Download