Oracle 1Z0-051 Dumps Questions 2021

NEW QUESTION 1
Which constraint can be defined only at the column level?

• A. UNIQUE
• B. NOT NULL
• C. CHECK
• D. PRIMARY KEY
• E. FOREIGN KEY

Answer: B

Explanation:
The NOT NULL constraint can be defined only at the column level. It enforces that a value must be defined for this column such that the column may not be NULL for any row.
Incorrect Answers A:The UNIQUE constraint enforces uniqueness on values in the constrained column. It can be defined not only at the column level. C:The CHECK constraint enforces that values added to the constrained column must be present in a static list of values permitted for the column.
D:The PRIMARY KEY constraint stipulates that values in the constrained column(s) must be unique and not NULL. If the primary key applies to multiple columns, then the combination of values in the columns must be unique and not NULL. E:The FOREIGN KEY constraint enforces that only values in the primary key of a parent table may be included as values in the constrained column(s) of the child table.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 2
View the Exhibit and examine the description for the CUSTOMERS table.

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566.
Which UPDATE statement will accomplish the task?

• A. UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360;
• B. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360;
• C. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360;
• D. UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360;

Answer: A

Explanation:
Updating Two Columns with a Subquery
You can update multiple columns in the SET clause of an UPDATE statement by writing
multiple subqueries. The syntax is as follows:
UPDATE table
SET column =
(SELECT column
FROM table
WHERE condition)
[ ,
column =
(SELECT column
FROM table
WHERE condition)]
[WHERE condition ] ;

NEW QUESTION 3
View the Exhibit and examine the structure of the CUSTOMERS table.
In the CUSTOMERS table, the CUST_LAST_NAME column contains the values 'Anderson' and 'Ausson'.
You issue the following query:
SQL> SELECT LOWER(REPLACE(TRIM('son' FROM cust_last_name),'An','O'))
FROM CUSTOMERS
WHERE LOWER(cust_last_name) LIKE 'a%n';
What would be the outcome?

• A. 'Oder' and 'Aus'
• B. an error because the TRIM function specified is not valid
• C. an error because the LOWER function specified is not valid
• D. an error because the REPLACE function specified is not valid

Answer: B

Explanation:
Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero The TRIM Function The TRIM function removes characters from the beginning or end of character literals, columns or expressions to yield one potentially shorter character item. Numeric and date literals are automatically cast as characters when they occur as parameters to the TRIM function. Numeric or date expressions are evaluated first before being converted to strings ready to be trimmed. The TRIM function takes a parameter made up of an optional and a mandatory component. Its syntax is TRIM ([trailing|leading|both] trimstring from s). The string to be trimmed (s) is mandatory. The following points list the rules governing the use of this function:
TRIM(s) removes spaces from both sides of the input string.
TRIM(trailing trimstring from s) removes all occurrences of trimstring from the end of the string s if it is present. TRIM(leading trimstring from s) removes all occurrences of trimstring from the beginning of the string s if it is present.
TRIM(both trimstring from s) removes all occurrences of trimstring from the beginning and
end of the string s if it is present.
The following queries illustrate the usage of this function:
Query 1: select trim(trailing 'e' from 1+2.14||' is pie') from dual
Query 2: select trim(both '*' from '*******Hidden*******') from dual
Query 3: select trim(1 from sysdate) from dual
ORA-30001: trim set should have only one character
30001. 00000 - "trim set should have only one character"
*Cause: Trim set contains more or less than 1 character. This is not allowed in TRIM
function.
REPLACE(text, search_string, replacement_string)
Searches a text expression for a character string and, if found, replaces it with a specified
replacement string

NEW QUESTION 4
Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?

• A. SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
• B. SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
• C. SELECT DISTINCT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;
• D. SELECT cust_income_level ' ' cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;

Answer: C

Explanation: Duplicate Rows Unless you indicate otherwise, SQL displays the results of a query without eliminating the duplicate rows. To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword. You can specify multiple columns after the DISTINCT qualifier. The DISTINCT qualifier affects all the selected columns, and the result is every distinct combination of the columns.

NEW QUESTION 5
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 6
Which two are true about aggregate functions? (Choose two.)

• A. You can use aggregate functions in any clause of a SELECT statemen
• B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statemen
• C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row column
• D. You can pass column names, expressions, constants, or functions as parameter to an aggregate functio
• E. You can use aggregate functions on a table, only by grouping the whole table as one single grou
• F. You cannot group the rows of a table by more than one column while using aggregate function

