Exact 1Z0-051 Exam Questions and Answers 2021

NEW QUESTION 1
You are granted the CREATE VIEW privilege. What does this allow you to do?

• A. Create a table vie
• B. Create a view in any schem
• C. Create a view in your schem
• D. Create a sequence view in any schem
• E. Create a view that is accessible by everyon
• F. Create a view only of it is based on tables that you create

Answer: C

Explanation:
You can create a view in your own schema only if you are granted the CREATE VIEW
privilege.
Incorrect Answers
A:You can create a view in your own schema only.
B:You can create a view in your own schema only, not in any schema.
D:There is no sequence view in Oracle.
E:You cannot create a view that is accessible by everyone. You will need specially grant
SELECT privileges on this view for everyone.
F:You can create a view in your own schema, but not only for tables in your schema. You
can use object from other users schemas if you have privileges to retrieve data from them.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 292-301
Chapter 7: Creating Other Database Objects in Oracle

NEW QUESTION 2
The EMPLOYEES table has these columns:
LAST NAMEVARCHAR2(35) SALARYNUMBER(8,2) HIRE_DATEDATE
Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement:
ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000);
What is true about your ALTER statement?

• A. Column definitions cannot be altered to add DEFAULT value
• B. A change to the DEFAULT value affects only subsequent insertions to the tabl
• C. Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data typ
• D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

Answer: B

Explanation:
A change to the DEFAULT value affects only subsequent insertions to the table. Existing
rows will not be affected.
Incorrect Answers
A:Column definitions can be altered to add DEFAULT values.
C:Column definitions can be altered to add DEFAULT values. It works for columns with a
NUMBER data type also.
D:A change to the DEFAULT value affects only subsequent insertions to the table. Existing rows will not be affected.
OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 219-224 Chapter 5: Creating Oracle Database Objects

NEW QUESTION 3
Examine the structure of the TRANSACTIONS table:
Name Null Type
TRANS_ID NOT NULL NUMBER(3)
CUST_NAME VARCHAR2(30) TRANS_DATE DATE TRANS_AMT NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend. Evaluate the following two queries:

Which statement is true regarding the above queries?

• A. Both give wrong result
• B. Both give the correct resul
• C. Only the first query gives the correct resul
• D. Only the second query gives the correct resul

Answer: C

Explanation:
Range Conditions Using the BETWEEN Operator Use the BETWEEN operator to display rows based on a range of values: SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Range Conditions Using the BETWEEN Operator You can display rows based on a range of values using the BETWEEN operator. The range that you specify contains a lower limit and an upper limit. The SELECT statement in the slide returns rows from the EMPLOYEES table for any employee whose salary is between $2,500 and $3,500. Values that are specified with the BETWEEN operator are inclusive. However, you must specify the lower limit first. You can also use the BETWEEN operator on character values: SELECT last_name FROM employees WHERE last_name BETWEEN 'King' AND 'Smith';

NEW QUESTION 4
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 5
Which CREATE TABLE statement is valid?

• A. CREATE TABLE ord_details (ord_no NUMBER(2) PRIMARY KEY, item_no NUMBER(3) PRIMARY KEY, ord_date DATE NOT NULL);
• B. CREATE TABLE ord_details (ord_no NUMBER(2) UNIQUE, NOT NULL, item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL);
• C. CREATE TABLE ord_details (ord_no NUMBER(2) , item_no NUMBER(3), ord_date DATE DEFAULT NOT NULL, CONSTRAINT ord_uq UNIQUE (ord_no), CONSTRAINT ord_pk PRIMARY KEY (ord_no));
• D. CREATE TABLE ord_details (ord_no NUMBER(2), item_no NUMBER(3), ord_date DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));

Answer: D

Explanation:
PRIMARY KEY Constraint
A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value. Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns.

NEW QUESTION 6
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 7
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 8
SLS is a private synonym for the SH.SALES table.
The user SH issues the following command:
DROP SYNONYM sls;
Which statement is true regarding the above SQL statement?

• A. Only the synonym would be droppe
• B. The synonym would be dropped and the corresponding table would become invali
• C. The synonym would be dropped and the packages referring to the synonym would be droppe
• D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invali

Answer: A

Explanation:
A synonym is an alias for a table (or a view). Users can execute SQL statements against the synonym, and the database will map them into statements against the object to which the synonym points.
Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name. Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name.
Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects. When executing statements that address objects without a schema qualifier, Oracle will first look for the object in the local schema, and only if it cannot be found will it look for a public synonym.

NEW QUESTION 9
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 vali

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

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

You want to create a SALE_PROD view by executing the following SQL statements:

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

• A. The view will be created and you can perform DLM operations on the view
• B. The view will not be created because the join statements are not allowed for creating a view
• C. The view will not be created because the GROUP BY clause is not allowed for creating a view
• D. The view will be created but no DML operations will be allowed on the view

Answer: D

Explanation:
Rules for Performing DML Operations on a View You cannot add data through a view if the view includes: Group functions A GROUP BY clause The DISTINCT keyword The pseudocolumn ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view

NEW QUESTION 11
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 12
Which two statements are true regarding constraints? (Choose two.)

• A. A foreign key cannot contain NULL value
• B. The column with a UNIQUE constraint can store NULLS .
• C. A constraint is enforced only for an INSERT operation on a tabl
• D. You can have more than one column in a table as part of a primary ke

Answer: BD

NEW QUESTION 13
Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition?

• A. SELECT &1, "&2" FROM &3 WHERE last_name = '&4';
• B. SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4' ';
• C. SELECT &1, &2 FROM &3 WHERE last_name = '&4';
• D. SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

Answer: C

Explanation:
In a WHERE clause, date and characters values must be enclosed within single quotation marks.
Sample of the correct syntax
SELECT EMPLOYEE_ID, &COLUMN_NAME FROM EMPLOYEES
Incorrect Answers :
A. Incorrect use of " symbol
B. Incorrect use of ' symbol
D. No input for table name as EMP has been use in the statement.
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Producing Readable Output with iSQL*PLUS, p. 7-8

NEW QUESTION 14
Which three statements are true about multiple-row sub queries? (Choose three.)

• A. They can contain a subquery within a sub quer
• B. They can return multiple columns as well as row
• C. They cannot contain a sub query within a sub quer
• D. They can return only one column but multiple row
• E. They can contain group functions and GROUP BY and HAVING clause
• F. They can contain group functions and the GROUP BY clause, but not the HAVING claus

Answer: ABE

NEW QUESTION 15
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 Chicago data and time
• B. Number of days since the promo started based on the current Singapore data and tim
• C. An error because the WHERE condition specified is invalid
• D. An error because the ROUND function specified is invalid

Answer: A

NEW QUESTION 16
View the Exhibit and examine the structure and data in the INVOICE table.

Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.)

• A. inv_amt ='0255982' : requires explicit conversion
• B. inv_date > '01-02-2008' : uses implicit conversion
• C. CONCAT(inv_amt,inv_date) : requires explicit conversion
• D. inv_date = '15-february-2008' : uses implicit conversion
• E. inv_no BETWEEN '101' AND '110' : uses implicit conversion

Answer: DE

Explanation:
In some cases, the Oracle server receives data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user. Explicit data type conversions are performed by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type and the second data type is the output. Note: Although implicit data type conversion is available, it is recommended that you do the explicit data type conversion to ensure the reliability of your SQL statements.