Answer: AD

NEW QUESTION 7
Evaluate the SQL statement:
SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
FROM dual;
Which values are displayed?

• A. 46 and 45
• B. 46 and 45.93
• C. 50 and 45.93
• D. 50 and 45.9
• E. 45 and 45.93
• F. 45.95 and 45.93

Answer: C

Explanation:
ROUND (45.953,-1) will round value to 1 decimal places to the left. TRUNC (45.936,2) will truncate value to 2 decimal The answer will be 50 and 45.93
Incorrect Answers :
A. Does not meet round and truncate functions
B. Does not meet round functions
D. Does not meet truncate functions
E. Does not meet round functions
F. Does not meet round functions
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Single-Row functions, p. 3-13

NEW QUESTION 8
View the Exhibit and examine the structure of CUSTOMERS and SALES tables.

Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id
FROM sales)
SET time_id = '22-MAR-2007'
WHERE cust_id = (SELECT cust_id
FROM customers
WHERE cust_last_name = 'Roberts' AND
credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?

• A. It would not execute because two tables cannot be used in a single UPDATE statemen
• B. It would not execute because the SELECT statement cannot be used in place of the table nam
• C. It would execute and restrict modifications to only the columns specified in the SELECT statemen
• D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statemen

Answer: C

Explanation:
One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.

NEW QUESTION 9
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the query statement:

What would be the outcome of the above statement?

• A. It executes successfull
• B. It produces an error because the condition on CUST_LAST_NAME is invali
• C. It executes successfully only if the CUST_CREDIT_LIMIT column does not contain any null value
• D. It produces an error because the AND operator cannot be used to combine multiple BETWEEN clause

Answer: A

NEW QUESTION 10
Which arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

• A. addition
• B. subtraction
• C. raising to a power
• D. finding the quotient
• E. finding the lowest value

Answer: ACE

NEW QUESTION 11
Evaluate the following SQL statements: Exhibit:

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization.
Exhibit:

Which two statements are true regarding the above view? (Choose two.)

• A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table
• B. It allows you to delete details of the existing sales staff from the EMPLOYEES table
• C. It allows you to insert rows into the EMPLOYEES table
• D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements

Answer: BD

NEW QUESTION 12
Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)

• A. Combining more than two columns or expressions into a single column in the output
• B. Displaying a date in a nondefault format
• C. Substituting a character string in a text expression with a specified string
• D. Finding the number of characters in an expression

Answer: BCD

NEW QUESTION 13
In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
CUST_FIRST_NAME 'ABIGAIL'.
Evaluate the following query:

What would be the outcome?

• A. Abigail PA
• B. Abigail Pa
• C. Abigail IS
• D. an error message

Answer: B

NEW QUESTION 14
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?

• A. 1
• B. 10
• C. 100
• D. an error

Answer: A

Explanation:
But why the answer is not "C" ? Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling. If you wanted to keep the minimum value you would need to specify it in the sequence creation. sequence Is the name of the sequence generator INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.) START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.) MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending sequence (This is the default option.) MINVALUE n Specifies the minimum sequence value NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending sequence (This is the default option.)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value (NOCYCLE is the default option.) CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)

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

and examine the structure of CUSTOMRS AND SALES tables:
Evaluate the following SQL statement:
Exhibit:

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

• A. It would not execute because the SELECT statement cannot be used in place of the table name
• B. It would execute and restrict modifications to only the column specified in the SELECT statement
• C. It would not execute because a sub query cannot be used in the WHERE clause of an UPDATE statement
• D. It would not execute because two tables cannot be used in a single UPDATE statement

Answer: B

NEW QUESTION 16
Examine the statement:
GRANT select, insert, update
ON student_grades
TO manager
WITH GRANT OPTION;
Which two are true? (Choose two.)

• A. MANAGER must be a rol
• B. It allows the MANAGER to pass the specified privileges on to other user
• C. It allows the MANAGER to create tables that refer to the STUDENT_GRADES tabl
• D. It allows the MANAGER to apply all DML statements on the STUDENT_GRADES tabl
• E. It allows the MANAGER the ability to select from, insert into, and update the STUDENT_GRADES tabl
• F. It allows the MANAGER the ability to select from, delete from, and update the STUDENT_GRADES tabl

Answer: BE

Explanation:
GRANT ROLE to ROLE/USER
Incorrect Answer: ARole can be grant to user CCreate table privilege is not granted DExecute privilege is not granted FDelete privilege is not granted
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-15